-- ====================================================================
-- SOCHE TECHNICAL COLLEGE MANAGEMENT SYSTEM - DATABASE SCHEMA
-- Ministry of Labour & Manpower Development, Republic of Malawi
-- Target: MySQL 8.0+ / MariaDB 10.5+
-- Session: July - December 2026 Intake (Semester Opens: 13th July, 2026)
-- ====================================================================

CREATE DATABASE IF NOT EXISTS `soche_college_db` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `soche_college_db`;

-- 1. USERS TABLE
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `full_name` VARCHAR(150) NOT NULL,
  `email` VARCHAR(150) NOT NULL UNIQUE,
  `password_hash` VARCHAR(255) NOT NULL,
  `role` ENUM('admin', 'teacher', 'student', 'parent') NOT NULL DEFAULT 'student',
  `phone` VARCHAR(30) NULL,
  `title` VARCHAR(100) NULL,
  `department` VARCHAR(100) NULL,
  `linked_student_id` VARCHAR(50) NULL,
  `status` ENUM('active', 'inactive', 'suspended') NOT NULL DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- 2. ACCREDITED COURSES (JULY - DECEMBER 2026 INTAKE ADVERT)
CREATE TABLE IF NOT EXISTS `courses` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `code` VARCHAR(30) NOT NULL UNIQUE,
  `title` VARCHAR(190) NOT NULL,
  `exam_board` VARCHAR(100) NOT NULL, -- ICAM, IOBM, City and Guilds, ICM, ABE, ABMA, CIPS, NCIC
  `department` VARCHAR(100) NOT NULL,
  `level` VARCHAR(100) NOT NULL,
  `duration` VARCHAR(50) NOT NULL DEFAULT '1 Year',
  `fee_type` ENUM('per term', 'per semester', '3 months') NOT NULL DEFAULT 'per semester',
  `tuition_fee` DECIMAL(12,2) NOT NULL,
  `day_release` TINYINT(1) NOT NULL DEFAULT 1,
  `weekend` TINYINT(1) NOT NULL DEFAULT 0,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB;

-- 3. APPLICATIONS (Form STC/APPLFORM/01/2025)
CREATE TABLE IF NOT EXISTS `applications` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `ref_number` VARCHAR(50) NOT NULL UNIQUE,
  `surname` VARCHAR(100) NOT NULL,
  `first_names` VARCHAR(150) NOT NULL,
  `dob` DATE NOT NULL,
  `nationality` VARCHAR(100) NOT NULL DEFAULT 'Malawian',
  `district_of_origin` VARCHAR(100) NOT NULL,
  `traditional_authority` VARCHAR(100) NOT NULL,
  `village` VARCHAR(100) NOT NULL,
  `phone` VARCHAR(30) NOT NULL,
  `gender` ENUM('MALE', 'FEMALE') NOT NULL,
  `guardian_name` VARCHAR(150) NOT NULL,
  `guardian_phone` VARCHAR(30) NOT NULL,
  `guardian_email` VARCHAR(150) NULL,
  `residential_address` TEXT NOT NULL,
  `postal_address` VARCHAR(150) NOT NULL,
  `previous_schools` TEXT NOT NULL,
  `english_grade` VARCHAR(20) NOT NULL,
  `math_grade` VARCHAR(20) NOT NULL,
  `highest_qualification` VARCHAR(100) NOT NULL,
  `first_choice_course_id` INT UNSIGNED NOT NULL,
  `second_choice_course_id` INT UNSIGNED NOT NULL,
  `study_mode` ENUM('Day-Release', 'Weekend') NOT NULL DEFAULT 'Day-Release',
  `boarding_requested` TINYINT(1) NOT NULL DEFAULT 0,
  `has_disability` TINYINT(1) NOT NULL DEFAULT 0,
  `disability_explanation` TEXT NULL,
  `bank_name` VARCHAR(100) NOT NULL DEFAULT 'National Bank of Malawi',
  `bank_branch` VARCHAR(100) NOT NULL DEFAULT 'Customs Road Service Centre',
  `bank_account_number` VARCHAR(50) NOT NULL DEFAULT '1003452219',
  `deposit_slip_ref` VARCHAR(100) NOT NULL,
  `application_fee_paid` DECIMAL(10,2) NOT NULL DEFAULT 10000.00,
  `status` ENUM('Pending Verification', 'Approved', 'Rejected', 'Enrolled') NOT NULL DEFAULT 'Pending Verification',
  `accounts_officer_stamp` VARCHAR(150) NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`first_choice_course_id`) REFERENCES `courses`(`id`),
  FOREIGN KEY (`second_choice_course_id`) REFERENCES `courses`(`id`)
) ENGINE=InnoDB;

