<?php

namespace Core\App\Models;

use Core\App\Support\OrderPricing;

/**
 * CustomerModel
 * ─────────────────────────────────────────────────────
 * UNIQUE KEY على phone_normalized → رقم هاتف واحد = عميل واحد
 * العميل كيان دائم، ولا يُحذف تلقائياً عند وصول عداد الطلبات إلى الصفر.
 * يتم احتساب الإحصائيات فقط للطلبات المشحونة فعلاً (tracking) والنشطة.
 */
class CustomerModel
{
    private \PDO $db;

    public function __construct()
    {
        $this->db = \db();
    }

    public function upsertFromOrder(array $order): void
    {
        $phone = trim($order['phone'] ?? '');
        if ($phone === '') return;

        $normalized = $this->normalizePhone($phone);
        if ($normalized === '') return;

        $extra    = $this->decodeExtra($order['extra'] ?? []);
        $shipping = is_array($extra['shipping'] ?? null) ? $extra['shipping'] : [];
        $dest     = is_array($shipping['destination'] ?? null) ? $shipping['destination'] : [];

        $city   = $dest['city_name']  ?? null;
        $wilaya = $dest['state_code'] ?? null;
        $total  = $this->extractOrderTotal($order, $extra);
        $name   = trim($order['full_name'] ?? '');
        $now    = date('Y-m-d H:i:s');

        $this->ensureCustomerExists($order);

        if (!$this->countsForStats($order)) {
            $this->db->prepare("\n                UPDATE customers SET\n                    full_name     = IF(? != '', ?, full_name),\n                    city          = COALESCE(?, city),\n                    wilaya_code   = COALESCE(?, wilaya_code),\n                    first_order_at = COALESCE(first_order_at, ?),\n                    last_order_at = ?\n                WHERE phone_normalized = ?\n            ")->execute([
                $name, $name, $city, $wilaya, $now, $now, $normalized,
            ]);
            return;
        }

        $this->db->prepare("\n            UPDATE customers SET\n                full_name      = IF(? != '', ?, full_name),\n                city           = COALESCE(?, city),\n                wilaya_code    = COALESCE(?, wilaya_code),\n                orders_count   = orders_count + 1,\n                total_spent    = total_spent + ?,\n                first_order_at = COALESCE(first_order_at, ?),\n                last_order_at  = ?,\n                tags           = CASE\n                                    WHEN orders_count + 1 >= 10 THEN 'vip'\n                                    WHEN orders_count + 1 >= 2  THEN 'returning'\n                                    ELSE 'new'\n                                 END\n            WHERE phone_normalized = ?\n        ")->execute([
            $name, $name, $city, $wilaya, $total, $now, $now, $normalized,
        ]);
    }

    public function decrementFromOrder(array $order, string $reason = ''): void
    {
        $phone = trim($order['phone'] ?? '');
        if ($phone === '') return;

        $normalized = $this->normalizePhone($phone);
        if ($normalized === '') return;

        $this->ensureCustomerExists($order);

        if ($this->countsForStats($order)) {
            $extra = $this->decodeExtra($order['extra'] ?? []);
            $total = $this->extractOrderTotal($order, $extra);

            $this->db->prepare("\n                UPDATE customers SET\n                    orders_count = GREATEST(0, orders_count - 1),\n                    total_spent  = GREATEST(0, total_spent - ?),\n                    tags = CASE\n                        WHEN GREATEST(0, orders_count - 1) >= 10 THEN 'vip'\n                        WHEN GREATEST(0, orders_count - 1) >= 2  THEN 'returning'\n                        WHEN GREATEST(0, orders_count - 1) = 1   THEN 'new'\n                        ELSE NULL\n                    END\n                WHERE phone_normalized = ?\n            ")->execute([$total, $normalized]);
        }

        if ($reason !== '') {
            $this->appendNoteToCustomer($normalized, $reason);
        }
    }

    public function countsForStats(array $order): bool
    {
        $extra = $this->decodeExtra($order['extra'] ?? []);
        $tracking = trim((string)($extra['shipping']['tracking'] ?? ''));
        if ($tracking === '') {
            return false;
        }

        $status = strtolower(trim((string)($order['order_status'] ?? '')));
        return !in_array($status, ['rejected', 'returned', 'cancelled'], true);
    }

