<?php

declare(strict_types=1);

namespace Sogr\Service;

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

/**
 * Produkcioni write sloj Faze 7C.
 * Svaki poslovni zahtev je atomski u MySQL-u, idempotentan i ostavlja
 * audit + DB_TO_SHEETS queue zapis. Google Sheets se poziva tek posle COMMIT-a.
 */
final class Phase7CWriteService
{
    public const VERSION = '7C-A6-R2-R1.1';
    public const MODE = 'MYSQL_PRIMARY_TRANSACTIONAL_WRITES';

    /** @var list<string> */
    public const ACTIONS = [
        'pay','payMultiple','storno','updateMember','saveExpense','deleteExpense',
        'saveTransfer','saveExtraIncome','deleteExtraIncome','saveReceiptPdf',
    ];

    private PDO $pdo;
    private Phase7CSyncService $sync;

    public function __construct(?PDO $pdo = null, ?Phase7CSyncService $sync = null)
    {
        $this->pdo = $pdo ?? Database::connection();
        $this->sync = $sync ?? new Phase7CSyncService($this->pdo);
    }

    /** @param array<string,mixed> $payload @param array<string,mixed> $session @return array<string,mixed> */
    public function execute(string $action, array $payload, array $session): array
    {
        if (!in_array($action, self::ACTIONS, true)) {
            throw new ApiException('WRITE_ACTION_NOT_ALLOWED', 'Poslovna akcija nije dozvoljena.', 400);
        }
        $user = $this->sessionUser($session);
        $payload = $this->normalizePayload($action, $payload);
        $requestId = (string) $payload['requestId'];
        $requestHash = $this->requestHash($action, $payload);

        $existing = $this->recoverExisting($requestId, $requestHash);
        if ($existing !== null) {
            return $existing;
        }

        $this->pdo->beginTransaction();
        try {
            $this->insertIdempotency($requestId, $action, $user['id'], $requestHash);
            $result = match ($action) {
                'pay' => $this->pay($payload, $user),
                'payMultiple' => $this->payMultiple($payload, $user),
                'storno' => $this->storno($payload, $user),
                'updateMember' => $this->updateMember($payload, $user),
                'saveExpense' => $this->saveExpense($payload, $user),
                'deleteExpense' => $this->deleteExpense($payload, $user),
                'saveTransfer' => $this->saveTransfer($payload, $user),
                'saveExtraIncome' => $this->saveExtraIncome($payload, $user),
                'deleteExtraIncome' => $this->deleteExtraIncome($payload, $user),
                'saveReceiptPdf' => $this->saveReceiptPdf($payload, $user),
                default => throw new ApiException('WRITE_ACTION_NOT_IMPLEMENTED', 'Akcija nije implementirana.', 500),
            };

            $queueId = $this->insertQueue($action, $requestId, $result['queue']);
            $response = $result['response'];
            $response['action'] = $action;
            $response['ok'] = true;
            $response['requestId'] = $requestId;
            $response['mysqlCommitted'] = true;
            $response['sync'] = ['queueId' => $queueId, 'status' => 'PENDING'];
            $this->updateIdempotencyBody($requestId, $response, 'PROCESSING');
            $this->pdo->commit();
        } catch (PDOException $e) {
            if ($this->pdo->inTransaction()) {
                $this->pdo->rollBack();
            }
            if ((string) $e->getCode() === '23000') {
                $existing = $this->recoverExisting($requestId, $requestHash);
                if ($existing !== null) {
                    return $existing;
                }
                throw new ApiException('WRITE_CONFLICT', 'Podaci su u međuvremenu promenjeni ili je zahtev već izvršen.', 409, [], $e);
            }
            throw $e;
        } catch (Throwable $e) {
            if ($this->pdo->inTransaction()) {
                $this->pdo->rollBack();
            }
            throw $e;
        }

        // Svaki novi poslovni zahtev prvo pokušava i starije neuspele queue zapise.
        // Tako se kratkotrajan prekid Google veze sam oporavlja bez ručnog rada.
        $batch = $this->sync->processPending(20);
        $syncResult = null;
        foreach (($batch['results'] ?? []) as $candidate) {
            if (is_array($candidate) && (int) ($candidate['queueId'] ?? 0) === $queueId) {
                $syncResult = $candidate;
                break;
            }
        }
        if (!is_array($syncResult)) {
            $syncResult = $this->sync->processQueueId($queueId);
        }
        if (($syncResult['ok'] ?? false) === true) {
            $response = $this->mergeBridgeResult($action, $response, $syncResult['bridgeResult'] ?? []);
            $response['sync'] = [
                'queueId' => $queueId,
                'status' => 'DONE',
                'sheetsSynced' => true,
            ];
            $this->finalizeIdempotency($requestId, $response);
            return $response;
        }

        $response['sync'] = [
            'queueId' => $queueId,
            'status' => 'FAILED',
            'sheetsSynced' => false,
            'retryScheduled' => true,
            'error' => (string) ($syncResult['error'] ?? 'Google Sheets sinhronizacija nije uspela.'),
        ];
        $response['syncPending'] = true;

        if ($action === 'saveReceiptPdf') {
            // Za PDF ne tvrdi da je Drive fajl sačuvan dok bridge stvarno ne uspe.
            $this->updateIdempotencyBody($requestId, $response, 'PROCESSING');
            throw new ApiException(
                'PDF_SYNC_PENDING',
                'PDF je primljen i bezbedno čeka u redu, ali još nije sačuvan na Google Drive. Pokušajte ponovo.',
                503,
                ['queueId' => $queueId]
            );
        }

        // MySQL poslovni COMMIT je autoritativan; Sheets će biti ponovljen iz reda.
        $this->finalizeIdempotency($requestId, $response);
        return $response;
    }

    /** @return array<string,mixed> */
    public function status(): array
    {
        $sequence = (int) $this->pdo->query(
            "SELECT current_value FROM number_sequences WHERE sequence_name='receipt'"
        )->fetchColumn();
        $pending = (int) $this->pdo->query(
            "SELECT COUNT(*) FROM sync_queue WHERE queue_status IN ('PENDING','PROCESSING','FAILED','CONFLICT')"
        )->fetchColumn();
        $conflicts = (int) $this->pdo->query(
            "SELECT COUNT(*) FROM sync_conflicts WHERE conflict_status='OPEN'"
        )->fetchColumn();
        return [
            'version' => self::VERSION,
            'mode' => self::MODE,
            'actions' => self::ACTIONS,
            'actionCount' => count(self::ACTIONS),
            'sequenceValue' => $sequence,
            'pendingOrFailedSync' => $pending,
            'openConflicts' => $conflicts,
            'sync' => $this->sync->status(false),
            'ready' => $conflicts === 0,
        ];
    }

    /** @param array<string,mixed> $payload @param array{id:int,displayName:string} $user @return array<string,mixed> */
    private function pay(array $payload, array $user): array
    {
        $year = $this->year($payload['year'] ?? null);
        $row = $this->positiveInt($payload['row'] ?? null, 'row');
        $hy = $this->householdYearBySheet($year, $row, true);
        $this->assertName($hy, $payload);
        $this->assertNoActivePayment((int) $hy['id']);

        $membership = $this->money($payload['iznos'] ?? 0, 'iznos', true);
        $donation = $this->money($payload['donacija'] ?? 0, 'donacija', false);
        $due = round((float) $hy['due_amount'], 2);
        if (abs($membership - $due) > 0.01) {
            throw new ApiException('PARTIAL_PAYMENT_NOT_ALLOWED', 'Delimična uplata nije dozvoljena. Očekivani iznos je ' . number_format($due, 2, '.', '') . ' €.', 409);
        }
        if ($membership <= 0 && $donation <= 0) {
            throw new ApiException('PAYMENT_AMOUNT_REQUIRED', 'Unesite iznos uplate ili donacije.', 400);
        }

        $collector = $this->boardMember((string) ($payload['odbor'] ?? ''));
        $receiptNumber = $this->nextReceiptNumber();
        $date = $this->date($payload['datum'] ?? null);
        $place = $this->requiredText($payload['mesto'] ?? '', 'mesto', 100);
        $purpose = $this->annualPurpose($year);
        $paymentUuid = $this->uuid();

        $stmt = $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(:uuid,:request,:household,:receipt,:receipt_year,:cash_year,:date,'ANNUAL',:place,:collector,
             :membership,:donation,:purpose,'APP','ACTIVE','ELECTRONIC',:user)"
        );
        $stmt->execute([
            ':uuid' => $paymentUuid,
            ':request' => $payload['requestId'],
            ':household' => $hy['household_id'],
            ':receipt' => $receiptNumber,
            ':receipt_year' => $year,
            ':cash_year' => $year,
            ':date' => $date,
            ':place' => $place,
            ':collector' => $collector['id'],
            ':membership' => $this->decimal($membership),
            ':donation' => $this->decimal($donation),
            ':purpose' => $purpose,
            ':user' => $user['id'],
        ]);
        $paymentId = (int) $this->pdo->lastInsertId();

