ALTER TABLE users
  ADD COLUMN phone VARCHAR(20) DEFAULT NULL,
  ADD COLUMN is_active TINYINT(1) DEFAULT 1,
  ADD COLUMN last_login DATETIME DEFAULT NULL,
  ADD COLUMN photo VARCHAR(255) DEFAULT NULL;

ALTER TABLE students
  ADD COLUMN father_name VARCHAR(100) DEFAULT NULL,
  ADD COLUMN father_phone VARCHAR(20) DEFAULT NULL,
  ADD COLUMN mother_name VARCHAR(100) DEFAULT NULL,
  ADD COLUMN mother_phone VARCHAR(20) DEFAULT NULL,
  ADD COLUMN father_email VARCHAR(100) DEFAULT NULL;

ALTER TABLE scores
  ADD COLUMN raw_class_score DECIMAL(5,2) DEFAULT NULL,
  ADD COLUMN raw_exam_score DECIMAL(5,2) DEFAULT NULL,
  ADD COLUMN weighted_class_score DECIMAL(5,2) DEFAULT NULL,
  ADD COLUMN weighted_exam_score DECIMAL(5,2) DEFAULT NULL;

ALTER TABLE report_details
  ADD COLUMN closing_date DATE DEFAULT NULL,
  ADD COLUMN reopening_date DATE DEFAULT NULL;

ALTER TABLE users
  MODIFY COLUMN role ENUM(
    'admin','headteacher','accountant','cashier',
    'registrar','supervisor','director','tutor'
  ) NOT NULL DEFAULT 'tutor';

