SET NAMES utf8mb4;
SET time_zone = '+06:00';

CREATE TABLE IF NOT EXISTS edu_settings (
  setting_key VARCHAR(100) PRIMARY KEY,
  setting_value TEXT NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_academic_years (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  year_name VARCHAR(50) NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  is_current TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_edu_year_name (year_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_classes (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  class_name VARCHAR(100) NOT NULL,
  numeric_order INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  UNIQUE KEY uq_edu_class_name (class_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_sections (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  class_id INT UNSIGNED NOT NULL,
  section_name VARCHAR(100) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  UNIQUE KEY uq_edu_section (class_id, section_name),
  CONSTRAINT fk_edu_section_class FOREIGN KEY (class_id) REFERENCES edu_classes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_teachers (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  employee_code VARCHAR(50) NOT NULL,
  full_name VARCHAR(150) NOT NULL,
  gender ENUM('male','female','other') NOT NULL DEFAULT 'other',
  phone VARCHAR(30) DEFAULT NULL,
  email VARCHAR(150) DEFAULT NULL,
  designation VARCHAR(100) DEFAULT NULL,
  joining_date DATE DEFAULT NULL,
  address TEXT DEFAULT NULL,
  photo_path VARCHAR(255) DEFAULT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_edu_teacher_code (employee_code),
  KEY idx_edu_teacher_name (full_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  teacher_id INT UNSIGNED DEFAULT NULL,
  full_name VARCHAR(150) NOT NULL,
  username VARCHAR(100) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('admin','teacher','accountant') NOT NULL DEFAULT 'teacher',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  last_login_at DATETIME DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_edu_username (username),
  UNIQUE KEY uq_edu_user_teacher (teacher_id),
  CONSTRAINT fk_edu_user_teacher FOREIGN KEY (teacher_id) REFERENCES edu_teachers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_teacher_assignments (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  academic_year_id INT UNSIGNED NOT NULL,
  teacher_id INT UNSIGNED NOT NULL,
  class_id INT UNSIGNED NOT NULL,
  section_id INT UNSIGNED NOT NULL,
  subject_name VARCHAR(100) DEFAULT NULL,
  is_class_teacher TINYINT(1) NOT NULL DEFAULT 0,
  UNIQUE KEY uq_edu_teacher_assignment (academic_year_id, teacher_id, class_id, section_id, subject_name),
  CONSTRAINT fk_edu_assignment_year FOREIGN KEY (academic_year_id) REFERENCES edu_academic_years(id),
  CONSTRAINT fk_edu_assignment_teacher FOREIGN KEY (teacher_id) REFERENCES edu_teachers(id) ON DELETE CASCADE,
  CONSTRAINT fk_edu_assignment_class FOREIGN KEY (class_id) REFERENCES edu_classes(id) ON DELETE CASCADE,
  CONSTRAINT fk_edu_assignment_section FOREIGN KEY (section_id) REFERENCES edu_sections(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_students (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  admission_no VARCHAR(50) NOT NULL,
  student_name VARCHAR(150) NOT NULL,
  gender ENUM('male','female','other') NOT NULL DEFAULT 'other',
  date_of_birth DATE DEFAULT NULL,
  blood_group VARCHAR(10) DEFAULT NULL,
  guardian_name VARCHAR(150) NOT NULL,
  guardian_relation VARCHAR(50) DEFAULT NULL,
  guardian_phone VARCHAR(30) NOT NULL,
  alternate_phone VARCHAR(30) DEFAULT NULL,
  address TEXT DEFAULT NULL,
  admission_date DATE DEFAULT NULL,
  photo_path VARCHAR(255) DEFAULT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_edu_admission_no (admission_no),
  KEY idx_edu_student_name (student_name),
  KEY idx_edu_guardian_phone (guardian_phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_enrollments (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  academic_year_id INT UNSIGNED NOT NULL,
  student_id INT UNSIGNED NOT NULL,
  class_id INT UNSIGNED NOT NULL,
  section_id INT UNSIGNED NOT NULL,
  roll_no VARCHAR(30) DEFAULT NULL,
  status ENUM('active','promoted','transferred','completed','inactive') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_edu_enrollment (academic_year_id, student_id),
  UNIQUE KEY uq_edu_roll (academic_year_id, section_id, roll_no),
  KEY idx_edu_enrollment_scope (academic_year_id, class_id, section_id, status),
  CONSTRAINT fk_edu_enrollment_year FOREIGN KEY (academic_year_id) REFERENCES edu_academic_years(id),
  CONSTRAINT fk_edu_enrollment_student FOREIGN KEY (student_id) REFERENCES edu_students(id) ON DELETE CASCADE,
  CONSTRAINT fk_edu_enrollment_class FOREIGN KEY (class_id) REFERENCES edu_classes(id),
  CONSTRAINT fk_edu_enrollment_section FOREIGN KEY (section_id) REFERENCES edu_sections(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_attendance (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  enrollment_id INT UNSIGNED NOT NULL,
  attendance_date DATE NOT NULL,
  status ENUM('present','absent','late','excused') NOT NULL DEFAULT 'present',
  check_in_time TIME DEFAULT NULL,
  remarks VARCHAR(255) DEFAULT NULL,
  marked_by INT UNSIGNED NOT NULL,
  marked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_edu_daily_attendance (enrollment_id, attendance_date),
  KEY idx_edu_attendance_date (attendance_date, status),
  CONSTRAINT fk_edu_attendance_enrollment FOREIGN KEY (enrollment_id) REFERENCES edu_enrollments(id) ON DELETE CASCADE,
  CONSTRAINT fk_edu_attendance_user FOREIGN KEY (marked_by) REFERENCES edu_users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_fee_types (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  fee_name VARCHAR(120) NOT NULL,
  frequency ENUM('one_time','monthly','quarterly','annual','custom') NOT NULL DEFAULT 'monthly',
  default_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  UNIQUE KEY uq_edu_fee_name (fee_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_fee_invoices (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  invoice_no VARCHAR(40) NOT NULL,
  enrollment_id INT UNSIGNED NOT NULL,
  fee_type_id INT UNSIGNED NOT NULL,
  billing_month CHAR(7) DEFAULT NULL,
  issue_date DATE NOT NULL,
  due_date DATE NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  discount DECIMAL(12,2) NOT NULL DEFAULT 0,
  fine DECIMAL(12,2) NOT NULL DEFAULT 0,
  status ENUM('unpaid','partial','paid','waived','cancelled') NOT NULL DEFAULT 'unpaid',
  notes VARCHAR(255) DEFAULT NULL,
  created_by INT UNSIGNED NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_edu_invoice_no (invoice_no),
  KEY idx_edu_invoice_student (enrollment_id, status, due_date),
  CONSTRAINT fk_edu_invoice_enrollment FOREIGN KEY (enrollment_id) REFERENCES edu_enrollments(id),
  CONSTRAINT fk_edu_invoice_type FOREIGN KEY (fee_type_id) REFERENCES edu_fee_types(id),
  CONSTRAINT fk_edu_invoice_user FOREIGN KEY (created_by) REFERENCES edu_users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_fee_payments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  invoice_id BIGINT UNSIGNED NOT NULL,
  receipt_no VARCHAR(40) NOT NULL,
  payment_date DATE NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  payment_method ENUM('cash','bank','mobile_banking','cheque','other') NOT NULL DEFAULT 'cash',
  reference_no VARCHAR(100) DEFAULT NULL,
  notes VARCHAR(255) DEFAULT NULL,
  received_by INT UNSIGNED NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_edu_receipt_no (receipt_no),
  KEY idx_edu_payment_date (payment_date),
  CONSTRAINT fk_edu_payment_invoice FOREIGN KEY (invoice_id) REFERENCES edu_fee_invoices(id),
  CONSTRAINT fk_edu_payment_user FOREIGN KEY (received_by) REFERENCES edu_users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_audit_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  action_name VARCHAR(80) NOT NULL,
  entity_type VARCHAR(80) NOT NULL,
  entity_id BIGINT DEFAULT NULL,
  details_json JSON DEFAULT NULL,
  ip_address VARCHAR(45) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY idx_edu_audit_date (created_at),
  CONSTRAINT fk_edu_audit_user FOREIGN KEY (user_id) REFERENCES edu_users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO edu_settings (setting_key, setting_value) VALUES
('institution_name', 'TUS EDU School'), ('institution_address', 'Khagrachari, Bangladesh'),
('institution_phone', ''), ('currency', 'BDT'), ('attendance_start_time', '09:00');

INSERT IGNORE INTO edu_academic_years (year_name, start_date, end_date, is_current)
VALUES ('2026', '2026-01-01', '2026-12-31', 1);

INSERT IGNORE INTO edu_classes (class_name, numeric_order) VALUES
('Play', 1), ('Nursery', 2), ('Class 1', 3), ('Class 2', 4), ('Class 3', 5),
('Class 4', 6), ('Class 5', 7), ('Class 6', 8), ('Class 7', 9), ('Class 8', 10), ('Class 9', 11), ('Class 10', 12);

INSERT IGNORE INTO edu_sections (class_id, section_name)
SELECT id, 'A' FROM edu_classes;

INSERT IGNORE INTO edu_fee_types (fee_name, frequency, default_amount) VALUES
('Admission Fee', 'one_time', 0), ('Monthly Tuition', 'monthly', 0),
('Examination Fee', 'quarterly', 0), ('Transport Fee', 'monthly', 0), ('Other Fee', 'custom', 0);