-- 4. FEES & RESULTS LOCKOUT ENGINE
CREATE TABLE IF NOT EXISTS `fees` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `student_id` VARCHAR(50) NOT NULL UNIQUE,
  `student_name` VARCHAR(150) NOT NULL,
  `course_id` INT UNSIGNED NOT NULL,
  `total_tuition` DECIMAL(12,2) NOT NULL,
  `amount_paid` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `balance_due` DECIMAL(12,2) GENERATED ALWAYS AS (`total_tuition` - `amount_paid`) STORED,
  `is_locked` TINYINT(1) GENERATED ALWAYS AS (IF(`total_tuition` > `amount_paid`, 1, 0)) STORED,
  `status` ENUM('Fully Cleared', 'Partial (70% Paid)', 'Unpaid Balance', 'Pending Bank Slip Review') NOT NULL DEFAULT 'Unpaid Balance'
) ENGINE=InnoDB;

-- 5. ACADEMIC GRADES (CAT 30% + Midterm 20% + Practicals 10% + Exam 40% = 100%)
CREATE TABLE IF NOT EXISTS `grades` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `student_id` VARCHAR(50) NOT NULL,
  `subject_name` VARCHAR(150) NOT NULL,
  `exam_board` VARCHAR(100) NOT NULL,
  `coursework_score` DECIMAL(5,2) NOT NULL, -- /30
  `midterm_score` DECIMAL(5,2) NOT NULL,    -- /20
  `practical_score` DECIMAL(5,2) NOT NULL,  -- /10
  `final_exam_score` DECIMAL(5,2) NOT NULL, -- /40
  `total_score` DECIMAL(5,2) GENERATED ALWAYS AS (`coursework_score` + `midterm_score` + `practical_score` + `final_exam_score`) STORED,
  `letter_grade` VARCHAR(5) NOT NULL,
  `gpa` DECIMAL(3,2) NOT NULL,
  `remarks` VARCHAR(150) NOT NULL
) ENGINE=InnoDB;

-- ====================================================================
-- SEED DATA: OFFICIAL JULY - DECEMBER 2026 INTAKE COURSES
-- ====================================================================
INSERT INTO `courses` (`id`, `code`, `title`, `exam_board`, `department`, `level`, `duration`, `fee_type`, `tuition_fee`, `day_release`, `weekend`) VALUES
-- 1 Year Programs (Fees Are Per Term)
(1, 'ICAM1001', 'Financial Accounting', 'ICAM', 'Business & Finance', 'Certificate & Diploma', '1 Year', 'per term', 140000.00, 1, 1),
(2, 'IOBM1001', 'Banking', 'IOBM', 'Business & Finance', 'Certificate', '1 Year', 'per term', 180000.00, 1, 0),
(3, 'CG1001', 'Food Production', 'City and Guilds', 'Hospitality & Services', 'Diploma & Advanced Diploma', '1 Year', 'per term', 230000.00, 1, 0),
(4, 'CG1002', 'Electrical and Electronics Engineering', 'City and Guilds', 'Engineering & ICT', 'Certificate, Diploma & Advanced Diploma', '1 Year', 'per term', 240000.00, 1, 1),
(5, 'CG1003', 'Textile and Fashion Design', 'City and Guilds', 'Hospitality & Services', 'Certificate, Diploma & Advanced Diploma', '1 Year', 'per term', 240000.00, 1, 0),
(6, 'ICM1002', 'Project Management (Weekend Only)', 'ICM', 'Management', 'Advanced Diploma', '1 Year', 'per term', 315000.00, 0, 1),

