CREATE TABLE record (
    uri TEXT PRIMARY KEY,
    cid TEXT UNIQUE NOT NULL,
    collection TEXT NOT NULL,
    creator_did TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL,
    indexed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    data JSONB NOT NULL,
    target_did TEXT,
    ref_cids TEXT[] NOT NULL DEFAULT '{}'
);

INSERT INTO record (uri, cid, collection, creator_did, created_at, indexed_at, data, target_did, ref_cids)
SELECT uri, cid, 'systems.gmstn.development.lattice', creator_did, created_at, indexed_at, data, NULL, '{}'
FROM lattice;

INSERT INTO record (uri, cid, collection, creator_did, created_at, indexed_at, data, target_did, ref_cids)
SELECT uri, cid, 'systems.gmstn.development.shard', creator_did, created_at, indexed_at, data, NULL, '{}'
FROM shard;

INSERT INTO record (uri, cid, collection, creator_did, created_at, indexed_at, data, target_did, ref_cids)
SELECT uri, cid, 'systems.gmstn.development.channel', creator_did, created_at, indexed_at, COALESCE(data, '{}'), NULL, '{}'
FROM channel WHERE uri IS NOT NULL;

INSERT INTO record (uri, cid, collection, creator_did, created_at, indexed_at, data, target_did, ref_cids)
SELECT uri, cid, 'systems.gmstn.development.channel.invite', creator_did, created_at, indexed_at, COALESCE(data, '{}'), recipient_did, ARRAY[channel]
FROM channel_invite WHERE uri IS NOT NULL;

INSERT INTO record (uri, cid, collection, creator_did, created_at, indexed_at, data, target_did, ref_cids)
SELECT uri, cid, 'systems.gmstn.development.channel.membership', recipient_did, created_at, indexed_at, data, recipient_did, ARRAY[channel, invite]
FROM channel_membership;

CREATE INDEX idx_record_collection_creator ON record(collection, creator_did, indexed_at DESC);
CREATE INDEX idx_record_collection_target ON record(collection, target_did, indexed_at DESC) WHERE target_did IS NOT NULL;
CREATE INDEX idx_record_cid ON record(cid);
CREATE INDEX idx_record_ref_cids ON record USING GIN(ref_cids);

DROP TABLE channel_membership;
DROP TABLE channel_invite;
DROP TABLE channel;
DROP TABLE shard;
DROP TABLE lattice;