<?php
namespace App\Controllers;

use App\Services\AuthService;
use App\Services\LeadService;
use App\Repositories\PersonRepository;
use App\Repositories\UserRepository;
use App\Utils\PhoneNormalizer;
use App\Utils\Validator;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Cell\DataValidation;

class ImportController extends ApiController
{
    private AuthService $authService;
    private LeadService $leadService;
    private PersonRepository $personRepo;
    private UserRepository $userRepo;

    public function __construct(
        AuthService $authService,
        LeadService $leadService,
        PersonRepository $personRepo,
        UserRepository $userRepo
    ) {
        $this->authService = $authService;
        $this->leadService = $leadService;
        $this->personRepo = $personRepo;
        $this->userRepo = $userRepo;
    }

    public function downloadLeadsImportTemplate()
    {
        if (!$this->authService->isAdmin() && !$this->hasDelegatedCapability('imports.admin.leads')) {
            $this->errorResponse('فقط مدیر می‌تواند فایل نمونه را دانلود کند');
            return;
        }

        $headers = [
            'شماره موبایل',
            'نام کاربری مسئول',
            'نام و نام خانوادگی',
            'شهر',
            'شیوه آشنایی',
            'جزئیات شیوه آشنایی',
            'موضوع محتوا'
        ];
        $sample = [
            '9123456789',
            $this->firstActiveUserImportIdentifier(),
            'نمونه کاربر',
            'تهران',
            $this->firstLookupTitle('lead_sources'),
            $this->firstLookupTitle('lead_source_details'),
            $this->firstLookupTitle('content_topics')
        ];

        if (!class_exists('\PhpOffice\PhpSpreadsheet\Spreadsheet') || !class_exists('\PhpOffice\PhpSpreadsheet\Writer\Xlsx')) {
            $this->downloadCsvTemplate($headers, $sample);
            return;
        }

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('leads');
        $sheet->fromArray($headers, null, 'A1');
        $sheet->fromArray($sample, null, 'A2');
        foreach (range('A', 'G') as $col) {
            $sheet->getColumnDimension($col)->setWidth($col === 'C' ? 28 : 22);
        }
        $sheet->freezePane('A2');
        $this->addLookupDropdowns($spreadsheet, $sheet);

        $filename = 'lead_import_template.xlsx';
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        header('Cache-Control: max-age=0');
        (new Xlsx($spreadsheet))->save('php://output');
    }

    private function downloadCsvTemplate(array $headers, array $sample): void
    {
        $filename = 'lead_import_template.csv';
        header('Content-Type: text/csv; charset=UTF-8');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        echo "\xEF\xBB\xBF";
        echo $this->csvLine($headers) . "\n";
        echo $this->csvLine($sample) . "\n";
    }

    private function csvLine(array $cells): string
    {
        return implode(',', array_map(static function ($cell) {
            $value = (string)$cell;
            if (strpos($value, ',') !== false || strpos($value, '"') !== false || strpos($value, "\n") !== false) {
                return '"' . str_replace('"', '""', $value) . '"';
            }
            return $value;
        }, $cells));
    }

    /**
     * پیش‌نمایش فایل ایمپورت لید
     */
    public function importLeadsPreview()
    {
        if (!$this->authService->isAdmin() && !$this->hasDelegatedCapability('imports.admin.leads')) {
            $this->errorResponse('فقط مدیر می‌تواند لیدها را وارد کند');
            return;
        }

        try {
            [$rows, $headerMap] = $this->loadImportRowsFromUpload();
            $preview = [];
            $validCount = 0;
            foreach ($rows as $idx => $row) {
                $rowNumber = $idx + 2;
                if ($this->isRowEmpty($row)) {
                    continue;
                }
                $rowData = $this->extractImportRow($row, $headerMap, $rowNumber);
                $errors = $this->validateImportRow($rowData);
                if (empty($errors)) $validCount++;
                $rowData['errors'] = $errors;
                $preview[] = $rowData;
            }

            $this->successResponse([
                'preview' => $preview,
                'valid_count' => $validCount,
                'error_count' => max(0, count($preview) - $validCount)
            ]);
        } catch (\Throwable $e) {
            $this->errorResponse('خطا در پردازش فایل: ' . $e->getMessage());
        }
    }

