<?php

require_once __DIR__ . '/db.php';
require_once __DIR__ . '/orders.php';

function sparrow_customers_ensure_profile_table(): void
{
    $pdo = sparrow_db();

    $pdo->exec("
        CREATE TABLE IF NOT EXISTS customer_profiles (
            id INT UNSIGNED NOT NULL AUTO_INCREMENT,
            customer_key VARCHAR(100) NOT NULL,
            customer_notes TEXT NULL,
            merged_into_key VARCHAR(100) NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY customer_key_unique (customer_key)
        )
    ");
}

function sparrow_get_customer_profile(string $customer_key): array
{
    sparrow_customers_ensure_profile_table();

    $pdo = sparrow_db();

    $stmt = $pdo->prepare("
        SELECT *
        FROM customer_profiles
        WHERE customer_key = :customer_key
        LIMIT 1
    ");

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

    $profile = $stmt->fetch();

    if ($profile) {
        return $profile;
    }

    return [
        'customer_key' => $customer_key,
        'customer_notes' => '',
        'merged_into_key' => null,
    ];
}

function sparrow_save_customer_notes(string $customer_key, string $notes): void
{
    sparrow_customers_ensure_profile_table();

    $pdo = sparrow_db();

    $stmt = $pdo->prepare("
        INSERT INTO customer_profiles (customer_key, customer_notes)
        VALUES (:customer_key, :customer_notes)
        ON DUPLICATE KEY UPDATE
            customer_notes = VALUES(customer_notes)
    ");

    $stmt->execute([
        ':customer_key' => $customer_key,
        ':customer_notes' => $notes,
    ]);
}

function sparrow_merge_customer_key(string $source_key, string $target_key): void
{
    sparrow_customers_ensure_profile_table();

    if ($source_key === '' || $target_key === '' || $source_key === $target_key) {
        return;
    }

    $pdo = sparrow_db();

    $stmt = $pdo->prepare("
        INSERT INTO customer_profiles (customer_key, merged_into_key)
        VALUES (:source_key, :target_key)
        ON DUPLICATE KEY UPDATE
            merged_into_key = VALUES(merged_into_key)
    ");

    $stmt->execute([
        ':source_key' => $source_key,
        ':target_key' => $target_key,
    ]);
}

function sparrow_get_customers_merged_into(string $target_key): array
{
    sparrow_customers_ensure_profile_table();

    $pdo = sparrow_db();

    $stmt = $pdo->prepare("
        SELECT customer_key
        FROM customer_profiles
        WHERE merged_into_key = :target_key
        ORDER BY customer_key ASC
    ");

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

    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function sparrow_unmerge_customer_key(string $source_key): void
{
    sparrow_customers_ensure_profile_table();

    $pdo = sparrow_db();

    $stmt = $pdo->prepare("
        UPDATE customer_profiles
        SET merged_into_key = NULL
        WHERE customer_key = :source_key
        LIMIT 1
    ");

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

function sparrow_resolve_customer_key(string $customer_key): string
{
    sparrow_customers_ensure_profile_table();

    $pdo = sparrow_db();

    $current_key = $customer_key;
    $seen = [];

    for ($i = 0; $i < 10; $i++) {
        if ($current_key === '' || isset($seen[$current_key])) {
            break;
        }

        $seen[$current_key] = true;

        $stmt = $pdo->prepare("
            SELECT merged_into_key
            FROM customer_profiles
            WHERE customer_key = :customer_key
            LIMIT 1
        ");

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

        $merged_into = $stmt->fetchColumn();

        if (!$merged_into) {
            break;
        }

        $current_key = (string) $merged_into;
    }

    return $current_key;
}

function sparrow_customer_key_from_order(array $order): string
{
    $name = strtolower(trim((string) ($order['customer_name'] ?? '')));
    $email = strtolower(trim((string) ($order['customer_email'] ?? '')));
    $phone = preg_replace('/\D+/', '', (string) ($order['customer_phone'] ?? ''));

    if ($name !== '') {
        return 'name_' . md5($name);
    }

    if ($email !== '') {
        return 'email_' . md5($email);
    }

    if ($phone !== '') {
        return 'phone_' . md5($phone);
    }

    return 'unknown_' . md5((string) ($order['id'] ?? uniqid('', true)));
}

function sparrow_customer_order_balance(array $order): float
{
    $total_amount = (float) ($order['total_amount'] ?? 0);

    if ($total_amount <= 0 && function_exists('sparrow_calculate_order_pricing_from_lookup')) {
        $lookup = sparrow_calculate_order_pricing_from_lookup((int) ($order['id'] ?? 0));
        $total_amount = (float) ($lookup['retail_total'] ?? 0);
    }

    $paid_amount = (float) ($order['paid_amount'] ?? 0);
    $order_discount_amount = (float) ($order['order_discount_amount'] ?? 0);

    $customer_total_after_discount = max(0, $total_amount - $order_discount_amount);

    return max(0, $customer_total_after_discount - $paid_amount);
}

function sparrow_get_customers_summary(): array
{
    $orders = sparrow_get_orders();
    $customers = [];

    foreach ($orders as $order) {
        $raw_key = sparrow_customer_key_from_order($order);
$key = sparrow_resolve_customer_key($raw_key);

        if (!isset($customers[$key])) {
            $customers[$key] = [
                'customer_key' => $key,
				'source_keys' => [],
                'customer_name' => trim((string) ($order['customer_name'] ?? '')),
                'customer_email' => trim((string) ($order['customer_email'] ?? '')),
                'customer_phone' => trim((string) ($order['customer_phone'] ?? '')),
                'order_count' => 0,
                'active_orders' => 0,
                'delivered_orders' => 0,
                'total_ordered' => 0.00,
                'total_paid' => 0.00,
                'total_balance' => 0.00,
                'total_blessings' => 0.00,
                'last_order_date' => '',
            ];
        }

        if ($customers[$key]['customer_name'] === '' && !empty($order['customer_name'])) {
            $customers[$key]['customer_name'] = trim((string) $order['customer_name']);
        }

        if ($customers[$key]['customer_email'] === '' && !empty($order['customer_email'])) {
            $customers[$key]['customer_email'] = trim((string) $order['customer_email']);
        }

        if ($customers[$key]['customer_phone'] === '' && !empty($order['customer_phone'])) {
            $customers[$key]['customer_phone'] = trim((string) $order['customer_phone']);
        }

        $total_amount = (float) ($order['total_amount'] ?? 0);

        if ($total_amount <= 0 && function_exists('sparrow_calculate_order_pricing_from_lookup')) {
            $lookup = sparrow_calculate_order_pricing_from_lookup((int) ($order['id'] ?? 0));
            $total_amount = (float) ($lookup['retail_total'] ?? 0);
        }

        $paid_amount = (float) ($order['paid_amount'] ?? 0);
        $blessing_amount = (float) ($order['blessing_amount'] ?? 0);
        $balance = sparrow_customer_order_balance($order);

        $status = trim((string) ($order['status'] ?? ''));

        $customers[$key]['order_count']++;
		$customers[$key]['source_keys'][$raw_key] = true;
        $customers[$key]['total_ordered'] += $total_amount;
        $customers[$key]['total_paid'] += $paid_amount;
        $customers[$key]['total_balance'] += $balance;
        $customers[$key]['total_blessings'] += $blessing_amount;

        if (in_array($status, ['Delivered', 'Completed', 'Complete'], true)) {
            $customers[$key]['delivered_orders']++;
        } else {
            $customers[$key]['active_orders']++;
        }

        $order_date = trim((string) ($order['order_date'] ?? ''));

        if (
            $order_date !== ''
            && (
                $customers[$key]['last_order_date'] === ''
                || strtotime($order_date) > strtotime($customers[$key]['last_order_date'])
            )
        ) {
            $customers[$key]['last_order_date'] = $order_date;
        }
    }

    usort($customers, function ($a, $b) {
        return strtotime($b['last_order_date'] ?: '1970-01-01') <=> strtotime($a['last_order_date'] ?: '1970-01-01');
    });

    return $customers;
}

function sparrow_get_customer_orders_by_key(string $customer_key): array
{
    $orders = sparrow_get_orders();
    $matches = [];
    $resolved_customer_key = sparrow_resolve_customer_key($customer_key);

    foreach ($orders as $order) {
        $order_key = sparrow_customer_key_from_order($order);
        $resolved_order_key = sparrow_resolve_customer_key($order_key);

        if ($resolved_order_key === $resolved_customer_key) {
            $matches[] = $order;
        }
    }

    return $matches;
}

function sparrow_get_customer_merge_options(string $current_customer_key): array
{
    $current_customer_key = sparrow_resolve_customer_key($current_customer_key);

    $customers = sparrow_get_customers_summary();

    return array_values(array_filter($customers, function ($customer) use ($current_customer_key) {
        return ($customer['customer_key'] ?? '') !== $current_customer_key;
    }));
}

function sparrow_update_customer_history_contact(
    string $customer_key,
    string $customer_name,
    string $customer_email,
    string $customer_phone
): void {
    $customer_key = sparrow_resolve_customer_key(
        trim($customer_key)
    );

    $customer_name = trim($customer_name);
    $customer_email = trim($customer_email);
    $customer_phone = trim($customer_phone);

    if ($customer_key === '') {
        throw new RuntimeException(
            'Customer history could not be identified.'
        );
    }

    if ($customer_name === '') {
        throw new RuntimeException(
            'Customer name is required.'
        );
    }

    if (
        $customer_email !== ''
        && !filter_var(
            $customer_email,
            FILTER_VALIDATE_EMAIL
        )
    ) {
        throw new RuntimeException(
            'Please enter a valid customer email address.'
        );
    }

    /*
     * Get the exact orders currently belonging
     * to this customer history before changing
     * any names or contact information.
     */
    $orders = sparrow_get_customer_orders_by_key(
        $customer_key
    );

    if (!$orders) {
        throw new RuntimeException(
            'No orders were found for this customer.'
        );
    }

    /*
     * Work out the raw key that the new customer
     * name will create.
     */
    $new_name_key = sparrow_customer_key_from_order([
        'customer_name' => $customer_name,
        'customer_email' => $customer_email,
        'customer_phone' => $customer_phone,
    ]);

    /*
     * If that new key already belongs to a different
     * customer history, stop instead of accidentally
     * combining two customers.
     */
    if ($new_name_key !== $customer_key) {
        $resolved_new_key =
            sparrow_resolve_customer_key(
                $new_name_key
            );

        $other_orders =
            sparrow_get_customer_orders_by_key(
                $new_name_key
            );

        if (
            $other_orders
            && $resolved_new_key !== $customer_key
        ) {
            throw new RuntimeException(
                'That customer name is already connected '
                . 'to another customer history. No changes '
                . 'were made.'
            );
        }
    }

    $order_ids = [];

    foreach ($orders as $order) {
        $order_id = (int) ($order['id'] ?? 0);

        if ($order_id > 0) {
            $order_ids[] = $order_id;
        }
    }

    if (!$order_ids) {
        throw new RuntimeException(
            'No valid orders were found to update.'
        );
    }

    $pdo = sparrow_db();
    $pdo->beginTransaction();

    try {
        /*
         * Update only the orders that were already
         * confirmed as belonging to this history.
         */
        $update_stmt = $pdo->prepare("
            UPDATE orders
            SET
                customer_name = :customer_name,
                customer_email = :customer_email,
                customer_phone = :customer_phone
            WHERE id = :order_id
            LIMIT 1
        ");

        foreach ($order_ids as $order_id) {
            $update_stmt->execute([
                ':customer_name' =>
                    $customer_name,

                ':customer_email' =>
                    $customer_email !== ''
                        ? $customer_email
                        : null,

                ':customer_phone' =>
                    $customer_phone !== ''
                        ? $customer_phone
                        : null,

                ':order_id' => $order_id,
            ]);
        }

        /*
         * A changed name creates a different raw
         * customer key. Point that new key back to
         * the existing customer history instead of
         * replacing the history's permanent key.
         *
         * This keeps notes, merges, and portal
         * accounts attached to the same history.
         */
        if ($new_name_key !== $customer_key) {
            $key_stmt = $pdo->prepare("
                INSERT INTO customer_profiles
                (
                    customer_key,
                    merged_into_key
                )
                VALUES
                (
                    :new_name_key,
                    :customer_key
                )
                ON DUPLICATE KEY UPDATE
                    merged_into_key =
                        VALUES(merged_into_key)
            ");

            $key_stmt->execute([
                ':new_name_key' =>
                    $new_name_key,

                ':customer_key' =>
                    $customer_key,
            ]);
        }

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

        throw $e;
    }
}