        $item = $this->pdo->prepare(
            "INSERT INTO payment_items(payment_id,household_year_id,membership_amount,donation_amount,item_status)
             VALUES(:payment,:hy,:membership,:donation,'ACTIVE')"
        );
        $item->execute([
            ':payment' => $paymentId,
            ':hy' => $hy['id'],
            ':membership' => $this->decimal($membership),
            ':donation' => $this->decimal($donation),
        ]);
        $itemId = (int) $this->pdo->lastInsertId();
        $link = $this->pdo->prepare('INSERT INTO active_payment_links(household_year_id,payment_item_id) VALUES(:hy,:item)');
        $link->execute([':hy' => $hy['id'], ':item' => $itemId]);

        $receipt = $this->insertReceipt(
            $paymentId,
            $year,
            $hy,
            $collector,
            [$year => $row],
            (string) $year,
            $place,
            $purpose,
            $membership,
            $donation,
            $date,
            $receiptNumber
        );

        $response = ['priznanica' => $receipt];
        $jobPayload = $payload;
        $jobPayload['expectedReceiptNumber'] = $receiptNumber;
        $this->audit($user['id'], 'PAY', 'PAYMENT', $paymentId, (string) $payload['requestId'], null, $response);
        return [
            'response' => $response,
            'queue' => [
                'entityType' => 'PAYMENT', 'entityId' => $paymentId, 'operation' => 'UPSERT',
                'job' => $this->job('pay', $jobPayload, $user, $receiptNumber, ['HOUSEHOLD_YEAR' => [(int) $hy['id']]]),
            ],
        ];
    }

    /** @param array<string,mixed> $payload @param array{id:int,displayName:string} $user @return array<string,mixed> */
    private function payMultiple(array $payload, array $user): array
    {
        $itemsIn = $payload['items'] ?? null;
        if (!is_array($itemsIn) || count($itemsIn) < 2 || count($itemsIn) > 7) {
            throw new ApiException('MULTI_PAYMENT_ITEMS_INVALID', 'Izaberite najmanje dve različite godine.', 400);
        }
        $rows = [];
        $seenYears = [];
        $householdId = 0;
        foreach ($itemsIn as $itemIn) {
            if (!is_array($itemIn)) {
                throw new ApiException('MULTI_PAYMENT_ITEM_INVALID', 'Jedna stavka višegodišnje uplate nije ispravna.', 400);
            }
            $year = $this->year($itemIn['year'] ?? null);
            if (isset($seenYears[$year])) {
                throw new ApiException('MULTI_PAYMENT_DUPLICATE_YEAR', 'Godina je izabrana više puta.', 400);
            }
            $seenYears[$year] = true;
            $row = $this->positiveInt($itemIn['row'] ?? null, 'row');
            $hy = $this->householdYearBySheet($year, $row, true);
            $this->assertName($hy, $payload);
            $this->assertNoActivePayment((int) $hy['id']);
            if ($householdId === 0) {
                $householdId = (int) $hy['household_id'];
            } elseif ($householdId !== (int) $hy['household_id']) {
                throw new ApiException('MULTI_PAYMENT_HOUSEHOLD_MISMATCH', 'Izabrane godine ne pripadaju istom domaćinstvu.', 409);
            }
            $rows[] = ['year' => $year, 'row' => $row, 'hy' => $hy];
        }
        usort($rows, static fn(array $a, array $b): int => $a['year'] <=> $b['year']);
        $donation = $this->money($payload['donacija'] ?? 0, 'donacija', false);
        $collector = $this->boardMember((string) ($payload['odbor'] ?? ''));
        $receiptNumber = $this->nextReceiptNumber();
        $date = $this->date($payload['datum'] ?? null);
        $place = $this->requiredText($payload['mesto'] ?? '', 'mesto', 100);
        $years = array_map(static fn(array $r): int => (int) $r['year'], $rows);
        $latest = $rows[count($rows) - 1];
        $receiptYear = max(array_keys($this->availableYears()));
        $purpose = $this->multiPurpose($years);
        $membershipTotal = 0.0;
        foreach ($rows as $row) {
            $membershipTotal += round((float) $row['hy']['due_amount'], 2);
        }
        $membershipTotal = round($membershipTotal, 2);

        $stmt = $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(:uuid,:request,:household,:receipt,:receipt_year,:cash_year,:date,'MULTI_YEAR',:place,
             :collector,:membership,:donation,:purpose,'APP','ACTIVE','ELECTRONIC',:user)"
        );
        $stmt->execute([
            ':uuid' => $this->uuid(), ':request' => $payload['requestId'], ':household' => $householdId,
            ':receipt' => $receiptNumber, ':receipt_year' => $receiptYear, ':cash_year' => $receiptYear, ':date' => $date, ':place' => $place,
            ':collector' => $collector['id'], ':membership' => $this->decimal($membershipTotal),
            ':donation' => $this->decimal($donation), ':purpose' => $purpose, ':user' => $user['id'],
        ]);
        $paymentId = (int) $this->pdo->lastInsertId();
        $sourceRows = [];
        $hyIds = [];
        foreach ($rows as $row) {
            $isLatest = (int) $row['year'] === (int) $latest['year'];
            $item = $this->pdo->prepare(
                "INSERT INTO payment_items(payment_id,household_year_id,membership_amount,donation_amount,item_status)
                 VALUES(:payment,:hy,:membership,:donation,'ACTIVE')"
            );
            $item->execute([
                ':payment' => $paymentId,
                ':hy' => $row['hy']['id'],
                ':membership' => $this->decimal((float) $row['hy']['due_amount']),
                ':donation' => $this->decimal($isLatest ? $donation : 0.0),
            ]);
            $itemId = (int) $this->pdo->lastInsertId();
            $link = $this->pdo->prepare('INSERT INTO active_payment_links(household_year_id,payment_item_id) VALUES(:hy,:item)');
            $link->execute([':hy' => $row['hy']['id'], ':item' => $itemId]);
            $sourceRows[(string) $row['year']] = (int) $row['row'];
            $hyIds[] = (int) $row['hy']['id'];
        }
        $receipt = $this->insertReceipt(
            $paymentId,
            $receiptYear,
            $latest['hy'],
            $collector,
            $sourceRows,
            implode(',', $years),
            $place,
            $purpose,
            $membershipTotal,
            $donation,
            $date,
            $receiptNumber
        );
        $response = ['affectedYears' => array_map('strval', $years), 'receiptYear' => (string) $receiptYear, 'priznanica' => $receipt];
        $jobPayload = $payload;
        $jobPayload['expectedReceiptNumber'] = $receiptNumber;
        $this->audit($user['id'], 'PAY_MULTIPLE', 'PAYMENT', $paymentId, (string) $payload['requestId'], null, $response);
        return [
            'response' => $response,
            'queue' => [
                'entityType' => 'PAYMENT', 'entityId' => $paymentId, 'operation' => 'UPSERT',
                'job' => $this->job('payMultiple', $jobPayload, $user, $receiptNumber, ['HOUSEHOLD_YEAR' => $hyIds]),
            ],
        ];
    }

    /** @param array<string,mixed> $payload @param array{id:int,displayName:string} $user @return array<string,mixed> */
    private function storno(array $payload, array $user): array
    {
        $year = $this->year($payload['year'] ?? null);
        $row = $this->positiveInt($payload['row'] ?? null, 'row');
        $hy = $this->householdYearBySheet($year, $row, true);
        $this->assertName($hy, $payload);
        $stmt = $this->pdo->prepare(
            "SELECT p.* FROM active_payment_links apl
             JOIN payment_items pi ON pi.id=apl.payment_item_id AND pi.item_status='ACTIVE'
             JOIN payments p ON p.id=pi.payment_id AND p.payment_status='ACTIVE'
             WHERE apl.household_year_id=:hy FOR UPDATE"
        );
        $stmt->execute([':hy' => $hy['id']]);
        $payment = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!is_array($payment)) {
            return [
                'response' => ['vecPonisteno' => true, 'affectedYears' => [(string) $year]],
                'queue' => [
                    'entityType' => 'PAYMENT', 'entityId' => null, 'operation' => 'VOID',
                    'job' => $this->job('storno', $payload, $user, null, ['HOUSEHOLD_YEAR' => [(int) $hy['id']]]),
                ],
            ];
        }
        $paymentId = (int) $payment['id'];
        $yearsStmt = $this->pdo->prepare(
            'SELECT hy.id,hy.year FROM payment_items pi JOIN household_years hy ON hy.id=pi.household_year_id
             WHERE pi.payment_id=:payment ORDER BY hy.year'
        );
        $yearsStmt->execute([':payment' => $paymentId]);
        $items = $yearsStmt->fetchAll(PDO::FETCH_ASSOC);
        $affectedYears = array_map(static fn(array $r): string => (string) $r['year'], $items);
        $hyIds = array_map(static fn(array $r): int => (int) $r['id'], $items);

        $old = $payment;
        $update = $this->pdo->prepare(
            "UPDATE payments SET payment_status='VOID',voided_by_user_id=:user,voided_at=UTC_TIMESTAMP(),
             void_reason='Poništeno kroz aplikaciju',row_version=row_version+1 WHERE id=:id AND payment_status='ACTIVE'"
        );
        $update->execute([':user' => $user['id'], ':id' => $paymentId]);
        $itemsUpdate = $this->pdo->prepare(
            "UPDATE payment_items SET item_status='VOID',row_version=row_version+1 WHERE payment_id=:id AND item_status='ACTIVE'"
        );
        $itemsUpdate->execute([':id' => $paymentId]);
        $deleteLinks = $this->pdo->prepare(
            'DELETE apl FROM active_payment_links apl JOIN payment_items pi ON pi.id=apl.payment_item_id WHERE pi.payment_id=:id'
        );
        $deleteLinks->execute([':id' => $paymentId]);
        $receiptUpdate = $this->pdo->prepare(
            "UPDATE receipts SET receipt_status='VOID',row_version=row_version+1 WHERE payment_id=:id AND receipt_status='ACTIVE'"
        );
        $receiptUpdate->execute([':id' => $paymentId]);
        $response = [
            'affectedYears' => $affectedYears,
            'receiptYear' => (string) $payment['receipt_year'],
            'ponistenePriznanice' => [str_pad((string) $payment['receipt_number'], 5, '0', STR_PAD_LEFT)],
            'brojPonistenihPriznanica' => 1,
        ];
        $this->audit($user['id'], 'STORNO', 'PAYMENT', $paymentId, (string) $payload['requestId'], $old, $response);
        return [
            'response' => $response,
            'queue' => [
                'entityType' => 'PAYMENT', 'entityId' => $paymentId, 'operation' => 'VOID',
                'job' => $this->job('storno', $payload, $user, null, ['HOUSEHOLD_YEAR' => $hyIds]),
            ],
        ];
    }

    /** @param array<string,mixed> $payload @param array{id:int,displayName:string} $user @return array<string,mixed> */
    private function updateMember(array $payload, array $user): array
    {
        $year = $this->year($payload['year'] ?? null);
        $row = $this->positiveInt($payload['row'] ?? null, 'row');
        $scope = strtolower(trim((string) ($payload['scope'] ?? 'forward')));
        if (!in_array($scope, ['year','forward'], true)) {
            throw new ApiException('MEMBER_SCOPE_INVALID', 'Opseg promene nije ispravan.', 400);
        }
        $start = $this->householdYearBySheet($year, $row, true);
        $original = is_array($payload['original'] ?? null) ? $payload['original'] : [];
        if ($original !== []) {
            $this->assertOriginal($start, $original);
        }
        $first = $this->requiredText($payload['ime'] ?? '', 'ime', 120);
        $last = $this->requiredText($payload['prezime'] ?? '', 'prezime', 120);
        $mahalla = $this->mahalla((string) ($payload['mahalo'] ?? ''));
        $members = $this->nonNegativeInt($payload['clanova'] ?? null, 'clanova', 99);
        $changed = is_array($payload['changedFields'] ?? null) ? $payload['changedFields'] : [];
        $flags = [
            'ime' => ($changed['ime'] ?? false) === true || strcasecmp($first, (string) $start['first_name']) !== 0,
            'prezime' => ($changed['prezime'] ?? false) === true || strcasecmp($last, (string) $start['last_name']) !== 0,
            'mahalo' => ($changed['mahalo'] ?? false) === true || (int) $mahalla['id'] !== (int) $start['mahalla_id'],
            'clanova' => ($changed['clanova'] ?? false) === true || $members !== (int) $start['members_count'],
        ];
        if (!in_array(true, $flags, true)) {
            throw new ApiException('MEMBER_NO_CHANGES', 'Nije promenjen nijedan podatak.', 400);
        }
        $op = $scope === 'year' ? '=' : '>=';
        $stmt = $this->pdo->prepare(
            "SELECT hy.* FROM household_years hy WHERE hy.household_id=:household AND hy.year $op :year
             AND hy.record_status='ACTIVE' ORDER BY hy.year FOR UPDATE"
        );
        $stmt->execute([':household' => $start['household_id'], ':year' => $year]);
        $targets = $stmt->fetchAll(PDO::FETCH_ASSOC);
        if ($targets === []) {
            throw new ApiException('MEMBER_TARGETS_NOT_FOUND', 'Nisu pronađene godine za promenu.', 409);
        }
        $updatedYears = [];
        $ids = [];
        foreach ($targets as $target) {
            $sets = ['row_version=row_version+1'];
            $params = [':id' => $target['id']];
            if ($flags['ime']) { $sets[] = 'first_name=:first'; $params[':first'] = $first; }
            if ($flags['prezime']) { $sets[] = 'last_name=:last'; $params[':last'] = $last; }
            if ($flags['mahalo']) { $sets[] = 'mahalla_id=:mahalla'; $params[':mahalla'] = $mahalla['id']; }
            if ($flags['clanova']) {
                $sets[] = 'members_count=:members'; $params[':members'] = $members;
                if ((string) $target['due_mode'] === 'RATE') {
                    $rateStmt = $this->pdo->prepare('SELECT amount_per_member FROM membership_rates WHERE year=:year AND is_active=1');
                    $rateStmt->execute([':year' => $target['year']]);
                    $rate = (float) $rateStmt->fetchColumn();
                    $sets[] = 'due_amount=:due'; $params[':due'] = $this->decimal($rate * $members);
                }
            }
            $update = $this->pdo->prepare('UPDATE household_years SET ' . implode(',', $sets) . ' WHERE id=:id');
            $update->execute($params);
            $updatedYears[] = (string) $target['year'];
            $ids[] = (int) $target['id'];
        }
        $current = $this->householdYearById((int) $start['id']);
        $response = [
            'row' => $this->legacyHouseholdRow($current, $row),
            'updatedYears' => $updatedYears,
            'skippedYears' => [],
            'scope' => $scope,
            'changedFields' => $flags,
            'warning' => '',
            'changedBy' => $user['displayName'],
        ];
        $this->audit($user['id'], 'UPDATE_MEMBER', 'HOUSEHOLD', (int) $start['household_id'], (string) $payload['requestId'], $start, $response);
        return [
            'response' => $response,
            'queue' => [
                'entityType' => 'HOUSEHOLD', 'entityId' => (int) $start['household_id'], 'operation' => 'UPSERT',
                'job' => $this->job('updateMember', $payload, $user, null, ['HOUSEHOLD_YEAR' => $ids]),
            ],
        ];
    }

    /** @param array<string,mixed> $payload @param array{id:int,displayName:string} $user @return array<string,mixed> */
    private function saveExpense(array $payload, array $user): array
    {
        $year = $this->year($payload['year'] ?? 2026);
        if ($year !== 2026) throw new ApiException('EXPENSE_YEAR_NOT_ALLOWED', 'Novi trošak je dozvoljen samo za 2026. godinu.', 400);
        $member = $this->boardMember((string) ($payload['odbor'] ?? ''));
        $amount = $this->money($payload['cena'] ?? 0, 'cena', true);
        if ($amount <= 0) throw new ApiException('EXPENSE_AMOUNT_INVALID', 'Cena mora biti veća od nule.', 400);
        $uuid = 'EXP-' . strtoupper(str_replace('-', '', $this->uuid()));
        $stmt = $this->pdo->prepare(
            "INSERT INTO expenses(expense_uuid,request_id,year,expense_date,purpose,contractor,amount,paid_by_member_id,
             origin,expense_status,created_by_user_id)
             VALUES(:uuid,:request,:year,:date,:purpose,:contractor,:amount,:member,'APP','ACTIVE',:user)"
        );
        $stmt->execute([
            ':uuid' => $uuid, ':request' => $payload['requestId'], ':year' => $year,
            ':date' => $this->date($payload['datum'] ?? null),
            ':purpose' => $this->requiredText($payload['namena'] ?? '', 'namena', 500),
            ':contractor' => $this->text($payload['majstor'] ?? '', 250),
            ':amount' => $this->decimal($amount), ':member' => $member['id'], ':user' => $user['id'],
        ]);
        $id = (int) $this->pdo->lastInsertId();
        $response = ['duplicate' => false, 'trosak' => [
            'id' => $uuid, 'dbId' => $id, 'datum' => $this->displayDate($this->date($payload['datum'] ?? null)),
            'namena' => (string) $payload['namena'], 'cena' => $amount, 'majstor' => (string) ($payload['majstor'] ?? ''),
            'odbor' => $member['code'], 'odborPuno' => $member['display_name'], 'sourceCell' => '',
        ]];
        $this->audit($user['id'], 'SAVE_EXPENSE', 'EXPENSE', $id, (string) $payload['requestId'], null, $response);
        return ['response' => $response, 'queue' => [
            'entityType' => 'EXPENSE', 'entityId' => $id, 'operation' => 'UPSERT',
            'job' => $this->job('saveExpense', $payload, $user, null, ['EXPENSE' => [$id]]),
        ]];
    }

    /** @param array<string,mixed> $payload @param array{id:int,displayName:string} $user @return array<string,mixed> */
    private function deleteExpense(array $payload, array $user): array
    {
        $uuid = $this->requiredText($payload['id'] ?? '', 'id', 100);
        $stmt = $this->pdo->prepare('SELECT e.*,bm.code,bm.display_name FROM expenses e JOIN board_members bm ON bm.id=e.paid_by_member_id WHERE e.expense_uuid=:uuid FOR UPDATE');
        $stmt->execute([':uuid' => $uuid]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!is_array($row) || (string) $row['expense_status'] === 'VOID') {
            return ['response' => ['alreadyDeleted' => true, 'trosak' => ['id' => $uuid]], 'queue' => [
                'entityType' => 'EXPENSE', 'entityId' => is_array($row) ? (int) $row['id'] : null, 'operation' => 'VOID',
                'job' => $this->job('deleteExpense', $payload, $user),
            ]];
        }
        $this->assertExpectedMoney($payload['expectedAmount'] ?? null, (float) $row['amount'], 'Iznos troška');
        if (trim((string) ($payload['expectedDescription'] ?? '')) !== '' && strcasecmp(trim((string) $payload['expectedDescription']), trim((string) $row['purpose'])) !== 0) {
            throw new ApiException('EXPENSE_CHANGED', 'Opis troška je u međuvremenu promenjen.', 409);
        }
        $update = $this->pdo->prepare("UPDATE expenses SET expense_status='VOID',voided_by_user_id=:user,voided_at=UTC_TIMESTAMP(),void_reason='Obrisano kroz aplikaciju',row_version=row_version+1 WHERE id=:id");
        $update->execute([':user' => $user['id'], ':id' => $row['id']]);
        $response = ['alreadyDeleted' => false, 'trosak' => [
            'id' => $uuid, 'row' => $this->sourceRow((string) ($row['source_reference'] ?? '')),
            'datum' => $this->displayDate((string) $row['expense_date']), 'namena' => (string) $row['purpose'],
            'cena' => (float) $row['amount'], 'majstor' => (string) ($row['contractor'] ?? ''), 'odbor' => (string) $row['code'],
        ]];
        $this->audit($user['id'], 'DELETE_EXPENSE', 'EXPENSE', (int) $row['id'], (string) $payload['requestId'], $row, $response);
        return ['response' => $response, 'queue' => [
            'entityType' => 'EXPENSE', 'entityId' => (int) $row['id'], 'operation' => 'VOID',
            'job' => $this->job('deleteExpense', $payload, $user, null, ['EXPENSE' => [(int) $row['id']]]),
        ]];
    }

    /** @param array<string,mixed> $payload @param array{id:int,displayName:string} $user @return array<string,mixed> */
    private function saveTransfer(array $payload, array $user): array
    {
        $year = $this->year($payload['year'] ?? 2026);
        $from = $this->boardMember((string) ($payload['odClana'] ?? ''));
        $to = $this->boardMember((string) ($payload['clanu'] ?? ''));
        if ($from['id'] === $to['id']) throw new ApiException('TRANSFER_SAME_MEMBER', 'Pošiljalac i primalac ne mogu biti isti član.', 400);
        $amount = $this->money($payload['iznos'] ?? 0, 'iznos', true);
        if ($amount <= 0) throw new ApiException('TRANSFER_AMOUNT_INVALID', 'Iznos transfera mora biti veći od nule.', 400);
        $uuid = 'TR-' . strtoupper(str_replace('-', '', $this->uuid()));
        $stmt = $this->pdo->prepare(
            "INSERT INTO transfers(transfer_uuid,request_id,year,transfer_date,from_member_id,to_member_id,amount,note,
             origin,transfer_status,created_by_user_id)
             VALUES(:uuid,:request,:year,:date,:from_id,:to_id,:amount,:note,'APP','ACTIVE',:user)"
        );
        $stmt->execute([
            ':uuid' => $uuid, ':request' => $payload['requestId'], ':year' => $year,
            ':date' => $this->date($payload['datum'] ?? null), ':from_id' => $from['id'], ':to_id' => $to['id'],
            ':amount' => $this->decimal($amount), ':note' => $this->text($payload['napomena'] ?? '', 500), ':user' => $user['id'],
        ]);
        $id = (int) $this->pdo->lastInsertId();
        $response = ['transfer' => ['id' => $uuid, 'dbId' => $id, 'year' => (string) $year,
            'datum' => $this->displayDate($this->date($payload['datum'] ?? null)), 'odClana' => $from['code'],
            'clanu' => $to['code'], 'iznos' => $amount, 'napomena' => (string) ($payload['napomena'] ?? '')]];
        $this->audit($user['id'], 'SAVE_TRANSFER', 'TRANSFER', $id, (string) $payload['requestId'], null, $response);
        return ['response' => $response, 'queue' => [
            'entityType' => 'TRANSFER', 'entityId' => $id, 'operation' => 'UPSERT',
            'job' => $this->job('saveTransfer', $payload, $user, null, ['TRANSFER' => [$id]]),
        ]];
    }

    /** @param array<string,mixed> $payload @param array{id:int,displayName:string} $user @return array<string,mixed> */
    private function saveExtraIncome(array $payload, array $user): array
    {
        $year = $this->year($payload['year'] ?? 2026);
        $member = $this->boardMember((string) ($payload['clan'] ?? ''));
        $amount = $this->money($payload['iznos'] ?? 0, 'iznos', true);
        if ($amount <= 0) throw new ApiException('INCOME_AMOUNT_INVALID', 'Iznos prihoda mora biti veći od nule.', 400);
        $uuid = 'INC-' . strtoupper(str_replace('-', '', $this->uuid()));
        $stmt = $this->pdo->prepare(
            "INSERT INTO extra_income(income_uuid,request_id,year,income_date,received_from,board_member_id,amount,note,
             origin,income_status,created_by_user_id)
             VALUES(:uuid,:request,:year,:date,:received_from,:member,:amount,NULL,'APP','ACTIVE',:user)"
        );
        $stmt->execute([
            ':uuid' => $uuid, ':request' => $payload['requestId'], ':year' => $year,
            ':date' => $this->date($payload['datum'] ?? null),
            ':received_from' => $this->requiredText($payload['od'] ?? '', 'od', 250),
            ':member' => $member['id'], ':amount' => $this->decimal($amount), ':user' => $user['id'],
        ]);
        $id = (int) $this->pdo->lastInsertId();
        $response = ['extraIncome' => ['id' => $uuid, 'dbId' => $id, 'year' => (string) $year,
            'datum' => $this->displayDate($this->date($payload['datum'] ?? null)), 'od' => (string) $payload['od'],
            'clan' => $member['code'], 'clanPuno' => $member['display_name'], 'iznos' => $amount]];
        $this->audit($user['id'], 'SAVE_EXTRA_INCOME', 'EXTRA_INCOME', $id, (string) $payload['requestId'], null, $response);
        return ['response' => $response, 'queue' => [
            'entityType' => 'EXTRA_INCOME', 'entityId' => $id, 'operation' => 'UPSERT',
            'job' => $this->job('saveExtraIncome', $payload, $user, null, ['EXTRA_INCOME' => [$id]]),
        ]];
    }

    /** @param array<string,mixed> $payload @param array{id:int,displayName:string} $user @return array<string,mixed> */
    private function deleteExtraIncome(array $payload, array $user): array
    {
        $uuid = $this->requiredText($payload['id'] ?? '', 'id', 100);
        $stmt = $this->pdo->prepare('SELECT ei.*,bm.code,bm.display_name FROM extra_income ei JOIN board_members bm ON bm.id=ei.board_member_id WHERE ei.income_uuid=:uuid FOR UPDATE');
        $stmt->execute([':uuid' => $uuid]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!is_array($row) || (string) $row['income_status'] === 'VOID') {
            return ['response' => ['alreadyDeleted' => true, 'extraIncome' => ['id' => $uuid]], 'queue' => [
                'entityType' => 'EXTRA_INCOME', 'entityId' => is_array($row) ? (int) $row['id'] : null, 'operation' => 'VOID',
                'job' => $this->job('deleteExtraIncome', $payload, $user),
            ]];
        }
        $this->assertExpectedMoney($payload['expectedAmount'] ?? null, (float) $row['amount'], 'Iznos prihoda');
        $update = $this->pdo->prepare("UPDATE extra_income SET income_status='VOID',voided_by_user_id=:user,voided_at=UTC_TIMESTAMP(),void_reason='Obrisano kroz aplikaciju',row_version=row_version+1 WHERE id=:id");
        $update->execute([':user' => $user['id'], ':id' => $row['id']]);
        $response = ['alreadyDeleted' => false, 'extraIncome' => ['id' => $uuid, 'year' => (string) $row['year'],
            'datum' => $this->displayDate((string) $row['income_date']), 'od' => (string) $row['received_from'],
            'clan' => (string) $row['code'], 'iznos' => (float) $row['amount']]];
        $this->audit($user['id'], 'DELETE_EXTRA_INCOME', 'EXTRA_INCOME', (int) $row['id'], (string) $payload['requestId'], $row, $response);
        return ['response' => $response, 'queue' => [
            'entityType' => 'EXTRA_INCOME', 'entityId' => (int) $row['id'], 'operation' => 'VOID',
            'job' => $this->job('deleteExtraIncome', $payload, $user, null, ['EXTRA_INCOME' => [(int) $row['id']]]),
        ]];
    }

    /** @param array<string,mixed> $payload @param array{id:int,displayName:string} $user @return array<string,mixed> */
    private function saveReceiptPdf(array $payload, array $user): array
    {
        $year = $this->year($payload['year'] ?? null);
        $number = (int) preg_replace('/\D+/', '', (string) ($payload['broj'] ?? ''));
        if ($number <= 0) throw new ApiException('RECEIPT_NUMBER_REQUIRED', 'Nedostaje broj priznanice.', 400);
        $pdf = preg_replace('/^data:application\/pdf;base64,/i', '', (string) ($payload['pdfBase64'] ?? ''));
        $pdf = preg_replace('/\s+/', '', (string) $pdf);
        if ($pdf === '' || strlen($pdf) > 8_500_000) throw new ApiException('PDF_INVALID', 'PDF nije poslat ili je prevelik.', 400);
        $bytes = base64_decode($pdf, true);
        if ($bytes === false || strlen($bytes) < 5 || !str_starts_with($bytes, '%PDF')) {
            throw new ApiException('PDF_INVALID', 'Poslati fajl nije ispravan PDF.', 400);
        }
        $stmt = $this->pdo->prepare(
            "SELECT r.id,r.receipt_status,p.payment_status FROM receipts r JOIN payments p ON p.id=r.payment_id
             WHERE p.receipt_number=:number AND p.receipt_year=:year LIMIT 1 FOR UPDATE"
        );
        $stmt->execute([':number' => $number, ':year' => $year]);
        $receipt = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!is_array($receipt) || (string) $receipt['receipt_status'] !== 'ACTIVE' || (string) $receipt['payment_status'] !== 'ACTIVE') {
            throw new ApiException('RECEIPT_NOT_FOUND', 'Aktivna priznanica nije pronađena.', 404);
        }
        $payload['pdfBase64'] = $pdf;
        $payload['pdfSha256'] = hash('sha256', $bytes);
        $response = ['queued' => true, 'receiptId' => (int) $receipt['id'], 'broj' => str_pad((string) $number, 5, '0', STR_PAD_LEFT)];
        $this->audit($user['id'], 'SAVE_RECEIPT_PDF', 'RECEIPT', (int) $receipt['id'], (string) $payload['requestId'], null, ['pdfSha256' => $payload['pdfSha256']]);
        return ['response' => $response, 'queue' => [
            'entityType' => 'RECEIPT', 'entityId' => (int) $receipt['id'], 'operation' => 'UPSERT',
            'job' => $this->job('saveReceiptPdf', $payload, $user, null, ['RECEIPT' => [(int) $receipt['id']]]),
        ]];
    }

    /** @param array<string,mixed> $queue */
    private function insertQueue(string $action, string $requestId, array $queue): int
    {
        $stmt = $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',:type,:entity,:operation,:dedupe,:payload,'PENDING',1,0,UTC_TIMESTAMP())"
        );
        $stmt->execute([
            ':type' => (string) $queue['entityType'],
            ':entity' => $queue['entityId'],
            ':operation' => (string) $queue['operation'],
            ':dedupe' => 'WEB:' . $requestId,
            ':payload' => self::json(['action' => $action, 'job' => $queue['job']]),
        ]);
        return (int) $this->pdo->lastInsertId();
    }

    /** @param array<string,mixed> $payload @param array{id:int,displayName:string} $user @param array<string,list<int>> $ids @return array<string,mixed> */
    private function job(string $action, array $payload, array $user, ?int $receipt = null, array $ids = []): array
    {
        return [
            'version' => self::VERSION,
            'businessAction' => $action,
            'requestId' => (string) $payload['requestId'],
            'expectedReceiptNumber' => $receipt,
            'actorUserId' => $user['id'],
            'actorDisplayName' => $user['displayName'],
            'payload' => $payload,
            'dbEntityIds' => $ids,
            'createdAtUtc' => gmdate('c'),
        ];
    }

    /** @param array<string,mixed> $payload @return array<string,mixed> */
    private function normalizePayload(string $action, array $payload): array
    {
        unset($payload['auth'], $payload['action']);
        $requestId = trim((string) ($payload['requestId'] ?? ''));
        if ($requestId === '') {
            $requestId = 'web-' . $this->uuid();
        }
        if (strlen($requestId) > 100 || preg_match('/^[A-Za-z0-9._:-]+$/', $requestId) !== 1) {
            throw new ApiException('REQUEST_ID_INVALID', 'requestId nije ispravan.', 400);
        }
        $payload['requestId'] = $requestId;
        return $payload;
    }

    /** @return array<string,mixed>|null */
    private function recoverExisting(string $requestId, string $requestHash): ?array
    {
        $stmt = $this->pdo->prepare('SELECT request_hash,response_body,request_status FROM idempotency_requests WHERE request_id=:id LIMIT 1');
        $stmt->execute([':id' => $requestId]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!is_array($row)) return null;
        if (!hash_equals((string) $row['request_hash'], $requestHash)) {
            throw new ApiException('IDEMPOTENCY_HASH_MISMATCH', 'Isti requestId je već korišćen sa drugim podacima.', 409);
        }
        $response = $this->decode((string) ($row['response_body'] ?? ''));
        if ((string) $row['request_status'] === 'DONE') {
            $response['idempotentReplay'] = true;
            return $response;
        }
        $queueStmt = $this->pdo->prepare('SELECT id,queue_status FROM sync_queue WHERE dedupe_key=:key LIMIT 1');
        $queueStmt->execute([':key' => 'WEB:' . $requestId]);
        $queue = $queueStmt->fetch(PDO::FETCH_ASSOC);
        if (!is_array($queue)) {
            throw new ApiException('IDEMPOTENCY_PROCESSING', 'Isti zahtev se još obrađuje. Ne ponavljajte unos.', 409);
        }
        $syncResult = $this->sync->processQueueId((int) $queue['id']);
        if (($syncResult['ok'] ?? false) === true) {
            $action = (string) ($response['action'] ?? '');
            $response = $this->mergeBridgeResult($action, $response, $syncResult['bridgeResult'] ?? []);
            $response['sync'] = ['queueId' => (int) $queue['id'], 'status' => 'DONE', 'sheetsSynced' => true];
            $response['idempotentReplay'] = true;
            $this->finalizeIdempotency($requestId, $response);
            return $response;
        }
        $response['syncPending'] = true;
        $response['sync'] = ['queueId' => (int) $queue['id'], 'status' => 'FAILED', 'sheetsSynced' => false];
        $response['idempotentReplay'] = true;
        return $response;
    }

    private function insertIdempotency(string $requestId, string $action, int $userId, string $requestHash): void
    {
        $stmt = $this->pdo->prepare(
            "INSERT INTO idempotency_requests(request_id,action_name,user_id,request_hash,response_body,request_status,expires_at)
             VALUES(:id,:action,:user,:hash,NULL,'PROCESSING',UTC_TIMESTAMP()+INTERVAL 30 DAY)"
        );
        $stmt->execute([':id' => $requestId, ':action' => $action, ':user' => $userId, ':hash' => $requestHash]);
    }

    /** @param array<string,mixed> $response */
    private function updateIdempotencyBody(string $requestId, array $response, string $status): void
    {
        $stmt = $this->pdo->prepare('UPDATE idempotency_requests SET response_body=:body,request_status=:status WHERE request_id=:id');
        $stmt->execute([':body' => self::json($response), ':status' => $status, ':id' => $requestId]);
    }

    /** @param array<string,mixed> $response */
    private function finalizeIdempotency(string $requestId, array $response): void
    {
        $this->updateIdempotencyBody($requestId, $response, 'DONE');
    }

    /** @param array<string,mixed> $response @param array<string,mixed> $bridge @return array<string,mixed> */
    private function mergeBridgeResult(string $action, array $response, array $bridge): array
    {
        if ($bridge === []) return $response;
        foreach (['priznanica','affectedYears','receiptYear','row','updatedYears','skippedYears','warning',
                  'trosak','transfer','extraIncome','alreadyDeleted','link','fileId','receiptRow'] as $key) {
            if (array_key_exists($key, $bridge)) $response[$key] = $bridge[$key];
        }
        $response['action'] = $action;
        return $response;
    }

    /** @return array{id:int,displayName:string} */
    private function sessionUser(array $session): array
    {
        $raw = is_array($session['user'] ?? null) ? $session['user'] : [];
        $id = (int) ($raw['id'] ?? 0);
        if ($id <= 0) throw new ApiException('UNAUTHORIZED', 'Korisnička sesija nije ispravna.', 401);
        return ['id' => $id, 'displayName' => trim((string) (($raw['displayName'] ?? '') ?: ($raw['display_name'] ?? '') ?: ($raw['username'] ?? 'Korisnik')))];
    }

    /** @return array<string,mixed> */
    private function householdYearBySheet(int $year, int $row, bool $lock): array
    {
        $sql = "SELECT hy.*,h.sogr_uuid,m.display_name AS mahalla_name,srl.sheet_row
                FROM sheet_row_links srl
                JOIN google_sheet_tabs gst ON gst.id=srl.sheet_tab_id AND gst.tab_role='ANNUAL' AND gst.is_active=1
                JOIN google_sheet_documents gsd ON gsd.id=gst.document_id AND gsd.is_primary=1 AND gsd.is_active=1
                JOIN household_years hy ON hy.id=srl.entity_id AND srl.entity_type='HOUSEHOLD_YEAR'
                JOIN households h ON h.id=hy.household_id
                JOIN mahallas m ON m.id=hy.mahalla_id
                WHERE gsd.year=:year AND srl.sheet_row=:row AND hy.record_status='ACTIVE' LIMIT 1" . ($lock ? ' FOR UPDATE' : '');
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':year' => $year, ':row' => $row]);
        $data = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!is_array($data)) throw new ApiException('HOUSEHOLD_ROW_NOT_FOUND', 'Izabrani red nije pronađen u MySQL bazi. Osvežite spisak.', 404);
        return $data;
    }

    /** @return array<string,mixed> */
    private function householdYearById(int $id): array
    {
        $stmt = $this->pdo->prepare('SELECT hy.*,m.display_name AS mahalla_name FROM household_years hy JOIN mahallas m ON m.id=hy.mahalla_id WHERE hy.id=:id');
        $stmt->execute([':id' => $id]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!is_array($row)) throw new ApiException('HOUSEHOLD_YEAR_NOT_FOUND', 'Godišnji zapis nije pronađen.', 404);
        return $row;
    }

    private function assertNoActivePayment(int $householdYearId): void
    {
        $stmt = $this->pdo->prepare('SELECT COUNT(*) FROM active_payment_links WHERE household_year_id=:id');
        $stmt->execute([':id' => $householdYearId]);
        if ((int) $stmt->fetchColumn() !== 0) throw new ApiException('PAYMENT_ALREADY_EXISTS', 'Uplata za ovu godinu je već evidentirana.', 409);
    }

    /** @param array<string,mixed> $row @param array<string,mixed> $payload */
    private function assertName(array $row, array $payload): void
    {
        if (isset($payload['ime']) && strcasecmp(trim((string) $payload['ime']), trim((string) $row['first_name'])) !== 0) {
            throw new ApiException('HOUSEHOLD_CHANGED', 'Ime u bazi je promenjeno. Osvežite spisak.', 409);
        }
        if (isset($payload['prezime']) && strcasecmp(trim((string) $payload['prezime']), trim((string) $row['last_name'])) !== 0) {
            throw new ApiException('HOUSEHOLD_CHANGED', 'Prezime u bazi je promenjeno. Osvežite spisak.', 409);
        }
    }

    /** @param array<string,mixed> $row @param array<string,mixed> $original */
    private function assertOriginal(array $row, array $original): void
    {
        $checks = [
            'ime' => ['first_name', 'tekst'], 'prezime' => ['last_name', 'tekst'],
            'mahalo' => ['mahalla_name', 'tekst'], 'clanova' => ['members_count', 'broj'],
        ];
        foreach ($checks as $key => [$column, $kind]) {
            if (!array_key_exists($key, $original)) continue;
            $ok = $kind === 'broj'
                ? (int) $original[$key] === (int) $row[$column]
                : strcasecmp(trim((string) $original[$key]), trim((string) $row[$column])) === 0;
            if (!$ok) throw new ApiException('MEMBER_CHANGED', 'Podaci člana su u međuvremenu promenjeni. Osvežite spisak.', 409);
        }
    }

    /** @return array{id:int,code:string,display_name:string} */
    private function boardMember(string $value): array
    {
        $value = trim($value);
        if ($value === '') throw new ApiException('BOARD_MEMBER_REQUIRED', 'Član odbora nije izabran.', 400);
        $stmt = $this->pdo->prepare(
            'SELECT id,code,display_name FROM board_members WHERE is_active=1 AND handles_money=1
             AND (LOWER(code)=LOWER(:code) OR LOWER(display_name)=LOWER(:display) OR LOWER(first_name)=LOWER(:first))
             ORDER BY id LIMIT 1'
        );
        $stmt->execute([':code' => $value, ':display' => $value, ':first' => $value]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!is_array($row)) throw new ApiException('BOARD_MEMBER_NOT_FOUND', 'Član odbora nije pronađen.', 400);
        return ['id' => (int) $row['id'], 'code' => (string) $row['code'], 'display_name' => (string) $row['display_name']];
    }

    /** @return array{id:int,display_name:string} */
    private function mahalla(string $value): array
    {
        $value = trim($value);
        $stmt = $this->pdo->prepare('SELECT id,display_name FROM mahallas WHERE is_active=1 AND (LOWER(code)=LOWER(:code) OR LOWER(display_name)=LOWER(:display)) LIMIT 1');
        $stmt->execute([':code' => $value, ':display' => $value]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!is_array($row)) throw new ApiException('MAHALLA_NOT_FOUND', 'Mahala nije pronađena.', 400);
        return ['id' => (int) $row['id'], 'display_name' => (string) $row['display_name']];
    }

    private function nextReceiptNumber(): int
    {
        $row = $this->pdo->query("SELECT current_value FROM number_sequences WHERE sequence_name='receipt' FOR UPDATE")->fetch(PDO::FETCH_ASSOC);
        if (!is_array($row)) throw new ApiException('RECEIPT_SEQUENCE_MISSING', 'Brojač priznanica nije pronađen.', 500);
        $current = (int) $row['current_value'];
        $max = (int) $this->pdo->query('SELECT COALESCE(MAX(receipt_number),0) FROM payments')->fetchColumn();
        if ($current !== $max) throw new ApiException('RECEIPT_SEQUENCE_MISMATCH', 'Brojač priznanica nije usklađen sa bazom.', 409, ['sequence' => $current, 'maxReceipt' => $max]);
        $next = $current + 1;
        $stmt = $this->pdo->prepare("UPDATE number_sequences SET current_value=:next,padding_length=GREATEST(padding_length,5) WHERE sequence_name='receipt' AND current_value=:current");
        $stmt->execute([':next' => $next, ':current' => $current]);
        if ($stmt->rowCount() !== 1) throw new ApiException('RECEIPT_SEQUENCE_RACE', 'Drugi zahtev je upravo zauzeo broj priznanice.', 409);
        return $next;
    }

    /** @param array<string,mixed> $hy @param array{id:int,code:string,display_name:string} $collector @param array<int|string,int> $sourceRows @return array<string,mixed> */
    private function insertReceipt(int $paymentId, int $receiptYear, array $hy, array $collector, array $sourceRows, string $yearsText,
                                   string $place, string $purpose, float $membership, float $donation, string $date, int $number): array
    {
        $link = $this->receiptLink($hy, $collector, $sourceRows, $place, $purpose, $membership, $donation, $date, $number, $receiptYear);
        $stmt = $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,:year,NULL,:full,:first,:last,:mahalla,:members,:place,:collector,:years,:source,:html,:link,'ACTIVE')"
        );
        $full = trim((string) $hy['first_name'] . ' ' . (string) $hy['last_name']);
        $html = [
            'broj' => str_pad((string) $number, 5, '0', STR_PAD_LEFT), 'datum' => $this->displayDate($date),
            'ime' => $hy['first_name'], 'prezime' => $hy['last_name'], 'mahalo' => $hy['mahalla_name'],
            'clanova' => (int) $hy['members_count'], 'iznos' => $membership, 'donacija' => $donation,
            'mesto' => $place, 'primio' => $collector['display_name'], 'svrha' => $purpose,
        ];
        $stmt->execute([
            ':payment' => $paymentId, ':year' => $receiptYear, ':full' => strtoupper($full),
            ':first' => $hy['first_name'], ':last' => $hy['last_name'], ':mahalla' => $hy['mahalla_name'],
            ':members' => $hy['members_count'], ':place' => $place, ':collector' => $collector['display_name'],
            ':years' => $yearsText, ':source' => self::json($sourceRows), ':html' => self::json($html), ':link' => $link,
        ]);
        $receiptId = (int) $this->pdo->lastInsertId();
        return [
            'id' => $receiptId, 'paymentId' => $paymentId, 'broj' => str_pad((string) $number, 5, '0', STR_PAD_LEFT),
            'datum' => $this->displayDate($date), 'ime' => (string) $hy['first_name'], 'prezime' => (string) $hy['last_name'],
            'imePrezime' => strtoupper($full), 'mahalo' => (string) $hy['mahalla_name'], 'clanova' => (int) $hy['members_count'],
            'iznos' => round($membership, 2), 'donacija' => round($donation, 2), 'ukupno' => round($membership + $donation, 2),
            'svrha' => $purpose, 'godine' => $yearsText, 'mesto' => $place, 'primio' => $collector['display_name'],
            'link' => $link, 'year' => (string) $receiptYear, 'receiptYear' => (string) $receiptYear,
            'sourceRow' => count($sourceRows) === 1 ? (int) reset($sourceRows) : 0,
            'sourceRows' => $sourceRows, 'receiptRow' => null, 'pdfReady' => false,
        ];
    }

    /** @param array<string,mixed> $hy @param array<string,mixed> $collector @param array<int|string,int> $sourceRows */
    private function receiptLink(array $hy, array $collector, array $sourceRows, string $place, string $purpose,
                                 float $membership, float $donation, string $date, int $number, int $receiptYear): string
    {
        $url = (string) $this->pdo->query("SELECT setting_value FROM app_settings WHERE setting_key='receipt_html_url'")->fetchColumn();
        if ($url === '') $url = 'https://skolarbbos.net/wp-content/uploads/2026/04/priznanica.html';
        $years = array_keys($sourceRows);
        $query = [
            'ime' => $hy['first_name'], 'prezime' => $hy['last_name'], 'mahalo' => $hy['mahalla_name'],
            'clanova' => $hy['members_count'], 'placeno' => $membership, 'donacija' => $donation,
            'primio' => $collector['display_name'], 'godina' => $receiptYear,
            'broj' => str_pad((string) $number, 5, '0', STR_PAD_LEFT), 'mesto' => $place,
            'datum' => $this->displayDate($date), 'svrha' => $purpose,
        ];
        if (count($years) > 1) { $query['godine'] = implode('|', $years); $query['vrsta'] = 'vise_godina'; }
        return rtrim($url, '?') . '?' . http_build_query($query, '', '&', PHP_QUERY_RFC3986);
    }

    /** @param array<string,mixed> $row @return array<string,mixed> */
    private function legacyHouseholdRow(array $row, int $sheetRow): array
    {
        return ['row' => $sheetRow, 'rb' => (string) ($row['rb'] ?? ''), 'ime' => (string) $row['first_name'],
            'prezime' => (string) $row['last_name'], 'mahalo' => (string) $row['mahalla_name'],
            'clanova' => (int) $row['members_count'], 'dug' => (float) $row['due_amount']];
    }

    /** @param array<string,mixed>|null $old @param array<string,mixed> $new */
    private function audit(int $userId, string $action, string $entityType, ?int $entityId, string $requestId, ?array $old, array $new): void
    {
        $stmt = $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,:action,:type,:entity,'WEB',:request,:old,:new,:ip,:ua)"
        );
        $stmt->execute([
            ':user' => $userId, ':action' => $action, ':type' => $entityType, ':entity' => $entityId,
            ':request' => $requestId, ':old' => $old === null ? null : self::json($old), ':new' => self::json($new),
            ':ip' => $this->safeSubstr((string) ($_SERVER['REMOTE_ADDR'] ?? ''), 0, 45),
            ':ua' => $this->safeSubstr((string) ($_SERVER['HTTP_USER_AGENT'] ?? ''), 0, 500),
        ]);
    }

    /** @return array<int,true> */
    private function availableYears(): array
    {
        $years = $this->pdo->query('SELECT year FROM google_sheet_documents WHERE is_primary=1 AND is_active=1')->fetchAll(PDO::FETCH_COLUMN);
        $out = [];
        foreach ($years as $year) $out[(int) $year] = true;
        return $out;
    }

    private function year(mixed $value): int
    {
        $year = filter_var($value, FILTER_VALIDATE_INT);
        if ($year === false || !isset($this->availableYears()[(int) $year])) throw new ApiException('YEAR_INVALID', 'Godina nije ispravna.', 400);
        return (int) $year;
    }

    private function positiveInt(mixed $value, string $field): int
    {
        $n = filter_var($value, FILTER_VALIDATE_INT);
        if ($n === false || $n < 1) throw new ApiException('VALIDATION_ERROR', 'Polje ' . $field . ' nije ispravno.', 400);
        return (int) $n;
    }

    private function nonNegativeInt(mixed $value, string $field, int $max): int
    {
        $n = filter_var($value, FILTER_VALIDATE_INT);
        if ($n === false || $n < 0 || $n > $max) throw new ApiException('VALIDATION_ERROR', 'Polje ' . $field . ' nije ispravno.', 400);
        return (int) $n;
    }

    private function money(mixed $value, string $field, bool $required): float
    {
        $text = str_replace(['€',' '], '', trim((string) $value));
        if (str_contains($text, ',') && !str_contains($text, '.')) $text = str_replace(',', '.', $text);
        if ($text === '' && !$required) return 0.0;
        if (!is_numeric($text)) throw new ApiException('VALIDATION_ERROR', 'Polje ' . $field . ' nije ispravan iznos.', 400);
        $number = round((float) $text, 2);
        if ($number < 0 || $number > 100000) throw new ApiException('VALIDATION_ERROR', 'Polje ' . $field . ' je van dozvoljenog opsega.', 400);
        return $number;
    }

    private function date(mixed $value): string
    {
        $text = trim((string) $value);
        if ($text === '') return gmdate('Y-m-d');
        if (preg_match('/^(\d{4})-(\d{2})-(\d{2})$/', $text, $m) === 1 && checkdate((int) $m[2], (int) $m[3], (int) $m[1])) return $text;
        if (preg_match('/^(\d{1,2})\.(\d{1,2})\.(\d{4})\.?$/', $text, $m) === 1 && checkdate((int) $m[2], (int) $m[1], (int) $m[3])) return sprintf('%04d-%02d-%02d', $m[3], $m[2], $m[1]);
        throw new ApiException('DATE_INVALID', 'Datum nije ispravan.', 400);
    }

    private function displayDate(string $date): string
    {
        $ts = strtotime($date . ' UTC');
        return $ts === false ? $date : gmdate('d.m.Y.', $ts);
    }

    private function requiredText(mixed $value, string $field, int $max): string
    {
        $text = trim((string) $value);
        if ($text === '') throw new ApiException('VALIDATION_ERROR', 'Polje ' . $field . ' je obavezno.', 400);
        return $this->safeSubstr($text, 0, $max);
    }

    private function text(mixed $value, int $max): ?string
    {
        $text = trim((string) $value);
        return $text === '' ? null : $this->safeSubstr($text, 0, $max);
    }

    /**
     * UTF-8 bezbedno skraćivanje koje radi i kada Hostinger PHP nema mbstring.
     */
    private function safeSubstr(string $text, int $start, int $length): string
    {
        if (function_exists('mb_substr')) {
            return (string) mb_substr($text, $start, $length, 'UTF-8');
        }
        if (function_exists('iconv_substr')) {
            $value = iconv_substr($text, $start, $length, 'UTF-8');
            if ($value !== false) {
                return (string) $value;
            }
        }
        $characterCount = preg_match_all('/./us', $text, $characters);
        if ($start === 0 && $characterCount !== false) {
            return implode('', array_slice($characters[0], 0, $length));
        }
        return substr($text, $start, $length);
    }

    private function decimal(float $value): string { return number_format(round($value, 2), 2, '.', ''); }
    private function annualPurpose(int $year): string { return $year <= 2024 ? 'Članarina za javnu rasvetu - ' . $year : 'Godišnja članarina za ' . $year . '. godinu'; }
    /** @param list<int> $years */
    private function multiPurpose(array $years): string { sort($years); return 'Godišnja članarina za ' . implode(', ', $years) . '. g.'; }
    private function uuid(): string { $d=random_bytes(16); $d[6]=chr((ord($d[6])&0x0f)|0x40); $d[8]=chr((ord($d[8])&0x3f)|0x80); return vsprintf('%s%s-%s-%s-%s-%s%s%s',str_split(bin2hex($d),4)); }
    private function requestHash(string $action, array $payload): string { return hash('sha256', self::json(['action'=>$action,'payload'=>$this->sortRecursive($payload)])); }
    private function sortRecursive(mixed $value): mixed { if(!is_array($value))return $value; if(!array_is_list($value))ksort($value); foreach($value as $k=>$v)$value[$k]=$this->sortRecursive($v); return $value; }
    private function decode(string $json): array { if(trim($json)==='')return []; try{$d=json_decode($json,true,512,JSON_THROW_ON_ERROR);return is_array($d)?$d:[];}catch(Throwable){return [];} }
    private function sourceRow(string $reference): int { return preg_match('/![A-Z]+(\d+)/i',$reference,$m)===1?(int)$m[1]:0; }
    private function assertExpectedMoney(mixed $expected, float $actual, string $label): void { if($expected===null||trim((string)$expected)==='')return; $e=$this->money($expected,'expectedAmount',false); if(abs($e-$actual)>0.01)throw new ApiException('ENTITY_CHANGED',$label.' je u međuvremenu promenjen.',409); }
    private static function json(mixed $value): string { return json_encode($value,JSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES|JSON_THROW_ON_ERROR); }
}