<?php
namespace App\Services;

use App\Utils\DateNormalizer;

class PaymentRecordsBackfillService
{
    private $app;
    private \PDO $pdo;
    private string $mode;
    private int $limit;
    private array $usersByDidar = [];
    private array $candidateByHash = [];
    private array $jsonMatchByDealAndKey = [];
    private array $matchedTransactionIds = [];
    private array $report;

    public function __construct($app, string $mode, int $limit)
    {
        $this->app = $app;
        $this->pdo = $app->db->getPdo();
        $this->mode = $mode;
        $this->limit = $limit;
        $this->report = [
            'mode' => $mode,
            'success' => true,
            'sources' => [
                'deal_json' => ['scanned' => 0, 'candidates' => 0, 'skipped_deleted' => 0, 'skipped_invalid' => 0],
                'transactions' => ['scanned' => 0, 'candidates' => 0, 'matched_to_deal_json' => 0, 'skipped_cancelled' => 0, 'skipped_invalid' => 0],
                'deal_payments' => ['scanned' => 0, 'candidates' => 0, 'skipped_invalid' => 0],
            ],
            'writes' => ['created_or_updated' => 0, 'would_create_or_update' => 0, 'links' => 0, 'transaction_projection_updates' => 0],
            'verify' => [],
            'warnings' => [],
            'report_path' => null,
        ];
    }

    public function execute(): array
    {
        $this->loadUsers();
        $this->collectDealJsonCandidates();
        $this->collectTransactionCandidates();
        $this->collectDealPaymentCandidates();

        if ($this->mode === 'run') {
            $this->writeCandidates();
        } elseif ($this->mode === 'dry-run') {
            $this->report['writes']['would_create_or_update'] = count($this->candidateByHash);
        }

        $this->verify();
        $this->writeReport();
        return $this->report;
    }

    private function collectDealJsonCandidates(): void
    {
        $sql = "
            SELECT d.id, d.didar_deal_id, d.contact_didar_id, d.owner_didar_id, d.payments
            FROM deals d
            WHERE d.payments IS NOT NULL
              AND d.payments <> ''
              AND d.payments <> '[]'
            ORDER BY d.id ASC
        ";
        if ($this->limit > 0) {
            $sql .= ' LIMIT ' . $this->limit;
        }
        $stmt = $this->pdo->query($sql);
        foreach ($stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [] as $deal) {
            $payments = json_decode((string)($deal['payments'] ?? ''), true);
            if (!is_array($payments)) {
                $this->report['sources']['deal_json']['skipped_invalid']++;
                continue;
            }
            $sequence = 0;
            foreach ($payments as $payment) {
                $this->report['sources']['deal_json']['scanned']++;
                $sequence++;
                if (!is_array($payment)) {
                    $this->report['sources']['deal_json']['skipped_invalid']++;
                    continue;
                }
                if (!empty($payment['deleted']) || !empty($payment['is_deleted'])) {
                    $this->report['sources']['deal_json']['skipped_deleted']++;
                    continue;
                }
                $candidate = $this->candidateFromDealJsonPayment($deal, $payment, $sequence);
                if (!$candidate) {
                    $this->report['sources']['deal_json']['skipped_invalid']++;
                    continue;
                }
                $this->addCandidate($candidate);
                $this->jsonMatchByDealAndKey[(int)$deal['id']][$candidate['_legacy_match_key']] = $candidate['source_hash'];
                $this->report['sources']['deal_json']['candidates']++;
            }
        }
    }

