-- Analytics snapshot tables — cached metric data per project per period

CREATE TABLE IF NOT EXISTS analytics_snapshots (
    id                          INT AUTO_INCREMENT PRIMARY KEY,
    project_id                  INT            NOT NULL,
    period_type                 VARCHAR(20)    NOT NULL DEFAULT 'custom', -- weekly|biweekly|monthly|3month|6month|yoy|custom
    period_start                DATE           NOT NULL,
    period_end                  DATE           NOT NULL,

    -- GSC Metrics
    gsc_clicks                  INT            DEFAULT 0,
    gsc_impressions             INT            DEFAULT 0,
    gsc_ctr                     DECIMAL(10,8)  DEFAULT 0,
    gsc_position                DECIMAL(8,2)   DEFAULT 0,
    branded_clicks              INT            DEFAULT 0,
    branded_impressions         INT            DEFAULT 0,
    non_branded_clicks          INT            DEFAULT 0,
    non_branded_impressions     INT            DEFAULT 0,

    -- GA4 Traffic by channel
    organic_sessions            INT            DEFAULT 0,
    paid_sessions               INT            DEFAULT 0,
    direct_sessions             INT            DEFAULT 0,
    social_sessions             INT            DEFAULT 0,
    referral_sessions           INT            DEFAULT 0,

    -- GA4 Users
    overall_users               INT            DEFAULT 0,
    new_users                   INT            DEFAULT 0,

    -- GA4 Organic Engagement
    organic_engaged_sessions    INT            DEFAULT 0,
    organic_page_views          INT            DEFAULT 0,
    organic_avg_engagement_time DECIMAL(10,2)  DEFAULT 0,
    organic_engagement_rate     DECIMAL(10,8)  DEFAULT 0,

    -- GA4 Revenue (e-commerce)
    overall_revenue             DECIMAL(14,2)  DEFAULT 0,
    organic_revenue             DECIMAL(14,2)  DEFAULT 0,
    overall_orders              INT            DEFAULT 0,
    organic_orders              INT            DEFAULT 0,

    -- GA4 Lead Categories (JSON: {"calls": 145, "forms": 87, "whatsapp": 32})
    lead_category_totals        JSON           NULL,
    total_leads                 INT            DEFAULT 0,

    -- AI & LLM
    ai_overview                 INT            DEFAULT 0,
    ref_chatgpt                 INT            DEFAULT 0,
    ref_claude                  INT            DEFAULT 0,
    ref_perplexity              INT            DEFAULT 0,
    ref_gemini                  INT            DEFAULT 0,
    ref_copilot                 INT            DEFAULT 0,
    llm_total                   INT            DEFAULT 0,

    -- Meta
    fetched_at                  DATETIME       NULL,
    created_at                  DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at                  DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY uq_project_period (project_id, period_start, period_end),
    INDEX idx_project_id  (project_id),
    INDEX idx_period_type (period_type),
    INDEX idx_fetched_at  (fetched_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Per-event counts within lead categories (enables drill-down in Team report)
CREATE TABLE IF NOT EXISTS analytics_event_data (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    snapshot_id     INT          NOT NULL,
    project_id      INT          NOT NULL,
    category_key    VARCHAR(50)  NOT NULL,   -- e.g. "calls", "forms", "whatsapp"
    event_name      VARCHAR(200) NOT NULL,   -- exact GA4 event name
    organic_count   INT          DEFAULT 0,
    created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_snapshot  (snapshot_id),
    INDEX idx_project   (project_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Page-level performance data (GA4 + GSC merged by URL path)
CREATE TABLE IF NOT EXISTS analytics_page_data (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    snapshot_id     INT           NOT NULL,
    project_id      INT           NOT NULL,
    url             VARCHAR(2000) NOT NULL,
    ga4_sessions    INT           DEFAULT 0,
    ga4_page_views  INT           DEFAULT 0,
    gsc_clicks      INT           DEFAULT 0,
    gsc_impressions INT           DEFAULT 0,
    gsc_ctr         DECIMAL(10,8) DEFAULT 0,
    gsc_position    DECIMAL(8,2)  DEFAULT 0,
    created_at      DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_snapshot  (snapshot_id),
    INDEX idx_project   (project_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Query-level GSC data
CREATE TABLE IF NOT EXISTS analytics_query_data (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    snapshot_id INT           NOT NULL,
    project_id  INT           NOT NULL,
    query       VARCHAR(1000) NOT NULL,
    clicks      INT           DEFAULT 0,
    impressions INT           DEFAULT 0,
    ctr         DECIMAL(10,8) DEFAULT 0,
    position    DECIMAL(8,2)  DEFAULT 0,
    created_at  DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_snapshot  (snapshot_id),
    INDEX idx_project   (project_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
