-- Polymodel SQLite projection schema.
-- Derived views projected from Hydrant ATProto events for space.polymodel.* collections.
--
-- NOTE: 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 (see 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
);

-- Derived content views
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,
    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,
    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,
    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
CREATE TABLE likes (
    did TEXT NOT NULL,
    rkey 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,
    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,
    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,
    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 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)
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;

-- OAuth session persistence (DB-backed ClientAuthStore; used in Task 3)
CREATE TABLE oauth_sessions (
    did TEXT NOT NULL,
    session_id TEXT NOT NULL,
    session_data BLOB NOT NULL,
    created_at INTEGER NOT NULL,
    updated_at INTEGER NOT NULL,
    PRIMARY KEY (did, session_id)
);

CREATE TABLE oauth_auth_requests (
    state TEXT PRIMARY KEY,
    request_data BLOB NOT NULL,
    created_at INTEGER NOT NULL
);