    public function syncFromOrders(): array
    {
        $rows = $this->db->query("\n            SELECT phone, full_name, extra, total_amount, created_at, order_status\n            FROM orders\n            ORDER BY created_at ASC\n        ")->fetchAll(\PDO::FETCH_ASSOC);

        // إعادة ضبط الملخصات بدون حذف السجلات
        $this->db->exec("\n            UPDATE customers SET\n                orders_count = 0,\n                total_spent  = 0,\n                tags         = NULL\n        ");

        $grouped = [];
        foreach ($rows as $row) {
            $norm = $this->normalizePhone($row['phone'] ?? '');
            if ($norm === '') continue;

            if (!isset($grouped[$norm])) {
                $grouped[$norm] = [
                    'phone'           => $row['phone'],
                    'phone_normalized'=> $norm,
                    'full_name'       => $row['full_name'] ?? '',
                    'city'            => null,
                    'wilaya_code'     => null,
                    'orders_count'    => 0,
                    'total_spent'     => 0.0,
                    'first_order_at'  => $row['created_at'],
                    'last_order_at'   => $row['created_at'],
                ];
            }

            $extra = $this->decodeExtra($row['extra'] ?? []);
            $shipping = is_array($extra['shipping'] ?? null) ? $extra['shipping'] : [];
            $dest     = is_array($shipping['destination'] ?? null) ? $shipping['destination'] : [];

            $city   = $dest['city_name']  ?? null;
            $wilaya = $dest['state_code'] ?? null;
            $total  = $this->extractOrderTotal($row, $extra);

            if ($city)   $grouped[$norm]['city'] = $city;
            if ($wilaya) $grouped[$norm]['wilaya_code'] = $wilaya;
            if (($row['full_name'] ?? '') !== '') $grouped[$norm]['full_name'] = $row['full_name'];
            $grouped[$norm]['last_order_at'] = $row['created_at'];

            if ($this->countsForStats($row)) {
                $grouped[$norm]['orders_count']++;
                $grouped[$norm]['total_spent'] += $total;
            }
        }

        $synced = $skipped = 0;
        foreach ($grouped as $norm => $d) {
            $tag = $d['orders_count'] >= 10 ? 'vip'
                 : ($d['orders_count'] >= 2 ? 'returning'
                 : ($d['orders_count'] >= 1 ? 'new' : null));
            try {
                $this->db->prepare("\n                    INSERT INTO customers\n                        (phone, phone_normalized, full_name, city, wilaya_code,\n                         orders_count, total_spent, first_order_at, last_order_at, tags)\n                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)\n                    ON DUPLICATE KEY UPDATE\n                        phone          = VALUES(phone),\n                        full_name      = IF(VALUES(full_name) != '', VALUES(full_name), full_name),\n                        city           = COALESCE(VALUES(city), city),\n                        wilaya_code    = COALESCE(VALUES(wilaya_code), wilaya_code),\n                        orders_count   = VALUES(orders_count),\n                        total_spent    = VALUES(total_spent),\n                        first_order_at = COALESCE(first_order_at, VALUES(first_order_at)),\n                        last_order_at  = VALUES(last_order_at),\n                        tags           = VALUES(tags)\n                ")->execute([
                    $d['phone'],
                    $norm,
                    $d['full_name'],
                    $d['city'],
                    $d['wilaya_code'],
                    $d['orders_count'],
                    $d['total_spent'],
                    $d['first_order_at'],
                    $d['last_order_at'],
                    $tag,
                ]);
                $synced++;
            } catch (\Throwable $e) {
                $skipped++;
            }
        }

        return ['synced' => $synced, 'skipped' => $skipped, 'total' => count($grouped)];
    }


