<?php
//die("hii");
set_time_limit(0);
ini_set('memory_limit', '1024M');
ini_set('post_max_size', '32M');
ini_set('upload_max_filesize', '32M');

include "config.php"; 

$destdir  = "live-data-migration-csvs/";
$name     = "man_route_detail.csv";
$destfile = $destdir . $name;

$table_name = "set_route_details";

$records_updated = 0;
$records_skipped = 0;
$records_not_found = 0;

// Arrays to store details
$skipped_records = [];
$not_found_records = [];

$fp = fopen($destfile, 'r');
if (!$fp) {
    die("Error: Unable to open CSV file.");
}

// Skip header
$header = fgetcsv($fp, 1000000, ',');

while (($buffer = fgetcsv($fp, 1000000, ',')) !== false) {

    $buffer = array_map('trim', $buffer);

    // Skip empty rows
    if (count(array_filter($buffer)) === 0) {
        continue;
    }

    if (count($buffer) < 9) {
        $records_skipped++;
        $skipped_records[] = [
            'route_no' => 'N/A',
            'reason' => 'Column count less than expected'
        ];
        continue;
    }

    list(
        $Route_Number,
        $Op_Number,
        $Resource_Code,
        $Description,
        $Manufacturing_Instructions,
        $Resource_Rate,
        $Set_Time_C,
        $Op_Time_C,
        $Op_Time_Per
    ) = array_map('addslashes', $buffer);

    // Required check
    if (empty($Route_Number) || empty($Resource_Code)) {
        $records_skipped++;
        $skipped_records[] = [
            'route_no' => $Route_Number,
            'reason' => 'Missing Route_Number or Resource_Code'
        ];
        continue;
    }
    // Get machine/resource ID
     $machine_id = utility::getTableFieldById(
        "station_detail_table",
        "id",
        "resource_code",
        $Resource_Code
    );

    if (empty($machine_id)) {
        $records_skipped++;
        $skipped_records[] = [
            'route_no' => $Route_Number,
            'reason' => "Resource_Code not found: $Resource_Code"
        ];
        continue;
    }

    // Check if route exists
    $check = mysqli_query($conn, "SELECT id FROM $table_name WHERE route_no = '$Route_Number'");

    if (mysqli_num_rows($check) > 0) {

        $sql = "UPDATE $table_name SET
                    route_desc = '$Description',
                    operation = '$Op_Number',
                    machine_high_priority = '$machine_id',
                    set_time = '$Set_Time_C',
                    operation_time = '$Op_Time_C',
                    parts_per_operation = '$Op_Time_Per',
                    machine_rate = '$Resource_Rate'
                WHERE route_no = '$Route_Number'";

        if (mysqli_query($conn, $sql)) {
            $records_updated++;
        } else {
            $records_skipped++;
            $skipped_records[] = [
                'route_no' => $Route_Number,
                'reason' => mysqli_error($conn)
            ];
        }

    } else {
        $records_not_found++;
        $not_found_records[] = $Route_Number;
    }
}

fclose($fp);


// ================= OUTPUT =================

echo "<br><span style='color:green;'>$records_updated records updated.</span>";
echo "<br><span style='color:orange;'>$records_skipped records skipped.</span>";
echo "<br><span style='color:red;'>$records_not_found routes not found.</span>";


// ===== SKIPPED RECORDS =====
if (!empty($skipped_records)) {
    echo "<h3 style='color:orange;'>Skipped Records Details:</h3>";
    echo "<table border='1' cellpadding='5'>";
    echo "<tr><th>Route No</th><th>Reason</th></tr>";

    foreach ($skipped_records as $row) {
        echo "<tr>
                <td>{$row['route_no']}</td>
                <td>{$row['reason']}</td>
              </tr>";
    }

    echo "</table>";
}


// ===== NOT FOUND RECORDS =====
if (!empty($not_found_records)) {
    echo "<h3 style='color:red;'>Routes Not Found:</h3>";
    echo "<table border='1' cellpadding='5'>";
    echo "<tr><th>Route No</th></tr>";

    foreach ($not_found_records as $route) {
        echo "<tr><td>$route</td></tr>";
    }

    echo "</table>";
}
?>