<?php
// Customers Module Helpers
// Built for Nay Store admin structure: app/admin/customers/

if (!function_exists('cust_db')) {
    function cust_db()
    {
        global $conn, $mysqli, $db;

        if (isset($conn) && $conn instanceof mysqli) {
            return $conn;
        }
        if (isset($mysqli) && $mysqli instanceof mysqli) {
            return $mysqli;
        }
        if (isset($db) && $db instanceof mysqli) {
            return $db;
        }
        if (function_exists('db')) {
            $maybe = db();
            if ($maybe instanceof mysqli) {
                return $maybe;
            }
        }

        throw new RuntimeException('Database connection not found. Check includes/config.php');
    }
}

if (!function_exists('cust_h')) {
    function cust_h($value)
    {
        return htmlspecialchars((string)$value, ENT_QUOTES, 'UTF-8');
    }
}


if (!function_exists('cust_normalize_phone')) {
    function cust_normalize_phone($phone)
    {
        return preg_replace('/\D+/', '', trim((string)$phone)) ?: '';
    }
}

if (!function_exists('cust_valid_phone')) {
    function cust_valid_phone($phone)
    {
        return preg_match('/^07\d{9}$/', cust_normalize_phone($phone)) === 1;
    }
}

if (!function_exists('cust_money')) {
    function cust_money($value)
    {
        return number_format((float)$value, 0) . ' د.ع';
    }
}

if (!function_exists('cust_datetime')) {
    function cust_datetime($value)
    {
        if (empty($value) || $value === '0000-00-00 00:00:00') {
            return '-';
        }

        $ts = strtotime($value);
        return $ts ? date('Y-m-d h:i A', $ts) : (string)$value;
    }
}

if (!function_exists('cust_date')) {
    function cust_date($value)
    {
        if (empty($value) || $value === '0000-00-00') {
            return '-';
        }

        $ts = strtotime($value);
        return $ts ? date('Y-m-d', $ts) : (string)$value;
    }
}

if (!function_exists('cust_gender_text')) {
    function cust_gender_text($gender)
    {
        switch ($gender) {
            case 'male':
                return 'ذكر';
            case 'female':
                return 'أنثى';
            default:
                return 'غير محدد';
        }
    }
}

if (!function_exists('cust_status_badge')) {
    function cust_status_badge($status)
    {
        return ((int)$status === 1)
            ? '<span style="display:inline-block;padding:4px 10px;border-radius:999px;background:#dcfce7;color:#166534;font-size:12px;">نشط</span>'
            : '<span style="display:inline-block;padding:4px 10px;border-radius:999px;background:#fee2e2;color:#991b1b;font-size:12px;">غير نشط</span>';
    }
}

if (!function_exists('cust_verified_badge')) {
    function cust_verified_badge($verified)
    {
        return ((int)$verified === 1)
            ? '<span style="display:inline-block;padding:4px 10px;border-radius:999px;background:#dbeafe;color:#1d4ed8;font-size:12px;">OTP مفعل</span>'
            : '<span style="display:inline-block;padding:4px 10px;border-radius:999px;background:#f3f4f6;color:#374151;font-size:12px;">OTP غير مفعل</span>';
    }
}

if (!function_exists('cust_fetch_all')) {
    function cust_fetch_all($stmt)
    {
        $result = $stmt->get_result();
        return $result ? ($result->fetch_all(MYSQLI_ASSOC) ?: array()) : array();
    }
}

if (!function_exists('cust_fetch_one')) {
    function cust_fetch_one($stmt)
    {
        $result = $stmt->get_result();
        if (!$result) {
            return null;
        }

        $row = $result->fetch_assoc();
        return $row ?: null;
    }
}

if (!function_exists('cust_bind_params')) {
    function cust_bind_params($stmt, $types, $params)
    {
        if ($types === '' || empty($params)) {
            return;
        }

        $refs = array();
        foreach ($params as $key => $value) {
            $refs[$key] = &$params[$key];
        }
        array_unshift($refs, $types);
        call_user_func_array(array($stmt, 'bind_param'), $refs);
    }
}

if (!function_exists('cust_redirect')) {
    function cust_redirect($url)
    {
        header('Location: ' . $url);
        exit;
    }
}

if (!function_exists('cust_prepare_result')) {
    function cust_prepare_result($ok, $message, $extra = array())
    {
        return array_merge(array('ok' => (bool)$ok, 'message' => (string)$message), $extra);
    }
}

if (!function_exists('cust_has_table')) {
    function cust_has_table($table)
    {
        $db = cust_db();
        static $cache = array();
        if (array_key_exists($table, $cache)) {
            return $cache[$table];
        }
        $stmt = $db->prepare("SELECT 1 FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? LIMIT 1");
        if (!$stmt) {
            return $cache[$table] = false;
        }
        $stmt->bind_param('s', $table);
        $stmt->execute();
        $exists = (bool)cust_fetch_one($stmt);
        $stmt->close();
        $cache[$table] = $exists;
        return $exists;
    }
}

if (!function_exists('cust_has_column')) {
    function cust_has_column($table, $column)
    {
        $db = cust_db();
        $cacheKey = $table . '.' . $column;
        static $cache = array();
        if (array_key_exists($cacheKey, $cache)) {
            return $cache[$cacheKey];
        }
        if (!cust_has_table($table)) {
            return $cache[$cacheKey] = false;
        }
        $stmt = $db->prepare("SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME = ? LIMIT 1");
        if (!$stmt) {
            return $cache[$cacheKey] = false;
        }
        $stmt->bind_param('ss', $table, $column);
        $stmt->execute();
        $exists = (bool)cust_fetch_one($stmt);
        $stmt->close();
        $cache[$cacheKey] = $exists;
        return $exists;
    }
}

