CREATE TABLE IF NOT EXISTS posts (
    id         UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    thread_id  UUID        NOT NULL REFERENCES threads(id) ON DELETE CASCADE,
    author_id  UUID        NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    content    TEXT        NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_posts_thread
    ON posts (thread_id, created_at ASC);

CREATE INDEX IF NOT EXISTS idx_posts_author
    ON posts (author_id);

-- Update reply_count and last_post_at on thread, post_count on category and user
CREATE OR REPLACE FUNCTION fn_post_insert()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE threads
    SET reply_count  = reply_count + 1,
        last_post_at = NEW.created_at
    WHERE id = NEW.thread_id;

    UPDATE categories
    SET post_count = post_count + 1
    WHERE id = (SELECT category_id FROM threads WHERE id = NEW.thread_id);

    UPDATE users
    SET post_count = post_count + 1
    WHERE id = NEW.author_id;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER trg_post_insert
AFTER INSERT ON posts
FOR EACH ROW EXECUTE FUNCTION fn_post_insert();

-- Update thread_count on category when threads are created/deleted
CREATE OR REPLACE FUNCTION fn_thread_count()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE categories SET thread_count = thread_count + 1 WHERE id = NEW.category_id;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE categories SET thread_count = thread_count - 1 WHERE id = OLD.category_id;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER trg_thread_count
AFTER INSERT OR DELETE ON threads
FOR EACH ROW EXECUTE FUNCTION fn_thread_count();
