<?php
$conn = mysqli_connect('localhost', 'root', '', 'dam');

if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// Clear tables
mysqli_query($conn, "SET FOREIGN_KEY_CHECKS=0");
mysqli_query($conn, "TRUNCATE TABLE pengguna");
mysqli_query($conn, "TRUNCATE TABLE kuesioner");
mysqli_query($conn, "SET FOREIGN_KEY_CHECKS=1");

$file_persepsi = fopen("persepsi.csv", "r");
$file_harapan = fopen("harapan.csv", "r");

if ($file_persepsi && $file_harapan) {
    // Skip header
    fgetcsv($file_persepsi, 1000, ";");
    fgetcsv($file_harapan, 1000, ";");

    $phone_base = 81273728882;

    while (($row_persepsi = fgetcsv($file_persepsi, 1000, ";")) !== FALSE) {
        $row_harapan = fgetcsv($file_harapan, 1000, ";");
        if (!$row_harapan) break;

        $nama = mysqli_real_escape_string($conn, trim($row_persepsi[1]));
        if (empty($nama)) continue;

        $phone = "0" . $phone_base;
        $phone_base++;
        
        $password = "123";

        // Insert pengguna
        $q_pengguna = "INSERT INTO pengguna (nama_pengguna, handphone, password) VALUES ('$nama', '$phone', '$password')";
        mysqli_query($conn, $q_pengguna);
        $id_pengguna = mysqli_insert_id($conn);

        // Insert kuesioner
        for ($i = 1; $i <= 12; $i++) {
            $jawaban = mysqli_real_escape_string($conn, $row_persepsi[$i+1]);
            $harapan = mysqli_real_escape_string($conn, $row_harapan[$i+1]);

            $q_kuesioner = "INSERT INTO kuesioner (tanggal, id_pengguna, id_pernyataan, jawaban, harapan) VALUES (NOW(), '$id_pengguna', '$i', '$jawaban', '$harapan')";
            mysqli_query($conn, $q_kuesioner);
        }
    }

    fclose($file_persepsi);
    fclose($file_harapan);
    
    echo "Import completed successfully.";
} else {
    echo "Failed to open CSV files.";
}
?>