    private function collectTransactionCandidates(): void
    {
        $sql = "
            SELECT t.*,
                   d.didar_deal_id,
                   d.contact_didar_id,
                   d.owner_didar_id,
                   cr.id AS cheque_review_id,
                   cr.check_status AS review_check_status,
                   cr.collection_status AS review_collection_status,
                   cr.collection_date AS review_collection_date,
                   pr.id AS payment_review_id,
                   pr.sepidar_registered_at AS review_sepidar_registered_at
            FROM transactions t
            INNER JOIN deals d ON d.id = t.deal_id
            LEFT JOIN cheque_reviews cr ON cr.transaction_id = t.id
            LEFT JOIN payment_reviews pr ON pr.transaction_id = t.id
            ORDER BY t.id ASC
        ";
        if ($this->limit > 0) {
            $sql .= ' LIMIT ' . $this->limit;
        }
        $stmt = $this->pdo->query($sql);
        foreach ($stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [] as $row) {
            $this->report['sources']['transactions']['scanned']++;
            if (strtolower(trim((string)($row['status'] ?? 'confirmed'))) === 'cancelled') {
                $this->report['sources']['transactions']['skipped_cancelled']++;
                continue;
            }
            $matchKey = $this->buildLegacyMatchKey((int)$row['deal_id'], (float)$row['amount'], (string)$row['payment_date'], (string)$row['method'], (string)($row['check_number'] ?? ''), (string)($row['receipt_image_path'] ?? ''));
            $jsonHash = $this->jsonMatchByDealAndKey[(int)$row['deal_id']][$matchKey] ?? '';
            if ($jsonHash !== '' && isset($this->candidateByHash[$jsonHash])) {
                $this->applyTransactionProjection($this->candidateByHash[$jsonHash], $row);
                $this->matchedTransactionIds[(int)$row['id']] = true;
                $this->report['sources']['transactions']['matched_to_deal_json']++;
                continue;
            }
            $candidate = $this->candidateFromTransaction($row);
            if (!$candidate) {
                $this->report['sources']['transactions']['skipped_invalid']++;
                continue;
            }
            $this->addCandidate($candidate);
            $this->matchedTransactionIds[(int)$row['id']] = true;
            $this->report['sources']['transactions']['candidates']++;
        }
    }

    private function collectDealPaymentCandidates(): void
    {
        if (!$this->tableExists('deal_payments')) {
            return;
        }
        $sql = "
            SELECT dp.*, d.id AS deal_id, d.contact_didar_id, d.owner_didar_id
            FROM deal_payments dp
            LEFT JOIN deals d ON d.didar_deal_id = dp.deal_didar_id
            ORDER BY dp.id ASC
        ";
        if ($this->limit > 0) {
            $sql .= ' LIMIT ' . $this->limit;
        }
        $stmt = $this->pdo->query($sql);
        foreach ($stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [] as $row) {
            $this->report['sources']['deal_payments']['scanned']++;
            $candidate = $this->candidateFromDealPayment($row);
            if (!$candidate) {
                $this->report['sources']['deal_payments']['skipped_invalid']++;
                continue;
            }
            $this->addCandidate($candidate);
            $this->report['sources']['deal_payments']['candidates']++;
        }
    }

    private function writeCandidates(): void
    {
        $repo = $this->app->repositories->paymentRecord;
        foreach ($this->candidateByHash as $candidate) {
            $links = $candidate['_links'] ?? [];
            unset($candidate['_links'], $candidate['_legacy_match_key'], $candidate['_transaction_projection']);
            $id = $repo->upsertFromLegacySource($candidate);
            if ($id <= 0) {
                $this->report['warnings'][] = ['type' => 'write_failed', 'source_hash' => $candidate['source_hash']];
                continue;
            }
            $this->report['writes']['created_or_updated']++;
            foreach ($links as $link) {
                $repo->link($id, $link['table'], $link['id'], $link['type']);
                $this->report['writes']['links']++;
                if ($link['table'] === 'transactions') {
                    $this->updateTransactionProjection((int)$link['id'], $id, $candidate);
                }
            }
        }
    }

