Untitled

USE master;
ALTER DATABASE module_registration SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE IF EXISTS module_registration;
GO

CREATE DATABASE module_registration;
GO

USE module_registration;
GO

------------------------------------------------------------
-- TABLE: faculties (Khoa)
------------------------------------------------------------
CREATE TABLE faculties (
    faculty_id VARCHAR(20) PRIMARY KEY,
    faculty_name NVARCHAR(100) NOT NULL,
    phone_number VARCHAR(20),
    email VARCHAR(50),
    website VARCHAR(50),
    dean NVARCHAR(50),
    description NVARCHAR(MAX),
    created_at DATETIME2 NOT NULL DEFAULT (GETDATE()),
    updated_at DATETIME2 NOT NULL DEFAULT (GETDATE())
);
GO

------------------------------------------------------------
-- TABLE: users (Tài khoản)
------------------------------------------------------------
CREATE TABLE users (
    user_id VARCHAR(15) PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(100) NOT NULL,
    full_name NVARCHAR(70) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    role NVARCHAR(10) NOT NULL,
    created_at DATETIME2 NOT NULL DEFAULT (GETDATE()),
    updated_at DATETIME2 NOT NULL DEFAULT (GETDATE()),
    CONSTRAINT CHK_users_role CHECK (role IN (N'STUDENT', N'TEACHER', N'ADMIN'))
);
GO

------------------------------------------------------------
-- TABLE: students (Sinh viên)
------------------------------------------------------------
CREATE TABLE students (
    student_id VARCHAR(15) PRIMARY KEY,
    class NVARCHAR(20) NOT NULL,
    -- major_id VARCHAR(20) NOT NULL,  -- ĐÃ XÓA
    status NVARCHAR(20) NOT NULL,
    created_at DATETIME2 NOT NULL DEFAULT (GETDATE()),
    updated_at DATETIME2 NOT NULL DEFAULT (GETDATE()),
    CONSTRAINT FK_students_users FOREIGN KEY (student_id)
        REFERENCES users(user_id)
        ON DELETE CASCADE,
    CONSTRAINT CHK_students_status CHECK (status IN (N'Đang học', N'Nghỉ học'))
);
GO

------------------------------------------------------------
-- TABLE: teachers (Giảng viên)
------------------------------------------------------------
CREATE TABLE teachers (
    teacher_id VARCHAR(15) PRIMARY KEY,
    faculty_id VARCHAR(20) NOT NULL,
    degree NVARCHAR(50),
    experience_years INT CHECK (experience_years >= 0),
    created_at DATETIME2 NOT NULL DEFAULT (GETDATE()),
    updated_at DATETIME2 NOT NULL DEFAULT (GETDATE()),
    CONSTRAINT FK_teachers_users FOREIGN KEY (teacher_id)
        REFERENCES users(user_id)
        ON DELETE CASCADE,
    CONSTRAINT FK_teachers_faculties FOREIGN KEY (faculty_id)
        REFERENCES faculties(faculty_id)
        ON DELETE NO ACTION
);
GO

------------------------------------------------------------
-- TABLE: modules (Học phần)
------------------------------------------------------------
CREATE TABLE modules (
    module_id VARCHAR(15) PRIMARY KEY,
    module_name NVARCHAR(70) NOT NULL,
    credits INT CHECK (credits > 0),
    faculty_id VARCHAR(20) NOT NULL,
    created_at DATETIME2 NOT NULL DEFAULT (GETDATE()),
    updated_at DATETIME2 NOT NULL DEFAULT (GETDATE()),
    CONSTRAINT FK_modules_faculties FOREIGN KEY (faculty_id)
        REFERENCES faculties(faculty_id)
        ON DELETE CASCADE
);
GO

------------------------------------------------------------
-- TABLE: rooms (Phòng học)
------------------------------------------------------------
CREATE TABLE rooms (
    room_id VARCHAR(10) PRIMARY KEY,
    capacity INT CHECK (capacity > 0),
    projector BIT DEFAULT 0,
    airconditioner BIT DEFAULT 0,
    micro_speaker BIT DEFAULT 0,
    created_at DATETIME2 NOT NULL DEFAULT (GETDATE()),
    updated_at DATETIME2 NOT NULL DEFAULT (GETDATE())
);
GO

