<?php
namespace App\Controllers;

use App\Services\LeadService;
use App\Services\VirtualStageService;
use App\Repositories\PersonRepository;
use App\Repositories\ActivityRepository;
use App\Repositories\DealRepository;
use App\Services\AuthService;
use App\Services\AuditService;
use App\Utils\Sanitizer;
use App\Utils\PhoneNormalizer;

class LeadController extends ApiController
{
    private $leadService;
    private $virtualStageService;
    private $personRepo;
    private $activityRepo;
    private $dealRepo;
    private $authService;
    private $auditService;

    private function canRbac(string $capabilityKey): bool
    {
        global $app;
        if (isset($app->services->rbac) && $app->services->rbac) {
            return (bool)$app->services->rbac->can($capabilityKey);
        }
        return false;
    }

    private function requireRbac(string $capabilityKey, string $message = 'دسترسی محدود'): void
    {
        if (!$this->canRbac($capabilityKey)) {
            $this->errorResponse($message, 403);
        }
    }

    private function getRbacScopeForResource(string $resourceKey): ?array
    {
        global $app;
        if (!isset($app->services->rbac) || !$app->services->rbac) {
            return null;
        }
        return (array)$app->services->rbac->getDataScope($resourceKey);
    }

    private function mapSelectedUserIdsToDidarIds(array $selectedUserIds): array
    {
        global $app;
        if (!isset($app->repositories->user) || !$app->repositories->user) {
            return [];
        }
        $didarIds = [];
        foreach ($selectedUserIds as $uid) {
            $userId = (int)$uid;
            if ($userId <= 0) {
                continue;
            }
            try {
                $user = $app->repositories->user->findById($userId);
                $didar = trim((string)($user->didar_user_id ?? ''));
                if ($didar !== '') {
                    $didarIds[$didar] = true;
                }
            } catch (\Throwable $e) {
                // Ignore missing users and continue mapping.
            }
        }
        return array_keys($didarIds);
    }

    private function applyOwnerScopeToFilters(array &$filters, string $resourceKey, string $requestedOwnerDidarId, string $selfDidarId): void
    {
        global $app;
        if (isset($app->services->rbac) && $app->services->rbac) {
            $filters = $app->services->rbac->applyOwnerScope($filters, $resourceKey, $requestedOwnerDidarId);
        }
    }

    private function assertOwnerInScope(string $resourceKey, string $ownerDidarId): void
    {
        global $app;
        if (!isset($app->services->rbac) || !$app->services->rbac || !$app->services->rbac->isOwnerInScope($resourceKey, $ownerDidarId)) {
            $this->errorResponse('دسترسی به این رکورد در محدوده مجاز شما نیست', 403);
        }
    }

    public function __construct(
        LeadService $leadService,
        VirtualStageService $virtualStageService,
        PersonRepository $personRepo,
        ActivityRepository $activityRepo,
        DealRepository $dealRepo,
        AuthService $authService,
        ?AuditService $auditService = null
    ) {
        $this->leadService = $leadService;
        $this->virtualStageService = $virtualStageService;
        $this->personRepo = $personRepo;
        $this->activityRepo = $activityRepo;
        $this->dealRepo = $dealRepo;
        $this->authService = $authService;
        $this->auditService = $auditService;
    }