    private function candidateFromDealJsonPayment(array $deal, array $payment, int $sequence): ?array
    {
        $grossAmount = max(0.0, (float)($payment['amount'] ?? 0));
        $refundAmount = min($grossAmount, max(0.0, (float)($payment['refund_amount'] ?? 0)));
        $amount = max(0.0, $grossAmount - $refundAmount);
        $paymentDate = $this->normalizeDate((string)($payment['date'] ?? ''), (string)($payment['date_persian'] ?? ''));
        if ($amount <= 0 || $paymentDate === null) {
            return null;
        }
        $method = trim((string)($payment['method'] ?? $payment['payment_method'] ?? ''));
        $registeredAt = $this->normalizeDateTime((string)($payment['registered_at'] ?? ''), $paymentDate);
        $depositDate = $this->normalizeDate((string)($payment['deposit_date'] ?? ''), (string)($payment['deposit_date_persian'] ?? ''));
        $checkNumber = trim((string)($payment['check_number'] ?? ''));
        $receiptPath = trim((string)($payment['receipt_image_path'] ?? ''));
        $sourceHash = hash('sha256', implode('|', [
            'DEAL_JSON',
            (string)($deal['didar_deal_id'] ?? ''),
            (string)$sequence,
            $this->money($amount),
            $paymentDate,
            $method,
            $registeredAt,
            $checkNumber,
            $receiptPath,
        ]));
        $owner = $this->resolvePaymentOwner(
            (string)($payment['registered_by_didar_id'] ?? ''),
            (string)($payment['registered_by_name'] ?? ''),
            (string)($deal['owner_didar_id'] ?? '')
        );
        $isCheque = $this->isChequeMethod($method, $checkNumber, (string)($payment['check_image_path'] ?? ''));
        $chequeDueDate = $this->normalizeDate((string)($payment['check_due_date'] ?? ''), (string)($payment['check_due_date_persian'] ?? '')) ?: ($isCheque ? $paymentDate : null);
        $paymentUuid = trim((string)($payment['payment_uuid'] ?? $payment['uuid'] ?? ''));
        if ($paymentUuid === '') {
            $paymentUuid = $this->uuidFromHash($sourceHash);
        }
        $candidate = [
            'source_type' => 'DEAL_JSON',
            'source_id' => (int)$deal['id'] . ':' . $sequence,
            'source_hash' => $sourceHash,
            'deal_id' => (int)$deal['id'],
            'deal_didar_id' => (string)($deal['didar_deal_id'] ?? ''),
            'contact_didar_id' => (string)($deal['contact_didar_id'] ?? ''),
            'payment_uuid' => $paymentUuid,
            'payment_sequence' => $sequence,
            'amount' => $amount,
            'currency' => (string)($payment['currency'] ?? 'IRR'),
            'method' => $method,
            'payment_date' => $paymentDate,
            'deposit_date' => $depositDate,
            'effective_date' => $isCheque ? null : ($depositDate ?: $paymentDate),
            'registered_at' => $registeredAt,
            'is_deleted_placeholder' => 0,
            'is_refunded' => $refundAmount > 0 ? 1 : 0,
            'refunded_amount' => $refundAmount,
            'refund_status' => $refundAmount <= 0 ? 'NONE' : ($refundAmount >= $grossAmount ? 'FULL' : 'PARTIAL'),
            'payment_owner_user_id' => $owner['user_id'],
            'payment_owner_didar_id' => $owner['didar_id'],
            'payment_owner_name_snapshot' => $owner['name'],
            'payment_owner_locked_at' => $registeredAt,
            'payment_owner_source' => $owner['source'],
            'cheque_status' => $isCheque ? 'pending' : 'not_cheque',
            'cheque_collection_status' => $isCheque ? 'pending' : 'not_cheque',
            'cheque_due_date' => $chequeDueDate,
            'cheque_collection_date' => null,
            'finance_status' => 'NONE',
            'commission_status' => ($refundAmount >= $grossAmount || $this->isSudranGatewayMethod($method)) ? 'NOT_ELIGIBLE' : 'PENDING',
            'raw_snapshot' => ['source' => 'deals.payments', 'payment' => $payment],
            '_legacy_match_key' => $this->buildLegacyMatchKey((int)$deal['id'], $amount, $paymentDate, $method, $checkNumber, $receiptPath),
            '_links' => [
                ['table' => 'deals', 'id' => (string)(int)$deal['id'], 'type' => 'SOURCE'],
            ],
        ];
        return $candidate;
    }

