-- ============================================================
--  CareBill — online payment links.
--  Existing database:  mysql carebill < sql/gateway.sql
-- ============================================================

CREATE TABLE payment_links (
  id              BIGINT AUTO_INCREMENT PRIMARY KEY,
  token           CHAR(40) NOT NULL,              -- ours, used in the URL we hand out
  client_id       INT NOT NULL,
  invoice_id      INT NULL,
  amount          DECIMAL(14,2) NOT NULL,
  currency        CHAR(3) DEFAULT 'INR',
  provider        VARCHAR(20) DEFAULT 'manual',
  provider_ref    VARCHAR(120) NULL,              -- the gateway's own link or order id
  short_url       VARCHAR(255) NULL,
  purpose         VARCHAR(160),
  status          ENUM('created','sent','paid','partly_paid','expired','cancelled','failed') DEFAULT 'created',
  amount_paid     DECIMAL(14,2) DEFAULT 0,
  expires_at      DATETIME NULL,
  created_by      INT NULL,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  paid_at         DATETIME NULL,
  UNIQUE KEY uq_pl_token (token),
  INDEX ix_pl_invoice (invoice_id), INDEX ix_pl_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- every webhook the gateway sends, kept whole. The unique key on the provider's
-- event id is what makes a retried or replayed webhook harmless.
CREATE TABLE gateway_events (
  id              BIGINT AUTO_INCREMENT PRIMARY KEY,
  provider        VARCHAR(20) NOT NULL,
  event_id        VARCHAR(120) NOT NULL,
  event_type      VARCHAR(60),
  link_id         BIGINT NULL,
  payment_id      BIGINT NULL,
  signature_ok    TINYINT(1) DEFAULT 0,
  processed       TINYINT(1) DEFAULT 0,
  result          VARCHAR(255),
  payload         MEDIUMTEXT,
  ip              VARCHAR(45),
  received_at     DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_ge (provider, event_id),
  INDEX ix_ge_link (link_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE payments
  ADD COLUMN gateway VARCHAR(20) NULL AFTER source,
  ADD COLUMN gateway_ref VARCHAR(120) NULL AFTER gateway,
  ADD COLUMN gateway_fee DECIMAL(12,2) DEFAULT 0 AFTER gateway_ref,
  ADD COLUMN settled_to_bank TINYINT(1) DEFAULT 0 AFTER gateway_fee;

INSERT INTO settings (k,v) VALUES
 ('pay_enabled','0'), ('pay_provider','manual'), ('pay_key_id',''), ('pay_key_secret',''),
 ('pay_webhook_secret',''), ('pay_link_days','15'), ('pay_min_amount','100'),
 ('pay_base_url','https://api.razorpay.com/v1')
ON DUPLICATE KEY UPDATE k=k;

-- online is a payment mode now
ALTER TABLE payments MODIFY mode ENUM('neft','rtgs','imps','upi','cheque','cash','card','adjustment','online','other') DEFAULT 'neft';
ALTER TABLE payments MODIFY source ENUM('manual','gateway','bank_import','portal') DEFAULT 'manual';