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 '',
    retire_after_completion INTEGER NOT NULL DEFAULT 0,
    artifact_owner_client_id TEXT REFERENCES clients(id),
    artifact_origin TEXT NOT NULL DEFAULT 'win',
    artifact_rarity_override TEXT NOT NULL DEFAULT '',
    artifact_rarity TEXT NOT NULL DEFAULT '',
    created_at INTEGER NOT NULL,
    completed_at INTEGER,
    winner_client_id TEXT REFERENCES clients(id),
    winner_worker_client_id TEXT REFERENCES clients(id),
    winner_beacon_path TEXT NOT NULL DEFAULT '',
    winner_beacon_boosted_path TEXT NOT NULL DEFAULT '',
    winner_beacon_round INTEGER NOT NULL DEFAULT 0,
    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);

-- Current collectible ownership is separate from historical winner provenance.
-- Transfers keep the original winner fields untouched and are audit logged here.
CREATE TABLE IF NOT EXISTS artifact_transfers (
    id TEXT PRIMARY KEY,
    task_id TEXT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
    from_client_id TEXT REFERENCES clients(id) ON DELETE SET NULL,
    to_client_id TEXT NOT NULL REFERENCES clients(id),
    reason TEXT NOT NULL DEFAULT '',
    created_at INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS artifact_transfers_task_idx ON artifact_transfers(task_id, created_at DESC);
CREATE INDEX IF NOT EXISTS artifact_transfers_to_idx ON artifact_transfers(to_client_id, created_at DESC);

-- 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);

-- Successful external image-model calls are logged with the token usage
-- returned by the provider. Costs are local estimates based on pinned public
-- standard token rates. Billing reconciliation still belongs to the provider.
CREATE TABLE IF NOT EXISTS artifact_api_usage (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    created_at INTEGER NOT NULL,
    task_id TEXT REFERENCES tasks(id) ON DELETE SET NULL,
    kind TEXT NOT NULL CHECK (kind IN ('character_anchor','artifact')),
    provider TEXT NOT NULL,
    model TEXT NOT NULL,
    endpoint TEXT NOT NULL,
    size TEXT NOT NULL,
    quality TEXT NOT NULL,
    request_id TEXT,
    input_tokens INTEGER NOT NULL DEFAULT 0,
    input_text_tokens INTEGER NOT NULL DEFAULT 0,
    input_image_tokens INTEGER NOT NULL DEFAULT 0,
    output_tokens INTEGER NOT NULL DEFAULT 0,
    total_tokens INTEGER NOT NULL DEFAULT 0,
    estimated_cost_usd REAL,
    pricing_basis TEXT NOT NULL DEFAULT '',
    meta_json TEXT NOT NULL DEFAULT '{}'
);
CREATE INDEX IF NOT EXISTS artifact_api_usage_created_idx ON artifact_api_usage(created_at DESC);
CREATE INDEX IF NOT EXISTS artifact_api_usage_kind_created_idx ON artifact_api_usage(kind, created_at DESC);
CREATE INDEX IF NOT EXISTS artifact_api_usage_task_idx ON artifact_api_usage(task_id);

-- Publicly auditable Beacon Hunt draws. Each row records the externally
-- sourced drand reveal used for the weighted path/draw decision.
CREATE TABLE IF NOT EXISTS beacon_draws (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    task_id TEXT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
    window_end INTEGER NOT NULL,
    beacon_id TEXT NOT NULL,
    beacon_round INTEGER NOT NULL,
    randomness TEXT NOT NULL,
    signature TEXT NOT NULL DEFAULT '',
    boosted_path TEXT NOT NULL,
    ticket_count INTEGER NOT NULL,
    selected_count INTEGER NOT NULL,
    created_at INTEGER NOT NULL,
    UNIQUE(task_id, window_end)
);
CREATE INDEX IF NOT EXISTS beacon_draws_task_idx ON beacon_draws(task_id, window_end DESC);


