<?php
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_parts.csv";
$destfile = $destdir.$name;  

$table_name = "route_details";

$records_inserted = 0;
$records_updated  = 0;  

$updated_routes = [];

$created_by = $_SESSION['user_id'];
$updated_by = $_SESSION['user_id'];
$created_on = date('Y-m-d H:i:s');

$fp = fopen($destfile, 'r');

// ================= STEP 1: GROUP DATA =================
$grouped_data = [];

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

    $Part_Number     = addslashes(trim($buffer[0]));
    $Route_Number    = addslashes(trim($buffer[1]));
    $Preferred_Route = strtolower(trim($buffer[2]));

    // Skip header or empty
    if ($Part_Number == "Part_Number" || empty($Part_Number)) {
        continue;
    }

    // Initialize if not exists
    if (!isset($grouped_data[$Part_Number])) {
        $grouped_data[$Part_Number] = [
            'route' => $Route_Number,
            'preferred' => $Preferred_Route
        ];
    }

    // ⭐ PRIORITY: If Preferred_Route = YES → override
    if ($Preferred_Route == "yes") {
        $grouped_data[$Part_Number] = [
            'route' => $Route_Number,
            'preferred' => $Preferred_Route
        ];
    }
}

fclose($fp);

// ================= STEP 2: PROCESS DATA =================
foreach ($grouped_data as $Part_Number => $data) {

    $Route_Number = $data['route'];

    // ================= GET PART ID =================
    $part_id = utility::getTableFieldById(
        "item_management",
        "id",
        "part_number",
        $Part_Number
    );

    if (empty($part_id)) {
        continue;
    }

    // ================= CHECK EXIST =================
    $check_query = mysqli_query($conn, "
        SELECT id, route_no 
        FROM route_details 
        WHERE item_id = '$part_id'
        LIMIT 1
    ");

    if (mysqli_num_rows($check_query) > 0) {

        $row = mysqli_fetch_assoc($check_query);
        $route_id = $row['id'];
        $old_route = $row['route_no'];

        // ===== UPDATE ONLY IF CHANGED =====
        if ($old_route != $Route_Number) {

            mysqli_query($conn, "
                UPDATE route_details 
                SET route_no = '$Route_Number',
                    updated_by = '$updated_by',
                    updated_on = '$created_on'
                WHERE id = '$route_id'
            ");

            $records_updated++;

            $updated_routes[] = [
                'part_number' => $Part_Number,
                'old_route'   => $old_route,
                'new_route'   => $Route_Number
            ];
        }

    } else {

        // ===== INSERT =====
        mysqli_query($conn, "
            INSERT INTO route_details 
            (item_id, route_no, part_no, created_by, created_on) 
            VALUES 
            ('$part_id', '$Route_Number', '$part_id', '$created_by', '$created_on')
        ");

        $records_inserted++;
    }
}
?>

<!-- ===== SUMMARY ===== -->
<?php 
if($records_inserted){    
  echo '<br><span style="color:green;">'.$records_inserted.' records inserted</span>';
}

if($records_updated){    
  echo '<br><span style="color:blue;">'.$records_updated.' records updated</span>';
}
?>

<!-- ===== UPDATED ROUTE LIST ===== -->
<?php if(!empty($updated_routes)) { ?>

    <h3 style="margin-top:20px;">Updated Route List</h3>

    <table border="1" cellpadding="6" cellspacing="0">
        <tr style="background:#f2f2f2;">
            <th>Part Number</th>
            <th>Old Route</th>
            <th>New Route</th>
        </tr>

        <?php foreach($updated_routes as $row) { ?>
            <tr>
                <td><?php echo $row['part_number']; ?></td>
                <td><?php echo $row['old_route']; ?></td>
                <td><?php echo $row['new_route']; ?></td>
            </tr>
        <?php } ?>

    </table>

<?php } ?>