UPDATE settings SET appversion = '4.0.0';
UPDATE settings_exambro SET version = '4.0.0';
CREATE TABLE IF NOT EXISTS `jenis_ujian` (
  `id` CHAR(36) NOT NULL,
  `nama` VARCHAR(150) NOT NULL,
  `deskripsi` TEXT NULL,
  `kode` VARCHAR(50) NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


ALTER TABLE `ujian`
ADD COLUMN  `jenis_ujian_id` CHAR(36) NULL AFTER `bank_soal_id`;



-- ===== Tambah FOREIGN KEY jika belum ada =====

SET @fk_exists := (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE CONSTRAINT_SCHEMA = DATABASE()
      AND TABLE_NAME = 'ujian'
      AND CONSTRAINT_NAME = 'fk_ujian_jenis'
);

SET @sql2 := IF(
    @fk_exists = 0,
    'ALTER TABLE `ujian`
        ADD CONSTRAINT `fk_ujian_jenis`
        FOREIGN KEY (`jenis_ujian_id`)
        REFERENCES `jenis_ujian`(`id`)
        ON DELETE SET NULL
        ON UPDATE NO ACTION',
    'SELECT "FOREIGN KEY fk_ujian_jenis sudah ada, SKIP"'
);

PREPARE stmt2 FROM @sql2;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;



CREATE TABLE `berita_acara` (
  `id` CHAR(36) NOT NULL,
  `ujian_id` CHAR(36) NOT NULL,
  `jenis_ujian_id` CHAR(36) NULL,
  `ruang_id` CHAR(36) NULL,
  `sesi_id` CHAR(36) NULL,

  `tanggal` DATE NOT NULL,
  `jam_mulai` TIME NOT NULL,
  `jam_selesai` TIME NOT NULL,

  `jumlah_peserta_seharusnya` INT NOT NULL DEFAULT 0,
  `jumlah_hadir` INT NOT NULL DEFAULT 0,
  `jumlah_tidak_hadir` INT NOT NULL DEFAULT 0,
  `peserta_tidak_hadir` TEXT NULL,
  `catatan` TEXT NULL,

  `proktor_nama` VARCHAR(100) NULL,
  `proktor_nip` VARCHAR(50) NULL,
  `pengawas_nama` VARCHAR(100) NULL,
  `pengawas_nip` VARCHAR(50) NULL,
  `kepala_sekolah_nama` VARCHAR(100) NULL,
  `kepala_sekolah_nip` VARCHAR(50) NULL,

  `created_at` DATETIME NULL,
  `updated_at` DATETIME NULL,

  PRIMARY KEY (`id`),
  KEY `ujian_id` (`ujian_id`),
  KEY `jenis_ujian_id` (`jenis_ujian_id`),
  KEY `ruang_id` (`ruang_id`),
  KEY `sesi_id` (`sesi_id`),

  CONSTRAINT `fk_beritaacara_ujian`
    FOREIGN KEY (`ujian_id`)
    REFERENCES `ujian` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,

  CONSTRAINT `fk_beritaacara_jenisujian`
    FOREIGN KEY (`jenis_ujian_id`)
    REFERENCES `jenis_ujian` (`id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE,

  CONSTRAINT `fk_beritaacara_ruang`
    FOREIGN KEY (`ruang_id`)
    REFERENCES `ruang` (`id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE,

  CONSTRAINT `fk_beritaacara_sesi`
    FOREIGN KEY (`sesi_id`)
    REFERENCES `sesi` (`id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE

) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_general_ci;


ALTER TABLE jenis_ujian 
CONVERT TO CHARACTER SET utf8mb4 
COLLATE utf8mb4_general_ci;

ALTER TABLE berita_acara
CONVERT TO CHARACTER SET utf8mb4 
COLLATE utf8mb4_general_ci;

ALTER TABLE `settings_exambro`
  ADD COLUMN `file_exam_config_upload` VARCHAR(200) NOT NULL AFTER `file_exam_config`;
ALTER TABLE `settings_exambro`
	ADD COLUMN `login_expire_duration` BIGINT NOT NULL DEFAULT '86400000' AFTER `login_nopassword`;
ALTER TABLE `settings_exambro`
	ADD COLUMN `no_whatsapp` VARCHAR(20) NULL DEFAULT NULL AFTER `login_expire_duration`;

ALTER TABLE `exambro_menu`
	ADD COLUMN `mode_exam` TINYINT(1) NOT NULL DEFAULT '1' AFTER `is_active`;