<?php

declare(strict_types=1);

if (!defined('DENTAL_ROOT')) {
    exit;
}

function revenue_schema_ready(): bool
{
    return schema_table_exists(table('cashier_sessions'))
        && schema_table_exists(table('cashier_windows'))
        && schema_table_exists(table('cashier_checkouts'))
        && schema_table_exists(table('discount_authorizations'))
        && schema_table_exists(table('zero_charge_authorizations'))
        && schema_table_exists(table('payment_adjustments'))
        && schema_table_exists(table('insurance_claim_history'))
        && schema_column_exists(table('invoice_items'), 'source_type')
        && schema_column_exists(table('invoices'), 'discount_authorization_id')
        && schema_column_exists(table('invoices'), 'legacy_charge_reviewed_at')
        && schema_column_exists(table('payments'), 'cashier_session_id')
        && schema_column_exists(table('payments'), 'cashier_window_id')
        && schema_column_exists(table('cashier_sessions'), 'window_id')
        && schema_column_exists(table('cashier_sessions'), 'locked_patient_id')
        && schema_column_exists(table('cashier_sessions'), 'locked_queue_entry_id')
        && schema_column_exists(table('cashier_sessions'), 'transaction_locked_at')
        && schema_column_exists(table('insurance_claims'), 'payment_id')
        && schema_column_exists(table('insurance_claims'), 'authorization_no')
        && schema_column_exists(table('insurance_claims'), 'created_by')
        && schema_column_exists(table('insurance_claims'), 'updated_at');
}



function revenue_payment_item_allocation_required_columns(): array
{
    return [
        'id','payment_id','invoice_id','invoice_item_id','sequence_no',
        'item_code_snapshot','description_snapshot','quantity_snapshot',
        'unit_price_snapshot','line_net_snapshot','payable_snapshot',
        'previous_paid_snapshot','amount_applied','remaining_after_snapshot','created_at',
    ];
}

function revenue_payment_item_allocation_schema_issues(): array
{
    $tableName = table('payment_item_allocations');
    try {
        if (!schema_table_exists($tableName)) return [$tableName . ' table is missing'];
        $issues = [];
        foreach (revenue_payment_item_allocation_required_columns() as $column) {
            if (!schema_column_exists($tableName, $column)) $issues[] = $tableName . '.' . $column;
        }
        return $issues;
    } catch (Throwable $exception) {
        return ['receipt-allocation schema check failed: ' . $exception->getMessage()];
    }
}

function revenue_payment_item_allocation_schema_ready(): bool
{
    return revenue_payment_item_allocation_schema_issues() === [];
}

/**
 * Return the immutable payable basis for each invoice item. Invoice-level
 * discounts or taxes are distributed proportionally so the item payables add
 * exactly to the final invoice amount, including the final cent.
 *
 * Upgraded databases are not assumed to have every historical invoice-item
 * column. Missing optional columns degrade to safe expressions instead of
 * bringing down the Cashier Register.
 */
function revenue_invoice_item_payment_basis(PDO $pdo, int $invoiceId): array
{
    $invoiceStmt = $pdo->prepare('SELECT id,description,amount FROM `' . table('invoices') . '` WHERE id=? LIMIT 1');
    $invoiceStmt->execute([$invoiceId]);
    $invoice = $invoiceStmt->fetch();
    if (!$invoice) throw new RuntimeException('Invoice not found while preparing the itemized payment receipt.');

    $items = [];
    $itemTable = table('invoice_items');
    if (schema_table_exists($itemTable)) {
        $select = [
            schema_column_exists($itemTable,'id') ? 'id' : 'NULL AS id',
            schema_column_exists($itemTable,'item_code') ? 'item_code' : 'NULL AS item_code',
            schema_column_exists($itemTable,'description') ? 'description' : '"Invoice item" AS description',
            schema_column_exists($itemTable,'quantity') ? 'quantity' : '1 AS quantity',
            schema_column_exists($itemTable,'unit_price') ? 'unit_price' : '0 AS unit_price',
            schema_column_exists($itemTable,'gross_amount') ? 'gross_amount' : '0 AS gross_amount',
            schema_column_exists($itemTable,'discount_amount') ? 'discount_amount' : '0 AS discount_amount',
            schema_column_exists($itemTable,'net_amount') ? 'net_amount' : '0 AS net_amount',
        ];
        try {
            $itemStmt = $pdo->prepare(
                'SELECT ' . implode(',', $select) . ' FROM `' . $itemTable . '` WHERE invoice_id=? ORDER BY id'
            );
            $itemStmt->execute([$invoiceId]);
            foreach ($itemStmt->fetchAll() as $row) {
                $lineNet = round((float)($row['net_amount'] ?? 0), 2);
                if ($lineNet <= 0.001) {
                    $lineNet = max(0, round((float)($row['gross_amount'] ?? 0) - (float)($row['discount_amount'] ?? 0), 2));
                }
                if ($lineNet <= 0.001) {
                    $lineNet = max(0, round((float)($row['unit_price'] ?? 0) * max(1, (float)($row['quantity'] ?? 1)), 2));
                }
                $items[] = [
                    'invoice_item_id'=>(int)($row['id'] ?? 0) ?: null,
                    'item_code'=>(string)($row['item_code'] ?? ''),
                    'description'=>trim((string)($row['description'] ?? '')) ?: 'Invoice item',
                    'quantity'=>round((float)($row['quantity'] ?? 1), 2),
                    'unit_price'=>round((float)($row['unit_price'] ?? 0), 2),
                    'line_net'=>$lineNet,
                ];
            }
        } catch (Throwable $exception) {
            error_log('Payment receipt invoice-item compatibility fallback: ' . $exception->getMessage());
            $items = [];
        }
    }

    $invoiceTotalCents = max(0, (int)round((float)$invoice['amount'] * 100));
    if (!$items) {
        return [[
            'invoice_item_id'=>null,
            'item_code'=>'',
            'description'=>trim((string)($invoice['description'] ?? '')) ?: 'Patient invoice',
            'quantity'=>1.0,
            'unit_price'=>$invoiceTotalCents / 100,
            'line_net'=>$invoiceTotalCents / 100,
            'payable_amount'=>$invoiceTotalCents / 100,
        ]];
    }

    $baseCents = array_map(static fn(array $item): int => max(0, (int)round((float)$item['line_net'] * 100)), $items);
    $baseTotal = array_sum($baseCents);
    if ($baseTotal <= 0) {
        $baseCents = array_fill(0, count($items), 1);
        $baseTotal = count($items);
    }

    $floors = [];
    $fractions = [];
    $allocated = 0;
    foreach ($baseCents as $index => $base) {
        $raw = $invoiceTotalCents * $base / $baseTotal;
        $floor = (int)floor($raw);
        $floors[$index] = $floor;
        $fractions[$index] = $raw - $floor;
        $allocated += $floor;
    }
    $remaining = max(0, $invoiceTotalCents - $allocated);
    arsort($fractions, SORT_NUMERIC);
    foreach (array_keys($fractions) as $index) {
        if ($remaining <= 0) break;
        $floors[$index]++;
        $remaining--;
    }

    foreach ($items as $index => &$item) {
        $item['payable_amount'] = ($floors[$index] ?? 0) / 100;
    }
    unset($item);
    return $items;
}

