<?php
namespace App\Services;

use App\Database\Connection;
use App\Utils\Sanitizer;
use App\Utils\Logger;
use PDO;

class AiContextService
{
    private Connection $db;
    private RbacService $rbac;

    public function __construct(Connection $db, RbacService $rbac)
    {
        $this->db = $db;
        $this->rbac = $rbac;
    }

    public function chatContext(string $message, string $screen = '', string $entityType = '', string $entityId = ''): array
    {
        $context = [
            'context_type' => 'chat',
            'screen' => $this->limit($screen, 80),
            'message' => $this->limit($message, 1200),
            'entity_type' => $this->limit($entityType, 40),
            'entity_id' => $this->limit($entityId, 120),
            'source_refs' => [],
        ];

        if ($entityType === 'customer' && $entityId !== '') {
            $brief = $this->customerBriefContext($entityId);
            $context['customer'] = $brief['customer'];
            $context['recent_deals'] = $brief['recent_deals'];
            $context['recent_activities'] = $brief['recent_activities'];
            $context['source_refs'] = $brief['source_refs'];
        } elseif ($entityType === 'deal' && $entityId !== '') {
            $deal = $this->dealRiskContext($entityId);
            $context['deal'] = $deal['deal'];
            $context['customer'] = $deal['customer'];
            $context['recent_activities'] = $deal['recent_activities'];
            $context['source_refs'] = $deal['source_refs'];
        } else {
            $this->attachScopedScreenContext($context, $message, $screen);
        }

        return $context;
    }

    public function customerBriefContext(string $contactId): array
    {
        $person = $this->findPerson($contactId);
        if (!$person) {
            throw new \RuntimeException('Customer not found');
        }
        $this->assertOwnerVisible('leads', (string)($person['owner_didar_id'] ?? ''));

        return [
            'context_type' => 'customer_brief',
            'entity_type' => 'customer',
            'entity_id' => (string)$person['didar_contact_id'],
            'customer' => $this->sanitizePerson($person),
            'recent_deals' => $this->recentDealsForContact((string)$person['didar_contact_id']),
            'recent_activities' => $this->recentActivities((string)$person['didar_contact_id'], '', 8),
            'recent_support' => $this->recentSupport((string)$person['didar_contact_id']),
            'source_refs' => [
                ['label' => 'persons', 'record_id' => (string)$person['didar_contact_id']],
                ['label' => 'deals', 'record_id' => 'contact:' . (string)$person['didar_contact_id']],
                ['label' => 'activities', 'record_id' => 'contact:' . (string)$person['didar_contact_id']],
            ],
        ];
    }

    public function dealRiskContext(string $dealId): array
    {
        $deal = $this->findDeal($dealId);
        if (!$deal) {
            throw new \RuntimeException('Deal not found');
        }
        $owner = (string)($deal['owner_didar_id'] ?? $deal['contact_owner_didar_id'] ?? '');
        $this->assertOwnerVisible('deals', $owner);

        $contactId = (string)($deal['contact_didar_id'] ?? '');
        return [
            'context_type' => 'deal_risk',
            'entity_type' => 'deal',
            'entity_id' => (string)$deal['didar_deal_id'],
            'deal' => $this->sanitizeDeal($deal),
            'customer' => $contactId !== '' ? $this->sanitizePerson($this->findPerson($contactId) ?: []) : [],
            'recent_activities' => $this->recentActivities($contactId, (string)$deal['didar_deal_id'], 8),
            'payments_summary' => $this->paymentsSummaryForDeal((int)$deal['id']),
            'source_refs' => [
                ['label' => 'deals', 'record_id' => (string)$deal['didar_deal_id']],
                ['label' => 'activities', 'record_id' => 'deal:' . (string)$deal['didar_deal_id']],
                ['label' => 'transactions', 'record_id' => 'deal_id:' . (string)$deal['id']],
            ],
        ];
    }

