<?php


declare(strict_types=1);


namespace Sogr\Service;


use PDO;
use Throwable;
use Sogr\Core\ApiException;
use Sogr\Core\Database;


/**
 * FAZA 7C-A2 — izolovani MySQL canary sa obaveznim ROLLBACK-om.
 *
 * Servis prolazi kroz kompletan budući payment write put, proverava sve
 * zapise unutar transakcije, a zatim UVEK izvršava ROLLBACK. Ne poziva
 * Google Sheets i ne može da izvrši COMMIT.
 */
final class Phase7CCanaryRollbackService
{
    private const PHASE = '7C-A2';
    private const VERSION = '7C-A2.1';
    private const MAIN_2026_DOCUMENT_ID = '1CIEcblm3bWxFu_ecqhKPSqM2ICpOqe27cr042iFGF8M';
    private const EXPECTED_PRODUCTION_INDEX_SHA256 = '4ef991b973409b9fba9f217a834c0a026235bd4d7e9fc6088e8de60ecba0095e';


    private PDO $pdo;
    private Phase7CWritePreviewService $previewService;


    public function __construct(?PDO $pdo = null)
    {
        $this->pdo = $pdo ?? Database::connection();
        $this->previewService = new Phase7CWritePreviewService($this->pdo);
    }


    /** @return array<string,mixed> */
    public function status(): array
    {
        $a1 = $this->previewService->status();
        $requiredColumns = $this->requiredColumns();
        $columnChecks = [];
        $allColumnsReady = true;


        $columnStatement = $this->pdo->prepare(
            'SELECT COUNT(*)
             FROM information_schema.columns
             WHERE table_schema = DATABASE()
               AND table_name = :table_name
               AND column_name = :column_name'
        );


        foreach ($requiredColumns as $table => $columns) {
            $columnChecks[$table] = [];
            foreach ($columns as $column) {
                $columnStatement->execute([
                    ':table_name' => $table,
                    ':column_name' => $column,
                ]);
                $exists = (int) $columnStatement->fetchColumn() === 1;
                $columnChecks[$table][$column] = $exists;
                if (!$exists) {
                    $allColumnsReady = false;
                }
            }
        }


        $targetTables = array_keys($requiredColumns);
        $placeholders = implode(',', array_fill(0, count($targetTables), '?'));
        $engineStatement = $this->pdo->prepare(
            "SELECT table_name, engine
             FROM information_schema.tables
             WHERE table_schema = DATABASE()
               AND table_name IN ($placeholders)"
        );
        $engineStatement->execute($targetTables);
        $engines = [];
        foreach ($engineStatement->fetchAll(PDO::FETCH_ASSOC) as $row) {
            $engines[(string) $row['table_name']] = strtoupper((string) $row['engine']);
        }


        $allInnoDb = true;
        foreach ($targetTables as $table) {
            if (($engines[$table] ?? '') !== 'INNODB') {
                $allInnoDb = false;
            }
        }


        $triggerStatement = $this->pdo->prepare(
            "SELECT COUNT(*)
             FROM information_schema.triggers
             WHERE trigger_schema = DATABASE()
               AND event_object_table IN ($placeholders)"
        );
        $triggerStatement->execute($targetTables);
        $triggerCount = (int) $triggerStatement->fetchColumn();


        $receiptModeType = (string) $this->pdo->query(
            "SELECT column_type
             FROM information_schema.columns
             WHERE table_schema = DATABASE()
               AND table_name = 'payments'
               AND column_name = 'receipt_mode'
             LIMIT 1"
        )->fetchColumn();
        $electronicReceiptSupported = stripos($receiptModeType, 'ELECTRONIC') !== false;


        $productionIndexPath = $this->productionIndexPath();
        $productionIndexExists = is_file($productionIndexPath);
        $productionIndexSha256 = $productionIndexExists
            ? (string) hash_file('sha256', $productionIndexPath)
            : '';
        $productionIndexUntouched = hash_equals(
            self::EXPECTED_PRODUCTION_INDEX_SHA256,
            $productionIndexSha256
        );


        $checks = [
            'phase7CA1Ready' => ($a1['ready'] ?? false) === true,
            'allRequiredColumnsExist' => $allColumnsReady,
            'allTransactionTablesUseInnoDB' => $allInnoDb,
            'noBusinessTableTriggers' => $triggerCount === 0,
            'electronicReceiptModeSupported' => $electronicReceiptSupported,
            'productionIndexExists' => $productionIndexExists,
            'productionIndexUntouched' => $productionIndexUntouched,
        ];


        return [
            'phase' => self::PHASE,
            'version' => self::VERSION,
            'mode' => 'STAGING_CANARY_ROLLBACK_ONLY',
            'ready' => !in_array(false, $checks, true),
            'writesPerformed' => false,
            'writesPersisted' => false,
            'rollbackRequired' => true,
            'commitAvailable' => false,
            'googleSheetsCallsAvailable' => false,
            'checks' => $checks,
            'phase7CA1Gate' => $a1,
            'schema' => [
                'columns' => $columnChecks,
                'engines' => $engines,
                'triggerCount' => $triggerCount,
                'paymentsReceiptModeColumnType' => $receiptModeType,
            ],
            'production' => [
                'indexPath' => $productionIndexPath,
                'expectedSha256' => self::EXPECTED_PRODUCTION_INDEX_SHA256,
                'actualSha256' => $productionIndexSha256,
                'untouched' => $productionIndexUntouched,
            ],
            'nextStep' => !in_array(false, $checks, true)
                ? 'READY_FOR_7C_A2_CANARY_ROLLBACK'
                : 'STOP_AND_REVIEW_FAILED_CHECKS',
            'checkedAtUtc' => gmdate('c'),
        ];
    }


