-- Drop existing tables if they exist
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;

-- Create User table
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 Student table (inherits from User)
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
);

-- Create Teacher table (inherits from User)
CREATE TABLE Teacher (
    teacher_id INT PRIMARY KEY REFERENCES "User"(user_id) ON DELETE CASCADE
);

-- Create LabEquipment table
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
);

-- Create Elements table
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,
    teacher_id INT NOT NULL REFERENCES Teacher(teacher_id) ON DELETE CASCADE
);

-- Create Reaction table
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(50),
    conditions TEXT
);

-- Create Experiment table
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
);

-- Join table for linking experiments and lab equipment
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)
);

-- Join table for tracking which users viewed elements
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)
);

-- Join table for tracking which users viewed lab equipment
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)
);

-- Join table for tracking which users viewed experiments
CREATE TABLE UserViewsExperiment (
    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)
);

-- Join table for tracking which users participated in an experiment
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)
);