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

$batch_size = 3000;
$table_name = "item_management";
$records_inserted = 0;
$records_updated = 0;  

$created_by = $_SESSION['user_id'];
$updated_by = $_SESSION['user_id'];
$created_on = date('Y-m-d H:i:s');

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

while (!feof($fp)) {

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

    if ($flag) { 

        if ($buffer) {

            $variable_array = array(
                "Part_Number","Product_Description",
                "Part_Status","Usual_Source","Stock_UOM","Product_Group",
                "Man_Lead_Time","Default_Workshop","Default_Receipt_Location",
                "Default_Issue_Location","Quantity_In_Stock","Quantity_Allocated",
                "Minimum_Stock","Maximum_Stock","Minimum_EBQ","Maximum_EBQ",
                "Standard_Cost_EBQ","Pack_Quantity","Standard_Cost","Nominal_Code_Home","Nominal_Code_Purchase","Packing_Code",
                "Recycle_Code","Drawing_Issue","UPH"
            );

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

            if ($Part_Number == "Part_Number" || empty($Part_Number)) {
                continue;
            }

            // ================= CHECK PART EXIST =================
            $part_id = utility::getTableFieldById(
                "item_management",
                "id",
                "part_number",
                $Part_Number
            );

            $product_group_id = utility::getTableFieldById(
                "product_group",
                "id",
                "product_group_name",
                $Product_Group
            );

            $Default_Issue_Location_id = utility::getTableFieldById(
                "default_receipt_location",
                "id",
                "receipt_location_code",
                $Default_Issue_Location
            );

            if (!empty($part_id)) {

                // ================= UPDATE item_management =================
                $records_updated++;

                $sql = "UPDATE $table_name SET 
                        product_description = '$Product_Description',
                        part_status = '$Part_Status',
                        usual_source = '$Usual_Source',
                        stock_uom = '$Stock_UOM',
                        product_group = '$product_group_id',
                        man_lead_time = '$Man_Lead_Time',
                        mpe = '$Default_Issue_Location_id',
                        mks = '$Maximum_EBQ',
						price_uom = '$Minimum_EBQ',
                        standard_cost_ebq = '$Standard_Cost_EBQ',
						pack_quantity = '$Pack_Quantity',
                        standard_cost = '$Standard_Cost',
						nominal_code_sales = '$Nominal_Code_Home',
						nominal_code_purchase = '$Nominal_Code_Purchase',
                        drawing_issue = '$Drawing_Issue',
                        updated_by = '$updated_by',
                        updated_on = '$created_on'
                        WHERE id = '$part_id'";

                mysqli_query($conn, $sql);

                // ================= UPDATE/INSERT product_name =================
                $product_name_id = utility::getTableFieldById(
                    "product_name",
                    "id",
                    "pro_id",
                    $part_id
                );

                if (!empty($product_name_id)) {

                    // UPDATE product_name
                    $sql_product_name_update = "UPDATE product_name SET
                            group_number = '$Part_Number',
                            group_code = '$Usual_Source',
                            group_name = '$Product_Description',
                            qty = '1'
                            WHERE pro_id = '$part_id'";

                    mysqli_query($conn, $sql_product_name_update);

                    // ✅ NEW: ALSO UPDATE part_number_id DURING UPDATE
                    mysqli_query($conn, "UPDATE item_management 
                            SET part_number_id = '$product_name_id' 
                            WHERE id = '$part_id'");

                } else {

                    // INSERT product_name
                    $sql_product_name_insert = "INSERT INTO product_name
                            (`pro_id`,`group_number`,`group_code`,`group_name`,`qty`)
                            VALUES
                            ('$part_id','$Part_Number','$Usual_Source','$Product_Description','1')";

                    mysqli_query($conn, $sql_product_name_insert);

                    // update item_management.part_number_id
                    $product_name_insert_id = mysqli_insert_id($conn);

                    mysqli_query($conn, "UPDATE item_management 
                            SET part_number_id = '$product_name_insert_id' 
                            WHERE id = '$part_id'");
                }

            } else {

                // ================= INSERT item_management =================
                $records_inserted++;

                $sql = "INSERT INTO $table_name 
                        (`part_number`,`product_description`,`part_status`,
                         `usual_source`,`stock_uom`,`product_group`,
                         `man_lead_time`,`mpe`,`mks`,`price_uom`,
                         `standard_cost_ebq`,`pack_quantity`,`standard_cost`,`nominal_code_sales`,`nominal_code_purchase`,`drawing_issue`,`created_by`,`created_on`) 
                        VALUES 
                        ('$Part_Number','$Product_Description','$Part_Status',
                         '$Usual_Source','$Stock_UOM','$product_group_id',
                         '$Man_Lead_Time','$Default_Issue_Location_id','$Maximum_EBQ','$Minimum_EBQ',
                         '$Standard_Cost_EBQ','$Pack_Quantity','$Standard_Cost','$Nominal_Code_Home','$Nominal_Code_Purchase','$Drawing_Issue','$created_by','$created_on')";

                mysqli_query($conn, $sql);

                $part_id = mysqli_insert_id($conn);

                // INSERT product_name
                $sql_product_name = "INSERT INTO product_name
                        (`pro_id`,`group_number`,`group_code`,`group_name`,`qty`)
                        VALUES
                        ('$part_id','$Part_Number','$Usual_Source','$Product_Description','1')";

                mysqli_query($conn, $sql_product_name);

                // update item_management.part_number_id
                $product_name_insert_id = mysqli_insert_id($conn);

                mysqli_query($conn, "UPDATE item_management 
                        SET part_number_id = '$product_name_insert_id' 
                        WHERE id = '$part_id'");
            }

            // ================= INVENTORY =================
            $inventory_id = utility::getTableFieldById(
                "inventory",
                "id",
                "item_id",
                $part_id
            );

            if (!empty($inventory_id)) {

                $sql = "UPDATE inventory SET
                        quantity_in_stock = '$Quantity_In_Stock',
                        quantity_allocated = '$Quantity_Allocated',
                        minimum_stock = '$Minimum_Stock',
                        maximum_stock = '$Maximum_Stock',
                        updated_by = '$updated_by',
                        updated_on = '$created_on'
                        WHERE id = '$inventory_id'";

                mysqli_query($conn, $sql);

            } else {

                $sql = "INSERT INTO inventory
                        (`item_id`,`quantity_in_stock`,
                         `quantity_allocated`,`minimum_stock`,
                         `maximum_stock`,`created_by`,`created_on`)
                        VALUES
                        ('$part_id','$Quantity_In_Stock',
                         '$Quantity_Allocated','$Minimum_Stock',
                         '$Maximum_Stock','$created_by','$created_on')";

                mysqli_query($conn, $sql);
            }
        }
    }
}

fclose($fp);
?>

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

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