-- Import via cPanel → phpMyAdmin → your database → Import
-- (create the database + user first in cPanel → MySQL Databases)

CREATE TABLE IF NOT EXISTS applicants (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  email         VARCHAR(255) NOT NULL UNIQUE,
  first_name    VARCHAR(120) NOT NULL,
  last_name     VARCHAR(120),
  role_applied  VARCHAR(200),
  recruiter_name VARCHAR(120),
  stage         VARCHAR(40) DEFAULT 'applied',
  unsubscribed  TINYINT(1) DEFAULT 0,
  created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS email_queue (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  applicant_id INT NOT NULL,
  template_key VARCHAR(60) NOT NULL,          -- application_received, ai_screen_invite, offer_letter, id_verification, rejection
  merge_json   TEXT,                          -- extra per-email fields as JSON
  send_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  status       VARCHAR(20) DEFAULT 'pending', -- pending | sending | sent | failed | cancelled
  attempts     INT DEFAULT 0,
  last_error   TEXT,
  sent_at      DATETIME NULL,
  created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_due (status, send_at),
  FOREIGN KEY (applicant_id) REFERENCES applicants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS email_log (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  queue_id     INT,
  to_email     VARCHAR(255),
  subject      VARCHAR(300),
  template_key VARCHAR(60),
  sent_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;