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

mysqli_report(MYSQLI_REPORT_OFF); // prevent fatal crash

include "config.php";

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

$records_inserted = 0;
$records_updated  = 0;
$records_skipped  = 0;
$missing_parts    = [];

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

    // Normalize part number
    $part_no = normalizePartNumber($part_no);

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

    $part_no = mysqli_real_escape_string($conn, $part_no);
    $ratio   = intval($part_qty);

    // 1️⃣ Check / Insert set
    $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'];
    } else {

        $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())";

        if (!mysqli_query($conn, $insert_set)) {
            echo "Set Insert Error: " . mysqli_error($conn) . "<br>";
            $records_skipped++;
            continue;
        }

        $set_id = mysqli_insert_id($conn);
    }

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

    // Missing part
    if (empty($part_id)) {
        $missing_parts[] = $part_no;
        $records_skipped++;
        continue;
    }

    // 3️⃣ INSERT OR UPDATE (DUPLICATE SAFE)
    $sql = "INSERT INTO set_mangmnt_mapping
            (set_id, part_id, ratio)
            VALUES
            ('$set_id','$part_id','$ratio')
            ON DUPLICATE KEY UPDATE
            ratio = VALUES(ratio)";

    $result = mysqli_query($conn, $sql);

    if ($result) {

        $affected = mysqli_affected_rows($conn);

        if ($affected == 1) {
            $records_inserted++;
        } elseif ($affected == 2) {
            $records_updated++;
        }

    } else {
        echo "Mapping Error: " . mysqli_error($conn) . "<br>";
        $records_skipped++;
    }
}

fclose($fp);

