-- ============================================================
-- Migration: Registrar role, ID Type, Semester-based Fees,
--            Fee Adjustments
-- Run this against an existing database. New installs should use
-- the updated database/schema.sql instead.
-- ============================================================

-- 1. Registrar role
INSERT INTO `roles` (`name`, `slug`, `description`)
SELECT 'Registrar','registrar','Manages registration, applications and fee structures'
WHERE NOT EXISTS (SELECT 1 FROM `roles` WHERE `slug`='registrar');

-- 2. ID Type on applications + national_id already exists there
ALTER TABLE `applications`
  ADD COLUMN `id_type` ENUM('NRC','Passport') NOT NULL DEFAULT 'NRC' AFTER `date_of_birth`;

-- 3. ID Type + current semester on students
ALTER TABLE `students`
  ADD COLUMN `current_semester_id` INT UNSIGNED DEFAULT NULL AFTER `academic_year_id`,
  ADD COLUMN `id_type` ENUM('NRC','Passport') NOT NULL DEFAULT 'NRC' AFTER `date_of_birth`,
  ADD KEY `fk_student_semester` (`current_semester_id`),
  ADD CONSTRAINT `fk_student_semester` FOREIGN KEY (`current_semester_id`) REFERENCES `semesters` (`id`) ON DELETE SET NULL;

-- 4. Semester attribution on payments
ALTER TABLE `payments`
  ADD COLUMN `semester_id` INT UNSIGNED DEFAULT NULL AFTER `fee_structure_id`,
  ADD KEY `fk_pay_semester` (`semester_id`),
  ADD CONSTRAINT `fk_pay_semester` FOREIGN KEY (`semester_id`) REFERENCES `semesters` (`id`) ON DELETE SET NULL;

-- 5. Fee adjustments
CREATE TABLE IF NOT EXISTS `fee_adjustments` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `student_id` INT UNSIGNED NOT NULL,
  `semester_id` INT UNSIGNED NOT NULL,
  `amount` DECIMAL(12,2) NOT NULL COMMENT 'Positive = extra charge, Negative = discount/credit',
  `reason` VARCHAR(255) NOT NULL,
  `created_by` INT UNSIGNED NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `fk_adj_student` (`student_id`),
  KEY `fk_adj_semester` (`semester_id`),
  KEY `fk_adj_creator` (`created_by`),
  CONSTRAINT `fk_adj_student` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`),
  CONSTRAINT `fk_adj_semester` FOREIGN KEY (`semester_id`) REFERENCES `semesters` (`id`),
  CONSTRAINT `fk_adj_creator` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. Backfill: set every existing student's current_semester_id to the
--    academic year's current semester, so fee lookups work immediately.
UPDATE `students` s
JOIN `semesters` sem ON sem.academic_year_id = s.academic_year_id AND sem.is_current = 1
SET s.current_semester_id = sem.id
WHERE s.current_semester_id IS NULL;