<?php

declare(strict_types=1);

namespace Sogr\Service;

use PDO;
use Sogr\Core\Database;
use Throwable;

/**
 * Faza 7C-A6-R2-R3-R6: isključivo read-only dijagnostika prve transakcije.
 * Ne poziva processQueueId/processPending, ne menja queue, ne vrši COMMIT,
 * ne radi VOID i ne upisuje Google Sheets. Bridge se koristi samo kroz
 * bridgeHealth i preflightSync, obe read-only akcije.
 */
final class Phase7CA6R2R3R6TargetedDiagnosticService
{
    public const PHASE = '7C-A6-R2-R3-R6';
    public const VERSION = '7C-A6-R2-R3-R6.1';
    public const MODE = 'TARGETED_FIRST_TRANSACTION_READ_ONLY_DIAGNOSTIC';
    public const EXPECTED_RECEIPT = 105;

    private PDO $pdo;
    private string $privateRoot;
    private string $documentRoot;
    private Phase7CSyncService $sync;

    public function __construct(?PDO $pdo = null, ?string $privateRoot = null, ?string $documentRoot = null)
    {
        $this->pdo = $pdo ?? Database::connection();
        $this->documentRoot = $documentRoot !== null
            ? rtrim($documentRoot, '/\\')
            : (isset($_SERVER['DOCUMENT_ROOT']) ? rtrim((string) $_SERVER['DOCUMENT_ROOT'], '/\\') : dirname(__DIR__, 3));
        $this->privateRoot = $privateRoot !== null
            ? rtrim($privateRoot, '/\\')
            : dirname($this->documentRoot) . '/sogr_private';
        $this->sync = new Phase7CSyncService($this->pdo, $this->privateRoot);
    }

    /** @return array<string,mixed> */
    public function status(): array
    {
        $files = $this->fileGate();
        $counts = $this->basicCounts();
        return [
            'phase' => self::PHASE,
            'version' => self::VERSION,
            'mode' => self::MODE,
            'valid' => ($files['valid'] ?? false) === true,
            'writesAvailableThroughThisEndpoint' => false,
            'diagnosticOnly' => true,
            'files' => $files,
            'database' => $counts,
            'nextStep' => 'RUN_TARGETED_READ_ONLY_DIAGNOSTIC',
            'checkedAtUtc' => gmdate('c'),
        ];
    }

    /** @return array<string,mixed> */
    public function run(?string $requestedRequestId = null): array
    {
        $requestedRequestId = $this->normalizeRequestId($requestedRequestId);
        $before = $this->snapshot($requestedRequestId);
        $bridge = $this->bridgeInspection($before['queues'] ?? []);
        $after = $this->snapshot($requestedRequestId);

        $beforeFingerprint = (string) ($before['fingerprint'] ?? '');
        $afterFingerprint = (string) ($after['fingerprint'] ?? '');
        $unchanged = $beforeFingerprint !== '' && hash_equals($beforeFingerprint, $afterFingerprint);
        $conclusion = $this->conclude($after, $bridge, $requestedRequestId);

        return [
            'phase' => self::PHASE,
            'version' => self::VERSION,
            'mode' => self::MODE,
            'valid' => ($after['valid'] ?? false) === true && ($bridge['valid'] ?? false) === true && $unchanged,
            'diagnosticCompleted' => true,
            'writesPerformed' => false,
            'productionFilesChanged' => false,
            'productionBusinessTablesWritten' => false,
            'mysqlBusinessWrites' => 0,
            'googleSheetsCalls' => (int) ($bridge['googleSheetsCalls'] ?? 0),
            'googleSheetsWrites' => 0,
            'googleDriveWrites' => 0,
            'forbiddenOperationsCalled' => [
                'processQueueId' => false,
                'processPending' => false,
                'syncJob' => false,
                'void' => false,
                'pay' => false,
            ],
            'requestedRequestId' => $requestedRequestId,
            'checks' => [
                'databaseSnapshotValid' => ($after['valid'] ?? false) === true,
                'bridgeReadOnlyInspectionValid' => ($bridge['valid'] ?? false) === true,
                'databaseFingerprintUnchanged' => $unchanged,
                'sequenceObserved' => (int) ($after['sequence'] ?? 0),
                'payment105Present' => (bool) ($conclusion['facts']['payment105Present'] ?? false),
                'receipt105Present' => (bool) ($conclusion['facts']['receipt105Present'] ?? false),
                'activeLinkPresent' => (bool) ($conclusion['facts']['activeLinkPresent'] ?? false),
                'problemQueuePresent' => (bool) ($conclusion['facts']['problemQueuePresent'] ?? false),
                'sheetsReceipt105Present' => (bool) ($conclusion['facts']['sheetsReceipt105Present'] ?? false),
            ],
            'conclusion' => $conclusion,
            'database' => $after,
            'bridge' => $bridge,
            'beforeFingerprint' => $beforeFingerprint,
            'afterFingerprint' => $afterFingerprint,
            'readyForNextPhase' => (bool) ($conclusion['readyForNextPhase'] ?? false),
            'nextStep' => (string) ($conclusion['nextStep'] ?? 'REVIEW_R3_R6_DIAGNOSTIC_RESULT'),
            'importantNote' => 'R3-R6 nije izvršio retry, VOID, pay niti bilo koji drugi poslovni upis.',
            'checkedAtUtc' => gmdate('c'),
        ];
    }