    /**
     * @param array<string,mixed> $input
     * @param array<string,mixed> $session
     * @return array<string,mixed>
     */
    public function run(array $input, array $session): array
    {
        $status = $this->status();
        if (($status['ready'] ?? false) !== true) {
            throw new ApiException(
                'PHASE_7C_A2_NOT_READY',
                'Faza 7C-A2 nije spremna. Pregledajte neuspele provere.',
                409,
                ['status' => $status]
            );
        }


        $requestId = trim((string) ($input['requestId'] ?? ''));
        if (
            strlen($requestId) < 24
            || strlen($requestId) > 100
            || !preg_match('/^PHASE7C-A2-[A-Za-z0-9:_-]+$/', $requestId)
        ) {
            throw new ApiException(
                'INVALID_PHASE_7C_A2_REQUEST_ID',
                'requestId za Fazu 7C-A2 nije ispravan.',
                400
            );
        }


        $actor = $session['user'] ?? $session;
        $actorId = is_array($actor) ? (int) ($actor['id'] ?? 0) : 0;
        if ($actorId <= 0) {
            throw new ApiException(
                'ACTOR_USER_REQUIRED',
                'Nije pronađen prijavljeni korisnik.',
                401
            );
        }


        $previewInput = [
            'year' => 2026,
            'sourceRow' => $input['sourceRow'] ?? 0,
            'membershipAmount' => $input['membershipAmount'] ?? null,
            'donationAmount' => $input['donationAmount'] ?? 0,
            'paymentDate' => $input['paymentDate'] ?? gmdate('Y-m-d'),
            'place' => $input['place'] ?? 'G. RAPČA',
            'collector' => $input['collector'] ?? 'munir',
            'requestId' => $requestId,
        ];
        $preview = $this->previewService->previewPayment($previewInput, $session);


        $householdYearId = (int) $preview['householdYear']['householdYearId'];
        $householdId = (int) $preview['householdYear']['householdId'];
        $sourceRow = (int) $preview['householdYear']['sourceRow'];
        $expectedRowVersion = (int) $preview['householdYear']['rowVersion'];
        $membershipAmount = $this->money($preview['payment']['membershipAmount']);
        $donationAmount = $this->money($preview['payment']['donationAmount']);
        $collectorId = (int) $preview['payment']['collectorId'];
        $nextReceiptNumber = (int) $preview['receipt']['nextReceiptNumber'];
        $formattedReceiptNumber = (string) $preview['receipt']['formattedReceiptNumber'];
        $dedupeKey = 'PHASE7C-A2:PAYMENT:' . $requestId;
        $paymentUuid = $this->uuidV4();
        $receiptToken = bin2hex(random_bytes(24));
        $requestHash = (string) $preview['request']['requestHash'];


        $before = $this->snapshot($requestId, $dedupeKey, $householdYearId);
        $inside = [];
        $rolledBack = false;
        $error = null;


        try {
            $this->pdo->exec('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');
            if (!$this->pdo->beginTransaction()) {
                throw new ApiException(
                    'TRANSACTION_START_FAILED',
                    'MySQL transakcija nije mogla da se pokrene.',
                    500
                );
            }


            $insertIdempotency = $this->pdo->prepare(
                "INSERT INTO idempotency_requests
                    (request_id, action_name, user_id, request_hash,
                     response_body, request_status, expires_at)
                 VALUES
                    (:request_id, 'PHASE7C_A2_CANARY', :user_id, :request_hash,
                     NULL, 'PROCESSING', UTC_TIMESTAMP() + INTERVAL 1 HOUR)"
            );
            $insertIdempotency->execute([
                ':request_id' => $requestId,
                ':user_id' => $actorId,
                ':request_hash' => $requestHash,
            ]);


            $householdLock = $this->pdo->prepare(
                "SELECT hy.id, hy.household_id, hy.due_amount, hy.row_version,
                        hy.record_status, hy.is_exempt, h.is_active
                 FROM household_years hy
                 JOIN households h ON h.id = hy.household_id
                 WHERE hy.id = :household_year_id
                 FOR UPDATE"
            );
            $householdLock->execute([':household_year_id' => $householdYearId]);
            $lockedHouseholdYear = $householdLock->fetch(PDO::FETCH_ASSOC);
            if (!is_array($lockedHouseholdYear)) {
                throw new ApiException(
                    'HOUSEHOLD_YEAR_DISAPPEARED',
                    'Godišnji red više ne postoji.',
                    409
                );
            }
            if (
                (int) $lockedHouseholdYear['household_id'] !== $householdId
                || (int) $lockedHouseholdYear['row_version'] !== $expectedRowVersion
                || (string) $lockedHouseholdYear['record_status'] !== 'ACTIVE'
                || (int) $lockedHouseholdYear['is_exempt'] !== 0
                || (int) $lockedHouseholdYear['is_active'] !== 1
                || abs($this->money($lockedHouseholdYear['due_amount']) - $membershipAmount) > 0.009
            ) {
                throw new ApiException(
                    'HOUSEHOLD_YEAR_CHANGED',
                    'Godišnji red je promenjen nakon preview kontrole.',
                    409
                );
            }


            $activeLinkLock = $this->pdo->prepare(
                'SELECT payment_item_id
                 FROM active_payment_links
                 WHERE household_year_id = :household_year_id
                 FOR UPDATE'
            );
            $activeLinkLock->execute([':household_year_id' => $householdYearId]);
            if ($activeLinkLock->fetchColumn() !== false) {
                throw new ApiException(
                    'HOUSEHOLD_YEAR_ALREADY_PAID',
                    'Izabrani red već ima aktivnu uplatu.',
                    409
                );
            }


            $sequenceLock = $this->pdo->prepare(
                "SELECT current_value, padding_length
                 FROM number_sequences
                 WHERE BINARY sequence_name = BINARY 'receipt'
                 FOR UPDATE"
            );
            $sequenceLock->execute();
            $sequenceRow = $sequenceLock->fetch(PDO::FETCH_ASSOC);
            if (!is_array($sequenceRow)) {
                throw new ApiException(
                    'RECEIPT_SEQUENCE_MISSING',
                    'Nedostaje brojač priznanica.',
                    500
                );
            }


            $maxReceipt = (int) $this->pdo->query(
                'SELECT COALESCE(MAX(receipt_number), 0) FROM payments'
            )->fetchColumn();
            $lockedNextReceipt = max(
                (int) $sequenceRow['current_value'],
                $maxReceipt
            ) + 1;
            if ($lockedNextReceipt !== $nextReceiptNumber) {
                throw new ApiException(
                    'RECEIPT_SEQUENCE_CHANGED',
                    'Brojač priznanica se promenio nakon preview kontrole.',
                    409,
                    [
                        'previewNext' => $nextReceiptNumber,
                        'lockedNext' => $lockedNextReceipt,
                    ]
                );
            }


            $updateSequence = $this->pdo->prepare(
                "UPDATE number_sequences
                 SET current_value = :current_value
                 WHERE BINARY sequence_name = BINARY 'receipt'"
            );
            $updateSequence->execute([':current_value' => $lockedNextReceipt]);


            $insertPayment = $this->pdo->prepare(
                "INSERT INTO payments
                    (payment_uuid, request_id, household_id, receipt_number,
                     receipt_year, cash_year, payment_date, payment_type,
                     place, collector_id, membership_total, donation_total,
                     purpose, origin, payment_status, receipt_mode,
                     created_by_user_id)
                 VALUES
                    (:payment_uuid, :request_id, :household_id, :receipt_number,
                     2026, 2026, :payment_date, 'ANNUAL',
                     :place, :collector_id, :membership_total, :donation_total,
                     :purpose, 'APP', 'ACTIVE', 'ELECTRONIC',
                     :created_by_user_id)"
            );
            $insertPayment->execute([
                ':payment_uuid' => $paymentUuid,
                ':request_id' => $requestId,
                ':household_id' => $householdId,
                ':receipt_number' => $lockedNextReceipt,
                ':payment_date' => (string) $preview['payment']['paymentDate'],
                ':place' => (string) $preview['payment']['place'],
                ':collector_id' => $collectorId,
                ':membership_total' => $this->decimal($membershipAmount),
                ':donation_total' => $this->decimal($donationAmount),
                ':purpose' => (string) $preview['payment']['purpose'],
                ':created_by_user_id' => $actorId,
            ]);
            $paymentId = (int) $this->pdo->lastInsertId();


            $insertItem = $this->pdo->prepare(
                "INSERT INTO payment_items
                    (payment_id, household_year_id, membership_amount,
                     donation_amount, item_status)
                 VALUES
                    (:payment_id, :household_year_id, :membership_amount,
                     :donation_amount, 'ACTIVE')"
            );
            $insertItem->execute([
                ':payment_id' => $paymentId,
                ':household_year_id' => $householdYearId,
                ':membership_amount' => $this->decimal($membershipAmount),
                ':donation_amount' => $this->decimal($donationAmount),
            ]);
            $paymentItemId = (int) $this->pdo->lastInsertId();


            $insertActiveLink = $this->pdo->prepare(
                'INSERT INTO active_payment_links
                    (household_year_id, payment_item_id)
                 VALUES (:household_year_id, :payment_item_id)'
            );
            $insertActiveLink->execute([
                ':household_year_id' => $householdYearId,
                ':payment_item_id' => $paymentItemId,
            ]);


            $sourceRowsJson = $this->jsonEncode([$sourceRow]);
            $receiptPayload = [
                'phase' => self::PHASE,
                'canaryRollbackOnly' => true,
                'receiptNumber' => $formattedReceiptNumber,
                'token' => $receiptToken,
                'tokenColumn' => 'K',
                'tokenHeader' => 'TOKEN',
                'mainSpreadsheetId' => self::MAIN_2026_DOCUMENT_ID,
            ];
            $insertReceipt = $this->pdo->prepare(
                "INSERT INTO receipts
                    (payment_id, receipt_sheet_year, receipt_sheet_row,
                     full_name_snapshot, first_name_snapshot, last_name_snapshot,
                     mahalla_snapshot, members_snapshot, place_snapshot,
                     collector_snapshot, years_text, source_rows_json,
                     html_payload_json, receipt_link, receipt_status)
                 VALUES
                    (:payment_id, 2026, NULL,
                     :full_name, :first_name, :last_name,
                     :mahalla, :members_count, :place,
                     :collector, '2026', :source_rows_json,
                     :html_payload_json, NULL, 'ACTIVE')"
            );
            $insertReceipt->execute([
                ':payment_id' => $paymentId,
                ':full_name' => (string) $preview['householdYear']['fullName'],
                ':first_name' => (string) $preview['householdYear']['firstName'],
                ':last_name' => (string) $preview['householdYear']['lastName'],
                ':mahalla' => (string) $preview['householdYear']['mahalla'],
                ':members_count' => (int) $preview['householdYear']['membersCount'],
                ':place' => (string) $preview['payment']['place'],
                ':collector' => (string) $preview['payment']['collectorDisplayName'],
                ':source_rows_json' => $sourceRowsJson,
                ':html_payload_json' => $this->jsonEncode($receiptPayload),
            ]);
            $receiptId = (int) $this->pdo->lastInsertId();


            $syncPayload = [
                'phase' => self::PHASE,
                'canaryRollbackOnly' => true,
                'paymentId' => $paymentId,
                'paymentItemId' => $paymentItemId,
                'receiptId' => $receiptId,
                'receiptNumber' => $lockedNextReceipt,
                'formattedReceiptNumber' => $formattedReceiptNumber,
                'receiptToken' => $receiptToken,
                'spreadsheetId' => self::MAIN_2026_DOCUMENT_ID,
                'annualSheet' => '2026',
                'annualRow' => $sourceRow,
                'receiptsSheet' => 'PRIZNANICE',
                'receiptTokenColumn' => 'K',
                'receiptTokenHeader' => 'TOKEN',
            ];
            $insertSync = $this->pdo->prepare(
                "INSERT INTO sync_queue
                    (direction, entity_type, entity_id, operation_name,
                     dedupe_key, payload_json, queue_status, priority,
                     attempts, next_attempt_at)
                 VALUES
                    ('DB_TO_SHEETS', 'PAYMENT', :entity_id, 'UPSERT',
                     :dedupe_key, :payload_json, 'PENDING', 1,
                     0, UTC_TIMESTAMP())"
            );
            $insertSync->execute([
                ':entity_id' => $paymentId,
                ':dedupe_key' => $dedupeKey,
                ':payload_json' => $this->jsonEncode($syncPayload),
            ]);
            $syncQueueId = (int) $this->pdo->lastInsertId();


            $newAuditData = [
                'phase' => self::PHASE,
                'canaryRollbackOnly' => true,
                'paymentId' => $paymentId,
                'paymentItemId' => $paymentItemId,
                'receiptId' => $receiptId,
                'syncQueueId' => $syncQueueId,
                'receiptNumber' => $lockedNextReceipt,
                'householdYearId' => $householdYearId,
            ];
            $insertAudit = $this->pdo->prepare(
                "INSERT INTO audit_log
                    (user_id, action_name, entity_type, entity_id,
                     source_system, request_id, old_data_json, new_data_json,
                     ip_address, user_agent)
                 VALUES
                    (:user_id, 'PHASE7C_A2_CANARY_CREATE', 'PAYMENT', :entity_id,
                     'WEB', :request_id, NULL, :new_data_json,
                     :ip_address, :user_agent)"
            );
            $insertAudit->execute([
                ':user_id' => $actorId,
                ':entity_id' => $paymentId,
                ':request_id' => $requestId,
                ':new_data_json' => $this->jsonEncode($newAuditData),
                ':ip_address' => $this->nullableText($input['ipAddress'] ?? null, 45),
                ':user_agent' => $this->nullableText($input['userAgent'] ?? null, 500),
            ]);
            $auditId = (int) $this->pdo->lastInsertId();


            $inside = $this->insideSnapshot(
                $requestId,
                $dedupeKey,
                $householdYearId,
                $lockedNextReceipt
            );
            $inside['ids'] = [
                'paymentId' => $paymentId,
                'paymentItemId' => $paymentItemId,
                'receiptId' => $receiptId,
                'syncQueueId' => $syncQueueId,
                'auditId' => $auditId,
            ];


            $insideValid =
                ($inside['payments'] ?? 0) === 1
                && ($inside['paymentItems'] ?? 0) === 1
                && ($inside['receipts'] ?? 0) === 1
                && ($inside['idempotencyRequests'] ?? 0) === 1
                && ($inside['syncQueue'] ?? 0) === 1
                && ($inside['auditLog'] ?? 0) === 1
                && ($inside['activePaymentLinks'] ?? 0) === 1
                && ($inside['sequenceValue'] ?? -1) === $lockedNextReceipt
                && ($inside['maxReceiptNumber'] ?? -1) === $lockedNextReceipt;
            if (!$insideValid) {
                throw new ApiException(
                    'CANARY_INSIDE_TRANSACTION_CHECK_FAILED',
                    'Kontrola zapisa unutar transakcije nije prošla.',
                    500,
                    ['inside' => $inside]
                );
            }


            $idempotencyResponse = [
                'phase' => self::PHASE,
                'insideTransactionValid' => true,
                'rollbackRequired' => true,
                'paymentId' => $paymentId,
                'receiptNumber' => $lockedNextReceipt,
            ];
            $finishIdempotency = $this->pdo->prepare(
                "UPDATE idempotency_requests
                 SET response_body = :response_body,
                     request_status = 'DONE'
                 WHERE BINARY request_id = BINARY :request_id"
            );
            $finishIdempotency->execute([
                ':response_body' => $this->jsonEncode($idempotencyResponse),
                ':request_id' => $requestId,
            ]);


            $this->pdo->rollBack();
            $rolledBack = true;
        } catch (Throwable $exception) {
            $error = [
                'type' => get_class($exception),
                'message' => $exception->getMessage(),
                'code' => (string) $exception->getCode(),
            ];
            if ($this->pdo->inTransaction()) {
                $this->pdo->rollBack();
                $rolledBack = true;
            }
        }


        $after = $this->snapshot($requestId, $dedupeKey, $householdYearId);
        $rollbackVerified =
            $rolledBack
            && ($after['payments'] ?? -1) === 0
            && ($after['paymentItems'] ?? -1) === 0
            && ($after['receipts'] ?? -1) === 0
            && ($after['idempotencyRequests'] ?? -1) === 0
            && ($after['syncQueue'] ?? -1) === 0
            && ($after['auditLog'] ?? -1) === 0
            && ($after['activePaymentLinks'] ?? -1) === ($before['activePaymentLinks'] ?? -2)
            && ($after['sequenceValue'] ?? -1) === ($before['sequenceValue'] ?? -2)
            && ($after['maxReceiptNumber'] ?? -1) === ($before['maxReceiptNumber'] ?? -2);


        $valid = $error === null && $rollbackVerified;


        return [
            'phase' => self::PHASE,
            'version' => self::VERSION,
            'mode' => 'STAGING_CANARY_ROLLBACK_ONLY',
            'valid' => $valid,
            'writesAttemptedInsideTransaction' => true,
            'writesPersisted' => false,
            'rollbackExecuted' => $rolledBack,
            'rollbackVerified' => $rollbackVerified,
            'commitAvailable' => false,
            'googleSheetsCalls' => 0,
            'googleSheetsWrites' => 0,
            'productionIndexUntouched' => true,
            'requestId' => $requestId,
            'candidate' => [
                'householdYearId' => $householdYearId,
                'householdId' => $householdId,
                'sourceRow' => $sourceRow,
                'fullName' => (string) $preview['householdYear']['fullName'],
                'membershipAmount' => $membershipAmount,
                'donationAmount' => $donationAmount,
                'collector' => (string) $preview['payment']['collectorDisplayName'],
                'previewReceiptNumber' => $formattedReceiptNumber,
            ],
            'before' => $before,
            'insideTransaction' => $inside,
            'afterRollback' => $after,
            'error' => $error,
            'readyForPhase7CA3' => $valid,
            'nextStep' => $valid
                ? 'READY_FOR_7C_A3_SINGLE_REAL_CANARY'
                : 'STOP_AND_REVIEW_PHASE_7C_A2_RESULT',
            'importantNote' => 'InnoDB AUTO_INCREMENT brojači mogu preskočiti interne ID vrednosti i posle ROLLBACK-a; nijedan poslovni red niti broj priznanice ne ostaje upisan.',
            'checkedAtUtc' => gmdate('c'),
        ];
    }


