-- PM-26: appview read support.
--
-- (a) record_json: the raw ATProto record for each content row, so read endpoints
--     can populate the required `record` field on thingView/modelView/partView
--     directly from SQLite. This also gives the write path (PM-28) read-your-own-
--     write capability: Hydrant exposes no public single-record write API into
--     its fjall store, so the record must live in SQLite to be readable
--     immediately after an eager write projection.
-- (b) profiles: denormalized space.polymodel.actor.profile projection.
-- (c) identities: DID -> handle map kept fresh by identity events, so handle
--     changes are caught for profile/actor hydration. The projection previously
--     filtered out non-record events, which meant handle changes were never
--     reflected (records do not carry the actor's current handle).

ALTER TABLE things ADD COLUMN record_json TEXT;
ALTER TABLE models ADD COLUMN record_json TEXT;
ALTER TABLE parts ADD COLUMN record_json TEXT;

-- Polymodel actor profile (singleton record, rkey = "self"). Denormalized fields
-- for the profileView union variant plus the raw record for the required `record`
-- field. Handle is not stored here: it lives in `identities` (identity-level, not
-- part of the profile record) 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
);
