DROP TABLE IF EXISTS userparticipatesinexperiment CASCADE;
DROP TABLE IF EXISTS userviewsexperiment CASCADE;
DROP TABLE IF EXISTS userviewselement CASCADE;
DROP TABLE IF EXISTS userviewslabequipment CASCADE;
DROP TABLE IF EXISTS experimentlabequipment CASCADE;
DROP TABLE IF EXISTS experiment CASCADE;
DROP TABLE IF EXISTS reaction CASCADE;
DROP TABLE IF EXISTS elements CASCADE;
DROP TABLE IF EXISTS labequipment CASCADE;
DROP TABLE IF EXISTS student CASCADE;
DROP TABLE IF EXISTS teacher CASCADE;
DROP TABLE IF EXISTS "User" CASCADE;

DROP TYPE IF EXISTS user_role CASCADE;

-- ENUM за улога
CREATE TYPE user_role AS ENUM ('student', 'teacher');

-- USER
CREATE TABLE "User" (
    user_id       SERIAL PRIMARY KEY,
    user_name     VARCHAR(30)  NOT NULL,
    user_surname  VARCHAR(30)  NOT NULL,
    email         VARCHAR(50)  NOT NULL UNIQUE,
    password      VARCHAR(100) NOT NULL,
    role          user_role    NOT NULL
);

CREATE TABLE teacher (
    teacher_id INT PRIMARY KEY REFERENCES "User"(user_id) ON DELETE CASCADE
);

CREATE TABLE student (
    student_id INT PRIMARY KEY REFERENCES "User"(user_id) ON DELETE CASCADE,
    teacher_id INT NOT NULL REFERENCES teacher(teacher_id) ON DELETE CASCADE
);

-- LABEQUIPMENT
CREATE TABLE labequipment (
    equipment_id   SERIAL PRIMARY KEY,
    equipment_name VARCHAR(50) NOT NULL UNIQUE,
    type           VARCHAR(50),
    description    TEXT,
    safety_info    TEXT,
    teacher_id     INT NOT NULL REFERENCES teacher(teacher_id) ON DELETE CASCADE
);

-- ELEMENTS
CREATE TABLE elements (
    element_id          SERIAL PRIMARY KEY,
    symbol              VARCHAR(2)  NOT NULL UNIQUE,
    element_name        VARCHAR(50) NOT NULL,
    atomic_number       INT         NOT NULL,
    atomic_weight       DOUBLE PRECISION NOT NULL,
    melting_point       DOUBLE PRECISION,
    boiling_point       DOUBLE PRECISION,
    hazard_type         VARCHAR(50),
    description_element TEXT,
    teacher_id          INT NOT NULL REFERENCES teacher(teacher_id) ON DELETE CASCADE
);

-- REACTION
CREATE TABLE reaction (
    reaction_id SERIAL PRIMARY KEY,
    teacher_id  INT NOT NULL REFERENCES teacher(teacher_id) ON DELETE CASCADE,
    element1_id INT NOT NULL REFERENCES elements(element_id) ON DELETE CASCADE,
    element2_id INT NOT NULL REFERENCES elements(element_id) ON DELETE CASCADE,
    product     VARCHAR(100),
    conditions  TEXT,
    CONSTRAINT chk_reaction_diff_elements CHECK (element1_id <> element2_id)
);

-- EXPERIMENT
CREATE TABLE experiment (
    experiment_id  SERIAL PRIMARY KEY,
    teacher_id     INT NOT NULL REFERENCES teacher(teacher_id) ON DELETE CASCADE,
    reaction_id    INT NOT NULL REFERENCES reaction(reaction_id) ON DELETE CASCADE,
    result         TEXT,
    time_stamp     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    safety_warning TEXT
);

-- EXPERIMENT <-> LABEQUIPMENT (N:M)
CREATE TABLE experimentlabequipment (
    experiment_id INT NOT NULL REFERENCES experiment(experiment_id) ON DELETE CASCADE,
    equipment_id  INT NOT NULL REFERENCES labequipment(equipment_id) ON DELETE CASCADE,
    PRIMARY KEY (experiment_id, equipment_id)
);

-- USER VIEWS
CREATE TABLE userviewselement (
    user_id    INT NOT NULL REFERENCES "User"(user_id) ON DELETE CASCADE,
    element_id INT NOT NULL REFERENCES elements(element_id) ON DELETE CASCADE,
    PRIMARY KEY (user_id, element_id)
);

CREATE TABLE userviewslabequipment (
    user_id      INT NOT NULL REFERENCES "User"(user_id) ON DELETE CASCADE,
    equipment_id INT NOT NULL REFERENCES labequipment(equipment_id) ON DELETE CASCADE,
    PRIMARY KEY (user_id, equipment_id)
);

-- USER PARTICIPATION 
CREATE TABLE userparticipatesinexperiment (
    user_id                 INT NOT NULL REFERENCES "User"(user_id) ON DELETE CASCADE,
    experiment_id           INT NOT NULL REFERENCES experiment(experiment_id) ON DELETE CASCADE,
    PRIMARY KEY (user_id, experiment_id)
);