    private function candidateFromTransaction(array $row): ?array
    {
        $amount = max(0.0, (float)($row['amount'] ?? 0));
        $paymentDate = $this->normalizeDate((string)($row['payment_date'] ?? ''), '');
        $depositDate = $this->normalizeDate((string)($row['deposit_date'] ?? ''), '');
        if ($amount <= 0 || $paymentDate === null) {
            return null;
        }
        $method = trim((string)($row['method'] ?? ''));
        $checkNumber = trim((string)($row['check_number'] ?? ''));
        $isCheque = $this->isChequeMethod($method, $checkNumber, (string)($row['check_image_path'] ?? ''));
        $sourceHash = trim((string)($row['payment_source_hash'] ?? ''));
        if ($sourceHash === '') {
            $sourceHash = hash('sha256', 'TRANSACTION|' . (int)$row['id']);
        }
        $owner = $this->resolvePaymentOwner(
            (string)($row['registered_by_didar_id'] ?? ''),
            (string)($row['registered_by_name'] ?? ''),
            (string)($row['owner_didar_id'] ?? '')
        );
        $paymentUuid = trim((string)($row['payment_uuid'] ?? ''));
        if ($paymentUuid === '') {
            $paymentUuid = $this->uuidFromHash($sourceHash);
        }
        $candidate = [
            'source_type' => 'TRANSACTION',
            'source_id' => (string)(int)$row['id'],
            'source_hash' => $sourceHash,
            'deal_id' => (int)$row['deal_id'],
            'deal_didar_id' => (string)($row['didar_deal_id'] ?? ''),
            'contact_didar_id' => (string)($row['contact_didar_id'] ?? ''),
            'payment_uuid' => $paymentUuid,
            'payment_sequence' => isset($row['payment_sequence']) ? (int)$row['payment_sequence'] : null,
            'amount' => $amount,
            'currency' => 'IRR',
            'method' => $method,
            'payment_date' => $paymentDate,
            'deposit_date' => $depositDate,
            'effective_date' => $isCheque ? null : ($depositDate ?: $paymentDate),
            'registered_at' => $this->normalizeDateTime((string)($row['created_at'] ?? $row['payment_time'] ?? ''), $paymentDate),
            'is_deleted_placeholder' => 0,
            'is_refunded' => ((float)($row['refund_amount'] ?? 0)) > 0 ? 1 : 0,
            'refunded_amount' => max(0.0, (float)($row['refund_amount'] ?? 0)),
            'refund_status' => ((float)($row['refund_amount'] ?? 0)) <= 0 ? 'NONE' : (((float)$row['refund_amount'] >= $amount) ? 'FULL' : 'PARTIAL'),
            'payment_owner_user_id' => $owner['user_id'],
            'payment_owner_didar_id' => $owner['didar_id'],
            'payment_owner_name_snapshot' => $owner['name'],
            'payment_owner_locked_at' => $this->normalizeDateTime((string)($row['created_at'] ?? $row['payment_time'] ?? ''), $paymentDate),
            'payment_owner_source' => $owner['source'],
            'cheque_status' => $isCheque ? 'pending' : 'not_cheque',
            'cheque_collection_status' => $isCheque ? 'pending' : 'not_cheque',
            'cheque_due_date' => $this->normalizeDate((string)($row['check_due_date'] ?? $row['check_date'] ?? ''), ''),
            'cheque_collection_date' => null,
            'finance_status' => 'NONE',
            'commission_status' => $this->isSudranGatewayMethod($method) ? 'NOT_ELIGIBLE' : 'PENDING',
            'raw_snapshot' => ['source' => 'transactions', 'transaction_id' => (int)$row['id']],
            '_links' => [
                ['table' => 'transactions', 'id' => (string)(int)$row['id'], 'type' => 'PROJECTION'],
            ],
        ];
        $this->applyTransactionProjection($candidate, $row);
        return $candidate;
    }