function revenue_payment_receipt_fallback(PDO $pdo, int $paymentId): array
{
    $stmt = $pdo->prepare(
        'SELECT p.id,p.invoice_id,p.amount,i.description FROM `' . table('payments') . '` p '
        . 'JOIN `' . table('invoices') . '` i ON i.id=p.invoice_id WHERE p.id=? LIMIT 1'
    );
    $stmt->execute([$paymentId]);
    $row = $stmt->fetch();
    if (!$row) return [];
    $amount = max(0, round((float)$row['amount'], 2));
    return [[
        'invoice_item_id'=>null,
        'sequence_no'=>1,
        'item_code'=>'',
        'description'=>trim((string)($row['description'] ?? '')) ?: 'Patient invoice payment',
        'quantity'=>1.0,
        'unit_price'=>$amount,
        'line_net'=>$amount,
        'payable_amount'=>$amount,
        'previous_paid'=>0.0,
        'amount_applied'=>$amount,
        'remaining_after'=>0.0,
    ]];
}

/**
 * Derive the exact items covered by one payment using chronological FIFO
 * allocation. This is also the backward-compatible path for payments recorded
 * before the allocation table existed.
 */
function revenue_derive_payment_item_allocations(PDO $pdo, int $paymentId): array
{
    $paymentTable = table('payments');
    $hasCreatedAt = schema_column_exists($paymentTable, 'created_at');
    $createdSelect = $hasCreatedAt ? 'created_at' : 'NULL AS created_at';
    $paymentStmt = $pdo->prepare('SELECT id,invoice_id,amount,' . $createdSelect . ' FROM `' . $paymentTable . '` WHERE id=? LIMIT 1');
    $paymentStmt->execute([$paymentId]);
    $target = $paymentStmt->fetch();
    if (!$target) throw new RuntimeException('Payment record not found while preparing the itemized receipt.');

    $basis = revenue_invoice_item_payment_basis($pdo, (int)$target['invoice_id']);
    if ($hasCreatedAt && !empty($target['created_at'])) {
        $paymentsStmt = $pdo->prepare(
            'SELECT id,amount,created_at FROM `' . $paymentTable . '` '
            . 'WHERE invoice_id=? AND (created_at<? OR (created_at=? AND id<=?)) ORDER BY created_at,id'
        );
        $paymentsStmt->execute([(int)$target['invoice_id'],(string)$target['created_at'],(string)$target['created_at'],$paymentId]);
    } else {
        $paymentsStmt = $pdo->prepare(
            'SELECT id,amount,NULL created_at FROM `' . $paymentTable . '` WHERE invoice_id=? AND id<=? ORDER BY id'
        );
        $paymentsStmt->execute([(int)$target['invoice_id'],$paymentId]);
    }
    $paidCents = array_fill(0, count($basis), 0);
    $targetRows = [];

    foreach ($paymentsStmt->fetchAll() as $payment) {
        $remaining = max(0, (int)round((float)$payment['amount'] * 100));
        $isTarget = (int)$payment['id'] === $paymentId;
        foreach ($basis as $index => $item) {
            if ($remaining <= 0) break;
            $payable = max(0, (int)round((float)$item['payable_amount'] * 100));
            $previous = $paidCents[$index];
            $outstanding = max(0, $payable - $previous);
            if ($outstanding <= 0) continue;
            $applied = min($outstanding, $remaining);
            $paidCents[$index] += $applied;
            $remaining -= $applied;
            if ($isTarget && $applied > 0) {
                $targetRows[] = array_merge($item, [
                    'sequence_no'=>count($targetRows) + 1,
                    'previous_paid'=>$previous / 100,
                    'amount_applied'=>$applied / 100,
                    'remaining_after'=>max(0, $payable - $paidCents[$index]) / 100,
                ]);
            }
        }
        if ($isTarget && $remaining > 0) {
            $targetRows[] = [
                'invoice_item_id'=>null,
                'item_code'=>'',
                'description'=>'Unallocated invoice balance',
                'quantity'=>1.0,
                'unit_price'=>$remaining / 100,
                'line_net'=>$remaining / 100,
                'payable_amount'=>$remaining / 100,
                'sequence_no'=>count($targetRows) + 1,
                'previous_paid'=>0.0,
                'amount_applied'=>$remaining / 100,
                'remaining_after'=>0.0,
            ];
        }
    }
    return $targetRows ?: revenue_payment_receipt_fallback($pdo, $paymentId);
}