CREATE TABLE IF NOT EXISTS staff (
  id INT AUTO_INCREMENT PRIMARY KEY,
  staff_id VARCHAR(20) UNIQUE,
  full_name VARCHAR(150) NOT NULL,
  reg_no VARCHAR(50),
  gender ENUM('Male','Female','Other') DEFAULT NULL,
  date_of_birth DATE DEFAULT NULL,
  nationality VARCHAR(50) DEFAULT NULL,
  religion VARCHAR(50) DEFAULT NULL,
  marital_status VARCHAR(20) DEFAULT NULL,
  phone VARCHAR(20) DEFAULT NULL,
  email VARCHAR(100) DEFAULT NULL,
  address TEXT DEFAULT NULL,
  photo VARCHAR(255) DEFAULT NULL,
  role VARCHAR(50) DEFAULT NULL,
  department VARCHAR(100) DEFAULT NULL,
  academic_qualification VARCHAR(100) DEFAULT NULL,
  professional_qualification VARCHAR(100) DEFAULT NULL,
  date_joined DATE DEFAULT NULL,
  employment_type VARCHAR(50) DEFAULT NULL,
  bank_name VARCHAR(100) DEFAULT NULL,
  bank_account VARCHAR(50) DEFAULT NULL,
  bank_branch VARCHAR(100) DEFAULT NULL,
  next_of_kin_name VARCHAR(150) DEFAULT NULL,
  next_of_kin_phone VARCHAR(20) DEFAULT NULL,
  next_of_kin_relation VARCHAR(50) DEFAULT NULL,
  status ENUM('active','inactive') DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS class_subjects (
  id INT AUTO_INCREMENT PRIMARY KEY,
  class_name VARCHAR(50) NOT NULL,
  subject_id INT NOT NULL,
  academic_year VARCHAR(20) NOT NULL,
  term VARCHAR(20) NOT NULL,
  teacher_id INT DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY unique_class_subject (class_name, subject_id, academic_year, term),
  FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS grade_boundaries (
  id INT AUTO_INCREMENT PRIMARY KEY,
  class_name VARCHAR(50) DEFAULT NULL,
  grade VARCHAR(5) NOT NULL,
  min_score DECIMAL(5,2) NOT NULL,
  max_score DECIMAL(5,2) NOT NULL,
  remark VARCHAR(100) DEFAULT NULL,
  academic_year VARCHAR(20) DEFAULT NULL,
  term VARCHAR(20) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS sms_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  recipient VARCHAR(20) NOT NULL,
  message TEXT NOT NULL,
  status ENUM('sent','failed','pending') DEFAULT 'pending',
  gateway_response TEXT DEFAULT NULL,
  sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS user_activities (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  action VARCHAR(100) NOT NULL,
  details TEXT DEFAULT NULL,
  ip_address VARCHAR(45) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS weekly_feeding_payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  student_id INT NOT NULL,
  week_start DATE NOT NULL,
  week_end DATE NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  paid_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  recorded_by INT DEFAULT NULL,
  notes TEXT DEFAULT NULL,
  FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE staff
  ADD COLUMN class_taught VARCHAR(100) DEFAULT NULL,
  ADD COLUMN present_rank VARCHAR(100) DEFAULT NULL,
  ADD COLUMN date_promoted_to_present_rank DATE DEFAULT NULL,
  ADD COLUMN date_of_first_appointment DATE DEFAULT NULL,
  ADD COLUMN year_of_certification VARCHAR(20) DEFAULT NULL,
  ADD COLUMN age INT DEFAULT NULL,
  ADD COLUMN sex ENUM('Male','Female','Other') DEFAULT NULL,
  ADD COLUMN place_of_birth VARCHAR(100) DEFAULT NULL,
  ADD COLUMN residential_address TEXT DEFAULT NULL,
  ADD COLUMN institutions_attended TEXT DEFAULT NULL,
  ADD COLUMN teaching_experience TEXT DEFAULT NULL,
  ADD COLUMN previous_schools TEXT DEFAULT NULL,
  ADD COLUMN date_posted_to_present_station DATE DEFAULT NULL,
  ADD COLUMN expected_retirement_date DATE DEFAULT NULL,
  ADD COLUMN tel_no VARCHAR(20) DEFAULT NULL,
  ADD COLUMN religious_denomination VARCHAR(50) DEFAULT NULL,
  ADD COLUMN languages_spoken VARCHAR(255) DEFAULT NULL,
  ADD COLUMN next_of_kin VARCHAR(150) DEFAULT NULL,
  ADD COLUMN digital_address VARCHAR(100) DEFAULT NULL,
  ADD COLUMN ssnit_number VARCHAR(50) DEFAULT NULL,
  ADD COLUMN district VARCHAR(100) DEFAULT NULL,
  ADD COLUMN tin_number VARCHAR(50) DEFAULT NULL,
  ADD COLUMN region VARCHAR(100) DEFAULT NULL,
  ADD COLUMN hometown VARCHAR(100) DEFAULT NULL,
  ADD COLUMN photo_path VARCHAR(255) DEFAULT NULL,
  ADD COLUMN date_of_employment DATE DEFAULT NULL,
  ADD COLUMN salary DECIMAL(10,2) DEFAULT NULL,
  ADD COLUMN class_assigned VARCHAR(100) DEFAULT NULL,
  ADD COLUMN subject_to_teach VARCHAR(100) DEFAULT NULL,
  ADD COLUMN approved_by VARCHAR(150) DEFAULT NULL;

ALTER TABLE class_subjects
  ADD COLUMN class_score_percent INT DEFAULT 50,
  ADD COLUMN exam_score_percent INT DEFAULT 50,
  ADD COLUMN max_class_score DECIMAL(5,2) DEFAULT 50,
  ADD COLUMN max_exam_score DECIMAL(5,2) DEFAULT 50,
  ADD COLUMN is_compulsory ENUM('yes','no') DEFAULT 'yes',
  ADD COLUMN display_order INT DEFAULT 0;

ALTER TABLE sms_logs
  ADD COLUMN phone VARCHAR(20) DEFAULT NULL,
  ADD COLUMN created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE user_activities
  ADD COLUMN description TEXT DEFAULT NULL,
  ADD COLUMN user_agent TEXT DEFAULT NULL;

ALTER TABLE weekly_feeding_payments
  ADD COLUMN payment_id INT DEFAULT NULL,
  ADD COLUMN week_start_date DATE DEFAULT NULL,
  ADD COLUMN week_end_date DATE DEFAULT NULL,
  ADD COLUMN amount_paid DECIMAL(10,2) DEFAULT NULL,
  ADD COLUMN sms_sent TINYINT(1) DEFAULT 0;

INSERT IGNORE INTO grade_boundaries
  (grade, min_score, max_score, remark, class_name, academic_year, term)
VALUES
  ('A+', 90, 100, 'Excellent', NULL, NULL, NULL),
  ('A', 80, 89, 'Very Good', NULL, NULL, NULL),
  ('B+', 75, 79, 'Good', NULL, NULL, NULL),
  ('B', 65, 74, 'Above Average', NULL, NULL, NULL),
  ('C+', 60, 64, 'Average', NULL, NULL, NULL),
  ('C', 50, 59, 'Pass', NULL, NULL, NULL),
  ('D', 40, 49, 'Below Pass', NULL, NULL, NULL),
  ('F', 0, 39, 'Fail', NULL, NULL, NULL);
