<?php
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     = "customer_addresses.csv";
$destfile = $destdir.$name;
$table_name = "client_management";
$records_inserted = 0;
$records_updated  = 0;

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

// ── 1. Read entire CSV first, group rows by Customer_Account_Code ──────────
$customers = []; // keyed by Customer_Account_Code
$fp   = fopen($destfile, 'r');
$flag = true;

while (!feof($fp)) {
    $buffer = fgetcsv($fp, 1000000, ',');
    if (!$buffer) continue;

    if ($flag) {
        $variable_array = [
            "Customer_Account_Code","Customer_Address_Code","Customer_Name",
            "Address_1","Address_2","Address_3","Address_4","Address_5",
            "Postcode","Telephone","Fax_Number","Email",
            "Country_Code","Currency_Code"
        ];
        foreach ($variable_array as $key => $val) {
            ${$val} = addslashes(trim($buffer[$key] ?? ''));
        }

        // Skip header row
        if ($Customer_Account_Code === "Customer Account Code") continue;
        if (empty($Customer_Account_Code)) continue;

        // Group addresses under each customer account code
        if (!isset($customers[$Customer_Account_Code])) {
            $customers[$Customer_Account_Code] = [
                'name'      => $Customer_Name,
                'email'     => $Email,
                'telephone' => $Telephone,
                'country'   => $Country_Code,
                'currency'  => $Currency_Code,
                'addresses' => [],
            ];
        }

        // Collect every address row for this customer
        $customers[$Customer_Account_Code]['addresses'][] = [
            'address_code' => $Customer_Address_Code,
            'address_1'    => $Address_1,
            'address_2'    => $Address_2,
            'address_3'    => $Address_3,
            'address_4'    => $Address_4,
            'address_5'    => $Address_5,
            'postcode'     => $Postcode,
        ];
    }
}
fclose($fp);

// ── 2. Process each customer ───────────────────────────────────────────────
foreach ($customers as $account_code => $data) {

    $country_id  = utility::getTableFieldById("country",  "id", "country_code",    $data['country']);
    $currency_id = utility::getTableFieldById("currency", "id", "currency_name",   $data['currency']);
    $customer_name = $data['name'];

    // Encrypt contact fields
    $enc_email = (!empty($data['email']))     ? utility::app_crypt($data['email'],     'e') : '';
    $enc_phone = (!empty($data['telephone'])) ? utility::app_crypt($data['telephone'], 'e') : '';

    // ── 2a. Upsert client_management (no contact/address fields on main row) ──
    $customer_id = utility::getTableFieldById("client_management", "id", "customer_code", $account_code);

    if (!empty($customer_id)) {
        $records_updated++;
        mysqli_query($conn, "UPDATE client_management SET
            customer_name        = '$customer_name',
            currency             = '$currency_id',
            default_country_code = '$country_id',
            updated_by           = '$updated_by',
            updated_on           = '$created_on'
            WHERE id = '$customer_id'");
    } else {
        $records_inserted++;
        mysqli_query($conn, "INSERT INTO client_management
            (customer_code, customer_name, currency, default_country_code, created_by, created_on, updated_by)
            VALUES
            ('$account_code','$customer_name','$currency_id','$country_id','$created_by','$created_on','$updated_by')");
        $customer_id = mysqli_insert_id($conn);
    }

    // ── 2b. Delete existing mapping records for this customer ──────────────
    mysqli_query($conn, "DELETE FROM client_delivery_address_mapping WHERE customer_id = '$customer_id'");
    mysqli_query($conn, "DELETE FROM client_contact_mapping          WHERE customer_id = '$customer_id'");

    // ── 2c. Insert contact into client_contact_mapping ─────────────────────
    if (!empty($data['email']) || !empty($data['telephone'])) {
        mysqli_query($conn, "INSERT INTO client_contact_mapping
            (customer_id, title, first_name, surname, email_address, phone_no, created_by, created_on, updated_by)
            VALUES
            ('$customer_id','','','','$enc_email','$enc_phone','$created_by','$created_on','$updated_by')");
    }

    // ── 2d. Insert all delivery addresses ──────────────────────────────────
    $first_address = null;

    foreach ($data['addresses'] as $addr) {
        $address_code = addslashes($addr['address_code']);

        // Build delivery_address_1 from Address_1/2/3 (same logic as original)
        $parts = array_filter([$addr['address_1'], $addr['address_2'], $addr['address_3']]);
        $full_address = implode(', ', $parts);

        $del_addr_1  = addslashes($full_address);
        $del_addr_2  = addslashes($addr['address_4']);
        $del_addr_3  = addslashes($addr['address_5']);
        $postcode    = addslashes($addr['postcode']);

        mysqli_query($conn, "INSERT INTO client_delivery_address_mapping
            (customer_id, address_code, delivery_address_1, delivery_address_2, delivery_address_3, delivery_add_postcode, created_by, created_on, updated_by)
            VALUES
            ('$customer_id','$address_code','$del_addr_1','$del_addr_2','$del_addr_3','$postcode','$created_by','$created_on','$updated_by')");

        // Remember first address for invoice address
        if ($first_address === null) {
            $first_address = [
                'address_1' => $del_addr_1,
                'address_2' => $del_addr_2,
                'address_3' => $del_addr_3,
                'postcode'  => $postcode,
            ];
        }
    }

    // ── 2e. Update invoice address on main record = first delivery address ─
    if ($first_address !== null) {
        mysqli_query($conn, "UPDATE client_management SET
            invoice_address_1    = '{$first_address['address_1']}',
            invoice_address_2    = '{$first_address['address_2']}',
            invoice_address_3    = '{$first_address['address_3']}',
            invoice_add_postcode = '{$first_address['postcode']}'
            WHERE id = '$customer_id'");
    }
}
?>

<?php if ($records_inserted): ?>
    <br><span style="font-family:calibri;color:red;"><?= $records_inserted ?> records inserted.</span>
<?php endif; ?>
<?php if ($records_updated): ?>
    <br><span style="font-family:calibri;color:red;"><?= $records_updated ?> records updated.</span>
<?php endif; ?>