-- 1. Department Table CREATE TABLE Department ( department_id SERIAL PRIMARY KEY, department_name VARCHAR(100) NOT NULL UNIQUE );

-- 2. User Table CREATE TABLE "User" ( user_id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(100) UNIQUE NOT NULL, password TEXT NOT NULL, role VARCHAR(20) NOT NULL CHECK (role IN ('admin', 'teacher')) );

-- 3. Teacher Table CREATE TABLE Teacher ( teacher_id SERIAL PRIMARY KEY, teacher_name VARCHAR(100) NOT NULL, email VARCHAR(100) UNIQUE NOT NULL, department_id INT REFERENCES Department(department_id) ON DELETE SET NULL, profile_photo VARCHAR(255), user_id INT UNIQUE, FOREIGN KEY (user_id) REFERENCES "User"(user_id) ON DELETE CASCADE );

-- 5. Event Table CREATE TABLE Event ( event_id SERIAL PRIMARY KEY, event_name VARCHAR(100) NOT NULL, start_date DATE NOT NULL, start_time TIME NOT NULL, end_date DATE NOT NULL, end_time TIME NOT NULL, event_description TEXT );

-- 6. Event Role Table (Roles specific to an event) CREATE TABLE Role ( role_id SERIAL PRIMARY KEY, role_name VARCHAR(100) NOT NULL, role_point INT NOT NULL, role_headcount INT NOT NULL, event_id INT NOT NULL, FOREIGN KEY (event_id) REFERENCES Event(event_id) ON DELETE CASCADE );

-- 7. TeacherAssignment Table CREATE TABLE TeacherAssignment ( event_id INT NOT NULL, teacher_id INT NOT NULL, role_id INT NOT NULL, PRIMARY KEY (event_id, teacher_id), FOREIGN KEY (event_id) REFERENCES Event(event_id) ON DELETE CASCADE, FOREIGN KEY (teacher_id) REFERENCES Teacher(teacher_id) ON DELETE CASCADE, FOREIGN KEY (role_id) REFERENCES Role(role_id) ON DELETE CASCADE );