    /** @return array<string,mixed> */
    private function snapshot(?string $requestedRequestId): array
    {
        $this->pdo->exec('START TRANSACTION READ ONLY');
        try {
            $sequence = (int) $this->pdo->query("SELECT current_value FROM number_sequences WHERE sequence_name='receipt'")->fetchColumn();
            $payments = $this->fetchAll(
                "SELECT p.id,p.payment_uuid,p.request_id,p.household_id,p.receipt_number,p.receipt_year,p.cash_year,
                        p.payment_date,p.payment_type,p.place,p.collector_id,bm.display_name AS collector_name,
                        p.membership_total,p.donation_total,p.purpose,p.origin,p.payment_status,p.created_at,p.updated_at,p.row_version,
                        r.id AS receipt_id,r.receipt_sheet_year,r.receipt_sheet_row,r.full_name_snapshot,r.first_name_snapshot,
                        r.last_name_snapshot,r.mahalla_snapshot,r.members_snapshot,r.place_snapshot,r.collector_snapshot,
                        r.years_text,r.source_rows_json,r.receipt_link,r.pdf_drive_file_id,r.pdf_url,r.receipt_status,r.row_version AS receipt_row_version
                 FROM payments p
                 LEFT JOIN receipts r ON r.payment_id=p.id
                 LEFT JOIN board_members bm ON bm.id=p.collector_id
                 WHERE p.receipt_number=105 OR p.request_id LIKE 'PHASE7C-A6-R2-R3-%'
                 ORDER BY p.id DESC LIMIT 30"
            );
            $items = $this->fetchAll(
                "SELECT pi.id AS payment_item_id,pi.payment_id,pi.household_year_id,pi.membership_amount,pi.donation_amount,
                        pi.item_status,pi.row_version AS item_row_version,
                        hy.year,hy.rb,hy.first_name,hy.last_name,hy.members_count,hy.due_amount,hy.record_status,
                        hy.row_version AS household_year_row_version,m.display_name AS mahalla,
                        srl.sheet_row,apl.payment_item_id AS active_link_item_id
                 FROM payment_items pi
                 JOIN household_years hy ON hy.id=pi.household_year_id
                 LEFT JOIN mahallas m ON m.id=hy.mahalla_id
                 LEFT JOIN active_payment_links apl ON apl.household_year_id=hy.id AND apl.payment_item_id=pi.id
                 LEFT JOIN google_sheet_tabs gst ON gst.tab_role='ANNUAL' AND gst.sheet_name=CAST(hy.year AS CHAR) AND gst.is_active=1
                 LEFT JOIN sheet_row_links srl ON srl.sheet_tab_id=gst.id AND srl.entity_type='HOUSEHOLD_YEAR' AND srl.entity_id=hy.id
                 WHERE pi.payment_id IN (SELECT id FROM payments WHERE receipt_number=105 OR request_id LIKE 'PHASE7C-A6-R2-R3-%')
                 ORDER BY pi.id DESC LIMIT 60"
            );
            $idempotency = $this->fetchAll(
                "SELECT request_id,action_name,user_id,request_hash,request_status,response_body,expires_at,created_at,updated_at
                 FROM idempotency_requests
                 WHERE request_id LIKE 'PHASE7C-A6-R2-R3-%'
                 ORDER BY created_at DESC LIMIT 30"
            );
            $queuesRaw = $this->fetchAll(
                "SELECT id,direction,entity_type,entity_id,operation_name,dedupe_key,payload_json,queue_status,priority,
                        attempts,next_attempt_at,locked_at,locked_by,last_error,completed_at,created_at,updated_at
                 FROM sync_queue
                 WHERE queue_status IN ('PENDING','PROCESSING','FAILED','CONFLICT')
                    OR dedupe_key LIKE 'WEB:PHASE7C-A6-R2-R3-%'
                 ORDER BY id DESC LIMIT 50"
            );
            $queues = array_map(fn(array $row): array => $this->summarizeQueue($row), $queuesRaw);
            $audits = $this->fetchAll(
                "SELECT id,user_id,action_name,entity_type,entity_id,source_system,request_id,created_at
                 FROM audit_log WHERE request_id LIKE 'PHASE7C-A6-R2-R3-%' ORDER BY id DESC LIMIT 50"
            );
            $conflicts = $this->fetchAll(
                "SELECT id,entity_type,entity_id,sheet_tab_id,sheet_row,db_version,sheet_version,conflict_status,
                        resolution_note,resolved_at,created_at
                 FROM sync_conflicts WHERE conflict_status='OPEN' ORDER BY id DESC LIMIT 20"
            );
            $settings = $this->fetchAll(
                "SELECT setting_key,setting_value,updated_at FROM app_settings
                 WHERE setting_key IN ('phase_7c_status','primary_data_store','phase_7c_version','financial_migration_status','financial_migration_locked','schema_version')
                 ORDER BY setting_key"
            );
            $receiptLinks = $this->fetchAll(
                "SELECT srl.id,srl.entity_type,srl.entity_id,srl.sheet_row,srl.db_version,srl.sheet_version,
                        srl.last_source,srl.last_synced_at,gst.sheet_name,gst.tab_role,gsd.year,gsd.document_title
                 FROM sheet_row_links srl
                 JOIN google_sheet_tabs gst ON gst.id=srl.sheet_tab_id
                 JOIN google_sheet_documents gsd ON gsd.id=gst.document_id
                 WHERE srl.entity_type='RECEIPT' AND srl.entity_id IN (
                    SELECT r.id FROM receipts r JOIN payments p ON p.id=r.payment_id WHERE p.receipt_number=105
                 ) ORDER BY srl.id DESC"
            );
            $targetRows = $this->targetRows($queues, $items);
            $counts = [
                'payments105' => $this->countWhere($payments, static fn(array $r): bool => (int) ($r['receipt_number'] ?? 0) === 105),
                'problemQueues' => $this->countWhere($queues, static fn(array $r): bool => in_array((string) ($r['queueStatus'] ?? ''), ['PENDING','PROCESSING','FAILED','CONFLICT'], true)),
                'openConflicts' => count($conflicts),
                'r3Idempotency' => count($idempotency),
            ];
            $fingerprintData = [
                'sequence' => $sequence,
                'payments' => $payments,
                'items' => $items,
                'idempotency' => $idempotency,
                'queues' => $queues,
                'conflicts' => $conflicts,
                'receiptLinks' => $receiptLinks,
            ];
            $fingerprint = hash('sha256', self::json($fingerprintData));
            $this->pdo->commit();
            return [
                'valid' => true,
                'readOnlyTransaction' => true,
                'sequence' => $sequence,
                'maxReceipt' => (int) $this->scalar("SELECT COALESCE(MAX(receipt_number),0) FROM payments"),
                'maxActiveReceipt' => (int) $this->scalar("SELECT COALESCE(MAX(receipt_number),0) FROM payments WHERE payment_status='ACTIVE'"),
                'counts' => $counts,
                'payments' => $payments,
                'paymentItems' => $items,
                'idempotency' => array_map(fn(array $r): array => $this->summarizeIdempotency($r), $idempotency),
                'queues' => $queues,
                'audits' => $audits,
                'openConflicts' => $conflicts,
                'receiptSheetLinks' => $receiptLinks,
                'targetHouseholdYears' => $targetRows,
                'settings' => $settings,
                'requestedRequestIdMatches' => $requestedRequestId === null ? [] : $this->requestMatches($requestedRequestId, $payments, $idempotency, $queues, $audits),
                'fingerprint' => $fingerprint,
            ];
        } catch (Throwable $e) {
            if ($this->pdo->inTransaction()) $this->pdo->rollBack();
            throw $e;
        }
    }

