-- Analytics Pipeline Schema
-- MySQL 8.0+ / InnoDB / utf8mb4

CREATE DATABASE IF NOT EXISTS analytics
  DEFAULT CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE analytics;

-- ============================================================
-- 1. pageviews — primary fact table for page-level events
-- ============================================================
CREATE TABLE IF NOT EXISTS pageviews (
    id                INT AUTO_INCREMENT PRIMARY KEY,
    url               VARCHAR(2048) NOT NULL,
    type              ENUM('pageview','event','error','performance') NOT NULL DEFAULT 'pageview',
    user_agent        VARCHAR(512),
    viewport_width    SMALLINT UNSIGNED,
    viewport_height   SMALLINT UNSIGNED,
    referrer          VARCHAR(2048),
    client_timestamp  DATETIME,
    server_timestamp  DATETIME NOT NULL,
    client_ip         VARCHAR(45),
    session_id        VARCHAR(64),
    payload           JSON,
    created_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_pv_url (url(255)),
    INDEX idx_pv_type (type),
    INDEX idx_pv_server_timestamp (server_timestamp),
    INDEX idx_pv_session_id (session_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 2. events — custom events (clicks, scrolls, form submissions)
-- ============================================================
CREATE TABLE IF NOT EXISTS events (
    id                INT AUTO_INCREMENT PRIMARY KEY,
    session_id        VARCHAR(64),
    event_name        VARCHAR(128) NOT NULL,
    event_category    VARCHAR(128),
    event_data        JSON,
    url               VARCHAR(2048),
    server_timestamp  DATETIME NOT NULL,
    created_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_ev_event_name (event_name),
    INDEX idx_ev_session_id (session_id),
    INDEX idx_ev_server_timestamp (server_timestamp)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 3. errors — JavaScript errors captured by the collector
-- ============================================================
CREATE TABLE IF NOT EXISTS errors (
    id                INT AUTO_INCREMENT PRIMARY KEY,
    session_id        VARCHAR(64),
    error_message     VARCHAR(1024) NOT NULL,
    error_source      VARCHAR(2048),
    error_line        INT UNSIGNED,
    error_column      INT UNSIGNED,
    stack_trace       TEXT,
    url               VARCHAR(2048),
    user_agent        VARCHAR(512),
    server_timestamp  DATETIME NOT NULL,
    created_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_err_message (error_message(255)),
    INDEX idx_err_session_id (session_id),
    INDEX idx_err_server_timestamp (server_timestamp)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 4. performance — Navigation Timing + Web Vitals
-- ============================================================
CREATE TABLE IF NOT EXISTS performance (
    id                  INT AUTO_INCREMENT PRIMARY KEY,
    session_id          VARCHAR(64),
    url                 VARCHAR(2048) NOT NULL,
    ttfb                INT UNSIGNED COMMENT 'Time to First Byte (ms)',
    dom_content_loaded  INT UNSIGNED COMMENT 'DOMContentLoaded (ms)',
    dom_complete        INT UNSIGNED COMMENT 'domComplete (ms)',
    load_time           INT UNSIGNED COMMENT 'loadEventEnd (ms)',
    lcp                 DECIMAL(10,2) COMMENT 'Largest Contentful Paint (ms)',
    cls                 DECIMAL(8,4) COMMENT 'Cumulative Layout Shift',
    inp                 DECIMAL(10,2) COMMENT 'Interaction to Next Paint (ms)',
    fcp                 DECIMAL(10,2) COMMENT 'First Contentful Paint (ms)',
    transfer_size       INT UNSIGNED COMMENT 'Total transfer size (bytes)',
    resource_count      SMALLINT UNSIGNED,
    server_timestamp    DATETIME NOT NULL,
    created_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_perf_url (url(255)),
    INDEX idx_perf_session_id (session_id),
    INDEX idx_perf_server_timestamp (server_timestamp)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 5. sessions — aggregated visit data
-- ============================================================
CREATE TABLE IF NOT EXISTS sessions (
    id                INT AUTO_INCREMENT PRIMARY KEY,
    session_id        VARCHAR(64) NOT NULL,
    first_page        VARCHAR(2048) NOT NULL,
    last_page         VARCHAR(2048) NOT NULL,
    page_count        INT UNSIGNED NOT NULL DEFAULT 1,
    start_time        DATETIME NOT NULL,
    last_activity     DATETIME NOT NULL,
    duration_seconds  INT UNSIGNED NOT NULL DEFAULT 0,
    referrer          VARCHAR(2048),
    user_agent        VARCHAR(512),
    created_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_sess_session_id (session_id),
    INDEX idx_sess_start_time (start_time),
    INDEX idx_sess_last_activity (last_activity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 6. users — dashboard login accounts (NOT analytics visitors)
-- ============================================================
CREATE TABLE IF NOT EXISTS users (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    email         VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    display_name  VARCHAR(128) NOT NULL,
    role          ENUM('owner','admin','viewer') NOT NULL DEFAULT 'viewer',
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login    DATETIME,

    INDEX idx_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
