<?php
// File: c:/laragon/www/api_koneksi/migration_schema.php
include 'config/koneksi.php';

$queries = [
    "SET FOREIGN_KEY_CHECKS = 0;",
    
    // Drop foreign keys
    "ALTER TABLE dat_distribusi DROP FOREIGN KEY dat_distribusi_ibfk_1;",
    "ALTER TABLE dat_notifikasi DROP FOREIGN KEY fk_notifikasi_user;",
    "ALTER TABLE dat_pembayaran DROP FOREIGN KEY dat_pembayaran_ibfk_1;",
    "ALTER TABLE dat_pembayaran DROP FOREIGN KEY dat_pembayaran_ibfk_2;",
    "ALTER TABLE dat_reservasi DROP FOREIGN KEY dat_reservasi_ibfk_1;",
    "ALTER TABLE dat_reservasi DROP FOREIGN KEY dat_reservasi_ibfk_2;",
    "ALTER TABLE dat_reservasi DROP FOREIGN KEY dat_reservasi_ibfk_3;",
    
    // Remove AUTO_INCREMENT by changing to standard INT first
    "ALTER TABLE mst_user MODIFY user_id INT NOT NULL;",
    "ALTER TABLE mst_promo MODIFY promo_id INT NOT NULL;",
    "ALTER TABLE mst_paket MODIFY paket_id INT NOT NULL;",
    "ALTER TABLE mst_metode_bayar MODIFY metode_id INT NOT NULL;",
    "ALTER TABLE dat_reservasi MODIFY reservasi_id INT NOT NULL;",
    "ALTER TABLE dat_pembayaran MODIFY pembayaran_id INT NOT NULL;",
    "ALTER TABLE dat_distribusi MODIFY distribusi_id INT NOT NULL;",
    "ALTER TABLE dat_notifikasi MODIFY notif_id INT NOT NULL;",
    "ALTER TABLE dat_jadwal_tutup MODIFY tutup_id INT NOT NULL;",
    
    // Alter primary keys to VARCHAR(20)
    "ALTER TABLE mst_user MODIFY user_id VARCHAR(20) NOT NULL;",
    "ALTER TABLE mst_promo MODIFY promo_id VARCHAR(20) NOT NULL;",
    "ALTER TABLE mst_paket MODIFY paket_id VARCHAR(20) NOT NULL;",
    "ALTER TABLE mst_metode_bayar MODIFY metode_id VARCHAR(20) NOT NULL;",
    "ALTER TABLE dat_reservasi MODIFY reservasi_id VARCHAR(20) NOT NULL;",
    "ALTER TABLE dat_pembayaran MODIFY pembayaran_id VARCHAR(20) NOT NULL;",
    "ALTER TABLE dat_distribusi MODIFY distribusi_id VARCHAR(20) NOT NULL;",
    "ALTER TABLE dat_notifikasi MODIFY notif_id VARCHAR(20) NOT NULL;",
    "ALTER TABLE dat_jadwal_tutup MODIFY tutup_id VARCHAR(20) NOT NULL;",
    
    // Alter foreign keys and related tables' ID columns to VARCHAR(20)
    "ALTER TABLE dat_distribusi MODIFY reservasi_id VARCHAR(20) NOT NULL;",
    "ALTER TABLE dat_notifikasi MODIFY user_id VARCHAR(20) NOT NULL;",
    "ALTER TABLE dat_pembayaran MODIFY reservasi_id VARCHAR(20) NOT NULL;",
    "ALTER TABLE dat_pembayaran MODIFY metode_id VARCHAR(20) NOT NULL;",
    "ALTER TABLE dat_reservasi MODIFY user_id VARCHAR(20) NOT NULL;",
    "ALTER TABLE dat_reservasi MODIFY paket_id VARCHAR(20) NOT NULL;",
    "ALTER TABLE dat_reservasi MODIFY promo_id VARCHAR(20) DEFAULT NULL;",
    
    // Re-add constraints
    "ALTER TABLE dat_distribusi ADD CONSTRAINT dat_distribusi_ibfk_1 FOREIGN KEY (reservasi_id) REFERENCES dat_reservasi (reservasi_id) ON DELETE CASCADE;",
    "ALTER TABLE dat_notifikasi ADD CONSTRAINT fk_notifikasi_user FOREIGN KEY (user_id) REFERENCES mst_user (user_id) ON DELETE CASCADE ON UPDATE CASCADE;",
    "ALTER TABLE dat_pembayaran ADD CONSTRAINT dat_pembayaran_ibfk_1 FOREIGN KEY (reservasi_id) REFERENCES dat_reservasi (reservasi_id) ON DELETE CASCADE;",
    "ALTER TABLE dat_pembayaran ADD CONSTRAINT dat_pembayaran_ibfk_2 FOREIGN KEY (metode_id) REFERENCES mst_metode_bayar (metode_id) ON DELETE RESTRICT;",
    "ALTER TABLE dat_reservasi ADD CONSTRAINT dat_reservasi_ibfk_1 FOREIGN KEY (user_id) REFERENCES mst_user (user_id) ON DELETE CASCADE;",
    "ALTER TABLE dat_reservasi ADD CONSTRAINT dat_reservasi_ibfk_2 FOREIGN KEY (paket_id) REFERENCES mst_paket (paket_id) ON DELETE CASCADE;",
    "ALTER TABLE dat_reservasi ADD CONSTRAINT dat_reservasi_ibfk_3 FOREIGN KEY (promo_id) REFERENCES mst_promo (promo_id) ON DELETE SET NULL;",
    
    "SET FOREIGN_KEY_CHECKS = 1;"
];

foreach ($queries as $index => $q) {
    if (!$conn->query($q)) {
        echo "Error at query #$index: " . $conn->error . "\nQuery: $q\n";
        exit(1);
    }
}

echo "Database schema migrated successfully to VARCHAR(20)!\n";
?>