    /**
     * اجرای واقعی ایمپورت لید/معامله
     */
    public function importLeadsExecute()
    {
        if (!$this->authService->isAdmin() && !$this->hasDelegatedCapability('imports.admin.leads')) {
            $this->errorResponse('فقط مدیر می‌تواند لیدها را وارد کند');
            return;
        }

        try {
            [$rows, $headerMap] = $this->loadImportRowsFromUpload();
            $leadCreatedCount = 0;
            $dealCreatedCount = 0;
            $skippedCount = 0;
            $results = [];
            foreach ($rows as $idx => $row) {
                $rowNumber = $idx + 2;
                if ($this->isRowEmpty($row)) continue;

                $rowData = $this->extractImportRow($row, $headerMap, $rowNumber);
                $errors = $this->validateImportRow($rowData);
                if (!empty($errors)) {
                    $skippedCount++;
                    $results[] = $this->buildImportResult($rowData, 'fail', 'skip', implode(' | ', $errors));
                    continue;
                }

                $owner = $this->resolveOwnerByUsername($rowData['owner_username']);
                if (!$owner || empty($owner->didar_user_id)) {
                    $skippedCount++;
                    $results[] = $this->buildImportResult($rowData, 'fail', 'skip', "مسئول با username '{$rowData['owner_username']}' یافت نشد");
                    continue;
                }

                $ownerDidarId = (string)$owner->didar_user_id;
                $ownerDisplay = trim((string)($owner->display_name ?? ($owner->first_name . ' ' . $owner->last_name)));

                $existingPerson = $this->personRepo->findByPhone(PhoneNormalizer::normalizeCandidates($rowData['mobile_phone']));
                if ($existingPerson) {
                    $existingOwnerDidarId = (string)($existingPerson->owner_didar_id ?? '');
                    if ($existingOwnerDidarId !== $ownerDidarId) {
                        $skippedCount++;
                        $existingOwnerName = $this->resolveOwnerName($existingOwnerDidarId);
                        $results[] = $this->buildImportResult(
                            $rowData,
                            'fail',
                            'skip',
                            "شماره قبلاً ثبت شده و متعلق به کاربر دیگری است ({$existingOwnerName})",
                            $ownerDisplay
                        );
                        continue;
                    }

                    $deal = $this->leadService->createAdditionalDealForContact(
                        (string)$existingPerson->didar_contact_id,
                        $ownerDidarId,
                        '',
                        $this->buildDealImportExtra($rowData)
                    );
                    if ($deal) {
                        $dealCreatedCount++;
                        $results[] = $this->buildImportResult(
                            $rowData,
                            'success',
                            'deal_created',
                            'لید قبلاً وجود داشت؛ معامله جدید ایجاد شد',
                            $ownerDisplay,
                            [
                                'didar_contact_id' => (string)($existingPerson->didar_contact_id ?? ''),
                                'didar_deal_id' => (string)($deal->didar_deal_id ?? '')
                            ]
                        );
                    } else {
                        $skippedCount++;
                        $results[] = $this->buildImportResult(
                            $rowData,
                            'fail',
                            'skip',
                            'لید تکراری بود ولی ایجاد معامله جدید ناموفق شد',
                            $ownerDisplay
                        );
                    }
                    continue;
                }

                $leadPayload = [
                    'full_name' => $rowData['full_name'] !== '' ? $rowData['full_name'] : 'لید جدید',
                    'mobile_phone' => $rowData['mobile_phone'],
                    'owner_didar_id' => $ownerDidarId,
                    'city' => $rowData['city'],
                    'acquaintance_source' => $rowData['acquaintance_source'],
                    'acquaintance_detail' => $rowData['acquaintance_detail'],
                    'content_topic' => $rowData['content_topic'],
                    'created_by_didar_id' => $this->currentCreatorDidarId(),
                    'created_by_name' => $this->currentCreatorName(),
                    'created_via' => 'excel'
                ];

                try {
                    $created = $this->leadService->createLead($leadPayload, false);
                    if (!empty($created['is_duplicate'])) {
                        $duplicateOwnerId = (string)($created['owner_id'] ?? '');
                        if ($duplicateOwnerId !== '' && $duplicateOwnerId === $ownerDidarId) {
                            $deal = $this->leadService->createAdditionalDealForContact(
                                (string)($created['didar_contact_id'] ?? ''),
                                $ownerDidarId,
                                '',
                                $this->buildDealImportExtra($rowData)
                            );
                            if ($deal) {
                                $dealCreatedCount++;
                                $results[] = $this->buildImportResult(
                                    $rowData,
                                    'success',
                                    'deal_created',
                                    'لید تکراری (همان مسئول)؛ معامله جدید ایجاد شد',
                                    $ownerDisplay,
                                    [
                                        'didar_contact_id' => (string)($created['didar_contact_id'] ?? ''),
                                        'didar_deal_id' => (string)($deal->didar_deal_id ?? '')
                                    ]
                                );
                            } else {
                                $skippedCount++;
                                $results[] = $this->buildImportResult($rowData, 'fail', 'skip', 'تشخیص تکراری انجام شد ولی ساخت معامله ناموفق بود', $ownerDisplay);
                            }
                        } else {
                            $skippedCount++;
                            $existingOwnerName = $this->resolveOwnerName($duplicateOwnerId);
                            $results[] = $this->buildImportResult($rowData, 'fail', 'skip', "شماره تکراری و متعلق به کاربر دیگر است ({$existingOwnerName})", $ownerDisplay);
                        }
                        continue;
                    }

                    $leadCreatedCount++;
                    $results[] = $this->buildImportResult(
                        $rowData,
                        'success',
                        'lead_created',
                        'لید جدید با موفقیت ثبت شد',
                        $ownerDisplay,
                        [
                            'didar_contact_id' => (string)($created['didar_contact_id'] ?? ''),
                            'didar_deal_id' => (string)($created['deal_id'] ?? '')
                        ]
                    );
                } catch (\Throwable $e) {
                    $skippedCount++;
                    $results[] = $this->buildImportResult($rowData, 'fail', 'skip', 'خطا: ' . $e->getMessage(), $ownerDisplay);
                }
            }

            $this->successResponse([
                'lead_created_count' => $leadCreatedCount,
                'deal_created_count' => $dealCreatedCount,
                'skipped_count' => $skippedCount,
                'success_count' => $leadCreatedCount + $dealCreatedCount,
                'failure_count' => $skippedCount,
                'results' => $results
            ]);
        } catch (\Throwable $e) {
            $this->errorResponse('خطا: ' . $e->getMessage());
        }
    }

