<?php

require_once __DIR__ . '/../app/auth.php';
require_once __DIR__ . '/../app/layout.php';
require_once __DIR__ . '/../app/orders.php';
require_once __DIR__ . '/../app/db.php';

$user = sparrow_require_backyard_login();

function sparrow_temp_csv_money($value): float
{
    $value = preg_replace('/[^0-9.\-]/', '', (string) $value);

    if ($value === '' || !is_numeric($value)) {
        return 0.00;
    }

    return (float) $value;
}

function sparrow_temp_find_order_for_wp_merge(int $wp_entry_id, string $order_number): ?array
{
    $pdo = sparrow_db();

    if ($wp_entry_id > 0) {
        $stmt = $pdo->prepare("
            SELECT *
            FROM orders
            WHERE original_forminator_entry_id = :entry_id
            LIMIT 1
        ");

        $stmt->execute([
            ':entry_id' => $wp_entry_id,
        ]);

        $order = $stmt->fetch();

        if ($order) {
            return $order;
        }
    }

    if ($order_number !== '') {
        $stmt = $pdo->prepare("
            SELECT *
            FROM orders
            WHERE order_number = :order_number
            LIMIT 1
        ");

        $stmt->execute([
            ':order_number' => $order_number,
        ]);

        $order = $stmt->fetch();

        if ($order) {
            return $order;
        }
    }

    return null;
}

function sparrow_temp_guess_item_type(string $desc): string
{
    $text = strtolower($desc);

    if (
        strpos($text, 'koozie') !== false ||
        strpos($text, 'coozie') !== false ||
        strpos($text, 'can cooler') !== false ||
        strpos($text, 'can holder') !== false
    ) {
        return 'Koozie';
    }

    return 'Shirt';
}

function sparrow_temp_extract_color(string $desc): ?string
{
    $desc = trim($desc);

    if ($desc === '') {
        return null;
    }

    // Shirt examples:
    // "Maroon Shirt, Size Medium - Front & Back"
    // "Heather Royal Shirt, Size Large - Full Front - Normal isn't coming back"
    if (preg_match('/^(.+?)\s+Shirt\b/i', $desc, $matches)) {
        $color = trim($matches[1]);

        return $color !== '' ? $color : null;
    }

    // Koozie examples:
    // "Koozie - Black - Daigle"
    // "Koozie - Black - No-Name"
    if (preg_match('/^Koozie\s*-\s*([^-]+)(?:\s*-|$)/i', $desc, $matches)) {
        $color = trim($matches[1]);

        return $color !== '' ? $color : null;
    }

    return null;
}

function sparrow_temp_extract_item_notes(string $desc): ?string
{
    $desc = trim($desc);

    if ($desc === '') {
        return null;
    }

    // Koozie - Black - Daigle
    // Return Daigle
    if (preg_match('/^Koozie\s*-\s*[^-]+\s*-\s*(.+)$/i', $desc, $matches)) {
        $notes = trim($matches[1]);

        return $notes !== '' ? $notes : null;
    }

    // Shirt, Size Medium - Full Front - Psalm 111:10
    // Return Psalm 111:10
    $parts = array_map('trim', explode(' - ', $desc));

    if (count($parts) >= 3) {
        $notes = trim(implode(' - ', array_slice($parts, 2)));

        return $notes !== '' ? $notes : null;
    }

    return null;
}

function sparrow_temp_extract_size(string $desc): ?string
{
    $patterns = [
        'XS' => '/\b(xs|x-small|extra small|extra-small)\b/i',
        'S' => '/\b(s|small)\b/i',
        'M' => '/\b(m|medium)\b/i',
        'L' => '/\b(l|large)\b/i',
        'XL' => '/\b(xl|x-large|extra large|extra-large)\b/i',
        '2XL' => '/\b(2xl|2x|xxl|2x-large|2x large)\b/i',
        '3XL' => '/\b(3xl|3x|3x-large|3x large)\b/i',
        '4XL' => '/\b(4xl|4x|4x-large|4x large)\b/i',
        '5XL' => '/\b(5xl|5x|5x-large|5x large)\b/i',
    ];

    foreach ($patterns as $size => $pattern) {
        if (preg_match($pattern, $desc)) {
            return $size;
        }
    }

    return null;
}

function sparrow_temp_extract_print_location(string $desc): ?string
{
    $text = strtolower($desc);

    if (strpos($text, 'front & back') !== false || strpos($text, 'front and back') !== false || strpos($text, 'double sided') !== false) {
        return 'Front & Back';
    }

    if (strpos($text, 'sleeve') !== false) {
        return 'Sleeve';
    }

    if (strpos($text, 'back') !== false) {
        return 'Back';
    }

    if (strpos($text, 'front') !== false) {
        return 'Full Front';
    }

    return null;
}

function sparrow_temp_extract_specialty(string $desc): string
{
    $text = strtolower($desc);

    $specialties = [
        'glitter' => 'Glitter',
        'glow' => 'Glow',
        'foil' => 'Foil',
        'gold foil' => 'Gold Foil',
        'puff' => 'Puff',
        'iridescent' => 'Iridescent',
        'thermochromic' => 'Thermochromic',
        'pearl' => 'Pearl',
    ];

    foreach ($specialties as $needle => $label) {
        if (strpos($text, $needle) !== false) {
            return $label;
        }
    }

    return 'None';
}

function sparrow_temp_import_items_for_new_order(int $order_id, array $rows): void
{
    $pdo = sparrow_db();

    $stmt = $pdo->prepare("
        INSERT INTO order_items
        (
            order_id,
            item_type,
            quantity,
            shirt_size,
            shirt_color,
            specialty,
            print_location,
            item_notes
        )
        VALUES
        (
            :order_id,
            :item_type,
            :quantity,
            :shirt_size,
            :shirt_color,
            :specialty,
            :print_location,
            :item_notes
        )
    ");

    foreach ($rows as $row) {
        $desc = trim((string) ($row['item_desc'] ?? ''));
        $qty = max(1, (int) sparrow_temp_csv_money($row['item_qty'] ?? 1));

        if ($desc === '' && $qty <= 0) {
            continue;
        }

        $item_type = sparrow_temp_guess_item_type($desc);

$stmt->execute([
    ':order_id' => $order_id,
    ':item_type' => $item_type,
    ':quantity' => $qty,
    ':shirt_size' => $item_type === 'Koozie' ? null : sparrow_temp_extract_size($desc),
    ':shirt_color' => sparrow_temp_extract_color($desc),
    ':specialty' => sparrow_temp_extract_specialty($desc),
    ':print_location' => sparrow_temp_extract_print_location($desc),
    ':item_notes' => sparrow_temp_extract_item_notes($desc),
]);
    }
}

function sparrow_temp_replace_items_for_existing_order(int $order_id, array $rows): void
{
    $pdo = sparrow_db();

    $delete_stmt = $pdo->prepare("
        DELETE FROM order_items
        WHERE order_id = :order_id
    ");

    $delete_stmt->execute([
        ':order_id' => $order_id,
    ]);

    sparrow_temp_import_items_for_new_order($order_id, $rows);

    $total_qty = 0;

    foreach ($rows as $row) {
        $total_qty += max(1, (int) sparrow_temp_csv_money($row['item_qty'] ?? 1));
    }

    $first_row = $rows[0] ?? [];
    $first_desc = trim((string) ($first_row['item_desc'] ?? ''));

    $stmt = $pdo->prepare("
        UPDATE orders
        SET
            quantity = :quantity,
            shirt_size = :shirt_size,
            shirt_color = :shirt_color,
            specialty = :specialty,
            print_location = :print_location
        WHERE id = :id
        LIMIT 1
    ");

    $stmt->execute([
        ':id' => $order_id,
        ':quantity' => $total_qty,
        ':shirt_size' => sparrow_temp_extract_size($first_desc),
        ':shirt_color' => '',
        ':specialty' => sparrow_temp_extract_specialty($first_desc),
        ':print_location' => sparrow_temp_extract_print_location($first_desc),
    ]);
}

function sparrow_temp_create_wp_merge_order(array $order_data, array $rows): int
{
    $pdo = sparrow_db();

    $first_row = $rows[0] ?? [];

    $order_number = trim((string) ($order_data['order_number'] ?? ''));
    $wp_entry_id = (int) ($order_data['wp_entry_id'] ?? 0);

    if ($order_number === '') {
        $order_number = sparrow_get_next_order_number();
    }

    $customer_name = trim((string) ($order_data['customer_name'] ?? ''));

    if ($customer_name === '') {
        $customer_name = 'Imported Customer';
    }

    $order_date_raw = trim((string) ($order_data['order_date'] ?? ''));
    $order_date = $order_date_raw !== '' ? date('Y-m-d', strtotime($order_date_raw)) : date('Y-m-d');

    $stmt = $pdo->prepare("
        INSERT INTO orders
        (
            order_number,
            original_forminator_entry_id,
            customer_name,
            customer_email,
            customer_phone,
            order_date,
            status,
            quantity,
            notes,
            total_amount,
            paid_amount,
            blessing_amount,
            order_discount_amount,
            delivery_method,
            delivery_notes,
            admin_notes,
            approval_status
        )
        VALUES
        (
            :order_number,
            :original_forminator_entry_id,
            :customer_name,
            :customer_email,
            :customer_phone,
            :order_date,
            :status,
            :quantity,
            :notes,
            :total_amount,
            :paid_amount,
            :blessing_amount,
            :order_discount_amount,
            :delivery_method,
            :delivery_notes,
            :admin_notes,
            :approval_status
        )
    ");

    $total_qty = 0;

    foreach ($rows as $row) {
        $total_qty += max(1, (int) sparrow_temp_csv_money($row['item_qty'] ?? 1));
    }

    $stmt->execute([
        ':order_number' => $order_number,
        ':original_forminator_entry_id' => $wp_entry_id ?: null,
        ':customer_name' => $customer_name,
        ':customer_email' => trim((string) ($order_data['customer_email'] ?? '')) ?: null,
        ':customer_phone' => trim((string) ($order_data['customer_phone'] ?? '')) ?: null,
        ':order_date' => $order_date,
        ':status' => trim((string) ($order_data['status'] ?? 'Order Received')) ?: 'Order Received',
        ':quantity' => $total_qty,
        ':notes' => trim((string) ($order_data['order_notes'] ?? '')) ?: null,
        ':total_amount' => sparrow_temp_csv_money($order_data['final_total_amount'] ?? 0),
        ':paid_amount' => sparrow_temp_csv_money($order_data['payment_applied_amount'] ?? $order_data['amount_paid'] ?? 0),
        ':blessing_amount' => sparrow_temp_csv_money($order_data['blessing_amount'] ?? 0),
        ':order_discount_amount' => sparrow_temp_csv_money($order_data['discount_amount'] ?? 0),
        ':delivery_method' => trim((string) ($order_data['delivery_method'] ?? '')) ?: null,
        ':delivery_notes' => trim((string) ($order_data['delivery_notes'] ?? '')) ?: null,
        ':admin_notes' => trim((string) ($order_data['admin_notes'] ?? '')) ?: null,
        ':approval_status' => sparrow_temp_normalize_approval_status($order_data['approval_status'] ?? 'waiting'),
    ]);

    $order_id = (int) $pdo->lastInsertId();

    sparrow_temp_import_items_for_new_order($order_id, $rows);

    return $order_id;
}

function sparrow_temp_normalize_approval_status($status): string
{
    $status = strtolower(trim((string) $status));

    if ($status === 'pending') {
        return 'waiting';
    }

    if ($status === 'approved') {
        return 'approved';
    }

    if ($status === 'changes_requested' || $status === 'changes requested') {
        return 'changes_requested';
    }

    if ($status === 'waiting' || $status === '') {
        return 'waiting';
    }

    return 'waiting';
}

function sparrow_temp_update_wp_merge_order(int $order_id, array $order_data): void
{
    $pdo = sparrow_db();

    $stmt = $pdo->prepare("
        UPDATE orders
        SET
            original_forminator_entry_id = :original_forminator_entry_id,
            customer_name = :customer_name,
            customer_email = :customer_email,
            customer_phone = :customer_phone,
            status = :status,
            total_amount = :total_amount,
            paid_amount = :paid_amount,
            blessing_amount = :blessing_amount,
            payment_method = COALESCE(NULLIF(:payment_method, ''), payment_method),
            order_discount_amount = :order_discount_amount,
            delivery_method = :delivery_method,
            delivery_notes = :delivery_notes,
            admin_notes = :admin_notes,
            approval_status = :approval_status
        WHERE id = :id
        LIMIT 1
    ");

    $stmt->execute([
        ':id' => $order_id,
        ':original_forminator_entry_id' => ((int) ($order_data['wp_entry_id'] ?? 0)) ?: null,
        ':customer_name' => trim((string) ($order_data['customer_name'] ?? '')) ?: 'Imported Customer',
        ':customer_email' => trim((string) ($order_data['customer_email'] ?? '')) ?: null,
        ':customer_phone' => trim((string) ($order_data['customer_phone'] ?? '')) ?: null,
        ':status' => trim((string) ($order_data['status'] ?? 'Order Received')) ?: 'Order Received',
        ':total_amount' => sparrow_temp_csv_money($order_data['final_total_amount'] ?? 0),
        ':paid_amount' => sparrow_temp_csv_money($order_data['payment_applied_amount'] ?? $order_data['amount_paid'] ?? 0),
        ':blessing_amount' => sparrow_temp_csv_money($order_data['blessing_amount'] ?? 0),
        ':payment_method' => trim((string) ($order_data['payment_method'] ?? '')),
        ':order_discount_amount' => sparrow_temp_csv_money($order_data['discount_amount'] ?? 0),
        ':delivery_method' => trim((string) ($order_data['delivery_method'] ?? '')) ?: null,
        ':delivery_notes' => trim((string) ($order_data['delivery_notes'] ?? '')) ?: null,
        ':admin_notes' => trim((string) ($order_data['admin_notes'] ?? '')) ?: null,
        ':approval_status' => sparrow_temp_normalize_approval_status($order_data['approval_status'] ?? 'waiting'),
    ]);
}

if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
    sparrow_redirect('/backyard/import.php');
}

try {
    if (empty($_FILES['wp_merge_csv']['tmp_name'])) {
        throw new RuntimeException('Please choose a WordPress merge CSV file.');
    }

    $file = $_FILES['wp_merge_csv'];

    if (($file['error'] ?? UPLOAD_ERR_NO_FILE) !== UPLOAD_ERR_OK) {
        throw new RuntimeException('The WordPress merge CSV could not be uploaded.');
    }

    $handle = fopen($file['tmp_name'], 'r');

    if (!$handle) {
        throw new RuntimeException('The WordPress merge CSV could not be opened.');
    }

    $headers = fgetcsv($handle);

    if (!$headers) {
        throw new RuntimeException('The WordPress merge CSV is missing a header row.');
    }

    $headers = array_map(function ($header) {
        return strtolower(trim((string) $header));
    }, $headers);

    $groups = [];

    while (($row = fgetcsv($handle)) !== false) {
        $data = [];

        foreach ($headers as $index => $header) {
            $data[$header] = $row[$index] ?? '';
        }

        if (($data['export_type'] ?? '') !== '' && ($data['export_type'] ?? '') !== 'backyard_merge_orders') {
            continue;
        }

        $wp_entry_id = (int) ($data['wp_entry_id'] ?? 0);
        $order_number = trim((string) ($data['order_number'] ?? ''));

        $group_key = $wp_entry_id > 0 ? 'wp_' . $wp_entry_id : 'order_' . $order_number;

        if ($group_key === 'order_') {
            continue;
        }

        if (!isset($groups[$group_key])) {
            $groups[$group_key] = [
                'order_data' => $data,
                'rows' => [],
            ];
        }

        $groups[$group_key]['rows'][] = $data;
    }

    fclose($handle);

    $pdo = sparrow_db();

    $created = 0;
    $updated = 0;
    $skipped = 0;

    $pdo->beginTransaction();

    try {
        foreach ($groups as $group) {
            $order_data = $group['order_data'];
            $rows = $group['rows'];

            $wp_entry_id = (int) ($order_data['wp_entry_id'] ?? 0);
            $order_number = trim((string) ($order_data['order_number'] ?? ''));

            $existing_order = sparrow_temp_find_order_for_wp_merge($wp_entry_id, $order_number);

            if ($existing_order) {
    sparrow_temp_update_wp_merge_order((int) $existing_order['id'], $order_data);

    sparrow_temp_replace_items_for_existing_order(
        (int) $existing_order['id'],
        $rows
    );

    $updated++;
} else {
    $skipped++;
    continue;
}
        }

        $pdo->commit();
    } catch (Throwable $e) {
        $pdo->rollBack();
        throw $e;
    }

    sparrow_redirect('/backyard/import.php?wp_merge_imported=1&created=' . $created . '&updated=' . $updated . '&skipped=' . $skipped);
} catch (Throwable $e) {
    sparrow_redirect('/backyard/import.php?wp_merge_error=' . urlencode($e->getMessage()));
}