    public function activityDraftContext(string $contactId = '', string $dealId = '', string $intent = ''): array
    {
        $context = [
            'context_type' => 'activity_draft',
            'intent' => $this->limit($intent, 800),
            'source_refs' => [],
        ];
        if ($dealId !== '') {
            $dealContext = $this->dealRiskContext($dealId);
            $context['deal'] = $dealContext['deal'];
            $context['customer'] = $dealContext['customer'];
            $context['recent_activities'] = $dealContext['recent_activities'];
            $context['entity_type'] = 'deal';
            $context['entity_id'] = (string)$dealContext['entity_id'];
            $context['source_refs'] = $dealContext['source_refs'];
            return $context;
        }
        if ($contactId !== '') {
            $brief = $this->customerBriefContext($contactId);
            $context['customer'] = $brief['customer'];
            $context['recent_deals'] = $brief['recent_deals'];
            $context['recent_activities'] = $brief['recent_activities'];
            $context['entity_type'] = 'customer';
            $context['entity_id'] = (string)$brief['entity_id'];
            $context['source_refs'] = $brief['source_refs'];
            return $context;
        }
        throw new \RuntimeException('contact_id or deal_id is required');
    }

    public function kpiExplainContext(array $filters): array
    {
        $filters = $this->normalizeKpiFilters($filters);
        $leadFilters = $this->rbac->applyOwnerScope($filters, 'leads', (string)($filters['owner_didar_id'] ?? ''));
        $dealFilters = $this->rbac->applyOwnerScope($filters, 'deals', (string)($filters['owner_didar_id'] ?? ''));

        return [
            'context_type' => 'kpi_explain',
            'filters' => $filters,
            'lead_summary' => $this->leadSummary($leadFilters),
            'deal_summary' => $this->dealSummary($dealFilters),
            'activity_summary' => $this->activitySummary($leadFilters),
            'payment_summary' => $this->paymentSummary($dealFilters),
            'source_refs' => [
                ['label' => 'persons', 'record_id' => 'aggregate'],
                ['label' => 'deals', 'record_id' => 'aggregate'],
                ['label' => 'activities', 'record_id' => 'aggregate'],
                ['label' => 'transactions', 'record_id' => 'aggregate'],
            ],
        ];
    }

    public function operationRuleDraftContext(string $instruction): array
    {
        return [
            'context_type' => 'operation_rule_draft',
            'instruction' => $this->limit($instruction, 1200),
            'allowed_filters' => [
                'owner_didar_id', 'previous_owner_id', 'sale_status', 'deal_status', 'pool_key',
                'no_owner', 'search', 'lead_date_from', 'lead_date_to', 'last_deal_from', 'last_deal_to',
                'last_activity_from', 'last_activity_to', 'last_payment_from', 'last_payment_to',
                'deal_older_than_days', 'has_payment',
            ],
            'allowed_condition_fields' => [
                'last_deal_at', 'lead_registered_at', 'last_activity_at', 'last_payment_at',
                'has_any_payment', 'last_deal_status', 'sale_status', 'owner_empty', 'pool_key',
            ],
            'allowed_operations' => [
                'change_owner' => ['requires' => ['owner_didar_id']],
                'move_lead_to_pool' => ['requires' => ['pool_key']],
                'mark_last_deal_lost' => ['requires' => ['failure_reason']],
            ],
            'operation_contract' => [
                'filter' => 'OperationManagerService preview filters. Do not include SQL.',
                'condition' => ['all' => [['field' => 'last_deal_at', 'operator' => 'older_than', 'value' => 30]]],
                'operation' => ['actions' => [['type' => 'move_lead_to_pool', 'pool_key' => 'crm_leads', 'reason' => 'Human-approved AI draft']]],
            ],
            'source_refs' => [
                ['label' => 'operation_manager_contract', 'record_id' => 'phase1'],
            ],
        ];
    }

