-- Migration 005: full crawl metrics for 46-column audit table
ALTER TABLE crawl_pages
    ADD COLUMN IF NOT EXISTS content_length      INT UNSIGNED NULL    DEFAULT NULL    COMMENT 'HTTP Content-Length header (bytes)',
    ADD COLUMN IF NOT EXISTS transferred_bytes   INT UNSIGNED NULL    DEFAULT NULL    COMMENT 'Actual response body size in bytes',
    ADD COLUMN IF NOT EXISTS sentence_count      SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Number of sentences in visible text',
    ADD COLUMN IF NOT EXISTS flesch_reading_ease FLOAT        NOT NULL DEFAULT 0     COMMENT 'Flesch Reading Ease score 0-100',
    ADD COLUMN IF NOT EXISTS text_ratio          FLOAT        NOT NULL DEFAULT 0     COMMENT 'Text-to-HTML ratio as percentage',
    ADD COLUMN IF NOT EXISTS last_modified       VARCHAR(100) NULL    DEFAULT NULL    COMMENT 'HTTP Last-Modified response header',
    ADD COLUMN IF NOT EXISTS redirect_type       VARCHAR(10)  NULL    DEFAULT NULL    COMMENT 'HTTP status code of the first redirect hop (301/302 etc.)',
    ADD COLUMN IF NOT EXISTS http_version        VARCHAR(10)  NOT NULL DEFAULT 'HTTP/1.1' COMMENT 'HTTP protocol version of the final response';

-- Index hint: ensure crawl_run_id is indexed on crawl_links for JOIN performance
-- CREATE INDEX IF NOT EXISTS idx_cl_run ON crawl_links (crawl_run_id, is_internal);
