-- One-off migration: adds the payment_reminders table, which logs each
-- payment reminder (upcoming / due / overdue) generated by
-- cron/send_payment_reminders.php so admin and adviser can see them in the
-- notification bell, and so the cron doesn't re-fire the same reminder
-- twice. Run once against the app database:
--
--   mysql -h <host> -u <user> -p <database> < scripts/create_payment_reminders_table.sql
--
-- (or paste the statement below into phpMyAdmin / Adminer / your MySQL
-- client of choice). No existing tables are modified.

CREATE TABLE IF NOT EXISTS payment_reminders (
    id             INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    student_id     INT NOT NULL,
    reminder_type  ENUM('upcoming', 'due', 'overdue') NOT NULL,
    due_date       DATE NOT NULL,     -- actual/projected date the balance goes negative
    trigger_date   DATE NOT NULL,     -- calendar date the cron generated this reminder
    created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_payment_reminder (student_id, reminder_type, trigger_date),
    INDEX idx_payment_reminders_student_id (student_id),
    INDEX idx_payment_reminders_trigger_date (trigger_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
