-- Store CIDs on all social record projection tables. Existing rows cannot be
-- backfilled because previous schema versions did not store record CIDs; rebuild
-- these derived tables instead of introducing placeholder CIDs. Hydrant/firehose
-- can replay social records, and eager writes after this migration preserve CIDs.

DROP TABLE likes;
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);

DROP TABLE saves;
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);

DROP TABLE tags;
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);

DROP TABLE listitems;
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);

UPDATE content_stats SET like_count = 0, save_count = 0, tag_count = 0;
