-- Polymodel initial SQLite projection schema (consolidated baseline).
--
-- Single non-destructive baseline combining the former 001–005 migrations into
-- their final form: record_json on content tables, persisted CIDs on social
-- records, owner-scoped upload staging, and read-your-writes pending-ops. Fresh
-- databases get the complete schema in one step, and future schema changes add
-- new migration files rather than rewriting tables, so projected data is never
-- destroyed by a migration.
--
-- WAL mode is intentionally NOT set here: sqlx runs migrations inside a
-- transaction and SQLite silently ignores journal_mode changes within one. WAL
-- is enabled programmatically on the connection pool (src/indexing/db.rs).

-- Projection state (durable cursor for crash recovery)
CREATE TABLE projection_state (
    key TEXT PRIMARY KEY,
    value TEXT NOT NULL,
    updated_at INTEGER NOT NULL
);

-- Content records (space.polymodel.library.*). record_json holds the raw ATProto
-- record so read endpoints and eager read-your-own-writes resolve from SQLite.
CREATE TABLE things (
    did TEXT NOT NULL,
    rkey TEXT NOT NULL,
    uri TEXT NOT NULL UNIQUE,
    cid TEXT NOT NULL,
    name TEXT NOT NULL,
    summary TEXT,
    license TEXT NOT NULL,
    tags_json TEXT,
    tags_text TEXT,
    instructions_text TEXT,
    cover_json TEXT,
    derived_from_uri TEXT,
    record_json TEXT,
    created_at INTEGER NOT NULL,
    indexed_at INTEGER NOT NULL,
    PRIMARY KEY (did, rkey)
);
CREATE INDEX idx_things_did ON things(did, created_at DESC);
CREATE INDEX idx_things_created ON things(created_at DESC);

CREATE TABLE models (
    did TEXT NOT NULL,
    rkey TEXT NOT NULL,
    uri TEXT NOT NULL UNIQUE,
    cid TEXT NOT NULL,
    name TEXT NOT NULL,
    summary TEXT,
    record_json TEXT,
    created_at INTEGER NOT NULL,
    indexed_at INTEGER NOT NULL,
    PRIMARY KEY (did, rkey)
);

CREATE TABLE parts (
    did TEXT NOT NULL,
    rkey TEXT NOT NULL,
    uri TEXT NOT NULL UNIQUE,
    cid TEXT NOT NULL,
    name TEXT NOT NULL,
    format TEXT,
    file_json TEXT NOT NULL,
    record_json TEXT,
    created_at INTEGER NOT NULL,
    indexed_at INTEGER NOT NULL,
    PRIMARY KEY (did, rkey)
);

-- Junction tables (relationship lookups + ordering)
CREATE TABLE thing_models (
    thing_uri TEXT NOT NULL,
    model_uri TEXT NOT NULL,
    position INTEGER NOT NULL,
    PRIMARY KEY (thing_uri, model_uri)
);
CREATE INDEX idx_thing_models_model ON thing_models(model_uri);
CREATE INDEX idx_thing_models_thing_pos ON thing_models(thing_uri, position);

CREATE TABLE model_parts (
    model_uri TEXT NOT NULL,
    part_uri TEXT NOT NULL,
    position INTEGER NOT NULL,
    PRIMARY KEY (model_uri, part_uri)
);
CREATE INDEX idx_model_parts_part ON model_parts(part_uri);
CREATE INDEX idx_model_parts_model_pos ON model_parts(model_uri, position);

-- Social record projections (space.polymodel.graph.*), with persisted record CIDs.
CREATE TABLE likes (
    did TEXT NOT NULL,
    rkey TEXT NOT NULL,
    cid TEXT NOT NULL,
    subject_uri TEXT NOT NULL,
    created_at INTEGER NOT NULL,
    PRIMARY KEY (did, rkey),
    UNIQUE (did, subject_uri)
);
CREATE INDEX idx_likes_subject ON likes(subject_uri);

CREATE TABLE saves (
    did TEXT NOT NULL,
    rkey TEXT NOT NULL,
    cid TEXT NOT NULL,
    subject_uri TEXT NOT NULL,
    note TEXT,
    created_at INTEGER NOT NULL,
    PRIMARY KEY (did, rkey),
    UNIQUE (did, subject_uri)
);
CREATE INDEX idx_saves_subject ON saves(subject_uri);

CREATE TABLE tags (
    did TEXT NOT NULL,
    rkey TEXT NOT NULL,
    cid TEXT NOT NULL,
    subject_uri TEXT NOT NULL,
    tag TEXT NOT NULL,
    created_at INTEGER NOT NULL,
    PRIMARY KEY (did, rkey)
);
CREATE INDEX idx_tags_subject ON tags(subject_uri);
CREATE INDEX idx_tags_tag ON tags(tag);

CREATE TABLE listitems (
    did TEXT NOT NULL,
    rkey TEXT NOT NULL,
    cid TEXT NOT NULL,
    list_uri TEXT NOT NULL,
    subject_uri TEXT NOT NULL,
    created_at INTEGER NOT NULL,
    PRIMARY KEY (did, rkey)
);
CREATE INDEX idx_listitems_list ON listitems(list_uri, created_at);

