-- =========================================================
-- SIMDATADIK
-- Schema Modul PPDB (Versi Modular Baru)
-- File: sql/schema_ppdb.sql
-- =========================================================
-- Catatan:
-- 1. Schema ini dirancang untuk terhubung dengan tabel inti yang sudah ada:
--    - schools
--    - users
--    - academic_years
--    - notifications (opsional dipakai di level aplikasi, tidak dibuat ulang di sini)
-- 2. File ini fokus pada tabel PPDB modular sesuai prompt.
-- 3. Gunakan pada database MySQL / MariaDB dengan engine InnoDB dan utf8mb4.
-- =========================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- =========================================================
-- 1. ppdb_batches
-- Gelombang / periode PPDB per sekolah
-- =========================================================
CREATE TABLE IF NOT EXISTS `ppdb_batches` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `school_id` BIGINT UNSIGNED NOT NULL,
    `academic_year_id` BIGINT UNSIGNED DEFAULT NULL,
    `name` VARCHAR(150) NOT NULL,
    `academic_year` VARCHAR(20) NOT NULL,
    `level` ENUM('SD','SMP','SMA','SMK','SLB','LAINNYA') NOT NULL DEFAULT 'LAINNYA',
    `registration_start` DATETIME NOT NULL,
    `registration_end` DATETIME NOT NULL,
    `verification_start` DATETIME DEFAULT NULL,
    `verification_end` DATETIME DEFAULT NULL,
    `announcement_date` DATETIME DEFAULT NULL,
    `re_registration_start` DATETIME DEFAULT NULL,
    `re_registration_end` DATETIME DEFAULT NULL,
    `status` ENUM('draft','active','closed','archived') NOT NULL DEFAULT 'draft',
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_ppdb_batches_school_id` (`school_id`),
    KEY `idx_ppdb_batches_academic_year_id` (`academic_year_id`),
    KEY `idx_ppdb_batches_status` (`status`),
    KEY `idx_ppdb_batches_registration_period` (`registration_start`, `registration_end`),
    CONSTRAINT `fk_ppdb_batches_school`
        FOREIGN KEY (`school_id`) REFERENCES `schools` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_ppdb_batches_academic_year`
        FOREIGN KEY (`academic_year_id`) REFERENCES `academic_years` (`id`)
        ON UPDATE CASCADE
        ON DELETE SET NULL,
    CONSTRAINT `chk_ppdb_batches_registration_period`
        CHECK (`registration_end` >= `registration_start`),
    CONSTRAINT `chk_ppdb_batches_verification_period`
        CHECK (
            `verification_start` IS NULL
            OR `verification_end` IS NULL
            OR `verification_end` >= `verification_start`
        ),
    CONSTRAINT `chk_ppdb_batches_reregistration_period`
        CHECK (
            `re_registration_start` IS NULL
            OR `re_registration_end` IS NULL
            OR `re_registration_end` >= `re_registration_start`
        )
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 2. ppdb_tracks
-- Jalur pendaftaran per batch
-- =========================================================
CREATE TABLE IF NOT EXISTS `ppdb_tracks` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `ppdb_batch_id` BIGINT UNSIGNED NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `quota` INT UNSIGNED NOT NULL DEFAULT 0,
    `description` TEXT DEFAULT NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uniq_ppdb_tracks_batch_name` (`ppdb_batch_id`, `name`),
    KEY `idx_ppdb_tracks_batch_id` (`ppdb_batch_id`),
    KEY `idx_ppdb_tracks_is_active` (`is_active`),
    CONSTRAINT `fk_ppdb_tracks_batch`
        FOREIGN KEY (`ppdb_batch_id`) REFERENCES `ppdb_batches` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 3. ppdb_requirements
-- Syarat / berkas per jalur
-- =========================================================
CREATE TABLE IF NOT EXISTS `ppdb_requirements` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `ppdb_track_id` BIGINT UNSIGNED NOT NULL,
    `name` VARCHAR(150) NOT NULL,
    `is_mandatory` TINYINT(1) NOT NULL DEFAULT 1,
    `description` TEXT DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uniq_ppdb_requirements_track_name` (`ppdb_track_id`, `name`),
    KEY `idx_ppdb_requirements_track_id` (`ppdb_track_id`),
    KEY `idx_ppdb_requirements_is_mandatory` (`is_mandatory`),
    CONSTRAINT `fk_ppdb_requirements_track`
        FOREIGN KEY (`ppdb_track_id`) REFERENCES `ppdb_tracks` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 4. ppdb_applicants