if (!function_exists('cust_get_latest_otp')) {
    function cust_get_latest_otp($customerId, $purpose = '')
    {
        $customerId = (int)$customerId;
        if ($customerId <= 0 || !cust_has_table('customer_otp_codes')) {
            return null;
        }
        $db = cust_db();
        $sql = "SELECT * FROM customer_otp_codes WHERE customer_id = ?";
        $types = 'i';
        $params = array($customerId);
        if ($purpose !== '') {
            $sql .= " AND purpose = ?";
            $types .= 's';
            $params[] = (string)$purpose;
        }
        $sql .= " ORDER BY id DESC LIMIT 1";
        $stmt = $db->prepare($sql);
        if (!$stmt) {
            return null;
        }
        cust_bind_params($stmt, $types, $params);
        $stmt->execute();
        $row = cust_fetch_one($stmt);
        $stmt->close();
        return $row ?: null;
    }
}

if (!function_exists('cust_issue_direct_otp')) {
    function cust_issue_direct_otp($customerId, $purpose = 'activation')
    {
        $customerId = (int)$customerId;
        if ($customerId <= 0) {
            return cust_prepare_result(false, 'الزبون غير صالح');
        }
        if (!cust_has_table('customer_otp_codes')) {
            return cust_prepare_result(false, 'جدول OTP غير موجود. شغل ملف SQL أولاً.');
        }
        $db = cust_db();
        $customer = cust_get_customer($customerId);
        if (!$customer) {
            return cust_prepare_result(false, 'الزبون غير موجود');
        }

        $codeLength = max(4, min(8, (int)stg_value('otp_code_length', '6')));
        $min = (int)pow(10, max(1, $codeLength - 1));
        $max = (int)pow(10, $codeLength) - 1;
        $code = str_pad((string)random_int($min, $max), $codeLength, '0', STR_PAD_LEFT);
        $expiry = max(60, (int)stg_value('otp_expiry_seconds', '300'));
        $expiresAt = date('Y-m-d H:i:s', time() + $expiry);

        $stmt = $db->prepare("UPDATE customer_otp_codes SET status = 'expired', updated_at = NOW() WHERE customer_id = ? AND purpose = ? AND status = 'active'");
        $stmt->bind_param('is', $customerId, $purpose);
        $stmt->execute();
        $stmt->close();

        $channel = stg_value('otp_admin_fallback_enabled', '1') === '1' ? 'manual_fallback' : 'manual';
        $stmt = $db->prepare("INSERT INTO customer_otp_codes (customer_id, code, purpose, status, channel, attempts_count, expires_at, sent_at, created_at, updated_at) VALUES (?, ?, ?, 'active', ?, 0, ?, NOW(), NOW(), NOW())");
        $stmt->bind_param('issss', $customerId, $code, $purpose, $channel, $expiresAt);
        $ok = $stmt->execute();
        $error = $stmt->error;
        $stmt->close();
        if (!$ok) {
            return cust_prepare_result(false, 'فشل إصدار رمز التحقق: ' . $error);
        }
        return cust_prepare_result(true, 'تم إصدار رمز OTP مباشر للزبون.', array('otp' => cust_get_latest_otp($customerId, $purpose)));
    }
}

if (!function_exists('cust_otp_badge')) {
    function cust_otp_badge($otp)
    {
        if (!$otp) {
            return '<span style="display:inline-block;padding:4px 10px;border-radius:999px;background:#f3f4f6;color:#374151;font-size:12px;">ماكو رمز</span>';
        }
        $status = (string)($otp['status'] ?? '');
        $map = array(
            'active' => array('رمز فعّال', '#dbeafe', '#1d4ed8'),
            'used' => array('مستخدم', '#dcfce7', '#166534'),
            'expired' => array('منتهي', '#fee2e2', '#991b1b'),
        );
        $item = isset($map[$status]) ? $map[$status] : array($status ?: 'غير معروف', '#f3f4f6', '#374151');
        return '<span style="display:inline-block;padding:4px 10px;border-radius:999px;background:' . $item[1] . ';color:' . $item[2] . ';font-size:12px;">' . cust_h($item[0]) . '</span>';
    }
}

if (!function_exists('cust_get_provinces')) {
    function cust_get_provinces()
    {
        $db = cust_db();
        $sql = "SELECT id, name, delivery_price, status FROM provinces ORDER BY name ASC";
        $result = $db->query($sql);
        return $result ? ($result->fetch_all(MYSQLI_ASSOC) ?: array()) : array();
    }
}

if (!function_exists('cust_customer_default_data')) {
    function cust_customer_default_data()
    {
        return array(
            'id' => 0,
            'full_name' => '',
            'phone' => '',
            'email' => '',
            'password' => '',
            'gender' => 'not_set',
            'birth_date' => '',
            'province_id' => '',
            'status' => 1,
            'is_verified' => 0,
            'points_balance' => 0,
            'total_orders' => 0,
            'total_spent' => 0,
            'last_login_at' => '',
            'created_at' => '',
            'updated_at' => '',
            'admin_notes' => '',
            'inactive_reason' => '',
        );
    }
}


if (!function_exists('cust_customer_address_default_data')) {
    function cust_customer_address_default_data()
    {
        return array(
            'title' => '',
            'city' => '',
            'area' => '',
            'street' => '',
            'building_no' => '',
            'floor_no' => '',
            'apartment_no' => '',
            'landmark' => '',
        );
    }
}

