<?php

namespace App\Services;

use App\Models\Deal;
use App\Repositories\DealRepository;
use App\Repositories\PersonRepository;
use App\Repositories\ActivityRepository;
use App\Utils\Logger;

class DealService
{
    private $dealRepo;
    private $personRepo;
    private $didarApiService;
    private $productRepo;
    private $db;
    private $lookupRepo;
    private ?AuditService $auditService;

    public function __construct(DealRepository $dealRepo, PersonRepository $personRepo, $didarApiService, $productRepo, $db, $lookupRepo = null, ?AuditService $auditService = null)
    {
        $this->dealRepo = $dealRepo;
        $this->personRepo = $personRepo;
        $this->didarApiService = $didarApiService;
        $this->productRepo = $productRepo;
        $this->db = $db;
        $this->lookupRepo = $lookupRepo;
        $this->auditService = $auditService;
    }

    public function getDeals(array $filters = [])
    {
        return $this->dealRepo->getAll($filters);
    }

    public function getDeal(string $dealId): ?Deal
    {
        return $this->dealRepo->findByDidarId($dealId);
    }

    public function saveDeal(array $data): Deal
    {
        // Ensure deal owner matches lead owner
        $contactId = $data['contact_didar_id'] ?? '';
        if (!empty($contactId)) {
            // IMPORTANT: Here we KNOW $contactId is a Didar ID (GUID),
            // so we MUST use findByDidarId to avoid collision with local numeric IDs
            $person = $this->personRepo->findByDidarId($contactId);
            if (!$person) {
                throw new \Exception('شناسه لید نامعتبر است');
            }
            $leadOwner = $person->owner_didar_id;
            if (empty($leadOwner)) {
                throw new \Exception('مسئول لید نامشخص است');
            }
            if (!empty($data['owner_didar_id']) && $data['owner_didar_id'] !== $leadOwner) {
                throw new \Exception('مسئول معامله باید با مسئول لید یکسان باشد');
            }
            // Force owner to align
            $data['owner_didar_id'] = $leadOwner;
            // Ensure we use the real GUID for the contact_didar_id field
            $data['contact_didar_id'] = $person->didar_contact_id;
            // Store the local person id for robust joining
            $data['local_person_id'] = $person->id;
        }

        // For new deals, generate title automatically if not provided
        $isNewDeal = empty($data['didar_deal_id']) || !isset($data['didar_deal_id']);
        if ($isNewDeal && !empty($contactId) && empty($data['title'])) {
            try {
                $data['title'] = $this->generateDealTitle($contactId);
                Logger::logInfo('Generated deal title for new deal', [
                    'contact_id' => $contactId,
                    'generated_title' => $data['title']
                ]);
            } catch (\Exception $e) {
                // If title generation fails, continue with empty title (will be caught by validation)
                Logger::logError('Failed to generate deal title', $e, ['contact_id' => $contactId]);
            }
        }

        // Generate unique local didar_deal_id if missing
        if (empty($data['didar_deal_id'])) {
            $data['didar_deal_id'] = uniqid('local_', true);
        }
        if (empty($data['status'])) {
            throw new \Exception('وضعیت معامله الزامی است');
        }
        if (empty($data['pipeline_stage'])) {
            throw new \Exception('مرحله کاریز معامله الزامی است');
        }
        if (empty($data['register_time'])) {
            $data['register_time'] = date('Y-m-d H:i:s');
        }
        if (empty($data['last_sync'])) {
            $data['last_sync'] = date('Y-m-d H:i:s');
        }

        // Set default values for financial fields to prevent NULL constraint violations
        if (!isset($data['payable_amount']) || $data['payable_amount'] === '') {
            $data['payable_amount'] = 0;
        }
        if (!isset($data['payment_amount']) || $data['payment_amount'] === '') {
            $data['payment_amount'] = 0;
        }

        // Normalize requested services: allow array or comma-separated
        if (isset($data['requested_services']) && is_array($data['requested_services'])) {
            $data['requested_services'] = json_encode(array_values(array_filter($data['requested_services'])), JSON_UNESCAPED_UNICODE);
        }
        $deal = new \App\Models\Deal($data);
        // Normalize status casing
        $statusMap = [
            'pending' => 'Pending',
            'Pending' => 'Pending',
            'won' => 'Won',
            'Won' => 'Won',
            'lost' => 'Lost',
            'Lost' => 'Lost'
        ];
        if (!empty($deal->status) && isset($statusMap[$deal->status])) {
            $deal->status = $statusMap[$deal->status];
        }
        $now = date('Y-m-d H:i:s');
        if ($deal->status === 'Won') {
            if (empty($deal->won_time)) {
                $deal->won_time = $now;
            }
            $deal->pipeline_stage = 'اتمام';
            $deal->lost_time = null;
        } elseif ($deal->status === 'Lost') {
            if (empty($deal->lost_time)) {
                $deal->lost_time = $now;
            }
            $deal->won_time = null;
        } else {
            $deal->won_time = null;
            $deal->lost_time = null;
        }

        $this->dealRepo->save($deal);

        // Sync deal products if provided
        if (!empty($data['products']) && is_array($data['products'])) {
            $this->syncDealProducts($deal->didar_deal_id, $data['products']);
        }

        // Keep lead customer_products aligned with requested services as well.
        if (!empty($deal->contact_didar_id) && isset($data['requested_services'])) {
            $requestedServices = $data['requested_services'];
            if (is_string($requestedServices)) {
                $decoded = json_decode($requestedServices, true);
                if (is_array($decoded)) {
                    $requestedServices = $decoded;
                } else {
                    $requestedServices = preg_split('/[،,]+/u', $requestedServices) ?: [];
                }
            }
            if (is_array($requestedServices)) {
                $requestedServices = array_values(array_filter(array_map(static function ($v) {
                    return trim((string)$v);
                }, $requestedServices)));
                if (!empty($requestedServices)) {
                    $this->personRepo->appendProducts($deal->contact_didar_id, $requestedServices);
                }
            }
        }

        // If deal is successful, update lead sale status and products
        if (!empty($deal->contact_didar_id) && $deal->status === 'Won') {
            $this->personRepo->updateSaleStatus($deal->contact_didar_id, 1);
            $this->handleSuccessfulDeal($deal->contact_didar_id, $data['products'] ?? [], $deal->title ?? '');
        }

        return $deal;
    }