    public function getLeads()
    {
        try {
            $this->requireRbac('leads.view', 'دسترسی محدود - شما مجاز به مشاهده لیدها نیستید');
            $isAdmin = $this->authService->isAdmin();
            $isAdminOrCrm = $this->authService->isAdminOrCrmSpecialist();
            $didarId = $_SESSION['didar_id'] ?? '';
            $requestedOwnerId = Sanitizer::sanitize($_POST['owner_id'] ?? '');
            
            $filters = [];
            if (!$isAdminOrCrm && !empty($didarId)) {
                $filters['owner_didar_id'] = $didarId;
            }

            // Bucket: normal leads vs "blataklif leads" (flagged in persons.is_blataklif)
            // - normal (default): excludes is_blataklif=1
            // - blataklif: only is_blataklif=1
            // - all: no filter
            $bucket = Sanitizer::sanitize($_POST['bucket'] ?? 'normal');
            if ($bucket === 'blataklif') {
                $filters['blataklif_bucket'] = 'blataklif';
            } elseif ($bucket === 'all') {
                // no filter
            } else {
                $filters['blataklif_bucket'] = 'normal';
            }
            
            if ($isAdminOrCrm && $requestedOwnerId) {
                $filters['owner_didar_id'] = $requestedOwnerId;
            }
            
            $saleStatus = Sanitizer::sanitize($_POST['sale_status'] ?? 'all');
            $search = Sanitizer::sanitize($_POST['search'] ?? '');
            $customerCodeSearch = Sanitizer::sanitize($_POST['customer_code_search'] ?? '');
            $registerFrom = $this->normalizeDateFilter($_POST['register_date_from'] ?? '');
            $registerTo = $this->normalizeDateFilter($_POST['register_date_to'] ?? '');
            $registerFromJalali = $this->normalizeDateFilter($_POST['register_date_from_jalali'] ?? '');
            $registerToJalali = $this->normalizeDateFilter($_POST['register_date_to_jalali'] ?? '');
            $activityFrom = $this->normalizeDateFilter($_POST['activity_date_from'] ?? '');
            $activityTo = $this->normalizeDateFilter($_POST['activity_date_to'] ?? '');
            $productId = Sanitizer::sanitize($_POST['product_id'] ?? '');
            $productName = Sanitizer::sanitize($_POST['product_name'] ?? '');
            $customerLevel = Sanitizer::sanitize($_POST['customer_level'] ?? '');
            
            // Pagination parameters
            $page = isset($_POST['page']) ? (int)$_POST['page'] : 1;
            $perPage = isset($_POST['per_page']) ? (int)$_POST['per_page'] : 50;
            
            // Validate pagination
            if ($page < 1) $page = 1;
            if ($perPage < 10) $perPage = 10;
            if ($perPage > 100) $perPage = 100;
            
            $filters['page'] = $page;
            $filters['per_page'] = $perPage;
            
            if ($saleStatus === 'success') {
                $filters['sale_status'] = 'success';
            } elseif ($saleStatus === 'failed') {
                $filters['sale_status'] = 'failed';
            }
            
            if ($search) {
                $filters['search'] = $search;
            }
            if ($customerCodeSearch) {
                $filters['customer_code_search'] = $customerCodeSearch;
            }

            if (!empty($registerFrom)) {
                $filters['register_date_from'] = $registerFrom;
            }
            if (!empty($registerTo)) {
                $filters['register_date_to'] = $registerTo;
            }
            if (!empty($registerFromJalali)) {
                $filters['register_date_from_jalali'] = $registerFromJalali;
            }
            if (!empty($registerToJalali)) {
                $filters['register_date_to_jalali'] = $registerToJalali;
            }
            if (!empty($activityFrom)) {
                $filters['activity_date_from'] = $activityFrom;
            }
            if (!empty($activityTo)) {
                $filters['activity_date_to'] = $activityTo;
            }
            if (!empty($productId)) {
                $filters['product_id'] = (int)$productId;
            } elseif (!empty($productName)) {
                $filters['product_name'] = $productName;
            }
            if (!empty($customerLevel)) {
                $filters['customer_level'] = $customerLevel;
            }

            // RBAC data scope (self/all/selected_users) overrides legacy owner visibility logic.
            $this->applyOwnerScopeToFilters($filters, 'leads', $requestedOwnerId, (string)$didarId);
            
            // Get total count before applying stage filter
            $totalCount = $this->personRepo->getLeadsCount($filters);
            
            $leads = $this->leadService->getLeads($filters);
            
            $totalPages = ceil($totalCount / $perPage);
            
            $this->successResponse([
                'leads' => array_values($leads),
                'pagination' => [
                    'current_page' => $page,
                    'per_page' => $perPage,
                    'total_count' => $totalCount,
                    'total_pages' => $totalPages,
                    'has_next' => $page < $totalPages,
                    'has_prev' => $page > 1
                ]
            ]);
        } catch (\Exception $e) {
            \App\Utils\Logger::logError("Error in getLeads", $e, [
                'role' => $_SESSION['role'] ?? 'unknown',
                'isAdminOrCrm' => isset($isAdminOrCrm) ? $isAdminOrCrm : false
            ]);
            $this->errorResponse('خطا در بارگذاری لیدها: ' . $e->getMessage(), 500);
        }
    }