    private function candidateFromDealPayment(array $row): ?array
    {
        $amount = max(0.0, (float)($row['payment_amount'] ?? 0));
        if ($amount <= 0) {
            return null;
        }
        $type = strtoupper(trim((string)($row['payment_type'] ?? 'CASH')));
        $paymentDate = $this->normalizeDate((string)($row['receipt_date'] ?? $row['created_at'] ?? ''), '');
        $depositDate = $this->normalizeDate((string)($row['deposit_date'] ?? $row['cheque_cashed_date'] ?? $row['receipt_date'] ?? ''), '');
        if ($paymentDate === null) {
            $paymentDate = $depositDate;
        }
        if ($paymentDate === null) {
            return null;
        }
        $isCheque = $type === 'CHEQUE';
        $status = strtoupper(trim((string)($row['payment_status'] ?? 'PENDING')));
        $collectionDate = $this->normalizeDate((string)($row['cheque_cashed_date'] ?? ''), '');
        $sourceHash = hash('sha256', 'DEAL_PAYMENT|' . (int)$row['id']);
        $owner = $this->resolvePaymentOwner('', '', (string)($row['owner_didar_id'] ?? ''));
        return [
            'source_type' => 'DEAL_PAYMENT',
            'source_id' => (string)(int)$row['id'],
            'source_hash' => $sourceHash,
            'deal_id' => isset($row['deal_id']) ? (int)$row['deal_id'] : null,
            'deal_didar_id' => (string)($row['deal_didar_id'] ?? ''),
            'contact_didar_id' => (string)($row['contact_didar_id'] ?? ''),
            'payment_uuid' => $this->uuidFromHash($sourceHash),
            'amount' => $amount,
            'currency' => 'IRR',
            'method' => strtolower($type),
            'payment_date' => $paymentDate,
            'deposit_date' => $depositDate,
            'effective_date' => $isCheque ? ($status === 'CHEQUE_CASHED' ? $collectionDate : null) : ($depositDate ?: $paymentDate),
            'registered_at' => $this->normalizeDateTime((string)($row['created_at'] ?? ''), $paymentDate),
            'is_deleted_placeholder' => 0,
            'is_refunded' => 0,
            'refunded_amount' => 0,
            'refund_status' => 'NONE',
            'payment_owner_user_id' => $owner['user_id'],
            'payment_owner_didar_id' => $owner['didar_id'],
            'payment_owner_name_snapshot' => $owner['name'],
            'payment_owner_locked_at' => $this->normalizeDateTime((string)($row['created_at'] ?? ''), $paymentDate),
            'payment_owner_source' => $owner['source'],
            'cheque_status' => $isCheque ? ($status === 'REJECTED' ? 'rejected' : ($status === 'CHEQUE_CASHED' ? 'approved' : 'pending')) : 'not_cheque',
            'cheque_collection_status' => $isCheque ? ($status === 'CHEQUE_CASHED' ? 'collected' : 'pending') : 'not_cheque',
            'cheque_due_date' => $this->normalizeDate((string)($row['cheque_due_date'] ?? ''), ''),
            'cheque_collection_date' => $collectionDate,
            'finance_status' => 'NONE',
            'commission_status' => $this->isSudranGatewayMethod(strtolower($type)) ? 'NOT_ELIGIBLE' : 'PENDING',
            'raw_snapshot' => ['source' => 'deal_payments', 'deal_payment_id' => (int)$row['id']],
            '_links' => [
                ['table' => 'deal_payments', 'id' => (string)(int)$row['id'], 'type' => 'SOURCE'],
            ],
        ];
    }