    private function loadImportRowsFromUpload(): array
    {
        if (!isset($_FILES['file']) || $_FILES['file']['error'] !== UPLOAD_ERR_OK) {
            throw new \RuntimeException('فایلی آپلود نشده است');
        }

        $file = $_FILES['file'];
        $allowedExtensions = ['xlsx', 'xls', 'csv'];
        $fileExtension = strtolower(pathinfo((string)$file['name'], PATHINFO_EXTENSION));
        if (!in_array($fileExtension, $allowedExtensions, true)) {
            throw new \RuntimeException('فقط فایل‌های Excel یا CSV قابل پذیرش هستند');
        }
        if ((int)$file['size'] > 5 * 1024 * 1024) {
            throw new \RuntimeException('حجم فایل نباید بیشتر از 5 مگابایت باشد');
        }

        $rows = [];
        if ($fileExtension === 'csv') {
            $rows = $this->readCsvRows((string)$file['tmp_name']);
        } else {
            if (!class_exists('\PhpOffice\PhpSpreadsheet\IOFactory')) {
                throw new \RuntimeException('کتابخانه Excel روی سرور نصب نیست. لطفاً فایل CSV آپلود کنید.');
            }
            $spreadsheet = IOFactory::load((string)$file['tmp_name']);
            $worksheet = $spreadsheet->getActiveSheet();
            $rows = $worksheet->toArray('', true, true, false);
        }
        if (empty($rows)) {
            throw new \RuntimeException('فایل خالی است');
        }

        $header = array_map(static function ($value) {
            return trim((string)$value);
        }, (array)($rows[0] ?? []));

        $mobileAliases = ['mobile_phone', 'mobile', 'phone', 'شماره موبایل', 'شماره', 'موبایل', 'شماره تماس'];
        $ownerAliases = ['owner_username', 'username', 'owner', 'نام کاربری مسئول', 'یوزرنیم مسئول', 'مسئول'];

        $mobileIdx = $this->findHeaderIndex($header, $mobileAliases);
        $ownerIdx = $this->findHeaderIndex($header, $ownerAliases);
        if ($mobileIdx < 0 || $ownerIdx < 0) {
            throw new \RuntimeException('ستون‌های الزامی یافت نشد. ستون‌های الزامی: mobile_phone و owner_username');
        }

        $headerMap = [
            'mobile_phone' => $mobileIdx,
            'owner_username' => $ownerIdx,
            'full_name' => $this->findHeaderIndex($header, ['full_name', 'name', 'نام و نام خانوادگی', 'نام']),
            'city' => $this->findHeaderIndex($header, ['city', 'شهر']),
            'acquaintance_source' => $this->findHeaderIndex($header, ['acquaintance_source', 'source', 'شیوه آشنایی']),
            'acquaintance_detail' => $this->findHeaderIndex($header, ['acquaintance_detail', 'source_detail', 'جزئیات شیوه آشنایی']),
            'content_topic' => $this->findHeaderIndex($header, ['content_topic', 'topic', 'موضوع محتوا'])
        ];

        return [array_slice($rows, 1), $headerMap];
    }

