-- Advanced Scraper V3.5 — additive migration
ALTER TABLE scrape_sources
    ADD COLUMN IF NOT EXISTS pagination_depth TINYINT NOT NULL DEFAULT 1 AFTER max_per_run,
    ADD COLUMN IF NOT EXISTS discovery_limit SMALLINT NOT NULL DEFAULT 100 AFTER pagination_depth,
    ADD COLUMN IF NOT EXISTS include_regex VARCHAR(500) NULL AFTER discovery_limit,
    ADD COLUMN IF NOT EXISTS exclude_regex VARCHAR(500) NULL AFTER include_regex,
    ADD COLUMN IF NOT EXISTS min_title_length SMALLINT NOT NULL DEFAULT 8 AFTER exclude_regex,
    ADD COLUMN IF NOT EXISTS min_description_length SMALLINT NOT NULL DEFAULT 0 AFTER min_title_length,
    ADD COLUMN IF NOT EXISTS retry_limit TINYINT NOT NULL DEFAULT 2 AFTER min_description_length,
    ADD COLUMN IF NOT EXISTS last_discovered INT NOT NULL DEFAULT 0 AFTER last_success_at,
    ADD COLUMN IF NOT EXISTS last_attempted INT NOT NULL DEFAULT 0 AFTER last_discovered,
    ADD COLUMN IF NOT EXISTS last_saved INT NOT NULL DEFAULT 0 AFTER last_attempted,
    ADD COLUMN IF NOT EXISTS last_duplicates INT NOT NULL DEFAULT 0 AFTER last_saved,
    ADD COLUMN IF NOT EXISTS last_failed INT NOT NULL DEFAULT 0 AFTER last_duplicates,
    ADD COLUMN IF NOT EXISTS last_retry_pending INT NOT NULL DEFAULT 0 AFTER last_failed,
    ADD COLUMN IF NOT EXISTS last_old_skipped INT NOT NULL DEFAULT 0 AFTER last_retry_pending,
    ADD COLUMN IF NOT EXISTS last_extraction_rate DECIMAL(5,2) NOT NULL DEFAULT 0 AFTER last_old_skipped,
    ADD COLUMN IF NOT EXISTS last_scrape_error VARCHAR(1000) NULL AFTER last_extraction_rate,
    ADD COLUMN IF NOT EXISTS last_scrape_run_at DATETIME NULL AFTER last_scrape_error;

CREATE TABLE IF NOT EXISTS scrape_retry_queue (
    url_hash CHAR(32) NOT NULL,
    source_id VARCHAR(100) NOT NULL,
    url VARCHAR(1500) NOT NULL,
    attempts TINYINT NOT NULL DEFAULT 0,
    last_http_status SMALLINT NULL,
    last_error VARCHAR(1000) NULL,
    next_retry_at DATETIME NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (url_hash),
    KEY idx_scrape_retry_due (next_retry_at),
    KEY idx_scrape_retry_source (source_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;