-- ====================================================================
-- SKRIP UPDATE DATA STATISTIK HISTORIS UNIVERSITAS CENDERAWASIH
-- Pangkalan Data Terpadu Akademik (iPADUAN)
-- ====================================================================

-- 1. Pastikan tabel 'prodi' memiliki kolom jenjang terisi lengkap
UPDATE `prodi` SET `jenjang` = 'S1' WHERE `jenjang` IS NULL OR `jenjang` = '';

-- 2. Contoh Query Penambahan / Update Rekap Data Penerimaan Historis
INSERT INTO `penerimaan` (`kode_penerimaan`, `kode_prodi`, `kode_jalur`, `tahun`, `daya_tampung`, `jlh_pendaftar`, `jlh_diterima`, `jlh_registrasi`, `created_at`, `updated_at`)
VALUES
('PEN-2025-IF-01', '55201', 'SNMPTN', 2025, 80, 450, 85, 80, NOW(), NOW()),
('PEN-2025-IF-02', '55201', 'SBMPTN', 2025, 120, 780, 130, 118, NOW(), NOW()),
('PEN-2025-IF-03', '55201', 'MANDIRI', 2025, 100, 520, 105, 98, NOW(), NOW()),
('PEN-2024-IF-01', '55201', 'SNMPTN', 2024, 75, 420, 80, 75, NOW(), NOW()),
('PEN-2024-IF-02', '55201', 'SBMPTN', 2024, 110, 710, 120, 108, NOW(), NOW())
ON DUPLICATE KEY UPDATE 
    `jlh_pendaftar` = VALUES(`jlh_pendaftar`),
    `jlh_diterima` = VALUES(`jlh_diterima`),
    `jlh_registrasi` = VALUES(`jlh_registrasi`),
    `updated_at` = NOW();

-- 3. Contoh Query Penambahan / Update Data AKLUS Historis
INSERT INTO `aklus` (`kode_aklus`, `kode_prodi`, `kode_periode`, `tahun`, `semester`, `mhs_aktif`, `mhs_lulus`, `mhs_cuti`, `mhs_nonaktif`, `created_at`, `updated_at`)
VALUES
('AKL-2025-GENAP-55201', '55201', 'PER-2025-1', 2025, 'Genap', 650, 85, 12, 28, NOW(), NOW()),
('AKL-2025-GANJIL-55201', '55201', 'PER-2025-1', 2025, 'Ganjil', 680, 0, 15, 32, NOW(), NOW()),
('AKL-2024-GENAP-55201', '55201', 'PER-2024-1', 2024, 'Genap', 620, 78, 10, 25, NOW(), NOW())
ON DUPLICATE KEY UPDATE 
    `mhs_aktif` = VALUES(`mhs_aktif`),
    `mhs_lulus` = VALUES(`mhs_lulus`),
    `mhs_cuti` = VALUES(`mhs_cuti`),
    `mhs_nonaktif` = VALUES(`mhs_nonaktif`),
    `updated_at` = NOW();