    private function readCsvRows(string $path): array
    {
        $lines = @file($path, FILE_IGNORE_NEW_LINES);
        if ($lines === false || empty($lines)) {
            throw new \RuntimeException('امکان خواندن فایل CSV وجود ندارد');
        }

        $normalizeLine = static function (string $line): string {
            $line = preg_replace('/^\xEF\xBB\xBF/u', '', $line);
            if (function_exists('mb_detect_encoding')) {
                $preferred = ['UTF-8', 'UTF-16LE', 'UTF-16BE', 'CP1256', 'Windows-1252', 'ISO-8859-1'];
                $available = function_exists('mb_list_encodings') ? (array)mb_list_encodings() : [];
                $preferredLower = array_map('strtolower', $preferred);
                $availableMap = [];
                foreach ($available as $encName) {
                    $availableMap[strtolower((string)$encName)] = (string)$encName;
                }
                $usable = [];
                foreach ($preferredLower as $encLower) {
                    if (isset($availableMap[$encLower])) {
                        $usable[] = $availableMap[$encLower];
                    }
                }
                if (!empty($usable)) {
                    $enc = mb_detect_encoding($line, $usable, true);
                    if ($enc && strtoupper($enc) !== 'UTF-8') {
                        $converted = @mb_convert_encoding($line, 'UTF-8', $enc);
                        if (is_string($converted) && $converted !== '') {
                            $line = $converted;
                        }
                    }
                }
            }
            return $line;
        };

        $firstLine = $normalizeLine((string)$lines[0]);
        $candidates = [',', ';', "\t", '|'];
        $delimiter = ',';
        $maxParts = 0;
        foreach ($candidates as $candidate) {
            $parts = str_getcsv($firstLine, $candidate);
            $count = is_array($parts) ? count($parts) : 0;
            if ($count > $maxParts) {
                $maxParts = $count;
                $delimiter = $candidate;
            }
        }

        $rows = [];
        foreach ($lines as $line) {
            $line = $normalizeLine((string)$line);
            $parsed = str_getcsv($line, $delimiter);
            if ($parsed === null) {
                continue;
            }
            $rows[] = array_map(static function ($cell) {
                $value = is_string($cell) ? trim($cell) : (string)$cell;
                return preg_replace('/^\xEF\xBB\xBF/u', '', $value);
            }, $parsed);
        }

        return $rows;
    }