------------------------------------------------------------
-- TABLE: semesters (Học kỳ)
------------------------------------------------------------
CREATE TABLE semesters (
    semester_id VARCHAR(15) PRIMARY KEY,
    term NVARCHAR(50) NOT NULL,
    academic_year VARCHAR(10) NOT NULL,
    start_date DATE,
    end_date DATE,
    created_at DATETIME2 NOT NULL DEFAULT (GETDATE()),
    updated_at DATETIME2 NOT NULL DEFAULT (GETDATE())
);
GO

------------------------------------------------------------
-- TABLE: module_offerings (Lớp học phần mở)
------------------------------------------------------------
CREATE TABLE module_offerings (
    module_offering_id VARCHAR(15) PRIMARY KEY,
    module_id VARCHAR(15) NOT NULL,
    -- major_id VARCHAR(20) NOT NULL, -- ĐÃ XÓA
    instructor NVARCHAR(50),
    room_id VARCHAR(10) NOT NULL,
    semester_id VARCHAR(15) NOT NULL,
    max_capacity INT CHECK (max_capacity > 0),
    current_capacity INT DEFAULT 0 CHECK (current_capacity >= 0),
    created_at DATETIME2 NOT NULL DEFAULT (GETDATE()),
    updated_at DATETIME2 NOT NULL DEFAULT (GETDATE()),
    CONSTRAINT FK_co_modules FOREIGN KEY (module_id)
        REFERENCES modules(module_id)
        ON DELETE CASCADE,
    CONSTRAINT FK_co_rooms FOREIGN KEY (room_id)
        REFERENCES rooms(room_id),
    CONSTRAINT FK_co_semesters FOREIGN KEY (semester_id)
        REFERENCES semesters(semester_id)
);

GO
------------------------------------------------------------
-- TABLE: schedules (Lịch học)
------------------------------------------------------------
CREATE TABLE schedules (
    schedule_id VARCHAR(15) PRIMARY KEY,
    day_of_week INT CHECK (day_of_week BETWEEN 2 AND 8),
    start_time TIME,
    end_time TIME,
    created_at DATETIME2 NOT NULL DEFAULT (GETDATE()),
    updated_at DATETIME2 NOT NULL DEFAULT (GETDATE())
);
GO

------------------------------------------------------------
-- TABLE: module_offerings_schedules (Liên kết lớp học phần - lịch)
------------------------------------------------------------
CREATE TABLE module_offerings_schedules (
    id INT IDENTITY(1,1) PRIMARY KEY,
    module_offering_id VARCHAR(15) NOT NULL,
    schedule_id VARCHAR(15) NOT NULL,
    start_date DATE,
    end_date DATE,
    created_at DATETIME2 NOT NULL DEFAULT (GETDATE()),
    updated_at DATETIME2 NOT NULL DEFAULT (GETDATE()),
    CONSTRAINT FK_cos_module_offerings FOREIGN KEY (module_offering_id)
        REFERENCES module_offerings(module_offering_id)
        ON DELETE CASCADE,
    CONSTRAINT FK_cos_schedules FOREIGN KEY (schedule_id)
        REFERENCES schedules(schedule_id)
        ON DELETE CASCADE
);
GO

------------------------------------------------------------
-- TABLE: registrations (Đăng ký học phần)
------------------------------------------------------------
CREATE TABLE registrations (
    registration_id VARCHAR(20) PRIMARY KEY,
    student_id VARCHAR(15) NOT NULL,
    module_offering_id VARCHAR(15) NOT NULL,
    module_id VARCHAR(15) NOT NULL,
    registered_at DATETIME2 DEFAULT (GETDATE()),
    status NVARCHAR(20) NOT NULL,
    note NVARCHAR(MAX),
    created_at DATETIME2 NOT NULL DEFAULT (GETDATE()),
    updated_at DATETIME2 NOT NULL DEFAULT (GETDATE()),
    CONSTRAINT FK_registrations_module_offerings FOREIGN KEY (module_offering_id)
        REFERENCES module_offerings(module_offering_id)
        ON DELETE CASCADE,
    CONSTRAINT FK_registrations_students FOREIGN KEY (student_id)
        REFERENCES students(student_id)
        ON DELETE CASCADE,
    CONSTRAINT FK_registrations_modules FOREIGN KEY (module_id)
        REFERENCES modules(module_id)
        ON DELETE NO ACTION,
    CONSTRAINT CHK_registrations_status CHECK (status IN (N'Thành công', N'Thất bại'))
);
GO