    /** @return array<string,array<int,string>> */
    private function requiredColumns(): array
    {
        return [
            'number_sequences' => ['sequence_name', 'current_value', 'padding_length'],
            'households' => ['id', 'is_active'],
            'household_years' => ['id', 'household_id', 'due_amount', 'row_version', 'record_status', 'is_exempt'],
            'payments' => ['id', 'payment_uuid', 'request_id', 'household_id', 'receipt_number', 'receipt_year', 'cash_year', 'payment_date', 'payment_type', 'place', 'collector_id', 'membership_total', 'donation_total', 'purpose', 'origin', 'payment_status', 'receipt_mode', 'created_by_user_id'],
            'payment_items' => ['id', 'payment_id', 'household_year_id', 'membership_amount', 'donation_amount', 'item_status'],
            'active_payment_links' => ['household_year_id', 'payment_item_id'],
            'receipts' => ['id', 'payment_id', 'receipt_sheet_year', 'receipt_sheet_row', 'full_name_snapshot', 'first_name_snapshot', 'last_name_snapshot', 'mahalla_snapshot', 'members_snapshot', 'place_snapshot', 'collector_snapshot', 'years_text', 'source_rows_json', 'html_payload_json', 'receipt_link', 'receipt_status'],
            'idempotency_requests' => ['request_id', 'action_name', 'user_id', 'request_hash', 'response_body', 'request_status', 'expires_at'],
            'sync_queue' => ['id', 'direction', 'entity_type', 'entity_id', 'operation_name', 'dedupe_key', 'payload_json', 'queue_status', 'priority', 'attempts', 'next_attempt_at'],
            'audit_log' => ['id', 'user_id', 'action_name', 'entity_type', 'entity_id', 'source_system', 'request_id', 'old_data_json', 'new_data_json', 'ip_address', 'user_agent'],
        ];
    }


