CREATE TABLE documents (
    id                   INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id              INTEGER NOT NULL REFERENCES users (id) ON DELETE CASCADE,

    -- KOSync key as sent by KOReader (usually a file path or hash)
    kosync_document_id   TEXT    NOT NULL,

    -- Metadata from KOReader PR #15306 optional payload
    title                TEXT,
    authors              TEXT,
    filename             TEXT,

    -- Book identifiers for Popfeed records (populated via association UI)
    isbn10               TEXT,
    isbn13               TEXT,
    asin                 TEXT,

    -- Percentage read (0–100), mirrored from the latest KOSync progress call
    percent              REAL,

    -- ATProto record keys written to the user's PDS
    -- social.popfeed.book.progress rkey
    popfeed_progress_rkey    TEXT,
    -- social.popfeed.feed.listItem at-uri (full uri, e.g. at://did/lex/rkey)
    popfeed_list_item_uri    TEXT,

    -- Association status: 'unassociated' | 'associated' | 'ignored'
    association_status   TEXT    NOT NULL DEFAULT 'unassociated',

    -- Set after we send the "new unassociated document" notification email
    notified_at          INTEGER,

    created_at           INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at           INTEGER NOT NULL DEFAULT (unixepoch()),

    UNIQUE (user_id, kosync_document_id)
);

CREATE INDEX documents_user_id ON documents (user_id);
CREATE INDEX documents_association_status ON documents (association_status);