CREATE TABLE IF NOT EXISTS account (
    did TEXT PRIMARY KEY,
    handle TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS lattice (
    uri TEXT PRIMARY KEY,
    cid TEXT UNIQUE NOT NULL,
    creator_did TEXT NOT NULL REFERENCES account(did),
    description TEXT,
    created_at TIMESTAMPTZ NOT NULL,
    indexed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    data JSONB NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_lattice_creator_indexed ON lattice(creator_did, indexed_at DESC);

CREATE TABLE IF NOT EXISTS shard (
    uri TEXT PRIMARY KEY,
    cid TEXT UNIQUE NOT NULL,
    creator_did TEXT NOT NULL REFERENCES account(did),
    description TEXT,
    created_at TIMESTAMPTZ NOT NULL,
    indexed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    data JSONB NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_shard_creator_indexed ON shard(creator_did, indexed_at DESC);

CREATE TABLE IF NOT EXISTS channel (
    cid TEXT PRIMARY KEY,
    uri TEXT UNIQUE,
    creator_did TEXT REFERENCES account(did),
    name TEXT,
    topic TEXT,
    created_at TIMESTAMPTZ,
    indexed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    data JSONB
);

CREATE INDEX IF NOT EXISTS idx_channel_creator_indexed ON channel(creator_did, indexed_at DESC);

CREATE TABLE IF NOT EXISTS channel_invite (
    cid TEXT PRIMARY KEY,
    uri TEXT UNIQUE,
    creator_did TEXT REFERENCES account(did),
    channel TEXT NOT NULL REFERENCES channel(cid),
    recipient_did TEXT REFERENCES account(did),
    created_at TIMESTAMPTZ,
    indexed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    data JSONB
);

CREATE INDEX IF NOT EXISTS idx_invite_recipient_indexed ON channel_invite(recipient_did, indexed_at DESC);

CREATE TABLE IF NOT EXISTS channel_membership (
    cid TEXT PRIMARY KEY,
    uri TEXT UNIQUE NOT NULL,
    channel TEXT NOT NULL REFERENCES channel(cid),
    invite TEXT NOT NULL REFERENCES channel_invite(cid),
    recipient_did TEXT REFERENCES account(did),
    state TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL,
    indexed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ,
    data JSONB NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_membership_recipient_indexed ON channel_membership(recipient_did, indexed_at DESC);