-- ============================================================
--  DATABASE PPDB (Penerimaan Peserta Didik Baru)
--  Jalankan file ini di phpMyAdmin atau MySQL client
-- ============================================================

CREATE DATABASE IF NOT EXISTS `db_ppdb`
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE `db_ppdb`;

-- ------------------------------------------------------------
-- Tabel utama pendaftar
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `pendaftar` (
  `id`                     INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

  -- Meta formulir
  `no_urut_formulir`       VARCHAR(20)   NOT NULL,
  `jurusan_dipilih`        VARCHAR(100)  NOT NULL,

  -- Data pribadi siswa
  `nama_lengkap`           VARCHAR(150)  NOT NULL,
  `jenis_kelamin`          ENUM('Laki-laki','Perempuan') NOT NULL,
  `nisn`                   VARCHAR(10)   DEFAULT NULL,
  `nik`                    VARCHAR(16)   NOT NULL,
  `no_kartu_keluarga`      VARCHAR(16)   NOT NULL,
  `tempat_lahir`           VARCHAR(100)  NOT NULL,
  `tanggal_lahir`          DATE          NOT NULL,
  `no_akta_lahir`          VARCHAR(50)   DEFAULT NULL,
  `agama`                  VARCHAR(20)   NOT NULL,
  `kewarganegaraan`        ENUM('WNI','WNA') NOT NULL DEFAULT 'WNI',
  `status_orang_tua`       ENUM('Lengkap','Yatim','Piatu','Yatim Piatu') DEFAULT NULL,
  `berkebutuhan_khusus`    VARCHAR(50)   NOT NULL DEFAULT 'Tidak',

  -- Alamat & mobilitas
  `alamat_rumah`           TEXT          NOT NULL,
  `kode_pos`               VARCHAR(5)    DEFAULT NULL,
  `tempat_tinggal`         VARCHAR(50)   DEFAULT NULL,
  `moda_transportasi`      VARCHAR(50)   DEFAULT NULL,

  -- Info tambahan siswa
  `anak_ke`                TINYINT UNSIGNED DEFAULT NULL,
  `memiliki_kip`           ENUM('Ya','Tidak') DEFAULT NULL,
  `asal_smp`               VARCHAR(150)  NOT NULL,
  `kompetensi_diminati`    VARCHAR(100)  DEFAULT NULL,
  `pernah_paud`            ENUM('Ya','Tidak') DEFAULT NULL,
  `hobi`                   VARCHAR(200)  DEFAULT NULL,
  `cita_cita`              VARCHAR(200)  DEFAULT NULL,

  -- Kontak
  `hp_ortu`                VARCHAR(20)   NOT NULL,
  `hp_siswa`               VARCHAR(20)   DEFAULT NULL,

  -- Data periodik
  `tinggi_badan`           DECIMAL(5,1)  DEFAULT NULL,
  `berat_badan`            DECIMAL(5,1)  DEFAULT NULL,
  `lingkar_kepala`         DECIMAL(5,1)  DEFAULT NULL,
  `jarak_tinggal`          VARCHAR(20)   DEFAULT NULL,
  `jarak_km`               DECIMAL(6,1)  DEFAULT NULL,
  `waktu_tempuh`           SMALLINT UNSIGNED DEFAULT NULL,
  `jumlah_saudara`         TINYINT UNSIGNED DEFAULT NULL,

  -- Timestamp
  `created_at`             TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`             TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  -- Indeks
  UNIQUE KEY `uq_nik`           (`nik`),
  UNIQUE KEY `uq_no_formulir`   (`no_urut_formulir`),
  INDEX `idx_nama`              (`nama_lengkap`),
  INDEX `idx_jurusan`           (`jurusan_dipilih`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ------------------------------------------------------------
-- Tabel orang tua / wali (ayah, ibu, wali pakai satu tabel)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `orang_tua` (
  `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `pendaftar_id`    INT UNSIGNED NOT NULL,
  `peran`           ENUM('ayah','ibu','wali') NOT NULL,

  `nama`            VARCHAR(150)  NOT NULL,
  `nik`             VARCHAR(16)   DEFAULT NULL,
  `tahun_lahir`     YEAR          DEFAULT NULL,
  `pendidikan`      VARCHAR(30)   DEFAULT NULL,
  `pekerjaan`       VARCHAR(50)   DEFAULT NULL,
  `penghasilan`     VARCHAR(50)   DEFAULT NULL,
  `berkebutuhan_khusus` VARCHAR(50) DEFAULT 'Tidak',

  INDEX `idx_pendaftar` (`pendaftar_id`),
  CONSTRAINT `fk_ortu_pendaftar`
    FOREIGN KEY (`pendaftar_id`) REFERENCES `pendaftar`(`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ------------------------------------------------------------
-- Tabel program kesejahteraan
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `kesejahteraan` (
  `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `pendaftar_id`    INT UNSIGNED NOT NULL,
  `jenis`           ENUM('PKH','KIP','KPS','KKS') NOT NULL,
  `nomor`           VARCHAR(50)   DEFAULT NULL,
  `nama_penerima`   VARCHAR(150)  DEFAULT NULL,

  INDEX `idx_pendaftar_ks` (`pendaftar_id`),
  CONSTRAINT `fk_ks_pendaftar`
    FOREIGN KEY (`pendaftar_id`) REFERENCES `pendaftar`(`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;