if (!function_exists('cust_fill_address_input')) {
    function cust_fill_address_input($data, $base = null)
    {
        $row = is_array($base) ? $base : cust_customer_address_default_data();
        foreach (array('title', 'city', 'area', 'street', 'building_no', 'floor_no', 'apartment_no', 'landmark') as $field) {
            $row[$field] = trim((string)($data[$field] ?? $row[$field] ?? ''));
        }
        return $row;
    }
}

if (!function_exists('cust_should_create_address')) {
    function cust_should_create_address($data)
    {
        foreach (array('title', 'city', 'area', 'street', 'building_no', 'floor_no', 'apartment_no', 'landmark') as $field) {
            if (trim((string)($data[$field] ?? '')) !== '') {
                return true;
            }
        }
        return false;
    }
}

if (!function_exists('cust_get_province_by_id')) {
    function cust_get_province_by_id($provinceId)
    {
        $provinceId = (int)$provinceId;
        if ($provinceId < 1) {
            return null;
        }

        $db = cust_db();
        $stmt = $db->prepare("SELECT id, name, delivery_price, status FROM provinces WHERE id = ? LIMIT 1");
        $stmt->bind_param('i', $provinceId);
        $stmt->execute();
        $row = cust_fetch_one($stmt);
        $stmt->close();
        return $row ?: null;
    }
}

if (!function_exists('cust_build_full_address')) {
    function cust_build_full_address($provinceName, $city, $area, $landmark, $street = '', $buildingNo = '', $floorNo = '', $apartmentNo = '')
    {
        $parts = array_filter(array(
            trim((string)$provinceName),
            trim((string)$city),
            trim((string)$area),
        ));

        $extra = array();
        if (trim((string)$street) !== '') {
            $extra[] = 'الشارع: ' . trim((string)$street);
        }
        if (trim((string)$buildingNo) !== '') {
            $extra[] = 'رقم/بناية: ' . trim((string)$buildingNo);
        }
        if (trim((string)$floorNo) !== '') {
            $extra[] = 'الطابق: ' . trim((string)$floorNo);
        }
        if (trim((string)$apartmentNo) !== '') {
            $extra[] = 'الشقة: ' . trim((string)$apartmentNo);
        }
        if (trim((string)$landmark) !== '') {
            $extra[] = 'أقرب نقطة دالة: ' . trim((string)$landmark);
        }

        return implode(' - ', array_merge($parts, $extra));
    }
}

if (!function_exists('cust_validate_address_data')) {
    function cust_validate_address_data($data)
    {
        if (!cust_should_create_address($data)) {
            return cust_prepare_result(true, 'ok', array('province' => null));
        }

        $provinceId = trim((string)($data['province_id'] ?? ''));
        if ($provinceId === '' || !ctype_digit($provinceId) || (int)$provinceId < 1) {
            return cust_prepare_result(false, 'لازم تختار المحافظة حتى تحفظ عنوان للزبون');
        }

        $province = cust_get_province_by_id((int)$provinceId);
        if (!$province) {
            return cust_prepare_result(false, 'المحافظة المختارة غير موجودة');
        }

        $city = trim((string)($data['city'] ?? ''));
        $area = trim((string)($data['area'] ?? ''));
        $landmark = trim((string)($data['landmark'] ?? ''));

        if ($city === '') {
            return cust_prepare_result(false, 'المدينة مطلوبة عند إضافة عنوان للزبون');
        }
        if ($area === '') {
            return cust_prepare_result(false, 'المنطقة مطلوبة عند إضافة عنوان للزبون');
        }
        if ($landmark === '') {
            return cust_prepare_result(false, 'أقرب نقطة دالة مطلوبة عند إضافة عنوان للزبون');
        }

        return cust_prepare_result(true, 'ok', array('province' => $province));
    }
}

if (!function_exists('cust_fill_customer_input')) {
    function cust_fill_customer_input($data, $base = null)
    {
        $row = is_array($base) ? $base : cust_customer_default_data();

        $row['full_name'] = trim((string)($data['full_name'] ?? $row['full_name']));
        $row['phone'] = trim((string)($data['phone'] ?? $row['phone']));
        $row['email'] = trim((string)($data['email'] ?? $row['email']));
        $row['admin_notes'] = trim((string)($data['admin_notes'] ?? $row['admin_notes'] ?? ''));
        $row['inactive_reason'] = trim((string)($data['inactive_reason'] ?? $row['inactive_reason'] ?? ''));
        $row['password'] = (string)($data['password'] ?? '');
        $row['gender'] = (string)($data['gender'] ?? $row['gender']);
        $row['birth_date'] = trim((string)($data['birth_date'] ?? $row['birth_date']));
        $row['province_id'] = trim((string)($data['province_id'] ?? (string)$row['province_id']));
        $row['status'] = (($data['status'] ?? $row['status']) == 1) ? 1 : 0;
        $row['is_verified'] = (($data['is_verified'] ?? $row['is_verified']) == 1) ? 1 : 0;

        return $row;
    }
}

if (!function_exists('cust_customer_exists')) {
    function cust_customer_exists($field, $value, $excludeId = 0)
    {
        $db = cust_db();
        $allowed = array('phone', 'email');
        if (!in_array($field, $allowed, true) || $value === null || $value === '') {
            return false;
        }

        $sql = "SELECT id FROM customers WHERE {$field} = ?" . ($excludeId > 0 ? ' AND id <> ?' : '') . ' LIMIT 1';
        $stmt = $db->prepare($sql);
        if ($excludeId > 0) {
            $stmt->bind_param('si', $value, $excludeId);
        } else {
            $stmt->bind_param('s', $value);
        }
        $stmt->execute();
        $row = cust_fetch_one($stmt);
        $stmt->close();

        return !empty($row);
    }
}