/**
 * Store the immutable allocation snapshot when possible. Receipt itemization is
 * important, but it must never roll back a valid patient payment or take down
 * billing.php. When an upgraded schema is incomplete, the printable receipt
 * safely derives the allocation until System Setup repairs the snapshot table.
 */
function revenue_store_payment_item_allocations(PDO $pdo, int $paymentId, int $invoiceId): bool
{
    if (!revenue_payment_item_allocation_schema_ready()) {
        error_log('Payment allocation snapshot skipped: ' . implode(', ', revenue_payment_item_allocation_schema_issues()));
        return false;
    }
    try {
        $exists = $pdo->prepare('SELECT COUNT(*) FROM `' . table('payment_item_allocations') . '` WHERE payment_id=?');
        $exists->execute([$paymentId]);
        if ((int)$exists->fetchColumn() > 0) return true;

        $rows = revenue_derive_payment_item_allocations($pdo, $paymentId);
        if (!$rows) return false;
        $insert = $pdo->prepare(
            'INSERT INTO `' . table('payment_item_allocations') . '` '
            . '(payment_id,invoice_id,invoice_item_id,sequence_no,item_code_snapshot,description_snapshot,quantity_snapshot,unit_price_snapshot,line_net_snapshot,payable_snapshot,previous_paid_snapshot,amount_applied,remaining_after_snapshot,created_at) '
            . 'VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,NOW())'
        );
        foreach ($rows as $row) {
            $insert->execute([
                $paymentId,$invoiceId,!empty($row['invoice_item_id'])?(int)$row['invoice_item_id']:null,(int)$row['sequence_no'],
                trim((string)($row['item_code'] ?? '')) ?: null,(string)($row['description'] ?? 'Invoice payment'),(float)($row['quantity'] ?? 1),(float)($row['unit_price'] ?? 0),
                (float)($row['line_net'] ?? 0),(float)($row['payable_amount'] ?? 0),(float)($row['previous_paid'] ?? 0),(float)($row['amount_applied'] ?? 0),(float)($row['remaining_after'] ?? 0),
            ]);
        }
        return true;
    } catch (Throwable $exception) {
        error_log('Payment allocation snapshot failed for payment #' . $paymentId . ': ' . $exception->getMessage());
        try {
            $cleanup = $pdo->prepare('DELETE FROM `' . table('payment_item_allocations') . '` WHERE payment_id=?');
            $cleanup->execute([$paymentId]);
        } catch (Throwable) {
            // The parent payment transaction remains authoritative.
        }
        return false;
    }
}

function revenue_receipt_description_is_generic(string $description): bool
{
    $value = mb_strtolower(trim($description));
    if ($value === '') return true;
    $generic = [
        'imported outstanding balance',
        'outstanding balance',
        'opening balance',
        'previous balance',
        'patient invoice',
        'patient invoice payment',
        'invoice item',
        'unallocated invoice balance',
        'legacy balance',
    ];
    foreach ($generic as $label) {
        if ($value === $label || str_starts_with($value, $label . ' ')) return true;
    }
    return false;
}

function revenue_payment_receipt_rows_are_generic(array $rows): bool
{
    if (!$rows) return true;
    foreach ($rows as $row) {
        if (!revenue_receipt_description_is_generic((string)($row['description'] ?? ''))) return false;
    }
    return true;
}

