create.sql

-- ==========================================================
-- DATABASE CLEANUP
-- ==========================================================

DROP TABLE IF EXISTS 
    content_owner, 
    accepted_answer,
    profile_view,
    admin_create_moderator,
    admin_create_admin,
    admin_manage_reader,
    admin_manage_tag,
    reader_tag_follow,
    reader_question_follow,
    content_tag,
    new_badge_notification,
    new_vote_notification,
    new_answer_notification,
    notification,
    reader_badge,
    vote,
    comment,
    answer,
    question,
    content,
    tag,
    badge,
    administrator,
    moderator,
    reader,
    oauthapi
CASCADE;

DROP TYPE IF EXISTS VoteType CASCADE;
DROP TYPE IF EXISTS ActionType CASCADE;

-- ==========================================================
-- DOMAIN DEFINITIONS
-- ==========================================================

CREATE TYPE VoteType AS ENUM ('-1', '+1');
CREATE TYPE ActionType AS ENUM ('create', 'delete', 'block', 'unblock');

-- ==========================================================
-- TABLE DEFINITIONS
-- ==========================================================

-- R01: oauthapi
CREATE TABLE oauthapi (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    provider_name TEXT NOT NULL UNIQUE
);

-- R02: reader
CREATE TABLE reader (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL,
    profile_picture TEXT,
    registration_date DATE NOT NULL CHECK (registration_date <= CURRENT_DATE),
    is_banned BOOLEAN NOT NULL DEFAULT FALSE,
    id_oauth BIGINT REFERENCES oauthapi(id)
);

-- R03: content
CREATE TABLE content (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    creation_date DATE NOT NULL CHECK (creation_date <= CURRENT_DATE),
    last_edited_date DATE NOT NULL CHECK (last_edited_date <= CURRENT_DATE AND last_edited_date >= creation_date),
    content_body TEXT NOT NULL,
    is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
    id_author BIGINT NOT NULL REFERENCES reader(id),
    id_last_editor BIGINT NOT NULL REFERENCES reader(id)
);

-- R04: question
CREATE TABLE question (
    id BIGINT PRIMARY KEY REFERENCES content(id),
    title TEXT NOT NULL
);

-- R05: answer
CREATE TABLE answer (
    id BIGINT PRIMARY KEY REFERENCES content(id),
    id_question BIGINT NOT NULL REFERENCES question(id)
);

-- R06: comment
CREATE TABLE comment (
    id BIGINT PRIMARY KEY REFERENCES content(id),
    parent_comment_id BIGINT REFERENCES comment(id),
    parent_answer_id BIGINT REFERENCES answer(id),
    CHECK (
        (parent_comment_id IS NOT NULL AND parent_answer_id IS NULL) OR
        (parent_comment_id IS NULL AND parent_answer_id IS NOT NULL)
    )
);

-- R07: tag
CREATE TABLE tag (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL UNIQUE
);

-- R08: vote
CREATE TABLE vote (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    value VoteType NOT NULL,
    date DATE NOT NULL CHECK (date <= CURRENT_DATE),
    id_voter BIGINT NOT NULL REFERENCES reader(id),
    id_content BIGINT NOT NULL REFERENCES content(id)
);

-- R09: badge
CREATE TABLE badge (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    description TEXT NOT NULL,
    icon TEXT NOT NULL
);

-- R10: reader_badge
CREATE TABLE reader_badge (
    id_reader BIGINT NOT NULL REFERENCES reader(id),
    id_badge BIGINT NOT NULL REFERENCES badge(id),
    PRIMARY KEY (id_reader, id_badge)
);

-- R11: notification
CREATE TABLE notification (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    date TIMESTAMP NOT NULL CHECK (date <= NOW()),
    is_read BOOLEAN NOT NULL,
    id_reader BIGINT NOT NULL REFERENCES reader(id)
);

-- R12: new_answer_notification
CREATE TABLE new_answer_notification (
    id BIGINT PRIMARY KEY REFERENCES notification(id),
    id_answer BIGINT NOT NULL REFERENCES answer(id)
);