-- A hosted worker uses its own cryptographic identity/presence, while prizes can
-- be delegated to a durable customer-owned reward identity. The worker remains
-- auditable on the completed task through winner_worker_client_id.
CREATE TABLE IF NOT EXISTS identity_delegations (
    worker_client_id TEXT PRIMARY KEY REFERENCES clients(id) ON DELETE CASCADE,
    owner_client_id TEXT NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
    created_at INTEGER NOT NULL,
    updated_at INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS identity_delegations_owner_idx ON identity_delegations(owner_client_id);

-- One-shot pairing codes let a logged-in owner prove control of the reward
-- identity to Customer Service without sharing the P-256 private key. Only the
-- SHA-256 token hash is stored and codes expire quickly.
CREATE TABLE IF NOT EXISTS customer_link_tokens (
    token_hash TEXT PRIMARY KEY,
    client_id TEXT NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
    expires_at INTEGER NOT NULL,
    created_at INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS customer_link_tokens_exp_idx ON customer_link_tokens(expires_at);

-- Durable outbox for hosted-worker positive-tip rewards. A "positive tip" is
-- a signed guess that improves that worker's personal best score. Delivery to
-- Customer Service is retried and the event id is idempotent there.
CREATE TABLE IF NOT EXISTS hosted_credit_events (
    event_id TEXT PRIMARY KEY,
    worker_client_id TEXT NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
    reward_client_id TEXT NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
    task_id TEXT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
    seq INTEGER NOT NULL,
    score REAL NOT NULL,
    created_at INTEGER NOT NULL,
    delivered_at INTEGER,
    attempts INTEGER NOT NULL DEFAULT 0,
    last_error TEXT NOT NULL DEFAULT ''
);
CREATE INDEX IF NOT EXISTS hosted_credit_events_pending_idx ON hosted_credit_events(delivered_at,created_at);

-- Neural Place: a shared, r/place-inspired canvas funded exclusively by
-- genuine Hunt score improvements. Point balances use milli-points so tiny
-- score increases accumulate instead of being rounded away.
CREATE TABLE IF NOT EXISTS place_wallets (
    client_id TEXT PRIMARY KEY REFERENCES clients(id) ON DELETE CASCADE,
    balance_milli INTEGER NOT NULL DEFAULT 0,
    earned_milli INTEGER NOT NULL DEFAULT 0,
    spent_milli INTEGER NOT NULL DEFAULT 0,
    placements INTEGER NOT NULL DEFAULT 0,
    updated_at INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS place_state (
    id INTEGER PRIMARY KEY CHECK (id = 1),
    revision INTEGER NOT NULL DEFAULT 0,
    updated_at INTEGER NOT NULL DEFAULT 0
);
INSERT OR IGNORE INTO place_state(id,revision,updated_at) VALUES(1,0,0);

CREATE TABLE IF NOT EXISTS place_pixels (
    x INTEGER NOT NULL,
    y INTEGER NOT NULL,
    color_index INTEGER NOT NULL,
    client_id TEXT NOT NULL,
    placed_at INTEGER NOT NULL,
    revision INTEGER NOT NULL,
    PRIMARY KEY (x,y)
);
CREATE INDEX IF NOT EXISTS place_pixels_revision_idx ON place_pixels(revision);
CREATE INDEX IF NOT EXISTS place_pixels_client_idx ON place_pixels(client_id);

CREATE TABLE IF NOT EXISTS place_pixel_events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    x INTEGER NOT NULL,
    y INTEGER NOT NULL,
    color_index INTEGER NOT NULL,
    client_id TEXT NOT NULL,
    cost_milli INTEGER NOT NULL,
    created_at INTEGER NOT NULL,
    revision INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS place_pixel_events_created_idx ON place_pixel_events(created_at DESC);
CREATE INDEX IF NOT EXISTS place_pixel_events_client_idx ON place_pixel_events(client_id,created_at DESC);

CREATE TABLE IF NOT EXISTS place_progress_events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    owner_client_id TEXT NOT NULL,
    source_client_id TEXT NOT NULL,
    task_id TEXT NOT NULL,
    old_score REAL NOT NULL,
    new_score REAL NOT NULL,
    points_milli INTEGER NOT NULL,
    created_at INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS place_progress_owner_idx ON place_progress_events(owner_client_id,created_at DESC);
CREATE INDEX IF NOT EXISTS place_progress_source_idx ON place_progress_events(source_client_id,created_at DESC);

-- V4.5 Place bonus economy: lottery draw rewards, active-time rewards and
-- manual admin grants. event_key makes externally triggered rewards idempotent.
CREATE TABLE IF NOT EXISTS place_bonus_events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    event_key TEXT NOT NULL UNIQUE,
    owner_client_id TEXT NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
    source_client_id TEXT NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
    task_id TEXT NOT NULL DEFAULT '',
    kind TEXT NOT NULL,
    points_milli INTEGER NOT NULL,
    multiplier REAL NOT NULL DEFAULT 1,
    units INTEGER NOT NULL DEFAULT 1,
    detail TEXT NOT NULL DEFAULT '',
    created_at INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS place_bonus_owner_idx ON place_bonus_events(owner_client_id,created_at DESC);
CREATE INDEX IF NOT EXISTS place_bonus_source_idx ON place_bonus_events(source_client_id,created_at DESC);
CREATE INDEX IF NOT EXISTS place_bonus_kind_idx ON place_bonus_events(kind,created_at DESC);