function revenue_payment_receipt_items(PDO $pdo, int $paymentId): array
{
    if (revenue_payment_item_allocation_schema_ready()) {
        try {
            $stmt = $pdo->prepare(
                'SELECT invoice_item_id,sequence_no,item_code_snapshot item_code,description_snapshot description,'
                . 'quantity_snapshot quantity,unit_price_snapshot unit_price,line_net_snapshot line_net,payable_snapshot payable_amount,'
                . 'previous_paid_snapshot previous_paid,amount_applied,remaining_after_snapshot remaining_after '
                . 'FROM `' . table('payment_item_allocations') . '` WHERE payment_id=? ORDER BY sequence_no,id'
            );
            $stmt->execute([$paymentId]);
            $rows = $stmt->fetchAll();
            if ($rows && !revenue_payment_receipt_rows_are_generic($rows)) return $rows;
            if ($rows) {
                // A legacy snapshot may contain only "Imported outstanding balance".
                // If an Administrator later reconstructs the real invoice lines, prefer
                // the reviewed breakdown instead of continuing to print the placeholder.
                try {
                    $paymentStmt = $pdo->prepare('SELECT invoice_id FROM `' . table('payments') . '` WHERE id=? LIMIT 1');
                    $paymentStmt->execute([$paymentId]);
                    $invoiceId = (int)$paymentStmt->fetchColumn();
                    if ($invoiceId > 0) {
                        $basis = revenue_invoice_item_payment_basis($pdo, $invoiceId);
                        if ($basis && !revenue_payment_receipt_rows_are_generic($basis)) {
                            return revenue_derive_payment_item_allocations($pdo, $paymentId);
                        }
                    }
                } catch (Throwable $exception) {
                    error_log('Reviewed receipt breakdown lookup failed for payment #' . $paymentId . ': ' . $exception->getMessage());
                }
                return $rows;
            }
        } catch (Throwable $exception) {
            error_log('Stored payment allocation read failed for payment #' . $paymentId . ': ' . $exception->getMessage());
        }
    }
    try {
        return revenue_derive_payment_item_allocations($pdo, $paymentId);
    } catch (Throwable $exception) {
        error_log('Derived payment allocation failed for payment #' . $paymentId . ': ' . $exception->getMessage());
        return revenue_payment_receipt_fallback($pdo, $paymentId);
    }
}

/**
 * Rebuild immutable payment-to-item snapshots after an authorized historical
 * invoice reconciliation. Payment amounts and dates are not changed.
 */
function revenue_rebuild_payment_allocations_for_invoice(PDO $pdo, int $invoiceId): int
{
    if ($invoiceId <= 0 || !revenue_payment_item_allocation_schema_ready()) return 0;
    $paymentTable = table('payments');
    $order = schema_column_exists($paymentTable, 'created_at') ? 'created_at,id' : 'id';
    $stmt = $pdo->prepare('SELECT id FROM `' . $paymentTable . '` WHERE invoice_id=? ORDER BY ' . $order);
    $stmt->execute([$invoiceId]);
    $paymentIds = array_map('intval', $stmt->fetchAll(PDO::FETCH_COLUMN));
    if (!$paymentIds) return 0;

    $delete = $pdo->prepare('DELETE FROM `' . table('payment_item_allocations') . '` WHERE invoice_id=?');
    $delete->execute([$invoiceId]);
    $rebuilt = 0;
    foreach ($paymentIds as $paymentId) {
        if (revenue_store_payment_item_allocations($pdo, $paymentId, $invoiceId)) $rebuilt++;
    }
    return $rebuilt;
}

function revenue_lock_name(string $scope, int|string ...$parts): string
{
    $scope = preg_replace('/[^a-z0-9_-]/i', '_', $scope) ?: 'revenue';
    $fingerprint = hash('sha256', json_encode([$scope,$parts], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES));
    return substr('dental:' . $scope . ':' . $fingerprint, 0, 64);
}

function revenue_acquire_lock(PDO $pdo, string $name, int $timeoutSeconds = 5): void
{
    $stmt = $pdo->prepare('SELECT GET_LOCK(?,?)');
    $stmt->execute([$name, max(0, min(30, $timeoutSeconds))]);
    if ((int)$stmt->fetchColumn() !== 1) {
        throw new RuntimeException('Another transaction is already updating this record. Reload and try again.');
    }
}

function revenue_release_lock(PDO $pdo, string $name): void
{
    try {
        $stmt = $pdo->prepare('SELECT RELEASE_LOCK(?)');
        $stmt->execute([$name]);
    } catch (Throwable $ignored) {
        // Connection shutdown will release advisory locks automatically.
    }
}

function revenue_invoice_charge_hash(PDO $pdo, int $invoiceId): string
{
    $stmt = $pdo->prepare(
        'SELECT source_hash,discount_amount,net_amount FROM `' . table('invoice_items') . '` '
        . 'WHERE invoice_id=? ORDER BY id'
    );
    $stmt->execute([$invoiceId]);
    $payload = [];
    foreach ($stmt->fetchAll() as $row) {
        $sourceHash = trim((string)($row['source_hash'] ?? ''));
        if ($sourceHash === '') {
            throw new RuntimeException('This invoice contains a legacy or unauthorised line without a source fingerprint. An Administrator must recreate and approve the invoice before another payment can be accepted.');
        }
        $payload[] = [$sourceHash, round((float)$row['discount_amount'], 2), round((float)$row['net_amount'], 2)];
    }
    if (!$payload) {
        throw new RuntimeException('This invoice has no authorised line items.');
    }
    return hash('sha256', json_encode($payload, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES));
}