    private function findHeaderIndex(array $headers, array $aliases): int
    {
        $normalizedAliases = array_map([$this, 'normalizeHeaderText'], $aliases);
        foreach ($headers as $index => $name) {
            $normalized = $this->normalizeHeaderText((string)$name);
            if (in_array($normalized, $normalizedAliases, true)) {
                return (int)$index;
            }
        }
        return -1;
    }

    private function normalizeHeaderText(string $value): string
    {
        $v = preg_replace('/^\xEF\xBB\xBF/u', '', $value);
        $v = str_replace(['-', '–', '—', 'ـ', ' '], '_', $v);
        $v = preg_replace('/_+/', '_', $v);
        $v = trim($v, "_ \t\n\r\0\x0B");
        return mb_strtolower($v);
    }

    private function extractImportRow(array $row, array $headerMap, int $rowNumber): array
    {
        $get = static function (array $data, int $idx): string {
            if ($idx < 0) return '';
            return trim((string)($data[$idx] ?? ''));
        };
        $mobileRaw = $get($row, (int)$headerMap['mobile_phone']);
        $ownerUsername = $get($row, (int)$headerMap['owner_username']);
        $mobileDigits = preg_replace('/\D+/', '', $mobileRaw);
        if (strlen($mobileDigits) === 11 && strpos($mobileDigits, '0') === 0) {
            $mobileDigits = substr($mobileDigits, 1);
        }
        return [
            'row_number' => $rowNumber,
            'mobile_phone' => (string)$mobileDigits,
            'owner_username' => $ownerUsername,
            'full_name' => $get($row, (int)$headerMap['full_name']),
            'city' => $get($row, (int)$headerMap['city']),
            'acquaintance_source' => $this->normalizeLookupImportValue('lead_sources', $get($row, (int)$headerMap['acquaintance_source'])),
            'acquaintance_detail' => $this->normalizeLookupImportValue('lead_source_details', $get($row, (int)$headerMap['acquaintance_detail'])),
            'content_topic' => $this->normalizeLookupImportValue('content_topics', $get($row, (int)$headerMap['content_topic']))
        ];
    }

    private function validateImportRow(array $rowData): array
    {
        $errors = [];
        if (empty($rowData['mobile_phone'])) {
            $errors[] = 'شماره موبایل خالی است';
        } else {
            $validation = Validator::validatePhone($rowData['mobile_phone'], 'شماره موبایل');
            if (!$validation['valid']) {
                $errors[] = $validation['message'];
            } else {
                $rowData['mobile_phone'] = $validation['cleaned'] ?? $rowData['mobile_phone'];
            }
        }
        if (empty($rowData['owner_username'])) {
            $errors[] = 'نام کاربری مسئول خالی است';
        } elseif (!$this->resolveOwnerByUsername((string)$rowData['owner_username'])) {
            $errors[] = "مسئول با username '{$rowData['owner_username']}' یافت نشد";
        }
        if (empty($rowData['acquaintance_source'])) {
            $errors[] = 'شیوه آشنایی خالی است';
        } elseif (!$this->lookupValueExists('lead_sources', (string)$rowData['acquaintance_source'])) {
            $errors[] = 'شیوه آشنایی معتبر نیست';
        }
        if (!empty($rowData['acquaintance_detail']) && !$this->lookupValueExists('lead_source_details', (string)$rowData['acquaintance_detail'])) {
            $errors[] = 'جزئیات شیوه آشنایی معتبر نیست';
        }
        if (empty($rowData['content_topic'])) {
            $errors[] = 'موضوع محتوا خالی است';
        } elseif (!$this->lookupValueExists('content_topics', (string)$rowData['content_topic'])) {
            $errors[] = 'موضوع محتوا معتبر نیست';
        }
        return $errors;
    }

    private function buildDealImportExtra(array $rowData): array
    {
        return [
            'acquaintance_source' => (string)($rowData['acquaintance_source'] ?? ''),
            'acquaintance_detail' => (string)($rowData['acquaintance_detail'] ?? ''),
            'content_topic' => (string)($rowData['content_topic'] ?? ''),
            'created_by_didar_id' => $this->currentCreatorDidarId(),
            'created_by_name' => $this->currentCreatorName(),
            'created_via' => 'excel'
        ];
    }

