-- =====================================================================
--  PayTrack • Wishlist features migration
--  Target schema: CANONICAL (tbl_bill / tbl_category_master / tbl_holder_master)
--  Run once:  mysql -u paytrak_user -p paytrak_db < 2026_06_wishlist.sql
--
--  Adds:
--   1. Payment ledger          -> tbl_payment_transaction
--   2. Manual late fee         -> tbl_bill.late_fee  (+ per-txn late_fee)
--   3. Reminder ladder + snooze-> tbl_bill.reminder_lead_days, tbl_reminder_log
--   4. Partial payment status  -> extends payment_status ENUM with 'partial'
--   5. Paid-amount cache        -> tbl_bill.amount_paid (maintained by ledger)
--  Cash-flow forecast, calendar, and PDF export need NO schema change.
-- =====================================================================

-- ---- 4 + 5 : extend bill table -------------------------------------
ALTER TABLE tbl_bill
    MODIFY COLUMN payment_status ENUM('pending','partial','paid','overdue')
        NOT NULL DEFAULT 'pending';

ALTER TABLE tbl_bill
    ADD COLUMN amount_paid       DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER bill_amount,
    ADD COLUMN late_fee          DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER amount_paid,
    ADD COLUMN reminder_lead_days INT          NOT NULL DEFAULT 7     AFTER recurrence_type;

-- ---- 1 : payment ledger --------------------------------------------
CREATE TABLE IF NOT EXISTS tbl_payment_transaction (
    txn_id        INT AUTO_INCREMENT PRIMARY KEY,
    bill_id       INT          NOT NULL,
    amount_paid   DECIMAL(12,2) NOT NULL,
    late_fee      DECIMAL(12,2) NOT NULL DEFAULT 0.00,   -- manual, per payment
    payment_date  DATE          NOT NULL,
    payment_mode  VARCHAR(50)   DEFAULT '',
    notes         VARCHAR(255)  DEFAULT '',
    is_active     TINYINT(1)    NOT NULL DEFAULT 1,
    created_on    DATETIME      DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_txn_bill FOREIGN KEY (bill_id)
        REFERENCES tbl_bill(bill_id) ON DELETE CASCADE,
    INDEX idx_txn_bill (bill_id),
    INDEX idx_txn_date (payment_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---- 3 : reminder ladder log (dedupe + snooze) ---------------------
-- One row per (bill, stage) once sent, so the cron never double-sends.
-- snoozed_until on the bill's latest row suppresses all stages until that date.
CREATE TABLE IF NOT EXISTS tbl_reminder_log (
    log_id        INT AUTO_INCREMENT PRIMARY KEY,
    bill_id       INT          NOT NULL,
    stage         ENUM('LEAD','DUE_SOON','DUE_TODAY','OVERDUE') NOT NULL,
    sent_on       DATE          NOT NULL,
    snoozed_until DATE          NULL,
    is_active     TINYINT(1)    NOT NULL DEFAULT 1,
    created_on    DATETIME      DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_rem_bill FOREIGN KEY (bill_id)
        REFERENCES tbl_bill(bill_id) ON DELETE CASCADE,
    UNIQUE KEY uq_bill_stage_cycle (bill_id, stage, sent_on),
    INDEX idx_rem_bill (bill_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;