function revenue_assert_invoice_integrity(PDO $pdo, array $invoice): void
{
    $expected = trim((string)($invoice['charge_source_hash'] ?? ''));
    if ($expected === '') {
        throw new RuntimeException('This locked invoice predates charge-source verification. An Administrator must use the legacy-invoice review before another payment can be accepted.');
    }
    $actual = revenue_invoice_charge_hash($pdo, (int)$invoice['id']);
    if (!hash_equals($expected, $actual)) {
        throw new RuntimeException('Invoice line integrity verification failed. Do not accept payment; ask an Administrator to investigate the altered charge record.');
    }
}

function revenue_is_supervisor(): bool
{
    return in_array(active_role(), ['super_admin','clinic_admin'], true)
        && has_role(['super_admin','clinic_admin']);
}

function revenue_active_cashier_session(PDO $pdo, int $branchId, int $userId, bool $forUpdate = false): ?array
{
    $sql = 'SELECT * FROM `' . table('cashier_sessions') . '` '
        . 'WHERE branch_id=? AND cashier_user_id=? AND status="open" ORDER BY id DESC LIMIT 1'
        . ($forUpdate ? ' FOR UPDATE' : '');
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$branchId, $userId]);
    return $stmt->fetch() ?: null;
}

function revenue_require_cashier_session(PDO $pdo, int $branchId, int $userId, bool $forUpdate = false): array
{
    if (!revenue_schema_ready()) {
        throw new RuntimeException('Apply Version 0.5.9.7 before receiving payments.');
    }
    $session = revenue_active_cashier_session($pdo, $branchId, $userId, $forUpdate);
    if (!$session) {
        throw new RuntimeException('Open your cashier session before receiving or returning money.');
    }
    return $session;
}

function revenue_cashier_session_totals(PDO $pdo, int $sessionId): array
{
    $payments = $pdo->prepare(
        'SELECT '
        . 'COALESCE(SUM(CASE WHEN method="cash" THEN amount ELSE 0 END),0) cash_received,'
        . 'COALESCE(SUM(CASE WHEN method<>"cash" THEN amount ELSE 0 END),0) noncash_received '
        . 'FROM `' . table('payments') . '` WHERE cashier_session_id=?'
    );
    $payments->execute([$sessionId]);
    $received = $payments->fetch() ?: ['cash_received'=>0,'noncash_received'=>0];

    $adjustments = $pdo->prepare(
        'SELECT '
        . 'COALESCE(SUM(CASE WHEN method="cash" THEN amount ELSE 0 END),0) cash_returned,'
        . 'COALESCE(SUM(CASE WHEN method<>"cash" THEN amount ELSE 0 END),0) noncash_returned '
        . 'FROM `' . table('payment_adjustments') . '` WHERE cashier_session_id=? AND status="processed"'
    );
    $adjustments->execute([$sessionId]);
    $returned = $adjustments->fetch() ?: ['cash_returned'=>0,'noncash_returned'=>0];

    return [
        'cash_received'=>(float)$received['cash_received'],
        'noncash_received'=>(float)$received['noncash_received'],
        'cash_returned'=>(float)$returned['cash_returned'],
        'noncash_returned'=>(float)$returned['noncash_returned'],
    ];
}

function revenue_load_discount_authorization(PDO $pdo, int $authorizationId, int $branchId, int $patientId, ?int $queueId, bool $forUpdate = false): array
{
    if ($authorizationId < 1) {
        throw new RuntimeException('Select an approved discount authorization.');
    }
    $stmt = $pdo->prepare(
        'SELECT * FROM `' . table('discount_authorizations') . '` '
        . 'WHERE id=? AND branch_id=? AND patient_id=? AND status="approved" '
        . 'AND (queue_entry_id IS NULL OR queue_entry_id=?) LIMIT 1'
        . ($forUpdate ? ' FOR UPDATE' : '')
    );
    $stmt->execute([$authorizationId, $branchId, $patientId, $queueId ?: 0]);
    $row = $stmt->fetch();
    if (!$row) {
        throw new RuntimeException('The selected discount is not approved for this patient and visit.');
    }
    if ((int)$row['requested_by'] === (int)$row['reviewed_by']) {
        throw new RuntimeException('A discount cannot be approved by the same user who requested it.');
    }
    return $row;
}

function revenue_mark_discount_used(PDO $pdo, int $authorizationId, int $invoiceId, int $userId): void
{
    $stmt = $pdo->prepare(
        'UPDATE `' . table('discount_authorizations') . '` '
        . 'SET status="used",invoice_id=?,used_by=?,used_at=NOW() WHERE id=? AND status="approved"'
    );
    $stmt->execute([$invoiceId, $userId ?: null, $authorizationId]);
    if ($stmt->rowCount() !== 1) {
        throw new RuntimeException('The discount authorization was already used or changed.');
    }
}