    public function backfillCustomerProductsFromRequestedServices()
    {
        $this->requireRbac('leads.backfill_products', 'دسترسی محدود - شما مجاز به اجرای بک‌فیل نیستید');

        try {
            $updatedCount = $this->personRepo->backfillCustomerProductsFromRequestedServices();
            $this->successResponse([
                'updated_count' => $updatedCount,
                'mode' => 'merge_only'
            ], 'بک‌فیل انجام شد (فقط اضافه/همگام‌سازی؛ بدون حذف داده)');
        } catch (\Throwable $e) {
            \App\Utils\Logger::logError('Backfill customer_products from requested_services failed', $e, [
                'user_id' => $_SESSION['user_id'] ?? null,
                'role' => $_SESSION['role'] ?? null
            ]);
            $this->errorResponse('خطا در اجرای بک‌فیل: ' . $e->getMessage(), 500);
        }
    }

    public function lookupMobile()
    {
        $this->requireRbac('leads.lookup_phone', 'دسترسی محدود - شما مجاز به جستجوی شماره نیستید');

        $raw = Sanitizer::sanitize($_POST['mobile_phone'] ?? '');
        if ($raw === '') {
            $this->errorResponse('شماره موبایل الزامی است');
        }

        // Convert Persian/Arabic digits to English digits (so regex and DB match works reliably)
        $raw = strtr($raw, [
            '۰' => '0', '۱' => '1', '۲' => '2', '۳' => '3', '۴' => '4',
            '۵' => '5', '۶' => '6', '۷' => '7', '۸' => '8', '۹' => '9',
            '٠' => '0', '١' => '1', '٢' => '2', '٣' => '3', '٤' => '4',
            '٥' => '5', '٦' => '6', '٧' => '7', '٨' => '8', '٩' => '9',
        ]);

        $digits = preg_replace('/\\D+/', '', $raw);
        if ($digits === '') {
            $this->errorResponse('شماره موبایل معتبر نیست');
        }

        // Accept only 10-digit format (9xxxxxxxxx) without leading zero
        if (strlen($digits) !== 10 || substr($digits, 0, 1) !== '9') {
            $this->errorResponse('شماره موبایل باید دقیقاً 10 رقم باشد و با 9 شروع شود');
        }

        $candidates = PhoneNormalizer::normalizeCandidates($digits);
        $matches = $this->personRepo->findByPhoneCandidatesWithOwners($candidates);
        // This lookup is intentionally broader than normal lead visibility.
        // Users who have the dedicated lookup capability should be able to verify
        // whether a phone exists anywhere in the local DB without gaining profile access.

        $this->successResponse([
            'exists' => !empty($matches),
            'matches' => $matches
        ]);
    }

