Q&A forum

CREATE TABLE users (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    username text NOT NULL UNIQUE,
    email text NOT NULL UNIQUE,
    reputation int DEFAULT 0 NOT NULL
);

CREATE TABLE questions (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    author_id uuid NOT NULL,
    title text NOT NULL,
    body text NOT NULL,
    accepted_answer_id bigint,
    created_at timestamptz DEFAULT now() NOT NULL,
    closed_at timestamptz
);
CREATE INDEX questions_author_id_idx ON questions (author_id);
CREATE INDEX questions_accepted_answer_id_idx ON questions (accepted_answer_id);

CREATE TABLE answers (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    question_id bigint NOT NULL,
    author_id uuid NOT NULL,
    body text NOT NULL,
    created_at timestamptz DEFAULT now() NOT NULL
);
CREATE INDEX answers_question_idx ON answers (question_id);
CREATE INDEX answers_author_id_idx ON answers (author_id);

CREATE TABLE comments (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    question_id bigint,
    answer_id bigint,
    author_id uuid NOT NULL,
    body text NOT NULL,
    created_at timestamptz DEFAULT now() NOT NULL,
    CHECK ((question_id is not null) <> (answer_id is not null))
);
CREATE INDEX comments_question_id_idx ON comments (question_id);
CREATE INDEX comments_answer_id_idx ON comments (answer_id);
CREATE INDEX comments_author_id_idx ON comments (author_id);

CREATE TABLE tags (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    name text NOT NULL UNIQUE
);

CREATE TABLE question_tags (
    question_id bigint NOT NULL,
    tag_id uuid NOT NULL,
    PRIMARY KEY (question_id, tag_id)
);
CREATE INDEX question_tags_tag_id_idx ON question_tags (tag_id);

CREATE TABLE votes (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id uuid NOT NULL,
    question_id bigint,
    answer_id bigint,
    value smallint NOT NULL,
    CHECK (value in (-1, 1)),
    CHECK ((question_id is not null) <> (answer_id is not null))
);
CREATE INDEX votes_user_id_idx ON votes (user_id);
CREATE INDEX votes_question_id_idx ON votes (question_id);
CREATE INDEX votes_answer_id_idx ON votes (answer_id);

ALTER TABLE questions ADD FOREIGN KEY (author_id) REFERENCES users (id);

ALTER TABLE answers ADD FOREIGN KEY (question_id) REFERENCES questions (id) ON DELETE CASCADE;

ALTER TABLE answers ADD FOREIGN KEY (author_id) REFERENCES users (id);

ALTER TABLE comments ADD FOREIGN KEY (question_id) REFERENCES questions (id) ON DELETE CASCADE;

ALTER TABLE comments ADD FOREIGN KEY (answer_id) REFERENCES answers (id) ON DELETE CASCADE;

ALTER TABLE comments ADD FOREIGN KEY (author_id) REFERENCES users (id);

ALTER TABLE question_tags ADD FOREIGN KEY (question_id) REFERENCES questions (id) ON DELETE CASCADE;

ALTER TABLE question_tags ADD FOREIGN KEY (tag_id) REFERENCES tags (id) ON DELETE CASCADE;

ALTER TABLE votes ADD FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE;

ALTER TABLE votes ADD FOREIGN KEY (question_id) REFERENCES questions (id) ON DELETE CASCADE;

ALTER TABLE votes ADD FOREIGN KEY (answer_id) REFERENCES answers (id) ON DELETE CASCADE;

ALTER TABLE questions ADD CONSTRAINT questions_accepted_fk FOREIGN KEY (accepted_answer_id) REFERENCES answers (id) ON DELETE SET NULL;