<?php
namespace App\Services;

use App\Database\Connection;
use App\Utils\Logger;
use PDO;
use RuntimeException;

class PaymentTafsiliBackfillService
{
    private Connection $db;
    private PDO $pdo;
    private string $mode;
    private int $limit;
    private array $lookup = [];

    public function __construct(Connection $db, string $mode = 'dry-run', int $limit = 0)
    {
        $this->db = $db;
        $this->pdo = $db->getPdo();
        $this->mode = in_array($mode, ['dry-run', 'run', 'verify'], true) ? $mode : 'dry-run';
        $this->limit = max(0, $limit);
    }

    public function execute(): array
    {
        if (!$this->db->hasColumn('transactions', 'destination_tafsili_code')) {
            throw new RuntimeException('ستون transactions.destination_tafsili_code وجود ندارد؛ ابتدا migration را اجرا کنید');
        }

        $this->lookup = $this->loadDestinationBankTafsiliLookup();
        $report = [
            'mode' => $this->mode,
            'limit' => $this->limit,
            'lookup_codes' => count(array_unique(array_values($this->lookup))),
            'scanned' => 0,
            'already_ok' => 0,
            'would_update' => 0,
            'updated' => 0,
            'missing_or_mismatched' => 0,
            'unresolved' => 0,
            'blank_destination' => 0,
            'samples' => [
                'updates' => [],
                'unresolved' => [],
            ],
        ];

        $stmt = $this->pdo->query($this->buildTransactionSelectSql());
        $update = $this->pdo->prepare('
            UPDATE transactions
            SET destination_tafsili_code = ?
            WHERE id = ?
        ');

        while ($stmt && ($row = $stmt->fetch(PDO::FETCH_ASSOC)) !== false) {
            $report['scanned']++;
            $current = trim((string)($row['destination_tafsili_code'] ?? ''));
            $destination = trim((string)($row['destination_bank'] ?? ''));
            $title = trim((string)($row['destination_bank_title'] ?? ''));
            if ($destination === '' && $title === '') {
                $report['blank_destination']++;
                continue;
            }

            $expected = $this->resolveExpectedTafsili($destination, $title);
            if ($expected === '') {
                $report['unresolved']++;
                if (count($report['samples']['unresolved']) < 10) {
                    $report['samples']['unresolved'][] = [
                        'transaction_id' => (int)$row['id'],
                        'destination_bank' => $destination,
                        'destination_bank_title' => $title,
                        'current_tafsili' => $current,
                    ];
                }
                continue;
            }

            if ($current === $expected) {
                $report['already_ok']++;
                continue;
            }

            $report['would_update']++;
            $report['missing_or_mismatched']++;
            if (count($report['samples']['updates']) < 10) {
                $report['samples']['updates'][] = [
                    'transaction_id' => (int)$row['id'],
                    'destination_bank' => $destination,
                    'destination_bank_title' => $title,
                    'from' => $current,
                    'to' => $expected,
                ];
            }

            if ($this->mode === 'run') {
                $update->execute([$expected, (int)$row['id']]);
                $report['updated'] += $update->rowCount();
            }
        }

        $report['ok'] = $this->mode === 'verify'
            ? ((int)$report['missing_or_mismatched'] === 0)
            : true;
        $report['checked_at'] = date('c');

        if ($this->mode === 'run') {
            Logger::logInfo('Payment tafsili backfill completed', [
                'updated' => $report['updated'],
                'scanned' => $report['scanned'],
                'unresolved' => $report['unresolved'],
            ]);
        }

        return $report;
    }

    private function buildTransactionSelectSql(): string
    {
        $sql = "
            SELECT id, destination_bank, destination_bank_title, destination_tafsili_code
            FROM transactions
            WHERE COALESCE(destination_bank, '') <> ''
               OR COALESCE(destination_bank_title, '') <> ''
            ORDER BY id ASC
        ";
        if ($this->limit > 0) {
            $sql .= ' LIMIT ' . $this->limit;
        }
        return $sql;
    }

    private function loadDestinationBankTafsiliLookup(): array
    {
        $lookup = [];
        $stmt = $this->pdo->query("
            SELECT li.code, li.value, li.title,
                   JSON_UNQUOTE(JSON_EXTRACT(li.meta_json, '$.tafsili_code')) AS tafsili_code,
                   JSON_UNQUOTE(JSON_EXTRACT(li.meta_json, '$.bank_name')) AS bank_name
            FROM lookup_groups lg
            JOIN lookup_items li ON li.group_id = lg.id
            WHERE lg.code = 'payment_destination_banks'
              AND li.meta_json IS NOT NULL
              AND li.meta_json <> ''
              AND JSON_VALID(li.meta_json)
        ");

        foreach (($stmt ? $stmt->fetchAll(PDO::FETCH_ASSOC) : []) as $row) {
            $tafsili = trim((string)($row['tafsili_code'] ?? ''));
            if ($tafsili === '') {
                continue;
            }
            foreach (['code', 'value', 'title'] as $field) {
                $key = trim((string)($row[$field] ?? ''));
                if ($key !== '') {
                    $lookup[$key] = $tafsili;
                }
            }
            $bankName = trim((string)($row['bank_name'] ?? ''));
            if ($bankName !== '') {
                $lookup[$bankName] = $tafsili;
            }
        }

        $this->addKnownHoldingBankAliases($lookup);
        return $lookup;
    }

    private function addKnownHoldingBankAliases(array &$lookup): void
    {
        $aliases = [
            '001' => ['tejarat', 'تجارت'],
            '022' => ['melli', 'meli', 'ملی'],
            '024' => ['mellat', 'melat', 'ملت'],
        ];
        foreach ($aliases as $tafsili => $keys) {
            if (!in_array($tafsili, $lookup, true)) {
                continue;
            }
            foreach ($keys as $key) {
                if (!isset($lookup[$key])) {
                    $lookup[$key] = $tafsili;
                }
            }
        }
    }

    private function resolveExpectedTafsili(string $destinationBank, string $destinationBankTitle): string
    {
        return (string)($this->lookup[$destinationBank] ?? $this->lookup[$destinationBankTitle] ?? '');
    }
}