if (!function_exists('cust_validate_customer_data')) {
    function cust_validate_customer_data($data, $customerId = 0, $isCreate = false)
    {
        $fullName = trim((string)($data['full_name'] ?? ''));
        $phone = cust_normalize_phone((string)($data['phone'] ?? ''));
        $email = trim((string)($data['email'] ?? ''));
        $gender = (string)($data['gender'] ?? 'not_set');
        $birthDate = trim((string)($data['birth_date'] ?? ''));
        $provinceId = trim((string)($data['province_id'] ?? ''));

        if ($fullName === '') {
            return cust_prepare_result(false, 'الاسم الكامل مطلوب');
        }

        if (!cust_valid_phone($phone)) {
            return cust_prepare_result(false, 'رقم الهاتف لازم يكون 11 رقم ويبدأ بـ 07');
        }

        $allowedGenders = array('male', 'female', 'not_set');
        if (!in_array($gender, $allowedGenders, true)) {
            return cust_prepare_result(false, 'قيمة الجنس غير صحيحة');
        }

        if ($birthDate !== '' && !preg_match('/^\d{4}-\d{2}-\d{2}$/', $birthDate)) {
            return cust_prepare_result(false, 'صيغة تاريخ الميلاد غير صحيحة');
        }

        if ($provinceId !== '' && (!ctype_digit($provinceId) || (int)$provinceId < 1)) {
            return cust_prepare_result(false, 'المحافظة غير صحيحة');
        }

        if (cust_customer_exists('phone', $phone, $customerId)) {
            return cust_prepare_result(false, 'رقم الهاتف مستخدم مسبقاً');
        }

        if ($email !== '' && cust_customer_exists('email', $email, $customerId)) {
            return cust_prepare_result(false, 'معرف الانستغرام مستخدم مسبقاً');
        }

        if ($isCreate) {
            $password = (string)($data['password'] ?? '');
            if ($password !== '' && strlen($password) < 6) {
                return cust_prepare_result(false, 'إذا تريد تضيف كلمة مرور، لازم تكون 6 أحرف أو أكثر');
            }
        } else {
            $password = (string)($data['password'] ?? '');
            if ($password !== '' && strlen($password) < 6) {
                return cust_prepare_result(false, 'كلمة المرور الجديدة لازم تكون 6 أحرف أو أكثر');
            }
        }

        return cust_prepare_result(true, 'ok');
    }
}

if (!function_exists('cust_get_customer_stats')) {
    function cust_get_customer_stats()
    {
        $db = cust_db();

        $stats = array(
            'total_customers' => 0,
            'active_customers' => 0,
            'inactive_customers' => 0,
            'verified_customers' => 0,
            'unverified_customers' => 0,
            'customers_with_orders' => 0,
            'customers_with_addresses' => 0,
            'total_points' => 0,
        );

        $sql = "SELECT
                    COUNT(*) AS total_customers,
                    SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END) AS active_customers,
                    SUM(CASE WHEN status = 0 THEN 1 ELSE 0 END) AS inactive_customers,
                    SUM(CASE WHEN is_verified = 1 THEN 1 ELSE 0 END) AS verified_customers,
                    SUM(CASE WHEN is_verified = 0 THEN 1 ELSE 0 END) AS unverified_customers,
                    SUM(CASE WHEN total_orders > 0 THEN 1 ELSE 0 END) AS customers_with_orders,
                    SUM(points_balance) AS total_points
                FROM customers";

        if ($result = $db->query($sql)) {
            $row = $result->fetch_assoc();
            if ($row) {
                foreach ($row as $key => $value) {
                    $stats[$key] = (int)($value ?: 0);
                }
            }
        }

        $sqlAddresses = "SELECT COUNT(DISTINCT customer_id) AS customers_with_addresses FROM customer_addresses WHERE status = 1";
        if ($result = $db->query($sqlAddresses)) {
            $row = $result->fetch_assoc();
            if ($row) {
                $stats['customers_with_addresses'] = (int)$row['customers_with_addresses'];
            }
        }

        return $stats;
    }
}

if (!function_exists('cust_build_customer_filters')) {
    function cust_build_customer_filters($input)
    {
        $where = array();
        $types = '';
        $params = array();

        $q = trim((string)($input['q'] ?? ''));
        $status = (string)($input['status'] ?? '');
        $verified = (string)($input['verified'] ?? '');
        $provinceId = (string)($input['province_id'] ?? '');

        if ($q !== '') {
            $where[] = "(c.full_name LIKE CONCAT('%', ?, '%') OR c.phone LIKE CONCAT('%', ?, '%') OR c.email LIKE CONCAT('%', ?, '%'))";
            $types .= 'sss';
            $params[] = $q;
            $params[] = $q;
            $params[] = $q;
        }

        if ($status === '1' || $status === '0') {
            $where[] = 'c.status = ?';
            $types .= 'i';
            $params[] = (int)$status;
        }

        if ($verified === '1' || $verified === '0') {
            $where[] = 'c.is_verified = ?';
            $types .= 'i';
            $params[] = (int)$verified;
        }

        if ($provinceId !== '' && ctype_digit($provinceId)) {
            $where[] = 'c.province_id = ?';
            $types .= 'i';
            $params[] = (int)$provinceId;
        }

        return array(
            'where_sql' => $where ? (' WHERE ' . implode(' AND ', $where)) : '',
            'types' => $types,
            'params' => $params,
            'q' => $q,
            'status' => $status,
            'verified' => $verified,
            'province_id' => $provinceId,
        );
    }
}