    private function applyTransactionProjection(array &$candidate, array $row): void
    {
        $transactionId = (int)($row['id'] ?? 0);
        if ($transactionId > 0) {
            $candidate['_links'][] = ['table' => 'transactions', 'id' => (string)$transactionId, 'type' => 'PROJECTION'];
        }
        if (!empty($row['cheque_review_id'])) {
            $candidate['_links'][] = ['table' => 'cheque_reviews', 'id' => (string)(int)$row['cheque_review_id'], 'type' => 'REVIEW'];
        }
        if (!empty($row['payment_review_id'])) {
            $candidate['_links'][] = ['table' => 'payment_reviews', 'id' => (string)(int)$row['payment_review_id'], 'type' => 'REVIEW'];
        }
        $isCheque = $this->isChequeMethod((string)($row['method'] ?? $candidate['method'] ?? ''), (string)($row['check_number'] ?? ''), (string)($row['check_image_path'] ?? ''));
        if ($isCheque) {
            $candidate['cheque_status'] = strtolower((string)($row['review_check_status'] ?? $row['check_status'] ?? 'pending')) ?: 'pending';
            $candidate['cheque_collection_status'] = strtolower((string)($row['review_collection_status'] ?? $row['collection_status'] ?? 'pending')) ?: 'pending';
            $candidate['cheque_collection_date'] = $this->normalizeDate((string)($row['review_collection_date'] ?? $row['collection_date'] ?? ''), '');
            $candidate['cheque_due_date'] = $this->normalizeDate((string)($row['check_due_date'] ?? $row['check_date'] ?? ''), '');
            $candidate['effective_date'] = $candidate['cheque_collection_status'] === 'collected' ? $candidate['cheque_collection_date'] : null;
        }
        if (!empty($row['review_sepidar_registered_at'])) {
            $candidate['finance_status'] = 'SEPIDAR_REGISTERED';
            $candidate['sepidar_registered_at'] = (string)$row['review_sepidar_registered_at'];
        }
        $candidate['_transaction_projection'] = $transactionId;
    }

    private function updateTransactionProjection(int $transactionId, int $paymentRecordId, array $candidate): void
    {
        if ($transactionId <= 0 || !$this->tableExists('transactions')) {
            return;
        }
        $columns = [
            'payment_record_id' => $paymentRecordId,
            'payment_uuid' => $candidate['payment_uuid'] ?? null,
            'payment_owner_user_id' => $candidate['payment_owner_user_id'] ?? null,
            'payment_owner_didar_id' => $candidate['payment_owner_didar_id'] ?? null,
            'payment_owner_name_snapshot' => $candidate['payment_owner_name_snapshot'] ?? null,
            'payment_owner_locked_at' => $candidate['payment_owner_locked_at'] ?? null,
            'commission_status' => $candidate['commission_status'] ?? 'PENDING',
            'commission_effective_date' => $candidate['effective_date'] ?? null,
            'payment_source_hash' => $candidate['source_hash'] ?? null,
        ];
        $set = [];
        $params = [];
        foreach ($columns as $column => $value) {
            if (!$this->columnExists('transactions', $column)) {
                continue;
            }
            if ($column === 'payment_source_hash') {
                $set[] = "{$column} = COALESCE(NULLIF({$column}, ''), ?)";
            } else {
                $set[] = "{$column} = ?";
            }
            $params[] = $value;
        }
        if (empty($set)) {
            return;
        }
        $params[] = $transactionId;
        $stmt = $this->pdo->prepare('UPDATE transactions SET ' . implode(', ', $set) . ' WHERE id = ?');
        $stmt->execute($params);
        $this->report['writes']['transaction_projection_updates']++;
    }

    private function addCandidate(array $candidate): void
    {
        $hash = (string)$candidate['source_hash'];
        if (isset($this->candidateByHash[$hash])) {
            $this->candidateByHash[$hash]['_links'] = $this->mergeLinks(
                $this->candidateByHash[$hash]['_links'] ?? [],
                $candidate['_links'] ?? []
            );
            return;
        }
        $this->candidateByHash[$hash] = $candidate;
    }

