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

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

$batch_size = 3000;
$table_name = "item_management";

$records_inserted = 0;
$records_updated  = 0;

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

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

    // Skip empty lines
    if (!$buffer || empty(array_filter($buffer))) {
        continue;
    }

    // Skip header row
    if ($first_row) {
        $first_row = false;
        continue;
    }

    // Column mapping
    $variable_array = [
        "Part_Number",
        "Usual_Source",
        "Stock_UOM",
        "Product_Group",
        "Man_Lead_Time",
        "Defaul_Receipt_Location",
        "Minimum_Stock",
        "Maximum_Stock",
        "Minimum_EBQ",
        "Maximum_EBQ",
        "Standard_Cost_EBQ",
        "Standard_Cost",
        "Drawing_Issue"
    ];

    foreach ($variable_array as $key => $val) {
        ${$val} = addslashes(trim($buffer[$key] ?? ''));
    }

    // Fetch IDs
    $Product_Group_id = utility::getTableFieldById(
        "product_group",
        "id",
        "product_short_code",
        $Product_Group
    );

    $receipt_location_code_id = utility::getTableFieldById(
        "default_receipt_location",
        "id",
        "receipt_location_code",
        $Defaul_Receipt_Location
    );

    // Check if part exists
    $checkSql = "SELECT id FROM $table_name WHERE part_number = '$Part_Number' LIMIT 1";
    $checkResult = mysqli_query($conn, $checkSql);

    if (mysqli_num_rows($checkResult) > 0) {

        // Update record
        $row = mysqli_fetch_assoc($checkResult);
        $part_id = $row['id'];

        $updateSql = "UPDATE $table_name SET
                usual_source = '$Usual_Source',
                stock_uom = '$Stock_UOM',
                product_group = '$Product_Group_id',
                man_lead_time = '$Man_Lead_Time',
                mpe = '$receipt_location_code_id',
                price_uom = '$Minimum_EBQ',
                mks = '$Maximum_EBQ',
                standard_cost_ebq = '$Standard_Cost_EBQ',
                standard_cost = '$Standard_Cost',
                drawing_issue = '$Drawing_Issue'
              WHERE id = '$part_id'";

        mysqli_query($conn, $updateSql);
        $records_updated++;

    } else {

        // Insert record
        $insertSql = "INSERT INTO $table_name
        (`part_number`,`usual_source`,`stock_uom`,`product_group`,
         `man_lead_time`,`mpe`,`price_uom`,`mks`,
         `standard_cost_ebq`,`standard_cost`,`drawing_issue`)
        VALUES
        ('$Part_Number','$Usual_Source','$Stock_UOM','$Product_Group_id',
         '$Man_Lead_Time','$receipt_location_code_id','$Minimum_EBQ','$Maximum_EBQ',
         '$Standard_Cost_EBQ','$Standard_Cost','$Drawing_Issue')";

        mysqli_query($conn, $insertSql);
        $partt_id = mysqli_insert_id($conn);
        $records_inserted++;

        // Insert product_name row
        $sql_product_name = "INSERT INTO product_name
            (`pro_id`,`group_number`,`group_code`,`group_name`,`qty`)
            VALUES ('$partt_id','$Part_Number','$Usual_Source','$Part_Number','1')";
        mysqli_query($conn, $sql_product_name);

        $partt_idd = mysqli_insert_id($conn);

        // Update item_management FK
        $sql_update = "UPDATE item_management
                       SET part_number_id = '$partt_idd'
                       WHERE id = '$partt_id'";
        mysqli_query($conn, $sql_update);
    }
}

fclose($fp);
?>

<?php 
if ($records_inserted) {
    echo '<br><span style="font-family:calibri;color:green;">'.$records_inserted.' records have been inserted...</span>';
}
?>

<?php 
if ($records_updated) {
    echo '<br><span style="font-family:calibri;color:blue;">'.$records_updated.' records have been updated...</span>';
}
?>