-- Fees Are Per Semester
(7, 'ABE1001', 'Business Management', 'ABE', 'Business & Finance', 'Level 4, Level 5 & Level 6', '1 Year', 'per semester', 180000.00, 1, 1),
(8, 'ABE1002', 'Human Resources Management', 'ABE', 'Management', 'Level 4, Level 5 & Level 6', '1 Year', 'per semester', 180000.00, 1, 1),
(9, 'ABE1003', 'Marketing Management', 'ABE', 'Business & Finance', 'Level 4, Level 5 & Level 6', '1 Year', 'per semester', 180000.00, 1, 1),
(10, 'ABMA1001', 'Community Development', 'ABMA', 'Management', 'Level 4 Diploma, Level 5 Diploma & Level 6 Diploma', '1 Year', 'per semester', 180000.00, 1, 1),
(11, 'ABMA1002', 'Journalism and Media Studies', 'ABMA', 'Management', 'Level 4 Diploma, Level 5 Diploma & Level 6 Diploma', '1 Year', 'per semester', 180000.00, 1, 1),
(12, 'ABMA1003', 'Shipping and Logistics', 'ABMA', 'Management', 'Level 4 Diploma, Level 5 Diploma & Level 6 Diploma', '1 Year', 'per semester', 180000.00, 1, 1),
(13, 'ABMA1004', 'Professional Procurement and Supply Chain', 'ABMA', 'Management', 'Level 4 Diploma, Level 5 Diploma & Level 6 Diploma', '1 Year', 'per semester', 180000.00, 1, 1),
(14, 'ABMA1005', 'Professional Project Management Diploma', 'ABMA', 'Management', 'Level 4 Diploma, Level 5 Diploma & Level 6 Diploma', '1 Year', 'per semester', 180000.00, 1, 1),
(15, 'ABMA1006', 'ICT - Computer Engineering', 'ABMA', 'Engineering & ICT', 'Level 4 Diploma, Level 5 Diploma & Level 6 Diploma', '1 Year', 'per semester', 180000.00, 1, 1),
(16, 'ABMA1007', 'Public Health Management', 'ABMA', 'Management', 'Level 4 Diploma, Level 5 Diploma & Level 6 Diploma', '1 Year', 'per semester', 180000.00, 1, 1),
(17, 'ABMA1008', 'Business Management (Weekend only)', 'ABMA', 'Business & Finance', 'Level 4 Diploma, Level 5 Diploma & Level 6 Diploma', '1 Year', 'per semester', 180000.00, 0, 1),
(18, 'ABMA1009', 'Human Resource Management (Weekend only)', 'ABMA', 'Management', 'Level 4 Diploma, Level 5 Diploma & Level 6 Diploma', '1 Year', 'per semester', 180000.00, 0, 1),
(19, 'CG1004', 'ICT - Systems Support', 'City and Guilds', 'Engineering & ICT', 'Diploma & Adv. Diploma', '1 Year', 'per semester', 180000.00, 1, 1),
(20, 'CIPS1001', 'Procurement and Supply Management', 'CIPS', 'Management', 'Certificate, Advanced Certificate & Diploma', '1 Year', 'per semester', 180000.00, 1, 1),
(21, 'ICM1001', 'Hospitality Management', 'ICM', 'Hospitality & Services', 'Level 4 Diploma, Level 5 Diploma & Level 6 Diploma', '1 Year', 'per semester', 195000.00, 1, 0),
(22, 'CCM1001', 'Construction Management (For Contractors)', 'NCIC', 'Management', 'Certificate (3 Months)', '3 Months', '3 months', 950000.00, 1, 1);

-- SEED FEES
INSERT INTO `fees` (`id`, `student_id`, `student_name`, `course_id`, `total_tuition`, `amount_paid`, `status`) VALUES
(1, 'STC/2026/ICT-108', 'Chimwemwe Phiri', 19, 180000.00, 126000.00, 'Partial (70% Paid)'),
(2, 'STC/2026/ICAM-042', 'Kondwani Banda', 1, 140000.00, 140000.00, 'Fully Cleared');