    public function syncTransactionsFromPayments(string $dealId, array $payments): void
    {
        if (empty($payments)) {
            return;
        }

        $internalDealId = $this->resolveInternalDealId($dealId);
        if (!$internalDealId) {
            Logger::logWarning('syncTransactionsFromPayments: deal not found', ['deal_id' => $dealId]);
            return;
        }

        $normalized = $this->normalizePaymentsForTransactions($payments);

        if (empty($normalized)) {
            return;
        }

        // Sort by timestamp to ensure chronological order (not string comparison)
        usort($normalized, function($a, $b) {
            $timeA = strtotime($a['payment_date']);
            $timeB = strtotime($b['payment_date']);
            return $timeA - $timeB;
        });

        $pdo = $this->db->getPdo();
        $existingTransactions = [];
        if ($this->auditService) {
            $existingStmt = $pdo->prepare("SELECT * FROM transactions WHERE deal_id = ?");
            $existingStmt->execute([$internalDealId]);
            $existingTransactions = $existingStmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];
        }
        $delete = $pdo->prepare("DELETE FROM transactions WHERE deal_id = ?");
        $delete->execute([$internalDealId]);

        if ($this->auditService && !empty($existingTransactions)) {
            $dealForAudit = $this->dealRepo->findById($internalDealId);
            $relatedPersonId = trim((string)($dealForAudit->contact_didar_id ?? ''));
            foreach ($existingTransactions as $transaction) {
                $transactionId = isset($transaction['id']) ? (string)$transaction['id'] : null;
                $this->auditService->log(
                    'delete',
                    'payment',
                    $transactionId,
                    ['transaction' => $transaction],
                    $relatedPersonId !== '' ? $relatedPersonId : null
                );
            }
        }

