<?php

namespace Core\App\Models;

use PDO;

class SuspiciousOrderModel extends BaseModel
{
    /* ============================================================
       جلب الطلبات المشبوهة
       البيانات مخزنة في عمود extra كـ JSON
    ============================================================ */
    public function getSuspicious(array $filters = [], int $limit = 20, int $offset = 0): array
    {
        $where  = ["JSON_EXTRACT(extra, '$.fraud_score') IS NOT NULL",
                   "CAST(JSON_UNQUOTE(JSON_EXTRACT(extra, '$.fraud_score')) AS UNSIGNED) > 0"];
        $params = [];

        if (($filters['risk'] ?? '') === 'high') {
            $where[] = "CAST(JSON_UNQUOTE(JSON_EXTRACT(extra, '$.fraud_score')) AS UNSIGNED) >= 50";
        } elseif (($filters['risk'] ?? '') === 'medium') {
            $where[] = "CAST(JSON_UNQUOTE(JSON_EXTRACT(extra, '$.fraud_score')) AS UNSIGNED) >= 20";
            $where[] = "CAST(JSON_UNQUOTE(JSON_EXTRACT(extra, '$.fraud_score')) AS UNSIGNED) < 50";
        }

        if (!empty($filters['phone'])) {
            $where[]  = 'phone LIKE ?';
            $params[] = '%' . $filters['phone'] . '%';
        }

        $whereStr = 'WHERE ' . implode(' AND ', $where);

        $stmt = $this->db->prepare("
            SELECT
                o.id,
                o.full_name   AS client_name,
                o.phone,
                o.address,
                o.total_amount AS total,
                o.order_status AS status,
                o.created_at,
                o.extra,
                CAST(COALESCE(
                    JSON_UNQUOTE(JSON_EXTRACT(o.extra, '$.fraud_score')),
                '0') AS UNSIGNED) AS fraud_score,
                COALESCE(
                    JSON_UNQUOTE(JSON_EXTRACT(o.extra, '$.fraud_reason')),
                    ''
                ) AS fraud_reason,
                COALESCE(
                    JSON_UNQUOTE(JSON_EXTRACT(o.extra, '$.shipping.destination.state_code')),
                    ''
                ) AS wilaya_name,
                foe_stats.return_rate
            FROM orders o
            LEFT JOIN (
                SELECT
                    CONVERT(phone USING utf8mb4) COLLATE utf8mb4_general_ci AS phone,
                    ROUND(
                        SUM(CASE WHEN outcome IN ('returned','cancelled') THEN 1 ELSE 0 END)
                        / COUNT(*) * 100, 1
                    ) AS return_rate
                FROM fraud_order_outcomes
                GROUP BY phone
            ) foe_stats ON foe_stats.phone = o.phone COLLATE utf8mb4_general_ci
            $whereStr
            ORDER BY fraud_score DESC, o.created_at DESC
            LIMIT ? OFFSET ?
        ");

        $stmt->execute(array_merge($params, [$limit, $offset]));
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        foreach ($rows as &$row) {
            $row['extra'] = $row['extra'] ? json_decode($row['extra'], true) : [];
        }

        return $rows;
    }

    public function countSuspicious(array $filters = []): int
    {
        $where  = [
            "JSON_EXTRACT(extra, '$.fraud_score') IS NOT NULL",
            "CAST(JSON_UNQUOTE(JSON_EXTRACT(extra, '$.fraud_score')) AS UNSIGNED) > 0",
        ];
        $params = [];

        if (($filters['risk'] ?? '') === 'high') {
            $where[] = "CAST(JSON_UNQUOTE(JSON_EXTRACT(extra, '$.fraud_score')) AS UNSIGNED) >= 50";
        } elseif (($filters['risk'] ?? '') === 'medium') {
            $where[] = "CAST(JSON_UNQUOTE(JSON_EXTRACT(extra, '$.fraud_score')) AS UNSIGNED) >= 20";
            $where[] = "CAST(JSON_UNQUOTE(JSON_EXTRACT(extra, '$.fraud_score')) AS UNSIGNED) < 50";
        }

        if (!empty($filters['phone'])) {
            $where[]  = 'phone LIKE ?';
            $params[] = '%' . $filters['phone'] . '%';
        }

        $whereStr = 'WHERE ' . implode(' AND ', $where);

        $stmt = $this->db->prepare("SELECT COUNT(*) FROM orders $whereStr");
        $stmt->execute($params);
        return (int)$stmt->fetchColumn();
    }

    public function getStats(): array
    {
        $row = $this->fetchOne("
            SELECT
                SUM(CAST(JSON_UNQUOTE(JSON_EXTRACT(extra,'$.fraud_score')) AS UNSIGNED) > 0)  AS total,
                SUM(CAST(JSON_UNQUOTE(JSON_EXTRACT(extra,'$.fraud_score')) AS UNSIGNED) >= 50) AS high_risk,
                SUM(
                    CAST(JSON_UNQUOTE(JSON_EXTRACT(extra,'$.fraud_score')) AS UNSIGNED) >= 20
                    AND
                    CAST(JSON_UNQUOTE(JSON_EXTRACT(extra,'$.fraud_score')) AS UNSIGNED) < 50
                ) AS medium_risk
            FROM orders
            WHERE JSON_EXTRACT(extra, '$.fraud_score') IS NOT NULL
        ");

        try {
            $bannedCount = (int)$this->db->query(
                "SELECT COUNT(*) FROM fraud_banned_phones"
            )->fetchColumn();
        } catch (\Throwable $e) {
            $bannedCount = 0;
        }

        return [
            'total'         => (int)($row['total']      ?? 0),
            'high_risk'     => (int)($row['high_risk']  ?? 0),
            'medium_risk'   => (int)($row['medium_risk'] ?? 0),
            'banned_phones' => $bannedCount,
        ];
    }

    /* تأكيد الطلب — إزالة fraud_score من extra */
    public function approveOrder(int $id): bool
    {
        try {
            $row = $this->fetchOne("SELECT extra FROM orders WHERE id = ? LIMIT 1", [$id]);
            if (!$row) return false;

            $extra = json_decode($row['extra'], true) ?? [];
            unset($extra['fraud_score'], $extra['fraud_reason']);

            return $this->execute(
                "UPDATE orders SET extra = ? WHERE id = ?",
                [json_encode($extra, JSON_UNESCAPED_UNICODE), $id]
            );
        } catch (\Throwable $e) {
            return false;
        }
    }

    /* إلغاء الطلب */
    public function cancelOrder(int $id): bool
    {
        // إعادة stock وتعديل customers إذا كان الطلب confirmed+مشحون
        try {
            $order = $this->getById($id);
            if ($order) {
                $st    = $order['order_status'] ?? '';
                $extra = $order['extra'] ?? [];

                // إعادة المخزون إذا كان confirmed
                if ($st === 'confirmed') {
                    $pm  = new \Core\App\Models\ProductModel();
                    $qty = (int)($extra['quantity'] ?? 1);
                    $vs  = $extra['variants']['size']  ?? null;
                    $vc  = $extra['variants']['color'] ?? null;
                    if ($vs !== null || $vc !== null) {
                        $pm->increaseVariantStock((int)$order['product_id'], $vs, $vc, $qty);
                    } else {
                        $pm->increaseStock((int)$order['product_id'], $qty);
                    }
                }

                // تعديل العميل بدون حذف سجله
                (new \Core\App\Models\CustomerModel())->decrementFromOrder($order, 'تم رفض طلب مشبوه');
            }
        } catch (\Throwable $e) {}

        return $this->execute(
            "UPDATE orders SET order_status = 'rejected' WHERE id = ?",
            [$id]
        );
    }

    public function getById(int $id): ?array
    {
        $row = $this->fetchOne("SELECT * FROM orders WHERE id = ? LIMIT 1", [$id]);
        if (!$row) return null;
        $row['extra'] = $row['extra'] ? json_decode($row['extra'], true) : [];
        return $row;
    }

    public function getBannedPhones(int $limit = 200): array
    {
        try {
            return $this->fetchAll("
                SELECT phone, reason, created_at
                FROM fraud_banned_phones
                ORDER BY created_at DESC
                LIMIT ?
            ", [$limit]);
        } catch (\Throwable $e) { return []; }
    }

    public function banPhone(string $phone, string $reason = ''): bool
    {
        try {
            return $this->execute("
                INSERT INTO fraud_banned_phones (phone, reason, created_at)
                VALUES (?, ?, NOW())
                ON DUPLICATE KEY UPDATE reason = VALUES(reason), created_at = NOW()
            ", [$phone, $reason]);
        } catch (\Throwable $e) { return false; }
    }

    public function unbanPhone(string $phone): bool
    {
        try {
            return $this->execute(
                "DELETE FROM fraud_banned_phones WHERE phone = ?",
                [$phone]
            );
        } catch (\Throwable $e) { return false; }
    }

    /* ============================================================
       جلب محاولات الطلبات المرفوضة من fraud_attempts
    ============================================================ */
    public function getAttempts(string $phone = '', int $limit = 20, int $offset = 0): array
    {
        $where  = [];
        $params = [];

        if ($phone !== '') {
            $where[]  = 'phone LIKE ?';
            $params[] = '%' . $phone . '%';
        }

        $whereStr = $where ? 'WHERE ' . implode(' AND ', $where) : '';

        try {
            $stmt = $this->db->prepare("
                SELECT
                    id, phone, ip, reason, full_name,
                    product_id, fraud_score, created_at,
                    COUNT(*) OVER (PARTITION BY phone) AS attempts_count
                FROM fraud_attempts
                $whereStr
                ORDER BY created_at DESC
                LIMIT ? OFFSET ?
            ");
            $stmt->execute(array_merge($params, [$limit, $offset]));
            return $stmt->fetchAll(\PDO::FETCH_ASSOC);
        } catch (\Throwable $e) {
            return [];
        }
    }

    public function countAttempts(string $phone = ''): int
    {
        try {
            if ($phone !== '') {
                $stmt = $this->db->prepare(
                    "SELECT COUNT(*) FROM fraud_attempts WHERE phone LIKE ?"
                );
                $stmt->execute(['%' . $phone . '%']);
            } else {
                $stmt = $this->db->query("SELECT COUNT(*) FROM fraud_attempts");
            }
            return (int)$stmt->fetchColumn();
        } catch (\Throwable $e) {
            return 0;
        }
    }

    public function getAttemptsStats(): array
    {
        try {
            $row = $this->fetchOne("
                SELECT
                    COUNT(*)                                          AS total,
                    SUM(reason = 'phone_banned')                     AS banned,
                    SUM(reason = 'suspicious')                       AS suspicious,
                    COUNT(DISTINCT phone)                            AS unique_phones,
                    COUNT(DISTINCT ip)                               AS unique_ips
                FROM fraud_attempts
            ");
            return [
                'total'         => (int)($row['total']         ?? 0),
                'banned'        => (int)($row['banned']        ?? 0),
                'suspicious'    => (int)($row['suspicious']    ?? 0),
                'unique_phones' => (int)($row['unique_phones'] ?? 0),
                'unique_ips'    => (int)($row['unique_ips']    ?? 0),
            ];
        } catch (\Throwable $e) {
            return ['total'=>0,'banned'=>0,'suspicious'=>0,'unique_phones'=>0,'unique_ips'=>0];
        }
    }

}