if (!function_exists('cust_get_customers')) {
    function cust_get_customers($input, $page = 1, $perPage = 20)
    {
        $db = cust_db();
        $filters = cust_build_customer_filters($input);
        $page = max(1, (int)$page);
        $perPage = max(1, (int)$perPage);
        $offset = max(0, ($page - 1) * $perPage);

        $countSql = "SELECT COUNT(*) AS total FROM customers c LEFT JOIN provinces p ON p.id = c.province_id {$filters['where_sql']}";
        $stmt = $db->prepare($countSql);
        cust_bind_params($stmt, $filters['types'], $filters['params']);
        $stmt->execute();
        $totalRow = cust_fetch_one($stmt);
        $stmt->close();

        $total = (int)($totalRow['total'] ?? 0);
        $pages = max(1, (int)ceil($total / $perPage));
        if ($page > $pages) {
            $page = $pages;
            $offset = max(0, ($page - 1) * $perPage);
        }

        $sql = "SELECT
                    c.*,
                    p.name AS province_name,
                    (
                        SELECT COUNT(*) FROM customer_addresses ca WHERE ca.customer_id = c.id AND ca.status = 1
                    ) AS addresses_count
                FROM customers c
                LEFT JOIN provinces p ON p.id = c.province_id
                {$filters['where_sql']}
                ORDER BY c.id DESC
                LIMIT ? OFFSET ?";

        $stmt = $db->prepare($sql);
        $types = $filters['types'] . 'ii';
        $params = array_merge($filters['params'], array($perPage, $offset));
        cust_bind_params($stmt, $types, $params);
        $stmt->execute();
        $rows = cust_fetch_all($stmt);
        $stmt->close();

        return array(
            'rows' => $rows,
            'total' => $total,
            'page' => $page,
            'pages' => $pages,
            'per_page' => $perPage,
            'filters' => $filters,
        );
    }
}

if (!function_exists('cust_get_customer')) {
    function cust_get_customer($id)
    {
        $db = cust_db();
        $sql = "SELECT c.*, p.name AS province_name, p.delivery_price AS province_delivery_price
                FROM customers c
                LEFT JOIN provinces p ON p.id = c.province_id
                WHERE c.id = ?
                LIMIT 1";
        $stmt = $db->prepare($sql);
        $stmt->bind_param('i', $id);
        $stmt->execute();
        $row = cust_fetch_one($stmt);
        $stmt->close();
        return $row;
    }
}

if (!function_exists('cust_get_customer_addresses')) {
    function cust_get_customer_addresses($customerId)
    {
        $db = cust_db();
        $sql = "SELECT *
                FROM customer_addresses
                WHERE customer_id = ?
                ORDER BY is_default DESC, id DESC";
        $stmt = $db->prepare($sql);
        $stmt->bind_param('i', $customerId);
        $stmt->execute();
        $rows = cust_fetch_all($stmt);
        $stmt->close();
        return $rows;
    }
}

if (!function_exists('cust_get_customer_orders')) {
    function cust_get_customer_orders($customerId, $limit = 10)
    {
        $db = cust_db();
        $sql = "SELECT id, order_number, total_amount, payment_status, order_status, order_type, placed_at
                FROM orders
                WHERE customer_id = ?
                ORDER BY id DESC
                LIMIT ?";
        $stmt = $db->prepare($sql);
        $stmt->bind_param('ii', $customerId, $limit);
        $stmt->execute();
        $rows = cust_fetch_all($stmt);
        $stmt->close();
        return $rows;
    }
}

