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

$table_name = "bom";

$records_inserted = 0;
$records_updated  = 0;
$records_deleted  = 0;

// Track CSV data per assembly
$csv_data = [];

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

while (!feof($fp)) {

    $buffer = fgetcsv($fp, 1000000, ',');

    if (!$buffer) continue;

    // Map CSV columns
    $Assembly_Part_Number  = trim($buffer[0]);
    $Sequence_Number       = trim($buffer[1]);
    $Component_Part_Number = trim($buffer[2]);
    $Quantity_Required     = trim($buffer[3]);

    // Skip header
    if ($Assembly_Part_Number == "Assembly_Part_Number") continue;

    // Skip empty rows
    if (empty($Assembly_Part_Number) || empty($Component_Part_Number)) continue;

    // Get IDs
    $part_id_main = utility::getTableFieldById(
        "item_management",
        "id",
        "part_number",
        $Assembly_Part_Number
    );

    $part_id_com = utility::getTableFieldById(
        "item_management",
        "id",
        "part_number",
        $Component_Part_Number
    );

    if (!$part_id_main || !$part_id_com) continue;

    // Get type
    $typee = utility::getTableFieldById(
        "item_management",
        "usual_source",
        "part_number",
        $Component_Part_Number
    );

    // Track CSV combinations per assembly
    $csv_data[$part_id_main][] = $part_id_com;

    // ✅ UPSERT (Insert or Update)
    $sql = "INSERT INTO $table_name 
            (`main_product_id`,`sub_part_id`,`sub_part_name`,`quantity_required`,`type`) 
            VALUES 
            ('$part_id_main','$part_id_com','$Component_Part_Number','$Quantity_Required','$typee')
            ON DUPLICATE KEY UPDATE 
                sub_part_name = VALUES(sub_part_name),
                quantity_required = VALUES(quantity_required),
                type = VALUES(type)";

    mysqli_query($conn, $sql);

    if (mysqli_affected_rows($conn) == 1) {
        $records_inserted++;
    } else {
        $records_updated++;
    }
}

fclose($fp);


// ✅ DELETE missing components (per assembly)
foreach ($csv_data as $main_id => $sub_parts_csv) {

    // Convert array to string
    $sub_parts_csv = array_unique($sub_parts_csv);
    $sub_parts_list = implode(",", $sub_parts_csv);

    // Delete records not in CSV for this assembly
    $delete_sql = "DELETE FROM $table_name 
                   WHERE main_product_id = '$main_id'
                   AND sub_part_id NOT IN ($sub_parts_list)";

    mysqli_query($conn, $delete_sql);

    $records_deleted += mysqli_affected_rows($conn);
}
?>

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

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

if($records_deleted){    
  echo '<br><span style="font-family:calibri;color:red;">'.$records_deleted.' records deleted...</span>';
}
?>