    /** @param list<array<string,mixed>> $queues @return array<string,mixed> */
    private function bridgeInspection(array $queues): array
    {
        $calls = 0;
        $health = null;
        $healthError = '';
        try {
            $health = $this->sync->bridgeHealth();
            $calls++;
        } catch (Throwable $e) {
            $healthError = $e->getMessage();
        }
        $preflights = [];
        foreach ($queues as $queue) {
            if (!in_array((string) ($queue['queueStatus'] ?? ''), ['PENDING','PROCESSING','FAILED','CONFLICT'], true)) continue;
            $job = $queue['_job'] ?? null;
            if (!is_array($job)) continue;
            if ((int) ($job['expectedReceiptNumber'] ?? 0) !== self::EXPECTED_RECEIPT && !str_starts_with((string) ($job['requestId'] ?? ''), 'PHASE7C-A6-R2-R3-')) continue;
            if (count($preflights) >= 5) break;
            try {
                $result = $this->sync->preflightJob($job);
                $calls++;
                $preflights[] = [
                    'queueId' => (int) ($queue['id'] ?? 0),
                    'requestId' => (string) ($queue['requestId'] ?? ''),
                    'queueStatus' => (string) ($queue['queueStatus'] ?? ''),
                    'ok' => (bool) ($result['ok'] ?? false),
                    'ready' => (bool) ($result['ready'] ?? false),
                    'reason' => (string) ($result['reason'] ?? ''),
                    'maxReceiptNumber' => (int) ($result['maxReceiptNumber'] ?? 0),
                    'expectedReceiptNumber' => (int) ($result['expectedReceiptNumber'] ?? 0),
                    'alreadySynced' => (bool) ($result['alreadySynced'] ?? false),
                    'existingReceipt' => $result['existingReceipt'] ?? null,
                ];
            } catch (Throwable $e) {
                $calls++;
                $preflights[] = [
                    'queueId' => (int) ($queue['id'] ?? 0),
                    'requestId' => (string) ($queue['requestId'] ?? ''),
                    'queueStatus' => (string) ($queue['queueStatus'] ?? ''),
                    'ok' => false,
                    'error' => $e->getMessage(),
                ];
            }
        }
        $valid = is_array($health) && ($health['ok'] ?? false) === true
            && !in_array(false, array_map(static fn(array $r): bool => ($r['ok'] ?? false) === true, $preflights), true);
        return [
            'valid' => $valid,
            'readOnly' => true,
            'googleSheetsCalls' => $calls,
            'googleSheetsWrites' => 0,
            'bridgeHealth' => $health,
            'bridgeHealthError' => $healthError,
            'pendingJobPreflights' => $preflights,
        ];
    }