function revenue_load_zero_charge_authorization(PDO $pdo, int $authorizationId, int $branchId, int $patientId, int $queueId, bool $forUpdate = false): array
{
    if ($authorizationId < 1) {
        throw new RuntimeException('Select an approved zero-charge authorization before completing this visit.');
    }
    $stmt = $pdo->prepare(
        'SELECT * FROM `' . table('zero_charge_authorizations') . '` '
        . 'WHERE id=? AND branch_id=? AND patient_id=? AND queue_entry_id=? AND status="approved" LIMIT 1'
        . ($forUpdate ? ' FOR UPDATE' : '')
    );
    $stmt->execute([$authorizationId, $branchId, $patientId, $queueId]);
    $row = $stmt->fetch();
    if (!$row) {
        throw new RuntimeException('The selected zero-charge authorization is not valid for this visit.');
    }
    if ((int)$row['requested_by'] === (int)$row['reviewed_by']) {
        throw new RuntimeException('A zero-charge case cannot be approved by the same user who requested it.');
    }
    return $row;
}

function revenue_mark_zero_charge_used(PDO $pdo, int $authorizationId, int $userId): void
{
    $stmt = $pdo->prepare(
        'UPDATE `' . table('zero_charge_authorizations') . '` '
        . 'SET status="used",used_by=?,used_at=NOW() WHERE id=? AND status="approved"'
    );
    $stmt->execute([$userId ?: null, $authorizationId]);
    if ($stmt->rowCount() !== 1) {
        throw new RuntimeException('The zero-charge authorization was already used or changed.');
    }
}

function revenue_recalculate_payment_status(PDO $pdo, int $paymentId): void
{
    $payment = $pdo->prepare('SELECT amount FROM `' . table('payments') . '` WHERE id=? FOR UPDATE');
    $payment->execute([$paymentId]);
    $amount = $payment->fetchColumn();
    if ($amount === false) {
        throw new RuntimeException('Payment record not found.');
    }
    $adjusted = $pdo->prepare(
        'SELECT COALESCE(SUM(amount),0) FROM `' . table('payment_adjustments') . '` '
        . 'WHERE payment_id=? AND status="processed"'
    );
    $adjusted->execute([$paymentId]);
    $total = round((float)$adjusted->fetchColumn(), 2);
    $status = $total <= 0.001 ? 'posted' : ($total + 0.001 >= (float)$amount ? 'adjusted' : 'partially_adjusted');
    $pdo->prepare('UPDATE `' . table('payments') . '` SET adjustment_total=?,status=? WHERE id=?')
        ->execute([$total, $status, $paymentId]);
}

function revenue_recalculate_invoice(PDO $pdo, int $invoiceId, ?string $adjustmentType = null): array
{
    $stmt = $pdo->prepare('SELECT amount,status FROM `' . table('invoices') . '` WHERE id=? FOR UPDATE');
    $stmt->execute([$invoiceId]);
    $invoice = $stmt->fetch();
    if (!$invoice) {
        throw new RuntimeException('Invoice record not found.');
    }
    $paid = $pdo->prepare(
        'SELECT COALESCE(SUM(p.amount-p.adjustment_total),0) FROM `' . table('payments') . '` p WHERE p.invoice_id=?'
    );
    $paid->execute([$invoiceId]);
    $paidAmount = max(0, round((float)$paid->fetchColumn(), 2));
    $amount = round((float)$invoice['amount'], 2);
    if ($paidAmount <= 0.001) {
        $status = $adjustmentType === 'refund' ? 'refunded' : 'unpaid';
    } elseif ($paidAmount + 0.001 >= $amount) {
        $status = 'paid';
    } else {
        $status = 'partial';
    }
    $pdo->prepare('UPDATE `' . table('invoices') . '` SET paid_amount=?,status=?,updated_at=NOW() WHERE id=?')
        ->execute([$paidAmount, $status, $invoiceId]);
    return ['paid_amount'=>$paidAmount,'status'=>$status,'amount'=>$amount];
}

function revenue_claim_transition_allowed(string $from, string $to): bool
{
    if ($from === $to) return true;
    $map = [
        'draft'=>['ready'],
        'ready'=>['draft','submitted'],
        'submitted'=>['pending','information_required','approved','partially_approved','denied'],
        'pending'=>['information_required','approved','partially_approved','denied'],
        'information_required'=>['submitted','pending','denied'],
        'approved'=>['appealed','closed'],
        'partially_approved'=>['appealed','closed'],
        'denied'=>['appealed','closed'],
        'appealed'=>['submitted','pending','approved','partially_approved','denied','closed'],
        'paid'=>['closed'],
        'closed'=>[],
    ];
    return in_array($to, $map[$from] ?? [], true);
}

function revenue_cashier_windows(PDO $pdo, int $branchId, bool $activeOnly = true): array
{
    if (!schema_table_exists(table('cashier_windows'))) return [];
    $stmt=$pdo->prepare('SELECT * FROM `' . table('cashier_windows') . '` WHERE branch_id=?' . ($activeOnly?' AND is_active=1':'') . ' ORDER BY window_name,id');
    $stmt->execute([$branchId]);
    return $stmt->fetchAll();
}

function revenue_cashier_window(PDO $pdo, int $branchId, int $windowId, bool $forUpdate = false): array
{
    $stmt=$pdo->prepare('SELECT * FROM `' . table('cashier_windows') . '` WHERE id=? AND branch_id=? AND is_active=1 LIMIT 1' . ($forUpdate?' FOR UPDATE':''));
    $stmt->execute([$windowId,$branchId]);
    $row=$stmt->fetch();
    if(!$row) throw new RuntimeException('Select an active cashier window.');
    return $row;
}