    private function attachScopedScreenContext(array &$context, string $message, string $screen): void
    {
        $normalizedScreen = strtolower(trim($screen));
        $context['crm_scope'] = [
            'rbac_enforced' => true,
            'current_date' => date('Y-m-d'),
            'current_datetime' => date('Y-m-d H:i:s'),
            'policy' => 'Only compact CRM projections inside the current user RBAC owner scope are included.',
        ];

        $leadFilters = $this->rbac->applyOwnerScope([], 'leads');
        $dealFilters = $this->rbac->applyOwnerScope([], 'deals');
        $needsFollowUp = $this->mentionsAny($message . ' ' . $screen, [
            'پیگیری', 'پی گیری', 'دنبال', 'امروز', 'سررسید', 'یادآوری',
            'follow', 'follow-up', 'follow up', 'today', 'due', 'reminder',
        ]);
        $needsOverdue = $this->mentionsAny($message . ' ' . $screen, [
            'عقب افتاده', 'عقب‌افتاده', 'گذشته', 'دیر شده', 'تاخیر', 'تأخیر',
            'overdue', 'late', 'past due',
        ]);
        $isLeadArea = $this->mentionsAny($normalizedScreen . ' ' . $message, [
            'lead', 'leads', 'لید', 'مشتری', 'customer', 'customers',
        ]);
        $isDealArea = $this->mentionsAny($normalizedScreen . ' ' . $message, [
            'deal', 'deals', 'معامله', 'فروش', 'pipeline',
        ]);
        $isPerformanceArea = $this->mentionsAny($normalizedScreen . ' ' . $message, [
            'dashboard', 'performance', 'kpi', 'داشبورد', 'عملکرد', 'شاخص',
        ]);
        $isPaymentArea = $this->mentionsAny($normalizedScreen . ' ' . $message, [
            'payment', 'payments', 'transaction', 'پرداخت', 'واریز', 'چک', 'مانده',
        ]);

        if (($isLeadArea || $isPerformanceArea) && !$needsFollowUp) {
            $context['lead_summary'] = $this->leadSummary($leadFilters);
            $context['visible_leads_sample'] = $this->visibleLeadsSample($leadFilters, 12);
            $this->addSourceRef($context, 'persons', 'rbac_scoped_leads');
        }

        if ($needsFollowUp) {
            $todayContext = $this->safeFollowUpLeads('today', 25);
            $context['follow_up_context'] = [
                'definition' => 'Open activities joined to their lead/customer. Today is included by default; overdue is included only when explicitly requested. Results are limited for chat latency; has_more indicates there are more records after the returned rows.',
                'today' => $todayContext,
            ];
            if ($needsOverdue) {
                $context['follow_up_context']['overdue'] = $this->safeFollowUpLeads('overdue', 10);
            }
            $this->addSourceRef($context, 'activities', 'rbac_scoped_follow_ups');
            $this->addSourceRef($context, 'persons', 'rbac_scoped_follow_up_leads');
        }

        if ($isDealArea || $isPerformanceArea) {
            $context['deal_summary'] = $this->dealSummary($dealFilters);
            $context['open_deals_sample'] = $this->openDealsSample($dealFilters, 10);
            $this->addSourceRef($context, 'deals', 'rbac_scoped_deals');
        }

        if ($isPerformanceArea || $isPaymentArea) {
            $context['activity_summary'] = $this->activitySummary($leadFilters);
            $context['payment_summary'] = $this->paymentSummary($dealFilters);
            $this->addSourceRef($context, 'activities', 'rbac_scoped_activity_summary');
            $this->addSourceRef($context, 'transactions', 'rbac_scoped_payment_summary');
        }
    }

    private function visibleLeadsSample(array $filters, int $limit = 12): array
    {
        if (empty($this->db->getTableColumns('persons'))) {
            return [];
        }

        $params = [];
        $where = ['p.didar_contact_id IS NOT NULL'];
        $this->addOwnerWhere($where, $params, 'p.owner_didar_id', $filters);

        $sql = "SELECT p.didar_contact_id, p.customer_code,
                CONCAT(COALESCE(p.first_name,''), ' ', COALESCE(p.last_name,'')) AS name,
                p.owner_didar_id, owner.display_name AS owner_name, p.sale_status,
                p.customer_level, p.requested_services, p.register_time, p.created_at
            FROM persons p
            LEFT JOIN users owner ON owner.didar_user_id = p.owner_didar_id
            WHERE " . implode(' AND ', $where) . "
            ORDER BY COALESCE(p.register_time, p.created_at) DESC
            LIMIT " . max(1, min(25, $limit));
        $stmt = $this->pdo()->prepare($sql);
        $stmt->execute($params);
        return array_map(function ($row) {
            return [
                'didar_contact_id' => (string)($row['didar_contact_id'] ?? ''),
                'customer_code' => (string)($row['customer_code'] ?? ''),
                'name' => trim((string)($row['name'] ?? '')),
                'owner_didar_id' => (string)($row['owner_didar_id'] ?? ''),
                'owner_name' => (string)($row['owner_name'] ?? ''),
                'sale_status' => (string)($row['sale_status'] ?? ''),
                'customer_level' => (string)($row['customer_level'] ?? ''),
                'requested_services' => $this->limit((string)($row['requested_services'] ?? ''), 220),
                'register_time' => (string)($row['register_time'] ?? $row['created_at'] ?? ''),
            ];
        }, $stmt->fetchAll(PDO::FETCH_ASSOC) ?: []);
    }

