<?php

namespace Core\App\Models;

class CallAttemptModel
{
    private \PDO $db;

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

    /* =====================================================
       تسجيل محاولة اتصال
    ===================================================== */
    public function log(array $data): int
    {
        $this->db->prepare("
            INSERT INTO order_call_attempts
                (order_id, admin_id, admin_name, admin_role, result, note, called_at)
            VALUES
                (:order_id, :admin_id, :admin_name, :admin_role, :result, :note, NOW())
        ")->execute([
            ':order_id'   => (int)$data['order_id'],
            ':admin_id'   => (int)$data['admin_id'],
            ':admin_name' => $data['admin_name'] ?? '',
            ':admin_role' => $data['admin_role'] ?? 'confirmer',
            ':result'     => $data['result']     ?? 'no_answer',
            ':note'       => $data['note']       ?? null,
        ]);
        return (int)$this->db->lastInsertId();
    }

    /* =====================================================
       جلب كل محاولات طلب معين
    ===================================================== */
    public function getByOrder(int $orderId): array
    {
        $stmt = $this->db->prepare("
            SELECT * FROM order_call_attempts
            WHERE order_id = ?
            ORDER BY called_at ASC
        ");
        $stmt->execute([$orderId]);
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }

    /* =====================================================
       عدد المحاولات لطلب معين
    ===================================================== */
    public function countByOrder(int $orderId): int
    {
        $stmt = $this->db->prepare("
            SELECT COUNT(*) FROM order_call_attempts WHERE order_id = ?
        ");
        $stmt->execute([$orderId]);
        return (int)$stmt->fetchColumn();
    }

    /* =====================================================
       إحصائيات موظف (آخر 30 يوم)
    ===================================================== */
    public function getAdminStats(int $adminId, int $days = 30): array
    {
        $stmt = $this->db->prepare("
            SELECT
                COUNT(*)                                           AS total_calls,
                SUM(result = 'confirmed')                         AS confirmed,
                SUM(result = 'rejected')                          AS rejected,
                SUM(result = 'no_answer')                         AS no_answer,
                SUM(result = 'busy')                              AS busy,
                SUM(result = 'callback_later')                    AS callback_later,
                SUM(result = 'not_interested')                    AS not_interested,
                COUNT(DISTINCT order_id)                          AS orders_handled,
                ROUND(AVG(result = 'confirmed') * 100, 1)         AS confirm_rate
            FROM order_call_attempts
            WHERE admin_id = ?
              AND called_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
        ");
        $stmt->execute([$adminId, $days]);
        return $stmt->fetch(\PDO::FETCH_ASSOC) ?: [];
    }

    /* =====================================================
       طلبات تحتاج callback (المؤجلة)
    ===================================================== */
    public function getPendingCallbacks(int $adminId = 0): array
    {
        $where  = "a.result = 'callback_later'";
        $params = [];

        // نأخذ فقط الطلبات التي آخر محاولة فيها = callback_later
        // ولم تُؤكد بعد
        $sql = "
            SELECT a.*, o.full_name, o.phone, o.order_status,
                   JSON_UNQUOTE(JSON_EXTRACT(o.extra, '$.product.name')) AS product_name
            FROM order_call_attempts a
            JOIN orders o ON o.id = a.order_id
            WHERE a.id = (
                SELECT MAX(id) FROM order_call_attempts
                WHERE order_id = a.order_id
            )
            AND a.result = 'callback_later'
            AND o.order_status = 'pending'
            " . ($adminId ? "AND a.admin_id = ?" : "") . "
            ORDER BY a.called_at ASC
        ";

        $params = $adminId ? [$adminId] : [];
        $stmt   = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }

    /* =====================================================
       إحصائيات إجمالية (لوحة التحكم)
    ===================================================== */
    public function getDashboardStats(string $from, string $to): array
    {
        $stmt = $this->db->prepare("
            SELECT
                COUNT(*)                                       AS total_calls,
                COUNT(DISTINCT order_id)                       AS orders_handled,
                COUNT(DISTINCT admin_id)                       AS active_agents,
                SUM(result = 'confirmed')                      AS confirmed,
                SUM(result = 'rejected')                       AS rejected,
                SUM(result = 'no_answer')                      AS no_answer,
                SUM(result = 'callback_later')                 AS pending_callbacks,
                ROUND(SUM(result='confirmed') / COUNT(*)*100,1) AS confirm_rate,
                ROUND(COUNT(*) / COUNT(DISTINCT order_id),1)   AS avg_calls_per_order
            FROM order_call_attempts
            WHERE called_at BETWEEN ? AND ?
        ");
        $stmt->execute([$from, $to]);
        return $stmt->fetch(\PDO::FETCH_ASSOC) ?: [];
    }

    /* =====================================================
       أداء الموظفين (للمقارنة)
    ===================================================== */
    public function getAgentsComparison(string $from, string $to): array
    {
        $stmt = $this->db->prepare("
            SELECT
                admin_id,
                admin_name,
                admin_role,
                COUNT(*)                                         AS total_calls,
                COUNT(DISTINCT order_id)                         AS orders_handled,
                SUM(result = 'confirmed')                        AS confirmed,
                SUM(result = 'rejected')                         AS rejected,
                SUM(result = 'no_answer')                        AS no_answer,
                SUM(result = 'callback_later')                   AS callbacks,
                ROUND(SUM(result='confirmed') / COUNT(*)*100, 1) AS confirm_rate,
                ROUND(COUNT(*) / COUNT(DISTINCT order_id), 1)    AS avg_calls_per_order
            FROM order_call_attempts
            WHERE called_at BETWEEN ? AND ?
            GROUP BY admin_id, admin_name, admin_role
            ORDER BY confirmed DESC
        ");
        $stmt->execute([$from, $to]);
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }
}