-- ============================================================
--  CareBill — CCAvenue.
--  Existing database:  mysql carebill < sql/ccavenue.sql
-- ============================================================

-- One row per time a client presses "Pay" — CCAvenue needs a fresh order id for
-- every attempt, and an attempt that never comes back (the client closed the tab
-- after paying) has to be found and asked about.
CREATE TABLE ccav_attempts (
  id              BIGINT AUTO_INCREMENT PRIMARY KEY,
  link_id         BIGINT NOT NULL,
  order_id        VARCHAR(30) NOT NULL,            -- ours, sent to CCAvenue
  amount          DECIMAL(14,2) NOT NULL,
  currency        CHAR(3) DEFAULT 'INR',
  status          ENUM('initiated','returned','verified','failed','abandoned','unverified') DEFAULT 'initiated',
  tracking_id     VARCHAR(40) NULL,                -- CCAvenue's reference, once known
  bank_ref_no     VARCHAR(60) NULL,
  gateway_status  VARCHAR(30) NULL,                -- what CCAvenue said, verbatim
  payment_mode    VARCHAR(40) NULL,
  payment_id      BIGINT NULL,                     -- the receipt, once credited
  checks          INT DEFAULT 0,
  last_checked_at DATETIME NULL,
  note            VARCHAR(255),
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_ccav_order (order_id),
  UNIQUE KEY uq_ccav_tracking (tracking_id),       -- one CCAvenue payment, one receipt, ever
  INDEX ix_ccav_link (link_id), INDEX ix_ccav_status (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO settings (k,v) VALUES
 ('ccav_merchant_id',''), ('ccav_access_code',''), ('ccav_working_key',''),
 ('ccav_mode','test')                               -- test | live
ON DUPLICATE KEY UPDATE k=k;