    /** @param array<string,mixed> $db @param array<string,mixed> $bridge @return array<string,mixed> */
    private function conclude(array $db, array $bridge, ?string $requestedRequestId): array
    {
        $payments = $db['payments'] ?? [];
        $items = $db['paymentItems'] ?? [];
        $queues = $db['queues'] ?? [];
        $idempotency = $db['idempotency'] ?? [];
        $payment105 = null;
        foreach ($payments as $payment) {
            if ((int) ($payment['receipt_number'] ?? 0) === self::EXPECTED_RECEIPT) { $payment105 = $payment; break; }
        }
        $paymentId = is_array($payment105) ? (int) ($payment105['id'] ?? 0) : 0;
        $requestId = is_array($payment105) ? (string) ($payment105['request_id'] ?? '') : ($requestedRequestId ?? '');
        $receiptPresent = is_array($payment105) && (int) ($payment105['receipt_id'] ?? 0) > 0;
        $activeLink = false;
        foreach ($items as $item) {
            if ((int) ($item['payment_id'] ?? 0) === $paymentId && (int) ($item['active_link_item_id'] ?? 0) > 0) { $activeLink = true; break; }
        }
        $targetQueues = [];
        foreach ($queues as $q) {
            $samePayment = $paymentId > 0 && (int) ($q['entityId'] ?? 0) === $paymentId;
            $sameRequest = $requestId !== '' && (string) ($q['requestId'] ?? '') === $requestId;
            $expected105 = (int) ($q['expectedReceiptNumber'] ?? 0) === self::EXPECTED_RECEIPT;
            if ($samePayment || $sameRequest || $expected105) $targetQueues[] = $q;
        }
        $problemQueue = null;
        $doneQueue = null;
        foreach ($targetQueues as $q) {
            if ((string) ($q['queueStatus'] ?? '') === 'DONE') $doneQueue = $q;
            if (in_array((string) ($q['queueStatus'] ?? ''), ['PENDING','PROCESSING','FAILED','CONFLICT'], true)) { $problemQueue = $q; break; }
        }
        $preflights = $bridge['pendingJobPreflights'] ?? [];
        $sheets105 = false;
        $preflightFor105 = null;
        foreach ($preflights as $pf) {
            if ((int) ($pf['expectedReceiptNumber'] ?? 0) === self::EXPECTED_RECEIPT || ($pf['alreadySynced'] ?? false) === true) {
                $preflightFor105 = $pf;
                if (($pf['alreadySynced'] ?? false) === true) $sheets105 = true;
            }
        }
        $healthMax = (int) (($bridge['bridgeHealth']['maxReceiptNumber'] ?? 0));
        if ($healthMax >= self::EXPECTED_RECEIPT) $sheets105 = true;
        $idempotencyTarget = null;
        foreach ($idempotency as $idem) {
            if ($requestId !== '' && (string) ($idem['requestId'] ?? '') === $requestId) { $idempotencyTarget = $idem; break; }
        }

        $facts = [
            'payment105Present' => is_array($payment105),
            'payment105Status' => is_array($payment105) ? (string) ($payment105['payment_status'] ?? '') : '',
            'receipt105Present' => $receiptPresent,
            'receipt105Status' => is_array($payment105) ? (string) ($payment105['receipt_status'] ?? '') : '',
            'activeLinkPresent' => $activeLink,
            'problemQueuePresent' => is_array($problemQueue),
            'problemQueueStatus' => is_array($problemQueue) ? (string) ($problemQueue['queueStatus'] ?? '') : '',
            'doneQueuePresent' => is_array($doneQueue),
            'sheetsReceipt105Present' => $sheets105,
            'bridgeMaxReceiptNumber' => $healthMax,
            'idempotencyStatus' => is_array($idempotencyTarget) ? (string) ($idempotencyTarget['requestStatus'] ?? '') : '',
            'requestId' => $requestId,
            'paymentId' => $paymentId,
            'sequence' => (int) ($db['sequence'] ?? 0),
        ];

        $state = 'INCONSISTENT_STATE_REQUIRES_REVIEW';
        $next = 'REVIEW_R3_R6_DIAGNOSTIC_RESULT';
        $message = 'Stanje zahteva ručnu analizu rezultata.';
        $ready = false;
        $safeToRepeat = false;
        $voidAllowed = false;

        if (!is_array($payment105) && !is_array($problemQueue) && !$sheets105) {
            $state = 'COMMIT_NOT_PERSISTED';
            $next = 'READY_FOR_7C_A6_R2_R3_R7_WRITE_PATH_REPAIR_OR_SAFE_RETRY_DECISION';
            $message = 'Priznanica 00105 nije pronađena ni u MySQL-u ni u Sheets-u i nema aktivnog sync posla.';
            $safeToRepeat = true;
            $ready = true;
        } elseif (!is_array($payment105) && is_array($problemQueue)) {
            $state = 'ORPHAN_SYNC_QUEUE_WITHOUT_PAYMENT';
            $next = 'READY_FOR_7C_A6_R2_R3_R7_ORPHAN_QUEUE_RECONCILIATION';
            $message = 'Postoji problematičan sync zapis, ali nema odgovarajuće MySQL uplate 00105.';
            $ready = true;
        } elseif (is_array($payment105) && (string) ($payment105['payment_status'] ?? '') === 'VOID') {
            $state = 'CANARY_ALREADY_VOID';
            $next = 'REVIEW_VOIDED_CANARY_BEFORE_CONTINUING';
            $message = 'Priznanica 00105 postoji kao VOID.';
            $ready = true;
        } elseif (is_array($payment105) && $receiptPresent && $activeLink && is_array($problemQueue) && !$sheets105) {
            $state = 'MYSQL_COMMITTED_SYNC_PENDING_NOT_IN_SHEETS';
            $next = 'READY_FOR_7C_A6_R2_R3_R7_CONTROLLED_SYNC_QUEUE_RECOVERY';
            $message = 'Uplata 00105 je kompletno sačuvana u MySQL-u, ali Google Sheets je još nema.';
            $voidAllowed = true;
            $ready = true;
        } elseif (is_array($payment105) && $receiptPresent && $activeLink && is_array($problemQueue) && $sheets105) {
            $state = 'SHEETS_WRITTEN_QUEUE_METADATA_NOT_FINALIZED';
            $next = 'READY_FOR_7C_A6_R2_R3_R7_QUEUE_METADATA_RECONCILIATION';
            $message = 'Uplata 00105 postoji i u MySQL-u i u Sheets-u, ali sync_queue nije zatvoren kao DONE.';
            $ready = true;
        } elseif (is_array($payment105) && $receiptPresent && $activeLink && is_array($doneQueue) && $sheets105) {
            $state = 'FIRST_TRANSACTION_FULLY_COMMITTED_AND_SYNCED';
            $next = 'READY_FOR_7C_A6_R2_R4_RECEIPT_PDF_PRINT_AND_NORMAL_APP_VALIDATION';
            $message = 'Priznanica 00105 je kompletna u MySQL-u i Google Sheets-u, a queue je DONE.';
            $ready = true;
        } elseif (is_array($payment105) && !$receiptPresent) {
            $state = 'PAYMENT_PRESENT_RECEIPT_MISSING';
            $next = 'READY_FOR_7C_A6_R2_R3_R7_PARTIAL_MYSQL_TRANSACTION_RECONCILIATION';
            $message = 'Payment 00105 postoji, ali receipt snapshot nedostaje.';
            $ready = true;
        }

        return [
            'state' => $state,
            'message' => $message,
            'facts' => $facts,
            'targetPayment' => $payment105,
            'targetQueue' => $problemQueue ?? $doneQueue,
            'targetIdempotency' => $idempotencyTarget,
            'bridgePreflight' => $preflightFor105,
            'safeToRepeatCommit' => $safeToRepeat,
            'voidAllowed' => $voidAllowed,
            'doNotRepeatCommit' => !$safeToRepeat,
            'readyForNextPhase' => $ready,
            'nextStep' => $next,
        ];
    }

