-- Migration: Create master_sekolah table for school registration
-- Date: 2026-04-09

CREATE TABLE IF NOT EXISTS `master_sekolah` (
    `ID` INT PRIMARY KEY AUTO_INCREMENT,
    `Nama_Sekolah` VARCHAR(255) NOT NULL,
    `NPSN` VARCHAR(50) UNIQUE NOT NULL,
    `Kategori` ENUM('SMA', 'SMK', 'MA', 'MAK', 'PKBM') NOT NULL DEFAULT 'SMA',
    `Kabupaten_Kota` VARCHAR(100),
    `Provinsi` VARCHAR(100),
    `Email` VARCHAR(255),
    `No_WA` VARCHAR(20),
    `Link_Bukti_BPTI` LONGTEXT,
    `Status` ENUM('memerlukan_validasi', 'tervalidasi', 'ditolak') NOT NULL DEFAULT 'memerlukan_validasi',
    `Keterangan_Penolakan` TEXT,
    `Validasi_Oleh` VARCHAR(255),
    `Tanggal_Validasi` DATETIME,
    `Tanggal_Daftar` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `Updated_At` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_npsn` (`NPSN`),
    INDEX `idx_status` (`Status`),
    INDEX `idx_kategori` (`Kategori`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add this index to peserta_terdaftar to link with master_sekolah
ALTER TABLE `peserta_terdaftar` ADD INDEX `idx_npsn` (`NPSN`);