    private function followUpLeads(string $window, int $limit = 25): array
    {
        if (empty($this->db->getTableColumns('activities')) || empty($this->db->getTableColumns('persons'))) {
            return [
                'window' => $window,
                'date' => date('Y-m-d'),
                'total' => 0,
                'returned' => 0,
                'leads' => [],
            ];
        }

        $today = date('Y-m-d');
        $filters = $this->rbac->applyOwnerScope([], 'leads');
        $ownerScoped = !empty($filters['owner_didar_id']) || !empty($filters['owner_didar_ids']);
        $params = [];
        $activityWhere = [
            'a.contact_didar_id IS NOT NULL',
            'a.due_date IS NOT NULL',
            '(a.is_done = 0 OR a.is_done IS NULL)',
        ];

        if ($window === 'overdue') {
            $activityWhere[] = 'a.due_date < ?';
            $params[] = $today . ' 00:00:00';
        } else {
            $window = 'today';
            $activityWhere[] = 'a.due_date >= ?';
            $params[] = $today . ' 00:00:00';
            $activityWhere[] = 'a.due_date <= ?';
            $params[] = $today . ' 23:59:59';
        }

        if ($ownerScoped) {
            $ownerParams = [];
            $personWhere = ['p.didar_contact_id IS NOT NULL'];
            $this->addOwnerWhere($personWhere, $ownerParams, 'p.owner_didar_id', $filters);
            $params = array_merge($ownerParams, $params);
            $from = "FROM persons p
                STRAIGHT_JOIN activities a FORCE INDEX (idx_act_contact_due_done) ON a.contact_didar_id = p.didar_contact_id
                LEFT JOIN users lead_owner ON lead_owner.didar_user_id = p.owner_didar_id
                LEFT JOIN users activity_owner ON activity_owner.didar_user_id = COALESCE(NULLIF(a.activity_owner_didar_id, ''), a.owner_didar_id)
                WHERE " . implode(' AND ', array_merge($personWhere, $activityWhere));
        } else {
            $from = "FROM activities a FORCE INDEX (idx_act_due_done_contact)
                INNER JOIN persons p ON p.didar_contact_id = a.contact_didar_id
                LEFT JOIN users lead_owner ON lead_owner.didar_user_id = p.owner_didar_id
                LEFT JOIN users activity_owner ON activity_owner.didar_user_id = COALESCE(NULLIF(a.activity_owner_didar_id, ''), a.owner_didar_id)
                WHERE " . implode(' AND ', $activityWhere);
        }

        $limit = max(1, min(50, $limit));
        $sql = "SELECT a.didar_activity_id, a.activity_type_title, a.title AS activity_title,
                a.due_date, a.register_date, a.contact_didar_id,
                p.customer_code,
                CONCAT(COALESCE(p.first_name,''), ' ', COALESCE(p.last_name,'')) AS lead_name,
                p.sale_status, p.requested_services,
                p.owner_didar_id AS lead_owner_didar_id,
                lead_owner.display_name AS lead_owner_name,
                activity_owner.display_name AS activity_owner_name
            " . $from . "
            ORDER BY a.due_date ASC, a.id ASC
            LIMIT " . ($limit + 1);
        $stmt = $this->pdo()->prepare($sql);
        $stmt->execute($params);
        $rawRows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        $hasMore = count($rawRows) > $limit;
        if ($hasMore) {
            $rawRows = array_slice($rawRows, 0, $limit);
        }
        $rows = array_map([$this, 'sanitizeFollowUpLead'], $rawRows);

        return [
            'window' => $window,
            'date' => $today,
            'returned' => count($rows),
            'has_more' => $hasMore,
            'leads' => $rows,
        ];
    }

    private function safeFollowUpLeads(string $window, int $limit = 25): array
    {
        try {
            return $this->followUpLeads($window, $limit);
        } catch (\Throwable $e) {
            Logger::logWarning('AI follow-up context query failed', [
                'window' => $window,
                'error' => $e->getMessage(),
            ]);
            return [
                'window' => $window,
                'date' => date('Y-m-d'),
                'returned' => 0,
                'has_more' => false,
                'unavailable' => true,
                'error' => 'follow_up_context_unavailable',
                'leads' => [],
            ];
        }
    }

