/*!40014 SET FOREIGN_KEY_CHECKS=0*/;
/*!40101 SET NAMES binary*/;
CREATE TABLE `applications` (
  `id` int NOT NULL AUTO_INCREMENT,
  `reference_number` varchar(50) NOT NULL,
  `full_name` varchar(150) NOT NULL,
  `dob` date NOT NULL,
  `gender` enum('Male','Female') NOT NULL,
  `id_number` varchar(50) NOT NULL,
  `phone` varchar(20) NOT NULL,
  `email` varchar(100) NOT NULL,
  `address` text DEFAULT NULL,
  `kcse_year` int NOT NULL,
  `kcse_grade` varchar(5) NOT NULL,
  `prev_school` varchar(200) DEFAULT NULL,
  `department_id` int NOT NULL,
  `course_id` int NOT NULL,
  `preferred_intake` varchar(50) NOT NULL,
  `study_mode` varchar(50) NOT NULL,
  `kcse_cert_path` varchar(500) DEFAULT NULL,
  `id_doc_path` varchar(500) NOT NULL,
  `photo_path` varchar(500) DEFAULT NULL,
  `status` enum('pending','reviewed','accepted','rejected') DEFAULT 'pending',
  `submitted_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `kcse_original_name` varchar(255) DEFAULT NULL,
  `id_original_name` varchar(255) DEFAULT NULL,
  `photo_original_name` varchar(255) DEFAULT NULL,
  `kcpe_cert_path` varchar(500) DEFAULT NULL,
  `leaving_cert_path` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
  KEY `idx_reference` (`reference_number`),
  KEY `idx_status` (`status`),
  KEY `idx_submitted` (`submitted_at`),
  UNIQUE KEY `reference_number` (`reference_number`),
  KEY `fk_1` (`department_id`),
  KEY `fk_2` (`course_id`),
  CONSTRAINT `fk_1` FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_2` FOREIGN KEY (`course_id`) REFERENCES `courses` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=270001;