-- Data pendaftar
-- =========================================================
CREATE TABLE IF NOT EXISTS `ppdb_applicants` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `ppdb_batch_id` BIGINT UNSIGNED NOT NULL,
    `ppdb_track_id` BIGINT UNSIGNED NOT NULL,
    `registration_number` VARCHAR(50) NOT NULL,
    `user_id` BIGINT UNSIGNED DEFAULT NULL,
    `full_name` VARCHAR(150) NOT NULL,
    `nik` VARCHAR(20) DEFAULT NULL,
    `gender` ENUM('L','P') NOT NULL,
    `birth_place` VARCHAR(100) NOT NULL,
    `birth_date` DATE NOT NULL,
    `religion` VARCHAR(50) DEFAULT NULL,
    `address` TEXT NOT NULL,
    `village` VARCHAR(100) DEFAULT NULL,
    `district` VARCHAR(100) DEFAULT NULL,
    `city` VARCHAR(100) DEFAULT NULL,
    `province` VARCHAR(100) DEFAULT NULL,
    `postal_code` VARCHAR(10) DEFAULT NULL,
    `latitude` DECIMAL(10,7) DEFAULT NULL,
    `longitude` DECIMAL(10,7) DEFAULT NULL,
    `previous_school` VARCHAR(150) DEFAULT NULL,
    `father_name` VARCHAR(150) DEFAULT NULL,
    `mother_name` VARCHAR(150) DEFAULT NULL,
    `parent_phone` VARCHAR(50) DEFAULT NULL,
    `preferred_major` VARCHAR(100) DEFAULT NULL,
    `score_average` DECIMAL(5,2) DEFAULT NULL,
    `registration_status` ENUM('submitted','under_verification','verified','rejected','cancelled') NOT NULL DEFAULT 'submitted',
    `final_status` ENUM('pending','accepted','waitlisted','not_accepted') NOT NULL DEFAULT 'pending',
    `notes` TEXT DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uniq_ppdb_applicants_registration_number` (`registration_number`),
    KEY `idx_ppdb_applicants_batch_id` (`ppdb_batch_id`),
    KEY `idx_ppdb_applicants_track_id` (`ppdb_track_id`),
    KEY `idx_ppdb_applicants_user_id` (`user_id`),
    KEY `idx_ppdb_applicants_registration_status` (`registration_status`),
    KEY `idx_ppdb_applicants_final_status` (`final_status`),
    KEY `idx_ppdb_applicants_name` (`full_name`),
    KEY `idx_ppdb_applicants_nik` (`nik`),
    KEY `idx_ppdb_applicants_score_average` (`score_average`),
    CONSTRAINT `fk_ppdb_applicants_batch`
        FOREIGN KEY (`ppdb_batch_id`) REFERENCES `ppdb_batches` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_ppdb_applicants_track`
        FOREIGN KEY (`ppdb_track_id`) REFERENCES `ppdb_tracks` (`id`)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT `fk_ppdb_applicants_user`
        FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 5. ppdb_applicant_documents
