-- Mosharef Grammar School Management System Database Schema
-- This file contains the complete database structure for the school management system

-- Users Table (Authentication)
CREATE TABLE IF NOT EXISTS users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(100) UNIQUE NOT NULL,
  password VARCHAR(255) NOT NULL,
  role ENUM('admin', 'teacher', 'staff', 'student') NOT NULL,
  status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  phone VARCHAR(20),
  profile_photo VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_email (email),
  INDEX idx_role (role),
  INDEX idx_status (status)
);

-- Classes Table
CREATE TABLE IF NOT EXISTS classes (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL UNIQUE,
  numeric_value INT NOT NULL,
  description TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Sections Table
CREATE TABLE IF NOT EXISTS sections (
  id INT PRIMARY KEY AUTO_INCREMENT,
  class_id INT NOT NULL,
  name VARCHAR(50) NOT NULL,
  capacity INT DEFAULT 50,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE,
  UNIQUE KEY unique_section (class_id, name)
);

-- Subjects Table
CREATE TABLE IF NOT EXISTS subjects (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL UNIQUE,
  code VARCHAR(20) NOT NULL UNIQUE,
  description TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Class-Subject Assignments
CREATE TABLE IF NOT EXISTS class_subjects (
  id INT PRIMARY KEY AUTO_INCREMENT,
  class_id INT NOT NULL,
  subject_id INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE,
  FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
  UNIQUE KEY unique_class_subject (class_id, subject_id)
);

-- Students Table
CREATE TABLE IF NOT EXISTS students (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT,
  roll_number INT NOT NULL,
  class_id INT NOT NULL,
  section_id INT NOT NULL,
  admission_number VARCHAR(50) UNIQUE NOT NULL,
  date_of_birth DATE NOT NULL,
  gender ENUM('male', 'female', 'other') NOT NULL,
  father_name VARCHAR(100) NOT NULL,
  father_phone VARCHAR(20),
  father_occupation VARCHAR(100),
  mother_name VARCHAR(100) NOT NULL,
  mother_phone VARCHAR(20),
  mother_occupation VARCHAR(100),
  guardian_name VARCHAR(100),
  guardian_relation VARCHAR(50),
  guardian_contact VARCHAR(20),
  address TEXT NOT NULL,
  city VARCHAR(50),
  state VARCHAR(50),
  postal_code VARCHAR(20),
  student_photo VARCHAR(255),
  father_photo VARCHAR(255),
  mother_photo VARCHAR(255),
  admission_date DATE NOT NULL,
  status ENUM('active', 'inactive', 'graduated', 'transferred') DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY (class_id) REFERENCES classes(id),
  FOREIGN KEY (section_id) REFERENCES sections(id),
  INDEX idx_class (class_id),
  INDEX idx_section (section_id),
  INDEX idx_admission (admission_number)
);

-- Teachers Table
CREATE TABLE IF NOT EXISTS teachers (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL UNIQUE,
  nid_number VARCHAR(50) UNIQUE NOT NULL,
  designation VARCHAR(100) NOT NULL,
  qualification VARCHAR(255),
  specialization VARCHAR(100),
  father_name VARCHAR(100),
  mother_name VARCHAR(100),
  date_of_birth DATE,
  joining_date DATE NOT NULL,
  salary DECIMAL(10, 2),
  bank_account VARCHAR(50),
  ifsc_code VARCHAR(20),
  status ENUM('active', 'inactive', 'on_leave') DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_nid (nid_number)
);

-- Teacher-Subject Assignments
CREATE TABLE IF NOT EXISTS teacher_subjects (
  id INT PRIMARY KEY AUTO_INCREMENT,
  teacher_id INT NOT NULL,
  subject_id INT NOT NULL,
  class_id INT NOT NULL,
  section_id INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE CASCADE,
  FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
  FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE,
  FOREIGN KEY (section_id) REFERENCES sections(id) ON DELETE CASCADE,
  UNIQUE KEY unique_assignment (teacher_id, subject_id, class_id, section_id)
);

-- Staff Table
CREATE TABLE IF NOT EXISTS staff (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL UNIQUE,
  employee_id VARCHAR(50) UNIQUE NOT NULL,
  designation VARCHAR(100) NOT NULL,
  department VARCHAR(100),
  joining_date DATE NOT NULL,
  salary DECIMAL(10, 2),
  status ENUM('active', 'inactive') DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Attendance Table
CREATE TABLE IF NOT EXISTS attendance (
  id INT PRIMARY KEY AUTO_INCREMENT,
  student_id INT NOT NULL,
  class_id INT NOT NULL,
  section_id INT NOT NULL,
  attendance_date DATE NOT NULL,
  status ENUM('present', 'absent', 'leave') DEFAULT 'present',
  marked_by INT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  FOREIGN KEY (class_id) REFERENCES classes(id),
  FOREIGN KEY (section_id) REFERENCES sections(id),
  FOREIGN KEY (marked_by) REFERENCES users(id),
  INDEX idx_attendance_date (attendance_date),
  INDEX idx_student (student_id),
  UNIQUE KEY unique_attendance (student_id, attendance_date)
);

-- Exam Types Table
CREATE TABLE IF NOT EXISTS exam_types (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL UNIQUE,
  code VARCHAR(20) NOT NULL UNIQUE,
  description TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Exams Table
CREATE TABLE IF NOT EXISTS exams (
  id INT PRIMARY KEY AUTO_INCREMENT,
  exam_type_id INT NOT NULL,
  class_id INT NOT NULL,
  section_id INT NOT NULL,
  subject_id INT NOT NULL,
  exam_date DATE NOT NULL,
  total_marks INT DEFAULT 100,
  passing_marks INT DEFAULT 40,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (exam_type_id) REFERENCES exam_types(id),
  FOREIGN KEY (class_id) REFERENCES classes(id),
  FOREIGN KEY (section_id) REFERENCES sections(id),
  FOREIGN KEY (subject_id) REFERENCES subjects(id)
);

-- Marks Table
CREATE TABLE IF NOT EXISTS marks (
  id INT PRIMARY KEY AUTO_INCREMENT,
  student_id INT NOT NULL,
  exam_id INT NOT NULL,
  subject_id INT NOT NULL,
  marks_obtained INT NOT NULL,
  total_marks INT NOT NULL,
  grade VARCHAR(2),
  gpa DECIMAL(3, 2),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  FOREIGN KEY (exam_id) REFERENCES exams(id) ON DELETE CASCADE,
  FOREIGN KEY (subject_id) REFERENCES subjects(id),
  UNIQUE KEY unique_marks (student_id, exam_id, subject_id)
);

-- Fees Table
CREATE TABLE IF NOT EXISTS fees (
  id INT PRIMARY KEY AUTO_INCREMENT,
  student_id INT NOT NULL,
  academic_year VARCHAR(20) NOT NULL,
  month VARCHAR(50) NOT NULL,
  amount DECIMAL(10, 2) NOT NULL,
  paid_amount DECIMAL(10, 2) DEFAULT 0,
  payment_status ENUM('unpaid', 'partial', 'paid') DEFAULT 'unpaid',
  due_date DATE NOT NULL,
  paid_date DATE,
  transaction_id VARCHAR(100),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  INDEX idx_student (student_id),
  INDEX idx_status (payment_status)
);

-- Online Admissions Table
CREATE TABLE IF NOT EXISTS online_admissions (
  id INT PRIMARY KEY AUTO_INCREMENT,
  application_number VARCHAR(50) UNIQUE NOT NULL,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  email VARCHAR(100) NOT NULL,
  phone VARCHAR(20) NOT NULL,
  date_of_birth DATE NOT NULL,
  gender ENUM('male', 'female', 'other') NOT NULL,
  class_id INT NOT NULL,
  father_name VARCHAR(100) NOT NULL,
  mother_name VARCHAR(100) NOT NULL,
  guardian_name VARCHAR(100),
  address TEXT NOT NULL,
  city VARCHAR(50),
  state VARCHAR(50),
  postal_code VARCHAR(20),
  registration_fee DECIMAL(10, 2) DEFAULT 500,
  bkash_number VARCHAR(20),
  transaction_id VARCHAR(100),
  payment_status ENUM('unpaid', 'pending', 'verified') DEFAULT 'unpaid',
  application_status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
  applied_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  approved_date DATETIME,
  approved_by INT,
  rejection_reason TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (class_id) REFERENCES classes(id),
  FOREIGN KEY (approved_by) REFERENCES users(id),
  INDEX idx_status (application_status),
  INDEX idx_payment (payment_status)
);

-- Certificates Table
CREATE TABLE IF NOT EXISTS certificates (
  id INT PRIMARY KEY AUTO_INCREMENT,
  student_id INT NOT NULL,
  certificate_type ENUM('character', 'transfer', 'conduct') NOT NULL,
  generated_date DATETIME,
  issue_date DATE,
  issued_by INT,
  file_path VARCHAR(255),
  status ENUM('draft', 'generated', 'issued') DEFAULT 'draft',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  FOREIGN KEY (issued_by) REFERENCES users(id),
  INDEX idx_type (certificate_type)
);

-- ID Cards Table
CREATE TABLE IF NOT EXISTS id_cards (
  id INT PRIMARY KEY AUTO_INCREMENT,
  student_id INT NOT NULL,
  card_number VARCHAR(50) UNIQUE NOT NULL,
  issue_date DATE NOT NULL,
  expiry_date DATE,
  file_path VARCHAR(255),
  printed INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  INDEX idx_card_number (card_number)
);

-- Sessions Table
CREATE TABLE IF NOT EXISTS sessions (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  session_token VARCHAR(255) UNIQUE NOT NULL,
  ip_address VARCHAR(45),
  user_agent TEXT,
  expires_at DATETIME NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_user (user_id),
  INDEX idx_expires (expires_at)
);

-- Activity Log Table
CREATE TABLE IF NOT EXISTS activity_logs (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  action VARCHAR(100) NOT NULL,
  entity_type VARCHAR(50),
  entity_id INT,
  description TEXT,
  ip_address VARCHAR(45),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_user (user_id),
  INDEX idx_action (action)
);

-- Insert Default Exam Types
INSERT INTO exam_types (name, code) VALUES
('Half-Yearly Exam', 'HY'),
('Final Exam', 'FE'),
('First Terminal', 'FT'),
('Second Terminal', 'ST');

-- Insert Default Classes
INSERT INTO classes (name, numeric_value) VALUES
('Class I', 1),
('Class II', 2),
('Class III', 3),
('Class IV', 4),
('Class V', 5),
('Class VI', 6),
('Class VII', 7),
('Class VIII', 8),
('Class IX', 9),
('Class X', 10);

-- Insert Default Sections
INSERT INTO sections (class_id, name) VALUES
(1, 'A'), (1, 'B'), (1, 'C'),
(2, 'A'), (2, 'B'), (2, 'C'),
(3, 'A'), (3, 'B'), (3, 'C'),
(4, 'A'), (4, 'B'), (4, 'C'),
(5, 'A'), (5, 'B'), (5, 'C'),
(6, 'A'), (6, 'B'), (6, 'C'),
(7, 'A'), (7, 'B'), (7, 'C'),
(8, 'A'), (8, 'B'), (8, 'C'),
(9, 'A'), (9, 'B'), (9, 'C'),
(10, 'A'), (10, 'B'), (10, 'C');

-- Insert Default Subjects
INSERT INTO subjects (name, code) VALUES
('English', 'ENG'),
('Mathematics', 'MATH'),
('Science', 'SCI'),
('Social Studies', 'SS'),
('Physical Education', 'PE'),
('Information Technology', 'IT'),
('Bengali', 'BNG'),
('History', 'HIST'),
('Geography', 'GEO'),
('Art', 'ART');