if (!function_exists('cust_create_customer')) {
    function cust_create_customer($data)
    {
        $db = cust_db();
        $validation = cust_validate_customer_data($data, 0, true);
        if (!$validation['ok']) {
            return $validation;
        }

        $addressValidation = cust_validate_address_data($data);
        if (!$addressValidation['ok']) {
            return $addressValidation;
        }

        $fullName = trim((string)$data['full_name']);
        $phone = cust_normalize_phone((string)$data['phone']);
        $email = trim((string)($data['email'] ?? ''));
        $adminNotes = trim((string)($data['admin_notes'] ?? ''));
        $inactiveReason = trim((string)($data['inactive_reason'] ?? ''));
        $password = (string)($data['password'] ?? '');
        $passwordHash = $password !== '' ? password_hash($password, PASSWORD_DEFAULT) : '';
        $gender = (string)($data['gender'] ?? 'not_set');
        $birthDate = trim((string)($data['birth_date'] ?? ''));
        $provinceId = (ctype_digit((string)($data['province_id'] ?? '')) && (int)$data['province_id'] > 0) ? (int)$data['province_id'] : 0;
        $status = (($data['status'] ?? 1) == 1) ? 1 : 0;
        $verified = (($data['is_verified'] ?? 0) == 1) ? 1 : 0;

        $hasAdminNotes = cust_has_column('customers', 'admin_notes');
        $hasInactiveReason = cust_has_column('customers', 'inactive_reason');

        $db->begin_transaction();

        try {
            if ($hasAdminNotes && $hasInactiveReason) {
                $sql = "INSERT INTO customers (
                            full_name, phone, email, password_hash, gender, birth_date,
                            points_balance, total_orders, total_spent,
                            status, is_verified, province_id, admin_notes, inactive_reason, created_at, updated_at
                        ) VALUES (
                            ?, ?, NULLIF(?, ''), NULLIF(?, ''), ?, NULLIF(?, ''),
                            0, 0, 0,
                            ?, ?, NULLIF(?, 0), NULLIF(?, ''), NULLIF(?, ''), NOW(), NOW()
                        )";
                $stmt = $db->prepare($sql);
                $stmt->bind_param('ssssssiiiss', $fullName, $phone, $email, $passwordHash, $gender, $birthDate, $status, $verified, $provinceId, $adminNotes, $inactiveReason);
            } elseif ($hasAdminNotes) {
                $sql = "INSERT INTO customers (
                            full_name, phone, email, password_hash, gender, birth_date,
                            points_balance, total_orders, total_spent,
                            status, is_verified, province_id, admin_notes, created_at, updated_at
                        ) VALUES (
                            ?, ?, NULLIF(?, ''), NULLIF(?, ''), ?, NULLIF(?, ''),
                            0, 0, 0,
                            ?, ?, NULLIF(?, 0), NULLIF(?, ''), NOW(), NOW()
                        )";
                $stmt = $db->prepare($sql);
                $stmt->bind_param('ssssssiiis', $fullName, $phone, $email, $passwordHash, $gender, $birthDate, $status, $verified, $provinceId, $adminNotes);
            } elseif ($hasInactiveReason) {
                $sql = "INSERT INTO customers (
                            full_name, phone, email, password_hash, gender, birth_date,
                            points_balance, total_orders, total_spent,
                            status, is_verified, province_id, inactive_reason, created_at, updated_at
                        ) VALUES (
                            ?, ?, NULLIF(?, ''), NULLIF(?, ''), ?, NULLIF(?, ''),
                            0, 0, 0,
                            ?, ?, NULLIF(?, 0), NULLIF(?, ''), NOW(), NOW()
                        )";
                $stmt = $db->prepare($sql);
                $stmt->bind_param('ssssssiiis', $fullName, $phone, $email, $passwordHash, $gender, $birthDate, $status, $verified, $provinceId, $inactiveReason);
            } else {
                $sql = "INSERT INTO customers (
                            full_name, phone, email, password_hash, gender, birth_date,
                            points_balance, total_orders, total_spent,
                            status, is_verified, province_id, created_at, updated_at
                        ) VALUES (
                            ?, ?, NULLIF(?, ''), NULLIF(?, ''), ?, NULLIF(?, ''),
                            0, 0, 0,
                            ?, ?, NULLIF(?, 0), NOW(), NOW()
                        )";
                $stmt = $db->prepare($sql);
                $stmt->bind_param('ssssssiii', $fullName, $phone, $email, $passwordHash, $gender, $birthDate, $status, $verified, $provinceId);
            }

            $ok = $stmt->execute();
            $error = $stmt->error;
            $newId = (int)$stmt->insert_id;
            $stmt->close();

            if (!$ok || $newId < 1) {
                throw new RuntimeException('فشل إضافة الزبون: ' . $error);
            }

            if (cust_should_create_address($data)) {
                $province = $addressValidation['province'];
                $title = trim((string)($data['title'] ?? ''));
                if ($title === '') {
                    $title = 'المنزل';
                }
                $city = trim((string)($data['city'] ?? ''));
                $area = trim((string)($data['area'] ?? ''));
                $street = trim((string)($data['street'] ?? ''));
                $buildingNo = trim((string)($data['building_no'] ?? ''));
                $floorNo = trim((string)($data['floor_no'] ?? ''));
                $apartmentNo = trim((string)($data['apartment_no'] ?? ''));
                $landmark = trim((string)($data['landmark'] ?? ''));
                $fullAddress = cust_build_full_address($province['name'] ?? '', $city, $area, $landmark, $street, $buildingNo, $floorNo, $apartmentNo);
                $deliveryFee = (float)($province['delivery_price'] ?? 0);

                $addressSql = "INSERT INTO customer_addresses (
                                customer_id, title, province, city, area, street, building_no, floor_no, apartment_no,
                                landmark, full_address, delivery_fee, is_default, status, created_at, updated_at
                              ) VALUES (
                                ?, ?, ?, ?, ?, NULLIF(?, ''), NULLIF(?, ''), NULLIF(?, ''), NULLIF(?, ''),
                                ?, ?, ?, 1, 1, NOW(), NOW()
                              )";
                $addressStmt = $db->prepare($addressSql);
                $addressStmt->bind_param('issssssssssd', $newId, $title, $province['name'], $city, $area, $street, $buildingNo, $floorNo, $apartmentNo, $landmark, $fullAddress, $deliveryFee);
                $addressOk = $addressStmt->execute();
                $addressError = $addressStmt->error;
                $addressStmt->close();

                if (!$addressOk) {
                    throw new RuntimeException('فشل حفظ عنوان الزبون: ' . $addressError);
                }
            }

            $db->commit();
            return cust_prepare_result(true, 'تمت إضافة الزبون بنجاح', array('id' => $newId));
        } catch (Throwable $e) {
            $db->rollback();
            return cust_prepare_result(false, $e->getMessage());
        }
    }
}