    private function currentCreatorDidarId(): string
    {
        return trim((string)($_SESSION['didar_id'] ?? ''));
    }

    private function currentCreatorName(): string
    {
        return trim((string)(($_SESSION['display_name'] ?? '') ?: ($_SESSION['name'] ?? '')));
    }

    private function firstLookupTitle(string $groupCode): string
    {
        $items = $this->getLookupItems($groupCode);
        return (string)($items[0]['title'] ?? $items[0]['value'] ?? '');
    }

    private function firstActiveUserImportIdentifier(): string
    {
        $users = $this->getActiveUserImportValues();
        return (string)($users[0] ?? '');
    }

    private function normalizeLookupImportValue(string $groupCode, string $value): string
    {
        $value = trim($value);
        if ($value === '') {
            return '';
        }
        $normalized = $this->normalizeHeaderText($value);
        foreach ($this->getLookupItems($groupCode) as $item) {
            foreach (['value', 'title', 'code'] as $key) {
                $candidate = trim((string)($item[$key] ?? ''));
                if ($candidate !== '' && $this->normalizeHeaderText($candidate) === $normalized) {
                    return (string)($item['value'] ?: $item['code'] ?: $candidate);
                }
            }
        }
        return $value;
    }

    private function lookupValueExists(string $groupCode, string $value): bool
    {
        $value = trim($value);
        if ($value === '') {
            return false;
        }
        foreach ($this->getLookupItems($groupCode) as $item) {
            if ($value === (string)($item['value'] ?? '') || $value === (string)($item['code'] ?? '')) {
                return true;
            }
        }
        return false;
    }

    private function getLookupItems(string $groupCode): array
    {
        static $cache = [];
        if (isset($cache[$groupCode])) {
            return $cache[$groupCode];
        }
        $pdo = $this->personRepo->getDb()->getPdo();
        $stmt = $pdo->prepare("
            SELECT li.code, li.title, li.value
            FROM lookup_items li
            INNER JOIN lookup_groups lg ON lg.id = li.group_id
            WHERE lg.code = ? AND li.is_active = 1
            ORDER BY li.sort_order ASC, li.id ASC
        ");
        $stmt->execute([$groupCode]);
        $cache[$groupCode] = $stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];
        return $cache[$groupCode];
    }

    private function addLookupDropdowns(Spreadsheet $spreadsheet, \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet $sheet): void
    {
        $lookupSheet = $spreadsheet->createSheet();
        $lookupSheet->setTitle('lists');
        $lookupSheet->fromArray($this->lookupColumnValues('lead_sources'), null, 'A1');
        $lookupSheet->fromArray($this->lookupColumnValues('lead_source_details'), null, 'B1');
        $lookupSheet->fromArray($this->lookupColumnValues('content_topics'), null, 'C1');
        $lookupSheet->fromArray($this->activeUserColumnValues(), null, 'D1');
        $lookupSheet->setSheetState(\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::SHEETSTATE_HIDDEN);

        $this->applyDropdown($sheet, 'E', max(1, count($this->getLookupItems('lead_sources'))), 'A');
        $this->applyDropdown($sheet, 'F', max(1, count($this->getLookupItems('lead_source_details'))), 'B');
        $this->applyDropdown($sheet, 'G', max(1, count($this->getLookupItems('content_topics'))), 'C');
        $this->applyDropdown($sheet, 'B', max(1, count($this->getActiveUserImportValues())), 'D');
    }

    private function lookupColumnValues(string $groupCode): array
    {
        return array_map(static fn($title) => [$title], array_column($this->getLookupItems($groupCode), 'title'));
    }

    private function activeUserColumnValues(): array
    {
        return array_map(static fn($value) => [$value], $this->getActiveUserImportValues());
    }

    private function getActiveUserImportValues(): array
    {
        $values = [];
        foreach ($this->userRepo->getLoginUsers() as $user) {
            $identifier = trim((string)($user['username'] ?? ''));
            if ($identifier === '') {
                $identifier = trim((string)($user['display_name'] ?? ''));
            }
            if ($identifier !== '') {
                $values[$identifier] = true;
            }
        }
        return array_keys($values);
    }

