-- ============================================================ -- Email Templates manager + Scheduled Student Reminders -- Run once on the MySQL host. -- ============================================================ CREATE TABLE IF NOT EXISTS email_templates ( `key` VARCHAR(64) NOT NULL PRIMARY KEY, subject VARCHAR(255) NOT NULL, html LONGTEXT NOT NULL, description VARCHAR(255) NULL, updated_by VARCHAR(64) NULL, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT IGNORE INTO email_templates (`key`, subject, html, description) VALUES ('password_changed', 'Your {{app_name}} password was changed', '

Password changed

Hi {{name}},

This is a confirmation that the password for your {{app_name}} account was changed on {{when}}.

If you did not change your password, please contact the administrator immediately.

— {{app_name}}

', 'Variables: {{name}} {{app_name}} {{when}}'), ('payment_thanks', 'Payment received — Invoice {{invoice_no}}', '

Thank you for your payment

Dear {{name}},

We have received your payment for invoice {{invoice_no}} dated {{date}}.

Amount৳{{amount}}
Invoice{{invoice_no}}
Date{{date}}

Thank you for being a part of {{app_name}}.

— {{app_name}}

', 'Variables: {{name}} {{invoice_no}} {{amount}} {{date}} {{app_name}}'), ('student_reminder', '{{title}}', '

{{title}}

Dear {{name}},

{{body}}

— {{app_name}}

', 'Variables: {{title}} {{name}} {{body}} {{app_name}}'), ('notice_broadcast', '{{title}}', '

{{title}}

{{body_html}}

— {{app_name}}

', 'Variables: {{title}} {{body_html}} {{app_name}}'); -- ============================================================ CREATE TABLE IF NOT EXISTS reminder_schedules ( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255) NOT NULL, template_key VARCHAR(64) NOT NULL DEFAULT 'student_reminder', audience_class VARCHAR(32) NULL, audience_group VARCHAR(64) NULL, audience_session INT NULL, audience_filter ENUM('all','unpaid','custom') NOT NULL DEFAULT 'all', custom_emails TEXT NULL, subject_override VARCHAR(255) NULL, body_override LONGTEXT NULL, schedule_type ENUM('once','daily','weekly','monthly') NOT NULL DEFAULT 'once', next_run_at DATETIME NULL, last_run_at DATETIME NULL, active TINYINT(1) NOT NULL DEFAULT 1, created_by VARCHAR(64) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_active_next (active, next_run_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS reminder_send_log ( id INT AUTO_INCREMENT PRIMARY KEY, schedule_id INT NULL, recipient_email VARCHAR(255) NOT NULL, status ENUM('sent','failed','skipped') NOT NULL, error VARCHAR(500) NULL, sent_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_schedule (schedule_id, sent_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;