function revenue_default_payment_method_rules(): array
{
    return [
        'cash' => ['method_code'=>'cash','method_name'=>'Cash','requires_reference'=>0,'allows_change'=>1,'is_active'=>1],
        'card' => ['method_code'=>'card','method_name'=>'Card','requires_reference'=>1,'allows_change'=>0,'is_active'=>1],
        'gcash' => ['method_code'=>'gcash','method_name'=>'GCash','requires_reference'=>1,'allows_change'=>0,'is_active'=>1],
        'maya' => ['method_code'=>'maya','method_name'=>'Maya','requires_reference'=>1,'allows_change'=>0,'is_active'=>1],
        'bank_transfer' => ['method_code'=>'bank_transfer','method_name'=>'Bank Transfer','requires_reference'=>1,'allows_change'=>0,'is_active'=>1],
        'e-wallet' => ['method_code'=>'e-wallet','method_name'=>'Other e-wallet','requires_reference'=>1,'allows_change'=>0,'is_active'=>1],
        'cheque' => ['method_code'=>'cheque','method_name'=>'Cheque','requires_reference'=>1,'allows_change'=>0,'is_active'=>1],
        'mixed' => ['method_code'=>'mixed','method_name'=>'Mixed Payment','requires_reference'=>1,'allows_change'=>1,'is_active'=>1],
        'other' => ['method_code'=>'other','method_name'=>'Other','requires_reference'=>1,'allows_change'=>0,'is_active'=>1],
    ];
}

function revenue_payment_method_rule(PDO $pdo, string $method): array
{
    $method = trim($method) ?: 'cash';
    $defaults = revenue_default_payment_method_rules();
    if (schema_table_exists(table('payment_method_rules')) && schema_column_exists(table('payment_method_rules'), 'method_code')) {
        try {
            $stmt = $pdo->prepare('SELECT method_code,method_name,requires_reference,allows_change,is_active FROM `' . table('payment_method_rules') . '` WHERE method_code=? LIMIT 1');
            $stmt->execute([$method]);
            $row = $stmt->fetch();
            if ($row) {
                return [
                    'method_code'=>(string)$row['method_code'],
                    'method_name'=>(string)$row['method_name'],
                    'requires_reference'=>(int)$row['requires_reference'],
                    'allows_change'=>(int)$row['allows_change'],
                    'is_active'=>(int)$row['is_active'],
                ];
            }
        } catch (Throwable $exception) {
            error_log('Payment method rule fallback: ' . $exception->getMessage());
        }
    }
    return $defaults[$method] ?? ['method_code'=>$method,'method_name'=>ucwords(str_replace(['_','-'],' ', $method)),'requires_reference'=>1,'allows_change'=>0,'is_active'=>1];
}

function revenue_payment_method_requires_reference(PDO $pdo, string $method): bool
{
    $rule = revenue_payment_method_rule($pdo, $method);
    return !empty($rule['requires_reference']);
}

function revenue_payment_method_allows_change(PDO $pdo, string $method): bool
{
    $rule = revenue_payment_method_rule($pdo, $method);
    return !empty($rule['allows_change']);
}

function revenue_clean_payment_reference(string $reference): string
{
    $reference = trim(preg_replace('/\s+/', ' ', $reference) ?? '');
    return mb_substr($reference, 0, 190);
}

function revenue_record_transaction_event(PDO $pdo, string $eventType, string $eventLabel, array $data = []): void
{
    if (!schema_table_exists(table('transaction_audit_events'))) return;
    try {
        $pdo->prepare(
            'INSERT INTO `' . table('transaction_audit_events') . '` (branch_id,patient_id,invoice_id,payment_id,cashier_session_id,cashier_window_id,event_type,event_label,previous_status,new_status,amount,reason,metadata_json,created_by,created_at) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,NOW())'
        )->execute([
            $data['branch_id'] ?? null,
            $data['patient_id'] ?? null,
            $data['invoice_id'] ?? null,
            $data['payment_id'] ?? null,
            $data['cashier_session_id'] ?? null,
            $data['cashier_window_id'] ?? null,
            mb_substr($eventType,0,80),
            mb_substr($eventLabel,0,190),
            $data['previous_status'] ?? null,
            $data['new_status'] ?? null,
            isset($data['amount']) ? round((float)$data['amount'],2) : null,
            isset($data['reason']) ? mb_substr((string)$data['reason'],0,500) : null,
            json_encode($data['metadata'] ?? [], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES),
            $data['created_by'] ?? (current_user()['id'] ?? null),
        ]);
    } catch (Throwable $exception) {
        error_log('Transaction audit event skipped: ' . $exception->getMessage());
    }
}