    private function productionIndexPath(): string
    {
        $documentRoot = isset($_SERVER['DOCUMENT_ROOT'])
            ? rtrim((string) $_SERVER['DOCUMENT_ROOT'], '/\\')
            : dirname(__DIR__, 4) . '/gornjarapca.skolarbbos.net';


        return $documentRoot . '/api/v1/index.php';
    }


    /** @return array<string,int> */
    private function snapshot(
        string $requestId,
        string $dedupeKey,
        int $householdYearId
    ): array {
        $paymentCount = $this->scalarPrepared(
            'SELECT COUNT(*) FROM payments WHERE BINARY request_id = BINARY :value',
            $requestId
        );
        $paymentItems = $this->scalarPrepared(
            'SELECT COUNT(*)
             FROM payment_items pi
             JOIN payments p ON p.id = pi.payment_id
             WHERE BINARY p.request_id = BINARY :value',
            $requestId
        );
        $receipts = $this->scalarPrepared(
            'SELECT COUNT(*)
             FROM receipts r
             JOIN payments p ON p.id = r.payment_id
             WHERE BINARY p.request_id = BINARY :value',
            $requestId
        );
        $idempotency = $this->scalarPrepared(
            'SELECT COUNT(*) FROM idempotency_requests WHERE BINARY request_id = BINARY :value',
            $requestId
        );
        $sync = $this->scalarPrepared(
            'SELECT COUNT(*) FROM sync_queue WHERE BINARY dedupe_key = BINARY :value',
            $dedupeKey
        );
        $audit = $this->scalarPrepared(
            'SELECT COUNT(*) FROM audit_log WHERE BINARY request_id = BINARY :value',
            $requestId
        );
        $activeLinkStatement = $this->pdo->prepare(
            'SELECT COUNT(*) FROM active_payment_links WHERE household_year_id = :household_year_id'
        );
        $activeLinkStatement->execute([':household_year_id' => $householdYearId]);


        return [
            'payments' => $paymentCount,
            'paymentItems' => $paymentItems,
            'receipts' => $receipts,
            'idempotencyRequests' => $idempotency,
            'syncQueue' => $sync,
            'auditLog' => $audit,
            'activePaymentLinks' => (int) $activeLinkStatement->fetchColumn(),
            'sequenceValue' => (int) $this->pdo->query(
                "SELECT current_value FROM number_sequences
                 WHERE BINARY sequence_name = BINARY 'receipt'"
            )->fetchColumn(),
            'maxReceiptNumber' => (int) $this->pdo->query(
                'SELECT COALESCE(MAX(receipt_number), 0) FROM payments'
            )->fetchColumn(),
        ];
    }


