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

$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');

if (!$fp) {
    die("Unable to open CSV file.");
}

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

    $set_no   = trim($buffer[0]);
    $set_desc = trim($buffer[1]);
    $route_no = trim($buffer[2]);
    $part_no  = trim($buffer[3]);
    $part_qty = trim($buffer[4]);

    // Skip header row
    if (
        strtolower($set_no) == 'set_no' ||
        empty($set_no)
    ) {
        continue;
    }

    $set_no   = mysqli_real_escape_string($conn, $set_no);
    $route_no = mysqli_real_escape_string($conn, $route_no);
    $part_no  = mysqli_real_escape_string($conn, $part_no);

    // Get Set ID
    $set_query = mysqli_query($conn, "
        SELECT id
        FROM set_mangmnt
        WHERE set_number = '$set_no'
        LIMIT 1
    ");

    if (!$set_query || mysqli_num_rows($set_query) == 0) {
        continue;
    }

    $set_row = mysqli_fetch_assoc($set_query);
    $set_id  = $set_row['id'];

    // Check if route already exists
    $check_query = mysqli_query($conn, "
        SELECT id, route_no
        FROM set_route_details
        WHERE set_id = '$set_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'];

        if ($old_route != $route_no) {

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

            $records_updated++;

            $updated_routes[] = [
                'set_number' => $set_no,
                'old_route'  => $old_route,
                'new_route'  => $route_no
            ];
        }

    } else {

        mysqli_query($conn, "
            INSERT INTO set_route_details
            (
                set_id,
                route_no,
                part_no,
                created_by,
                created_on
            )
            VALUES
            (
                '$set_id',
                '$route_no',
                '$part_no',
                '$created_by',
                '$created_on'
            )
        ");

        $records_inserted++;
    }
}

fclose($fp);
?>

<!-- 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>Set Number</th>
        <th>Old Route</th>
        <th>New Route</th>
    </tr>

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

</table>

<?php } ?>