function revenue_lock_cashier_transaction(PDO $pdo, int $branchId, int $userId, int $patientId, int $queueId = 0, string $note = ''): array
{
    if($patientId<1) throw new RuntimeException('Select a patient or payee account.');
    $session=revenue_require_cashier_session($pdo,$branchId,$userId,true);
    $lockedPatient=(int)($session['locked_patient_id']??0);
    $lockedQueue=(int)($session['locked_queue_entry_id']??0);
    if($lockedPatient>0 && ($lockedPatient!==$patientId || ($lockedQueue>0 && $queueId>0 && $lockedQueue!==$queueId))){
        throw new RuntimeException('Finish or cancel the current cashier transaction before switching to another patient.');
    }
    if($queueId>0){
        $queue=$pdo->prepare('SELECT patient_id,status FROM `' . table('queue_entries') . '` WHERE id=? AND branch_id=? FOR UPDATE');
        $queue->execute([$queueId,$branchId]);$row=$queue->fetch();
        if(!$row || (int)$row['patient_id']!==$patientId || !in_array((string)$row['status'],['waiting_billing','cashier_called'],true)) throw new RuntimeException('This cashier queue visit is no longer available for the selected patient.');
    }
    if($lockedPatient===0){
        // Some upgraded databases carried a legacy numeric zero instead of NULL.
        // Accept both empty representations so the first focused transaction can
        // be established reliably after the cashier-window migration.
        $update=$pdo->prepare('UPDATE `' . table('cashier_sessions') . '` SET locked_patient_id=?,locked_queue_entry_id=?,transaction_locked_at=NOW(),transaction_lock_note=? WHERE id=? AND status="open" AND (locked_patient_id IS NULL OR locked_patient_id=0)');
        $update->execute([$patientId,$queueId?:null,mb_substr(trim($note),0,500)?:null,(int)$session['id']]);
        if($update->rowCount()!==1) throw new RuntimeException('The cashier session was locked by another transaction. Reload the register.');
    }elseif($queueId>0 && $lockedQueue===0){
        $update=$pdo->prepare('UPDATE `' . table('cashier_sessions') . '` SET locked_queue_entry_id=? WHERE id=? AND status="open" AND locked_patient_id=? AND (locked_queue_entry_id IS NULL OR locked_queue_entry_id=0)');
        $update->execute([$queueId,(int)$session['id'],$patientId]);
        if($update->rowCount()!==1) throw new RuntimeException('The cashier queue lock changed before it could be attached to this transaction. Reload the register.');
    }

    $verify=$pdo->prepare('SELECT * FROM `' . table('cashier_sessions') . '` WHERE id=? AND branch_id=? AND cashier_user_id=? AND status="open" LIMIT 1 FOR UPDATE');
    $verify->execute([(int)$session['id'],$branchId,$userId]);
    $locked=$verify->fetch();
    if(!$locked || (int)($locked['locked_patient_id']??0)!==$patientId){
        throw new RuntimeException('The focused cashier transaction did not persist. Reload the register and try again.');
    }
    if($queueId>0 && (int)($locked['locked_queue_entry_id']??0)!==$queueId){
        throw new RuntimeException('The focused cashier transaction was not attached to the selected queue visit. Reload the register and try again.');
    }
    return $locked;
}

function revenue_assert_cashier_transaction(array $session, int $patientId, int $queueId = 0): void
{
    if((int)($session['locked_patient_id']??0)!==$patientId) throw new RuntimeException('Start and lock this patient transaction before posting cashier activity.');
    $lockedQueue=(int)($session['locked_queue_entry_id']??0);
    if($lockedQueue>0 && $queueId>0 && $lockedQueue!==$queueId) throw new RuntimeException('The active cashier transaction is locked to another queue visit.');
}

function revenue_release_cashier_transaction(PDO $pdo, int $sessionId, int $patientId, string $reason): void
{
    $reason=trim($reason);
    $update=$pdo->prepare('UPDATE `' . table('cashier_sessions') . '` SET locked_patient_id=NULL,locked_queue_entry_id=NULL,transaction_locked_at=NULL,transaction_lock_note=? WHERE id=? AND status="open" AND locked_patient_id=?');
    $update->execute([$reason!==''?mb_substr($reason,0,500):null,$sessionId,$patientId]);
    if($update->rowCount()!==1) throw new RuntimeException('The cashier transaction lock changed before it could be released.');
}

function revenue_record_checkout(PDO $pdo, array $session, int $patientId, int $queueId, ?int $appointmentId, ?int $invoiceId, string $type, float $amount, ?string $reason = null, ?int $authorizationId = null): int
{
    if(!schema_table_exists(table('cashier_checkouts'))) return 0;
    if(!in_array($type,['paid','zero_balance','insurance','settled','balance_payment','partial_balance'],true))$type='settled';
    $pdo->prepare('INSERT INTO `' . table('cashier_checkouts') . '` (branch_id,cashier_session_id,cashier_window_id,cashier_user_id,patient_id,queue_entry_id,appointment_id,invoice_id,checkout_type,amount_collected,reason,authorization_id,completed_at) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,NOW())')
        ->execute([(int)$session['branch_id'],(int)$session['id'],(int)($session['window_id']??0),(int)$session['cashier_user_id'],$patientId,$queueId?:null,$appointmentId,$invoiceId,$type,round($amount,2),$reason?mb_substr(trim($reason),0,500):null,$authorizationId]);
    return (int)$pdo->lastInsertId();
}
