CREATE TABLE IF NOT EXISTS crawl_pagespeed (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    crawl_run_id  INT          NOT NULL,
    project_id    INT          NOT NULL,
    url           VARCHAR(2000) NOT NULL,
    strategy      ENUM('desktop','mobile') NOT NULL DEFAULT 'mobile',
    score         INT          DEFAULT NULL,
    fcp           DECIMAL(8,3) DEFAULT NULL,
    lcp           DECIMAL(8,3) DEFAULT NULL,
    tbt           DECIMAL(8,3) DEFAULT NULL,
    cls           DECIMAL(8,4) DEFAULT NULL,
    si            DECIMAL(8,3) DEFAULT NULL,
    tti           DECIMAL(8,3) DEFAULT NULL,
    score_seo     INT          DEFAULT NULL,
    score_a11y    INT          DEFAULT NULL,
    score_bp      INT          DEFAULT NULL,
    opportunities JSON         DEFAULT NULL,
    fetched_at    DATETIME     DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_run     (crawl_run_id),
    INDEX idx_project (project_id),
    UNIQUE KEY uq_run_url_strategy (crawl_run_id, url(500), strategy)
);