    public function getProfile()
    {
        $this->requireRbac('leads.profile.view', 'دسترسی محدود - شما مجاز به مشاهده پروفایل لید نیستید');

        $contactId = Sanitizer::sanitize($_POST['contact_id'] ?? '');

        if (empty($contactId)) {
            $this->errorResponse('شناسه شخص الزامی است');
        }

        // Prefer didar_contact_id to avoid numeric ID collisions, then fallback to local id
        $person = $this->personRepo->findByDidarId($contactId);
        if (!$person) {
            $person = $this->personRepo->findByIdOrDidarId($contactId);
        }

        if (!$person) {
            $this->errorResponse('شخص یافت نشد');
        }
        $this->assertOwnerInScope('leads', (string)($person->owner_didar_id ?? ''));
        
        // Get virtual stage: first try to get from virtual_stages table, then calculate if not found
        // Get the most recent stage with priority: active stage > special stage
        $virtualStageRepo = $this->virtualStageService->getRepository();
        $existingStage = $virtualStageRepo->getActiveStage($contactId);
        
        if (!$existingStage) {
            // If no active stage, check for special stage
            $existingStage = $virtualStageRepo->getSpecialStage($contactId);
        }
        
        if ($existingStage) {
            // Use existing stage from database (it was set manually)
            $virtualStage = $existingStage;
        } else {
            // Calculate virtual stage if no existing stage found
            $virtualStage = $this->virtualStageService->calculate($contactId);
        }
        
        $personArray = $person->toArray();
        $personArray['customer_code'] = (string)($personArray['customer_code'] ?? '');
        $personArray['virtual_stage'] = $virtualStage;
        $personArray['owner_name'] = $this->getUserNameByDidarId($person->owner_didar_id);
        $personArray['previous_owner_name'] = $this->getUserNameByDidarId($person->previous_owner_history ?? $person->previous_owner_id);
        $personArray['sale_status'] = isset($personArray['sale_status']) ? (int)$personArray['sale_status'] : 0;
        
        $dealLimit = isset($_POST['deal_limit']) ? (int)$_POST['deal_limit'] : 50;
        if ($dealLimit <= 0) {
            $dealLimit = 50;
        }
        $activityLimit = isset($_POST['activity_limit']) ? (int)$_POST['activity_limit'] : 50;
        if ($activityLimit <= 0) {
            $activityLimit = 50;
        }

        // Get current and legacy deals using the standardized Didar GUID
        $deals = $this->dealRepo->findByContactIdLegacy($person->didar_contact_id, false, $dealLimit);
        $dealsArray = array_map(function($deal) {
            return $deal->toArray();
        }, $deals);

        $legacyDeals = $this->dealRepo->findByContactIdLegacy($person->didar_contact_id, true, $dealLimit);
        $legacyDealsArray = array_map(function($deal) {
            return $deal->toArray();
        }, $legacyDeals);
        
        // Get activities using the standardized Didar GUID
        $activities = $this->activityRepo->findByContactId($person->didar_contact_id, $activityLimit);
        $activitiesArray = array_map(function($act) {
            return $act->toArray();
        }, $activities);
        
        $this->successResponse([
            'person' => $personArray,
            'deals' => $dealsArray,
            'legacy_deals' => $legacyDealsArray,
            'activities' => $activitiesArray
        ]);
    }

    public function create()
    {
        $this->requireRbac('leads.create', 'دسترسی محدود - شما مجاز به ایجاد لید نیستید');
        
        $allowPreviousOwner = $this->authService->isAdmin()
            || (method_exists($this->authService, 'isSystemRole') && $this->authService->isSystemRole());
        $result = $this->leadService->createLead($_POST, $allowPreviousOwner);
        
        if ($result['is_duplicate']) {
            // For duplicate contacts, return the existing contact information
            $person = $result['person'];
            
            $response = [
                'is_duplicate' => true,
                'contact_id' => $person->id, // ALWAYS use local primary key 'id' for UI consistency
                'didar_contact_id' => $person->didar_contact_id, 
                'deal_id' => null,
                'person' => $person,
                'contact_name' => trim(($person->first_name ?? '') . ' ' . ($person->last_name ?? '')),
                'matched_phone' => $result['matched_phone'],
                'owner_name' => $result['owner_name'] ?: $result['owner_id'],
                'owner_id' => $result['owner_id'],
                'block_reason' => $result['block_reason'] ?? 'DUPLICATE_PHONE_PREVENTION'
            ];
            
            \App\Utils\Logger::logInfo("Duplicate lead response prepared using local ID", [
                'local_id' => $person->id,
                'didar_id' => $person->didar_contact_id
            ]);
            
            $this->successResponse($response, 'این شماره قبلاً در سیستم وجود دارد. از لید موجود استفاده می‌شود.');
        } else {
            $this->successResponse($result, 'لید با موفقیت ایجاد شد');
        }
    }

