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,
    password TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
);

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,
    type TEXT NOT NULL,
	CHECK (type IN (
            'cpu', 'gpu', 'memory', 'storage', 'power_supply', 'motherboard',
            'case', 'cooler', 'memory_card', 'optical_drive', 'sound_card',
            'cables', 'network_adapter', 'network_card'
        )),
    img_url TEXT
);

CREATE TABLE cpu (
    component_id INTEGER PRIMARY KEY,
    socket TEXT NOT NULL,
    cores INTEGER NOT NULL,
    threads INTEGER NOT NULL,
    base_clock NUMERIC NOT NULL,
    boost_clock NUMERIC,
    tdp NUMERIC NOT NULL,
    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,
    tdp NUMERIC NOT NULL,
    base_clock NUMERIC,
    boost_clock NUMERIC,
    chipset TEXT NOT NULL,
    length NUMERIC NOT NULL,
    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,
    capacity NUMERIC NOT NULL,
    modules INTEGER NOT NULL,
    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,
    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,
    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,
    max_ram_capacity NUMERIC NOT NULL,
    pci_express_slots NUMERIC NOT NULL,
    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,
    gpu_max_length NUMERIC NOT NULL,
    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,
    num_slots INTEGER 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,
    max_tdp_supported NUMERIC NOT NULL,
    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,
    channel TEXT NOT NULL,
    FOREIGN KEY (component_id) REFERENCES components(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,
    description TEXT,
    total_price NUMERIC NOT NULL,
    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,
    UNIQUE (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 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,
	CHECK (component_type IN (
            'cpu', 'gpu', 'memory', 'storage', 'power_supply', 'motherboard',
            'case', 'cooler', 'memory_card', 'optical_drive', 'sound_card',
            'cables', 'network_adapter', 'network_card'
        )),
    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
);