    /** @return array<string,int> */
    private function insideSnapshot(
        string $requestId,
        string $dedupeKey,
        int $householdYearId,
        int $receiptNumber
    ): array {
        $snapshot = $this->snapshot($requestId, $dedupeKey, $householdYearId);
        $receiptNumberCountStatement = $this->pdo->prepare(
            'SELECT COUNT(*) FROM payments
             WHERE receipt_number = :receipt_number
               AND BINARY request_id = BINARY :request_id'
        );
        $receiptNumberCountStatement->execute([
            ':receipt_number' => $receiptNumber,
            ':request_id' => $requestId,
        ]);
        $snapshot['receiptNumberRows'] = (int) $receiptNumberCountStatement->fetchColumn();
        return $snapshot;
    }


    private function scalarPrepared(string $sql, string $value): int
    {
        $statement = $this->pdo->prepare($sql);
        $statement->execute([':value' => $value]);
        return (int) $statement->fetchColumn();
    }


    private function uuidV4(): string
    {
        $bytes = random_bytes(16);
        $bytes[6] = chr((ord($bytes[6]) & 0x0f) | 0x40);
        $bytes[8] = chr((ord($bytes[8]) & 0x3f) | 0x80);
        $hex = bin2hex($bytes);
        return substr($hex, 0, 8) . '-'
            . substr($hex, 8, 4) . '-'
            . substr($hex, 12, 4) . '-'
            . substr($hex, 16, 4) . '-'
            . substr($hex, 20, 12);
    }