if (!function_exists('cust_update_customer')) {

    function cust_update_customer($id, $data)
    {
        $db = cust_db();
        $validation = cust_validate_customer_data($data, (int)$id, false);
        if (!$validation['ok']) {
            return $validation;
        }

        $fullName = trim((string)$data['full_name']);
        $phone = cust_normalize_phone((string)$data['phone']);
        $email = trim((string)($data['email'] ?? ''));
        $adminNotes = trim((string)($data['admin_notes'] ?? ''));
        $inactiveReason = trim((string)($data['inactive_reason'] ?? ''));
        $password = (string)($data['password'] ?? '');
        $passwordHash = $password !== '' ? password_hash($password, PASSWORD_DEFAULT) : '';
        $gender = (string)($data['gender'] ?? 'not_set');
        $birthDate = trim((string)($data['birth_date'] ?? ''));
        $provinceId = (ctype_digit((string)($data['province_id'] ?? '')) && (int)$data['province_id'] > 0) ? (int)$data['province_id'] : 0;
        $status = (($data['status'] ?? 0) == 1) ? 1 : 0;
        $verified = (($data['is_verified'] ?? 0) == 1) ? 1 : 0;

        $hasAdminNotes = cust_has_column('customers', 'admin_notes');
        $hasInactiveReason = cust_has_column('customers', 'inactive_reason');

        if ($password !== '') {
            if ($hasAdminNotes && $hasInactiveReason) {
                $sql = "UPDATE customers
                        SET full_name = ?, phone = ?, email = NULLIF(?, ''), password_hash = ?, gender = ?, birth_date = NULLIF(?, ''),
                            province_id = NULLIF(?, 0), admin_notes = NULLIF(?, ''), inactive_reason = NULLIF(?, ''), status = ?, is_verified = ?, updated_at = NOW()
                        WHERE id = ?
                        LIMIT 1";
                $stmt = $db->prepare($sql);
                $stmt->bind_param('ssssssissiii', $fullName, $phone, $email, $passwordHash, $gender, $birthDate, $provinceId, $adminNotes, $inactiveReason, $status, $verified, $id);
            } elseif ($hasAdminNotes) {
                $sql = "UPDATE customers
                        SET full_name = ?, phone = ?, email = NULLIF(?, ''), password_hash = ?, gender = ?, birth_date = NULLIF(?, ''),
                            province_id = NULLIF(?, 0), admin_notes = NULLIF(?, ''), status = ?, is_verified = ?, updated_at = NOW()
                        WHERE id = ?
                        LIMIT 1";
                $stmt = $db->prepare($sql);
                $stmt->bind_param('ssssssisiii', $fullName, $phone, $email, $passwordHash, $gender, $birthDate, $provinceId, $adminNotes, $status, $verified, $id);
            } elseif ($hasInactiveReason) {
                $sql = "UPDATE customers
                        SET full_name = ?, phone = ?, email = NULLIF(?, ''), password_hash = ?, gender = ?, birth_date = NULLIF(?, ''),
                            province_id = NULLIF(?, 0), inactive_reason = NULLIF(?, ''), status = ?, is_verified = ?, updated_at = NOW()
                        WHERE id = ?
                        LIMIT 1";
                $stmt = $db->prepare($sql);
                $stmt->bind_param('ssssssisiii', $fullName, $phone, $email, $passwordHash, $gender, $birthDate, $provinceId, $inactiveReason, $status, $verified, $id);
            } else {
                $sql = "UPDATE customers
                        SET full_name = ?, phone = ?, email = NULLIF(?, ''), password_hash = ?, gender = ?, birth_date = NULLIF(?, ''),
                            province_id = NULLIF(?, 0), status = ?, is_verified = ?, updated_at = NOW()
                        WHERE id = ?
                        LIMIT 1";
                $stmt = $db->prepare($sql);
                $stmt->bind_param('ssssssiiii', $fullName, $phone, $email, $passwordHash, $gender, $birthDate, $provinceId, $status, $verified, $id);
            }
        } else {
            if ($hasAdminNotes && $hasInactiveReason) {
                $sql = "UPDATE customers
                        SET full_name = ?, phone = ?, email = NULLIF(?, ''), gender = ?, birth_date = NULLIF(?, ''),
                            province_id = NULLIF(?, 0), admin_notes = NULLIF(?, ''), inactive_reason = NULLIF(?, ''), status = ?, is_verified = ?, updated_at = NOW()
                        WHERE id = ?
                        LIMIT 1";
                $stmt = $db->prepare($sql);
                $stmt->bind_param('sssssissiii', $fullName, $phone, $email, $gender, $birthDate, $provinceId, $adminNotes, $inactiveReason, $status, $verified, $id);
            } elseif ($hasAdminNotes) {
                $sql = "UPDATE customers
                        SET full_name = ?, phone = ?, email = NULLIF(?, ''), gender = ?, birth_date = NULLIF(?, ''),
                            province_id = NULLIF(?, 0), admin_notes = NULLIF(?, ''), status = ?, is_verified = ?, updated_at = NOW()
                        WHERE id = ?
                        LIMIT 1";
                $stmt = $db->prepare($sql);
                $stmt->bind_param('sssssisiii', $fullName, $phone, $email, $gender, $birthDate, $provinceId, $adminNotes, $status, $verified, $id);
            } elseif ($hasInactiveReason) {
                $sql = "UPDATE customers
                        SET full_name = ?, phone = ?, email = NULLIF(?, ''), gender = ?, birth_date = NULLIF(?, ''),
                            province_id = NULLIF(?, 0), inactive_reason = NULLIF(?, ''), status = ?, is_verified = ?, updated_at = NOW()
                        WHERE id = ?
                        LIMIT 1";
                $stmt = $db->prepare($sql);
                $stmt->bind_param('sssssisiii', $fullName, $phone, $email, $gender, $birthDate, $provinceId, $inactiveReason, $status, $verified, $id);
            } else {
                $sql = "UPDATE customers
                        SET full_name = ?, phone = ?, email = NULLIF(?, ''), gender = ?, birth_date = NULLIF(?, ''),
                            province_id = NULLIF(?, 0), status = ?, is_verified = ?, updated_at = NOW()
                        WHERE id = ?
                        LIMIT 1";
                $stmt = $db->prepare($sql);
                $stmt->bind_param('sssssiiii', $fullName, $phone, $email, $gender, $birthDate, $provinceId, $status, $verified, $id);
            }
        }

        $ok = $stmt->execute();
        $error = $stmt->error;
        $stmt->close();

        if (!$ok) {
            return cust_prepare_result(false, 'فشل تحديث الزبون: ' . $error);
        }

        return cust_prepare_result(true, 'تم تحديث بيانات الزبون بنجاح');
    }
}

