-- migration_20260906_siswa_details.sql
-- Idempotent schema migration to add complete student bio & parental fields

SET @dbname = DATABASE();
SET @tablename = "siswa";

-- 1. tanggal_lahir
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = "tanggal_lahir") > 0,
  "SELECT 1",
  "ALTER TABLE siswa ADD COLUMN tanggal_lahir DATE NULL AFTER jenis_kelamin"
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- 2. agama
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = "agama") > 0,
  "SELECT 1",
  "ALTER TABLE siswa ADD COLUMN agama VARCHAR(64) NULL AFTER tanggal_lahir"
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- 3. nama_ayah
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = "nama_ayah") > 0,
  "SELECT 1",
  "ALTER TABLE siswa ADD COLUMN nama_ayah VARCHAR(128) NULL AFTER agama"
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- 4. nama_ibu
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = "nama_ibu") > 0,
  "SELECT 1",
  "ALTER TABLE siswa ADD COLUMN nama_ibu VARCHAR(128) NULL AFTER nama_ayah"
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;
