CREATE TYPE user_status AS ENUM ('active', 'inactive');

CREATE TABLE IF NOT EXISTS categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    description TEXT
);

CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    uid UUID NOT NULL UNIQUE,
    username VARCHAR(32) NOT NULL UNIQUE,
    first_name VARCHAR(64) NOT NULL,
    last_name VARCHAR(64) NOT NULL,
    university VARCHAR(128) NOT NULL,
    email VARCHAR(128) NOT NULL UNIQUE,
    password VARCHAR(128) NOT NULL,
    first_password VARCHAR(128) NOT NULL,
    status user_status NOT NULL DEFAULT 'active',
    salt VARCHAR(64) NOT NULL,
    is_admin BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS forums (
    id SERIAL PRIMARY KEY,
    fid UUID NOT NULL UNIQUE,
    title VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    category_id INTEGER REFERENCES categories(id) ON DELETE SET NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS forum_members (
    user_id UUID NOT NULL REFERENCES users(uid) ON DELETE CASCADE,
    forum_id UUID NOT NULL REFERENCES forums(fid) ON DELETE CASCADE,
    role VARCHAR(10) NOT NULL CHECK (role IN ('admin', 'member')),
    joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, forum_id)
);

CREATE TABLE IF NOT EXISTS posts (
    id SERIAL PRIMARY KEY,
    forum_id UUID NOT NULL REFERENCES forums(fid) ON DELETE CASCADE,
    user_id UUID REFERENCES users(uid) ON DELETE SET NULL,
    title VARCHAR(200) NOT NULL,
    body TEXT NOT NULL,
    media_url VARCHAR(255),
    media_type VARCHAR(50),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS comments (
    id SERIAL PRIMARY KEY,
    post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    user_id UUID REFERENCES users(uid) ON DELETE SET NULL,
    body TEXT NOT NULL,
    parent_comment_id INTEGER REFERENCES comments(id) ON DELETE CASCADE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS votes (
    id SERIAL PRIMARY KEY,
    user_id UUID NOT NULL REFERENCES users(uid) ON DELETE CASCADE,
    post_id INTEGER REFERENCES posts(id) ON DELETE CASCADE,
    comment_id INTEGER REFERENCES comments(id) ON DELETE CASCADE,
    value SMALLINT NOT NULL CHECK (value IN (1, -1)),
    CONSTRAINT one_vote_per_user_on_target UNIQUE (user_id, post_id, comment_id),
    CHECK (
        (post_id IS NOT NULL AND comment_id IS NULL) OR
        (post_id IS NULL AND comment_id IS NOT NULL)
    )
);

INSERT INTO categories (name, description)
VALUES
    ('General Discussion', 'Ruang diskusi umum untuk topik apa saja seputar kehidupan universitas (mirip r/AskReddit).'),
    ('Computer Science & IT', 'Diskusi mengenai pemrograman, jaringan, AI, dan teknologi (mirip r/programming).'),
    ('Engineering', 'Komunitas untuk mahasiswa teknik sipil, mesin, elektro, dan arsitektur.'),
    ('Economics & Business', 'Diskusi seputar manajemen, akuntansi, saham, dan bisnis.'),
    ('Arts & Humanities', 'Ruang untuk seni, sastra, psikologi, dan ilmu sosial.'),
    ('Campus Life & Events', 'Informasi event kampus, UKM, rekomendasi kantin, atau info kosan.'),
    ('Career & Internships', 'Info lowongan magang, tips CV, dan persiapan karir setelah lulus.'),
    ('Thesis & Research', 'Tempat berkeluh kesah dan berbagi tips seputar Skripsi/Tesis (mirip r/GradSchool).'),
    ('Admissions & New Students', 'Panduan dan tanya jawab untuk mahasiswa baru (Maba).')
ON CONFLICT (name) DO NOTHING;