

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