-- R13: new_vote_notification
CREATE TABLE new_vote_notification (
    id BIGINT PRIMARY KEY REFERENCES notification(id),
    id_vote BIGINT NOT NULL REFERENCES vote(id)
);

-- R14: new_badge_notification
CREATE TABLE new_badge_notification (
    id BIGINT PRIMARY KEY REFERENCES notification(id),
    id_badge BIGINT NOT NULL REFERENCES badge(id)
);

-- R15: moderator
CREATE TABLE moderator (
    id_reader BIGINT PRIMARY KEY REFERENCES reader(id),
    moderator_since DATE NOT NULL CHECK (moderator_since <= CURRENT_DATE)
);

-- R16: administrator
CREATE TABLE administrator (
    id_reader BIGINT PRIMARY KEY REFERENCES reader(id),
    admin_since DATE NOT NULL CHECK (admin_since <= CURRENT_DATE)
);

-- R17: reader_question_follow
CREATE TABLE reader_question_follow (
    id_reader BIGINT NOT NULL REFERENCES reader(id),
    id_question BIGINT NOT NULL REFERENCES question(id),
    PRIMARY KEY (id_reader, id_question)
);

-- R18: reader_tag_follow
CREATE TABLE reader_tag_follow (
    id_reader BIGINT NOT NULL REFERENCES reader(id),
    id_tag BIGINT NOT NULL REFERENCES tag(id),
    PRIMARY KEY (id_reader, id_tag)
);

-- R19: content_tag
CREATE TABLE content_tag (
    id_content BIGINT NOT NULL REFERENCES content(id),
    id_tag BIGINT NOT NULL REFERENCES tag(id),
    PRIMARY KEY (id_content, id_tag)
);

-- R20: admin_manage_reader
CREATE TABLE admin_manage_reader (
    id_admin BIGINT NOT NULL REFERENCES administrator(id_reader),
    id_reader BIGINT NOT NULL REFERENCES reader(id),
    action_type ActionType NOT NULL,
    action_date DATE NOT NULL CHECK (action_date <= CURRENT_DATE),
    notes TEXT,
    PRIMARY KEY (id_admin, id_reader, action_date)
);

-- R21: admin_manage_tag
CREATE TABLE admin_manage_tag (
    id_admin BIGINT NOT NULL REFERENCES administrator(id_reader),
    id_tag BIGINT NOT NULL REFERENCES tag(id),
    action_type ActionType NOT NULL,
    action_date DATE NOT NULL CHECK (action_date <= CURRENT_DATE),
    PRIMARY KEY (id_admin, id_tag, action_date)
);

-- R22: accepted_answer
CREATE TABLE accepted_answer (
    question_id BIGINT PRIMARY KEY REFERENCES question(id),
    answer_id BIGINT UNIQUE REFERENCES answer(id)
);

-- R23: profile_view
CREATE TABLE profile_view (
    viewer_id BIGINT NOT NULL REFERENCES reader(id),
    viewed_id BIGINT NOT NULL REFERENCES reader(id),
    view_date DATE NOT NULL,
    PRIMARY KEY (viewer_id, viewed_id, view_date)
);

-- R24: admin_create_moderator
CREATE TABLE admin_create_moderator (
    id_admin BIGINT NOT NULL REFERENCES administrator(id_reader),
    id_moderator BIGINT NOT NULL REFERENCES moderator(id_reader),
    creation_date DATE NOT NULL CHECK (creation_date <= CURRENT_DATE),
    notes TEXT,
    PRIMARY KEY (id_admin, id_moderator)
);

-- R25: admin_create_admin
CREATE TABLE admin_create_admin (
    id_admin BIGINT NOT NULL REFERENCES administrator(id_reader),
    id_new_admin BIGINT NOT NULL REFERENCES administrator(id_reader),
    creation_date DATE NOT NULL CHECK (creation_date <= CURRENT_DATE),
    notes TEXT,
    PRIMARY KEY (id_admin, id_new_admin)
);