-- Berkas yang diunggah pendaftar
-- =========================================================
CREATE TABLE IF NOT EXISTS `ppdb_applicant_documents` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `ppdb_applicant_id` BIGINT UNSIGNED NOT NULL,
    `requirement_id` BIGINT UNSIGNED NOT NULL,
    `file_path` VARCHAR(255) NOT NULL,
    `is_valid` TINYINT(1) DEFAULT NULL,
    `validator_id` BIGINT UNSIGNED DEFAULT NULL,
    `validation_notes` TEXT DEFAULT NULL,
    `validated_at` DATETIME DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uniq_ppdb_applicant_documents_applicant_requirement` (`ppdb_applicant_id`, `requirement_id`),
    KEY `idx_ppdb_applicant_documents_requirement_id` (`requirement_id`),
    KEY `idx_ppdb_applicant_documents_validator_id` (`validator_id`),
    KEY `idx_ppdb_applicant_documents_is_valid` (`is_valid`),
    CONSTRAINT `fk_ppdb_applicant_documents_applicant`
        FOREIGN KEY (`ppdb_applicant_id`) REFERENCES `ppdb_applicants` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_ppdb_applicant_documents_requirement`
        FOREIGN KEY (`requirement_id`) REFERENCES `ppdb_requirements` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_ppdb_applicant_documents_validator`
        FOREIGN KEY (`validator_id`) REFERENCES `users` (`id`)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 6. ppdb_applicant_verifications
-- Riwayat verifikasi umum pendaftar
-- =========================================================
CREATE TABLE IF NOT EXISTS `ppdb_applicant_verifications` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `ppdb_applicant_id` BIGINT UNSIGNED NOT NULL,
    `verifier_id` BIGINT UNSIGNED NOT NULL,
    `status` ENUM('pending','needs_revision','verified','rejected') NOT NULL DEFAULT 'pending',
    `notes` TEXT DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_ppdb_applicant_verifications_applicant_id` (`ppdb_applicant_id`),
    KEY `idx_ppdb_applicant_verifications_verifier_id` (`verifier_id`),
    KEY `idx_ppdb_applicant_verifications_status` (`status`),
    CONSTRAINT `fk_ppdb_applicant_verifications_applicant`
        FOREIGN KEY (`ppdb_applicant_id`) REFERENCES `ppdb_applicants` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_ppdb_applicant_verifications_verifier`
        FOREIGN KEY (`verifier_id`) REFERENCES `users` (`id`)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 7. ppdb_selections
-- Hasil seleksi / ranking
-- =========================================================
CREATE TABLE IF NOT EXISTS `ppdb_selections` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `ppdb_batch_id` BIGINT UNSIGNED NOT NULL,
    `ppdb_track_id` BIGINT UNSIGNED NOT NULL,
    `ppdb_applicant_id` BIGINT UNSIGNED NOT NULL,
    `score_total` DECIMAL(7,2) DEFAULT NULL,
    `distance_school` DECIMAL(10,2) DEFAULT NULL,
    `ranking` INT UNSIGNED DEFAULT NULL,
    `selection_status` ENUM('in_process','accepted','waitlisted','not_accepted') NOT NULL DEFAULT 'in_process',
    `decided_by` BIGINT UNSIGNED DEFAULT NULL,
    `decided_at` DATETIME DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uniq_ppdb_selections_applicant` (`ppdb_applicant_id`),
    KEY `idx_ppdb_selections_batch_id` (`ppdb_batch_id`),
    KEY `idx_ppdb_selections_track_id` (`ppdb_track_id`),
    KEY `idx_ppdb_selections_ranking` (`ranking`),
    KEY `idx_ppdb_selections_selection_status` (`selection_status`),
    KEY `idx_ppdb_selections_score_total` (`score_total`),
    KEY `idx_ppdb_selections_distance_school` (`distance_school`),
    KEY `idx_ppdb_selections_decided_by` (`decided_by`),
    CONSTRAINT `fk_ppdb_selections_batch`
        FOREIGN KEY (`ppdb_batch_id`) REFERENCES `ppdb_batches` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_ppdb_selections_track`
        FOREIGN KEY (`ppdb_track_id`) REFERENCES `ppdb_tracks` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_ppdb_selections_applicant`
        FOREIGN KEY (`ppdb_applicant_id`) REFERENCES `ppdb_applicants` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_ppdb_selections_decided_by`
        FOREIGN KEY (`decided_by`) REFERENCES `users` (`id`)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 8. ppdb_re_registrations
-- Daftar ulang setelah diterima
-- =========================================================
CREATE TABLE IF NOT EXISTS `ppdb_re_registrations` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `ppdb_applicant_id` BIGINT UNSIGNED NOT NULL,
    `re_registration_status` ENUM('pending','completed','rejected') NOT NULL DEFAULT 'pending',
    `notes` TEXT DEFAULT NULL,
    `submitted_at` DATETIME DEFAULT NULL,
    `verified_by` BIGINT UNSIGNED DEFAULT NULL,
    `verified_at` DATETIME DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uniq_ppdb_re_registrations_applicant` (`ppdb_applicant_id`),
    KEY `idx_ppdb_re_registrations_status` (`re_registration_status`),
    KEY `idx_ppdb_re_registrations_verified_by` (`verified_by`),
    CONSTRAINT `fk_ppdb_re_registrations_applicant`
        FOREIGN KEY (`ppdb_applicant_id`) REFERENCES `ppdb_applicants` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_ppdb_re_registrations_verified_by`
        FOREIGN KEY (`verified_by`) REFERENCES `users` (`id`)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;