128 lines
4.0 KiB
SQL
128 lines
4.0 KiB
SQL
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
|
|
);
|