        $insertColumns = ['deal_id', 'amount', 'payment_date', 'payment_time', 'method', 'card_last4', 'destination_bank'];
        $insertValues = ['?', '?', '?', '?', '?', '?', '?'];
        $hasTrackingCodeLast4 = $this->db->hasColumn('transactions', 'tracking_code_last4');
        if ($hasTrackingCodeLast4) {
            $insertColumns[] = 'tracking_code_last4';
            $insertValues[] = '?';
        }
        $hasCheckOwnerName = $this->db->hasColumn('transactions', 'check_owner_name');
        if ($hasCheckOwnerName) {
            $insertColumns[] = 'check_owner_name';
            $insertValues[] = '?';
        }
        $insertColumns = array_merge($insertColumns, ['description', 'is_first_payment', 'status', 'created_at']);
        $insertValues = array_merge($insertValues, ['?', '?', "'confirmed'", 'NOW()']);
        $insert = $pdo->prepare("
            INSERT INTO transactions (" . implode(', ', $insertColumns) . ")
            VALUES (" . implode(', ', $insertValues) . ")
        ");

        foreach ($normalized as $idx => $payment) {
            $rowParams = [
                $internalDealId,
                $payment['amount'],
                $payment['payment_date'],
                (preg_match('/^\d{2}:\d{2}$/', (string)$payment['payment_time']) ? $payment['payment_date'] . ' ' . $payment['payment_time'] . ':00' : $payment['payment_date'] . ' 00:00:00'),
                $payment['method'],
                $payment['card_last4'],
                $payment['destination_bank'],
            ];
            if ($hasTrackingCodeLast4) {
                $rowParams[] = $payment['tracking_code_last4'] ?? '';
            }
            if ($hasCheckOwnerName) {
                $rowParams[] = $payment['check_owner_name'] ?? '';
            }
            $rowParams[] = $payment['description'];
            $rowParams[] = 0;
            $insert->execute($rowParams);
        }

        $resetFirstPayment = $pdo->prepare("UPDATE transactions SET is_first_payment = 0 WHERE deal_id = ?");
        $resetFirstPayment->execute([$internalDealId]);

        $firstIdStmt = $pdo->prepare("
            SELECT id
            FROM transactions
            WHERE deal_id = ?
            ORDER BY COALESCE(payment_date, DATE(payment_time)) ASC, id ASC
            LIMIT 1
        ");
        $firstIdStmt->execute([$internalDealId]);
        $firstId = $firstIdStmt->fetchColumn();
        if ($firstId) {
            $markFirst = $pdo->prepare("UPDATE transactions SET is_first_payment = 1 WHERE id = ?");
            $markFirst->execute([$firstId]);
        }

        if ($this->db->hasColumn('transactions', 'invoice_code')) {
            $dealInvoiceCode = '';
            $dealStmt = $pdo->prepare("SELECT invoice_code FROM deals WHERE id = ? LIMIT 1");
            $dealStmt->execute([$internalDealId]);
            $dealInvoiceCode = trim((string)$dealStmt->fetchColumn());
            $updateInvoiceStmt = $pdo->prepare("UPDATE transactions SET invoice_code = ? WHERE deal_id = ?");
            $updateInvoiceStmt->execute([$dealInvoiceCode !== '' ? $dealInvoiceCode : null, $internalDealId]);
        }

        // Auto-promote lead to successful when at least one valid payment is recorded.
        $deal = $this->dealRepo->findById($internalDealId);
        if ($deal && !empty($deal->contact_didar_id) && $this->hasSuccessfulPayment($normalized)) {
            $this->personRepo->updateSaleStatus($deal->contact_didar_id, 1);
        }

        try {
            if ($deal) {
                $this->ensureDealProductsFromText(
                    (string)$internalDealId,
                    $deal->requested_services ?? '',
                    $deal->title ?? ''
                );
            }
        } catch (\Exception $e) {
            Logger::logWarning('syncTransactionsFromPayments: ensureDealProductsFromText failed', [
                'deal_id' => $dealId,
                'error' => $e->getMessage()
            ]);
        }
    }

    public function ensureInvoiceCodeForDeal(string $dealId, array $payments): ?string
    {
        if (empty($payments)) {
            return null;
        }

        $internalDealId = $this->resolveInternalDealId($dealId);
        if ($internalDealId <= 0) {
            return null;
        }

        $pdo = $this->db->getPdo();
        if (!$this->db->hasColumn('deals', 'invoice_code')) {
            return null;
        }

        $dealStmt = $pdo->prepare("SELECT invoice_code FROM deals WHERE id = ? LIMIT 1");
        $dealStmt->execute([$internalDealId]);
        $existingCode = trim((string)$dealStmt->fetchColumn());
        if ($existingCode !== '') {
            return $existingCode;
        }

        $normalized = $this->normalizePaymentsForTransactions($payments);
        if (empty($normalized)) {
            return null;
        }

        $invoiceCode = $this->allocateNextInvoiceCode();

        $updateStmt = $pdo->prepare("UPDATE deals SET invoice_code = ? WHERE id = ? AND (invoice_code IS NULL OR invoice_code = '')");
        $updateStmt->execute([$invoiceCode, $internalDealId]);

        if ((int)$updateStmt->rowCount() === 0) {
            $dealStmt->execute([$internalDealId]);
            $existingAfterRace = trim((string)$dealStmt->fetchColumn());
            return $existingAfterRace !== '' ? $existingAfterRace : null;
        }

        return $invoiceCode;
    }

    public function updateStatus($dealId, $status, $timeField = null)
    {
        return $this->dealRepo->updateStatus($dealId, $status, $timeField);
    }

    public function deleteDeal($dealId)
    {
        return $this->dealRepo->delete($dealId);
    }

    public function updatePipelineStage(string $dealId, string $stage)
    {
        return $this->dealRepo->updatePipelineStage($dealId, $stage);
    }

    public function generateDealTitle(string $contactId): string
    {
        // CRITICAL: This method MUST ONLY use findByDidarId
        // It should receive a Didar GUID from the controller, NOT a local numeric ID
        $person = $this->personRepo->findByDidarId($contactId);
        
        if (!$person) {
            Logger::logError('generateDealTitle: person not found in Didar - CRITICAL ERROR', [
                'contact_id' => $contactId,
                'expected_format' => 'Didar GUID (didar_contact_id)',
                'note' => 'This method should NEVER receive a local numeric ID'
            ]);
            throw new \Exception("شخص در سیستم دیدار یافت نشد");
        }

        if (empty($person->mobile_phone)) {
            Logger::logError('generateDealTitle: no mobile phone', [
                'contact_id' => $contactId,
                'person_name' => trim(($person->first_name ?? '') . ' ' . ($person->last_name ?? ''))
            ]);
            throw new \Exception("شماره تماس اصلی لید پیدا نشد");
        }

        // Always use contact_didar_id (GUID) for counting to ensure consistency
        // This matches the logic in createLocalDealForContact which also uses countDealsByContactId
        $dealCount = $this->dealRepo->countDealsByContactId($contactId);

        // Generate ordinal number (اول، دوم، سوم، ...)
        $ordinals = [
            0 => "اول",
            1 => "دوم",
            2 => "سوم",
            3 => "چهارم",
            4 => "پنجم",
            5 => "ششم",
            6 => "هفتم",
            7 => "هشتم",
            8 => "نهم",
            9 => "دهم"
        ];

        $ordinal = isset($ordinals[$dealCount])
            ? $ordinals[$dealCount]
            : ($dealCount + 1) . "ام";
        $title = "معامله {$ordinal} {$person->mobile_phone}";

        Logger::logInfo('generateDealTitle result', [
            'contact_id' => $contactId,
            'person_name' => trim(($person->first_name ?? '') . ' ' . ($person->last_name ?? '')),
            'mobile' => $person->mobile_phone,
            'existing_deals' => $dealCount,
            'ordinal' => $ordinal,
            'title' => $title
        ]);

        return $title;
    }

    /**
     * Get allowed services list
     */
    public function getAllowedServices(): array
    {
        if ($this->lookupRepo) {
            $items = $this->lookupRepo->getItemsByGroupCode('allowed_services');
            if (!empty($items)) {
                $values = [];
                foreach ($items as $item) {
                    $key = $item['code'] ?? $item['value'] ?? null;
                    $val = $item['value'] ?? $item['title'] ?? null;
                    if ($key !== null && $key !== '') {
                        $values[$key] = $val ?? $key;
                    }
                }
                return $values;
            }
        }
        return [
            'consultation' => 'مشاوره',
            'training' => 'آموزش',
            'development' => 'توسعه',
            'support' => 'پشتیبانی'
        ];
    }

    /**
     * Sync deal products from form
     */
    private function syncDealProducts(string $dealId, array $products): void
    {
        if (empty($products)) {
            return;
        }

        $internalDealId = $this->resolveInternalDealId($dealId);
        if (!$internalDealId) {
            Logger::logWarning('syncDealProducts: deal not found', ['deal_id' => $dealId]);
            return;
        }

        $pdo = $this->db->getPdo();

        // First, delete existing products for this deal
        $stmt = $pdo->prepare("DELETE FROM deal_products WHERE deal_id = ?");
        $stmt->execute([$internalDealId]);

        // Insert new products
        $stmt = $pdo->prepare("
            INSERT INTO deal_products (deal_id, product_id, created_at)
            VALUES (?, ?, NOW())
        ");

        foreach ($products as $productId) {
            $stmt->execute([$internalDealId, $productId]);
        }
    }

    private function hasSuccessfulPayment(array $payments): bool
    {
        foreach ($payments as $payment) {
            $amount = isset($payment['amount']) ? floatval($payment['amount']) : 0;
            $paymentDate = trim((string)($payment['payment_date'] ?? ''));
            $method = trim((string)($payment['method'] ?? ''));
            if ($amount > 0 && $paymentDate !== '' && $method !== '') {
                return true;
            }
        }
        return false;
    }

    public function backfillLeadSaleStatusFromSuccessfulPayments(): array
    {
        $pdo = $this->db->getPdo();

        $countSql = "
            SELECT COUNT(DISTINCT p.didar_contact_id)
            FROM persons p
            INNER JOIN deals d ON d.contact_didar_id = p.didar_contact_id
            INNER JOIN transactions t ON t.deal_id = d.id
            WHERE COALESCE(p.sale_status, 0) <> 1
              AND t.status = 'confirmed'
              AND COALESCE(t.amount, 0) > 0
        ";
        $countStmt = $pdo->query($countSql);
        $eligible = (int)$countStmt->fetchColumn();

        $updateSql = "
            UPDATE persons p
            SET p.sale_status = 1
            WHERE COALESCE(p.sale_status, 0) <> 1
              AND EXISTS (
                  SELECT 1
                  FROM deals d
                  INNER JOIN transactions t ON t.deal_id = d.id
                  WHERE d.contact_didar_id = p.didar_contact_id
                    AND t.status = 'confirmed'
                    AND COALESCE(t.amount, 0) > 0
              )
        ";
        $updated = (int)$pdo->exec($updateSql);

        Logger::logInfo('Backfill lead sale_status from successful payments completed', [
            'eligible' => $eligible,
            'updated' => $updated
        ]);

        return [
            'eligible' => $eligible,
            'updated' => $updated
        ];
    }

    private function resolveInternalDealId(string $dealId): int
    {
        if ($dealId === '') {
            return 0;
        }

        // Always try didar_deal_id mapping first.
        // Numeric external IDs (e.g. "15400") must not be treated as deals.id directly.
        $mappedId = (int)$this->dealRepo->findInternalIdByDidarId($dealId);
        if ($mappedId > 0) {
            return $mappedId;
        }

        // Fallback: allow direct internal ID only if that row actually exists.
        if (ctype_digit((string)$dealId)) {
            $directId = (int)$dealId;
            if ($directId > 0 && $this->dealRepo->findById($directId)) {
                return $directId;
            }
        }

        return 0;
    }

    private function normalizePaymentsForTransactions(array $payments): array
    {
        $normalized = array_values(array_filter(array_map(function($payment) {
            if (!is_array($payment)) {
                return null;
            }
            $amount = isset($payment['amount']) ? floatval($payment['amount']) : 0;
            $date = trim((string)($payment['date'] ?? ''));
            $datePersian = trim((string)($payment['date_persian'] ?? ''));
            $method = trim((string)($payment['method'] ?? $payment['payment_method'] ?? ''));
            $description = trim((string)($payment['description'] ?? ''));
            $cardLast4 = trim((string)($payment['card_last4'] ?? $payment['card_last_4'] ?? ''));
            $bank = trim((string)($payment['destination_bank'] ?? $payment['bank_destination'] ?? ''));
            $bankOther = trim((string)($payment['destination_bank_other'] ?? ''));
            $accountOwnerName = trim((string)($payment['account_owner_name'] ?? ''));
            $paymentTime = trim((string)($payment['payment_time'] ?? ''));
            $checkOwnerName = trim((string)($payment['check_owner_name'] ?? ''));
            $trackingCodeLast4 = preg_replace('/\D+/', '', (string)($payment['tracking_code_last4'] ?? ''));
            if (strlen($trackingCodeLast4) > 4) {
                $trackingCodeLast4 = substr($trackingCodeLast4, -4);
            }

            if ($amount <= 0 || ($date === '' && $datePersian === '')) {
                return null;
            }

            $dateFinal = $this->normalizePaymentDate($date, $datePersian);
            if (!$dateFinal) {
                $dateFinal = date('Y-m-d');
            }

            return [
                'amount' => $amount,
                'payment_date' => $dateFinal,
                'method' => $method,
                'description' => $description,
                'card_last4' => $cardLast4,
                'destination_bank' => ($bank === '__other__' && $bankOther !== '') ? $bankOther : (($bank !== '') ? $bank : $accountOwnerName),
                'payment_time' => $paymentTime,
                'tracking_code_last4' => $trackingCodeLast4,
                'check_owner_name' => $checkOwnerName
            ];
        }, $payments)));

        return $normalized;
    }

    private function allocateNextInvoiceCode(): string
    {
        // Global gapless serial invoice code: 1001, 1002, ...
        $sequenceKey = 'global';
        $pdo = $this->db->getPdo();

        $initStmt = $pdo->prepare("
            INSERT INTO invoice_sequences (sequence_key, last_serial, updated_at)
            VALUES (?, 1000, NOW())
            ON DUPLICATE KEY UPDATE updated_at = NOW()
        ");
        $initStmt->execute([$sequenceKey]);

        $lockStmt = $pdo->prepare("SELECT last_serial FROM invoice_sequences WHERE sequence_key = ? FOR UPDATE");
        $lockStmt->execute([$sequenceKey]);
        $lastSerial = (int)$lockStmt->fetchColumn();
        $nextSerial = $lastSerial + 1;

        $updateStmt = $pdo->prepare("UPDATE invoice_sequences SET last_serial = ?, updated_at = NOW() WHERE sequence_key = ?");
        $updateStmt->execute([$nextSerial, $sequenceKey]);

        return (string)$nextSerial;
    }

    private function getJalaliYearFromGregorianDate(string $gregorianDate): int
    {
        $date = trim($gregorianDate);
        if (!preg_match('/^(\d{4})-(\d{2})-(\d{2})$/', $date, $m)) {
            return (int)date('Y');
        }
        [$jy] = $this->gregorianToJalali((int)$m[1], (int)$m[2], (int)$m[3]);
        return (int)$jy;
    }

    private function gregorianToJalali(int $gy, int $gm, int $gd): array
    {
        $g_d_m = [0,31,59,90,120,151,181,212,243,273,304,334];
        $jy = ($gy <= 1600) ? 0 : 979;
        $gy -= ($gy <= 1600) ? 621 : 1600;
        $gy2 = ($gm > 2) ? ($gy + 1) : $gy;
        $days = (365 * $gy) + (int)floor(($gy2 + 3) / 4) - (int)floor(($gy2 + 99) / 100) + (int)floor(($gy2 + 399) / 400) - 80 + $gd + $g_d_m[$gm - 1];
        $jy += 33 * (int)floor($days / 12053);
        $days %= 12053;
        $jy += 4 * (int)floor($days / 1461);
        $days %= 1461;
        if ($days > 365) {
            $jy += (int)floor(($days - 1) / 365);
            $days = ($days - 1) % 365;
        }
        if ($days < 186) {
            $jm = 1 + (int)floor($days / 31);
            $jd = 1 + ($days % 31);
        } else {
            $jm = 7 + (int)floor(($days - 186) / 30);
            $jd = 1 + (($days - 186) % 30);
        }
        return [$jy, $jm, $jd];
    }

    private function normalizePaymentDate(string $gregorianDate, string $persianDate): ?string
    {
        $gregorianDate = trim($this->normalizePersianDigits($gregorianDate));
        if ($gregorianDate !== '') {
            if (preg_match('/^\d{4}-\d{2}-\d{2}$/', $gregorianDate)) {
                return $gregorianDate;
            }
            if (preg_match('/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}/', $gregorianDate)) {
                return substr($gregorianDate, 0, 10);
            }
        }

        $persianDate = trim($this->normalizePersianDigits($persianDate));
        if (preg_match('/^\d{4}\/\d{1,2}\/\d{1,2}$/', $persianDate)) {
            [$jy, $jm, $jd] = array_map('intval', explode('/', $persianDate));
            
            // Validate parsed values
            if ($jy < 1300 || $jy > 1500 || $jm < 1 || $jm > 12 || $jd < 1 || $jd > 31) {
                // Invalid Persian date, return current date as fallback
                return date('Y-m-d');
            }
            
            $greg = $this->jalaliToGregorian($jy, $jm, $jd);
            
            // Validate converted date
            if ($greg[0] < 1900 || $greg[0] > 2100) {
                // Invalid conversion result, return current date as fallback
                return date('Y-m-d');
            }
            
            return sprintf('%04d-%02d-%02d', $greg[0], $greg[1], $greg[2]);
        }

        // If no valid date found, return current date (prevents NULL payment_date)
        return date('Y-m-d');
    }

    private function normalizePersianDigits(string $value): string
    {
        if ($value === '') {
            return $value;
        }

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

        return strtr($value, $map);
    }

    /**
     * Convert Jalali (Persian) date to Gregorian date
     * Uses accurate algorithm for date conversion
     */
    private function jalaliToGregorian(int $jy, int $jm, int $jd): array
    {
        $jy = (int)$jy;
        $jm = (int)$jm;
        $jd = (int)$jd;

        // Validate input
        if ($jy < 1 || $jy > 3000 || $jm < 1 || $jm > 12 || $jd < 1 || $jd > 31) {
            // Invalid date, return current date as fallback
            $now = getdate();
            return [$now['year'], $now['mon'], $now['mday']];
        }

        // Accurate Jalali to Gregorian conversion using standard algorithm
        // Source: https://www.fourmilab.ch/documents/calendar/
        
        // Step 1: Calculate Julian Day Number for Jalali date
        $jy = (int)$jy;
        $jm = (int)$jm;
        $jd = (int)$jd;
        
        // Jalali epoch: 226894 (March 21, 622 AD)
        $julianDay = $jy < 0 ? (($jy + 1) * 365) + floor(($jy + 1) / 4) - floor(($jy + 1) / 128) : 
                   ($jy * 365) + floor($jy / 4) - floor($jy / 128) + 226894;
        
        // Add days for months
        if ($jm <= 6) {
            $julianDay += ($jm - 1) * 31;
        } else {
            $julianDay += 186 + (($jm - 7) * 30);
        }
        
        // Add day
        $julianDay += $jd - 1;
        
        // Step 2: Convert Julian Day to Gregorian date
        $j = $julianDay + 1402;
        $k = floor(($j - 1) / 1461);
        $l = $j - (1461 * $k);
        $n = floor(($l - 1) / 365) - floor($l / 1461);
        $i = $l - (365 * $n) + 30;
        $gy = (4 * $k) + $n + $jy - 621;
        $gm = floor($i / 31);
        $gd = $i - (31 * $gm);
        
        // Adjust month and day
        if ($gm < 0 || $gm > 11) {
            $gy += floor($gm / 12);
            $gm = (($gm % 12) + 12) % 12;
        }
        $gm++; // Month is 1-12
        $gd++; // Day is 1-31
        
        // Validate and fix day if needed
        $daysInMonth = [31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31];
        // Check for leap year
        if ($gm == 2 && (($gy % 4 == 0 && $gy % 100 != 0) || ($gy % 400 == 0))) {
            $daysInMonth[1] = 29;
        }
        if ($gd > $daysInMonth[$gm - 1]) {
            $gd = $daysInMonth[$gm - 1];
        }
        
        // Final validation
        if ($gy < 1900 || $gy > 2100 || $gm < 1 || $gm > 12 || $gd < 1 || $gd > 31) {
            // Invalid result, use current date as fallback
            $now = getdate();
            return [$now['year'], $now['mon'], $now['mday']];
        }
        
        return [$gy, $gm, $gd];
    }

    public function ensureDealProductsFromText(string $dealId, $requestedServices, string $title): array
    {
        $internalDealId = ctype_digit((string)$dealId) ? (int)$dealId : null;
        if (!$internalDealId) {
            $internalDealId = (int)$this->dealRepo->findInternalIdByDidarId($dealId);
        }
        if (!$internalDealId) {
            Logger::logWarning('ensureDealProductsFromText: deal not found', ['deal_id' => $dealId]);
            return [];
        }

        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("SELECT COUNT(*) FROM deal_products WHERE deal_id = ?");
        $stmt->execute([$internalDealId]);
        $hasProducts = (int)$stmt->fetchColumn() > 0;
        if ($hasProducts) {
            return [];
        }

        $services = [];
        if (is_array($requestedServices)) {
            $services = $requestedServices;
        } else {
            $raw = trim((string)$requestedServices);
            if ($raw !== '') {
                $decoded = json_decode($raw, true);
                if (is_array($decoded)) {
                    $services = $decoded;
                } else {
                    $services = array_filter(array_map('trim', preg_split('/[,،]+/', $raw)));
                }
            }
        }

        $candidateTexts = array_values(array_filter(array_map('trim', $services)));
        if (empty($candidateTexts)) {
            $title = trim((string)$title);
            if ($title !== '') {
                if (strpos($title, '|') !== false) {
                    $parts = array_map('trim', explode('|', $title));
                    $candidateTexts[] = end($parts);
                }
                $candidateTexts[] = $title;
            }
        }

        $candidateTexts = array_values(array_unique(array_filter($candidateTexts)));
        if (empty($candidateTexts)) {
            return [];
        }

        $matchedProductIds = $this->productRepo->getIdsByNames($candidateTexts);
        if (empty($matchedProductIds)) {
            $matchedProductIds = $this->productRepo->matchIdsByText($candidateTexts);
        }
        if (empty($matchedProductIds)) {
            Logger::logInfo('ensureDealProductsFromText: no product match', [
                'deal_id' => $dealId,
                'texts' => $candidateTexts
            ]);
            return [];
        }

        $this->syncDealProducts((string)$internalDealId, $matchedProductIds);
        Logger::logInfo('ensureDealProductsFromText: products synced', [
            'deal_id' => $dealId,
            'product_ids' => $matchedProductIds
        ]);

        return $matchedProductIds;
    }

    /**
     * Handle successful deal: update lead status and products
     */
    private function handleSuccessfulDeal(string $contactId, array $products, string $dealTitle): void
    {
        $person = $this->personRepo->findByDidarId($contactId);
        if (!$person) {
            Logger::logWarning('handleSuccessfulDeal: person not found', ['contact_id' => $contactId]);
            return;
        }

        // Add products to customer_products
        if (!empty($products)) {
            $this->personRepo->appendProducts($contactId, $products);
        }

        Logger::logInfo('Deal success processed', [
            'contact_id' => $contactId,
            'person_name' => trim(($person->first_name ?? '') . ' ' . ($person->last_name ?? '')),
            'products_count' => count($products)
        ]);
    }

    public function reactivateDeal(string $dealId, string $newStage, string $reason = ''): void
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("
            UPDATE deals
            SET status = 'Pending',
                pipeline_stage = ?,
                won_time = NULL,
                lost_time = NULL,
                failure_reason_code = NULL,
                failure_reason_text = NULL,
                failure_reason_description = NULL,
                last_update_time = NOW(),
                last_sync = NOW()
            WHERE didar_deal_id = ?
        ");
        $stmt->execute([$newStage, $dealId]);

        Logger::logInfo('Deal successfully reactivated', [
            'deal_id' => $dealId,
            'new_stage' => $newStage,
            'reason' => $reason
        ]);
    }

    public function getLookupItems(string $groupCode, bool $includeInactive = false): array
    {
        if (!$this->lookupRepo) {
            return [];
        }
        return $this->lookupRepo->getItemsByGroupCode($groupCode, $includeInactive);
    }
}