// Output
echo "<br><span style='color:green;font-weight:bold;'>$records_inserted records inserted</span>";
echo "<br><span style='color:blue;font-weight:bold;'>$records_updated records updated</span>";
echo "<br><span style='color:red;font-weight:bold;'>$records_skipped records skipped</span>";
function normalizePartNumber($part_no){

    $map = [

        "HF2303LH-ML" => "HF2303 LH-ML",
        "HF2303RH-ML" => "HF2303 RH-ML",

        "KY9Y-4D9532-PA1ML" => "KY9Y-4D9532-A-PA1ML",
        "KY9Y-4D9552-PIA1ML" => "KY9Y-4D9552-A-PIA1ML",
        "KY9Y-610A36-ACPIA1M" => "KY9Y-610A36-ACPIA1ML",

        "KY9Y-4D9532-PA2ML" => "KY9Y-4D9532-A-PA2ML",
        "KY9Y-4D9552-PIA2ML" => "KY9Y-4D9552-A-PIA2ML",
        "KY9Y-610A36-ACPIA2M" => "KY9Y-610A36-ACPIA2ML",

        "401/55755-ML" => "401//R2755-ML",
        "26NA5112SP-ML" => "26NA512SP-ML",
        "25NA551SP-ML" => "26NA551SP-ML",

        "MY6Y-8C464-A-PIA01-ML" => "MY6Y-8C464-A-PIA01ML",
        "MY6Y-8C464-A-PIA02-ML" => "MY6Y-8C464-A-PIA02ML",
        "MY6Y-8C464-A-PIA03-ML" => "MY6Y-8C464-A-PIA03ML",

        "MY6Y-8C465-A-PIA01-ML" => "MY6Y-8C465-A-PIA01ML",
        "MY6Y-8C465-A-PIA02-ML" => "MY6Y-8C465-A-PIA02ML",
        "MY6Y-8C465-A-PIA03-ML" => "MY6Y-8C465-A-PIA03ML",

        "401/M8876-ML" => "401/M5876-ML",
        "MY6Y-16E107-APIA03-ML" => "MY6Y-16E107-APIA03ML",

        "889094318-ML" => "8890943418-ML",

        "KY9Y-61411-A-PIA1=ML" => "KY9Y-61411-A-PIA1-ML",
        "KY9Y-Q6141B-B-P2-ML" => "KY9Y-Q61411B-B-P2-ML",
        "KY9Y-61411-A-PIA2=ML" => "KY9Y-61411-A-PIA2-ML",
        "KY9Y-Q6141B-B-P1-ML" => "KY9Y-Q61411B-B-P1-ML",

        "BD7 I6 UNDERTRAY" => "DB7 I6 UNDERTRAY",

        "394/9512-ML" => "394-9512-ML",
        "394/9513-ML" => "394-9513-ML",
        "506/5534-ML" => "506-5534-ML",
        "506/5535-ML" => "506-5535-ML",

        "PY63-8C4654AAPIA1ML" => "PY63-8C465AAPIA1ML",

        "KY9YQ95152AAPIA1ML" => "KY9Y-Q95152AAPIA1ML",
        "KY9YQ95152AAPIA2ML" => "KY9Y-Q95152AAPIA2ML",

        "PY83-8B412-AAPIA01-ML" => "PY83-8B412-AAPIA1-ML",
        "PY83-8B413-AAPIA01-ML" => "PY83-8B413-AAPIA1-ML",

        "1B00002893AB-ML" => "1B0002893AB-ML",

        "SY7Y-8B411AA-ML" => "SY7Y-8B411-AA-ML",
        "SY7Y-403C52-AA-ML" => "SY7Y-403C52-AB-ML",

        "8890942418-ML" => "8890943418-ML",

        "SY7Y-018C22-A-PIA2-ML" => "SY7Y-018C22-A-PIA2ML",
        "SY7Y-018C23-A-PIA2-ML" => "SY7Y-018C23-A-PIA2ML",

        "31010080-1ML" => "310100801ML",

        "401//N5481-ML" => "401/N5481-ML",

        "8901090811-ML" => "890190811-ML",

        "AJ22 P6 C+HEAD MC-ML" => "AJ22 P6 C-HEAD MC-ML",
        "AJ22 P6 C+HEAD SC-ML" => "AJ22 P6 C-HEAD SC-ML",

        // DELETE cases
        "VW83-16E108-AA-ML" => "__DELETE__",
        "VW83-16E109-AA-ML" => "__DELETE__",
        "VW83-17224-AA-ML" => "__DELETE__",
        "VW83-17226-AA-ML" => "__DELETE__",

        "15014247M-BODY-ML" => "15014247 M-BODY-ML",

        "6G33-19A835-AML" => "6G33-17A835-AML",
        "4G43-133664-ML" => "4G43-13364-ML",

        "332/P1812-ML" => "332/P1825-ML",
        "332/8708-ML" => "332/T8708-ML",

        "AD23-166563-BA-PIAML" => "AD23-166563-BAPIAML",

        "334\\S7107-ML" => "334/S7107-ML",
        "334\\V6092-ML" => "334/V6092-ML",
        "335/H4676-ML" => "335/H4686-ML",
        "334//U8245-ML" => "334/U8245-ML",

        "KY63-F46805-AC-ML" => "KY63-F46806-AC-ML",

        "402/E3255-ML" => "402/E3255",

        "KY73-11464-AE-PIA6-ML" => "KY73-11464-AEPIA6-ML",
        "KY73-11464-AE-PIA7-ML" => "KY73-11464-AEPIA7-ML",
        "KY73-11464-AE-PIA8-ML" => "KY73-11464-AEPIA8-ML",

        "KY73-8C464-APIA01-ML" => "KY73-8C464-APIA01ML",
        "KY73-8C464-APIA02-ML" => "KY73-8C464-APIA02ML",
        "KY73-8C464-APIA03-ML" => "KY73-8C464-APIA03ML",

        "KY73-8C465-APIA01-ML" => "KY73-8C465-APIA01ML",
        "KY73-8C465-APIA02-ML" => "KY73-8C465-APIA02ML",
        "KY73-8C465-APIA03-ML" => "KY73-8C465-APIA03ML",

        "KY73-018C23-ABPIA01M" => "KY73-018C23-AAPIA01M",
        "KY73-018C23-ABPIA02M" => "KY73-018C23-AAPIA02M",

        "KY73-17A835-AAPIA1ML" => "KY73-17A835-AAPIA1-M",

        "MOULDED INS L-ML" => "MOLDED INS L-ML",
        "MOULDED INS R-ML" => "MOLDED INS R-ML",

        "TURB LID-ML" => "TURBO LID-ML",

        "MY83-17F889-AAPIA4ML" => "MY83-17F889-AAPIA3ML",
        "MY83-8B413-ACPIA1ML" => "MY83-8B413-AAPIA1ML",

        "AJ21 PISTON-TRAY-ML" => "AJ21 PISTON TRAY-ML",

        "D401034-ML" => "D401034- ML",
    ];

    return $map[$part_no] ?? $part_no;
}
// 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>";
    }
}