    public function update()
    {
        $this->requireRbac('leads.edit', 'دسترسی محدود - شما مجاز به ویرایش لید نیستید');
        
        $contactId = Sanitizer::sanitize($_POST['contact_id'] ?? '');

        if (empty($contactId)) {
            $this->errorResponse('شناسه لید الزامی است');
        }

        // Debug logging for CRM transfer fields
        \App\Utils\Logger::logInfo("Lead update received", [
            'contact_id' => $contactId,
            'has_previous_purchase' => $_POST['has_previous_purchase'] ?? 'not_set',
            'previous_purchase_number' => $_POST['previous_purchase_number'] ?? 'not_set',
            'activity_has_purchase' => $_POST['activity_has_purchase'] ?? 'not_set',
            'activity_previous_number' => $_POST['activity_previous_number'] ?? 'not_set'
        ]);

        // Convert activity fields to standard fields for CRM transfer logic
        if (isset($_POST['activity_has_purchase']) && !isset($_POST['has_previous_purchase'])) {
            $_POST['has_previous_purchase'] = $_POST['activity_has_purchase'];
        }
        if (isset($_POST['activity_previous_number']) && !isset($_POST['previous_purchase_number'])) {
            $_POST['previous_purchase_number'] = $_POST['activity_previous_number'];
        }

        // اگر full_name ارسال شده باشد، به first_name و last_name تقسیم کن
        if (!empty($_POST['full_name'])) {
            $fullName = Sanitizer::sanitize($_POST['full_name']);
            $parts = explode(' ', trim($fullName), 2);
            $_POST['first_name'] = $parts[0] ?? '';
            $_POST['last_name'] = $parts[1] ?? ($_POST['first_name'] ?? '');
        }

        // In lead edit flow, `contact_id` is expected to be didar_contact_id from UI.
        // Resolve by Didar ID first; fallback to local id only if needed.
        $personBefore = $this->personRepo->findByDidarId($contactId);
        if (!$personBefore) {
            $personBefore = $this->personRepo->findById($contactId);
        }

        if (!$personBefore) {
            $this->errorResponse('لید یافت نشد');
        }
        $this->assertOwnerInScope('leads', (string)($personBefore->owner_didar_id ?? ''));

        // Standardize to the real Didar GUID for the service call
        $standardDidarId = $personBefore->didar_contact_id;
        $oldOwnerId = $personBefore->owner_didar_id ?? null;
        $personName = trim(($personBefore->first_name ?? '') . ' ' . ($personBefore->last_name ?? ''));

        $allowPreviousOwner = $this->authService->isAdmin()
            || (method_exists($this->authService, 'isSystemRole') && $this->authService->isSystemRole())
            || $this->canRbac('leads.transfer_owner');
        $allowCustomerCodeEdit = $this->authService->isAdmin();
        $result = $this->leadService->updateLead($standardDidarId, $_POST, $allowPreviousOwner, $allowCustomerCodeEdit);

        if ($this->auditService && !empty($result['owner_change'])) {
            $newOwnerId = $result['owner_change']['new_owner_id'] ?? ($_POST['owner_didar_id'] ?? null);
            $oldName = $this->getUserNameByDidarId($result['owner_change']['old_owner_id'] ?? $oldOwnerId);
            $newName = $this->getUserNameByDidarId($newOwnerId);
            $this->auditService->log('owner_change', 'person', $standardDidarId, [
                'contact_id' => $standardDidarId,
                'person_name' => $personName,
                'old_owner_id' => $result['owner_change']['old_owner_id'] ?? $oldOwnerId,
                'old_owner_name' => $oldName,
                'new_owner_id' => $newOwnerId,
                'new_owner_name' => $newName,
            ]);
        }
        $this->successResponse($result, 'لید با موفقیت به‌روزرسانی شد');
    }

