DROP SCHEMA IF EXISTS pcforge CASCADE;
CREATE SCHEMA pcforge;
SET search_path TO pcforge;

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username TEXT NOT NULL UNIQUE
        CHECK (length(username) >= 3),
    password TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
        CHECK (email LIKE '%@%.%')
);

CREATE TABLE admins (
    user_id INTEGER PRIMARY KEY,
    CONSTRAINT fk_admin_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE components (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    brand TEXT NOT NULL,
    price NUMERIC NOT NULL
        CHECK (price >= 0),
    img_url TEXT
);

CREATE TABLE cpu (
    component_id INTEGER PRIMARY KEY,
    socket TEXT NOT NULL,
    cores INTEGER NOT NULL
        CHECK (cores > 0),
    threads INTEGER NOT NULL,
    base_clock NUMERIC NOT NULL
        CHECK (base_clock > 0),
    boost_clock NUMERIC
        CHECK (boost_clock IS NULL OR boost_clock >= base_clock),
    tdp NUMERIC NOT NULL
        CHECK (tdp > 0),
    FOREIGN KEY (component_id) REFERENCES components(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE gpu (
    component_id INTEGER PRIMARY KEY,
    vram NUMERIC NOT NULL
        CHECK (vram > 0),
    tdp NUMERIC NOT NULL
        CHECK (tdp > 0),
    base_clock NUMERIC,
    boost_clock NUMERIC,
    chipset TEXT NOT NULL,
    length NUMERIC NOT NULL
        CHECK (length > 0),
    FOREIGN KEY (component_id) REFERENCES components(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE memory (
    component_id INTEGER PRIMARY KEY,
    type TEXT NOT NULL,
    speed NUMERIC NOT NULL
        CHECK (speed > 0),
    capacity NUMERIC NOT NULL
        CHECK (capacity > 0),
    modules INTEGER NOT NULL
        CHECK (modules > 0),
    FOREIGN KEY (component_id) REFERENCES components(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE storage (
    component_id INTEGER PRIMARY KEY,
    type TEXT NOT NULL,
    capacity NUMERIC NOT NULL
        CHECK (capacity > 0),
    form_factor TEXT NOT NULL,
    FOREIGN KEY (component_id) REFERENCES components(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE power_supply (
    component_id INTEGER PRIMARY KEY,
    type TEXT NOT NULL,
    wattage NUMERIC NOT NULL
        CHECK (wattage > 0),
    form_factor TEXT NOT NULL,
    FOREIGN KEY (component_id) REFERENCES components(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE motherboard (
    component_id INTEGER PRIMARY KEY,
    socket TEXT NOT NULL,
    chipset TEXT NOT NULL,
    form_factor TEXT NOT NULL,
    ram_type TEXT NOT NULL,
    num_ram_slots INTEGER NOT NULL
        CHECK (num_ram_slots > 0),
    max_ram_capacity NUMERIC NOT NULL
        CHECK (max_ram_capacity > 0),
    FOREIGN KEY (component_id) REFERENCES components(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE pc_case (
    component_id INTEGER PRIMARY KEY,
    cooler_max_height NUMERIC NOT NULL
        CHECK (cooler_max_height > 0),
    gpu_max_length NUMERIC NOT NULL
        CHECK (gpu_max_length > 0),
    FOREIGN KEY (component_id) REFERENCES components(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE case_storage_form_factors (
    case_id INTEGER NOT NULL,
    form_factor TEXT NOT NULL,
    PRIMARY KEY (case_id, form_factor),
    FOREIGN KEY (case_id) REFERENCES pc_case(component_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE case_ps_form_factors (
    case_id INTEGER NOT NULL,
    form_factor TEXT NOT NULL,
    PRIMARY KEY (case_id, form_factor),
    FOREIGN KEY (case_id) REFERENCES pc_case(component_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE case_mobo_form_factors (
    case_id INTEGER NOT NULL,
    form_factor TEXT NOT NULL,
    PRIMARY KEY (case_id, form_factor),
    FOREIGN KEY (case_id) REFERENCES pc_case(component_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE cooler (
    component_id INTEGER PRIMARY KEY,
    type TEXT NOT NULL,
    height NUMERIC NOT NULL
        CHECK (height > 0),
    max_tdp_supported NUMERIC NOT NULL
        CHECK (max_tdp_supported > 0),
    FOREIGN KEY (component_id) REFERENCES components(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE cooler_cpu_sockets (
    cooler_id INTEGER NOT NULL,
    socket TEXT NOT NULL,
    PRIMARY KEY (cooler_id, socket),
    FOREIGN KEY (cooler_id) REFERENCES cooler(component_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE memory_card (
    component_id INTEGER PRIMARY KEY,
    num_slots INTEGER NOT NULL,
    interface TEXT NOT NULL,
    FOREIGN KEY (component_id) REFERENCES components(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE optical_drive (
    component_id INTEGER PRIMARY KEY,
    form_factor TEXT NOT NULL,
    type TEXT NOT NULL,
    interface TEXT NOT NULL,
    write_speed NUMERIC NOT NULL,
    read_speed NUMERIC NOT NULL,
    FOREIGN KEY (component_id) REFERENCES components(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE sound_card (
    component_id INTEGER PRIMARY KEY,
    sample_rate NUMERIC NOT NULL,
    bit_depth NUMERIC NOT NULL,
    chipset TEXT NOT NULL,
    interface TEXT NOT NULL,
    FOREIGN KEY (component_id) REFERENCES components(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE sound_card_channels (
    sound_card_id INTEGER NOT NULL,
    channel TEXT NOT NULL,
    PRIMARY KEY (sound_card_id, channel),
    FOREIGN KEY (sound_card_id) REFERENCES sound_card(component_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE cables (
    component_id INTEGER PRIMARY KEY,
    length_cm NUMERIC NOT NULL,
    type TEXT NOT NULL,
    FOREIGN KEY (component_id) REFERENCES components(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE network_adapter (
    component_id INTEGER PRIMARY KEY,
    wifi_version TEXT NOT NULL,
    interface TEXT NOT NULL,
    num_antennas INTEGER NOT NULL,
    FOREIGN KEY (component_id) REFERENCES components(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE network_card (
    component_id INTEGER PRIMARY KEY,
    num_ports INTEGER NOT NULL,
    speed NUMERIC NOT NULL,
    interface TEXT NOT NULL,
    FOREIGN KEY (component_id) REFERENCES components(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE build (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    name TEXT NOT NULL,
    created_at DATE NOT NULL
        CHECK (created_at <= CURRENT_DATE),
    description TEXT,
    total_price NUMERIC NOT NULL
        CHECK (total_price >= 0),
    is_approved BOOLEAN NOT NULL,
    CONSTRAINT fk_build_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE build_component (
    build_id INTEGER NOT NULL,
    component_id INTEGER NOT NULL,
    PRIMARY KEY (build_id, component_id),
    FOREIGN KEY (build_id) REFERENCES build(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (component_id) REFERENCES components(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE favorite_build (
    build_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    PRIMARY KEY (build_id, user_id),
    FOREIGN KEY (build_id) REFERENCES build(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE rating_build (
    build_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    value NUMERIC NOT NULL
        CHECK (value BETWEEN 1 AND 5),
    PRIMARY KEY (build_id, user_id),
    FOREIGN KEY (build_id) REFERENCES build(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE review (
    id SERIAL PRIMARY KEY,
    build_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    content TEXT NOT NULL,
    created_at DATE NOT NULL
        CHECK (created_at <= CURRENT_DATE),
    FOREIGN KEY (build_id) REFERENCES build(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE suggestions (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    admin_id INTEGER,
    link TEXT NOT NULL,
    admin_comment TEXT,
    description TEXT,
    status TEXT NOT NULL DEFAULT 'pending'
        CHECK (status in ('pending', 'approved', 'rejected')),
    component_type TEXT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (admin_id) REFERENCES admins(user_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

