PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS clients (
    id TEXT PRIMARY KEY,
    public_jwk TEXT NOT NULL,
    created_at INTEGER NOT NULL,
    last_seen INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS settings (
    key TEXT PRIMARY KEY,
    value TEXT NOT NULL,
    updated_at INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS tasks (
    id TEXT PRIMARY KEY,
    secret TEXT NOT NULL,
    public_seed TEXT NOT NULL,
    range_bits INTEGER NOT NULL,
    status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active','completed','closed')),
    paused INTEGER NOT NULL DEFAULT 0,
    guess_min_interval_sec INTEGER,
    client_submit_interval_sec INTEGER,
    revision INTEGER NOT NULL DEFAULT 0,
    parent_task_id TEXT REFERENCES tasks(id),
    display_name TEXT NOT NULL DEFAULT '',
    description TEXT NOT NULL DEFAULT '',
    nft_prompt_instructions TEXT NOT NULL DEFAULT '',
    nft_negative_prompt TEXT NOT NULL DEFAULT '',
    nft_style_reference TEXT NOT NULL DEFAULT '',
    created_at INTEGER NOT NULL,
    completed_at INTEGER,
    winner_client_id TEXT REFERENCES clients(id),
    winner_signature TEXT,
    winning_guess TEXT,
    artifact_status TEXT NOT NULL DEFAULT 'none' CHECK (artifact_status IN ('none','pending','generating','ready','error')),
    artifact_uri TEXT,
    artifact_manifest_uri TEXT,
    artifact_error TEXT
);
CREATE INDEX IF NOT EXISTS tasks_status_created_idx ON tasks(status, created_at);

-- False guesses are intentionally not persisted. This table only keeps the
-- aggregate state needed for the 3D map, ranking, rate limiting and next seq.
CREATE TABLE IF NOT EXISTS task_points (
    task_id TEXT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
    client_id TEXT NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
    score REAL NOT NULL DEFAULT 0,
    x REAL NOT NULL DEFAULT 0,
    y REAL NOT NULL DEFAULT 0,
    z REAL NOT NULL DEFAULT 0,
    guess_count INTEGER NOT NULL DEFAULT 0,
    next_seq INTEGER NOT NULL DEFAULT 0,
    last_guess_at INTEGER,
    PRIMARY KEY (task_id, client_id)
);
CREATE INDEX IF NOT EXISTS task_points_rank_idx ON task_points(task_id, score DESC);



-- A browser or shell identity can explicitly choose which active task it works
-- on. The selection is persisted so switching devices with the same exported
-- identity keeps the chosen task (while single-active-connection enforcement
-- still applies at runtime).
CREATE TABLE IF NOT EXISTS client_task_selection (
    client_id TEXT PRIMARY KEY REFERENCES clients(id) ON DELETE CASCADE,
    task_id TEXT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
    updated_at INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS client_task_selection_task_idx ON client_task_selection(task_id);

CREATE TABLE IF NOT EXISTS client_unlocks (
    client_id TEXT NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
    unlock_key TEXT NOT NULL,
    task_id TEXT REFERENCES tasks(id) ON DELETE SET NULL,
    created_at INTEGER NOT NULL,
    PRIMARY KEY (client_id, unlock_key)
);

-- A short lease enforces one active websocket per browser identity.
CREATE TABLE IF NOT EXISTS presence_leases (
    client_id TEXT PRIMARY KEY,
    session_id TEXT NOT NULL,
    expires_at INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS presence_exp_idx ON presence_leases(expires_at);

-- Admin task actions may run immediately or at a future timestamp. Payloads are
-- small JSON objects validated by the Go server before being scheduled.
CREATE TABLE IF NOT EXISTS task_actions (
    id TEXT PRIMARY KEY,
    task_id TEXT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
    action_type TEXT NOT NULL,
    payload_json TEXT NOT NULL DEFAULT '{}',
    execute_at INTEGER NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','running','done','error','cancelled')),
    created_at INTEGER NOT NULL,
    executed_at INTEGER,
    error TEXT
);
CREATE INDEX IF NOT EXISTS task_actions_due_idx ON task_actions(status, execute_at);
CREATE INDEX IF NOT EXISTS task_actions_task_idx ON task_actions(task_id, created_at DESC);