-- =====================================================================
--  Migration: reputation-safety upgrade (domain throttling, soft-fail
--  backoff, catch-all caching, daily cap).
--
--  Safe to run on an EXISTING database — only adds new columns/tables and
--  new setting rows; nothing here touches or deletes existing data.
--
--  Run:  mysql -u root -p email_verifier < migrations/002_reputation_safety.sql
-- =====================================================================

-- 1. Retry counter for soft-fails (greylisting), so a queue item doesn't
--    stay pending forever if a domain keeps saying "try again later."
ALTER TABLE job_queue
    ADD COLUMN attempts SMALLINT UNSIGNED NOT NULL DEFAULT 0;

-- 2. Per-domain throttle state: contact spacing, cooldowns, catch-all cache.
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;

-- 3. New settings (INSERT IGNORE — won't touch settings you've already set).
INSERT IGNORE INTO app_settings (setting_key, setting_value) VALUES
    ('smtp_delay_jitter_ms',       '250'),
    ('daily_limit',                '3000'),
    ('domain_delay_ms',            '5000'),
    ('soft_fail_cooldown_minutes', '30'),
    ('catch_all_cache_days',       '30');