    private function applyDropdown(\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet $sheet, string $targetColumn, int $count, string $lookupColumn): void
    {
        for ($row = 2; $row <= 501; $row++) {
            $validation = $sheet->getCell($targetColumn . $row)->getDataValidation();
            $validation->setType(DataValidation::TYPE_LIST);
            $validation->setErrorStyle(DataValidation::STYLE_STOP);
            $validation->setAllowBlank($targetColumn === 'F');
            $validation->setShowDropDown(true);
            $validation->setFormula1("'lists'!$" . $lookupColumn . '$1:$' . $lookupColumn . '$' . $count);
        }
    }

    private function resolveOwnerByUsername(string $username)
    {
        $normalized = trim($username);
        if ($normalized === '') return null;
        return $this->userRepo->findByUsernameOrDisplayName($normalized);
    }

    private function resolveOwnerName(string $ownerDidarId): string
    {
        if ($ownerDidarId === '') return 'نامشخص';
        $owner = $this->userRepo->findByDidarId($ownerDidarId);
        if (!$owner) return $ownerDidarId;
        $name = trim((string)($owner->display_name ?? (($owner->first_name ?? '') . ' ' . ($owner->last_name ?? ''))));
        return $name !== '' ? $name : $ownerDidarId;
    }

    private function buildImportResult(array $rowData, string $status, string $action, string $message, string $ownerDisplayName = '', array $extra = []): array
    {
        return array_merge([
            'row' => (int)($rowData['row_number'] ?? 0),
            'status' => $status,
            'action' => $action,
            'mobile_phone' => (string)($rowData['mobile_phone'] ?? ''),
            'owner_username' => (string)($rowData['owner_username'] ?? ''),
            'owner_name' => $ownerDisplayName,
            'message' => $message
        ], $extra);
    }

    private function isRowEmpty(array $row): bool
    {
        foreach ($row as $cell) {
            if (trim((string)$cell) !== '') return false;
        }
        return true;
    }

    /**
     * استعلام انبوه شماره‌ها از فایل Excel/CSV
     */
    public function bulkPhoneLookup()
    {
        if (!$this->authService->isAdmin() && !$this->hasDelegatedCapability('imports.admin.leads')) {
            $this->errorResponse('دسترسی محدود - فقط مدیر می‌تواند استعلام انبوه انجام دهد', 403);
            return;
        }

        try {
            $phones = $this->parsePhonesFromUpload();
            if (empty($phones)) {
                $this->errorResponse('هیچ شماره‌ای در فایل یافت نشد');
                return;
            }

            $results = [];
            foreach ($phones as $entry) {
                $raw = $entry['raw'];
                $digits = $entry['digits'];
                $rowNumber = $entry['row'];

                if ($digits === '') {
                    $results[] = [
                        'row' => $rowNumber,
                        'phone' => $raw,
                        'found' => false,
                        'matches' => [],
                        'error' => 'فرمت شماره نامعتبر است'
                    ];
                    continue;
                }

                $candidates = \App\Utils\PhoneNormalizer::normalizeCandidates($digits);
                $matches = $this->personRepo->findByPhoneCandidatesWithOwners($candidates);

                $results[] = [
                    'row' => $rowNumber,
                    'phone' => $digits,
                    'found' => !empty($matches),
                    'matches' => array_map(static function ($m) {
                        return [
                            'display_name' => (string)($m['display_name'] ?? ''),
                            'didar_contact_id' => (string)($m['didar_contact_id'] ?? ''),
                            'owner_name' => (string)($m['owner_name'] ?? ''),
                            'previous_owner_name' => (string)($m['previous_owner_name'] ?? ''),
                            'mobile_phone' => (string)($m['mobile_phone'] ?? ''),
                            'secondary_mobile_phone' => (string)($m['secondary_mobile_phone'] ?? ''),
                            'mobile_phone_3' => (string)($m['mobile_phone_3'] ?? ''),
                            'mobile_phone_4' => (string)($m['mobile_phone_4'] ?? ''),
                        ];
                    }, $matches),
                    'error' => null
                ];
            }

            $foundCount = count(array_filter($results, static fn($r) => $r['found']));
            $notFoundCount = count(array_filter($results, static fn($r) => !$r['found'] && $r['error'] === null));
            $errorCount = count(array_filter($results, static fn($r) => $r['error'] !== null));

            $this->successResponse([
                'results' => $results,
                'total' => count($results),
                'found_count' => $foundCount,
                'not_found_count' => $notFoundCount,
                'error_count' => $errorCount
            ]);
        } catch (\Throwable $e) {
            $this->errorResponse('خطا در پردازش فایل: ' . $e->getMessage());
        }
    }