    private function openDealsSample(array $filters, int $limit = 10): array
    {
        if (empty($this->db->getTableColumns('deals'))) {
            return [];
        }

        $params = [];
        $where = ["d.didar_deal_id IS NOT NULL", "d.status = 'Pending'"];
        $this->addOwnerWhere($where, $params, 'COALESCE(d.owner_didar_id, p.owner_didar_id)', $filters);

        $sql = "SELECT d.didar_deal_id, d.title, d.status, d.pipeline_stage_title,
                d.service_name, d.payable_amount, d.payment_amount, d.register_time, d.last_update_time,
                p.customer_code,
                CONCAT(COALESCE(p.first_name,''), ' ', COALESCE(p.last_name,'')) AS contact_name,
                owner.display_name AS owner_name
            FROM deals d
            LEFT JOIN persons p ON p.didar_contact_id = d.contact_didar_id
            LEFT JOIN users owner ON owner.didar_user_id = COALESCE(d.owner_didar_id, p.owner_didar_id)
            WHERE " . implode(' AND ', $where) . "
            ORDER BY COALESCE(d.last_update_time, d.register_time, d.created_at) DESC
            LIMIT " . max(1, min(25, $limit));
        $stmt = $this->pdo()->prepare($sql);
        $stmt->execute($params);
        return array_map(function ($row) {
            return [
                'didar_deal_id' => (string)($row['didar_deal_id'] ?? ''),
                'title' => $this->limit((string)($row['title'] ?? ''), 160),
                'status' => (string)($row['status'] ?? ''),
                'pipeline_stage_title' => (string)($row['pipeline_stage_title'] ?? ''),
                'service_name' => (string)($row['service_name'] ?? ''),
                'payable_amount' => (string)($row['payable_amount'] ?? ''),
                'payment_amount' => (string)($row['payment_amount'] ?? ''),
                'register_time' => (string)($row['register_time'] ?? ''),
                'last_update_time' => (string)($row['last_update_time'] ?? ''),
                'customer_code' => (string)($row['customer_code'] ?? ''),
                'contact_name' => trim((string)($row['contact_name'] ?? '')),
                'owner_name' => (string)($row['owner_name'] ?? ''),
            ];
        }, $stmt->fetchAll(PDO::FETCH_ASSOC) ?: []);
    }

    private function sanitizeFollowUpLead(array $row): array
    {
        return [
            'didar_activity_id' => (string)($row['didar_activity_id'] ?? ''),
            'contact_didar_id' => (string)($row['contact_didar_id'] ?? ''),
            'customer_code' => (string)($row['customer_code'] ?? ''),
            'lead_name' => trim((string)($row['lead_name'] ?? '')),
            'sale_status' => (string)($row['sale_status'] ?? ''),
            'requested_services' => $this->limit((string)($row['requested_services'] ?? ''), 220),
            'activity_type_title' => (string)($row['activity_type_title'] ?? ''),
            'activity_title' => $this->limit((string)($row['activity_title'] ?? ''), 180),
            'due_date' => (string)($row['due_date'] ?? ''),
            'register_date' => (string)($row['register_date'] ?? ''),
            'lead_owner_didar_id' => (string)($row['lead_owner_didar_id'] ?? ''),
            'lead_owner_name' => (string)($row['lead_owner_name'] ?? ''),
            'activity_owner_name' => (string)($row['activity_owner_name'] ?? ''),
        ];
    }

    private function mentionsAny(string $haystack, array $needles): bool
    {
        $haystack = strtolower($haystack);
        foreach ($needles as $needle) {
            $needle = strtolower((string)$needle);
            if ($needle !== '' && strpos($haystack, $needle) !== false) {
                return true;
            }
        }
        return false;
    }

    private function addSourceRef(array &$context, string $label, string $recordId): void
    {
        $refs = (array)($context['source_refs'] ?? []);
        foreach ($refs as $ref) {
            if ((string)($ref['label'] ?? '') === $label && (string)($ref['record_id'] ?? '') === $recordId) {
                return;
            }
        }
        $refs[] = ['label' => $label, 'record_id' => $recordId];
        $context['source_refs'] = $refs;
    }