-- Denormalized engagement counts
CREATE TABLE content_stats (
    uri TEXT PRIMARY KEY,
    like_count INTEGER NOT NULL DEFAULT 0,
    save_count INTEGER NOT NULL DEFAULT 0,
    tag_count INTEGER NOT NULL DEFAULT 0
);

-- Full-text search (FTS5, external content) over things
CREATE VIRTUAL TABLE things_fts USING fts5(
    name, summary, tags_text, instructions_text,
    content=things, content_rowid=rowid,
    tokenize='porter unicode61'
);

CREATE TRIGGER things_ai AFTER INSERT ON things BEGIN
    INSERT INTO things_fts(rowid, name, summary, tags_text, instructions_text)
    VALUES (new.rowid, new.name, new.summary, new.tags_text, new.instructions_text);
END;
CREATE TRIGGER things_ad AFTER DELETE ON things BEGIN
    INSERT INTO things_fts(things_fts, rowid, name, summary, tags_text, instructions_text)
    VALUES ('delete', old.rowid, old.name, old.summary, old.tags_text, old.instructions_text);
END;
CREATE TRIGGER things_au AFTER UPDATE ON things BEGIN
    INSERT INTO things_fts(things_fts, rowid, name, summary, tags_text, instructions_text)
    VALUES ('delete', old.rowid, old.name, old.summary, old.tags_text, old.instructions_text);
    INSERT INTO things_fts(rowid, name, summary, tags_text, instructions_text)
    VALUES (new.rowid, new.name, new.summary, new.tags_text, new.instructions_text);
END;

-- Polymodel actor profile (singleton record, rkey = "self"). Handle is not stored
-- here: it lives in `identities` (identity-level) so it stays current across
-- handle changes.
CREATE TABLE profiles (
    did TEXT PRIMARY KEY,
    display_name TEXT,
    description TEXT,
    avatar_json TEXT,
    default_license TEXT,
    pronouns TEXT,
    printers_json TEXT,
    links_json TEXT,
    record_json TEXT NOT NULL,
    indexed_at INTEGER NOT NULL
);

-- DID -> handle, refreshed by identity events. Joined by profile/actor hydration.
CREATE TABLE identities (
    did TEXT PRIMARY KEY,
    handle TEXT,
    updated_at INTEGER NOT NULL
);

-- OAuth session persistence (DB-backed ClientAuthStore for Jacquard 0.12 OAuth).
-- Structured columns power key enumeration / by-DID selection / deletes without
-- deserializing payload; payload holds the canonical ClientSessionData JSON.
CREATE TABLE oauth_sessions (
    account_did TEXT NOT NULL,
    session_id TEXT NOT NULL,
    host_url TEXT NOT NULL,
    authserver_url TEXT NOT NULL,
    token_endpoint TEXT NOT NULL,
    revocation_endpoint TEXT,
    created_at INTEGER NOT NULL,
    updated_at INTEGER NOT NULL,
    payload TEXT NOT NULL,
    PRIMARY KEY (account_did, session_id)
);
CREATE INDEX idx_oauth_sessions_did ON oauth_sessions(account_did);

-- Pending OAuth authorization requests (PAR state round-trip).
CREATE TABLE oauth_auth_requests (
    state TEXT NOT NULL,
    authserver_url TEXT NOT NULL,
    account_did TEXT,
    request_uri TEXT NOT NULL,
    token_endpoint TEXT NOT NULL,
    revocation_endpoint TEXT,
    created_at INTEGER NOT NULL,
    payload TEXT NOT NULL,
    PRIMARY KEY (state)
);
CREATE INDEX idx_oauth_auth_req_did ON oauth_auth_requests(account_did);

-- Owner-scoped upload staging for raw geometry bytes (publish flow).
CREATE TABLE upload_staging (
    owner_did TEXT NOT NULL,
    upload_id TEXT NOT NULL,
    sha256 BLOB NOT NULL,
    mime_type TEXT NOT NULL,
    size INTEGER NOT NULL,
    filename TEXT,
    status TEXT NOT NULL,
    file_json TEXT NOT NULL,
    chunks_json TEXT NOT NULL,
    created_at INTEGER NOT NULL,
    updated_at INTEGER NOT NULL,
    PRIMARY KEY (owner_did, upload_id)
);
CREATE INDEX idx_upload_staging_owner_updated
    ON upload_staging(owner_did, updated_at DESC);

-- Read-your-writes pending-ops tracking. Collection-agnostic; keyed by record
-- URI. Cleared by the firehose record-event projection once the Hydrant #commit
-- redelivers the event.
CREATE TABLE pending_writes (
    uri TEXT PRIMARY KEY,
    cid TEXT NOT NULL,
    value TEXT NOT NULL,
    written_at INTEGER NOT NULL
);

CREATE TABLE pending_deletes (
    uri TEXT PRIMARY KEY,
    deleted_at INTEGER NOT NULL
);