-- ============================================================
-- 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',
 '<!doctype html><html><body style="margin:0;background:#f6f8fa;font-family:Arial,sans-serif;color:#1f2937"><div style="max-width:560px;margin:32px auto;background:#fff;border-radius:12px;overflow:hidden;border:1px solid #e5e7eb"><div style="padding:24px 28px;background:#0f766e;color:#fff"><h1 style="margin:0;font-size:20px">Password changed</h1></div><div style="padding:24px 28px;line-height:1.55;font-size:14px"><p>Hi {{name}},</p><p>This is a confirmation that the password for your <strong>{{app_name}}</strong> account was changed on <strong>{{when}}</strong>.</p><p>If you did <strong>not</strong> change your password, please contact the administrator immediately.</p><p style="margin-top:28px;color:#6b7280;font-size:12px">— {{app_name}}</p></div></div></body></html>',
 'Variables: {{name}} {{app_name}} {{when}}'),

('payment_thanks',
 'Payment received — Invoice {{invoice_no}}',
 '<!doctype html><html><body style="margin:0;background:#f6f8fa;font-family:Arial,sans-serif;color:#1f2937"><div style="max-width:560px;margin:32px auto;background:#fff;border-radius:12px;overflow:hidden;border:1px solid #e5e7eb"><div style="padding:24px 28px;background:#0f766e;color:#fff"><h1 style="margin:0;font-size:20px">Thank you for your payment</h1></div><div style="padding:24px 28px;line-height:1.55;font-size:14px"><p>Dear {{name}},</p><p>We have received your payment for invoice <strong>{{invoice_no}}</strong> dated {{date}}.</p><table style="border-collapse:collapse;margin:16px 0"><tr><td style="padding:6px 16px 6px 0;color:#6b7280">Amount</td><td style="padding:6px 0"><strong>৳{{amount}}</strong></td></tr><tr><td style="padding:6px 16px 6px 0;color:#6b7280">Invoice</td><td style="padding:6px 0">{{invoice_no}}</td></tr><tr><td style="padding:6px 16px 6px 0;color:#6b7280">Date</td><td style="padding:6px 0">{{date}}</td></tr></table><p>Thank you for being a part of {{app_name}}.</p><p style="margin-top:28px;color:#6b7280;font-size:12px">— {{app_name}}</p></div></div></body></html>',
 'Variables: {{name}} {{invoice_no}} {{amount}} {{date}} {{app_name}}'),

('student_reminder',
 '{{title}}',
 '<!doctype html><html><body style="margin:0;background:#f6f8fa;font-family:Arial,sans-serif;color:#1f2937"><div style="max-width:560px;margin:32px auto;background:#fff;border-radius:12px;overflow:hidden;border:1px solid #e5e7eb"><div style="padding:24px 28px;background:#0f766e;color:#fff"><h1 style="margin:0;font-size:20px">{{title}}</h1></div><div style="padding:24px 28px;line-height:1.55;font-size:14px"><p>Dear {{name}},</p><p>{{body}}</p><p style="margin-top:28px;color:#6b7280;font-size:12px">— {{app_name}}</p></div></div></body></html>',
 'Variables: {{title}} {{name}} {{body}} {{app_name}}'),

('notice_broadcast',
 '{{title}}',
 '<!doctype html><html><body style="margin:0;background:#f6f8fa;font-family:Arial,sans-serif;color:#1f2937"><div style="max-width:600px;margin:32px auto;background:#fff;border-radius:12px;overflow:hidden;border:1px solid #e5e7eb"><div style="padding:24px 28px;background:#0f766e;color:#fff"><h1 style="margin:0;font-size:20px">{{title}}</h1></div><div style="padding:24px 28px;line-height:1.55;font-size:14px">{{body_html}}<p style="margin-top:28px;color:#6b7280;font-size:12px">— {{app_name}}</p></div></div></body></html>',
 '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;