-- ==========================================================
-- INDEXES
-- ==========================================================

--IDX01
CREATE INDEX idx_answer_question ON answer(id_question);

--IDX02-A
CREATE INDEX idx_comment_parent_comment ON comment(parent_comment_id);

--IDX02-B
CREATE INDEX idx_comment_parent_answer ON comment(parent_answer_id);

--IDX03
CREATE INDEX idx_content_tag_tag ON content_tag(id_tag);

--IDX04
ALTER TABLE question
    ADD COLUMN IF NOT EXISTS document_tsv tsvector;

CREATE OR REPLACE FUNCTION fn_update_question_tsv()
RETURNS trigger AS $$
DECLARE
    v_content_body TEXT;
BEGIN
    SELECT content_body INTO v_content_body
    FROM content
    WHERE id = NEW.id;

    NEW.document_tsv :=
        setweight(to_tsvector('english', COALESCE(NEW.title, '')), 'A') ||
        setweight(to_tsvector('english', COALESCE(v_content_body, '')), 'B');

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_question_tsv
BEFORE INSERT OR UPDATE ON question
FOR EACH ROW
EXECUTE FUNCTION fn_update_question_tsv();

CREATE OR REPLACE FUNCTION fn_refresh_question_tsv_from_content()
RETURNS trigger AS $$
BEGIN
    IF EXISTS (SELECT 1 FROM question WHERE id = NEW.id) THEN
        UPDATE question
        SET document_tsv =
            setweight(to_tsvector('english', title), 'A') ||
            setweight(to_tsvector('english', NEW.content_body), 'B')
        WHERE id = NEW.id;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_refresh_question_tsv_from_content ON content;

CREATE TRIGGER trg_refresh_question_tsv_from_content
AFTER UPDATE OF content_body ON content
FOR EACH ROW
EXECUTE FUNCTION fn_refresh_question_tsv_from_content();

CREATE INDEX IF NOT EXISTS idx_question_document_tsv
    ON question USING GIN(document_tsv);

-- ==========================================================
-- TRIGGERS
-- ==========================================================

-- TRIGGER02: Validate accepted answer
CREATE OR REPLACE FUNCTION fn_check_accepted_answer()
RETURNS TRIGGER AS
$$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM answer
        WHERE id = NEW.answer_id
          AND id_question = NEW.question_id
    ) THEN
        RAISE EXCEPTION
            'Accepted answer (ID: %) does not belong to question (ID: %)',
            NEW.answer_id, NEW.question_id;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_check_accepted_answer
BEFORE INSERT OR UPDATE ON accepted_answer
FOR EACH ROW
EXECUTE FUNCTION fn_check_accepted_answer();

-- TRIGGER03: Notify users on new answer
CREATE OR REPLACE FUNCTION fn_notify_new_answer()
RETURNS TRIGGER AS
$$
DECLARE
    v_question_author_id BIGINT;
    v_answer_author_id BIGINT;
    v_follower RECORD;
    v_notification_id BIGINT;
BEGIN
    SELECT c.id_author INTO v_question_author_id
    FROM question q
    JOIN content c ON q.id = c.id
    WHERE q.id = NEW.id_question;

    SELECT id_author INTO v_answer_author_id
    FROM content
    WHERE id = NEW.id;

    IF v_question_author_id IS NOT NULL AND v_question_author_id <> v_answer_author_id THEN
        INSERT INTO notification(date, is_read, id_reader)
        VALUES (NOW(), FALSE, v_question_author_id)
        RETURNING id INTO v_notification_id;

        INSERT INTO new_answer_notification(id, id_answer)
        VALUES (v_notification_id, NEW.id);
    END IF;

    FOR v_follower IN
        SELECT id_reader FROM reader_question_follow
        WHERE id_question = NEW.id_question
          AND id_reader <> v_answer_author_id
    LOOP
        INSERT INTO notification(date, is_read, id_reader)
        VALUES (NOW(), FALSE, v_follower.id_reader)
        RETURNING id INTO v_notification_id;

        INSERT INTO new_answer_notification(id, id_answer)
        VALUES (v_notification_id, NEW.id);
    END LOOP;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER trg_notify_new_answer
