-- ============================================================
-- Migration: Online Application Portal
-- Run this against an existing database. New installs should use
-- the updated database/schema.sql instead.
-- ============================================================

CREATE TABLE IF NOT EXISTS `applications` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `first_name` VARCHAR(100) NOT NULL,
  `last_name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(150) NOT NULL,
  `phone` VARCHAR(20) DEFAULT NULL,
  `gender` VARCHAR(20) DEFAULT NULL,
  `date_of_birth` DATE DEFAULT NULL,
  `national_id` VARCHAR(30) DEFAULT NULL,
  `program_id` INT UNSIGNED NOT NULL,
  `previous_school` VARCHAR(200) DEFAULT NULL,
  `highest_qualification` VARCHAR(150) DEFAULT NULL,
  `address` TEXT DEFAULT NULL,
  `next_of_kin_name` VARCHAR(150) DEFAULT NULL,
  `next_of_kin_phone` VARCHAR(20) DEFAULT NULL,
  `next_of_kin_relation` VARCHAR(60) DEFAULT NULL,
  `message` TEXT DEFAULT NULL,
  `nrc_scan` VARCHAR(255) DEFAULT NULL,
  `grade_12_results` VARCHAR(255) DEFAULT NULL,
  `status` ENUM('pending','under_review','accepted','rejected') NOT NULL DEFAULT 'pending',
  `reviewed_by` INT UNSIGNED DEFAULT NULL,
  `reviewed_at` DATETIME DEFAULT NULL,
  `rejection_reason` VARCHAR(255) DEFAULT NULL,
  `converted_student_id` INT UNSIGNED DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `fk_app_program` (`program_id`),
  KEY `fk_app_reviewer` (`reviewed_by`),
  KEY `fk_app_student` (`converted_student_id`),
  CONSTRAINT `fk_app_program` FOREIGN KEY (`program_id`) REFERENCES `programs` (`id`),
  CONSTRAINT `fk_app_reviewer` FOREIGN KEY (`reviewed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_app_student` FOREIGN KEY (`converted_student_id`) REFERENCES `students` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;