CREATE TABLE IF NOT EXISTS schema_migrations (
    version INTEGER PRIMARY KEY,
    description TEXT NOT NULL,
    applied_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS comments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id TEXT,
    username TEXT,
    target_type TEXT NOT NULL,
    target_id TEXT NOT NULL,
    content TEXT NOT NULL,
    likes INTEGER NOT NULL DEFAULT 0,
    is_hidden INTEGER NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_comments_target
ON comments(target_type, target_id, is_hidden, created_at DESC);

CREATE TABLE IF NOT EXISTS player_metadata (
    steam_id_64 TEXT PRIMARY KEY,
    notes TEXT,
    tags TEXT NOT NULL DEFAULT '[]',
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS strategy_boards (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    map_name TEXT NOT NULL,
    data_json TEXT NOT NULL,
    created_by TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS team_lineups (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    description TEXT,
    player_ids_json TEXT NOT NULL DEFAULT '[]',
    is_active INTEGER NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_team_lineups_single_active
ON team_lineups(is_active)
WHERE is_active = 1;

CREATE TABLE IF NOT EXISTS team_roster_versions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    effective_from INTEGER NOT NULL,
    effective_to INTEGER,
    is_current INTEGER NOT NULL DEFAULT 0,
    notes TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CHECK (effective_to IS NULL OR effective_to >= effective_from)
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_roster_versions_single_current
ON team_roster_versions(is_current)
WHERE is_current = 1;

CREATE INDEX IF NOT EXISTS idx_roster_versions_period
ON team_roster_versions(effective_from, effective_to);

CREATE TABLE IF NOT EXISTS team_roster_members (
    roster_version_id INTEGER NOT NULL,
    steam_id_64 TEXT NOT NULL,
    member_role TEXT NOT NULL DEFAULT 'member'
        CHECK (member_role IN ('starter', 'substitute', 'member')),
    position_order INTEGER NOT NULL DEFAULT 0,
    PRIMARY KEY (roster_version_id, steam_id_64),
    FOREIGN KEY (roster_version_id)
        REFERENCES team_roster_versions(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_roster_members_player
ON team_roster_members(steam_id_64, roster_version_id);

CREATE TABLE IF NOT EXISTS wiki_pages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    path TEXT NOT NULL UNIQUE,
    title TEXT NOT NULL,
    content TEXT NOT NULL DEFAULT '',
    updated_by TEXT,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS etl_jobs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    job_type TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'queued'
        CHECK (status IN ('queued', 'running', 'succeeded', 'failed')),
    match_id TEXT,
    input_path TEXT,
    current_stage TEXT,
    progress INTEGER NOT NULL DEFAULT 0
        CHECK (progress >= 0 AND progress <= 100),
    message TEXT,
    log_text TEXT NOT NULL DEFAULT '',
    created_by TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    started_at TIMESTAMP,
    finished_at TIMESTAMP,
    duration_seconds REAL
);

CREATE INDEX IF NOT EXISTS idx_etl_jobs_created
ON etl_jobs(created_at DESC, id DESC);

CREATE INDEX IF NOT EXISTS idx_etl_jobs_status
ON etl_jobs(status, created_at);

CREATE TABLE IF NOT EXISTS match_imports (
    match_id TEXT PRIMARY KEY,
    content_sha256 TEXT NOT NULL,
    source_path TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'queued',
    job_id INTEGER,
    imported_at TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (job_id) REFERENCES etl_jobs(id) ON DELETE SET NULL
);