-- =====================================================================
-- 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 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;