    /**
     * انتقال مسئول لید و معاملات مرتبط
     */
    public function transferOwner()
    {
        $this->requireRbac('leads.transfer_owner', 'دسترسی محدود - شما مجاز به تغییر مسئول لید نیستید');

        $contactId = Sanitizer::sanitize($_POST['contact_id'] ?? '');
        $newOwnerId = Sanitizer::sanitize($_POST['new_owner_id'] ?? '');
        if ($newOwnerId === 'undefined') {
            $newOwnerId = '';
        }

        if (empty($contactId) || empty($newOwnerId)) {
            $this->errorResponse('شناسه لید و مسئول جدید الزامی است', 200);
        }

        // Use the robust repository method to find the person
        $person = $this->personRepo->findByIdOrDidarId($contactId);

        if (!$person) {
            $this->errorResponse('لید یافت نشد', 200);
        }
        $this->assertOwnerInScope('leads', (string)($person->owner_didar_id ?? ''));

        $standardDidarId = $person->didar_contact_id;

        try {
            $result = $this->leadService->transferLeadOwner($standardDidarId, $newOwnerId);
            if ($this->auditService) {
                $personName = trim(($person->first_name ?? '') . ' ' . ($person->last_name ?? ''));
                $this->auditService->log('owner_change', 'person', $standardDidarId, [
                    'contact_id' => $standardDidarId,
                    'person_name' => $personName,
                    'old_owner_id' => $result['old_owner_id'] ?? null,
                    'old_owner_name' => $result['old_owner_name'] ?? $this->getUserNameByDidarId($result['old_owner_id'] ?? ''),
                    'new_owner_id' => $result['new_owner_id'] ?? null,
                    'new_owner_name' => $result['new_owner_name'] ?? $this->getUserNameByDidarId($result['new_owner_id'] ?? ''),
                ]);
            }
            $this->successResponse($result, $result['message'] ?? 'مسئول لید تغییر کرد');
        } catch (\Exception $e) {
            \App\Utils\Logger::logError("Error in transferOwner", $e, [
                'contact_id' => $standardDidarId,
                'new_owner_id' => $newOwnerId
            ]);
            $this->errorResponse('خطا در تغییر مسئول لید: ' . $e->getMessage(), 200);
        }
    }

    public function delete()
    {
        $this->requireRbac('leads.delete', 'دسترسی محدود - شما مجاز به حذف لید نیستید');

        $contactId = Sanitizer::sanitize($_POST['contact_id'] ?? '');
        if ($contactId === '') {
            $this->errorResponse('شناسه لید الزامی است');
        }

        // Use the robust repository method to find the person
        $person = $this->personRepo->findByIdOrDidarId($contactId);

        if (!$person) {
            $this->errorResponse('لید یافت نشد');
        }
        $this->assertOwnerInScope('leads', (string)($person->owner_didar_id ?? ''));

        $standardDidarId = $person->didar_contact_id;

        if ($this->auditService) {
            $cascadeSnapshot = $this->buildLeadCascadeSnapshot($standardDidarId);
            $this->auditService->log('delete', 'person', $standardDidarId, [
                'person' => $person->toArray(),
                'person_name' => trim(($person->first_name ?? '') . ' ' . ($person->last_name ?? '')),
                'deals' => $cascadeSnapshot['deals'],
                'activities' => $cascadeSnapshot['activities'],
                'transactions' => $cascadeSnapshot['transactions'],
            ]);
        }

        $result = $this->leadService->deleteLeadCascade($standardDidarId);
        $this->successResponse($result, 'لید و اطلاعات مرتبط با موفقیت حذف شد');
    }

    private function buildLeadCascadeSnapshot(string $contactDidarId): array
    {
        $snapshot = [
            'deals' => [],
            'activities' => [],
            'transactions' => [],
        ];

        if ($contactDidarId === '') {
            return $snapshot;
        }

        try {
            $pdo = $this->personRepo->getDb()->getPdo();

            $dealsStmt = $pdo->prepare("SELECT * FROM deals WHERE contact_didar_id = ?");
            $dealsStmt->execute([$contactDidarId]);
            $deals = $dealsStmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];
            $snapshot['deals'] = $deals;

            $activitiesStmt = $pdo->prepare("SELECT * FROM activities WHERE contact_didar_id = ?");
            $activitiesStmt->execute([$contactDidarId]);
            $snapshot['activities'] = $activitiesStmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];

            $dealInternalIds = [];
            foreach ($deals as $dealRow) {
                $internalId = isset($dealRow['id']) ? (int)$dealRow['id'] : 0;
                if ($internalId > 0) {
                    $dealInternalIds[] = $internalId;
                }
            }

