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 "User" CASCADE;


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 ENUM('student', 'teacher') NOT NULL
);


CREATE TABLE LabEquipment (
    equipment_id SERIAL PRIMARY KEY,
    equipment_name VARCHAR(50) NOT NULL UNIQUE,
    type VARCHAR(50),
    description TEXT,
    safety_info TEXT
);


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 FLOAT NOT NULL,
    melting_point FLOAT,
    boiling_point FLOAT,
    hazard_type VARCHAR(50),
    description_element TEXT
);


CREATE TABLE Reaction (
    reaction_id SERIAL PRIMARY KEY,
    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(50),
    conditions TEXT
);


CREATE TABLE Experiment (
    experiment_id SERIAL PRIMARY KEY,
    user_id INT NOT NULL REFERENCES "User" (user_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
);


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)
);