    private function parsePhonesFromUpload(): array
    {
        if (!isset($_FILES['file']) || $_FILES['file']['error'] !== UPLOAD_ERR_OK) {
            throw new \RuntimeException('فایلی آپلود نشده است');
        }

        $file = $_FILES['file'];
        $allowedExtensions = ['xlsx', 'xls', 'csv'];
        $fileExtension = strtolower(pathinfo((string)$file['name'], PATHINFO_EXTENSION));
        if (!in_array($fileExtension, $allowedExtensions, true)) {
            throw new \RuntimeException('فقط فایل‌های Excel یا CSV قابل پذیرش هستند');
        }
        if ((int)$file['size'] > 5 * 1024 * 1024) {
            throw new \RuntimeException('حجم فایل نباید بیشتر از 5 مگابایت باشد');
        }

        $rows = [];
        if ($fileExtension === 'csv') {
            $rows = $this->readCsvRows((string)$file['tmp_name']);
        } else {
            if (!class_exists('\PhpOffice\PhpSpreadsheet\IOFactory')) {
                throw new \RuntimeException('کتابخانه Excel روی سرور نصب نیست. لطفاً فایل CSV آپلود کنید.');
            }
            $spreadsheet = IOFactory::load((string)$file['tmp_name']);
            $rows = $spreadsheet->getActiveSheet()->toArray('', true, true, false);
        }

        if (empty($rows)) {
            throw new \RuntimeException('فایل خالی است');
        }

        $phoneAliases = ['mobile_phone', 'mobile', 'phone', 'شماره موبایل', 'شماره', 'موبایل', 'شماره تماس', 'phones', 'شماره‌ها'];
        $header = array_map(static fn($v) => trim((string)$v), (array)($rows[0] ?? []));
        $phoneColIdx = $this->findHeaderIndex($header, $phoneAliases);

        $entries = [];
        $dataRows = array_slice($rows, 1);

        foreach ($dataRows as $idx => $row) {
            $rowNumber = $idx + 2;
            if ($this->isRowEmpty((array)$row)) {
                continue;
            }

            if ($phoneColIdx >= 0) {
                $raw = trim((string)(((array)$row)[$phoneColIdx] ?? ''));
            } else {
                $raw = trim((string)(((array)$row)[0] ?? ''));
            }

            if ($raw === '') {
                continue;
            }

            $raw = strtr($raw, [
                '۰' => '0', '۱' => '1', '۲' => '2', '۳' => '3', '۴' => '4',
                '۵' => '5', '۶' => '6', '۷' => '7', '۸' => '8', '۹' => '9',
                '٠' => '0', '١' => '1', '٢' => '2', '٣' => '3', '٤' => '4',
                '٥' => '5', '٦' => '6', '٧' => '7', '٨' => '8', '٩' => '9',
            ]);

            $digitsOnly = preg_replace('/\D+/', '', $raw) ?? '';
            if (strlen($digitsOnly) === 11 && strpos($digitsOnly, '0') === 0) {
                $digitsOnly = substr($digitsOnly, 1);
            }

            $valid = strlen($digitsOnly) === 10 && strpos($digitsOnly, '9') === 0;

            $entries[] = [
                'row' => $rowNumber,
                'raw' => $raw,
                'digits' => $valid ? $digitsOnly : ''
            ];
        }

        return $entries;
    }

    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;
    }
}