AFTER INSERT ON answer
FOR EACH ROW
EXECUTE FUNCTION fn_notify_new_answer();

-- TRIGGER04: Notify content author on new vote
CREATE OR REPLACE FUNCTION fn_notify_new_vote()
RETURNS TRIGGER AS
$$
DECLARE
    v_content_author_id BIGINT;
    v_notification_id BIGINT;
BEGIN
    SELECT id_author INTO v_content_author_id
    FROM content
    WHERE id = NEW.id_content;

    IF v_content_author_id IS NOT NULL AND v_content_author_id <> NEW.id_voter THEN
        INSERT INTO notification (date, is_read, id_reader)
        VALUES (NOW(), FALSE, v_content_author_id)
        RETURNING id INTO v_notification_id;

        INSERT INTO new_vote_notification (id, id_vote)
        VALUES (v_notification_id, NEW.id);
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_notify_new_vote
AFTER INSERT ON vote
FOR EACH ROW
EXECUTE FUNCTION fn_notify_new_vote();

-- TRIGGER05: Notify reader on new badge
CREATE OR REPLACE FUNCTION fn_notify_new_badge()
RETURNS TRIGGER AS
$$
DECLARE
    v_notification_id BIGINT;
BEGIN
    INSERT INTO notification (date, is_read, id_reader)
    VALUES (CURRENT_DATE, FALSE, NEW.id_reader)
    RETURNING id INTO v_notification_id;
    INSERT INTO new_badge_notification (id, id_badge)
    VALUES (v_notification_id, NEW.id_badge);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_notify_new_badge
AFTER INSERT ON reader_badge
FOR EACH ROW
EXECUTE FUNCTION fn_notify_new_badge();

-- TRIGGER07: Remove moderator/admin roles when reader is deleted or banned
CREATE OR REPLACE FUNCTION fn_cleanup_roles_on_reader_change()
RETURNS TRIGGER AS
$$
BEGIN
    IF TG_OP = 'DELETE' 
       OR (TG_OP = 'UPDATE' AND NEW.is_banned = TRUE AND OLD.is_banned = FALSE) THEN
        DELETE FROM moderator WHERE id_reader = OLD.id;
        DELETE FROM administrator WHERE id_reader = OLD.id;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_cleanup_roles_on_reader_change
AFTER UPDATE OR DELETE ON reader
FOR EACH ROW
EXECUTE FUNCTION fn_cleanup_roles_on_reader_change();

-- TRIGGER08: Auto-update content.last_edited_date on content changes
CREATE OR REPLACE FUNCTION fn_auto_update_last_edited_date()
RETURNS TRIGGER AS
$$
BEGIN
    IF NEW.content_body IS DISTINCT FROM OLD.content_body THEN
        NEW.last_edited_date := CURRENT_DATE;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_auto_update_last_edited_date
BEFORE UPDATE ON content
FOR EACH ROW
EXECUTE FUNCTION fn_auto_update_last_edited_date();

-- TRIGGER09: Clean up follower references when questions or tags are deleted
CREATE OR REPLACE FUNCTION fn_cleanup_follows()
RETURNS TRIGGER AS
$$
BEGIN
    IF TG_TABLE_NAME = 'question' THEN
        DELETE FROM reader_question_follow WHERE id_question = OLD.id;
    ELSIF TG_TABLE_NAME = 'tag' THEN
        DELETE FROM reader_tag_follow WHERE id_tag = OLD.id;
    END IF;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_cleanup_question_follows
AFTER DELETE ON question
FOR EACH ROW
EXECUTE FUNCTION fn_cleanup_follows();

CREATE TRIGGER trg_cleanup_tag_follows
AFTER DELETE ON tag
FOR EACH ROW
EXECUTE FUNCTION fn_cleanup_follows();