-- =====================================================================
-- Phase 7 — Payment Gateway Integration — Migration
-- আগের schema.sql-এর উপর এই ফাইলটা আলাদাভাবে রান করুন (existing DB-তে):
--   mysql -u isp_app -p ispbdcom_isp_billing < database/migration_phase7.sql
-- নতুন ইনস্টলে schema.sql-এর সাথে এটা ইতিমধ্যে merge করা আছে।
-- =====================================================================

USE ispbdcom_isp_billing;

-- ---------------------------------------------------------------------
-- Gateway Settings — bKash / SSLCommerz ইত্যাদির API Credential রাখা হয়
-- (credentials সবসময় AES-256-GCM এনক্রিপ্টেড, Crypto::encrypt() দিয়ে)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS payment_gateways (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    gateway_key     VARCHAR(30) NOT NULL UNIQUE COMMENT 'bkash | sslcommerz',
    display_name    VARCHAR(60) NOT NULL,
    mode            ENUM('sandbox','live') NOT NULL DEFAULT 'sandbox',
    is_enabled      TINYINT(1) NOT NULL DEFAULT 0,
    credentials_enc TEXT DEFAULT NULL COMMENT 'AES-256-GCM encrypted JSON blob',
    updated_by      BIGINT UNSIGNED DEFAULT NULL,
    updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

INSERT IGNORE INTO payment_gateways (gateway_key, display_name, mode, is_enabled) VALUES
    ('bkash', 'bKash Tokenized Checkout', 'sandbox', 0),
    ('sslcommerz', 'SSLCommerz (Card/Nagad/Rocket/Bank)', 'sandbox', 0);

-- ---------------------------------------------------------------------
-- Gateway Transactions — প্রতিটি অনলাইন পেমেন্ট চেষ্টার সম্পূর্ণ লগ
-- (auto-reconciliation ও manual audit দুটোর জন্যই এই টেবিল ব্যবহার হয়)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS gateway_transactions (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    invoice_id        BIGINT UNSIGNED NOT NULL,
    customer_id       BIGINT UNSIGNED NOT NULL,
    gateway_key       VARCHAR(30) NOT NULL,
    merchant_txn_id   VARCHAR(64) NOT NULL UNIQUE COMMENT 'আমাদের নিজস্ব রেফারেন্স, গেটওয়েতে পাঠানো হয়',
    gateway_txn_id    VARCHAR(100) DEFAULT NULL COMMENT 'paymentID (bKash) / val_id-tran_id (SSLCommerz)',
    amount            DECIMAL(10,2) NOT NULL,
    currency          VARCHAR(10) NOT NULL DEFAULT 'BDT',
    status            ENUM('initiated','pending','completed','failed','cancelled') NOT NULL DEFAULT 'initiated',
    raw_response      JSON DEFAULT NULL,
    reconciled        TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1 হলে payments টেবিলে এন্ট্রি বসে গেছে',
    payment_id        BIGINT UNSIGNED DEFAULT NULL,
    attempts          SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'কতবার status verify করা হয়েছে',
    created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE SET NULL,
    INDEX idx_status (status),
    INDEX idx_invoice (invoice_id),
    INDEX idx_gateway_txn (gateway_txn_id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- Invoice-এ Public Pay Token — লগইন ছাড়াই গ্রাহক লিংক থেকে বিল দেখে
-- পেমেন্ট করতে পারবে (token secret, ভাঙা যায় না এমন লম্বা random string)
-- ---------------------------------------------------------------------
ALTER TABLE invoices
    ADD COLUMN pay_token VARCHAR(64) DEFAULT NULL UNIQUE AFTER invoice_no;