    private function mergeLinks(array $a, array $b): array
    {
        $seen = [];
        $out = [];
        foreach (array_merge($a, $b) as $link) {
            $key = ($link['table'] ?? '') . ':' . ($link['id'] ?? '') . ':' . ($link['type'] ?? '');
            if ($key === '::' || isset($seen[$key])) {
                continue;
            }
            $seen[$key] = true;
            $out[] = $link;
        }
        return $out;
    }

    private function verify(): void
    {
        $hashes = array_keys($this->candidateByHash);
        $existing = [];
        foreach (array_chunk($hashes, 500) as $chunk) {
            if (empty($chunk)) {
                continue;
            }
            $stmt = $this->pdo->prepare('SELECT source_hash FROM payment_records WHERE source_hash IN (' . implode(',', array_fill(0, count($chunk), '?')) . ')');
            $stmt->execute($chunk);
            foreach ($stmt->fetchAll(\PDO::FETCH_COLUMN) ?: [] as $hash) {
                $existing[(string)$hash] = true;
            }
        }
        $dupSource = (int)$this->pdo->query("SELECT COUNT(*) FROM (SELECT source_hash FROM payment_records GROUP BY source_hash HAVING COUNT(*) > 1) x")->fetchColumn();
        $dupUuid = (int)$this->pdo->query("SELECT COUNT(*) FROM (SELECT payment_uuid FROM payment_records GROUP BY payment_uuid HAVING COUNT(*) > 1) x")->fetchColumn();
        $missing = count(array_diff($hashes, array_keys($existing)));
        $orphanTransactions = 0;
        if ($this->tableExists('transactions')) {
            $orphanTransactions = (int)$this->pdo->query("
                SELECT COUNT(*)
                FROM transactions t
                LEFT JOIN payment_record_links l
                  ON l.linked_table = 'transactions'
                 AND l.linked_id = (CAST(t.id AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_unicode_ci)
                 AND l.link_type = 'PROJECTION'
                WHERE LOWER(COALESCE(t.status, 'confirmed')) <> 'cancelled'
                  AND l.id IS NULL
            ")->fetchColumn();
        }
        $this->report['verify'] = [
            'candidate_hashes' => count($hashes),
            'existing_candidate_hashes' => count($existing),
            'missing_candidate_hashes' => $missing,
            'duplicate_source_hash_groups' => $dupSource,
            'duplicate_payment_uuid_groups' => $dupUuid,
            'orphan_active_transactions' => $orphanTransactions,
        ];
        if ($this->mode === 'verify' || $this->mode === 'run') {
            $this->report['success'] = ($missing === 0 && $dupSource === 0 && $dupUuid === 0);
        }
    }

    private function writeReport(): void
    {
        $dir = __DIR__ . '/../../output/payment-ledger';
        if (!is_dir($dir)) {
            mkdir($dir, 0777, true);
        }
        $path = $dir . '/backfill_payment_records_' . date('Ymd_His') . '_' . $this->mode . '.json';
        $this->report['report_path'] = realpath($dir) . DIRECTORY_SEPARATOR . basename($path);
        file_put_contents($path, json_encode($this->report, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES));
    }

    private function loadUsers(): void
    {
        $stmt = $this->pdo->query("
            SELECT id, didar_user_id, COALESCE(NULLIF(display_name, ''), CONCAT(COALESCE(first_name, ''), ' ', COALESCE(last_name, '')), username, didar_user_id) AS name
            FROM users
        ");
        foreach ($stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [] as $row) {
            $didar = trim((string)($row['didar_user_id'] ?? ''));
            if ($didar === '') {
                continue;
            }
            $this->usersByDidar[$didar] = [
                'id' => (int)$row['id'],
                'name' => trim((string)($row['name'] ?? '')),
            ];
        }
    }

    private function resolvePaymentOwner(string $directDidarId, string $directName, string $dealOwnerDidarId): array
    {
        $directDidarId = trim($directDidarId);
        if ($directDidarId !== '') {
            $user = $this->usersByDidar[$directDidarId] ?? ['id' => null, 'name' => trim($directName)];
            return [
                'user_id' => $user['id'] ?: null,
                'didar_id' => $directDidarId,
                'name' => trim($directName) !== '' ? trim($directName) : ($user['name'] ?? ''),
                'source' => 'SESSION',
            ];
        }
        $dealOwnerDidarId = trim($dealOwnerDidarId);
        $user = $this->usersByDidar[$dealOwnerDidarId] ?? ['id' => null, 'name' => ''];
        return [
            'user_id' => $user['id'] ?: null,
            'didar_id' => $dealOwnerDidarId !== '' ? $dealOwnerDidarId : null,
            'name' => $user['name'] !== '' ? $user['name'] : null,
            'source' => $dealOwnerDidarId !== '' ? 'DEAL_OWNER_BACKFILL' : 'UNKNOWN',
        ];
    }

    private function buildLegacyMatchKey(int $dealId, float $amount, string $paymentDate, string $method, string $checkNumber, string $receiptPath): string
    {
        return implode('|', [
            $dealId,
            $this->money($amount),
            trim($paymentDate),
            strtolower(trim($method)),
            trim($checkNumber),
            trim($receiptPath),
        ]);
    }

    private function isChequeMethod(string $method, string $checkNumber = '', string $checkImage = ''): bool
    {
        $methodLower = strtolower(trim($method));
        $methodIsCheque = strpos($methodLower, 'check') !== false
            || strpos($methodLower, 'cheque') !== false
            || strpos($method, 'چک') !== false;
        if (trim($method) !== '') {
            return $methodIsCheque;
        }
        return $checkNumber !== '' || $checkImage !== '';
    }

    private function isSudranGatewayMethod(string $method): bool
    {
        $code = strtolower(trim($method));
        $code = preg_replace('/[\s\-\/]+/', '_', $code);
        return in_array($code, ['sudran_payment_gateway', 'sudran_gateway', 'soodran_payment_gateway', 'soodran_gateway', 'sodran_payment_gateway'], true)
            || strpos($method, 'سودران') !== false;
    }

    private function normalizeDate(string $gregorianDate, string $persianDate): ?string
    {
        return DateNormalizer::normalizeGregorianOrJalaliDate($gregorianDate)
            ?: DateNormalizer::normalizeGregorianOrJalaliDate($persianDate);
    }

    private function normalizeDateTime(string $value, ?string $fallbackDate): ?string
    {
        $value = trim($this->normalizeDigits($value));
        if ($value !== '' && preg_match('/^(\d{4}-\d{2}-\d{2})(?:[ T](\d{2}:\d{2}(?::\d{2})?))?/', $value, $m)) {
            $date = DateNormalizer::normalizeGregorianOrJalaliDate($m[1]);
            if ($date === null) {
                return $fallbackDate ? $fallbackDate . ' 00:00:00' : null;
            }
            $time = $m[2] ?? '00:00:00';
            if (strlen($time) === 5) {
                $time .= ':00';
            }
            return $date . ' ' . $time;
        }
        return $fallbackDate ? $fallbackDate . ' 00:00:00' : null;
    }

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

    private function uuidFromHash(string $hash): string
    {
        $hex = substr(hash('sha256', $hash), 0, 32);
        return substr($hex, 0, 8) . '-' . substr($hex, 8, 4) . '-4' . substr($hex, 12, 3) . '-8' . substr($hex, 15, 3) . '-' . substr($hex, 18, 12);
    }

    private function money(float $amount): string
    {
        return number_format($amount, 2, '.', '');
    }

    private function tableExists(string $table): bool
    {
        $stmt = $this->pdo->prepare("SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = ?");
        $stmt->execute([$table]);
        return (int)$stmt->fetchColumn() > 0;
    }

    private function columnExists(string $table, string $column): bool
    {
        return (bool)$this->app->db->hasColumn($table, $column);
    }
}
