-- 001_baseline.sql
-- Consolidated baseline schema replacing migrations 001–008.

CREATE TABLE users (
    id  TEXT PRIMARY KEY,
    did TEXT UNIQUE NOT NULL
);

CREATE TABLE atproto_sessions (
    user_id        TEXT PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    did            TEXT NOT NULL,
    pds_url        TEXT NOT NULL,
    access_token   TEXT NOT NULL,
    refresh_token  TEXT NOT NULL,
    dpop_key_jwk   TEXT NOT NULL,
    dpop_nonce     TEXT DEFAULT '',
    token_endpoint TEXT NOT NULL,
    expires_at     DATETIME NOT NULL,
    updated_at     DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE invites (
    id             TEXT PRIMARY KEY,
    document_rkey  TEXT NOT NULL,
    token          TEXT NOT NULL UNIQUE,
    created_by_did TEXT NOT NULL,
    created_at     DATETIME DEFAULT CURRENT_TIMESTAMP,
    expires_at     DATETIME NOT NULL
);

CREATE INDEX idx_invites_document ON invites(document_rkey);
CREATE INDEX idx_invites_token    ON invites(token);

CREATE TABLE doc_steps (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    doc_rkey   TEXT    NOT NULL,
    version    INTEGER NOT NULL,
    step_json  TEXT    NOT NULL,
    client_id  TEXT    NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(doc_rkey, version)
);

CREATE INDEX idx_doc_steps_rkey_version ON doc_steps(doc_rkey, version);

CREATE TABLE collaborations (
    collaborator_did TEXT NOT NULL,
    owner_did        TEXT NOT NULL,
    document_rkey    TEXT NOT NULL,
    added_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (collaborator_did, owner_did, document_rkey)
);

CREATE INDEX idx_collaborations_did_added ON collaborations(collaborator_did, added_at DESC);