<?php
declare(strict_types=1);

function find_customer_matches(mysqli $conn, string $phone = '', string $plateNumber = '', int $limit = 8): array
{
    ensure_customer_group_column($conn);
    ensure_car_profile_columns($conn);

    $phoneDigits = normalize_phone($phone);
    $plateNumber = normalize_plate($plateNumber);
    if ($phoneDigits === '' && $plateNumber === '') {
        return array();
    }

    $params = array();
    $types = '';
    $whereParts = array();

    if ($phoneDigits !== '') {
        $whereParts[] = 'cust.normalized_phone = ?';
        $params[] = $phoneDigits;
        $types .= 's';
    }

    if ($plateNumber !== '') {
        $whereParts[] = 'car.plate_number = ?';
        $params[] = $plateNumber;
        $types .= 's';
    }

    $whereSql = '(' . implode(' OR ', $whereParts) . ')';

    if (is_logged_in($conn) && auth_scope($conn) !== 'all') {
        $branchCondition = auth_branch_condition($conn, 'ord', $types, $params);
        if ($branchCondition === '1 = 0') {
            return array();
        }

        if ($branchCondition !== '') {
            $whereSql .= ' AND (ord.order_id IS NULL OR ' . $branchCondition . ')';
        }
    }

    $limit = max(1, min(20, $limit));
    $sql = "
        SELECT
            cust.customer_id,
            cust.name AS customer_name,
            cust.phone,
            cust.customer_group,
            car.car_id,
            car.plate_number,
            car.province,
            car.vehicle_type,
            car.brand,
            car.model,
            car.color,
            car.profile_image_path,
            car.profile_image_name,
            COALESCE(MAX(ord.order_date), car.updated_at, cust.updated_at) AS last_seen
        FROM customers cust
        LEFT JOIN cars car ON car.customer_id = cust.customer_id
        LEFT JOIN orders ord ON ord.car_id = car.car_id
        WHERE {$whereSql}
        GROUP BY
            cust.customer_id,
            cust.name,
            cust.phone,
            cust.customer_group,
            car.car_id,
            car.plate_number,
            car.province,
            car.vehicle_type,
            car.brand,
            car.model,
            car.color,
            car.profile_image_path,
            car.profile_image_name,
            car.updated_at,
            cust.updated_at
        ORDER BY
            last_seen DESC,
            car.car_id DESC
        LIMIT {$limit}
    ";

    $stmt = db_prepare($conn, $sql);
    $stmt->bind_param($types, ...$params);
    $stmt->execute();
    $result = $stmt->get_result();
    $matches = array();

    while ($row = $result->fetch_assoc()) {
        $matches[] = array(
            'customerId' => (int) $row['customer_id'],
            'carId' => isset($row['car_id']) ? (int) $row['car_id'] : 0,
            'customerName' => (string) ($row['customer_name'] ?? ''),
            'phone' => (string) ($row['phone'] ?? ''),
            'customerGroup' => customer_group_key((string) ($row['customer_group'] ?? 'member')),
            'plateNumber' => (string) ($row['plate_number'] ?? ''),
            'province' => (string) ($row['province'] ?? ''),
            'vehicleType' => (string) ($row['vehicle_type'] ?? ''),
            'brand' => (string) ($row['brand'] ?? ''),
            'model' => (string) ($row['model'] ?? ''),
            'color' => (string) ($row['color'] ?? ''),
            'profileImageUrl' => (string) ($row['profile_image_path'] ?? ''),
            'profileImageName' => (string) ($row['profile_image_name'] ?? ''),
        );
    }

    $stmt->close();

    return $matches;
}

function customer_lookup_record_from_row(array $row): array
{
    return array(
        'customerId' => (int) ($row['customer_id'] ?? 0),
        'carId' => isset($row['car_id']) ? (int) ($row['car_id'] ?? 0) : 0,
        'customerName' => (string) ($row['customer_name'] ?? ''),
        'phone' => (string) ($row['phone'] ?? ''),
        'customerGroup' => customer_group_key((string) ($row['customer_group'] ?? 'member')),
        'plateNumber' => (string) ($row['plate_number'] ?? ''),
        'province' => (string) ($row['province'] ?? ''),
        'vehicleType' => (string) ($row['vehicle_type'] ?? ''),
        'brand' => (string) ($row['brand'] ?? ''),
        'model' => (string) ($row['model'] ?? ''),
        'color' => (string) ($row['color'] ?? ''),
        'profileImageUrl' => (string) ($row['profile_image_path'] ?? ''),
        'profileImageName' => (string) ($row['profile_image_name'] ?? ''),
    );
}

function customer_lookup_has_branch_access(mysqli $conn, int $customerId): bool
{
    if ($customerId <= 0 || !is_logged_in($conn)) {
        return false;
    }

    if (auth_scope($conn) === 'all') {
        return true;
    }

    $totalStmt = db_prepare($conn, "
        SELECT COUNT(*) AS total_orders
        FROM orders
        WHERE customer_id = ?
    ");
    $totalStmt->bind_param('i', $customerId);
    $totalStmt->execute();
    $totalRow = $totalStmt->get_result()->fetch_assoc() ?: array('total_orders' => 0);
    $totalStmt->close();

    $totalOrders = (int) ($totalRow['total_orders'] ?? 0);
    if ($totalOrders === 0) {
        return true;
    }

    $types = '';
    $params = array();
    $branchCondition = auth_branch_condition($conn, 'o', $types, $params);
    if ($branchCondition === '1 = 0') {
        return false;
    }

    $types .= 'i';
    $params[] = $customerId;
    $stmt = db_prepare($conn, "
        SELECT COUNT(*) AS total_orders
        FROM orders o
        WHERE {$branchCondition}
            AND o.customer_id = ?
    ");
    $stmt->bind_param($types, ...$params);
    $stmt->execute();
    $row = $stmt->get_result()->fetch_assoc() ?: array('total_orders' => 0);
    $stmt->close();

    return (int) ($row['total_orders'] ?? 0) > 0;
}

function find_customer_record_by_ids(mysqli $conn, int $customerId, int $carId): ?array
{
    ensure_customer_group_column($conn);
    ensure_car_profile_columns($conn);

    if ($customerId <= 0 || $carId <= 0) {
        return null;
    }

    if (!customer_lookup_has_branch_access($conn, $customerId)) {
        return null;
    }

    $stmt = db_prepare($conn, "
        SELECT
            cust.customer_id,
            cust.name AS customer_name,
            cust.phone,
            cust.customer_group,
            car.car_id,
            car.plate_number,
            car.province,
            car.vehicle_type,
            car.brand,
            car.model,
            car.color,
            car.profile_image_path,
            car.profile_image_name
        FROM customers cust
        INNER JOIN cars car ON car.customer_id = cust.customer_id
        WHERE cust.customer_id = ?
            AND car.car_id = ?
        LIMIT 1
    ");
    $stmt->bind_param('ii', $customerId, $carId);
    $stmt->execute();
    $row = $stmt->get_result()->fetch_assoc() ?: null;
    $stmt->close();

    return $row ? customer_lookup_record_from_row($row) : null;
}
