CREATE DATABASE IF NOT EXISTS `db_sched`;
USE `db_sched`;

SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS `course_prerequisites`;
DROP TABLE IF EXISTS `studentcourseenrollment`;
DROP TABLE IF EXISTS `studentsections`;
DROP TABLE IF EXISTS `tb_sched`;
DROP TABLE IF EXISTS `sections`;
DROP TABLE IF EXISTS `students`;
DROP TABLE IF EXISTS `rooms`;
DROP TABLE IF EXISTS `courses`;
DROP TABLE IF EXISTS `majors`;
DROP TABLE IF EXISTS `instructors`;
DROP TABLE IF EXISTS `departments`;
DROP TABLE IF EXISTS `colleges`;
DROP TABLE IF EXISTS `system_settings`;
DROP TABLE IF EXISTS `users`;

SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE `users` (
    `user_id` INT PRIMARY KEY AUTO_INCREMENT,
    `username` VARCHAR(50) UNIQUE NOT NULL,
    `email` VARCHAR(100) UNIQUE NOT NULL,
    `password` VARCHAR(255) NOT NULL,
    `first_name` VARCHAR(50),
    `last_name` VARCHAR(50),
    `role` ENUM('admin', 'instructor', 'student') DEFAULT 'student',
    `is_active` TINYINT(1) DEFAULT 1,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `colleges` (
    `college_id` INT PRIMARY KEY AUTO_INCREMENT,
    `college_code` VARCHAR(50) NOT NULL UNIQUE,
    `college_name` VARCHAR(150) NOT NULL,
    `description` TEXT,
    `is_active` TINYINT(1) DEFAULT 1,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `departments` (
    `department_id` INT PRIMARY KEY AUTO_INCREMENT,
    `college_id` INT,
    `department_code` VARCHAR(50) NOT NULL UNIQUE,
    `department_name` VARCHAR(100) NOT NULL,
    `description` TEXT,
    `is_active` TINYINT(1) DEFAULT 1,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY `fk_department_college` (`college_id`),
    CONSTRAINT `fk_department_college` FOREIGN KEY (`college_id`) REFERENCES `colleges` (`college_id`)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `majors` (
    `major_id` INT PRIMARY KEY AUTO_INCREMENT,
    `department_id` INT NOT NULL,
    `major_code` VARCHAR(50) NOT NULL UNIQUE,
    `major_name` VARCHAR(150) NOT NULL,
    `description` TEXT,
    `is_active` TINYINT(1) DEFAULT 1,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY `fk_major_department` (`department_id`),
    CONSTRAINT `fk_major_department` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `courses` (
    `course_id` INT PRIMARY KEY AUTO_INCREMENT,
    `course_code` VARCHAR(20) NOT NULL UNIQUE,
    `course_name` VARCHAR(100) NOT NULL,
    `units` INT NOT NULL,
    `lecture_hours` INT DEFAULT 0,
    `lab_hours` INT DEFAULT 0,
    `year_level` INT,
    `semester` VARCHAR(20),
    `description` TEXT,
    `department_id` INT,
    `major_id` INT,
    KEY `fk_course_department` (`department_id`),
    KEY `fk_course_major` (`major_id`),
    CONSTRAINT `fk_course_department` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`)
        ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT `fk_course_major` FOREIGN KEY (`major_id`) REFERENCES `majors` (`major_id`)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `instructors` (
    `instructor_id` INT PRIMARY KEY AUTO_INCREMENT,
    `first_name` VARCHAR(50) NOT NULL,
    `last_name` VARCHAR(50) NOT NULL,
    `department` VARCHAR(100),
    `email` VARCHAR(100) UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `rooms` (
    `room_id` INT PRIMARY KEY AUTO_INCREMENT,
    `room_code` VARCHAR(50) NOT NULL UNIQUE,
    `room_name` VARCHAR(100) NOT NULL,
    `department_id` INT NOT NULL,
    `capacity` INT,
    `building` VARCHAR(100),
    `floor` INT,
    `description` TEXT,
    `is_active` TINYINT(1) DEFAULT 1,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY `fk_room_department` (`department_id`),
    CONSTRAINT `fk_room_department` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `sections` (
    `section_id` INT PRIMARY KEY AUTO_INCREMENT,
    `section_code` VARCHAR(50) NOT NULL UNIQUE,
    `department_id` INT NOT NULL DEFAULT 1,
    `major_id` INT,
    `section_name` VARCHAR(100) NOT NULL,
    `course_id` INT NOT NULL,
    `instructor_id` INT,
    `year_level` INT,
    `max_students` INT,
    `description` TEXT,
    `is_active` TINYINT(1) DEFAULT 1,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY `fk_section_course` (`course_id`),
    KEY `fk_section_instructor` (`instructor_id`),
    KEY `fk_section_department` (`department_id`),
    KEY `fk_section_major` (`major_id`),
    CONSTRAINT `fk_section_course` FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_section_instructor` FOREIGN KEY (`instructor_id`) REFERENCES `instructors` (`instructor_id`)
        ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT `fk_section_department` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_section_major` FOREIGN KEY (`major_id`) REFERENCES `majors` (`major_id`)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `students` (
    `student_id` INT PRIMARY KEY AUTO_INCREMENT,
    `first_name` VARCHAR(50) NOT NULL,
    `last_name` VARCHAR(50) NOT NULL,
    `birth_date` DATE,
    `email` VARCHAR(100) UNIQUE,
    `contact_number` VARCHAR(20),
    `department_id` INT,
    `year_level` INT,
    `section_id` INT,
    KEY `fk_student_department` (`department_id`),
    KEY `fk_student_section` (`section_id`),
    CONSTRAINT `fk_student_department` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`)
        ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT `fk_student_section` FOREIGN KEY (`section_id`) REFERENCES `sections` (`section_id`)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `studentcourseenrollment` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `student_id` INT NOT NULL,
    `course_id` INT NOT NULL,
    `section_id` INT,
    `instructor_id` INT,
    `enrolled_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `unique_student_course` (`student_id`, `course_id`),
    KEY `fk_ce_course` (`course_id`),
    KEY `fk_ce_section` (`section_id`),
    KEY `fk_ce_instructor` (`instructor_id`),
    CONSTRAINT `fk_ce_student` FOREIGN KEY (`student_id`) REFERENCES `students` (`student_id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_ce_course` FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_ce_section` FOREIGN KEY (`section_id`) REFERENCES `sections` (`section_id`)
        ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT `fk_ce_instructor` FOREIGN KEY (`instructor_id`) REFERENCES `instructors` (`instructor_id`)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `studentsections` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `student_id` INT NOT NULL,
    `section_id` INT NOT NULL,
    `enrolled_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `unique_student_section` (`student_id`, `section_id`),
    KEY `fk_student_section_section` (`section_id`),
    CONSTRAINT `fk_student_section_student` FOREIGN KEY (`student_id`) REFERENCES `students` (`student_id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_student_section_section` FOREIGN KEY (`section_id`) REFERENCES `sections` (`section_id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `tb_sched` (
    `sched_id` INT PRIMARY KEY AUTO_INCREMENT,
    `student_id` INT NOT NULL,
    `course_id` INT NOT NULL,
    `instructor_id` INT NOT NULL,
    `schedule_time` DATETIME NOT NULL,
    `room` VARCHAR(50),
    `grade` DECIMAL(4,2),
    KEY `fk_student` (`student_id`),
    KEY `fk_course` (`course_id`),
    KEY `fk_instructor` (`instructor_id`),
    CONSTRAINT `fk_student` FOREIGN KEY (`student_id`) REFERENCES `students` (`student_id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_course` FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_instructor` FOREIGN KEY (`instructor_id`) REFERENCES `instructors` (`instructor_id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `system_settings` (
    `setting_id` INT PRIMARY KEY AUTO_INCREMENT,
    `setting_key` VARCHAR(100) NOT NULL UNIQUE,
    `setting_value` TEXT,
    `setting_type` ENUM('string', 'integer', 'boolean', 'json') DEFAULT 'string',
    `description` TEXT,
    `is_editable` TINYINT(1) DEFAULT 1,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `course_prerequisites` (
    `prerequisite_id` INT PRIMARY KEY AUTO_INCREMENT,
    `course_id` INT NOT NULL,
    `prerequisite_course_id` INT NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `unique_prerequisite` (`course_id`, `prerequisite_course_id`),
    KEY `fk_prereq_course` (`course_id`),
    KEY `fk_prereq_prerequisite` (`prerequisite_course_id`),
    CONSTRAINT `fk_prereq_course` FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_prereq_prerequisite` FOREIGN KEY (`prerequisite_course_id`) REFERENCES `courses` (`course_id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `system_settings` (`setting_key`, `setting_value`, `setting_type`, `description`, `is_editable`) VALUES
('school_year', '2025-2026', 'string', 'Current school/academic year', 1),
('semester', '1st Semester', 'string', 'Current semester', 1),
('items_per_page', '10', 'integer', 'Number of records per page', 1),
('date_format', 'Y-m-d', 'string', 'System-wide date format', 1),
('time_zone', 'UTC', 'string', 'System timezone', 1),
('system_status', 'active', 'string', 'System status', 0);

INSERT INTO `users` (`username`, `email`, `password`, `first_name`, `last_name`, `role`, `is_active`)
VALUES ('admin', 'admin@local.test', SHA2('admin123', 256), 'System', 'Admin', 'admin', 1);