<?php
set_time_limit(0);
ini_set('memory_limit', '1024M');

include "config.php";

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

$records_inserted = 0;
$records_skipped  = 0;
$missing_parts    = [];
$processed_sets   = []; // to avoid deleting mappings multiple times

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

if (!$fp) {
    die("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;
    }

    // Column mapping
    list($set_no, $set_desc, $route, $part_no, $part_qty) = $buffer;

    if (empty($set_no) || empty($part_no)) {
        $records_skipped++;
        continue;
    }

    $set_no   = mysqli_real_escape_string($conn, $set_no);
    $set_desc = mysqli_real_escape_string($conn, $set_desc);
    //$part_no  = mysqli_real_escape_string($conn, $part_no);
	    //$part_no  = mysqli_real_escape_string($conn, $part_no);
	$part_no = normalizePartNumber($part_no);

// Skip delete items
if($part_no === "__DELETE__"){
    $records_skipped++;
    continue;
}

    $ratio    = intval($part_qty);

    // Check if set exists
    $check_set = mysqli_query($conn, "SELECT id FROM set_mangmnt WHERE set_number='$set_no'");

    if (mysqli_num_rows($check_set) > 0) {

        $row = mysqli_fetch_assoc($check_set);
        $set_id = $row['id'];

        // Delete mappings only once for this set
        if(!in_array($set_id,$processed_sets)){

            mysqli_query($conn,"DELETE FROM set_mangmnt_mapping WHERE set_id='$set_id'");
            $processed_sets[] = $set_id;

        }

    } else {

        // Insert new set
        $insert_set = "INSERT INTO set_mangmnt
        (description,set_number,set_name,use_both_tables,created_by,created_on)
        VALUES
        ('$set_desc','$set_no','$set_desc','0','1',NOW())";

        mysqli_query($conn,$insert_set);

        $set_id = mysqli_insert_id($conn);

        $processed_sets[] = $set_id;

    }

    // Get part_id
    $part_id = utility::getTableFieldById(
        "item_management",
        "id",
        "part_number",
        $part_no
    );

    // If part not found
    if(empty($part_id)){

        $missing_parts[] = $part_no;
        $records_skipped++;
        continue;

    }

    // Check if mapping already exists
    $check_mapping = mysqli_query($conn,"
        SELECT id 
        FROM set_mangmnt_mapping 
        WHERE set_id='$set_id' 
        AND part_id='$part_id'
    ");

    if(mysqli_num_rows($check_mapping) == 0){

        // Insert mapping
        $sql = "INSERT INTO set_mangmnt_mapping
                (set_id, part_id, ratio)
                VALUES
                ('$set_id','$part_id','$ratio')";

        if(mysqli_query($conn,$sql)){
            $records_inserted++;
        }else{
            $records_skipped++;
        }

    }else{

        // Update ratio if already exists
        mysqli_query($conn,"
            UPDATE set_mangmnt_mapping 
            SET ratio='$ratio'
            WHERE set_id='$set_id'
            AND part_id='$part_id'
        ");

        $records_skipped++;
    }

}

fclose($fp);

echo "<br><span style='color:green;font-weight:bold;'>$records_inserted records inserted</span>";
echo "<br><span style='color:red;font-weight:bold;'>$records_skipped records skipped</span>";

function normalizePartNumber($part_no){

    $map = [


"3Y0.809.95ED" => "3Y0.809.957E", 

"8D33-166568-A-PIA2" => "8D33-166568-A-PIA1",
"HK8M-166511-BA-PIA" => "HK8M-166511-AA-PIA",

"JLRC0455A20BB-PIA1" => "JLRC045A20BB-PIA1",
"JLRC0455A20BB-PIA2" => "JLRC045A20BB-PIA2",
"JLRC0455A20BC-PIA1" => "JLRC045A20BC-PIA1",

"KY63-01903-AA-PIA3" => "KY63-01903-AAPIA2",

"KY73-018C21-AB-PIA01" => "KY73-018C21-PIA01",
"KY73-018C21-AB-PIA02" => "KY73-018C21-PIA02",

"KY7308B412-A-PIA01" => "KY73-8B412-A-PIA01",
"KY7308B413-A-PIA02" => "KY73-8B413-A-PIA01",

"KY9Y-Q614108-B-P1" => "KY9Y-Q614108-B-PIA1",
"KY9Y-Q61411B-B-P1" => "KY9Y-Q61411B-B-PIA1",

"KY9Y-Q4D9503-AA-PIA3" => "KY9Y-Q4D9503-AA-PIA2",

"MY83-539A44-AA" => "MY63-539A44-AA",
"MY83-539A45-AA" => "MY63-539A45-AA",

"MY6Y-8C464-PIA01" => "MY6Y-8C464-A-PIA01",
"MY6Y-8C464-PIA02" => "MY6Y-8C464-A-PIA02",
"MY6Y-8C464-PIA03" => "MY6Y-8C464-A-PIA03",

"MY6Y-8C465-PIA01" => "MY6Y-8C465-A-PIA01",
"MY6Y-8C465-PIA02" => "MY6Y-8C465-A-PIA02",
"MY6Y-8C465-PIA03" => "MY6Y-8C465-A-PIA03",

"NY83-16E107-AA-PIA2" => "NY83-16E107-AA-PIA1",
"NY83-17225-AA-PIA2" => "NY83-17225-AA-PIA1",

"NY83-8C484-AA-PIA1" => "NY83-8C464-AA-PIA1",
"NY83-8C485-AA-PIA2" => "NY83-8C465-AA-PIA1",

"RY53-8B411-AA-PIA2" => "RY53-8B411-AA-PIA1",

"RY63-11465-A-PIA1" => "RY63-11464-A-PIA2",

"RY6Y-16E107AB-PIA2" => "RY6Y-16E107AA-PIA2",
"RY6Y-16E107AB-PIA3" => "RY6Y-16E107AA-PIA3",

"TY63-5E221AA" => "TY63-5E221-AA",

"TY83-17724-AA-PIA1" => "TY83-17224-AA-PIA1",
"TY83-17725-AA-PIA1" => "TY83-17225-AA-PIA1",
    ];

    return $map[$part_no] ?? $part_no;
}
// Show missing parts
if(!empty($missing_parts)){

    $missing_parts = array_unique($missing_parts);

    echo "<br><br><span style='color:red;font-weight:bold;'>Parts not found in item_management:</span><br><br>";

    foreach($missing_parts as $part){
        echo $part . "<br>";
    }

}
?>