<?php
/**
 * Transactions API - Cash Book Entry Processing
 * Handles creation, updating, and management of financial transactions
 * with secure database transaction blocks and automated bank balance updates
 */

require_once __DIR__ . '/../config.php';
require_once __DIR__ . '/../includes/auth.php';
require_once __DIR__ . '/../includes/db.php';

require_login();
header('Content-Type: application/json');

$mysqli = db();
$action = $_GET['action'] ?? $_POST['action'] ?? 'create';

// ── Petty Cash helpers ───────────────────────────────────────────────────────

function ensure_petty_cash_replen_schema($db) {
    // Add cash_book_transaction_id column if it doesn't exist yet
    $db->query("ALTER TABLE petty_cash_replenishments
                ADD COLUMN IF NOT EXISTS cash_book_transaction_id INT DEFAULT NULL");
    $db->query("ALTER TABLE petty_cash_replenishments
                MODIFY COLUMN IF EXISTS reference_no VARCHAR(100) DEFAULT NULL");
}

/**
 * Find the petty cash float for a Petty Cash Top Up transaction.
 * Priority: sub_account_head "Float: <name>" → linked bank account → first active float.
 */
function find_petty_cash_float($db, $subAccountHead, $bankAccountId) {
    if (preg_match('/^Float:\s*(.+)$/i', trim($subAccountHead ?? ''), $fm)) {
        $name = trim($fm[1]);
        $s = $db->prepare("SELECT id FROM petty_cash_float WHERE float_name=? AND is_active=1 LIMIT 1");
        $s->bind_param('s', $name); $s->execute();
        $r = $s->get_result()->fetch_assoc(); $s->close();
        if ($r) return $r['id'];
    }
    if ($bankAccountId) {
        $s = $db->prepare("SELECT id FROM petty_cash_float WHERE linked_bank_account_id=? AND is_active=1 LIMIT 1");
        $s->bind_param('i', $bankAccountId); $s->execute();
        $r = $s->get_result()->fetch_assoc(); $s->close();
        if ($r) return $r['id'];
    }
    $r = $db->query("SELECT id FROM petty_cash_float WHERE is_active=1 ORDER BY id LIMIT 1")->fetch_assoc();
    return $r ? $r['id'] : 0;
}

/**
 * Update petty cash float balance and write a Completed replenishment record.
 * Called from both create (direct submit) and approve flows.
 */
function auto_replenish_petty_cash($db, $transaction, $amount, $performedBy) {
    ensure_petty_cash_replen_schema($db);

    $txId       = $transaction['id'];
    $bankId     = $transaction['bank_account_id'];
    $subHead    = $transaction['sub_account_head'] ?? '';
    $vNum       = $transaction['voucher_number'];
    $txDate     = $transaction['transaction_date'];

    // ── Step 1: check for existing Pending replenishment linked to this transaction
    $s = $db->prepare("SELECT * FROM petty_cash_replenishments
                       WHERE cash_book_transaction_id=? AND status='Pending' LIMIT 1");
    $s->bind_param('i', $txId); $s->execute();
    $replen = $s->get_result()->fetch_assoc(); $s->close();

    if ($replen) {
        // Linked record exists — use its float_id and amount
        $floatId    = $replen['float_id'];
        $replenAmt  = $replen['amount'];

        $u = $db->prepare("UPDATE petty_cash_float SET current_balance=current_balance+? WHERE id=? AND is_active=1");
        $u->bind_param('di', $replenAmt, $floatId);
        if (!$u->execute()) throw new Exception('Failed to update petty cash float');
        $u->close();

        $u2 = $db->prepare("UPDATE petty_cash_replenishments SET status='Completed', performed_by=?, performed_at=NOW() WHERE id=?");
        $u2->bind_param('ii', $performedBy, $replen['id']); $u2->execute(); $u2->close();

    } else {
        // No linked record — find float and insert a fresh Completed record
        $floatId = find_petty_cash_float($db, $subHead, $bankId);
        if (!$floatId) return; // no float configured, skip silently

        $u = $db->prepare("UPDATE petty_cash_float SET current_balance=current_balance+? WHERE id=? AND is_active=1");
        $u->bind_param('di', $amount, $floatId);
        if (!$u->execute()) throw new Exception('Failed to update petty cash float');
        $u->close();

        $ins = $db->prepare("INSERT INTO petty_cash_replenishments
            (float_id, source_bank_account_id, contra_voucher_number, amount,
             replenishment_date, narration, performed_by, cash_book_transaction_id, status)
            VALUES (?,?,?,?,?,?,?,?,'Completed')");
        $narr = 'Petty Cash Top Up via Cash Book';
        $ins->bind_param('iisdssii',
            $floatId, $bankId, $vNum, $amount,
            $txDate, $narr, $performedBy, $txId);
        $ins->execute(); $ins->close();
    }
}

// ── End Petty Cash helpers ───────────────────────────────────────────────────

// Permission helper functions
function can_view_transactions() {
    return has_role(['admin', 'super_admin', 'accountant', 'finance_manager', 'sales_user']);
}

function can_create_transactions() {
    return has_role(['admin', 'super_admin', 'accountant', 'finance_manager']);
}

function can_approve_transactions() {
    return has_role(['admin', 'super_admin', 'finance_manager']);
}

function can_modify_transactions() {
    return has_role(['admin', 'super_admin', 'accountant']);
}

// Helper: verify CSRF
if (!function_exists('verify_csrf')) {
    function verify_csrf() {
        $token = $_POST['csrf'] ?? '';
        if (empty($_SESSION['csrf_token']) || !hash_equals($_SESSION['csrf_token'], $token)) {
            http_response_code(403);
            header('Content-Type: application/json');
            echo json_encode(['message' => 'Invalid CSRF token']);
            exit;
        }
    }
}

// Helper: Generate voucher number
function generate_voucher_number($mysqli, $type, $date = null) {
    $date = $date ?? date('Y-m-d');
    $year = date('Y', strtotime($date));
    $month = date('m', strtotime($date));
    $day = date('d', strtotime($date));
    
    // Format: VCH/YYYY/MMDD/XXXX
    $prefix = 'VCH/' . $year . '/' . $month . $day . '/';
    $like = $prefix . '%';
    
    $stmt = $mysqli->prepare("SELECT voucher_number FROM transactions WHERE voucher_number LIKE ? ORDER BY id DESC LIMIT 1");
    $stmt->bind_param('s', $like);
    $stmt->execute();
    $result = $stmt->get_result();
    $row = $result->fetch_assoc();
    $stmt->close();
    
    $next = 1;
    if ($row && preg_match('/\/\d{4}\/(\d{4})$/', $row['voucher_number'], $matches)) {
        $next = intval($matches[1]) + 1;
    }
    
    return $prefix . str_pad($next, 4, '0', STR_PAD_LEFT);
}

// Helper: Convert amount to words (simplified)
function amount_in_words($amount, $currency = 'LKR') {
    $amount = floatval($amount);
    $whole = floor($amount);
    $decimal = round(($amount - $whole) * 100);
    
    $ones = ['', 'One', 'Two', 'Three', 'Four', 'Five', 'Six', 'Seven', 'Eight', 'Nine'];
    $teens = ['Ten', 'Eleven', 'Twelve', 'Thirteen', 'Fourteen', 'Fifteen', 'Sixteen', 'Seventeen', 'Eighteen', 'Nineteen'];
    $tens = ['', '', 'Twenty', 'Thirty', 'Forty', 'Fifty', 'Sixty', 'Seventy', 'Eighty', 'Ninety'];
    $places = ['', 'Thousand', 'Million', 'Billion'];
    
    $convert_number = function($num) use (&$convert_number, $ones, $teens, $tens) {
        if ($num < 10) return $ones[$num];
        if ($num < 20) return $teens[$num - 10];
        if ($num < 100) return $tens[floor($num / 10)] . ($num % 10 ? ' ' . $ones[$num % 10] : '');
        if ($num < 1000) return $ones[floor($num / 100)] . ' Hundred' . ($num % 100 ? ' and ' . $convert_number($num % 100) : '');
        return '';
    };
    
    $convert_whole = function($num) use ($places, $convert_number) {
        if ($num == 0) return 'Zero';
        
        $parts = [];
        $place = 0;
        while ($num > 0) {
            $chunk = $num % 1000;
            if ($chunk > 0) {
                $part = $convert_number($chunk);
                if (isset($places[$place]) && $places[$place]) {
                    $part .= ' ' . $places[$place];
                }
                array_unshift($parts, $part);
            }
            $num = floor($num / 1000);
            $place++;
        }
        return implode(', ', $parts);
    };
    
    $words = $convert_whole($whole);
    if ($decimal > 0) {
        $words .= ' and ' . $convert_number($decimal) . ' Cents';
    }
    
    $currencyNames = ['LKR' => 'Sri Lankan Rupees', 'USD' => 'US Dollars'];
    return $words . ' ' . ($currencyNames[$currency] ?? $currency);
}

// Helper: Get bank account currency
function get_bank_currency($mysqli, $bankAccountId) {
    if (!$bankAccountId) return 'LKR';
    $stmt = $mysqli->prepare("SELECT currency FROM bank_accounts WHERE id = ?");
    $stmt->bind_param('i', $bankAccountId);
    $stmt->execute();
    $result = $stmt->get_result();
    $row = $result->fetch_assoc();
    $stmt->close();
    return $row['currency'] ?? 'LKR';
}

// Main API Action Switch
switch ($action) {
    case 'create':
    case 'create_transaction':
        if (!can_create_transactions()) {
            json_response(['message' => 'Access denied'], 403);
        }
        
        verify_csrf();
        
        // Extract and validate input data
        $transactionType = trim($_POST['transaction_type'] ?? '');
        $transactionDate = trim($_POST['transaction_date'] ?? date('Y-m-d'));
        $accountHead = trim($_POST['account_head'] ?? '');
        $subAccountHead = trim($_POST['sub_account_head'] ?? '');
        $bankAccountId = !empty($_POST['bank_account_id']) ? intval($_POST['bank_account_id']) : null;
        $destinationBankAccountId = !empty($_POST['destination_bank_account_id']) ? intval($_POST['destination_bank_account_id']) : null;
        $partyType = trim($_POST['party_type'] ?? '');
        $partyName = trim($_POST['party_name'] ?? '');
        $partyId = !empty($_POST['party_id']) ? intval($_POST['party_id']) : null;
        $amount = floatval($_POST['amount'] ?? 0);
        $modeOfPayment = trim($_POST['mode_of_payment'] ?? 'Cash');
        $narration = trim($_POST['narration'] ?? '');
        $documentReference = trim($_POST['document_reference'] ?? '');
        $documentDate = !empty($_POST['document_date']) ? trim($_POST['document_date']) : null;
        $costCenter = trim($_POST['cost_center'] ?? '');
        $projectCode = trim($_POST['project_code'] ?? '');
        $taxType = trim($_POST['tax_type'] ?? '');
        $taxPercent = floatval($_POST['tax_percent'] ?? 0);
        $taxAmount = floatval($_POST['tax_amount'] ?? 0);
        $chequeNumber = trim($_POST['cheque_number'] ?? '');
        $chequeDate = !empty($_POST['cheque_date']) ? trim($_POST['cheque_date']) : null;
        $bankName = trim($_POST['bank_name'] ?? '');
        $isDraft = !empty($_POST['draft']) && $_POST['draft'] == '1';
        
        // Validation
        if (!$transactionType || !in_array($transactionType, ['Receipt', 'Payment', 'Contra', 'Journal'])) {
            json_response(['message' => 'Valid transaction type is required (Receipt, Payment, Contra, Journal)'], 400);
        }
        
        if (!$accountHead) {
            json_response(['message' => 'Account head is required'], 400);
        }
        
        if ($amount <= 0) {
            json_response(['message' => 'Amount must be greater than 0'], 400);
        }
        
        if (!$narration) {
            json_response(['message' => 'Narration is required'], 400);
        }
        
        // Special validation for Contra entries
        if ($transactionType === 'Contra') {
            if (!$bankAccountId || !$destinationBankAccountId) {
                json_response(['message' => 'Contra transactions require both source and destination bank accounts'], 400);
            }
            if ($bankAccountId === $destinationBankAccountId) {
                json_response(['message' => 'Source and destination bank accounts cannot be the same for Contra transactions'], 400);
            }
        } else {
            if (!$bankAccountId) {
                json_response(['message' => 'Bank account is required for this transaction type'], 400);
            }
        }
        
        // Generate voucher number
        $voucherNumber = generate_voucher_number($mysqli, $transactionType, $transactionDate);
        
        // Calculate total amount including tax
        $totalAmount = $amount + $taxAmount;
        
        // Get currency and generate amount in words
        $currency = get_bank_currency($mysqli, $bankAccountId);
        $amountInWords = amount_in_words($totalAmount, $currency);
        
        // Set approval status based on user role
        $enteredBy = $_SESSION['user']['id'] ?? null;
        $approvalStatus = $isDraft ? 'Pending' : (can_approve_transactions() ? 'Approved' : 'Pending');
        $isApproved = $approvalStatus === 'Approved' ? 1 : 0;
        $approvedBy = $isApproved ? $enteredBy : null;
        $approvedAt = $isApproved ? date('Y-m-d H:i:s') : null;
        
        // BEGIN DATABASE TRANSACTION
        $mysqli->begin_transaction();
        
        try {
            // Insert transaction record
            $stmt = $mysqli->prepare("INSERT INTO transactions 
                (voucher_number, transaction_date, transaction_type, account_head, sub_account_head,
                 bank_account_id, party_id, party_type, party_name, amount, amount_in_words,
                 mode_of_payment, narration, document_reference, document_date, cost_center,
                 project_code, tax_type, tax_percent, tax_amount, cheque_number, cheque_date,
                 bank_name, approval_status, is_approved, approved_by, approved_at, entered_by)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
            
            // 28 parameters: voucher_number, transaction_date, transaction_type, account_head, sub_account_head,
            // bank_account_id, party_id, party_type, party_name, amount, amount_in_words,
            // mode_of_payment, narration, document_reference, document_date, cost_center,
            // project_code, tax_type, tax_percent, tax_amount, cheque_number, cheque_date,
            // bank_name, approval_status, is_approved, approved_by, approved_at, entered_by
            $stmt->bind_param('sssssiissdsssssssssdssssiiss',
                $voucherNumber,
                $transactionDate,
                $transactionType,
                $accountHead,
                $subAccountHead,
                $bankAccountId,
                $partyId,
                $partyType,
                $partyName,
                $amount,
                $amountInWords,
                $modeOfPayment,
                $narration,
                $documentReference,
                $documentDate,
                $costCenter,
                $projectCode,
                $taxType,
                $taxPercent,
                $taxAmount,
                $chequeNumber,
                $chequeDate,
                $bankName,
                $approvalStatus,
                $isApproved,
                $approvedBy,
                $approvedAt,
                $enteredBy
            );
            
            if (!$stmt->execute()) {
                throw new Exception('Failed to insert transaction: ' . $stmt->error);
            }
            
            $transactionId = $stmt->insert_id;
            $stmt->close();
            
            // Process bank balance updates (only for approved non-draft transactions)
            if ($isApproved && !$isDraft) {
                switch ($transactionType) {
                    case 'Receipt':
                        // Debit - Increase bank balance
                        $updateStmt = $mysqli->prepare("UPDATE bank_accounts SET current_balance = current_balance + ? WHERE id = ?");
                        $updateStmt->bind_param('di', $totalAmount, $bankAccountId);
                        if (!$updateStmt->execute()) {
                            throw new Exception('Failed to update bank balance for Receipt: ' . $updateStmt->error);
                        }
                        $updateStmt->close();
                        break;
                        
                    case 'Payment':
                        // Credit - Decrease bank balance
                        // Check sufficient balance first
                        $checkStmt = $mysqli->prepare("SELECT current_balance FROM bank_accounts WHERE id = ? FOR UPDATE");
                        $checkStmt->bind_param('i', $bankAccountId);
                        $checkStmt->execute();
                        $result = $checkStmt->get_result();
                        $account = $result->fetch_assoc();
                        $checkStmt->close();
                        
                        if (!$account || $account['current_balance'] < $totalAmount) {
                            throw new Exception('Insufficient balance in bank account');
                        }
                        
                        $updateStmt = $mysqli->prepare("UPDATE bank_accounts SET current_balance = current_balance - ? WHERE id = ?");
                        $updateStmt->bind_param('di', $totalAmount, $bankAccountId);
                        if (!$updateStmt->execute()) {
                            throw new Exception('Failed to update bank balance for Payment: ' . $updateStmt->error);
                        }
                        $updateStmt->close();
                        break;
                        
                    case 'Contra':
                        // Internal fund transfer - Decrement source, increment destination
                        $destCurrency = get_bank_currency($mysqli, $destinationBankAccountId);
                        $sourceCurrency = get_bank_currency($mysqli, $bankAccountId);
                        
                        // Check if currencies match (they should for contra)
                        if ($sourceCurrency !== $destCurrency) {
                            throw new Exception('Source and destination bank accounts must have the same currency for Contra transactions');
                        }
                        
                        // Check sufficient balance in source account
                        $checkStmt = $mysqli->prepare("SELECT current_balance FROM bank_accounts WHERE id = ? FOR UPDATE");
                        $checkStmt->bind_param('i', $bankAccountId);
                        $checkStmt->execute();
                        $result = $checkStmt->get_result();
                        $sourceAccount = $result->fetch_assoc();
                        $checkStmt->close();
                        
                        if (!$sourceAccount || $sourceAccount['current_balance'] < $totalAmount) {
                            throw new Exception('Insufficient balance in source bank account');
                        }
                        
                        // Decrement source account
                        $debitStmt = $mysqli->prepare("UPDATE bank_accounts SET current_balance = current_balance - ? WHERE id = ?");
                        $debitStmt->bind_param('di', $totalAmount, $bankAccountId);
                        if (!$debitStmt->execute()) {
                            throw new Exception('Failed to debit source bank account: ' . $debitStmt->error);
                        }
                        $debitStmt->close();
                        
                        // Increment destination account
                        $creditStmt = $mysqli->prepare("UPDATE bank_accounts SET current_balance = current_balance + ? WHERE id = ?");
                        $creditStmt->bind_param('di', $totalAmount, $destinationBankAccountId);
                        if (!$creditStmt->execute()) {
                            throw new Exception('Failed to credit destination bank account: ' . $creditStmt->error);
                        }
                        $creditStmt->close();
                        
                        // Create linked transaction for the destination account
                        $linkedVoucher = generate_voucher_number($mysqli, 'Contra', $transactionDate);
                        $linkStmt = $mysqli->prepare("INSERT INTO transactions 
                            (voucher_number, transaction_date, transaction_type, account_head, sub_account_head,
                             bank_account_id, party_id, party_type, party_name, amount, amount_in_words,
                             mode_of_payment, narration, document_reference, document_date, cost_center,
                             project_code, tax_type, tax_percent, tax_amount, related_transaction_id,
                             approval_status, is_approved, approved_by, approved_at, entered_by)
                            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
                        
                        $contraNarration = 'Contra Entry (Received from: ' . $voucherNumber . ') - ' . $narration;
                        $linkStmt->bind_param('sssssississssssssssdiisssiiss',
                            $linkedVoucher,
                            $transactionDate,
                            'Contra',
                            $accountHead,
                            $subAccountHead,
                            $destinationBankAccountId,
                            $partyId,
                            $partyType,
                            $partyName,
                            $amount,
                            $amountInWords,
                            $modeOfPayment,
                            $contraNarration,
                            $documentReference,
                            $documentDate,
                            $costCenter,
                            $projectCode,
                            $taxType,
                            $taxPercent,
                            $taxAmount,
                            $transactionId,
                            $approvalStatus,
                            $isApproved,
                            $approvedBy,
                            $approvedAt,
                            $enteredBy
                        );
                        
                        if (!$linkStmt->execute()) {
                            throw new Exception('Failed to create linked contra transaction: ' . $linkStmt->error);
                        }
                        $linkStmt->close();
                        
                        // Update the original transaction with related_transaction_id
                        $updateRelatedStmt = $mysqli->prepare("UPDATE transactions SET related_transaction_id = ? WHERE id = ?");
                        $linkedTransactionId = $mysqli->insert_id;
                        $updateRelatedStmt->bind_param('ii', $linkedTransactionId, $transactionId);
                        $updateRelatedStmt->execute();
                        $updateRelatedStmt->close();
                        break;
                        
                    case 'Journal':
                        // Journal entries are just record keeping - no balance change
                        // They represent accounting entries that don't affect bank balances
                        break;
                }

                // AUTO-REPLENISH PETTY CASH on direct submit (already-approved Payment)
                $isPettyCashTx = $transactionType === 'Payment' && (
                    $accountHead === 'Petty Cash Top Up' ||
                    stripos($accountHead, 'petty cash') !== false ||
                    stripos($subAccountHead ?? '', 'petty cash') !== false
                );
                if ($isPettyCashTx) {
                    $txData = [
                        'id'               => $transactionId,
                        'bank_account_id'  => $bankAccountId,
                        'sub_account_head' => $subAccountHead,
                        'voucher_number'   => $voucherNumber,
                        'transaction_date' => $transactionDate,
                    ];
                    auto_replenish_petty_cash($mysqli, $txData, $totalAmount, $enteredBy);
                }
            }
            
            // Handle file attachments
            if (!empty($_FILES['attachments'])) {
                $uploadDir = __DIR__ . '/../uploads/transaction_attachments/';
                if (!is_dir($uploadDir)) {
                    mkdir($uploadDir, 0755, true);
                }
                
                foreach ($_FILES['attachments']['tmp_name'] as $key => $tmpName) {
                    if ($_FILES['attachments']['error'][$key] === UPLOAD_ERR_OK) {
                        $fileName = basename($_FILES['attachments']['name'][$key]);
                        $fileName = preg_replace('/[^a-zA-Z0-9._-]/', '_', $fileName);
                        $uniqueName = time() . '_' . $fileName;
                        $filePath = $uploadDir . $uniqueName;
                        
                        if (move_uploaded_file($tmpName, $filePath)) {
                            $fileType = $_FILES['attachments']['type'][$key];
                            $fileSize = $_FILES['attachments']['size'][$key];
                            
                            $attStmt = $mysqli->prepare("INSERT INTO transaction_attachments 
                                (transaction_id, file_name, file_path, file_type, file_size, uploaded_by) 
                                VALUES (?, ?, ?, ?, ?, ?)");
                            $relativePath = 'uploads/transaction_attachments/' . $uniqueName;
                            $attStmt->bind_param('isssii', $transactionId, $fileName, $relativePath, $fileType, $fileSize, $enteredBy);
                            $attStmt->execute();
                            $attStmt->close();
                        }
                    }
                }
            }
            
            // COMMIT TRANSACTION
            $mysqli->commit();
            
            json_response([
                'success' => true,
                'message' => 'Transaction created successfully',
                'id' => $transactionId,
                'voucher_number' => $voucherNumber,
                'status' => $approvalStatus,
                'amount' => $totalAmount,
                'currency' => $currency
            ]);
            
        } catch (Exception $e) {
            // ROLLBACK TRANSACTION on error
            $mysqli->rollback();
            json_response(['message' => 'Transaction failed: ' . $e->getMessage()], 500);
        }
        break;
        
    case 'list':
    case 'list_transactions':
        if (!can_view_transactions()) {
            json_response(['message' => 'Access denied'], 403);
        }
        
        $where = [];
        $params = [];
        $types = '';
        
        // Build filters
        if (!empty($_GET['transaction_type'])) {
            if ($_GET['transaction_type'] === 'Contra') {
                // Include Contra, Contra In, and Contra Out
                $where[] = "(t.transaction_type = 'Contra' OR t.transaction_type = 'Contra In' OR t.transaction_type = 'Contra Out')";
            } else {
                $where[] = 't.transaction_type = ?';
                $params[] = $_GET['transaction_type'];
                $types .= 's';
            }
        }
        
        if (!empty($_GET['bank_account_id'])) {
            $where[] = 't.bank_account_id = ?';
            $params[] = intval($_GET['bank_account_id']);
            $types .= 'i';
        }
        
        if (!empty($_GET['account_head'])) {
            $where[] = 't.account_head = ?';
            $params[] = $_GET['account_head'];
            $types .= 's';
        }
        
        if (!empty($_GET['party_name'])) {
            $where[] = 't.party_name LIKE ?';
            $params[] = '%' . $_GET['party_name'] . '%';
            $types .= 's';
        }
        
        if (!empty($_GET['voucher_number'])) {
            $where[] = 't.voucher_number LIKE ?';
            $params[] = '%' . $_GET['voucher_number'] . '%';
            $types .= 's';
        }
        
        if (!empty($_GET['date_from'])) {
            $where[] = 't.transaction_date >= ?';
            $params[] = $_GET['date_from'];
            $types .= 's';
        }
        
        if (!empty($_GET['date_to'])) {
            $where[] = 't.transaction_date <= ?';
            $params[] = $_GET['date_to'];
            $types .= 's';
        }
        
        if (!empty($_GET['approval_status'])) {
            $where[] = 't.approval_status = ?';
            $params[] = $_GET['approval_status'];
            $types .= 's';
        }
        
        if (isset($_GET['is_cancelled'])) {
            $where[] = 't.is_cancelled = ?';
            $params[] = intval($_GET['is_cancelled']);
            $types .= 'i';
        }
        
        $sql = "SELECT t.*, 
                ba.account_name as bank_account_name, 
                ba.account_number as bank_account_number,
                ba.currency as bank_currency,
                u.username as entered_by_name
                FROM transactions t
                LEFT JOIN bank_accounts ba ON ba.id = t.bank_account_id
                LEFT JOIN users u ON u.id = t.entered_by";
        
        if ($where) {
            $sql .= " WHERE " . implode(' AND ', $where);
        }
        
        $sql .= " ORDER BY t.transaction_date DESC, t.id DESC";
        
        if ($types) {
            $stmt = $mysqli->prepare($sql);
            $stmt->bind_param($types, ...$params);
            $stmt->execute();
            $result = $stmt->get_result();
        } else {
            $result = $mysqli->query($sql);
        }
        
        $data = [];
        while ($row = $result->fetch_assoc()) {
            $data[] = $row;
        }
        
        json_response(['success' => true, 'data' => $data]);
        break;
        
    case 'get':
    case 'get_transaction':
        if (!can_view_transactions()) {
            json_response(['message' => 'Access denied'], 403);
        }
        
        $id = intval($_GET['id'] ?? 0);
        if (!$id) {
            json_response(['message' => 'Transaction ID required'], 400);
        }
        
        $stmt = $mysqli->prepare("SELECT t.*, 
            ba.account_name as bank_account_name, 
            ba.account_number as bank_account_number,
            ba.currency as bank_currency,
            u.username as entered_by_name,
            au.username as approved_by_name
            FROM transactions t
            LEFT JOIN bank_accounts ba ON ba.id = t.bank_account_id
            LEFT JOIN users u ON u.id = t.entered_by
            LEFT JOIN users au ON au.id = t.approved_by
            WHERE t.id = ?");
        $stmt->bind_param('i', $id);
        $stmt->execute();
        $result = $stmt->get_result();
        $transaction = $result->fetch_assoc();
        $stmt->close();
        
        if (!$transaction) {
            json_response(['message' => 'Transaction not found'], 404);
        }
        
        // Get attachments
        $attachments = [];
        $attStmt = $mysqli->prepare("SELECT * FROM transaction_attachments WHERE transaction_id = ?");
        $attStmt->bind_param('i', $id);
        $attStmt->execute();
        $attResult = $attStmt->get_result();
        while ($att = $attResult->fetch_assoc()) {
            $attachments[] = $att;
        }
        $attStmt->close();
        $transaction['attachments'] = $attachments;
        
        // Get related transaction if any
        if ($transaction['related_transaction_id']) {
            $relStmt = $mysqli->prepare("SELECT id, voucher_number, transaction_type, bank_account_id, amount FROM transactions WHERE id = ?");
            $relStmt->bind_param('i', $transaction['related_transaction_id']);
            $relStmt->execute();
            $relResult = $relStmt->get_result();
            $transaction['related_transaction'] = $relResult->fetch_assoc();
            $relStmt->close();
        }
        
        json_response(['success' => true, 'data' => $transaction]);
        break;
        
    case 'approve':
    case 'approve_transaction':
        if (!can_approve_transactions()) {
            json_response(['message' => 'Access denied'], 403);
        }
        
        verify_csrf();
        
        $id = intval($_POST['id'] ?? 0);
        $status = trim($_POST['status'] ?? ''); // 'Approved' or 'Rejected'
        $reason = trim($_POST['reason'] ?? '');
        
        if (!$id || !in_array($status, ['Approved', 'Rejected'])) {
            json_response(['message' => 'Invalid request'], 400);
        }
        
        $mysqli->begin_transaction();
        
        try {
            // Get transaction details
            $stmt = $mysqli->prepare("SELECT * FROM transactions WHERE id = ? FOR UPDATE");
            $stmt->bind_param('i', $id);
            $stmt->execute();
            $result = $stmt->get_result();
            $transaction = $result->fetch_assoc();
            $stmt->close();
            
            if (!$transaction) {
                throw new Exception('Transaction not found');
            }
            
            if ($transaction['approval_status'] === $status) {
                throw new Exception('Transaction is already ' . $status);
            }
            
            $approvedBy = $_SESSION['user']['id'] ?? null;
            $approvedAt = date('Y-m-d H:i:s');
            $isApproved = $status === 'Approved' ? 1 : 0;
            
            // Update transaction status
            $updateStmt = $mysqli->prepare("UPDATE transactions SET 
                approval_status = ?, is_approved = ?, approved_by = ?, approved_at = ?, rejection_reason = ?
                WHERE id = ?");
            $updateStmt->bind_param('siissi', $status, $isApproved, $approvedBy, $approvedAt, $reason, $id);
            
            if (!$updateStmt->execute()) {
                throw new Exception('Failed to update approval status');
            }
            $updateStmt->close();
            
            // If approving, update bank balance and post GL entries
            if ($status === 'Approved' && !$transaction['is_cancelled']) {
                $totalAmount = $transaction['amount'] + ($transaction['tax_amount'] ?? 0);
                $bankAccountId = $transaction['bank_account_id'];
                $transactionType = $transaction['transaction_type'];
                
                // Update bank balance
                switch ($transactionType) {
                    case 'Receipt':
                        $balStmt = $mysqli->prepare("UPDATE bank_accounts SET current_balance = current_balance + ? WHERE id = ?");
                        $balStmt->bind_param('di', $totalAmount, $bankAccountId);
                        $balStmt->execute();
                        $balStmt->close();
                        break;
                        
                    case 'Payment':
                        $balStmt = $mysqli->prepare("UPDATE bank_accounts SET current_balance = current_balance - ? WHERE id = ?");
                        $balStmt->bind_param('di', $totalAmount, $bankAccountId);
                        $balStmt->execute();
                        $balStmt->close();
                        break;
                        
                    case 'Contra':
                        // Handle contra approval - both directions
                        if ($transaction['related_transaction_id']) {
                            // Get the related transaction for contra
                            $relStmt = $mysqli->prepare("SELECT bank_account_id FROM transactions WHERE id = ?");
                            $relStmt->bind_param('i', $transaction['related_transaction_id']);
                            $relStmt->execute();
                            $relResult = $relStmt->get_result();
                            $relTransaction = $relResult->fetch_assoc();
                            $relStmt->close();
                            
                            if ($relTransaction) {
                                $destAccountId = $relTransaction['bank_account_id'];
                                
                                // Credit from source account
                                $balStmt1 = $mysqli->prepare("UPDATE bank_accounts SET current_balance = current_balance - ? WHERE id = ?");
                                $balStmt1->bind_param('di', $totalAmount, $bankAccountId);
                                $balStmt1->execute();
                                $balStmt1->close();
                                
                                // Debit to destination account
                                $balStmt2 = $mysqli->prepare("UPDATE bank_accounts SET current_balance = current_balance + ? WHERE id = ?");
                                $balStmt2->bind_param('di', $totalAmount, $destAccountId);
                                $balStmt2->execute();
                                $balStmt2->close();
                            }
                        }
                        break;
                }
                
                // POST GL ENTRIES (Double Entry Bookkeeping)
                postGLEntries($mysqli, $transaction, $totalAmount, $approvedBy);

                // AUTO-REPLENISH PETTY CASH: if this Payment is a Petty Cash Top Up
                $isPettyCashTx = $transactionType === 'Payment' && (
                    $transaction['account_head'] === 'Petty Cash Top Up' ||
                    stripos($transaction['account_head'] ?? '', 'petty cash') !== false ||
                    stripos($transaction['sub_account_head'] ?? '', 'petty cash') !== false
                );
                if ($isPettyCashTx) {
                    auto_replenish_petty_cash($mysqli, $transaction, $totalAmount, $approvedBy);
                }
            }
            
            // LOG AUDIT TRAIL
            logAuditTrail($mysqli, 'transactions', $id, $status, $transaction, array_merge($transaction, [
                'approval_status' => $status,
                'is_approved' => $isApproved,
                'approved_by' => $approvedBy,
                'approved_at' => $approvedAt,
                'rejection_reason' => $reason
            ]), $approvedBy);
            
            $mysqli->commit();
            json_response(['success' => true, 'message' => 'Transaction ' . strtolower($status)]);
        } catch (Exception $e) {
            $mysqli->rollback();
            json_response(['message' => 'Approval failed: ' . $e->getMessage()], 500);
        }
        break;
        
    case 'list_gl_entries':
        if (!can_view_transactions()) {
            json_response(['message' => 'Access denied'], 403);
        }
        
        $where = ['1=1'];
        $params = [];
        $types = '';
        
        if (!empty($_GET['transaction_id'])) {
            $where[] = 'g.transaction_id = ?';
            $params[] = intval($_GET['transaction_id']);
            $types .= 'i';
        }
        
        if (!empty($_GET['date_from'])) {
            $where[] = 'g.entry_date >= ?';
            $params[] = $_GET['date_from'];
            $types .= 's';
        }
        
        if (!empty($_GET['date_to'])) {
            $where[] = 'g.entry_date <= ?';
            $params[] = $_GET['date_to'];
            $types .= 's';
        }
        
        $sql = "SELECT g.*, u.username as created_by_name 
                FROM gl_entries g
                LEFT JOIN users u ON u.id = g.created_by
                WHERE " . implode(' AND ', $where) . "
                ORDER BY g.entry_date DESC, g.id DESC";
        
        if ($types) {
            $stmt = $mysqli->prepare($sql);
            $stmt->bind_param($types, ...$params);
            $stmt->execute();
            $result = $stmt->get_result();
        } else {
            $result = $mysqli->query($sql);
        }
        
        $data = [];
        while ($row = $result->fetch_assoc()) {
            $data[] = $row;
        }
        
        json_response(['success' => true, 'data' => $data]);
        break;
        
    case 'cancel':
    case 'cancel_transaction':
        if (!can_modify_transactions()) {
            json_response(['message' => 'Access denied'], 403);
        }
        
        verify_csrf();
        
        $id = intval($_POST['id'] ?? 0);
        $reason = trim($_POST['reason'] ?? '');
        
        if (!$id) {
            json_response(['message' => 'Transaction ID required'], 400);
        }
        
        $mysqli->begin_transaction();
        
        try {
            // Get transaction details
            $stmt = $mysqli->prepare("SELECT * FROM transactions WHERE id = ? FOR UPDATE");
            $stmt->bind_param('i', $id);
            $stmt->execute();
            $result = $stmt->get_result();
            $transaction = $result->fetch_assoc();
            $stmt->close();
            
            if (!$transaction) {
                throw new Exception('Transaction not found');
            }
            
            if ($transaction['is_cancelled']) {
                throw new Exception('Transaction is already cancelled');
            }
            
            $cancelledBy = $_SESSION['user']['id'] ?? null;
            $cancelledAt = date('Y-m-d H:i:s');
            
            // Cancel the transaction
            $updateStmt = $mysqli->prepare("UPDATE transactions SET 
                is_cancelled = 1, cancelled_by = ?, cancelled_at = ?, cancellation_reason = ?
                WHERE id = ?");
            $updateStmt->bind_param('issi', $cancelledBy, $cancelledAt, $reason, $id);
            
            if (!$updateStmt->execute()) {
                throw new Exception('Failed to cancel transaction');
            }
            $updateStmt->close();
            
            // Reverse bank balance if transaction was approved
            if ($transaction['is_approved'] && !$transaction['is_cancelled']) {
                $totalAmount = $transaction['amount'] + ($transaction['tax_amount'] ?? 0);
                $bankAccountId = $transaction['bank_account_id'];
                $transactionType = $transaction['transaction_type'];
                
                switch ($transactionType) {
                    case 'Receipt':
                        // Reverse receipt - decrease balance
                        $balStmt = $mysqli->prepare("UPDATE bank_accounts SET current_balance = current_balance - ? WHERE id = ?");
                        $balStmt->bind_param('di', $totalAmount, $bankAccountId);
                        $balStmt->execute();
                        $balStmt->close();
                        break;
                        
                    case 'Payment':
                        // Reverse payment - increase balance
                        $balStmt = $mysqli->prepare("UPDATE bank_accounts SET current_balance = current_balance + ? WHERE id = ?");
                        $balStmt->bind_param('di', $totalAmount, $bankAccountId);
                        $balStmt->execute();
                        $balStmt->close();
                        break;
                        
                    case 'Contra':
                        // Reverse contra - swap the amounts
                        if ($transaction['related_transaction_id']) {
                            // Get the related transaction
                            $relStmt = $mysqli->prepare("SELECT bank_account_id FROM transactions WHERE id = ?");
                            $relStmt->bind_param('i', $transaction['related_transaction_id']);
                            $relStmt->execute();
                            $relResult = $relStmt->get_result();
                            $relTransaction = $relResult->fetch_assoc();
                            $relStmt->close();
                            
                            if ($relTransaction) {
                                $destAccountId = $relTransaction['bank_account_id'];
                                
                                // Credit back source account
                                $balStmt1 = $mysqli->prepare("UPDATE bank_accounts SET current_balance = current_balance + ? WHERE id = ?");
                                $balStmt1->bind_param('di', $totalAmount, $bankAccountId);
                                $balStmt1->execute();
                                $balStmt1->close();
                                
                                // Debit from destination account
                                $balStmt2 = $mysqli->prepare("UPDATE bank_accounts SET current_balance = current_balance - ? WHERE id = ?");
                                $balStmt2->bind_param('di', $totalAmount, $destAccountId);
                                $balStmt2->execute();
                                $balStmt2->close();
                            }
                        }
                        break;
                }
            }
            
            $mysqli->commit();
            json_response(['success' => true, 'message' => 'Transaction cancelled successfully']);
            
        } catch (Exception $e) {
            $mysqli->rollback();
            json_response(['message' => 'Cancellation failed: ' . $e->getMessage()], 500);
        }
        break;
        
    case 'generate_voucher':
        if (!can_view_transactions()) {
            json_response(['message' => 'Access denied'], 403);
        }
        
        $type = trim($_GET['type'] ?? 'Receipt');
        $date = trim($_GET['date'] ?? date('Y-m-d'));
        
        if (!in_array($type, ['Receipt', 'Payment', 'Contra', 'Journal'])) {
            json_response(['message' => 'Invalid transaction type'], 400);
        }
        
        $voucherNumber = generate_voucher_number($mysqli, $type, $date);
        json_response(['success' => true, 'voucher_number' => $voucherNumber]);
        break;

    case 'update':
    case 'update_transaction':
        if (!has_role(['admin', 'super_admin'])) {
            json_response(['message' => 'Access denied: Admin only'], 403);
        }
        verify_csrf();

        $id             = intval($_POST['id'] ?? 0);
        $newDate        = trim($_POST['transaction_date'] ?? '');
        $newAmount      = floatval($_POST['amount'] ?? 0);
        $newNarration   = trim($_POST['narration'] ?? '');
        $newReference   = trim($_POST['document_reference'] ?? '');
        $newMode        = trim($_POST['mode_of_payment'] ?? '');
        $newAccountHead = trim($_POST['account_head'] ?? '');
        $newSubHead     = trim($_POST['sub_account_head'] ?? '');
        $newCostCenter  = trim($_POST['cost_center'] ?? '');
        $newPartyName   = trim($_POST['party_name'] ?? '');
        $newBankId      = intval($_POST['bank_account_id'] ?? 0);

        if (!$id || !$newDate || $newAmount <= 0) {
            json_response(['message' => 'ID, date and amount are required'], 400);
        }

        $mysqli->begin_transaction();
        try {
            $stmt = $mysqli->prepare("SELECT * FROM transactions WHERE id = ? AND is_cancelled = 0 FOR UPDATE");
            $stmt->bind_param('i', $id);
            $stmt->execute();
            $old = $stmt->get_result()->fetch_assoc();
            $stmt->close();
            if (!$old) throw new Exception('Transaction not found or already cancelled');

            $oldAmount    = floatval($old['amount']) + floatval($old['tax_amount'] ?? 0);
            $oldBankId    = intval($old['bank_account_id']);
            $txType       = $old['transaction_type'];
            $wasApproved  = intval($old['is_approved']);
            $editedBy     = $_SESSION['user']['id'] ?? null;

            // Use new bank id if provided, else keep old
            $bankId = $newBankId ?: $oldBankId;

            // ── Step 1: Reverse old bank balance impact ──────────────────────
            if ($wasApproved) {
                switch ($txType) {
                    case 'Receipt':
                        $s = $mysqli->prepare("UPDATE bank_accounts SET current_balance = current_balance - ? WHERE id = ?");
                        $s->bind_param('di', $oldAmount, $oldBankId); $s->execute(); $s->close();
                        break;
                    case 'Payment':
                        $s = $mysqli->prepare("UPDATE bank_accounts SET current_balance = current_balance + ? WHERE id = ?");
                        $s->bind_param('di', $oldAmount, $oldBankId); $s->execute(); $s->close();
                        // Reverse petty cash if Petty Cash Top Up
                        if ($old['account_head'] === 'Petty Cash Top Up') {
                            $rc = $mysqli->prepare("SELECT * FROM petty_cash_replenishments WHERE cash_book_transaction_id=? AND status='Completed' LIMIT 1");
                            $rc->bind_param('i', $id); $rc->execute();
                            $rep = $rc->get_result()->fetch_assoc(); $rc->close();
                            if ($rep) {
                                $ru = $mysqli->prepare("UPDATE petty_cash_float SET current_balance=current_balance-? WHERE id=? AND is_active=1");
                                $ru->bind_param('di', $rep['amount'], $rep['float_id']); $ru->execute(); $ru->close();
                                $rs = $mysqli->prepare("UPDATE petty_cash_replenishments SET status='Cancelled' WHERE id=?");
                                $rs->bind_param('i', $rep['id']); $rs->execute(); $rs->close();
                            }
                        }
                        break;
                    case 'Contra':
                        if ($old['related_transaction_id']) {
                            $rel = $mysqli->prepare("SELECT bank_account_id FROM transactions WHERE id=?");
                            $rel->bind_param('i', $old['related_transaction_id']); $rel->execute();
                            $relRow = $rel->get_result()->fetch_assoc(); $rel->close();
                            if ($relRow) {
                                $s1 = $mysqli->prepare("UPDATE bank_accounts SET current_balance=current_balance+? WHERE id=?");
                                $s1->bind_param('di', $oldAmount, $oldBankId); $s1->execute(); $s1->close();
                                $s2 = $mysqli->prepare("UPDATE bank_accounts SET current_balance=current_balance-? WHERE id=?");
                                $s2->bind_param('di', $oldAmount, $relRow['bank_account_id']); $s2->execute(); $s2->close();
                            }
                        }
                        break;
                }
            }

            // ── Step 2: Delete old GL entries ─────────────────────────────────
            $dgl = $mysqli->prepare("DELETE FROM gl_entries WHERE transaction_id = ?");
            $dgl->bind_param('i', $id); $dgl->execute(); $dgl->close();

            // ── Step 3: Update the transaction row ────────────────────────────
            $currency       = get_bank_currency($mysqli, $bankId);
            $amountInWords  = amount_in_words($newAmount, $currency);
            $upd = $mysqli->prepare("UPDATE transactions SET
                transaction_date=?, amount=?, amount_in_words=?, narration=?,
                document_reference=?, mode_of_payment=?, account_head=?,
                sub_account_head=?, cost_center=?, party_name=?, bank_account_id=?
                WHERE id=?");
            $upd->bind_param('sdssssssssii',
                $newDate, $newAmount, $amountInWords, $newNarration,
                $newReference, $newMode, $newAccountHead,
                $newSubHead, $newCostCenter, $newPartyName, $bankId,
                $id);
            if (!$upd->execute()) throw new Exception('Failed to update transaction: ' . $upd->error);
            $upd->close();

            // ── Step 4: Re-apply bank balance ─────────────────────────────────
            if ($wasApproved) {
                switch ($txType) {
                    case 'Receipt':
                        $s = $mysqli->prepare("UPDATE bank_accounts SET current_balance=current_balance+? WHERE id=?");
                        $s->bind_param('di', $newAmount, $bankId); $s->execute(); $s->close();
                        break;
                    case 'Payment':
                        $s = $mysqli->prepare("UPDATE bank_accounts SET current_balance=current_balance-? WHERE id=?");
                        $s->bind_param('di', $newAmount, $bankId); $s->execute(); $s->close();
                        // Re-apply petty cash if still Petty Cash Top Up
                        if ($newAccountHead === 'Petty Cash Top Up') {
                            $updTx = $mysqli->query("SELECT * FROM transactions WHERE id=$id LIMIT 1")->fetch_assoc();
                            if ($updTx) auto_replenish_petty_cash($mysqli, $updTx, $newAmount, $editedBy);
                        }
                        break;
                    case 'Contra':
                        if ($old['related_transaction_id']) {
                            $rel = $mysqli->prepare("SELECT bank_account_id FROM transactions WHERE id=?");
                            $rel->bind_param('i', $old['related_transaction_id']); $rel->execute();
                            $relRow = $rel->get_result()->fetch_assoc(); $rel->close();
                            if ($relRow) {
                                $s1 = $mysqli->prepare("UPDATE bank_accounts SET current_balance=current_balance-? WHERE id=?");
                                $s1->bind_param('di', $newAmount, $bankId); $s1->execute(); $s1->close();
                                $s2 = $mysqli->prepare("UPDATE bank_accounts SET current_balance=current_balance+? WHERE id=?");
                                $s2->bind_param('di', $newAmount, $relRow['bank_account_id']); $s2->execute(); $s2->close();
                            }
                        }
                        break;
                }

                // ── Step 5: Re-post GL entries ────────────────────────────────
                $freshTx = $mysqli->query("SELECT * FROM transactions WHERE id=$id LIMIT 1")->fetch_assoc();
                if ($freshTx) postGLEntries($mysqli, $freshTx, $newAmount, $editedBy);
            }

            $mysqli->commit();
            json_response(['success' => true, 'message' => 'Transaction updated successfully']);

        } catch (Exception $e) {
            $mysqli->rollback();
            json_response(['message' => 'Update failed: ' . $e->getMessage()], 500);
        }
        break;

    case 'delete':
    case 'delete_transaction':
        if (!has_role(['admin', 'super_admin'])) {
            json_response(['message' => 'Access denied: Admin only'], 403);
        }
        verify_csrf();

        $id = intval($_POST['id'] ?? 0);
        if (!$id) json_response(['message' => 'Transaction ID required'], 400);

        $mysqli->begin_transaction();
        try {
            $stmt = $mysqli->prepare("SELECT * FROM transactions WHERE id = ? FOR UPDATE");
            $stmt->bind_param('i', $id);
            $stmt->execute();
            $tx = $stmt->get_result()->fetch_assoc();
            $stmt->close();
            if (!$tx) throw new Exception('Transaction not found');

            $amount      = floatval($tx['amount']) + floatval($tx['tax_amount'] ?? 0);
            $bankId      = intval($tx['bank_account_id']);
            $txType      = $tx['transaction_type'];
            $wasApproved = intval($tx['is_approved']) && !intval($tx['is_cancelled']);
            $deletedBy   = $_SESSION['user']['id'] ?? null;

            // ── Reverse bank balance ──────────────────────────────────────────
            if ($wasApproved) {
                switch ($txType) {
                    case 'Receipt':
                        $s = $mysqli->prepare("UPDATE bank_accounts SET current_balance=current_balance-? WHERE id=?");
                        $s->bind_param('di', $amount, $bankId); $s->execute(); $s->close();
                        break;
                    case 'Payment':
                        $s = $mysqli->prepare("UPDATE bank_accounts SET current_balance=current_balance+? WHERE id=?");
                        $s->bind_param('di', $amount, $bankId); $s->execute(); $s->close();
                        // Reverse petty cash if applicable
                        if ($tx['account_head'] === 'Petty Cash Top Up') {
                            $rc = $mysqli->prepare("SELECT * FROM petty_cash_replenishments WHERE cash_book_transaction_id=? AND status='Completed' LIMIT 1");
                            $rc->bind_param('i', $id); $rc->execute();
                            $rep = $rc->get_result()->fetch_assoc(); $rc->close();
                            if ($rep) {
                                $ru = $mysqli->prepare("UPDATE petty_cash_float SET current_balance=current_balance-? WHERE id=? AND is_active=1");
                                $ru->bind_param('di', $rep['amount'], $rep['float_id']); $ru->execute(); $ru->close();
                                $rs = $mysqli->prepare("UPDATE petty_cash_replenishments SET status='Cancelled' WHERE id=?");
                                $rs->bind_param('i', $rep['id']); $rs->execute(); $rs->close();
                            }
                        }
                        break;
                    case 'Contra':
                        if ($tx['related_transaction_id']) {
                            $rel = $mysqli->prepare("SELECT bank_account_id FROM transactions WHERE id=?");
                            $rel->bind_param('i', $tx['related_transaction_id']); $rel->execute();
                            $relRow = $rel->get_result()->fetch_assoc(); $rel->close();
                            if ($relRow) {
                                $s1 = $mysqli->prepare("UPDATE bank_accounts SET current_balance=current_balance+? WHERE id=?");
                                $s1->bind_param('di', $amount, $bankId); $s1->execute(); $s1->close();
                                $s2 = $mysqli->prepare("UPDATE bank_accounts SET current_balance=current_balance-? WHERE id=?");
                                $s2->bind_param('di', $amount, $relRow['bank_account_id']); $s2->execute(); $s2->close();
                                // Also delete related contra transaction
                                $dr = $mysqli->prepare("DELETE FROM transactions WHERE id=?");
                                $dr->bind_param('i', $tx['related_transaction_id']); $dr->execute(); $dr->close();
                            }
                        }
                        break;
                }
            }

            // ── Delete GL entries ─────────────────────────────────────────────
            $dgl = $mysqli->prepare("DELETE FROM gl_entries WHERE transaction_id = ?");
            $dgl->bind_param('i', $id); $dgl->execute(); $dgl->close();

            // ── Delete attachments records ────────────────────────────────────
            $dat = $mysqli->prepare("DELETE FROM transaction_attachments WHERE transaction_id = ?");
            $dat->bind_param('i', $id); $dat->execute(); $dat->close();

            // ── Hard delete transaction ───────────────────────────────────────
            $del = $mysqli->prepare("DELETE FROM transactions WHERE id = ?");
            $del->bind_param('i', $id);
            if (!$del->execute()) throw new Exception('Failed to delete transaction');
            $del->close();

            logAuditTrail($mysqli, 'transactions', $id, 'Deleted', $tx, ['deleted_by' => $deletedBy], $deletedBy);

            $mysqli->commit();
            json_response(['success' => true, 'message' => 'Transaction deleted and all effects reversed']);

        } catch (Exception $e) {
            $mysqli->rollback();
            json_response(['message' => 'Delete failed: ' . $e->getMessage()], 500);
        }
        break;

    default:
        json_response(['message' => 'Unknown action: ' . $action], 400);
}

/**
 * Post GL Entries (Double Entry Bookkeeping)
 * Creates debit and credit entries in the General Ledger
 */
function postGLEntries($mysqli, $transaction, $totalAmount, $postedBy) {
    // Ensure gl_entries table exists
    $mysqli->query("CREATE TABLE IF NOT EXISTS gl_entries (
        id INT AUTO_INCREMENT PRIMARY KEY,
        transaction_id INT NOT NULL,
        voucher_number VARCHAR(50) NOT NULL,
        entry_date DATE NOT NULL,
        account_code VARCHAR(20) NOT NULL,
        account_name VARCHAR(150) NOT NULL,
        debit_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,
        credit_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,
        narration TEXT,
        cost_center VARCHAR(100) DEFAULT NULL,
        reference_id INT DEFAULT NULL,
        reference_type VARCHAR(50) DEFAULT NULL,
        created_by INT DEFAULT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        INDEX transaction_id (transaction_id),
        INDEX voucher_number (voucher_number),
        INDEX entry_date (entry_date),
        INDEX account_code (account_code)
    ) ENGINE=InnoDB");
    
    // Chart of accounts mapping
    $accountMap = [
        'Sales Revenue' => ['code' => '4000', 'type' => 'Income'],
        'Service Revenue' => ['code' => '4100', 'type' => 'Income'],
        'Other Income' => ['code' => '4200', 'type' => 'Income'],
        'Interest Income' => ['code' => '4300', 'type' => 'Income'],
        'Purchase Expenses' => ['code' => '5000', 'type' => 'Expense'],
        'Salary & Wages' => ['code' => '5100', 'type' => 'Expense'],
        'Rent Expense' => ['code' => '5200', 'type' => 'Expense'],
        'Utilities' => ['code' => '5300', 'type' => 'Expense'],
        'Transportation' => ['code' => '5400', 'type' => 'Expense'],
        'Office Supplies' => ['code' => '5500', 'type' => 'Expense'],
        'Marketing' => ['code' => '5600', 'type' => 'Expense'],
        'Maintenance' => ['code' => '5700', 'type' => 'Expense'],
        'Bank Charges' => ['code' => '5800', 'type' => 'Expense'],
        'Other Expenses' => ['code' => '5900', 'type' => 'Expense'],
        'Cash in Hand' => ['code' => '1000', 'type' => 'Asset'],
        'Bank Accounts' => ['code' => '1100', 'type' => 'Asset'],
        'Accounts Receivable' => ['code' => '1300', 'type' => 'Asset'],
        'Inventory' => ['code' => '1400', 'type' => 'Asset'],
        'Fixed Assets' => ['code' => '1500', 'type' => 'Asset'],
        'Accounts Payable' => ['code' => '2000', 'type' => 'Liability'],
        'Taxes Payable' => ['code' => '2100', 'type' => 'Liability']
    ];
    
    $transactionType = $transaction['transaction_type'];
    $accountHead = $transaction['account_head'];
    $bankAccountId = $transaction['bank_account_id'];
    $voucherNumber = $transaction['voucher_number'];
    $entryDate = $transaction['transaction_date'];
    $costCenter = $transaction['cost_center'] ?? '';
    $referenceId = $transaction['reference_id'] ?? null;
    $referenceType = $transaction['reference_type'] ?? null;
    
    // Get account codes
    $expenseAccount = $accountMap[$accountHead] ?? ['code' => '5900', 'type' => 'Expense'];
    $bankAccountCode = '1100'; // Default bank account code
    
    // Get bank account name
    $bankName = 'Bank Account';
    $stmt = $mysqli->prepare("SELECT account_name FROM bank_accounts WHERE id = ?");
    $stmt->bind_param('i', $bankAccountId);
    $stmt->execute();
    $result = $stmt->get_result();
    if ($bank = $result->fetch_assoc()) {
        $bankName = $bank['account_name'];
    }
    $stmt->close();
    
    $entries = [];
    
    switch($transactionType) {
        case 'Receipt':
            // Receipt: Debit Bank (Asset +), Credit Income (Income +)
            $entries[] = [
                'code' => $bankAccountCode,
                'name' => $bankName,
                'debit' => $totalAmount,
                'credit' => 0,
                'narration' => 'Receipt: ' . $transaction['narration']
            ];
            $entries[] = [
                'code' => $expenseAccount['code'],
                'name' => $accountHead,
                'debit' => 0,
                'credit' => $totalAmount,
                'narration' => 'Revenue recognized: ' . $transaction['narration']
            ];
            break;
            
        case 'Payment':
            // Payment: Debit Expense (Expense +), Credit Bank (Asset -)
            $entries[] = [
                'code' => $expenseAccount['code'],
                'name' => $accountHead,
                'debit' => $totalAmount,
                'credit' => 0,
                'narration' => 'Expense recorded: ' . $transaction['narration']
            ];
            $entries[] = [
                'code' => $bankAccountCode,
                'name' => $bankName,
                'debit' => 0,
                'credit' => $totalAmount,
                'narration' => 'Payment from bank: ' . $transaction['narration']
            ];
            break;
            
        case 'Contra':
            // Contra: Debit Destination Bank, Credit Source Bank
            $destBankName = 'Destination Bank';
            if ($transaction['related_transaction_id']) {
                $relStmt = $mysqli->prepare("SELECT ba.account_name 
                    FROM transactions t
                    LEFT JOIN bank_accounts ba ON ba.id = t.bank_account_id
                    WHERE t.id = ?");
                $relStmt->bind_param('i', $transaction['related_transaction_id']);
                $relStmt->execute();
                $relResult = $relStmt->get_result();
                if ($rel = $relResult->fetch_assoc()) {
                    $destBankName = $rel['account_name'];
                }
                $relStmt->close();
            }
            
            $entries[] = [
                'code' => $bankAccountCode,
                'name' => $destBankName,
                'debit' => $totalAmount,
                'credit' => 0,
                'narration' => 'Funds received: ' . $transaction['narration']
            ];
            $entries[] = [
                'code' => $bankAccountCode,
                'name' => $bankName,
                'debit' => 0,
                'credit' => $totalAmount,
                'narration' => 'Funds transferred: ' . $transaction['narration']
            ];
            break;
            
        case 'Journal':
            // Journal entries - depends on the account head type
            $entries[] = [
                'code' => $expenseAccount['code'],
                'name' => $accountHead . ' (Debit)',
                'debit' => $totalAmount,
                'credit' => 0,
                'narration' => 'Journal debit: ' . $transaction['narration']
            ];
            $entries[] = [
                'code' => $bankAccountCode,
                'name' => $bankName . ' (Credit)',
                'debit' => 0,
                'credit' => $totalAmount,
                'narration' => 'Journal credit: ' . $transaction['narration']
            ];
            break;
    }
    
    // Insert GL entries
    $stmt = $mysqli->prepare("INSERT INTO gl_entries 
        (transaction_id, voucher_number, entry_date, account_code, account_name, 
         debit_amount, credit_amount, narration, cost_center, reference_id, reference_type, created_by)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
    
    foreach ($entries as $entry) {
        $stmt->bind_param('issssssssiis',
            $transaction['id'],
            $voucherNumber,
            $entryDate,
            $entry['code'],
            $entry['name'],
            $entry['debit'],
            $entry['credit'],
            $entry['narration'],
            $costCenter,
            $referenceId,
            $referenceType,
            $postedBy
        );
        
        if (!$stmt->execute()) {
            throw new Exception('Failed to post GL entry: ' . $stmt->error);
        }
    }
    
    $stmt->close();
    return count($entries);
}

/**
 * Log Audit Trail
 * Records all approval/rejection actions for compliance
 */
function logAuditTrail($mysqli, $tableName, $recordId, $action, $oldValues, $newValues, $performedBy) {
    // Ensure audit_log table exists
    $mysqli->query("CREATE TABLE IF NOT EXISTS audit_log (
        id INT AUTO_INCREMENT PRIMARY KEY,
        table_name VARCHAR(50) NOT NULL,
        record_id INT NOT NULL,
        action VARCHAR(50) NOT NULL,
        old_values JSON DEFAULT NULL,
        new_values JSON DEFAULT NULL,
        performed_by INT NOT NULL,
        performed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        ip_address VARCHAR(45) DEFAULT NULL,
        user_agent VARCHAR(255) DEFAULT NULL,
        INDEX table_name (table_name),
        INDEX record_id (record_id),
        INDEX action (action),
        INDEX performed_by (performed_by),
        INDEX performed_at (performed_at)
    ) ENGINE=InnoDB");
    
    $ipAddress = $_SERVER['REMOTE_ADDR'] ?? null;
    $userAgent = $_SERVER['HTTP_USER_AGENT'] ?? null;
    
    // Sanitize old and new values (remove sensitive data)
    $sanitizedOld = sanitizeForAudit($oldValues);
    $sanitizedNew = sanitizeForAudit($newValues);
    
    $oldJson = $sanitizedOld ? json_encode($sanitizedOld) : null;
    $newJson = $sanitizedNew ? json_encode($sanitizedNew) : null;
    
    $stmt = $mysqli->prepare("INSERT INTO audit_log 
        (table_name, record_id, action, old_values, new_values, performed_by, ip_address, user_agent)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
    
    $stmt->bind_param('sisssiss',
        $tableName,
        $recordId,
        $action,
        $oldJson,
        $newJson,
        $performedBy,
        $ipAddress,
        $userAgent
    );
    
    $result = $stmt->execute();
    $stmt->close();
    
    return $result;
}

/**
 * Sanitize data for audit log
 * Removes sensitive fields like passwords
 */
function sanitizeForAudit($data) {
    if (!is_array($data)) {
        return $data;
    }
    
    $sensitiveFields = ['password', 'pass', 'pwd', 'secret', 'token', 'csrf'];
    $sanitized = [];
    
    foreach ($data as $key => $value) {
        if (in_array(strtolower($key), $sensitiveFields)) {
            $sanitized[$key] = '***REDACTED***';
        } elseif (is_array($value)) {
            $sanitized[$key] = sanitizeForAudit($value);
        } else {
            $sanitized[$key] = $value;
        }
    }
    
    return $sanitized;
}

// Helper function for JSON responses
if (!function_exists('json_response')) {
    function json_response($data, $httpCode = 200) {
        http_response_code($httpCode);
        header('Content-Type: application/json');
        echo json_encode($data);
        exit;
    }
}

// Helper function for CSRF verification
if (!function_exists('verify_csrf')) {
    function verify_csrf() {
        $token = $_POST['csrf'] ?? '';
        if (empty($_SESSION['csrf_token']) || !hash_equals($_SESSION['csrf_token'], $token)) {
            json_response(['message' => 'Invalid CSRF token'], 403);
        }
    }
}
?>