    /** @return array<string,mixed> */
    private function fileGate(): array
    {
        $expected = [
            'productionIndex' => ['path' => $this->documentRoot . '/api/v1/index.php', 'sha256' => 'f980f95f8532f332447fd13e2f84089c70044cb9b4c8a2db34cb7aa8da10959a'],
            'productionClient' => ['path' => $this->documentRoot . '/evidencija.html', 'sha256' => 'a094cddb33ee8a37939d9d20f7868183e709673b7edf2233ad3cc6932d8eeeb1'],
            'r3Endpoint' => ['path' => $this->documentRoot . '/api/v1/phase7c-a6-r2-r3.php', 'sha256' => 'c67b49b1f9a444b9b6e5fb9834c3427f299d7cb6ef4cff47828469996bd5ca4a'],
            'r3Control' => ['path' => $this->documentRoot . '/faza-7c-a6-r2-r3-kontrola.html', 'sha256' => '15357a2e2cd7284380b91bfed4e0df8add8ab5451f5912ad2e8c248b12c48dca'],
            'writeService' => ['path' => $this->privateRoot . '/src/Service/Phase7CWriteService.php', 'sha256' => 'bb89710431dce9c8f499f9a668be3ce5859006bbe50aa79a9a4d336063c99c2d'],
            'syncService' => ['path' => $this->privateRoot . '/src/Service/Phase7CSyncService.php', 'sha256' => 'ae684dd7adcb7bdbf2d1590b95a4ddd1a9c7b07a6138dfca60f79705ebfa1c4e'],
            'canaryService' => ['path' => $this->privateRoot . '/src/Service/Phase7CA6R2R3FirstTransactionCanaryService.php', 'sha256' => '08c56c58afff6382d25cc91ae8c5a0d723819c0562147a3fd030edf6ba09bc10'],
        ];
        $checks = []; $files = [];
        foreach ($expected as $key => $item) {
            $actual = is_file($item['path']) ? (string) hash_file('sha256', $item['path']) : '';
            $checks[$key . 'Exact'] = $actual !== '' && hash_equals($item['sha256'], $actual);
            $files[$key] = ['path' => $item['path'], 'expectedSha256' => $item['sha256'], 'actualSha256' => $actual];
        }
        return ['valid' => !in_array(false, $checks, true), 'checks' => $checks, 'files' => $files];
    }

