<?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;
    private ?CommissionService $commissionService;
    private ?array $pipelineStageLookupCache = null;

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

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

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

    public function isDealParked($deal): bool
    {
        return trim((string)($deal->pipeline_bucket ?? 'active')) === 'parked';
    }

    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->pipeline_stage = 'اتمام';
            $deal->won_time = null;
        } else {
            $deal->won_time = null;
            $deal->lost_time = null;
        }

        $existingStage = null;
        $existingDeal = null;
        if (!$isNewDeal && !empty($deal->didar_deal_id)) {
            $existingDeal = $this->dealRepo->findByDidarId((string)$deal->didar_deal_id);
            $existingStage = $existingDeal ? (string)($existingDeal->pipeline_stage ?? '') : null;
        }

        if ($existingDeal && $this->isDealParked($existingDeal)) {
            $incomingStatus = trim((string)($deal->status ?? 'Pending'));
            if (!in_array($incomingStatus, ['Pending', 'Won', 'Lost'], true)) {
                throw new \Exception('معامله پارک‌شده فقط می‌تواند جاری، موفق یا ناموفق باشد');
            }
            if ($incomingStatus === 'Pending') {
                $stageChanging = trim((string)$deal->pipeline_stage) !== trim((string)$existingDeal->pipeline_stage);
                if ($stageChanging) {
                    // Stage change by any user auto-unparks the deal
                    $deal->pipeline_bucket = 'active';
                    $deal->parked_from_stage = null;
                    $deal->parked_at = null;
                    $deal->parked_by_didar_id = null;
                    $deal->parked_reason = null;
                    $deal->park_count = (int)($existingDeal->park_count ?? 0);
                } else {
                    // Stage not changing — preserve parked state
                    $deal->pipeline_bucket = 'parked';
                    $deal->parked_from_stage = (string)($existingDeal->parked_from_stage ?: $existingDeal->pipeline_stage);
                    $deal->parked_at = $existingDeal->parked_at ?? null;
                    $deal->parked_by_didar_id = $existingDeal->parked_by_didar_id ?? null;
                    $deal->parked_reason = $existingDeal->parked_reason ?? null;
                    $deal->park_count = (int)($existingDeal->park_count ?? 0);
                }
            } else {
                $deal->pipeline_bucket = 'active';
                $deal->parked_from_stage = null;
                $deal->parked_at = null;
                $deal->parked_by_didar_id = null;
                $deal->parked_reason = null;
                $deal->park_count = (int)($existingDeal->park_count ?? 0);
            }
        } else {
            if (empty($deal->pipeline_bucket)) {
                $deal->pipeline_bucket = 'active';
            }
            if ($deal->pipeline_bucket !== 'parked') {
                $deal->parked_from_stage = null;
                $deal->parked_at = null;
                $deal->parked_by_didar_id = null;
                $deal->parked_reason = null;
            }
        }
        $this->assertPipelineStageAssignable((string)$deal->pipeline_stage, $existingStage, !$isNewDeal);

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

        // Assign a customer code to the lead when a deal is created/saved (idempotent: only once).
        if (!empty($deal->contact_didar_id)) {
            $this->personRepo->assignCustomerCodeIfMissing((string)$deal->contact_didar_id);
        }

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

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

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

        $pdo = $this->db->getPdo();
        $hasSourceHash = $this->db->hasColumn('transactions', 'payment_source_hash');
        $existingStmt = $pdo->prepare("SELECT * FROM transactions WHERE deal_id = ? ORDER BY id ASC");
        $existingStmt->execute([$internalDealId]);
        $existingTransactions = $existingStmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];
        $existingByHash = [];
        $unclaimedLegacyTransactions = [];
        foreach ($existingTransactions as $transaction) {
            $hash = trim((string)($transaction['payment_source_hash'] ?? ''));
            if ($hasSourceHash && $hash !== '') {
                $existingByHash[$hash] = $transaction;
                continue;
            }
            if (strtolower(trim((string)($transaction['status'] ?? 'confirmed'))) !== 'cancelled') {
                $unclaimedLegacyTransactions[] = $transaction;
            }
        }

        if (empty($normalized)) {
            $this->cancelMissingTransactions($internalDealId, []);
            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;
        });

        $baseColumns = ['deal_id', 'amount', 'payment_date', 'payment_time', 'method', 'card_last4', 'destination_bank'];
        $hasTrackingCodeLast4 = $this->db->hasColumn('transactions', 'tracking_code_last4');
        if ($hasTrackingCodeLast4) {
            $baseColumns[] = 'tracking_code_last4';
        }
        $hasCheckOwnerName = $this->db->hasColumn('transactions', 'check_owner_name');
        if ($hasCheckOwnerName) {
            $baseColumns[] = 'check_owner_name';
        }
        // Optional sprint columns (S0) — only bind those that exist in this DB.
        $optionalColumns = [
            'destination_card_number', 'destination_bank_title', 'destination_card_full',
            'destination_account_number', 'destination_sheba', 'destination_account_owner',
            'account_owner_name', 'depositor_name', 'deposit_date',
            'check_bank_name', 'check_number', 'check_date', 'check_due_date',
            'sayadi_date', 'check_delivery_date', 'check_recipient_name',
            'receipt_image_path', 'check_image_path',
            'payment_sequence', 'payment_source_hash', 'registered_by_didar_id',
            'registered_by_name', 'payment_owner_user_id', 'payment_owner_didar_id',
            'payment_owner_name_snapshot', 'payment_owner_locked_at',
            'refund_amount', 'refund_count', 'refund_description',
        ];
        $activeOptionalColumns = [];
        foreach ($optionalColumns as $col) {
            if ($this->db->hasColumn('transactions', $col)) {
                $activeOptionalColumns[] = $col;
            }
        }
        // created_at carries the immutable registration date (C3) when the column exists.
        $hasCreatedAt = $this->db->hasColumn('transactions', 'created_at');
        $writeColumns = array_merge($baseColumns, $activeOptionalColumns, ['description', 'is_first_payment']);

        $keptTransactionIds = [];
        foreach ($normalized as $idx => $payment) {
            $payment['payment_sequence'] = (int)($payment['payment_sequence'] ?? 0) > 0 ? (int)$payment['payment_sequence'] : ($idx + 1);
            $payment['payment_source_hash'] = $this->buildTransactionPaymentSourceHash($internalDealId, $payment, $idx);
            $rowValues = $this->buildTransactionWriteValues($internalDealId, $payment, $writeColumns);
            $existing = $existingByHash[$payment['payment_source_hash']] ?? null;
            if (!$existing && !empty($unclaimedLegacyTransactions)) {
                $existing = array_shift($unclaimedLegacyTransactions);
            }

            if ($existing && (int)($existing['id'] ?? 0) > 0) {
                $setParts = [];
                $params = [];
                foreach ($writeColumns as $column) {
                    if ($column === 'deal_id') {
                        continue;
                    }
                    $setParts[] = "{$column} = ?";
                    $params[] = $rowValues[$column];
                }
                if ($this->db->hasColumn('transactions', 'status')) {
                    $setParts[] = "status = 'confirmed'";
                }
                $params[] = (int)$existing['id'];
                $update = $pdo->prepare("UPDATE transactions SET " . implode(', ', $setParts) . " WHERE id = ?");
                $update->execute($params);
                $keptTransactionIds[] = (int)$existing['id'];
                continue;
            }

            $insertColumns = $writeColumns;
            $insertValues = array_fill(0, count($insertColumns), '?');
            $insertParams = [];
            foreach ($insertColumns as $column) {
                $insertParams[] = $rowValues[$column];
            }
            $insertColumns[] = 'status';
            $insertValues[] = "'confirmed'";
            if ($hasCreatedAt) {
                $insertColumns[] = 'created_at';
                $insertValues[] = 'COALESCE(?, NOW())';
                $insertParams[] = !empty($payment['registered_at']) ? $payment['registered_at'] : null;
            }
            $insert = $pdo->prepare("
                INSERT INTO transactions (" . implode(', ', $insertColumns) . ")
                VALUES (" . implode(', ', $insertValues) . ")
            ");
            $insert->execute($insertParams);
            $keptTransactionIds[] = (int)$pdo->lastInsertId();
        }

        $this->cancelMissingTransactions($internalDealId, $keptTransactionIds);

        $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 = ?
              AND LOWER(COALESCE(status, 'confirmed')) = 'confirmed'
            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]);
        }

        // C9/Sprint 1: keep transaction ids stable and re-project durable financial status
        // without rebuilding rows. Prefer transaction_id; fall back to legacy check_number.
        if ($this->db->hasColumn('transactions', 'check_status') && $this->db->hasColumn('cheque_reviews', 'id')) {
            try {
                $pdo->prepare("
                    UPDATE transactions t
                    JOIN cheque_reviews cr
                      ON cr.deal_id = t.deal_id
                     AND (
                         cr.transaction_id = t.id
                         OR (cr.transaction_id IS NULL AND cr.check_number IS NOT NULL AND cr.check_number <> '' AND cr.check_number = t.check_number)
                     )
                    SET t.check_status = cr.check_status,
                        t.check_status_note = cr.check_status_note,
                        t.check_status_at = cr.reviewed_at,
                        t.check_status_by = cr.reviewed_by,
                        t.collection_status = cr.collection_status,
                        t.collection_date = cr.collection_date
                    WHERE t.deal_id = ?
                ")->execute([$internalDealId]);
            } catch (\PDOException $e) {
                Logger::logWarning('syncTransactionsFromPayments: cheque status re-attach failed', ['error' => $e->getMessage()]);
            }
        }

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

        // Assign a customer code to the lead when a payment is recorded (idempotent: only once).
        if ($deal && !empty($deal->contact_didar_id)) {
            $this->personRepo->assignCustomerCodeIfMissing((string)$deal->contact_didar_id);
        }

        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)
    {
        $normalized = strtolower(trim((string)$status));
        $resolvedStatus = $normalized === 'won' ? 'Won' : ($normalized === 'lost' ? 'Lost' : $status);

        $pdo = $this->db->getPdo();
        $pdo->beginTransaction();
        try {
            $ok = $this->dealRepo->updateStatus($dealId, $resolvedStatus, $timeField);
            if (!$ok) {
                throw new \RuntimeException('به‌روزرسانی وضعیت معامله انجام نشد');
            }
            $pdo->commit();
        } catch (\Throwable $e) {
            if ($pdo->inTransaction()) {
                $pdo->rollBack();
            }
            throw $e;
        }

        if ($resolvedStatus === 'Won' && $this->commissionService) {
            try {
                $this->commissionService->calculateAndCreateForDeal((string)$dealId);
            } catch (\Throwable $e) {
                Logger::logWarning('Commission calculation failed after deal marked Won', [
                    'deal_id' => (string)$dealId,
                    'error' => $e->getMessage(),
                ]);
            }
        }

        return true;
    }

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

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

    public function moveDealToParked(string $dealId, string $actorDidarId = '', string $reason = ''): void
    {
        $deal = $this->dealRepo->findByDidarId($dealId);
        if (!$deal) {
            throw new \Exception('معامله یافت نشد');
        }
        if ($this->isDealParked($deal)) {
            throw new \Exception('این معامله قبلاً به بخش پارک‌شده منتقل شده است');
        }
        if (trim((string)($deal->status ?? '')) !== 'Pending') {
            throw new \Exception('فقط معاملات جاری قابل انتقال به بخش پارک‌شده هستند');
        }
        $stage = trim((string)($deal->pipeline_stage ?? ''));
        if ($stage === '' || $this->isSpecialPipelineStage($stage)) {
            throw new \Exception('مرحله فعلی این معامله قابل پارک کردن نیست');
        }
        if (!$this->dealRepo->moveToParked($dealId, $stage, $actorDidarId, $reason)) {
            throw new \Exception('انتقال معامله به بخش پارک‌شده انجام نشد');
        }
    }

    public function moveDealOutOfParked(string $dealId, ?string $targetStage = null): void
    {
        $deal = $this->dealRepo->findByDidarId($dealId);
        if (!$deal) {
            throw new \Exception('معامله یافت نشد');
        }
        if (!$this->isDealParked($deal)) {
            throw new \Exception('این معامله در بخش پارک‌شده نیست');
        }

        $resolvedStage = trim((string)($targetStage ?? ''));
        if ($resolvedStage === '') {
            $resolvedStage = trim((string)($deal->parked_from_stage ?? $deal->pipeline_stage ?? ''));
        }
        $this->assertPipelineStageAssignable($resolvedStage, (string)($deal->pipeline_stage ?? ''), true);

        if (!$this->dealRepo->restoreFromParked($dealId, $resolvedStage)) {
            throw new \Exception('بازگرداندن معامله از بخش پارک‌شده انجام نشد');
        }
    }

    public function moveStageDealsToParked(string $stage, string $actorDidarId = '', string $reason = '', array $filters = []): int
    {
        $stage = trim($stage);
        if ($stage === '' || $this->isSpecialPipelineStage($stage)) {
            throw new \Exception('مرحله انتخاب‌شده برای انتقال گروهی معتبر نیست');
        }
        $this->assertPipelineStageAssignable($stage, $stage, true);

        return $this->dealRepo->moveStageDealsToParked($stage, $actorDidarId, $reason, $filters);
    }

    public function moveStageDealsOutOfParked(string $stage, array $filters = []): int
    {
        $stage = trim($stage);
        if ($stage === '' || $this->isSpecialPipelineStage($stage)) {
            throw new \Exception('مرحله انتخاب‌شده برای بازگردانی گروهی معتبر نیست');
        }
        $this->assertPipelineStageAssignable($stage, $stage, true);

        return $this->dealRepo->moveStageDealsOutOfParked($stage, $filters);
    }

    public function validatePipelineStageForAssignment(string $stage, ?string $currentStage = null, bool $allowCurrentInactive = false): void
    {
        $this->assertPipelineStageAssignable($stage, $currentStage, $allowCurrentInactive);
    }

    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, float $unitPriceSnapshot = 0): 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]);

        $hasProductNameSnapshot = $this->db->hasColumn('deal_products', 'product_name_snapshot');
        $hasServiceLookupSnapshot = $this->db->hasColumn('deal_products', 'service_lookup_item_id_snapshot');
        $hasServiceTitleSnapshot = $this->db->hasColumn('deal_products', 'service_title_snapshot');
        $hasUnitPriceSnapshot = $this->db->hasColumn('deal_products', 'unit_price_snapshot');
        $hasProductServiceLink = $this->db->hasColumn('products', 'service_lookup_item_id');

        foreach ($products as $productId) {
            $productId = (int)$productId;
            if ($productId <= 0) {
                continue;
            }

            if ($hasProductNameSnapshot || $hasServiceLookupSnapshot || $hasServiceTitleSnapshot || $hasUnitPriceSnapshot) {
                $columns = ['deal_id', 'product_id'];
                $selects = ['?', 'p.id'];
                $params = [$internalDealId];

                if ($hasProductNameSnapshot) {
                    $columns[] = 'product_name_snapshot';
                    $selects[] = 'p.name';
                }
                if ($hasServiceLookupSnapshot) {
                    $columns[] = 'service_lookup_item_id_snapshot';
                    $selects[] = $hasProductServiceLink ? 'p.service_lookup_item_id' : 'NULL';
                }
                if ($hasServiceTitleSnapshot) {
                    $columns[] = 'service_title_snapshot';
                    $selects[] = $hasProductServiceLink
                        ? "COALESCE(NULLIF(li.title, ''), NULLIF(li.value, ''), p.name)"
                        : "p.name";
                }
                if ($hasUnitPriceSnapshot) {
                    $columns[] = 'unit_price_snapshot';
                    $selects[] = '?';
                    $params[] = $unitPriceSnapshot;
                }
                $columns[] = 'created_at';
                $selects[] = 'NOW()';
                $params[] = $productId;

                $stmt = $pdo->prepare("
                    INSERT INTO deal_products (" . implode(', ', $columns) . ")
                    SELECT " . implode(', ', $selects) . "
                    FROM products p
                    " . ($hasProductServiceLink ? "LEFT JOIN lookup_items li ON li.id = p.service_lookup_item_id" : "") . "
                    WHERE p.id = ?
                ");
                $stmt->execute($params);
                continue;
            }

            $stmt = $pdo->prepare("
                INSERT INTO deal_products (deal_id, product_id, created_at)
                VALUES (?, ?, NOW())
            ");
            $stmt->execute([$internalDealId, $productId]);
        }
    }

    private function buildTransactionPaymentSourceHash(int $internalDealId, array $payment, int $idx): string
    {
        $parts = [
            (string)$internalDealId,
            (string)((int)($payment['payment_sequence'] ?? 0) > 0 ? (int)$payment['payment_sequence'] : ($idx + 1)),
            (string)($payment['registered_at'] ?? ''),
            (string)($payment['payment_date'] ?? ''),
            (string)($payment['method'] ?? ''),
            (string)($payment['check_number'] ?? ''),
            (string)($payment['receipt_image_path'] ?? ''),
            (string)($payment['check_image_path'] ?? ''),
        ];
        return hash('sha256', implode('|', $parts));
    }

    private function buildTransactionWriteValues(int $internalDealId, array $payment, array $columns): array
    {
        $paymentTime = preg_match('/^\d{2}:\d{2}$/', (string)($payment['payment_time'] ?? ''))
            ? $payment['payment_date'] . ' ' . $payment['payment_time'] . ':00'
            : $payment['payment_date'] . ' 00:00:00';
        $values = [
            'deal_id' => $internalDealId,
            'amount' => (float)($payment['amount'] ?? 0),
            'payment_date' => $payment['payment_date'],
            'payment_time' => $paymentTime,
            'method' => $payment['method'],
            'card_last4' => $payment['card_last4'],
            'destination_bank' => $payment['destination_bank'],
            'tracking_code_last4' => $payment['tracking_code_last4'] ?? '',
            'check_owner_name' => $payment['check_owner_name'] ?? '',
            'destination_card_number' => $payment['destination_card_number'] ?? null,
            'destination_bank_title' => $payment['destination_bank_title'] ?? null,
            'destination_card_full' => $payment['destination_card_full'] ?? null,
            'destination_account_number' => $payment['destination_account_number'] ?? null,
            'destination_sheba' => $payment['destination_sheba'] ?? null,
            'destination_account_owner' => $payment['destination_account_owner'] ?? null,
            'account_owner_name' => $payment['account_owner_name'] ?? null,
            'depositor_name' => $payment['depositor_name'] ?? null,
            'deposit_date' => $payment['deposit_date'] ?? null,
            'check_bank_name' => $payment['check_bank_name'] ?? null,
            'check_number' => $payment['check_number'] ?? null,
            'check_date' => $payment['check_date'] ?? null,
            'check_due_date' => $payment['check_due_date'] ?? null,
            'sayadi_date' => $payment['sayadi_date'] ?? null,
            'check_delivery_date' => $payment['check_delivery_date'] ?? null,
            'check_recipient_name' => $payment['check_recipient_name'] ?? null,
            'receipt_image_path' => $payment['receipt_image_path'] ?? null,
            'check_image_path' => $payment['check_image_path'] ?? null,
            'payment_sequence' => (int)($payment['payment_sequence'] ?? 0) ?: null,
            'payment_source_hash' => $payment['payment_source_hash'] ?? null,
            'registered_by_didar_id' => $payment['registered_by_didar_id'] ?? null,
            'registered_by_name' => $payment['registered_by_name'] ?? null,
            'payment_owner_user_id' => (int)($payment['payment_owner_user_id'] ?? 0) ?: null,
            'payment_owner_didar_id' => $payment['payment_owner_didar_id'] ?? null,
            'payment_owner_name_snapshot' => $payment['payment_owner_name_snapshot'] ?? null,
            'payment_owner_locked_at' => $payment['payment_owner_locked_at'] ?? null,
            'refund_amount' => (float)($payment['refund_amount'] ?? 0),
            'refund_count' => (int)($payment['refund_count'] ?? 0),
            'refund_description' => $payment['refund_description'] ?? null,
            'description' => $payment['description'],
            'is_first_payment' => 0,
        ];

        $row = [];
        foreach ($columns as $column) {
            $value = $values[$column] ?? null;
            $row[$column] = $value === '' ? null : $value;
        }
        return $row;
    }

    private function cancelMissingTransactions(int $internalDealId, array $keptTransactionIds): void
    {
        if (!$this->db->hasColumn('transactions', 'status')) {
            return;
        }
        $pdo = $this->db->getPdo();
        $params = [$internalDealId];
        $sql = "UPDATE transactions SET status = 'cancelled', is_first_payment = 0 WHERE deal_id = ? AND LOWER(COALESCE(status, 'confirmed')) <> 'cancelled'";
        if (!empty($keptTransactionIds)) {
            $placeholders = implode(',', array_fill(0, count($keptTransactionIds), '?'));
            $sql .= " AND id NOT IN ({$placeholders})";
            $params = array_merge($params, array_map('intval', $keptTransactionIds));
        }
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
    }

    private function hasSuccessfulPayment(array $payments): bool
    {
        foreach ($payments as $payment) {
            $grossAmount = isset($payment['amount']) ? max(0.0, floatval($payment['amount'])) : 0.0;
            $refundAmount = isset($payment['refund_amount']) ? max(0.0, floatval($payment['refund_amount'])) : 0.0;
            $refundAmount = min($grossAmount, $refundAmount);
            $amount = max(0.0, $grossAmount - $refundAmount);
            $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;
            }
            if (!empty($payment['deleted']) || !empty($payment['is_deleted'])) {
                return null;
            }
            $grossAmount = isset($payment['amount']) ? max(0.0, floatval($payment['amount'])) : 0.0;
            $refundAmount = isset($payment['refund_amount']) ? max(0.0, floatval($payment['refund_amount'])) : 0.0;
            $refundAmount = min($grossAmount, $refundAmount);
            $amount = max(0.0, $grossAmount - $refundAmount);
            $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'] ?? ''));
            $depositorName = trim((string)($payment['depositor_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');
            }

            // Sprint fields projected into transactions for reporting + cheque workflow (C9).
            // Only convert dates that were actually provided; leave blanks as null (do not
            // fall back to today, which normalizePaymentDate does for empty input).
            $optionalDate = function ($greg, $persian) {
                $greg = trim((string)$greg);
                $persian = trim((string)$persian);
                if ($greg === '' && $persian === '') {
                    return null;
                }
                return $this->normalizePaymentDate($greg, $persian) ?: null;
            };
            $destinationCardNumber = preg_replace('/\D+/', '', (string)($payment['destination_card_number'] ?? ''));
            if (strlen($destinationCardNumber) > 4) {
                $destinationCardNumber = substr($destinationCardNumber, -4);
            }
            $destinationCardFull = preg_replace('/\D+/', '', (string)($payment['destination_card_full'] ?? ''));
            $destinationAccountNumber = preg_replace('/\s+/', '', (string)($payment['destination_account_number'] ?? ''));
            $destinationSheba = strtoupper(preg_replace('/\s+/', '', (string)($payment['destination_sheba'] ?? '')));
            $destinationAccountOwner = trim((string)($payment['destination_account_owner'] ?? ''));
            $depositDate = $optionalDate($payment['deposit_date'] ?? '', $payment['deposit_date_persian'] ?? '');
            $checkDueDate = $optionalDate($payment['check_due_date'] ?? '', $payment['check_due_date_persian'] ?? '');
            $checkDeliveryDate = $optionalDate($payment['check_delivery_date'] ?? '', $payment['check_delivery_date_persian'] ?? '');
            $checkPayDate = $optionalDate($payment['check_date'] ?? '', '');
            $registeredAt = trim((string)($payment['registered_at'] ?? ''));
            $paymentSequence = max(0, (int)($payment['payment_sequence'] ?? 0));
            $refundAmount = isset($payment['refund_amount']) ? max(0.0, (float)$payment['refund_amount']) : 0.0;
            $refundAmount = min($grossAmount, $refundAmount);
            $refundCount = isset($payment['refund_count']) ? max(0, (int)$payment['refund_count']) : ($refundAmount > 0 ? 1 : 0);

            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,
                'destination_card_number' => $destinationCardNumber,
                'destination_bank_title' => trim((string)($payment['destination_bank_title'] ?? '')),
                'destination_card_full' => $destinationCardFull,
                'destination_account_number' => $destinationAccountNumber,
                'destination_sheba' => $destinationSheba,
                'destination_account_owner' => $destinationAccountOwner,
                'account_owner_name' => $accountOwnerName,
                'depositor_name' => $depositorName,
                'deposit_date' => $depositDate ?: null,
                'check_bank_name' => trim((string)($payment['check_bank_name'] ?? '')),
                'check_number' => trim((string)($payment['check_number'] ?? '')),
                'check_date' => $checkPayDate ?: null,
                'check_due_date' => $checkDueDate ?: null,
                'sayadi_date' => trim((string)($payment['sayadi_date'] ?? '')),
                'check_delivery_date' => $checkDeliveryDate ?: null,
                'check_recipient_name' => trim((string)($payment['check_recipient_name'] ?? '')),
                'receipt_image_path' => trim((string)($payment['receipt_image_path'] ?? '')),
                'check_image_path' => trim((string)($payment['check_image_path'] ?? '')),
                'registered_at' => $registeredAt !== '' ? $registeredAt : null,
                'payment_sequence' => $paymentSequence,
                'registered_by_didar_id' => trim((string)($payment['registered_by_didar_id'] ?? '')),
                'registered_by_name' => trim((string)($payment['registered_by_name'] ?? '')),
                'payment_owner_user_id' => (int)($payment['payment_owner_user_id'] ?? 0) ?: null,
                'payment_owner_didar_id' => trim((string)($payment['payment_owner_didar_id'] ?? '')),
                'payment_owner_name_snapshot' => trim((string)($payment['payment_owner_name_snapshot'] ?? '')),
                'payment_owner_locked_at' => trim((string)($payment['payment_owner_locked_at'] ?? '')),
                'refund_amount' => $refundAmount,
                'refund_count' => $refundCount,
                'refund_description' => trim((string)($payment['refund_description'] ?? '')),
            ];
        }, $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
    {
        $this->assertPipelineStageAssignable($newStage);
        $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,
                pipeline_bucket = 'active',
                parked_from_stage = NULL,
                parked_at = NULL,
                parked_by_didar_id = NULL,
                parked_reason = 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);
    }

    private function assertPipelineStageAssignable(string $stage, ?string $currentStage = null, bool $allowCurrentInactive = false): void
    {
        $stage = trim($stage);
        if ($stage === '' || $this->isSpecialPipelineStage($stage) || !$this->lookupRepo) {
            return;
        }

        $summary = $this->getPipelineStageLookupSummary();
        $stageKey = $this->normalizePipelineStageComparable($stage);
        $currentStageKey = $this->normalizePipelineStageComparable((string)$currentStage);

        if (isset($summary['active'][$stageKey])) {
            return;
        }

        if (
            $allowCurrentInactive &&
            $currentStageKey !== '' &&
            $currentStageKey === $stageKey &&
            isset($summary['inactive'][$stageKey])
        ) {
            return;
        }

        if (isset($summary['inactive'][$stageKey])) {
            throw new \Exception('مرحله کاریز انتخاب‌شده غیرفعال است و دیگر قابل استفاده نیست');
        }

        throw new \Exception('مرحله کاریز انتخاب‌شده معتبر نیست');
    }

    private function isSpecialPipelineStage(string $stage): bool
    {
        return in_array(trim($stage), [
            'اتمام',
            'deal_success',
            'deal_failed',
            'معامله موفق',
            'موفق',
            'معامله ناموفق',
            'ناموفق',
        ], true);
    }

    private function getPipelineStageLookupSummary(): array
    {
        if ($this->pipelineStageLookupCache !== null) {
            return $this->pipelineStageLookupCache;
        }

        $summary = [
            'active' => [],
            'inactive' => [],
        ];

        $didarUserId = trim((string)($_SESSION['didar_id'] ?? ''));
        foreach ($this->getLookupItems('pipeline_stages', true) as $item) {
            $value = trim((string)($item['value'] ?? $item['title'] ?? ''));
            if ($value === '') {
                continue;
            }

            $key = $this->normalizePipelineStageComparable($value);
            $isActive = (int)($item['is_active'] ?? 1) === 1 && (int)($item['is_visible'] ?? 1) === 1;
            if (
                $isActive &&
                $didarUserId !== '' &&
                $this->lookupRepo &&
                $this->lookupRepo->isLookupItemDisabledForDidarUser((array)$item, $didarUserId)
            ) {
                $isActive = false;
            }

            if ($isActive) {
                $summary['active'][$key] = $value;
                unset($summary['inactive'][$key]);
                continue;
            }

            if (!isset($summary['active'][$key])) {
                $summary['inactive'][$key] = $value;
            }
        }

        $this->pipelineStageLookupCache = $summary;
        return $summary;
    }

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

        return preg_replace('/\s+/u', ' ', $value) ?? $value;
    }
}
