<?php
require_once __DIR__ . '/../includes/conn.php';

$sqlFile = '/private/tmp/complaint_hierarchy_migration.sql';

if (!file_exists($sqlFile)) {
    die("Migration SQL file not found: ".$sqlFile.PHP_EOL);
}

function run_sql($conn, $sql) {
    $sql = trim($sql);
    if ($sql === '') {
        return;
    }
    if (!$conn->query($sql)) {
        die("SQL failed: ".$conn->error.PHP_EOL.$sql.PHP_EOL);
    }
}

function column_exists($conn, $table, $column) {
    $table = $conn->real_escape_string($table);
    $column = $conn->real_escape_string($column);
    $result = $conn->query("SHOW COLUMNS FROM `$table` LIKE '$column'");
    return $result && $result->num_rows > 0;
}

function index_exists($conn, $table, $index) {
    $table = $conn->real_escape_string($table);
    $index = $conn->real_escape_string($index);
    $result = $conn->query("SHOW INDEX FROM `$table` WHERE Key_name='$index'");
    return $result && $result->num_rows > 0;
}

if (!column_exists($conn, 'complaint_list', 'complaint_type')) {
    run_sql($conn, "ALTER TABLE complaint_list ADD COLUMN complaint_type VARCHAR(10) DEFAULT NULL AFTER cid");
}

if (!column_exists($conn, 'complaint_list', 'node_id')) {
    run_sql($conn, "ALTER TABLE complaint_list ADD COLUMN node_id INT DEFAULT NULL AFTER complaint_type");
}

if (!index_exists($conn, 'complaint_list', 'idx_complaint_list_node_id')) {
    run_sql($conn, "ALTER TABLE complaint_list ADD INDEX idx_complaint_list_node_id (node_id)");
}

$sql = file_get_contents($sqlFile);
$statements = preg_split('/;\s*\n/', $sql);

foreach ($statements as $statement) {
    $statement = trim($statement);
    if ($statement === '') {
        continue;
    }
    if (stripos($statement, 'ALTER TABLE complaint_list ADD COLUMN IF NOT EXISTS') === 0) {
        continue;
    }
    if (stripos($statement, 'ALTER TABLE complaint_list ADD INDEX IF NOT EXISTS') === 0) {
        continue;
    }
    run_sql($conn, $statement);
}

run_sql($conn, "
    UPDATE users_complaint uc
    INNER JOIN complaint_list old_cl ON old_cl.cid = uc.c_list
    INNER JOIN complaint_list new_cl ON new_cl.node_id IS NOT NULL
        AND new_cl.details = old_cl.details
        AND new_cl.priority = old_cl.priority
        AND new_cl.subdivision = old_cl.subdivision
    SET uc.c_list = new_cl.cid
    WHERE old_cl.node_id IS NULL OR old_cl.node_id = 0
");

run_sql($conn, "
    UPDATE users_complaint uc
    INNER JOIN complaint_list old_cl ON old_cl.cid = uc.c_list
    INNER JOIN complaint_list new_cl ON new_cl.node_id IS NOT NULL
        AND new_cl.details = 'Fire in DB /MCB/ MCCB'
        AND new_cl.priority = old_cl.priority
        AND new_cl.subdivision = old_cl.subdivision
    SET uc.c_list = new_cl.cid
    WHERE (old_cl.node_id IS NULL OR old_cl.node_id = 0)
    AND old_cl.details = 'Sparking in DB /MCB/ MCCB'
");

run_sql($conn, "
    UPDATE users_complaint uc
    INNER JOIN complaint_list old_cl ON old_cl.cid = uc.c_list
    SET uc.c_list = NULL
    WHERE old_cl.node_id IS NULL OR old_cl.node_id = 0
");

run_sql($conn, "DELETE FROM complaint_list WHERE node_id IS NULL OR node_id = 0");

$nodes = $conn->query("SELECT COUNT(*) AS total FROM complaint_nodes")->fetch_assoc();
$complaints = $conn->query("SELECT COUNT(*) AS total FROM complaint_list WHERE node_id IS NOT NULL")->fetch_assoc();

echo "Migration completed. Nodes: ".$nodes['total'].". Hierarchy complaints: ".$complaints['total'].".".PHP_EOL;
