-- SQLite schema (auto-applied on first run).
-- For MySQL: INTEGER PRIMARY KEY AUTOINCREMENT -> BIGINT AUTO_INCREMENT PRIMARY KEY,
--            TEXT dates -> DATETIME. For Postgres: use BIGSERIAL / TIMESTAMPTZ.
CREATE TABLE IF NOT EXISTS batches (
  id                  INTEGER PRIMARY KEY AUTOINCREMENT,
  name                TEXT,
  status              TEXT NOT NULL DEFAULT 'queued',   -- queued | processing | completed | failed
  total_count         INTEGER NOT NULL DEFAULT 0,
  processed_count     INTEGER NOT NULL DEFAULT 0,
  deliverable_count   INTEGER NOT NULL DEFAULT 0,
  risky_count         INTEGER NOT NULL DEFAULT 0,
  undeliverable_count INTEGER NOT NULL DEFAULT 0,
  created_at          TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  completed_at        TEXT
);

CREATE TABLE IF NOT EXISTS verifications (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  batch_id      INTEGER REFERENCES batches(id) ON DELETE CASCADE,  -- NULL = single lookup
  email         TEXT NOT NULL,
  domain        TEXT NOT NULL,
  status        TEXT,                                -- Deliverable | Risky | Undeliverable
  health_score  INTEGER,
  valid_syntax  INTEGER,
  has_mx        INTEGER,
  is_disposable INTEGER,
  is_role       INTEGER,
  is_catch_all  INTEGER,
  smtp_result   TEXT,                                -- accepted | rejected | unknown | skipped
  processed     INTEGER NOT NULL DEFAULT 0,
  created_at    TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  processed_at  TEXT
);
CREATE INDEX IF NOT EXISTS idx_ver_batch ON verifications (batch_id, processed);
CREATE INDEX IF NOT EXISTS idx_ver_email ON verifications (email);