            if (!empty($dealInternalIds)) {
                $placeholders = implode(',', array_fill(0, count($dealInternalIds), '?'));
                $txStmt = $pdo->prepare("SELECT * FROM transactions WHERE deal_id IN ($placeholders)");
                $txStmt->execute($dealInternalIds);
                $snapshot['transactions'] = $txStmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];
            }
        } catch (\Throwable $e) {
            \App\Utils\Logger::logWarning('Failed to build lead cascade snapshot before delete', [
                'contact_didar_id' => $contactDidarId,
                'error' => $e->getMessage(),
            ]);
        }

        return $snapshot;
    }

    /**
     * Get referrals list
     */
    public function getReferrals()
    {
        if (!$this->authService->isAdminOrCrmSpecialist()) {
            $this->errorResponse('دسترسی محدود');
        }

        try {
            $db = $this->leadService->getPersonRepo()->getDb();
            $pdo = $db->getPdo();

            $stmt = $pdo->prepare("
                SELECT
                    r.*,
                    p.first_name,
                    p.last_name,
                    p.mobile_phone,
                    p.secondary_mobile_phone,
                    ru.display_name as referrer_name,
                    cu.display_name as crm_specialist_name
                FROM referrals r
                LEFT JOIN persons p ON r.contact_didar_id = p.didar_contact_id
                LEFT JOIN users ru ON r.referrer_didar_id = ru.didar_user_id
                LEFT JOIN users cu ON cu.role = 'crm_specialist' AND cu.is_active = 1
                ORDER BY r.created_at DESC
            ");
            $stmt->execute();
            $referrals = $stmt->fetchAll(\PDO::FETCH_ASSOC);

            $this->successResponse(['referrals' => $referrals]);
        } catch (\Exception $e) {
            \App\Utils\Logger::logError("Failed to get referrals", $e);
            $this->errorResponse('خطا در دریافت لیست ارجاعات');
        }
    }

    private function getUserNameByDidarId($didarId): string
    {
        if (empty($didarId)) {
            return '';
        }
        $db = $this->personRepo->getDb();
        $pdo = $db->getPdo();
        $hasDisplayName = $db->hasColumn('users', 'display_name');
        $field = $hasDisplayName ? 'display_name' : "CONCAT(first_name, ' ', last_name)";
        $raw = trim((string)$didarId);
        $ids = array_values(array_filter(array_map('trim', explode(',', $raw)), static function ($value) {
            return $value !== '';
        }));
        if (empty($ids)) {
            return '';
        }
        if (count($ids) === 1) {
            $stmt = $pdo->prepare("SELECT $field as name FROM users WHERE didar_user_id = ? LIMIT 1");
            $stmt->execute([$ids[0]]);
            $name = $stmt->fetchColumn();
            return $name ?: '';
        }

        $placeholders = implode(',', array_fill(0, count($ids), '?'));
        $stmt = $pdo->prepare("SELECT didar_user_id, $field as name FROM users WHERE didar_user_id IN ($placeholders)");
        $stmt->execute($ids);
        $nameMap = [];
        foreach (($stmt->fetchAll(\PDO::FETCH_ASSOC) ?: []) as $row) {
            $nameMap[(string)($row['didar_user_id'] ?? '')] = trim((string)($row['name'] ?? ''));
        }
        $names = [];
        foreach ($ids as $id) {
            if (!empty($nameMap[$id])) {
                $names[] = $nameMap[$id];
            }
        }
        return implode(' ← ', $names);
    }

    private function normalizeDateFilter($value): string
    {
        $clean = Sanitizer::sanitize($value ?? '');
        $clean = strtr($clean, [
            '۰' => '0', '۱' => '1', '۲' => '2', '۳' => '3', '۴' => '4',
            '۵' => '5', '۶' => '6', '۷' => '7', '۸' => '8', '۹' => '9',
            '٠' => '0', '١' => '1', '٢' => '2', '٣' => '3', '٤' => '4',
            '٥' => '5', '٦' => '6', '٧' => '7', '٨' => '8', '٩' => '9',
        ]);
        $clean = str_replace('-', '/', $clean);
        if ($clean === '') {
            return '';
        }
        if (!preg_match('/^\d{4}\/\d{1,2}\/\d{1,2}$/', $clean)) {
            return '';
        }
        return $clean;
    }
}