    /** @return array<string,mixed> */
    private function basicCounts(): array
    {
        return [
            'sequence' => (int) $this->scalar("SELECT current_value FROM number_sequences WHERE sequence_name='receipt'"),
            'payment105' => (int) $this->scalar("SELECT COUNT(*) FROM payments WHERE receipt_number=105"),
            'problemSync' => (int) $this->scalar("SELECT COUNT(*) FROM sync_queue WHERE queue_status IN ('PENDING','PROCESSING','FAILED','CONFLICT')"),
            'openConflicts' => (int) $this->scalar("SELECT COUNT(*) FROM sync_conflicts WHERE conflict_status='OPEN'"),
        ];
    }

    /** @param list<array<string,mixed>> $queues @param list<array<string,mixed>> $items @return list<array<string,mixed>> */
    private function targetRows(array $queues, array $items): array
    {
        $ids = [146, 244];
        foreach ($queues as $q) {
            foreach ((array) ($q['householdYearIds'] ?? []) as $id) if ((int) $id > 0) $ids[] = (int) $id;
        }
        foreach ($items as $item) if ((int) ($item['household_year_id'] ?? 0) > 0) $ids[] = (int) $item['household_year_id'];
        $ids = array_values(array_unique($ids));
        if (!$ids) return [];
        $placeholders = []; $params = [];
        foreach ($ids as $i => $id) { $key = ':hy' . $i; $placeholders[] = $key; $params[$key] = $id; }
        $sql = "SELECT hy.id,hy.household_id,hy.year,hy.rb,hy.first_name,hy.last_name,m.display_name AS mahalla,
                       hy.members_count,hy.due_amount,hy.record_status,hy.row_version,srl.sheet_row,
                       apl.payment_item_id AS active_payment_item_id,p.id AS active_payment_id,p.receipt_number,p.request_id,p.payment_status
                FROM household_years hy
                LEFT JOIN mahallas m ON m.id=hy.mahalla_id
                LEFT JOIN google_sheet_tabs gst ON gst.tab_role='ANNUAL' AND gst.sheet_name=CAST(hy.year AS CHAR) AND gst.is_active=1
                LEFT JOIN sheet_row_links srl ON srl.sheet_tab_id=gst.id AND srl.entity_type='HOUSEHOLD_YEAR' AND srl.entity_id=hy.id
                LEFT JOIN active_payment_links apl ON apl.household_year_id=hy.id
                LEFT JOIN payment_items pi ON pi.id=apl.payment_item_id
                LEFT JOIN payments p ON p.id=pi.payment_id
                WHERE hy.id IN (" . implode(',', $placeholders) . ") ORDER BY hy.id";
        $stmt = $this->pdo->prepare($sql); $stmt->execute($params); return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    /** @param array<string,mixed> $row @return array<string,mixed> */
    private function summarizeQueue(array $row): array
    {
        $payload = $this->decode((string) ($row['payload_json'] ?? ''));
        $job = is_array($payload['job'] ?? null) ? $payload['job'] : [];
        $jobPayload = is_array($job['payload'] ?? null) ? $job['payload'] : [];
        $dbIds = is_array($job['dbEntityIds'] ?? null) ? $job['dbEntityIds'] : [];
        $hyIds = array_map('intval', (array) ($dbIds['HOUSEHOLD_YEAR'] ?? []));
        return [
            'id' => (int) ($row['id'] ?? 0),
            'direction' => (string) ($row['direction'] ?? ''),
            'entityType' => (string) ($row['entity_type'] ?? ''),
            'entityId' => isset($row['entity_id']) ? (int) $row['entity_id'] : null,
            'operationName' => (string) ($row['operation_name'] ?? ''),
            'dedupeKey' => (string) ($row['dedupe_key'] ?? ''),
            'queueStatus' => (string) ($row['queue_status'] ?? ''),
            'attempts' => (int) ($row['attempts'] ?? 0),
            'nextAttemptAt' => $row['next_attempt_at'] ?? null,
            'lockedAt' => $row['locked_at'] ?? null,
            'lockedBy' => $row['locked_by'] ?? null,
            'lastError' => (string) ($row['last_error'] ?? ''),
            'completedAt' => $row['completed_at'] ?? null,
            'createdAt' => $row['created_at'] ?? null,
            'updatedAt' => $row['updated_at'] ?? null,
            'businessAction' => (string) ($job['businessAction'] ?? ($payload['action'] ?? '')),
            'requestId' => (string) ($job['requestId'] ?? ($jobPayload['requestId'] ?? '')),
            'expectedReceiptNumber' => (int) ($job['expectedReceiptNumber'] ?? ($jobPayload['expectedReceiptNumber'] ?? 0)),
            'year' => isset($jobPayload['year']) ? (int) $jobPayload['year'] : null,
            'sourceRow' => isset($jobPayload['row']) ? (int) $jobPayload['row'] : null,
            'ime' => (string) ($jobPayload['ime'] ?? ''),
            'prezime' => (string) ($jobPayload['prezime'] ?? ''),
            'iznos' => isset($jobPayload['iznos']) ? (float) $jobPayload['iznos'] : null,
            'donacija' => isset($jobPayload['donacija']) ? (float) $jobPayload['donacija'] : null,
            'collector' => (string) ($jobPayload['odbor'] ?? ''),
            'householdYearIds' => $hyIds,
            'hasBridgeResult' => is_array($payload['bridgeResult'] ?? null),
            '_job' => $job,
        ];
    }

    /** @param array<string,mixed> $row @return array<string,mixed> */
    private function summarizeIdempotency(array $row): array
    {
        $body = $this->decode((string) ($row['response_body'] ?? ''));
        return [
            'requestId' => (string) ($row['request_id'] ?? ''),
            'actionName' => (string) ($row['action_name'] ?? ''),
            'userId' => isset($row['user_id']) ? (int) $row['user_id'] : null,
            'requestHash' => (string) ($row['request_hash'] ?? ''),
            'requestStatus' => (string) ($row['request_status'] ?? ''),
            'responseSummary' => [
                'ok' => $body['ok'] ?? null,
                'requestId' => $body['requestId'] ?? null,
                'mysqlCommitted' => $body['mysqlCommitted'] ?? null,
                'receiptNumber' => $body['priznanica']['broj'] ?? null,
                'sync' => $body['sync'] ?? null,
                'syncPending' => $body['syncPending'] ?? null,
            ],
            'expiresAt' => $row['expires_at'] ?? null,
            'createdAt' => $row['created_at'] ?? null,
            'updatedAt' => $row['updated_at'] ?? null,
        ];
    }

    /** @param list<array<string,mixed>> $payments @param list<array<string,mixed>> $idempotency @param list<array<string,mixed>> $queues @param list<array<string,mixed>> $audits */
    private function requestMatches(string $requestId, array $payments, array $idempotency, array $queues, array $audits): array
    {
        return [
            'payments' => array_values(array_filter($payments, static fn(array $r): bool => (string) ($r['request_id'] ?? '') === $requestId)),
            'idempotency' => array_values(array_filter($idempotency, static fn(array $r): bool => (string) ($r['requestId'] ?? '') === $requestId)),
            'queues' => array_values(array_filter($queues, static fn(array $r): bool => (string) ($r['requestId'] ?? '') === $requestId)),
            'audits' => array_values(array_filter($audits, static fn(array $r): bool => (string) ($r['request_id'] ?? '') === $requestId)),
        ];
    }

    private function normalizeRequestId(?string $value): ?string
    {
        $value = trim((string) $value);
        if ($value === '') return null;
        if (preg_match('/^PHASE7C-A6-R2-R3-[a-f0-9-]{36}$/D', $value) !== 1) return null;
        return $value;
    }

    /** @return list<array<string,mixed>> */
    private function fetchAll(string $sql): array { return $this->pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC); }
    private function scalar(string $sql): mixed { return $this->pdo->query($sql)->fetchColumn(); }
    /** @param list<array<string,mixed>> $rows */
    private function countWhere(array $rows, callable $predicate): int { return count(array_filter($rows, $predicate)); }
    /** @return array<string,mixed> */
    private function decode(string $json): array { if (trim($json)==='') return []; try { $v=json_decode($json,true,512,JSON_THROW_ON_ERROR); return is_array($v)?$v:[]; } catch(Throwable) { return []; } }
    private static function json(mixed $value): string { return json_encode($value, JSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES|JSON_THROW_ON_ERROR); }
}