-- Collections group posts/quotes. They carry their own visibility flag —
-- a private collection holds posts that are themselves public on the user's
-- feed, but the collection page is only visible to the owner.
--
-- `item_count` is denormalized so listing pages don't need a join+count.
-- It's kept in sync by collection_items triggers below.
CREATE TABLE IF NOT EXISTS collections (
    id           UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id      UUID         NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title        VARCHAR(120) NOT NULL,
    description  TEXT,
    cover_url    TEXT,
    is_public    BOOLEAN      NOT NULL DEFAULT FALSE,
    item_count   INT          NOT NULL DEFAULT 0,
    created_at   TIMESTAMPTZ  NOT NULL DEFAULT now(),
    updated_at   TIMESTAMPTZ  NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS collections_user_created_idx
    ON collections (user_id, created_at DESC);

-- Public-collection lookups by owner+visibility (for /u/<handle> page).
CREATE INDEX IF NOT EXISTS collections_user_public_idx
    ON collections (user_id, is_public) WHERE is_public = TRUE;

CREATE TABLE IF NOT EXISTS collection_items (
    collection_id  UUID         NOT NULL REFERENCES collections(id) ON DELETE CASCADE,
    post_id        UUID         NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    position       INT          NOT NULL DEFAULT 0,
    added_at       TIMESTAMPTZ  NOT NULL DEFAULT now(),
    PRIMARY KEY (collection_id, post_id)
);

CREATE INDEX IF NOT EXISTS collection_items_position_idx
    ON collection_items (collection_id, position);

-- Keep item_count in sync.
CREATE OR REPLACE FUNCTION sync_collection_item_count() RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE collections SET item_count = item_count + 1, updated_at = now()
            WHERE id = NEW.collection_id;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE collections SET item_count = GREATEST(item_count - 1, 0), updated_at = now()
            WHERE id = OLD.collection_id;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS collection_items_count_trg ON collection_items;
CREATE TRIGGER collection_items_count_trg
    AFTER INSERT OR DELETE ON collection_items
    FOR EACH ROW EXECUTE FUNCTION sync_collection_item_count();