    public function getById(int $id): ?array
    {
        $stmt = $this->db->prepare("
            SELECT c.*,
                (SELECT COUNT(*) FROM whatsapp_messages wm
                 WHERE wm.customer_id = c.id AND wm.status = 'sent') AS msgs_sent,
                (SELECT COUNT(*) FROM orders o
                 WHERE o.phone = c.phone OR o.phone = c.phone_normalized) AS raw_orders_count
            FROM customers c
            WHERE c.id = :id
            LIMIT 1
        ");
        $stmt->bindValue(':id', $id, \PDO::PARAM_INT);
        $stmt->execute();
        $row = $stmt->fetch(\PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    public function getOrdersByCustomer(int $customerId, int $limit = 20): array
    {
        $customer = $this->getById($customerId);
        if (!$customer) {
            return [];
        }

        $variants = $this->getPhoneVariants((string)($customer['phone'] ?? ''), (string)($customer['phone_normalized'] ?? ''));
        if (empty($variants)) {
            return [];
        }

        $placeholders = implode(',', array_fill(0, count($variants), '?'));
        $sql = "
            SELECT id, full_name, phone, order_status, status, total_amount, total, shipping_provider, tracking_number, extra, created_at
            FROM orders
            WHERE phone IN ($placeholders)
            ORDER BY created_at DESC
            LIMIT " . (int)$limit;

        $stmt = $this->db->prepare($sql);
        $stmt->execute($variants);
        $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC);

        foreach ($rows as &$row) {
            $extra = $this->decodeExtra($row['extra'] ?? []);
            $row['tracking_from_extra'] = trim((string)($extra['shipping']['tracking'] ?? ''));
            $row['shipping_destination_city'] = $extra['shipping']['destination']['city_name'] ?? null;
            $row['computed_total'] = $this->extractOrderTotal($row, $extra);
            unset($row['extra']);
        }
        unset($row);

        return $rows;
    }

    public function getMessagesByCustomer(int $customerId, int $limit = 12): array
    {
        $stmt = $this->db->prepare("
            SELECT id, type, event, campaign, message, provider, status, error, sent_at, created_at
            FROM whatsapp_messages
            WHERE customer_id = :id
            ORDER BY COALESCE(sent_at, created_at) DESC
            LIMIT :limit
        ");
        $stmt->bindValue(':id', $customerId, \PDO::PARAM_INT);
        $stmt->bindValue(':limit', $limit, \PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }

    public function getStats(): array
    {
        $row = $this->db->query("\n            SELECT\n                COUNT(*)                                AS total,\n                SUM(orders_count = 1)                   AS new_customers,\n                SUM(orders_count >= 2)                  AS `returning`,\n                SUM(orders_count >= 10\n                    OR FIND_IN_SET('vip', COALESCE(tags,''))) AS vip,\n                COALESCE(AVG(total_spent),  0)          AS avg_spent,\n                COALESCE(AVG(orders_count), 0)          AS avg_orders\n            FROM customers WHERE is_blocked = 0\n        ")->fetch(\PDO::FETCH_ASSOC);

        return $row ?: [
            'total'=>0,'new_customers'=>0,'returning'=>0,
            'vip'=>0,'avg_spent'=>0,'avg_orders'=>0,
        ];
    }

    public function getList(array $filters = [], int $page = 1, int $perPage = 50): array
    {
        [$where, $params] = $this->buildWhere($filters, 'c.');
        $stmt = $this->db->prepare("\n            SELECT c.*,\n                (SELECT COUNT(*) FROM whatsapp_messages wm\n                 WHERE wm.customer_id = c.id AND wm.status = 'sent') AS msgs_sent\n            FROM customers c\n            WHERE " . implode(' AND ', $where) . "\n            ORDER BY c.last_order_at DESC\n            LIMIT :limit OFFSET :offset\n        ");
        foreach ($params as $k => $v) $stmt->bindValue($k, $v);
        $stmt->bindValue(':limit',  $perPage,             \PDO::PARAM_INT);
        $stmt->bindValue(':offset', ($page-1) * $perPage, \PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }

    public function countList(array $filters = []): int
    {
        [$where, $params] = $this->buildWhere($filters);
        $stmt = $this->db->prepare(
            "SELECT COUNT(*) FROM customers WHERE " . implode(' AND ', $where)
        );
        $stmt->execute($params);
        return (int)$stmt->fetchColumn();
    }

    public function getPhonesForCampaign(array $filters = []): array
    {
        [$where, $params] = $this->buildWhere($filters);
        $where[] = "phone_normalized != ''";
        $stmt = $this->db->prepare(
            "SELECT id, phone_normalized, full_name FROM customers
             WHERE " . implode(' AND ', $where) . " ORDER BY last_order_at DESC"
        );
        $stmt->execute($params);
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }

    public function logMessage(array $data): void
    {
        $this->db->prepare("\n            INSERT INTO whatsapp_messages\n                (type, event, campaign, phone, customer_id, order_id,\n                 `message`, provider, status, error, sent_at)\n            VALUES (?,?,?,?,?,?,?,?,?,?,?)\n        ")->execute([
            $data['type']       ?? 'auto',
            $data['event']      ?? null,
            $data['campaign']   ?? null,
            $data['phone']      ?? '',
            $data['customer_id']?? null,
            $data['order_id']   ?? null,
            $data['message']    ?? '',
            $data['provider']   ?? null,
            $data['status']     ?? 'pending',
            $data['error']      ?? null,
            ($data['status'] ?? '') === 'sent' ? date('Y-m-d H:i:s') : null,
        ]);
    }

    private function buildWhere(array $filters, string $prefix = ''): array
    {
        $where  = ["{$prefix}is_blocked = 0"];
        $params = [];
        if (!empty($filters['search'])) {
            $where[] = "({$prefix}full_name LIKE :search OR {$prefix}phone LIKE :search OR {$prefix}phone_normalized LIKE :search)";
            $params[':search'] = '%' . $filters['search'] . '%';
        }
        if (!empty($filters['tag'])) {
            $where[] = "FIND_IN_SET(:tag, COALESCE({$prefix}tags,'')) > 0";
            $params[':tag'] = $filters['tag'];
        }
        if (!empty($filters['wilaya'])) {
            $where[] = "{$prefix}wilaya_code = :wilaya";
            $params[':wilaya'] = $filters['wilaya'];
        }
        if (!empty($filters['min_orders'])) {
            $where[] = "{$prefix}orders_count >= :min_orders";
            $params[':min_orders'] = (int)$filters['min_orders'];
        }
        return [$where, $params];
    }

    private function ensureCustomerExists(array $order): void
    {
        $phone = trim($order['phone'] ?? '');
        if ($phone === '') return;

        $normalized = $this->normalizePhone($phone);
        if ($normalized === '') return;

        $extra    = $this->decodeExtra($order['extra'] ?? []);
        $shipping = is_array($extra['shipping'] ?? null) ? $extra['shipping'] : [];
        $dest     = is_array($shipping['destination'] ?? null) ? $shipping['destination'] : [];
        $city     = $dest['city_name'] ?? null;
        $wilaya   = $dest['state_code'] ?? null;
        $name     = trim($order['full_name'] ?? '');
        $created  = $order['created_at'] ?? date('Y-m-d H:i:s');

        $this->db->prepare("\n            INSERT INTO customers\n                (phone, phone_normalized, full_name, city, wilaya_code,\n                 orders_count, total_spent, first_order_at, last_order_at, tags)\n            VALUES (?, ?, ?, ?, ?, 0, 0, ?, ?, NULL)\n            ON DUPLICATE KEY UPDATE\n                phone          = VALUES(phone),\n                full_name      = IF(VALUES(full_name) != '', VALUES(full_name), full_name),\n                city           = COALESCE(VALUES(city), city),\n                wilaya_code    = COALESCE(VALUES(wilaya_code), wilaya_code),\n                first_order_at = COALESCE(first_order_at, VALUES(first_order_at)),\n                last_order_at  = GREATEST(COALESCE(last_order_at, VALUES(last_order_at)), VALUES(last_order_at))\n        ")->execute([
            $phone,
            $normalized,
            $name,
            $city,
            $wilaya,
            $created,
            $created,
        ]);
    }

    private function appendNoteToCustomer(string $normalizedPhone, string $reason): void
    {
        $reason = trim($reason);
        if ($reason === '') return;

        $noteLine = '[' . date('Y-m-d H:i:s') . '] ' . $reason;
        $this->db->prepare("\n            UPDATE customers\n            SET notes = CASE\n                    WHEN notes IS NULL OR notes = '' THEN ?\n                    ELSE CONCAT(notes, '\n', ?)\n                END\n            WHERE phone_normalized = ?\n        ")->execute([$noteLine, $noteLine, $normalizedPhone]);
    }

    private function decodeExtra($extra): array
    {
        if (is_string($extra)) {
            return json_decode($extra, true) ?: [];
        }
        return is_array($extra) ? $extra : [];
    }

    private function extractOrderTotal(array $order, array $extra = []): float
    {
        if (!isset($order['extra'])) { $order['extra'] = $extra; }
        return OrderPricing::effectiveTotal($order);
    }


    private function getPhoneVariants(string $phone, string $normalizedPhone = ''): array
    {
        $variants = [];
        foreach ([$phone, $normalizedPhone] as $value) {
            $value = trim($value);
            if ($value === '') continue;
            $variants[] = $value;
            $digits = preg_replace('/\D/', '', $value);
            if ($digits !== '') {
                $variants[] = $digits;
                if (str_starts_with($digits, '213') && strlen($digits) === 12) {
                    $local = substr($digits, 3);
                    if ($local !== '') {
                        $variants[] = $local;
                        if (strlen($local) === 9) {
                            $variants[] = '0' . $local;
                        }
                    }
                }
                if (strlen($digits) === 10 && $digits[0] === '0') {
                    $variants[] = '+213' . substr($digits, 1);
                    $variants[] = '213' . substr($digits, 1);
                    $variants[] = substr($digits, 1);
                }
            }
        }

        $variants = array_values(array_unique(array_filter($variants)));
        return $variants;
    }

    public function normalizePhone(string $phone): string
    {
        $p = preg_replace('/\D/', '', $phone);
        if (strlen($p) === 10 && $p[0] === '0') return '+213' . substr($p, 1);
        if (strlen($p) === 9  && preg_match('/^[5-7]/', $p)) return '+213' . $p;
        if (strlen($p) === 12 && str_starts_with($p, '213')) return '+' . $p;
        if (strlen($p) > 10) return '+' . $p;
        return $phone;
    }
}
