-- =====================================================================
--  Email Verifier Portal — MySQL schema
--  Run once:  mysql -u root -p email_verifier < schema.sql
--  (Create the database first: CREATE DATABASE email_verifier
--   CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;)
-- =====================================================================

-- Persistent verification cache. This is what protects your mail-server
-- reputation: once an email is here, it is NEVER re-verified until reset.
CREATE TABLE IF NOT EXISTS email_cache (
    id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    email        VARCHAR(320)    NOT NULL,
    domain       VARCHAR(255)    NOT NULL DEFAULT '',
    status       ENUM('deliverable','undeliverable','catch-all','unknown',
                      'disposable','invalid_syntax','no_mx') NOT NULL,
    is_catch_all TINYINT(1)      NOT NULL DEFAULT 0,
    smtp_code    SMALLINT        NULL,
    checked_at   TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP
                                 ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_email (email),
    KEY idx_domain (domain),
    KEY idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- One row per uploaded file / verification run.
CREATE TABLE IF NOT EXISTS verification_jobs (
    id                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    job_token         CHAR(32)        NOT NULL,
    original_filename VARCHAR(255)    NULL,
    total_emails      INT             NOT NULL DEFAULT 0,
    new_emails        INT             NOT NULL DEFAULT 0,
    cached_emails     INT             NOT NULL DEFAULT 0,
    processed         INT             NOT NULL DEFAULT 0,
    status            ENUM('pending','processing','paused','completed','failed')
                                      NOT NULL DEFAULT 'pending',
    notify_email      VARCHAR(255)    NULL, -- email sent here once status becomes 'completed'
    notified_at       DATETIME        NULL, -- guards against sending the notification twice
    created_at        TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_token (job_token)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Every (member, company, email) triple from the upload, deduped by email.
-- Used to rebuild the export mapped back to companies.
CREATE TABLE IF NOT EXISTS job_records (
    id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    job_id       BIGINT UNSIGNED NOT NULL,
    member_id    VARCHAR(100)    NULL,
    company_name VARCHAR(255)    NULL,
    email        VARCHAR(320)    NOT NULL,
    PRIMARY KEY (id),
    KEY idx_job (job_id),
    KEY idx_email (email),
    CONSTRAINT fk_records_job FOREIGN KEY (job_id)
        REFERENCES verification_jobs (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- The work queue: ONLY the new/unknown emails that actually need verifying.
CREATE TABLE IF NOT EXISTS job_queue (
    id        BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    job_id    BIGINT UNSIGNED NOT NULL,
    email     VARCHAR(320)    NOT NULL,
    processed TINYINT(1)      NOT NULL DEFAULT 0,
    attempts  SMALLINT UNSIGNED NOT NULL DEFAULT 0, -- soft-fail retries before giving up
    PRIMARY KEY (id),
    KEY idx_job_processed (job_id, processed),
    CONSTRAINT fk_queue_job FOREIGN KEY (job_id)
        REFERENCES verification_jobs (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Per-domain reputation guard: contact spacing, soft-fail cooldowns, and
-- catch-all caching. See app/includes/DomainThrottle.php for how it's used.
CREATE TABLE IF NOT EXISTS domain_throttle (
    domain                VARCHAR(255) NOT NULL,
    last_contacted_at     DATETIME     NULL,
    cooldown_until         DATETIME     NULL,
    catch_all              TINYINT(1)   NULL,
    catch_all_checked_at   DATETIME     NULL,
    PRIMARY KEY (domain)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Runtime-editable settings (sender address, rate limits, etc.), managed from
-- the Settings page in the UI. Values are stored as strings and cast on read.
CREATE TABLE IF NOT EXISTS app_settings (
    setting_key   VARCHAR(100) NOT NULL,
    setting_value TEXT         NULL,
    updated_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP
                               ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Login accounts. No rows are seeded here on purpose — bcrypt hashes can't be
-- generated in portable SQL. Create your admin account with:
--   php bin/create_admin.php <username> <password>
CREATE TABLE IF NOT EXISTS users (
    id              BIGINT UNSIGNED    NOT NULL AUTO_INCREMENT,
    username        VARCHAR(100)       NOT NULL,
    password_hash   VARCHAR(255)       NOT NULL,
    failed_attempts SMALLINT UNSIGNED  NOT NULL DEFAULT 0,
    locked_until    DATETIME           NULL,
    last_login_at   DATETIME           NULL,
    created_at      TIMESTAMP          NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed defaults. INSERT IGNORE so re-running this file never clobbers
-- values you've already changed from the Settings page.
INSERT IGNORE INTO app_settings (setting_key, setting_value) VALUES
    ('mail_from',                'no-reply@yourdomain.com'),
    ('ehlo_host',                'verifier.yourdomain.com'),
    ('smtp_port',                '25'),
    ('smtp_timeout',             '10'),
    ('batch_size',               '25'),
    ('smtp_delay_ms',            '400'),
    ('smtp_delay_jitter_ms',     '250'),
    ('hourly_limit',             '500'),
    ('daily_limit',              '3000'),
    ('domain_delay_ms',          '5000'),
    ('soft_fail_cooldown_minutes', '30'),
    ('catch_all_cache_days',     '30'),
    ('catch_all_as_deliverable', '0');