    private function findPerson(string $contactId): ?array
    {
        $stmt = $this->pdo()->prepare("SELECT p.*, owner.display_name AS owner_name
            FROM persons p
            LEFT JOIN users owner ON owner.didar_user_id = p.owner_didar_id
            WHERE p.didar_contact_id = ? OR p.id = ?
            LIMIT 1");
        $stmt->execute([$contactId, ctype_digit($contactId) ? (int)$contactId : 0]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return is_array($row) ? $row : null;
    }

    private function findDeal(string $dealId): ?array
    {
        $stmt = $this->pdo()->prepare("SELECT d.*, p.owner_didar_id AS contact_owner_didar_id,
                CONCAT(COALESCE(p.first_name,''), ' ', COALESCE(p.last_name,'')) AS contact_name,
                p.customer_code, owner.display_name AS owner_name
            FROM deals d
            LEFT JOIN persons p ON p.didar_contact_id = d.contact_didar_id
            LEFT JOIN users owner ON owner.didar_user_id = d.owner_didar_id
            WHERE d.didar_deal_id = ? OR d.id = ?
            LIMIT 1");
        $stmt->execute([$dealId, ctype_digit($dealId) ? (int)$dealId : 0]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return is_array($row) ? $row : null;
    }

    private function recentDealsForContact(string $contactId): array
    {
        $stmt = $this->pdo()->prepare("SELECT didar_deal_id, title, status, pipeline_stage, pipeline_stage_title,
                service_name, payable_amount, payment_amount, is_paid, failure_reason_text, register_time, last_update_time
            FROM deals
            WHERE contact_didar_id = ?
            ORDER BY COALESCE(last_update_time, register_time, created_at) DESC
            LIMIT 6");
        $stmt->execute([$contactId]);
        return array_map([$this, 'sanitizeDeal'], $stmt->fetchAll(PDO::FETCH_ASSOC) ?: []);
    }

    private function recentActivities(string $contactId = '', string $dealId = '', int $limit = 8): array
    {
        $where = [];
        $params = [];
        if ($contactId !== '') {
            $where[] = 'contact_didar_id = ?';
            $params[] = $contactId;
        }
        if ($dealId !== '') {
            $where[] = 'deal_didar_id = ?';
            $params[] = $dealId;
        }
        if (empty($where)) {
            return [];
        }
        $sql = "SELECT activity_type_title, title, note, result_note, is_done, due_date, done_date,
                register_date, direction, call_type, activity_category
            FROM activities
            WHERE " . implode(' OR ', $where) . "
            ORDER BY COALESCE(done_date, due_date, register_date, created_at) DESC
            LIMIT " . max(1, min(20, $limit));
        $stmt = $this->pdo()->prepare($sql);
        $stmt->execute($params);
        return array_map(function ($row) {
            $row['note'] = $this->limit(Sanitizer::cleanNote((string)($row['note'] ?? '')), 500);
            $row['result_note'] = $this->limit(Sanitizer::cleanNote((string)($row['result_note'] ?? '')), 500);
            return $row;
        }, $stmt->fetchAll(PDO::FETCH_ASSOC) ?: []);
    }

    private function recentSupport(string $contactId): array
    {
        if (empty($this->db->getTableColumns('crm_support_logs'))) {
            return [];
        }
        $stmt = $this->pdo()->prepare("SELECT channel, direction, result_note, created_at
            FROM crm_support_logs
            WHERE contact_didar_id = ?
            ORDER BY created_at DESC
            LIMIT 5");
        $stmt->execute([$contactId]);
        return array_map(function ($row) {
            $row['result_note'] = $this->limit(Sanitizer::cleanNote((string)($row['result_note'] ?? '')), 500);
            return $row;
        }, $stmt->fetchAll(PDO::FETCH_ASSOC) ?: []);
    }

    private function paymentsSummaryForDeal(int $dealId): array
    {
        $stmt = $this->pdo()->prepare("SELECT COUNT(*) AS payment_count,
                COALESCE(SUM(amount), 0) AS total_amount,
                MAX(payment_date) AS last_payment_date,
                SUM(CASE WHEN status = 'confirmed' THEN 1 ELSE 0 END) AS confirmed_count,
                SUM(CASE WHEN check_status = 'pending' THEN 1 ELSE 0 END) AS pending_cheques
            FROM transactions
            WHERE deal_id = ?");
        $stmt->execute([$dealId]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return is_array($row) ? $row : [];
    }

    private function leadSummary(array $filters): array
    {
        $params = [];
        $where = ['p.didar_contact_id IS NOT NULL'];
        $this->addOwnerWhere($where, $params, 'p.owner_didar_id', $filters);
        $this->addDateWhere($where, $params, 'COALESCE(p.register_time, p.created_at)', $filters);
        $stmt = $this->pdo()->prepare("SELECT COUNT(*) AS total_leads,
                SUM(CASE WHEN p.sale_status = 1 THEN 1 ELSE 0 END) AS sold_leads,
                SUM(CASE WHEN p.owner_didar_id IS NULL OR p.owner_didar_id = '' THEN 1 ELSE 0 END) AS unowned_leads
            FROM persons p WHERE " . implode(' AND ', $where));
        $stmt->execute($params);
        return $stmt->fetch(PDO::FETCH_ASSOC) ?: [];
    }

    private function dealSummary(array $filters): array
    {
        $params = [];
        $where = ['d.didar_deal_id IS NOT NULL'];
        $this->addOwnerWhere($where, $params, 'COALESCE(d.owner_didar_id, p.owner_didar_id)', $filters);
        $this->addDateWhere($where, $params, 'COALESCE(d.register_time, d.created_at)', $filters);
        $stmt = $this->pdo()->prepare("SELECT d.status, COUNT(*) AS deal_count,
                COALESCE(SUM(d.payable_amount), 0) AS payable_amount,
                COALESCE(SUM(d.payment_amount), 0) AS payment_amount
            FROM deals d
            LEFT JOIN persons p ON p.didar_contact_id = d.contact_didar_id
            WHERE " . implode(' AND ', $where) . "
            GROUP BY d.status");
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    private function activitySummary(array $filters): array
    {
        $params = [];
        $where = ['a.didar_activity_id IS NOT NULL'];
        $this->addOwnerWhere($where, $params, 'COALESCE(a.activity_owner_didar_id, a.owner_didar_id, p.owner_didar_id)', $filters);
        $this->addDateWhere($where, $params, 'COALESCE(a.register_date, a.created_at)', $filters);
        $stmt = $this->pdo()->prepare("SELECT COUNT(*) AS activity_count,
                SUM(CASE WHEN a.is_done = 1 THEN 1 ELSE 0 END) AS done_count,
                SUM(CASE WHEN a.is_done = 0 THEN 1 ELSE 0 END) AS open_count
            FROM activities a
            LEFT JOIN persons p ON p.didar_contact_id = a.contact_didar_id
            WHERE " . implode(' AND ', $where));
        $stmt->execute($params);
        return $stmt->fetch(PDO::FETCH_ASSOC) ?: [];
    }

    private function paymentSummary(array $filters): array
    {
        $params = [];
        $where = ['t.id IS NOT NULL'];
        $this->addOwnerWhere($where, $params, 'COALESCE(t.payment_owner_didar_id, d.owner_didar_id, p.owner_didar_id)', $filters);
        $this->addDateWhere($where, $params, 't.payment_date', $filters);
        $stmt = $this->pdo()->prepare("SELECT COUNT(*) AS payment_count,
                COALESCE(SUM(t.amount), 0) AS total_amount,
                SUM(CASE WHEN t.status = 'confirmed' THEN 1 ELSE 0 END) AS confirmed_count,
                SUM(CASE WHEN t.status = 'refunded' THEN 1 ELSE 0 END) AS refunded_count,
                SUM(CASE WHEN t.check_status = 'pending' THEN 1 ELSE 0 END) AS pending_cheques
            FROM transactions t
            INNER JOIN deals d ON d.id = t.deal_id
            LEFT JOIN persons p ON p.didar_contact_id = d.contact_didar_id
            WHERE " . implode(' AND ', $where));
        $stmt->execute($params);
        return $stmt->fetch(PDO::FETCH_ASSOC) ?: [];
    }

    private function sanitizePerson(array $person): array
    {
        if (empty($person)) {
            return [];
        }
        return [
            'didar_contact_id' => (string)($person['didar_contact_id'] ?? ''),
            'customer_code' => (string)($person['customer_code'] ?? ''),
            'name' => trim((string)(($person['first_name'] ?? '') . ' ' . ($person['last_name'] ?? ''))),
            'owner_didar_id' => (string)($person['owner_didar_id'] ?? ''),
            'owner_name' => (string)($person['owner_name'] ?? ''),
            'sale_status' => (string)($person['sale_status'] ?? ''),
            'customer_level' => (string)($person['customer_level'] ?? ''),
            'financial_level' => (string)($person['financial_level'] ?? ''),
            'requested_services' => $this->limit((string)($person['requested_services'] ?? ''), 500),
            'customer_products' => $this->limit((string)($person['customer_products'] ?? ''), 500),
            'acquaintance_source' => (string)($person['acquaintance_source'] ?? ''),
            'register_time' => (string)($person['register_time'] ?? ''),
            'register_time_jalali' => (string)($person['register_time_jalali'] ?? ''),
            'background_info' => $this->limit(Sanitizer::cleanNote((string)($person['background_info'] ?? '')), 700),
        ];
    }

    private function sanitizeDeal(array $deal): array
    {
        return [
            'didar_deal_id' => (string)($deal['didar_deal_id'] ?? ''),
            'title' => $this->limit((string)($deal['title'] ?? ''), 180),
            'status' => (string)($deal['status'] ?? ''),
            'pipeline_stage' => (string)($deal['pipeline_stage'] ?? ''),
            'pipeline_stage_title' => (string)($deal['pipeline_stage_title'] ?? ''),
            'service_name' => (string)($deal['service_name'] ?? ''),
            'payable_amount' => (string)($deal['payable_amount'] ?? ''),
            'payment_amount' => (string)($deal['payment_amount'] ?? ''),
            'is_paid' => (string)($deal['is_paid'] ?? ''),
            'failure_reason_text' => $this->limit((string)($deal['failure_reason_text'] ?? ''), 250),
            'register_time' => (string)($deal['register_time'] ?? ''),
            'last_update_time' => (string)($deal['last_update_time'] ?? ''),
            'contact_didar_id' => (string)($deal['contact_didar_id'] ?? ''),
            'contact_name' => trim((string)($deal['contact_name'] ?? '')),
            'owner_didar_id' => (string)($deal['owner_didar_id'] ?? ''),
            'owner_name' => (string)($deal['owner_name'] ?? ''),
        ];
    }

    private function normalizeKpiFilters(array $filters): array
    {
        $allowed = ['date_from', 'date_to', 'owner_didar_id'];
        $result = [];
        foreach ($allowed as $key) {
            if (isset($filters[$key])) {
                $result[$key] = $this->limit((string)$filters[$key], 80);
            }
        }
        return $result;
    }

    private function addOwnerWhere(array &$where, array &$params, string $expr, array $filters): void
    {
        if (!empty($filters['owner_didar_ids']) && is_array($filters['owner_didar_ids'])) {
            $ids = array_values(array_filter(array_map('strval', $filters['owner_didar_ids'])));
            if (empty($ids)) {
                $where[] = '1 = 0';
                return;
            }
            $where[] = $expr . ' IN (' . implode(',', array_fill(0, count($ids), '?')) . ')';
            $params = array_merge($params, $ids);
            return;
        }
        if (!empty($filters['owner_didar_id'])) {
            $where[] = $expr . ' = ?';
            $params[] = (string)$filters['owner_didar_id'];
        }
    }

    private function addDateWhere(array &$where, array &$params, string $expr, array $filters): void
    {
        if (!empty($filters['date_from'])) {
            $where[] = $expr . ' >= ?';
            $params[] = str_replace('/', '-', (string)$filters['date_from']) . ' 00:00:00';
        }
        if (!empty($filters['date_to'])) {
            $where[] = $expr . ' <= ?';
            $params[] = str_replace('/', '-', (string)$filters['date_to']) . ' 23:59:59';
        }
    }

    private function assertOwnerVisible(string $resourceKey, string $ownerDidarId): void
    {
        $ownerDidarId = trim($ownerDidarId);
        if ($ownerDidarId === '') {
            $scope = $this->rbac->getDataScope($resourceKey);
            if ((string)($scope['scope_type'] ?? 'none') !== 'all') {
                throw new \RuntimeException('Forbidden by RBAC scope');
            }
            return;
        }
        $this->rbac->assertRecordInScope($resourceKey, $ownerDidarId);
    }

    private function limit(string $value, int $length): string
    {
        $value = trim($value);
        if (function_exists('mb_substr')) {
            return mb_substr($value, 0, $length);
        }
        return substr($value, 0, $length);
    }

    private function pdo(): PDO
    {
        return $this->db->getPdo();
    }
}
