<?php
require_once 'db_config.php';
require_once 'districts.php';

// Connect to database
$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// 1. Create Tables
$sql_create = file_get_contents('migration_locations.sql');
$conn->multi_query($sql_create);

// Wait for multi_query to finish
while ($conn->next_result()) {;} 

echo "Tables created successfully.<br>";

// 2. Clear existing data to avoid duplicates (optional, strictly for migration safety)
$conn->query("SET FOREIGN_KEY_CHECKS = 0");
$conn->query("TRUNCATE TABLE divisional_secretariats");
$conn->query("TRUNCATE TABLE districts");
$conn->query("SET FOREIGN_KEY_CHECKS = 1");

// 3. Migrate Data
$districts_data = get_districts();

foreach ($districts_data as $district_name => $ds_list) {
    // Insert District
    $stmt = $conn->prepare("INSERT INTO districts (name) VALUES (?)");
    $stmt->bind_param("s", $district_name);
    
    if ($stmt->execute()) {
        $district_id = $stmt->insert_id;
        echo "Inserted District: $district_name (ID: $district_id)<br>";
        
        // Insert Divisional Secretariats
        $stmt_ds = $conn->prepare("INSERT INTO divisional_secretariats (district_id, name) VALUES (?, ?)");
        
        foreach ($ds_list as $ds_name) {
            $stmt_ds->bind_param("is", $district_id, $ds_name);
            $stmt_ds->execute();
        }
        $stmt_ds->close();
        echo " - Inserted " . count($ds_list) . " DS divisions.<br>";
    } else {
        echo "Error inserting district $district_name: " . $stmt->error . "<br>";
    }
    $stmt->close();
}

$conn->close();
echo "Migration completed.";
?>