    /** @param mixed $value */
    private function money($value): float
    {
        if (is_int($value) || is_float($value)) {
            return round((float) $value, 2);
        }
        $text = trim((string) $value);
        $text = str_replace(["\u{00A0}", '€', ' '], '', $text);
        if ($text === '') {
            return 0.0;
        }
        $comma = strrpos($text, ',');
        $dot = strrpos($text, '.');
        if ($comma !== false && $dot !== false) {
            if ($comma > $dot) {
                $text = str_replace('.', '', $text);
                $text = str_replace(',', '.', $text);
            } else {
                $text = str_replace(',', '', $text);
            }
        } elseif ($comma !== false) {
            $text = str_replace('.', '', $text);
            $text = str_replace(',', '.', $text);
        }
        if (!is_numeric($text)) {
            throw new ApiException(
                'INVALID_MONEY_VALUE',
                'Finansijski iznos nije ispravan.',
                400
            );
        }
        return round((float) $text, 2);
    }


    private function decimal(float $value): string
    {
        return number_format($value, 2, '.', '');
    }


    /** @param mixed $value */
    private function nullableText($value, int $maxLength): ?string
    {
        $text = trim((string) $value);
        if ($text === '') {
            return null;
        }
        return mb_substr($text, 0, $maxLength);
    }


    /** @param mixed $value */
    private function jsonEncode($value): string
    {
        $json = json_encode(
            $value,
            JSON_UNESCAPED_UNICODE
            | JSON_UNESCAPED_SLASHES
            | JSON_PRESERVE_ZERO_FRACTION
            | JSON_THROW_ON_ERROR
        );
        return $json;
    }
}