if (!function_exists('cust_set_customer_status')) {
    function cust_set_customer_status($id, $status, $inactiveReason = null)
    {
        $customer = cust_get_customer((int)$id);
        if (!$customer) {
            return cust_prepare_result(false, 'الزبون غير موجود');
        }

        $db = cust_db();
        $status = ((int)$status === 1) ? 1 : 0;
        $reasonColumn = cust_inactive_reason_column();
        $reason = trim((string)($inactiveReason ?? ''));

        if ($reasonColumn !== '') {
            if ($status === 1) {
                $stmt = $db->prepare('UPDATE customers SET status = 1, inactive_reason = NULL, updated_at = NOW() WHERE id = ? LIMIT 1');
                $stmt->bind_param('i', $id);
            } else {
                $effectiveReason = $reason !== '' ? $reason : trim((string)($customer['inactive_reason'] ?? ''));
                $stmt = $db->prepare('UPDATE customers SET status = 0, inactive_reason = NULLIF(?, ""), updated_at = NOW() WHERE id = ? LIMIT 1');
                $stmt->bind_param('si', $effectiveReason, $id);
            }
        } else {
            $stmt = $db->prepare('UPDATE customers SET status = ?, updated_at = NOW() WHERE id = ? LIMIT 1');
            $stmt->bind_param('ii', $status, $id);
        }
        $ok = $stmt->execute();
        $error = $stmt->error;
        $stmt->close();

        if (!$ok) {
            return cust_prepare_result(false, 'فشل تحديث حالة الزبون: ' . $error);
        }

        return cust_prepare_result(true, $status === 1 ? 'تم تفعيل الزبون بنجاح' : 'تم تعطيل الزبون بنجاح');
    }
}

if (!function_exists('cust_toggle_customer_status')) {
    function cust_toggle_customer_status($id)
    {
        $customer = cust_get_customer((int)$id);
        if (!$customer) {
            return cust_prepare_result(false, 'الزبون غير موجود');
        }
        $newStatus = ((int)$customer['status'] === 1) ? 0 : 1;
        return cust_set_customer_status($id, $newStatus);
    }
}

if (!function_exists('cust_set_customer_verification')) {
    function cust_set_customer_verification($id, $verified)
    {
        $customer = cust_get_customer((int)$id);
        if (!$customer) {
            return cust_prepare_result(false, 'الزبون غير موجود');
        }

        $db = cust_db();
        $verified = ((int)$verified === 1) ? 1 : 0;
        if ($verified === 1) {
            $stmt = $db->prepare('UPDATE customers SET is_verified = 1, updated_at = NOW() WHERE id = ? LIMIT 1');
            $stmt->bind_param('i', $id);
        } else {
            $stmt = $db->prepare('UPDATE customers SET is_verified = 0, updated_at = NOW() WHERE id = ? LIMIT 1');
            $stmt->bind_param('i', $id);
        }
        $ok = $stmt->execute();
        $error = $stmt->error;
        $stmt->close();

        if (!$ok) {
            return cust_prepare_result(false, 'فشل تحديث تفعيل OTP: ' . $error);
        }

        return cust_prepare_result(true, $verified === 1 ? 'تم تفعيل OTP للزبون بنجاح' : 'تم إلغاء تفعيل OTP للزبون بنجاح');
    }
}

if (!function_exists('cust_order_status_badge')) {
    function cust_order_status_badge($status)
    {
        $map = array(
            'pending' => array('قيد الانتظار', '#fff7ed', '#9a3412'),
            'confirmed' => array('مؤكد', '#eff6ff', '#1d4ed8'),
            'processing' => array('قيد التجهيز', '#f5f3ff', '#6d28d9'),
            'ready' => array('جاهز', '#ecfeff', '#155e75'),
            'out_for_delivery' => array('خرج للتوصيل', '#fef3c7', '#92400e'),
            'delivered' => array('تم التسليم', '#dcfce7', '#166534'),
            'cancelled' => array('ملغي', '#fee2e2', '#991b1b'),
            'returned' => array('راجع', '#f3f4f6', '#374151'),
        );
        $item = isset($map[$status]) ? $map[$status] : array($status, '#f3f4f6', '#111827');
        return '<span style="display:inline-block;padding:4px 10px;border-radius:999px;background:' . $item[1] . ';color:' . $item[2] . ';font-size:12px;">' . cust_h($item[0]) . '</span>';
    }
}

if (!function_exists('cust_payment_status_badge')) {
    function cust_payment_status_badge($status)
    {
        $map = array(
            'unpaid' => array('غير مدفوع', '#f3f4f6', '#374151'),
            'pending_confirmation' => array('بانتظار التأكيد', '#fef3c7', '#92400e'),
            'paid' => array('مدفوع', '#dcfce7', '#166534'),
            'failed' => array('فشل', '#fee2e2', '#991b1b'),
            'refunded' => array('مسترجع', '#dbeafe', '#1d4ed8'),
        );
        $item = isset($map[$status]) ? $map[$status] : array($status, '#f3f4f6', '#111827');
        return '<span style="display:inline-block;padding:4px 10px;border-radius:999px;background:' . $item[1] . ';color:' . $item[2] . ';font-size:12px;">' . cust_h($item[0]) . '</span>';
    }
}
if (!function_exists('cust_inactive_reason_column')) {
    function cust_inactive_reason_column()
    {
        return cust_has_column('customers', 'inactive_reason') ? 'inactive_reason' : '';
    }
}


