<?php

namespace Core\App\Models;

use PDO;

class OrderWorkflowModel
{
    private PDO $db;
    private TeamMemberModel $team;
    private OrderModel $orders;
    private AdminNotificationModel $notifications;

    public function __construct()
    {
        $this->db = \db();
        $this->team = new TeamMemberModel();
        $this->orders = new OrderModel();
        $this->notifications = new AdminNotificationModel();
        $this->ensureSchema();
    }

    private function ensureSchema(): void
    {
        static $ready = false;
        if ($ready) {
            return;
        }

        try {
            $this->db->exec("
                CREATE TABLE IF NOT EXISTS order_distribution_rules (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    rule_name VARCHAR(150) NOT NULL,
                    distribution_mode VARCHAR(30) NOT NULL DEFAULT 'balanced',
                    wilaya_codes TEXT NULL,
                    source_pattern VARCHAR(190) NULL,
                    product_ids TEXT NULL,
                    admin_ids TEXT NOT NULL,
                    work_start TIME NULL,
                    work_end TIME NULL,
                    hourly_limit INT NOT NULL DEFAULT 0,
                    daily_limit INT NOT NULL DEFAULT 0,
                    max_active_orders INT NOT NULL DEFAULT 0,
                    allow_when_backlog TINYINT(1) NOT NULL DEFAULT 1,
                    max_reassignments INT NOT NULL DEFAULT 3,
                    priority INT NOT NULL DEFAULT 100,
                    last_selected_admin_id INT NULL,
                    auto_reassign TINYINT(1) NOT NULL DEFAULT 1,
                    is_active TINYINT(1) NOT NULL DEFAULT 1,
                    created_by INT NULL,
                    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
            ");
        } catch (\Throwable $e) {}

        try {
            $this->db->exec("
                CREATE TABLE IF NOT EXISTS order_workflow_logs (
                    id BIGINT AUTO_INCREMENT PRIMARY KEY,
                    order_id INT NOT NULL,
                    admin_id INT NULL,
                    target_admin_id INT NULL,
                    event_type VARCHAR(60) NOT NULL,
                    note TEXT NULL,
                    meta LONGTEXT NULL,
                    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    INDEX idx_order_workflow_order (order_id),
                    INDEX idx_order_workflow_admin (admin_id),
                    INDEX idx_order_workflow_target (target_admin_id),
                    INDEX idx_order_workflow_type (event_type),
                    INDEX idx_order_workflow_created (created_at)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
            ");
        } catch (\Throwable $e) {}

        try {
            $this->db->exec("ALTER TABLE order_distribution_rules MODIFY updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");
        } catch (\Throwable $e) {}
        try { $this->db->exec("ALTER TABLE order_distribution_rules ADD COLUMN distribution_mode VARCHAR(30) NOT NULL DEFAULT 'balanced' AFTER rule_name"); } catch (\Throwable $e) {}
        try { $this->db->exec("ALTER TABLE order_distribution_rules ADD COLUMN work_start TIME NULL AFTER admin_ids"); } catch (\Throwable $e) {}
        try { $this->db->exec("ALTER TABLE order_distribution_rules ADD COLUMN work_end TIME NULL AFTER work_start"); } catch (\Throwable $e) {}
        try { $this->db->exec("ALTER TABLE order_distribution_rules ADD COLUMN hourly_limit INT NOT NULL DEFAULT 0 AFTER work_end"); } catch (\Throwable $e) {}
        try { $this->db->exec("ALTER TABLE order_distribution_rules ADD COLUMN daily_limit INT NOT NULL DEFAULT 0 AFTER hourly_limit"); } catch (\Throwable $e) {}
        try { $this->db->exec("ALTER TABLE order_distribution_rules ADD COLUMN allow_when_backlog TINYINT(1) NOT NULL DEFAULT 1 AFTER max_active_orders"); } catch (\Throwable $e) {}
        try { $this->db->exec("ALTER TABLE order_distribution_rules ADD COLUMN max_reassignments INT NOT NULL DEFAULT 3 AFTER allow_when_backlog"); } catch (\Throwable $e) {}
        try { $this->db->exec("ALTER TABLE order_distribution_rules ADD COLUMN last_selected_admin_id INT NULL AFTER priority"); } catch (\Throwable $e) {}

        $ready = true;
    }

    private function decodeJsonList($value): array
    {
        if (is_array($value)) {
            return $value;
        }
        $value = trim((string)$value);
        if ($value === '') {
            return [];
        }
        $json = json_decode($value, true);
        if (is_array($json)) {
            return $json;
        }
        return preg_split('/\s*,\s*/', $value, -1, PREG_SPLIT_NO_EMPTY) ?: [];
    }

    private function normalizeIntList($value): array
    {
        $items = [];
        foreach ($this->decodeJsonList($value) as $item) {
            $n = (int)$item;
            if ($n > 0) {
                $items[] = $n;
            }
        }
        $items = array_values(array_unique($items));
        sort($items);
        return $items;
    }

    private function normalizeStringList($value): array
    {
        $items = [];
        foreach ($this->decodeJsonList($value) as $item) {
            $item = trim((string)$item);
            if ($item !== '') {
                $items[] = $item;
            }
        }
        return array_values(array_unique($items));
    }

    private function encodeList(array $items): string
    {
        return json_encode(array_values($items), JSON_UNESCAPED_UNICODE);
    }

    public function getRules(): array
    {
        try {
            $stmt = $this->db->query("
                SELECT r.*,
                       a.username AS creator_name
                FROM order_distribution_rules r
                LEFT JOIN admins a ON a.id = r.created_by
                ORDER BY r.priority DESC, r.id ASC
            ");
            $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        } catch (\Throwable $e) {
            return [];
        }

        $nameMap = [];
        try {
            $admins = $this->db->query("SELECT id, username FROM admins WHERE role = 'confirmer'")->fetchAll(PDO::FETCH_ASSOC) ?: [];
            foreach ($admins as $admin) {
                $nameMap[(int)$admin['id']] = (string)$admin['username'];
            }
        } catch (\Throwable $e) {}

        foreach ($rows as &$row) {
            $row['admin_ids_list'] = $this->normalizeIntList($row['admin_ids'] ?? []);
            $row['product_ids_list'] = $this->normalizeIntList($row['product_ids'] ?? []);
            $row['wilaya_codes_list'] = $this->normalizeStringList($row['wilaya_codes'] ?? []);
            $row['distribution_mode'] = trim((string)($row['distribution_mode'] ?? 'balanced')) ?: 'balanced';
            $row['admin_names_list'] = array_values(array_filter(array_map(
                static fn(int $id): string => $nameMap[$id] ?? ('#' . $id),
                $row['admin_ids_list']
            )));
        }

        return $rows;
    }

    public function saveRule(array $data, int $currentAdminId): int
    {
        $id = (int)($data['id'] ?? 0);
        $name = trim((string)($data['rule_name'] ?? ''));
        $adminIds = $this->normalizeIntList($data['admin_ids'] ?? []);

        if ($name === '' || empty($adminIds)) {
            throw new \RuntimeException('Rule name and agents are required.');
        }

        $payload = [
            ':rule_name' => $name,
            ':distribution_mode' => trim((string)($data['distribution_mode'] ?? 'balanced')) ?: 'balanced',
            ':wilaya_codes' => $this->encodeList($this->normalizeStringList($data['wilaya_codes'] ?? [])),
            ':source_pattern' => trim((string)($data['source_pattern'] ?? '')),
            ':product_ids' => $this->encodeList($this->normalizeIntList($data['product_ids'] ?? [])),
            ':admin_ids' => $this->encodeList($adminIds),
            ':work_start' => trim((string)($data['work_start'] ?? '')) ?: null,
            ':work_end' => trim((string)($data['work_end'] ?? '')) ?: null,
            ':hourly_limit' => max(0, (int)($data['hourly_limit'] ?? 0)),
            ':daily_limit' => max(0, (int)($data['daily_limit'] ?? 0)),
            ':max_active_orders' => max(0, (int)($data['max_active_orders'] ?? 0)),
            ':allow_when_backlog' => !empty($data['allow_when_backlog']) ? 1 : 0,
            ':max_reassignments' => max(1, (int)($data['max_reassignments'] ?? 3)),
            ':priority' => max(0, (int)($data['priority'] ?? 100)),
            ':auto_reassign' => !empty($data['auto_reassign']) ? 1 : 0,
            ':is_active' => !empty($data['is_active']) ? 1 : 0,
        ];

        if ($id > 0) {
            $payload[':id'] = $id;
            $stmt = $this->db->prepare("
                UPDATE order_distribution_rules
                SET rule_name = :rule_name,
                    distribution_mode = :distribution_mode,
                    wilaya_codes = :wilaya_codes,
                    source_pattern = :source_pattern,
                    product_ids = :product_ids,
                    admin_ids = :admin_ids,
                    work_start = :work_start,
                    work_end = :work_end,
                    hourly_limit = :hourly_limit,
                    daily_limit = :daily_limit,
                    max_active_orders = :max_active_orders,
                    allow_when_backlog = :allow_when_backlog,
                    max_reassignments = :max_reassignments,
                    priority = :priority,
                    auto_reassign = :auto_reassign,
                    is_active = :is_active
                WHERE id = :id
            ");
            $stmt->execute($payload);
            return $id;
        }

        $payload[':created_by'] = $currentAdminId > 0 ? $currentAdminId : null;
        $stmt = $this->db->prepare("
            INSERT INTO order_distribution_rules
            (rule_name, distribution_mode, wilaya_codes, source_pattern, product_ids, admin_ids, work_start, work_end, hourly_limit, daily_limit, max_active_orders, allow_when_backlog, max_reassignments, priority, auto_reassign, is_active, created_by)
            VALUES
            (:rule_name, :distribution_mode, :wilaya_codes, :source_pattern, :product_ids, :admin_ids, :work_start, :work_end, :hourly_limit, :daily_limit, :max_active_orders, :allow_when_backlog, :max_reassignments, :priority, :auto_reassign, :is_active, :created_by)
        ");
        $stmt->execute($payload);
        return (int)$this->db->lastInsertId();
    }

    public function toggleRule(int $id): void
    {
        $this->db->prepare("UPDATE order_distribution_rules SET is_active = NOT is_active WHERE id = ?")->execute([$id]);
    }

    public function deleteRule(int $id): void
    {
        $this->db->prepare("DELETE FROM order_distribution_rules WHERE id = ?")->execute([$id]);
    }

    public function getAssignableMembers(): array
    {
        try {
            $stmt = $this->db->query("
                SELECT a.id, a.username, a.role, a.last_seen_at,
                       COUNT(CASE WHEN oa.action = 'pending' THEN oa.id END) AS pending_count
                FROM admins a
                LEFT JOIN order_assignments oa ON oa.admin_id = a.id
                WHERE a.role = 'confirmer'
                  AND a.is_active = 1
                GROUP BY a.id
                ORDER BY pending_count ASC, a.username ASC
            ");
            return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        } catch (\Throwable $e) {
            return [];
        }
    }

    private function extractOrderProfile(array $order): array
    {
        $extra = is_array($order['extra'] ?? null) ? $order['extra'] : [];
        $source = trim((string)(
            $extra['source']
            ?? $extra['meta']['source']
            ?? $extra['utm']['source']
            ?? $extra['whatsapp']['source']
            ?? ''
        ));

        $wilaya = trim((string)(
            $extra['shipping']['destination']['state_code']
            ?? $extra['shipping']['state_code']
            ?? ''
        ));

        if ($wilaya !== '') {
            $wilaya = str_pad((string)((int)$wilaya), 2, '0', STR_PAD_LEFT);
        }

        return [
            'product_id' => (int)($order['product_id'] ?? 0),
            'source' => mb_strtolower($source),
            'wilaya_code' => $wilaya,
        ];
    }

    private function isWithinWorkWindow(array $rule): bool
    {
        $start = trim((string)($rule['work_start'] ?? ''));
        $end = trim((string)($rule['work_end'] ?? ''));
        if ($start === '' || $end === '') {
            return true;
        }

        $now = date('H:i:s');
        if ($start <= $end) {
            return $now >= $start && $now <= $end;
        }

        return $now >= $start || $now <= $end;
    }

    private function countRecentAssignmentsForAdmin(int $adminId, string $unit = 'DAY'): int
    {
        $unit = strtoupper($unit) === 'HOUR' ? 'HOUR' : 'DAY';
        try {
            $stmt = $this->db->prepare("
                SELECT COUNT(*)
                FROM order_workflow_logs
                WHERE target_admin_id = ?
                  AND event_type IN ('assigned','reassigned')
                  AND created_at >= DATE_SUB(NOW(), INTERVAL 1 {$unit})
            ");
            $stmt->execute([$adminId]);
            return (int)$stmt->fetchColumn();
        } catch (\Throwable $e) {
            return 0;
        }
    }

    private function getCurrentReassignCount(int $orderId): int
    {
        try {
            $stmt = $this->db->prepare("
                SELECT COUNT(*)
                FROM order_workflow_logs
                WHERE order_id = ?
                  AND event_type = 'reassigned'
            ");
            $stmt->execute([$orderId]);
            return (int)$stmt->fetchColumn();
        } catch (\Throwable $e) {
            return 0;
        }
    }

    private function pickAssignee(array $members, array $rule, array $excludeIds = []): ?array
    {
        $allowedAdminIds = $rule['admin_ids_list'] ?? [];
        $maxLoad = (int)($rule['max_active_orders'] ?? 0);
        $hourlyLimit = (int)($rule['hourly_limit'] ?? 0);
        $dailyLimit = (int)($rule['daily_limit'] ?? 0);
        $allowWhenBacklog = !empty($rule['allow_when_backlog']);
        $distributionMode = (string)($rule['distribution_mode'] ?? 'balanced');

        if (!$this->isWithinWorkWindow($rule)) {
            return null;
        }

        $candidates = [];
        foreach ($members as $member) {
            $memberId = (int)($member['id'] ?? 0);
            if ($memberId <= 0) {
                continue;
            }
            if (!empty($allowedAdminIds) && !in_array($memberId, $allowedAdminIds, true)) {
                continue;
            }
            if (in_array($memberId, $excludeIds, true)) {
                continue;
            }
            if ($maxLoad > 0 && (int)($member['pending_count'] ?? 0) >= $maxLoad) {
                continue;
            }
            if (!$allowWhenBacklog && (int)($member['pending_count'] ?? 0) > 0) {
                continue;
            }
            if ($hourlyLimit > 0 && $this->countRecentAssignmentsForAdmin($memberId, 'HOUR') >= $hourlyLimit) {
                continue;
            }
            if ($dailyLimit > 0 && $this->countRecentAssignmentsForAdmin($memberId, 'DAY') >= $dailyLimit) {
                continue;
            }
            $candidates[] = $member;
        }

        usort($candidates, static function (array $a, array $b): int {
            $pendingA = (int)($a['pending_count'] ?? 0);
            $pendingB = (int)($b['pending_count'] ?? 0);
            if ($pendingA !== $pendingB) {
                return $pendingA <=> $pendingB;
            }
            return strcmp((string)($a['username'] ?? ''), (string)($b['username'] ?? ''));
        });

        if ($distributionMode === 'priority') {
            usort($candidates, static function (array $a, array $b): int {
                $seenA = strtotime((string)($a['last_seen_at'] ?? '1970-01-01 00:00:00'));
                $seenB = strtotime((string)($b['last_seen_at'] ?? '1970-01-01 00:00:00'));
                if ($seenA !== $seenB) {
                    return $seenB <=> $seenA;
                }
                return ((int)($a['pending_count'] ?? 0)) <=> ((int)($b['pending_count'] ?? 0));
            });
        } elseif ($distributionMode === 'random') {
            shuffle($candidates);
        } elseif ($distributionMode === 'round_robin') {
            $orderedIds = !empty($allowedAdminIds)
                ? array_values(array_filter(array_map('intval', $allowedAdminIds), static fn(int $id): bool => $id > 0))
                : array_map(static fn(array $candidate): int => (int)($candidate['id'] ?? 0), $candidates);
            $lastSelected = (int)($rule['last_selected_admin_id'] ?? 0);
            $nextId = $this->pickNextRoundRobinId($orderedIds, $candidates, $lastSelected);
            if ($nextId > 0) {
                foreach ($candidates as $candidate) {
                    if ((int)($candidate['id'] ?? 0) === $nextId) {
                        return $candidate;
                    }
                }
            }
        }

        return $candidates[0] ?? null;
    }

    private function pickNextRoundRobinId(array $orderedIds, array $candidates, int $lastSelected): int
    {
        $candidateIds = array_values(array_map(static fn(array $candidate): int => (int)($candidate['id'] ?? 0), $candidates));
        $candidateIds = array_values(array_filter($candidateIds, static fn(int $id): bool => $id > 0));
        if (empty($candidateIds)) {
            return 0;
        }

        $sequence = array_values(array_filter($orderedIds, static fn(int $id): bool => in_array($id, $candidateIds, true)));
        if (empty($sequence)) {
            $sequence = $candidateIds;
        }

        if ($lastSelected <= 0 || !in_array($lastSelected, $sequence, true)) {
            return (int)$sequence[0];
        }

        $index = array_search($lastSelected, $sequence, true);
        if ($index === false) {
            return (int)$sequence[0];
        }

        $nextIndex = ($index + 1) % count($sequence);
        return (int)$sequence[$nextIndex];
    }

    private function markRuleAssignmentUsage(int $ruleId, int $adminId): void
    {
        if ($ruleId <= 0 || $adminId <= 0) {
            return;
        }

        try {
            $this->db->prepare("
                UPDATE order_distribution_rules
                SET last_selected_admin_id = :admin_id
                WHERE id = :id
            ")->execute([
                ':admin_id' => $adminId,
                ':id' => $ruleId,
            ]);
        } catch (\Throwable $e) {
        }
    }

    private function ruleMatches(array $rule, array $profile): bool
    {
        $wilayas = $rule['wilaya_codes_list'] ?? [];
        if (!empty($wilayas) && !in_array($profile['wilaya_code'], $wilayas, true)) {
            return false;
        }

        $productIds = $rule['product_ids_list'] ?? [];
        if (!empty($productIds) && !in_array((int)$profile['product_id'], $productIds, true)) {
            return false;
        }

        $sourcePattern = mb_strtolower(trim((string)($rule['source_pattern'] ?? '')));
        if ($sourcePattern !== '' && mb_strpos($profile['source'], $sourcePattern) === false) {
            return false;
        }

        return !empty($rule['admin_ids_list']);
    }

    public function logEvent(int $orderId, ?int $adminId, string $eventType, string $note = '', array $meta = [], ?int $targetAdminId = null): void
    {
        try {
            $stmt = $this->db->prepare("
                INSERT INTO order_workflow_logs (order_id, admin_id, target_admin_id, event_type, note, meta, created_at)
                VALUES (:order_id, :admin_id, :target_admin_id, :event_type, :note, :meta, NOW())
            ");
            $stmt->execute([
                ':order_id' => $orderId,
                ':admin_id' => $adminId ?: null,
                ':target_admin_id' => $targetAdminId ?: null,
                ':event_type' => $eventType,
                ':note' => $note !== '' ? $note : null,
                ':meta' => !empty($meta) ? json_encode($meta, JSON_UNESCAPED_UNICODE) : null,
            ]);
        } catch (\Throwable $e) {}
    }

    public function getOrderTimeline(int $orderId): array
    {
        try {
            $stmt = $this->db->prepare("
                SELECT l.*,
                       a.username AS admin_name,
                       a.role AS admin_role,
                       t.username AS target_admin_name
                FROM order_workflow_logs l
                LEFT JOIN admins a ON a.id = l.admin_id
                LEFT JOIN admins t ON t.id = l.target_admin_id
                WHERE l.order_id = ?
                ORDER BY l.created_at DESC, l.id DESC
            ");
            $stmt->execute([$orderId]);
            $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        } catch (\Throwable $e) {
            return [];
        }

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

        return $rows;
    }

    public function getCurrentAssignment(int $orderId): ?array
    {
        try {
            $stmt = $this->db->prepare("
                SELECT oa.*, a.username, a.role, a.last_seen_at
                FROM order_assignments oa
                LEFT JOIN admins a ON a.id = oa.admin_id
                WHERE oa.order_id = ?
                LIMIT 1
            ");
            $stmt->execute([$orderId]);
            return $stmt->fetch(PDO::FETCH_ASSOC) ?: null;
        } catch (\Throwable $e) {
            return null;
        }
    }

    private function syncOrderExtraAssignment(int $orderId, int $adminId, string $reason, string $note = ''): void
    {
        $order = $this->orders->getById($orderId);
        if (!$order) {
            return;
        }

        $extra = is_array($order['extra'] ?? null) ? $order['extra'] : [];
        $extra['workflow'] = $extra['workflow'] ?? [];
        $previousAssignment = $extra['workflow']['current_assignment']['admin_id'] ?? null;
        $extra['workflow']['assigned_history'] = $extra['workflow']['assigned_history'] ?? [];
        $extra['workflow']['assigned_history'][] = [
            'admin_id' => $adminId,
            'reason' => $reason,
            'note' => $note,
            'assigned_at' => date('Y-m-d H:i:s'),
        ];
        if (empty($extra['workflow']['first_assigned_at'])) {
            $extra['workflow']['first_assigned_at'] = date('Y-m-d H:i:s');
        }
        if ($previousAssignment && (int)$previousAssignment !== $adminId) {
            $extra['workflow']['reassign_count'] = (int)($extra['workflow']['reassign_count'] ?? 0) + 1;
        } else {
            $extra['workflow']['reassign_count'] = (int)($extra['workflow']['reassign_count'] ?? 0);
        }
        $extra['workflow']['current_assignment'] = [
            'admin_id' => $adminId,
            'reason' => $reason,
            'note' => $note,
            'assigned_at' => date('Y-m-d H:i:s'),
        ];
        $extra['workflow']['last_action'] = $reason;
        $extra['workflow']['last_action_at'] = date('Y-m-d H:i:s');
        $this->orders->updateExtra($orderId, $extra);
    }

    private function syncWorkflowActionState(int $orderId, string $action, int $adminId, string $note = ''): void
    {
        $order = $this->orders->getById($orderId);
        if (!$order) {
            return;
        }
        $extra = is_array($order['extra'] ?? null) ? $order['extra'] : [];
        $extra['workflow'] = $extra['workflow'] ?? [];
        $extra['workflow']['last_action'] = $action;
        $extra['workflow']['last_action_by'] = $adminId;
        $extra['workflow']['last_action_at'] = date('Y-m-d H:i:s');
        $extra['workflow']['last_note'] = $note;
        $this->orders->updateExtra($orderId, $extra);
    }

    public function assignOrderToAdmin(int $orderId, int $adminId, ?int $actorId = null, string $reason = 'manual_assignment', string $note = ''): ?array
    {
        if ($orderId <= 0 || $adminId <= 0) {
            return null;
        }

        $current = $this->getCurrentAssignment($orderId);

        $this->team->assignOrderTo($orderId, $adminId);
        $this->syncOrderExtraAssignment($orderId, $adminId, $reason, $note);

        $target = $this->getCurrentAssignment($orderId);
        $this->logEvent($orderId, $actorId, $current ? 'reassigned' : 'assigned', $note, [
            'reason' => $reason,
            'from_admin_id' => (int)($current['admin_id'] ?? 0),
            'to_admin_id' => $adminId,
        ], $adminId);

        $this->notifications->create(
            $current ? 'order_reassigned' : 'order_assigned',
            $orderId,
            __('team.workflow_order_assigned_notification', 'Order assigned to you') . ' #' . $orderId,
            $adminId,
            [
                'reason' => $reason,
                'from_admin_id' => (int)($current['admin_id'] ?? 0),
                'to_admin_id' => $adminId,
            ]
        );

        return $target;
    }

    public function autoAssignOrder(int $orderId, string $reason = 'auto_assignment', array $excludeIds = [], ?int $actorId = null, string $note = ''): ?array
    {
        $order = $this->orders->getById($orderId);
        if (!$order) {
            return null;
        }

        $profile = $this->extractOrderProfile($order);
        $rules = array_filter($this->getRules(), static fn(array $rule): bool => (int)($rule['is_active'] ?? 0) === 1);
        $members = $this->getAssignableMembers();

        foreach ($rules as $rule) {
            if (!$this->ruleMatches($rule, $profile)) {
                continue;
            }

            $candidate = $this->pickAssignee($members, $rule, $excludeIds);
            if ($candidate) {
                $assigned = $this->assignOrderToAdmin(
                    $orderId,
                    (int)$candidate['id'],
                    $actorId,
                    $reason,
                    $note !== '' ? $note : (string)($rule['rule_name'] ?? '')
                );
                if ($assigned) {
                    $this->markRuleAssignmentUsage((int)($rule['id'] ?? 0), (int)$candidate['id']);
                }
                return $assigned;
            }
        }

        $fallbackRule = [
            'admin_ids_list' => array_map(static fn(array $m): int => (int)$m['id'], $members),
            'max_active_orders' => 0,
        ];
        $candidate = $this->pickAssignee($members, $fallbackRule, $excludeIds);
        if (!$candidate) {
            return null;
        }

        return $this->assignOrderToAdmin($orderId, (int)$candidate['id'], $actorId, $reason, $note);
    }

    public function markOutcome(int $orderId, int $adminId, string $status, string $note = ''): void
    {
        try {
            $stmt = $this->db->prepare("
                UPDATE order_assignments
                SET action = :action, confirmed_at = NOW()
                WHERE order_id = :order_id AND admin_id = :admin_id
            ");
            $stmt->execute([
                ':action' => $status,
                ':order_id' => $orderId,
                ':admin_id' => $adminId,
            ]);
        } catch (\Throwable $e) {}

        $this->logEvent($orderId, $adminId, $status === 'confirmed' ? 'order_confirmed' : 'order_rejected', $note, [
            'status' => $status,
        ]);
        $this->syncWorkflowActionState($orderId, $status, $adminId, $note);
    }

    public function recordHandlingAction(int $orderId, int $adminId, string $action, string $note = ''): void
    {
        $map = [
            'call_made' => 'call_made',
            'no_answer' => 'no_answer',
            'closed' => 'closed',
            'deferred' => 'deferred',
            'callback_requested' => 'callback_requested',
            'cancelled' => 'cancelled',
            'address_issue' => 'address_issue',
            'wrong_number' => 'wrong_number',
            'needs_manager' => 'needs_manager',
            'note_added' => 'note_added',
        ];
        $eventType = $map[$action] ?? 'note_added';
        $this->logEvent($orderId, $adminId, $eventType, $note);
        $this->syncWorkflowActionState($orderId, $eventType, $adminId, $note);
    }

    public function markViewed(int $orderId, int $adminId): void
    {
        if ($adminId <= 0) {
            return;
        }

        $order = $this->orders->getById($orderId);
        if (!$order) {
            return;
        }

        $extra = is_array($order['extra'] ?? null) ? $order['extra'] : [];
        $extra['workflow'] = $extra['workflow'] ?? [];

        if (empty($extra['workflow']['first_opened_at'])) {
            $extra['workflow']['first_opened_at'] = date('Y-m-d H:i:s');
            $this->logEvent($orderId, $adminId, 'order_opened', '');
        }

        $extra['workflow']['last_opened_at'] = date('Y-m-d H:i:s');
        $extra['workflow']['last_opened_by'] = $adminId;
        $extra['workflow']['open_count'] = (int)($extra['workflow']['open_count'] ?? 0) + 1;
        $this->orders->updateExtra($orderId, $extra);
    }

    public function declineAndReassign(int $orderId, int $adminId, string $note = ''): ?array
    {
        $current = $this->getCurrentAssignment($orderId);
        if (!$current || (int)($current['admin_id'] ?? 0) !== $adminId) {
            throw new \RuntimeException('This order is not assigned to the current user.');
        }

        try {
            $this->db->prepare("
                UPDATE order_assignments
                SET action = 'declined', confirmed_at = NOW()
                WHERE order_id = ? AND admin_id = ?
            ")->execute([$orderId, $adminId]);
        } catch (\Throwable $e) {}

        $this->logEvent($orderId, $adminId, 'declined_by_agent', $note, [
            'from_admin_id' => $adminId,
        ]);

        $reassignCount = $this->getCurrentReassignCount($orderId);
        $maxReassignments = 3;
        $rules = array_filter($this->getRules(), static fn(array $rule): bool => (int)($rule['is_active'] ?? 0) === 1);
        foreach ($rules as $rule) {
            if (!empty($rule['admin_ids_list']) && in_array($adminId, $rule['admin_ids_list'], true)) {
                $maxReassignments = max(1, (int)($rule['max_reassignments'] ?? 3));
                break;
            }
        }

        if ($reassignCount >= $maxReassignments) {
            $this->logEvent($orderId, $adminId, 'awaiting_manager', $note, [
                'reason' => 'reassign_limit_reached',
                'reassign_count' => $reassignCount,
                'max_reassignments' => $maxReassignments,
            ]);
            $this->syncWorkflowActionState($orderId, 'awaiting_manager', $adminId, $note);
            $this->notifications->create(
                'order_awaiting_manager',
                $orderId,
                __('team.workflow_awaiting_manager_notification', 'Order needs manager review') . ' #' . $orderId
            );
            return null;
        }

        $next = $this->autoAssignOrder($orderId, 'reassigned_after_decline', [$adminId], $adminId, $note);
        if (!$next) {
            $this->logEvent($orderId, $adminId, 'awaiting_manager', $note, [
                'reason' => 'no_agent_available',
            ]);
            $this->syncWorkflowActionState($orderId, 'awaiting_manager', $adminId, $note);
            $this->notifications->create(
                'order_awaiting_manager',
                $orderId,
                __('team.workflow_awaiting_manager_notification', 'Order needs manager review') . ' #' . $orderId
            );
        }

        return $next;
    }

    private function sumWorkedSeconds(array $events): int
    {
        $seconds = 0;
        $loginAt = null;

        foreach ($events as $event) {
            $action = (string)($event['action'] ?? '');
            $at = strtotime((string)($event['created_at'] ?? ''));
            if (!$at) {
                continue;
            }

            if ($action === 'login') {
                $loginAt = $at;
            } elseif ($action === 'logout' && $loginAt) {
                if ($at > $loginAt) {
                    $seconds += ($at - $loginAt);
                }
                $loginAt = null;
            }
        }

        if ($loginAt) {
            $seconds += max(0, time() - $loginAt);
        }

        return $seconds;
    }

    public function estimateWorkedSeconds(int $adminId, int $days = 7): int
    {
        try {
            $stmt = $this->db->prepare("
                SELECT action, created_at
                FROM admin_activity_log
                WHERE admin_id = ?
                  AND action IN ('login','logout')
                  AND created_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
                ORDER BY created_at ASC, id ASC
            ");
            $stmt->execute([$adminId, $days]);
            $events = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        } catch (\Throwable $e) {
            return 0;
        }

        return $this->sumWorkedSeconds($events);
    }

    private function normalizeRange(string $range, ?string $from = null, ?string $to = null): array
    {
        $range = trim($range);
        $now = new \DateTimeImmutable('now');

        switch ($range) {
            case 'hour':
                $start = $now->modify('-1 hour');
                $end = $now;
                break;
            case 'day':
                $start = $now->setTime(0, 0, 0);
                $end = $now;
                break;
            case 'week':
                $start = $now->modify('-7 days');
                $end = $now;
                break;
            case 'month':
                $start = $now->modify('-30 days');
                $end = $now;
                break;
            case 'custom':
                $start = $from ? new \DateTimeImmutable($from . ' 00:00:00') : $now->modify('-7 days');
                $end = $to ? new \DateTimeImmutable($to . ' 23:59:59') : $now;
                break;
            default:
                $range = 'week';
                $start = $now->modify('-7 days');
                $end = $now;
        }

        return [
            'range' => $range,
            'start' => $start->format('Y-m-d H:i:s'),
            'end' => $end->format('Y-m-d H:i:s'),
            'days' => max(1, (int)ceil((strtotime($end->format('Y-m-d H:i:s')) - strtotime($start->format('Y-m-d H:i:s'))) / 86400)),
        ];
    }

    public function getDashboard(string $range = 'week', ?string $from = null, ?string $to = null): array
    {
        $window = $this->normalizeRange($range, $from, $to);
        $days = (int)$window['days'];
        $start = $window['start'];
        $end = $window['end'];

        $summary = [
            'active_rules' => 0,
            'pending_assignments' => 0,
            'confirmed' => 0,
            'rejected' => 0,
            'cancelled' => 0,
            'deferred' => 0,
            'declined' => 0,
            'reassigned' => 0,
            'avg_first_response_minutes' => 0,
            'avg_resolution_minutes' => 0,
            'stale_orders' => 0,
        ];

        try {
            $summary['active_rules'] = (int)$this->db->query("SELECT COUNT(*) FROM order_distribution_rules WHERE is_active = 1")->fetchColumn();
        } catch (\Throwable $e) {}

        try {
            $summary['pending_assignments'] = (int)$this->db->query("SELECT COUNT(*) FROM order_assignments WHERE action = 'pending'")->fetchColumn();
        } catch (\Throwable $e) {}

        try {
            $stmt = $this->db->prepare("
                SELECT
                    COALESCE(SUM(confirmed), 0) AS confirmed,
                    COALESCE(SUM(rejected), 0) AS rejected
                FROM team_performance
                WHERE date BETWEEN DATE(?) AND DATE(?)
            ");
            $stmt->execute([$start, $end]);
            $perf = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];
            $summary['confirmed'] = (int)($perf['confirmed'] ?? 0);
            $summary['rejected'] = (int)($perf['rejected'] ?? 0);
        } catch (\Throwable $e) {}

        try {
            $stmt = $this->db->prepare("
                SELECT event_type, COUNT(*) AS total
                FROM order_workflow_logs
                WHERE created_at BETWEEN ? AND ?
                  AND event_type IN ('declined_by_agent','reassigned','cancelled','deferred')
                GROUP BY event_type
            ");
            $stmt->execute([$start, $end]);
            foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) ?: [] as $row) {
                if (($row['event_type'] ?? '') === 'declined_by_agent') {
                    $summary['declined'] = (int)($row['total'] ?? 0);
                }
                if (($row['event_type'] ?? '') === 'reassigned') {
                    $summary['reassigned'] = (int)($row['total'] ?? 0);
                }
                if (($row['event_type'] ?? '') === 'cancelled') {
                    $summary['cancelled'] = (int)($row['total'] ?? 0);
                }
                if (($row['event_type'] ?? '') === 'deferred') {
                    $summary['deferred'] = (int)($row['total'] ?? 0);
                }
            }
        } catch (\Throwable $e) {}

        try {
            $summary['stale_orders'] = (int)$this->db->query("
                SELECT COUNT(*)
                FROM order_assignments oa
                WHERE oa.action = 'pending'
                  AND oa.assigned_at <= DATE_SUB(NOW(), INTERVAL 6 HOUR)
            ")->fetchColumn();
        } catch (\Throwable $e) {}

        try {
            $stmt = $this->db->prepare("
                SELECT
                    AVG(TIMESTAMPDIFF(MINUTE, assigned.created_at, opened.created_at)) AS avg_first_response
                FROM order_workflow_logs assigned
                JOIN order_workflow_logs opened
                    ON opened.order_id = assigned.order_id
                   AND opened.event_type = 'order_opened'
                WHERE assigned.event_type IN ('assigned','reassigned')
                  AND assigned.created_at BETWEEN ? AND ?
                  AND opened.created_at >= assigned.created_at
            ");
            $stmt->execute([$start, $end]);
            $summary['avg_first_response_minutes'] = round((float)$stmt->fetchColumn(), 1);
        } catch (\Throwable $e) {}

        try {
            $stmt = $this->db->prepare("
                SELECT
                    AVG(TIMESTAMPDIFF(MINUTE, assigned.created_at, outcome.created_at)) AS avg_resolution
                FROM order_workflow_logs assigned
                JOIN order_workflow_logs outcome
                    ON outcome.order_id = assigned.order_id
                   AND outcome.event_type IN ('order_confirmed','order_rejected')
                WHERE assigned.event_type IN ('assigned','reassigned')
                  AND assigned.created_at BETWEEN ? AND ?
                  AND outcome.created_at >= assigned.created_at
            ");
            $stmt->execute([$start, $end]);
            $summary['avg_resolution_minutes'] = round((float)$stmt->fetchColumn(), 1);
        } catch (\Throwable $e) {}

        $members = [];
        try {
            $stmt = $this->db->prepare("
                SELECT a.id, a.username, a.role, a.last_seen_at, a.is_active,
                       COUNT(CASE WHEN oa.action = 'pending' THEN oa.id END) AS pending_assigned,
                       COALESCE(SUM(tp.confirmed), 0) AS confirmed,
                       COALESCE(SUM(tp.rejected), 0) AS rejected,
                       COALESCE(SUM(tp.total), 0) AS total
                FROM admins a
                LEFT JOIN order_assignments oa ON oa.admin_id = a.id
                LEFT JOIN team_performance tp
                    ON tp.admin_id = a.id
                   AND tp.date BETWEEN DATE(?) AND DATE(?)
                WHERE a.is_active = 1
                  AND a.role IN ('manager','confirmer','shipping')
                GROUP BY a.id
                ORDER BY FIELD(a.role, 'manager', 'confirmer', 'shipping'), a.username ASC
            ");
            $stmt->execute([$start, $end]);
            $members = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        } catch (\Throwable $e) {}

        $metaMap = [];
        try {
            $stmt = $this->db->prepare("
                SELECT admin_id,
                       SUM(event_type = 'declined_by_agent') AS declined_count,
                       SUM(event_type = 'callback_requested') AS callback_count,
                       SUM(event_type = 'needs_manager') AS escalations_count,
                       SUM(event_type = 'note_added') AS notes_count,
                       SUM(event_type = 'cancelled') AS cancelled_count,
                       SUM(event_type = 'deferred') AS deferred_count,
                       SUM(event_type = 'wrong_number') AS wrong_number_count,
                       SUM(event_type = 'no_answer') AS no_answer_count,
                       SUM(event_type = 'closed') AS closed_count
                FROM order_workflow_logs
                WHERE created_at BETWEEN ? AND ?
                  AND admin_id IS NOT NULL
                GROUP BY admin_id
            ");
            $stmt->execute([$start, $end]);
            foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) ?: [] as $row) {
                $metaMap[(int)$row['admin_id']] = $row;
            }
        } catch (\Throwable $e) {}

        foreach ($members as &$member) {
            $memberId = (int)($member['id'] ?? 0);
            $meta = $metaMap[$memberId] ?? [];
            $member['declined_count'] = (int)($meta['declined_count'] ?? 0);
            $member['callback_count'] = (int)($meta['callback_count'] ?? 0);
            $member['escalations_count'] = (int)($meta['escalations_count'] ?? 0);
            $member['notes_count'] = (int)($meta['notes_count'] ?? 0);
            $member['cancelled_count'] = (int)($meta['cancelled_count'] ?? 0);
            $member['deferred_count'] = (int)($meta['deferred_count'] ?? 0);
            $member['wrong_number_count'] = (int)($meta['wrong_number_count'] ?? 0);
            $member['no_answer_count'] = (int)($meta['no_answer_count'] ?? 0);
            $member['closed_count'] = (int)($meta['closed_count'] ?? 0);
            $member['worked_seconds'] = $this->estimateWorkedSeconds($memberId, $days);
            $member['confirm_rate'] = (int)($member['total'] ?? 0) > 0
                ? round(((int)($member['confirmed'] ?? 0) / (int)$member['total']) * 100, 1)
                : 0;
            $member['score'] = round(
                ((int)($member['confirmed'] ?? 0) * 6)
                + ((float)$member['confirm_rate'] * 1.5)
                - ((int)($member['declined_count'] ?? 0) * 4)
                - ((int)($member['cancelled_count'] ?? 0) * 3)
                - ((int)($member['wrong_number_count'] ?? 0) * 2)
                - ((int)($member['pending_assigned'] ?? 0) * 1.2)
                + min(10, round(((int)($member['worked_seconds'] ?? 0) / 3600), 1)),
                1
            );
        }

        usort($members, static function (array $a, array $b): int {
            return ((float)($b['score'] ?? 0)) <=> ((float)($a['score'] ?? 0));
        });
        foreach ($members as $idx => &$member) {
            $member['rank'] = $idx + 1;
        }

        return [
            'summary' => $summary,
            'rules' => $this->getRules(),
            'members' => $members,
            'window' => $window,
        ];
    }
}
