<?php
/**
 * Petty Cash API - Imprest System
 * Handles all petty cash operations including float management, 
 * replenishments, vouchers, and reconciliation
 */

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'] ?? 'list_floats';

// Permission checks
function can_manage_petty_cash() {
    return has_role(['admin', 'super_admin', 'finance_manager', 'accountant']);
}

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

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

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

// Helper functions - MUST be defined before use
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;
    }
}

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);
        }
    }
}

// Amount to words converter
function amountToWords($amount, $currency = 'LKR') {
    $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'];
    
    $amount = floatval($amount);
    $whole = floor($amount);
    $decimal = round(($amount - $whole) * 100);
    
    $convertNumber = function($num) use (&$convertNumber, $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 ' . $convertNumber($num % 100) : '');
        return '';
    };
    
    $convertLarge = function($num) use (&$convertLarge, $convertNumber, $places) {
        if ($num == 0) return 'Zero';
        $parts = [];
        $place = 0;
        while ($num > 0) {
            $chunk = $num % 1000;
            if ($chunk > 0) {
                $part = $convertNumber($chunk);
                if ($places[$place]) {
                    $part .= ' ' . $places[$place];
                }
                array_unshift($parts, $part);
            }
            $num = floor($num / 1000);
            $place++;
        }
        return implode(', ', $parts);
    };
    
    $words = $convertLarge($whole);
    if ($decimal > 0) {
        $words .= ' and ' . $decimal . '/100';
    } else {
        $words .= ' only';
    }
    
    return $words . ' (' . $currency . ')';
}

// Ensure schema exists
function ensure_petty_cash_schema($db) {
    // Petty Cash Float Table
    $db->query("CREATE TABLE IF NOT EXISTS petty_cash_float (
        id INT AUTO_INCREMENT PRIMARY KEY,
        float_name VARCHAR(100) NOT NULL DEFAULT 'Main Petty Cash',
        float_limit DECIMAL(15,2) NOT NULL DEFAULT 25000.00,
        current_balance DECIMAL(15,2) NOT NULL DEFAULT 0.00,
        warning_threshold DECIMAL(15,2) NOT NULL DEFAULT 5000.00,
        linked_bank_account_id INT DEFAULT NULL,
        cost_center VARCHAR(100) DEFAULT 'Head Office',
        is_active TINYINT(1) NOT NULL DEFAULT 1,
        created_by INT DEFAULT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX linked_bank_account_id (linked_bank_account_id),
        INDEX is_active (is_active)
    ) ENGINE=InnoDB");
    
    // Petty Cash Replenishments
    $db->query("CREATE TABLE IF NOT EXISTS petty_cash_replenishments (
        id INT AUTO_INCREMENT PRIMARY KEY,
        float_id INT NOT NULL,
        source_bank_account_id INT NOT NULL,
        contra_voucher_number VARCHAR(50) NOT NULL,
        amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,
        replenishment_date DATE NOT NULL,
        reference_no VARCHAR(100) DEFAULT NULL,
        cash_book_transaction_id INT DEFAULT NULL,
        narration TEXT,
        performed_by INT DEFAULT NULL,
        performed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        status ENUM('Pending','Completed','Cancelled') DEFAULT 'Completed',
        INDEX float_id (float_id),
        INDEX source_bank_account_id (source_bank_account_id),
        INDEX replenishment_date (replenishment_date),
        INDEX cash_book_transaction_id (cash_book_transaction_id)
    ) ENGINE=InnoDB");
    // Add cash_book_transaction_id column if it doesn't exist (for upgrades)
    $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 reference_no VARCHAR(100) DEFAULT NULL");
    
    // Petty Cash Vouchers
    $db->query("CREATE TABLE IF NOT EXISTS petty_cash_vouchers (
        id INT AUTO_INCREMENT PRIMARY KEY,
        float_id INT NOT NULL,
        voucher_number VARCHAR(50) NOT NULL UNIQUE,
        voucher_date DATE NOT NULL,
        expense_account_head VARCHAR(100) NOT NULL,
        sub_account_head VARCHAR(100) DEFAULT NULL,
        amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,
        recipient_name VARCHAR(150) NOT NULL,
        recipient_id VARCHAR(50) DEFAULT NULL,
        recipient_contact VARCHAR(50) DEFAULT NULL,
        narration TEXT NOT NULL,
        cost_center VARCHAR(100) DEFAULT NULL,
        has_receipt TINYINT(1) NOT NULL DEFAULT 0,
        receipt_file_path VARCHAR(500) DEFAULT NULL,
        status ENUM('Draft','Pending','Posted','Cancelled') DEFAULT 'Pending',
        posted_by INT DEFAULT NULL,
        posted_at TIMESTAMP NULL,
        created_by INT DEFAULT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX float_id (float_id),
        INDEX voucher_date (voucher_date),
        INDEX status (status),
        INDEX expense_account_head (expense_account_head)
    ) ENGINE=InnoDB");
    
    // Petty Cash Expense Categories
    $db->query("CREATE TABLE IF NOT EXISTS petty_cash_expense_categories (
        id INT AUTO_INCREMENT PRIMARY KEY,
        category_code VARCHAR(20) NOT NULL UNIQUE,
        category_name VARCHAR(100) NOT NULL,
        account_head VARCHAR(100) NOT NULL,
        description TEXT,
        is_active TINYINT(1) DEFAULT 1,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB");
    
    // Insert default categories
    $categories = [
        ['OFFICE', 'Office Supplies', 'Office Supplies', 'Stationery, printing, office consumables'],
        ['REFRESH', 'Refreshments', 'Other Expenses', 'Tea, coffee, snacks for staff/meetings'],
        ['TRAVEL', 'Traveling Expenses', 'Transportation', 'Local travel, taxi, fuel reimbursement'],
        ['COURIER', 'Courier & Postage', 'Office Supplies', 'Postal services, courier charges'],
        ['MAINT', 'Minor Maintenance', 'Maintenance', 'Small repairs, maintenance supplies'],
        ['PHONE', 'Telephone & Internet', 'Utilities', 'Mobile recharge, minor telecom expenses'],
        ['CLEAN', 'Cleaning Supplies', 'Maintenance', 'Cleaning materials, sanitation supplies'],
        ['MISC', 'Miscellaneous', 'Other Expenses', 'Other small operational expenses']
    ];
    
    foreach ($categories as $cat) {
        $stmt = $db->prepare("INSERT IGNORE INTO petty_cash_expense_categories 
            (category_code, category_name, account_head, description) VALUES (?, ?, ?, ?)");
        $stmt->bind_param('ssss', $cat[0], $cat[1], $cat[2], $cat[3]);
        $stmt->execute();
        $stmt->close();
    }
    
    // Initialize default float if not exists
    $db->query("INSERT INTO petty_cash_float (float_name, float_limit, current_balance, warning_threshold, cost_center, is_active)
        SELECT 'Main Petty Cash', 25000.00, 0.00, 5000.00, 'Head Office', 1
        WHERE NOT EXISTS (SELECT 1 FROM petty_cash_float WHERE float_name = 'Main Petty Cash')");
}

// Ensure schema
//ensure_petty_cash_schema($mysqli);

// Generate voucher number
function generate_pcv_number($mysqli, $date = null) {
    $date = $date ?? date('Y-m-d');
    $year = date('Y', strtotime($date));
    $month = date('m', strtotime($date));
    $day = date('d', strtotime($date));
    
    $prefix = 'PCV/' . $year . '/' . $month . $day . '/';
    $like = $prefix . '%';
    
    $stmt = $mysqli->prepare("SELECT voucher_number FROM petty_cash_vouchers 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})$/', $row['voucher_number'], $matches)) {
        $next = intval($matches[1]) + 1;
    }
    
    return $prefix . str_pad($next, 4, '0', STR_PAD_LEFT);
}

// Generate contra voucher number
function generate_contra_voucher($mysqli, $date = null) {
    $date = $date ?? date('Y-m-d');
    $year = date('Y', strtotime($date));
    $prefix = 'CTR-PC/' . $year . '-';
    $like = $prefix . '%';
    
    // Check both tables for existing voucher numbers
    $lastNum = 0;
    
    // Check petty_cash_replenishments
    $stmt1 = $mysqli->prepare("SELECT contra_voucher_number FROM petty_cash_replenishments WHERE contra_voucher_number LIKE ? ORDER BY id DESC LIMIT 1");
    $stmt1->bind_param('s', $like);
    $stmt1->execute();
    $result1 = $stmt1->get_result();
    $row1 = $result1->fetch_assoc();
    $stmt1->close();
    
    if ($row1 && preg_match('/-(\d{4})$/', $row1['contra_voucher_number'], $matches)) {
        $lastNum = max($lastNum, intval($matches[1]));
    }
    
    // Check transactions table (in case of truncation mismatch)
    // Use pattern that matches base voucher numbers (ignore -OUT/-IN suffixes)
    $baseLike = $prefix . '____';
    $stmt2 = $mysqli->prepare("SELECT voucher_number FROM transactions WHERE voucher_number LIKE ? AND (transaction_type = 'Contra In' OR transaction_type = 'Contra Out') ORDER BY id DESC LIMIT 1");
    $stmt2->bind_param('s', $baseLike);
    $stmt2->execute();
    $result2 = $stmt2->get_result();
    $row2 = $result2->fetch_assoc();
    $stmt2->close();
    
    if ($row2 && preg_match('/-(\d{4})(?:-OUT|-IN)?$/', $row2['voucher_number'], $matches)) {
        $lastNum = max($lastNum, intval($matches[1]));
    }
    
    $next = $lastNum + 1;
    
    return $prefix . str_pad($next, 4, '0', STR_PAD_LEFT);
}

// API Actions
switch ($action) {
    // ==================== FLOAT MANAGEMENT ====================
    
    case 'list_floats':
    case 'get_float_status':
        if (!can_manage_petty_cash() && !can_create_vouchers()) {
            json_response(['message' => 'Access denied'], 403);
        }
        
        $sql = "SELECT 
            f.*,
            ba.account_name as linked_bank_name,
            ba.account_number as linked_bank_account_no,
            CASE 
                WHEN f.current_balance <= f.warning_threshold THEN 'CRITICAL'
                WHEN f.current_balance <= (f.float_limit * 0.3) THEN 'LOW'
                ELSE 'NORMAL'
            END as balance_status
            FROM petty_cash_float f
            LEFT JOIN bank_accounts ba ON ba.id = f.linked_bank_account_id
            WHERE f.is_active = 1";
        
        $result = $mysqli->query($sql);
        $floats = [];
        while ($row = $result->fetch_assoc()) {
            // Calculate replenishment needed
            $row['replenishment_needed'] = max(0, $row['float_limit'] - $row['current_balance']);
            $row['utilization_percent'] = $row['float_limit'] > 0 ? round(($row['current_balance'] / $row['float_limit']) * 100, 2) : 0;
            $floats[] = $row;
        }
        
        json_response(['success' => true, 'data' => $floats]);
        break;
        
    case 'setup_float':
    case 'update_float':
        if (!can_manage_petty_cash()) {
            json_response(['message' => 'Access denied'], 403);
        }
        
        verify_csrf();
        
        $floatId = intval($_POST['float_id'] ?? 0);
        $floatName = trim($_POST['float_name'] ?? 'Main Petty Cash');
        $floatLimit = floatval($_POST['float_limit'] ?? 25000);
        $warningThreshold = floatval($_POST['warning_threshold'] ?? 5000);
        $linkedBankAccountId = !empty($_POST['linked_bank_account_id']) ? intval($_POST['linked_bank_account_id']) : null;
        $costCenter = trim($_POST['cost_center'] ?? 'Head Office');
        
        if ($floatLimit <= 0) {
            json_response(['message' => 'Float limit must be greater than 0'], 400);
        }
        
        if ($warningThreshold >= $floatLimit) {
            json_response(['message' => 'Warning threshold must be less than float limit'], 400);
        }
        
        if ($floatId) {
            // Update existing
            $stmt = $mysqli->prepare("UPDATE petty_cash_float SET 
                float_name = ?, float_limit = ?, warning_threshold = ?, 
                linked_bank_account_id = ?, cost_center = ?
                WHERE id = ?");
            $stmt->bind_param('sdiisi', $floatName, $floatLimit, $warningThreshold, $linkedBankAccountId, $costCenter, $floatId);
        } else {
            // Create new
            $stmt = $mysqli->prepare("INSERT INTO petty_cash_float 
                (float_name, float_limit, current_balance, warning_threshold, linked_bank_account_id, cost_center, created_by)
                VALUES (?, ?, 0, ?, ?, ?, ?)");
            $createdBy = $_SESSION['user_id'] ?? null;
            $stmt->bind_param('sdiisi', $floatName, $floatLimit, $warningThreshold, $linkedBankAccountId, $costCenter, $createdBy);
        }
        
        if ($stmt->execute()) {
            $id = $floatId ?: $stmt->insert_id;
            $stmt->close();
            json_response(['success' => true, 'message' => 'Float configuration saved', 'id' => $id]);
        } else {
            $stmt->close();
            json_response(['message' => 'Failed to save float configuration'], 500);
        }
        break;
        
    // ==================== REPLENISHMENT (IMPREST HANDSHAKE) ====================
    
    case 'replenish':
    case 'create_replenishment':
        if (!can_manage_petty_cash()) {
            json_response(['message' => 'Access denied'], 403);
        }
        
        verify_csrf();
        
        $floatId = intval($_POST['float_id'] ?? 0);
        $sourceBankAccountId = intval($_POST['source_bank_account_id'] ?? 0);
        $amount = floatval($_POST['amount'] ?? 0);
        $replenishmentDate = trim($_POST['replenishment_date'] ?? date('Y-m-d'));
        $referenceNo = trim($_POST['reference_no'] ?? '');
        $narration = trim($_POST['narration'] ?? 'Petty cash replenishment');
        
        if (!$floatId || !$sourceBankAccountId || $amount <= 0) {
            json_response(['message' => 'Float ID, source bank account, and valid amount are required'], 400);
        }
        
        // Get float details
        $floatStmt = $mysqli->prepare("SELECT * FROM petty_cash_float WHERE id = ? AND is_active = 1");
        $floatStmt->bind_param('i', $floatId);
        $floatStmt->execute();
        $floatResult = $floatStmt->get_result();
        $float = $floatResult->fetch_assoc();
        $floatStmt->close();
        
        if (!$float) {
            json_response(['message' => 'Petty cash float not found'], 404);
        }
        
        // Check if amount would exceed float limit
        $newBalance = $float['current_balance'] + $amount;
        if ($newBalance > $float['float_limit']) {
            json_response(['message' => 'Replenishment would exceed float limit. Maximum allowed: ' . ($float['float_limit'] - $float['current_balance'])], 400);
        }
        
        // Check bank account balance
        $bankStmt = $mysqli->prepare("SELECT current_balance FROM bank_accounts WHERE id = ? AND is_active = 1");
        $bankStmt->bind_param('i', $sourceBankAccountId);
        $bankStmt->execute();
        $bankResult = $bankStmt->get_result();
        $bankAccount = $bankResult->fetch_assoc();
        $bankStmt->close();
        
        if (!$bankAccount) {
            json_response(['message' => 'Source bank account not found'], 404);
        }
        
        if ($bankAccount['current_balance'] < $amount) {
            json_response(['message' => 'Insufficient balance in source bank account'], 400);
        }
        
        $mysqli->begin_transaction();
        
        try {
            $contraVoucher = generate_contra_voucher($mysqli, $replenishmentDate);
            $performedBy = $_SESSION['user_id'] ?? null;
            
            // 1. Record replenishment
            $replenStmt = $mysqli->prepare("INSERT INTO petty_cash_replenishments 
                (float_id, source_bank_account_id, contra_voucher_number, amount, replenishment_date, reference_no, narration, performed_by)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
            $replenStmt->bind_param('iisdsssi', $floatId, $sourceBankAccountId, $contraVoucher, $amount, $replenishmentDate, $referenceNo, $narration, $performedBy);
            
            if (!$replenStmt->execute()) {
                throw new Exception('Failed to record replenishment');
            }
            $replenId = $replenStmt->insert_id;
            $replenStmt->close();
            
            // 2. Update petty cash float balance (+)
            $updateFloatStmt = $mysqli->prepare("UPDATE petty_cash_float SET current_balance = current_balance + ? WHERE id = ?");
            $updateFloatStmt->bind_param('di', $amount, $floatId);
            if (!$updateFloatStmt->execute()) {
                throw new Exception('Failed to update petty cash balance');
            }
            $updateFloatStmt->close();
            
            // 3. Update bank account balance (-)
            $updateBankStmt = $mysqli->prepare("UPDATE bank_accounts SET current_balance = current_balance - ? WHERE id = ?");
            $updateBankStmt->bind_param('di', $amount, $sourceBankAccountId);
            if (!$updateBankStmt->execute()) {
                throw new Exception('Failed to update bank balance');
            }
            $updateBankStmt->close();
            
            // 4. Create contra entries for cash book (double entry)
            // Generate unique voucher numbers for each side
            $contraVoucherOut = $contraVoucher . '-OUT';
            $contraVoucherIn = $contraVoucher . '-IN';
            
            // 4a. Contra Out - Bank side (money leaving bank)
            $contraOutStmt = $mysqli->prepare("INSERT INTO transactions 
                (voucher_number, transaction_date, transaction_type, account_head, bank_account_id,
                 amount, narration, document_reference, entered_by, approval_status, is_approved, approved_by, approved_at)
                VALUES (?, ?, 'Contra Out', 'Bank Transfer - Petty Cash Replenishment', ?, ?, ?, ?, ?, 'Approved', 1, ?, NOW())");
            $contraOutStmt->bind_param('ssidssii', $contraVoucherOut, $replenishmentDate, $sourceBankAccountId, $amount, $narration, $referenceNo, $performedBy, $performedBy);
            $contraOutStmt->execute();
            $contraOutStmt->close();
            
            // 4b. Contra In - Petty Cash side (money entering petty cash)
            $contraInStmt = $mysqli->prepare("INSERT INTO transactions 
                (voucher_number, transaction_date, transaction_type, account_head, bank_account_id,
                 amount, narration, document_reference, entered_by, approval_status, is_approved, approved_by, approved_at)
                VALUES (?, ?, 'Contra In', 'Petty Cash - Replenishment', NULL, ?, ?, ?, ?, 'Approved', 1, ?, NOW())");
            $contraInStmt->bind_param('ssdssii', $contraVoucherIn, $replenishmentDate, $amount, $narration, $referenceNo, $performedBy, $performedBy);
            $contraInStmt->execute();
            $contraInStmt->close();
            
            $mysqli->commit();
            
            json_response([
                'success' => true,
                'message' => 'Petty cash replenished successfully',
                'replenishment_id' => $replenId,
                'contra_voucher' => $contraVoucher,
                'amount' => $amount,
                'new_float_balance' => $newBalance
            ]);
            
        } catch (Exception $e) {
            $mysqli->rollback();
            json_response(['message' => 'Replenishment failed: ' . $e->getMessage()], 500);
        }
        break;
        
    case 'list_replenishments':
        if (!can_view_petty_cash()) {
            json_response(['message' => 'Access denied'], 403);
        }
        
        $floatId = intval($_GET['float_id'] ?? 0);
        $limit = intval($_GET['limit'] ?? 20);
        
        $sql = "SELECT r.*, 
                b.account_name as source_bank_name, b.bank_name as source_bank,
                u.username as performed_by_name
                FROM petty_cash_replenishments r
                LEFT JOIN bank_accounts b ON b.id = r.source_bank_account_id
                LEFT JOIN users u ON u.id = r.performed_by
                WHERE 1=1";
        
        $params = [];
        $types = '';
        
        if ($floatId) {
            $sql .= " AND r.float_id = ?";
            $params[] = $floatId;
            $types .= 'i';
        }
        
        if (!empty($_GET['date_from'])) {
            $sql .= " AND r.replenishment_date >= ?";
            $params[] = $_GET['date_from'];
            $types .= 's';
        }
        
        if (!empty($_GET['date_to'])) {
            $sql .= " AND r.replenishment_date <= ?";
            $params[] = $_GET['date_to'];
            $types .= 's';
        }
        
        $sql .= " ORDER BY r.replenishment_date DESC, r.id DESC LIMIT ?";
        $params[] = $limit;
        $types .= 'i';
        
        $stmt = $mysqli->prepare($sql);
        if (!empty($params)) {
            $stmt->bind_param($types, ...$params);
        }
        $stmt->execute();
        $result = $stmt->get_result();
        
        $replenishments = [];
        while ($row = $result->fetch_assoc()) {
            $replenishments[] = $row;
        }
        $stmt->close();
        
        json_response(['success' => true, 'data' => $replenishments]);
        break;
        
    // ==================== VOUCHER MANAGEMENT ====================
    
    case 'list_vouchers':
        if (!can_create_vouchers()) {
            json_response(['message' => 'Access denied'], 403);
        }
        
        $where = ['1=1'];
        $params = [];
        $types = '';
        
        if (!empty($_GET['float_id'])) {
            $where[] = 'v.float_id = ?';
            $params[] = intval($_GET['float_id']);
            $types .= 'i';
        }
        
        if (!empty($_GET['status'])) {
            $where[] = 'v.status = ?';
            $params[] = $_GET['status'];
            $types .= 's';
        }
        
        if (!empty($_GET['date_from'])) {
            $where[] = 'v.voucher_date >= ?';
            $params[] = $_GET['date_from'];
            $types .= 's';
        }
        
        if (!empty($_GET['date_to'])) {
            $where[] = 'v.voucher_date <= ?';
            $params[] = $_GET['date_to'];
            $types .= 's';
        }
        
        $sql = "SELECT v.*, 
            f.float_name, f.float_limit, f.current_balance as float_current_balance,
            u.username as created_by_name,
            pu.username as posted_by_name
            FROM petty_cash_vouchers v
            LEFT JOIN petty_cash_float f ON f.id = v.float_id
            LEFT JOIN users u ON u.id = v.created_by
            LEFT JOIN users pu ON pu.id = v.posted_by
            WHERE " . implode(' AND ', $where) . "
            ORDER BY v.voucher_date DESC, v.id DESC";
        
        if ($types) {
            $stmt = $mysqli->prepare($sql);
            $stmt->bind_param($types, ...$params);
            $stmt->execute();
            $result = $stmt->get_result();
        } else {
            $result = $mysqli->query($sql);
        }
        
        $vouchers = [];
        while ($row = $result->fetch_assoc()) {
            $vouchers[] = $row;
        }
        
        json_response(['success' => true, 'data' => $vouchers]);
        break;
        
    case 'get_voucher':
        if (!can_create_vouchers()) {
            json_response(['message' => 'Access denied'], 403);
        }
        
        $id = intval($_GET['id'] ?? 0);
        if (!$id) {
            json_response(['message' => 'Voucher ID required'], 400);
        }
        
        $stmt = $mysqli->prepare("SELECT v.*, 
            f.float_name, f.float_limit, f.current_balance as float_current_balance,
            u.username as created_by_name,
            pu.username as posted_by_name
            FROM petty_cash_vouchers v
            LEFT JOIN petty_cash_float f ON f.id = v.float_id
            LEFT JOIN users u ON u.id = v.created_by
            LEFT JOIN users pu ON pu.id = v.posted_by
            WHERE v.id = ?");
        $stmt->bind_param('i', $id);
        $stmt->execute();
        $result = $stmt->get_result();
        $voucher = $result->fetch_assoc();
        $stmt->close();
        
        if (!$voucher) {
            json_response(['message' => 'Voucher not found'], 404);
        }
        
        json_response(['success' => true, 'data' => $voucher]);
        break;
        
    case 'create_voucher':
        if (!can_create_vouchers()) {
            json_response(['message' => 'Access denied'], 403);
        }
        
        verify_csrf();
        
        $floatId = intval($_POST['float_id'] ?? 0);
        $voucherDate = trim($_POST['voucher_date'] ?? date('Y-m-d'));
        $expenseAccountHead = trim($_POST['expense_account_head'] ?? '');
        $subAccountHead = trim($_POST['sub_account_head'] ?? '');
        $amount = floatval($_POST['amount'] ?? 0);
        $recipientName = trim($_POST['recipient_name'] ?? '');
        $recipientId = trim($_POST['recipient_contact'] ?? '');  // Form sends contact as ID
        $recipientContact = trim($_POST['recipient_contact'] ?? '');
        $narration = trim($_POST['narration'] ?? '');
        $costCenter = trim($_POST['cost_center'] ?? 'Head Office');
        
        // Validation
        if (!$floatId) {
            json_response(['message' => 'Petty cash float is required'], 400);
        }
        
        if (!$expenseAccountHead) {
            json_response(['message' => 'Expense account head is required'], 400);
        }
        
        if ($amount <= 0) {
            json_response(['message' => 'Amount must be greater than 0'], 400);
        }
        
        if (!$recipientName) {
            json_response(['message' => 'Recipient name is required'], 400);
        }
        
        if (!$narration) {
            json_response(['message' => 'Narration is required'], 400);
        }
        
        // Check float balance (BACKEND VALIDATION - No negative cash)
        $floatStmt = $mysqli->prepare("SELECT current_balance, float_limit FROM petty_cash_float WHERE id = ? AND is_active = 1");
        $floatStmt->bind_param('i', $floatId);
        $floatStmt->execute();
        $floatResult = $floatStmt->get_result();
        $float = $floatResult->fetch_assoc();
        $floatStmt->close();
        
        if (!$float) {
            json_response(['message' => 'Petty cash float not found'], 404);
        }
        
        if ($float['current_balance'] < $amount) {
            json_response([
                'success' => false,
                'message' => 'Insufficient petty cash balance',
                'error_type' => 'INSUFFICIENT_BALANCE',
                'current_balance' => $float['current_balance'],
                'requested_amount' => $amount,
                'shortfall' => $amount - $float['current_balance']
            ], 400);
        }
        
        $mysqli->begin_transaction();
        
        try {
            $voucherNumber = generate_pcv_number($mysqli, $voucherDate);
            $createdBy = ($_SESSION['user_id'] ?? null) ?: 1;
            $hasReceipt = !empty($_FILES['receipt_image']) && $_FILES['receipt_image']['error'] === UPLOAD_ERR_OK ? 1 : 0;
            $receiptPath = null;
            
            // Handle receipt upload
            if ($hasReceipt) {
                $uploadDir = dirname(__DIR__) . '/uploads/petty_cash_receipts/';
                if (!is_dir($uploadDir)) {
                    @mkdir($uploadDir, 0755, true);
                }
                
                $fileName = basename($_FILES['receipt_image']['name']);
                $fileName = preg_replace('/[^a-zA-Z0-9._-]/', '_', $fileName);
                // Remove slashes from voucher number for filename
                $safeVoucherNumber = str_replace('/', '_', $voucherNumber);
                $uniqueName = time() . '_' . $safeVoucherNumber . '_' . $fileName;
                $filePath = $uploadDir . $uniqueName;
                
                if (move_uploaded_file($_FILES['receipt_image']['tmp_name'], $filePath)) {
                    $receiptPath = 'uploads/petty_cash_receipts/' . $uniqueName;
                }
            }
            
            // Create voucher
            $amountInWords = amountToWords($amount, 'LKR');
            
            $sql = "INSERT INTO petty_cash_vouchers 
                (float_id, voucher_number, voucher_date, expense_account_head, sub_account_head,
                 amount, amount_in_words, recipient_name, recipient_id, recipient_contact, narration, cost_center,
                 has_receipt, receipt_file_path, status, created_by)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            $stmt = $mysqli->prepare($sql);
            
            $status = 'Pending';
            // 16 parameters: i s s s s d s s s s s s i s s i
            $types = 'issssdssssssissi';
            $stmt->bind_param($types,
                $floatId,
                $voucherNumber,
                $voucherDate,
                $expenseAccountHead,
                $subAccountHead,
                $amount,
                $amountInWords,
                $recipientName,
                $recipientId,
                $recipientContact,
                $narration,
                $costCenter,
                $hasReceipt,
                $receiptPath,
                $status,
                $createdBy
            );
            
            if (!$stmt->execute()) {
                throw new Exception('Failed to create voucher: ' . $stmt->error);
            }
            
            $voucherId = $stmt->insert_id;
            $stmt->close();
            
            // Update float balance immediately for Pending vouchers (optional - can also wait for posting)
            // For strict control, we deduct immediately to prevent overspending
            $updateStmt = $mysqli->prepare("UPDATE petty_cash_float SET current_balance = current_balance - ? WHERE id = ?");
            $updateStmt->bind_param('di', $amount, $floatId);
            if (!$updateStmt->execute()) {
                throw new Exception('Failed to update float balance');
            }
            $updateStmt->close();
            
            $mysqli->commit();
            
            json_response([
                'success' => true,
                'message' => 'Petty cash voucher created successfully',
                'voucher_id' => $voucherId,
                'voucher_number' => $voucherNumber,
                'amount' => $amount,
                'remaining_balance' => $float['current_balance'] - $amount
            ]);
            
        } catch (Exception $e) {
            $mysqli->rollback();
            json_response(['message' => $e->getMessage()], 500);
        }
        break;
        
    case 'post_voucher':
        if (!can_post_vouchers()) {
            json_response(['message' => 'Access denied'], 403);
        }
        
        verify_csrf();
        
        $voucherId = intval($_POST['voucher_id'] ?? 0);
        
        if (!$voucherId) {
            json_response(['message' => 'Voucher ID required'], 400);
        }
        
        $mysqli->begin_transaction();
        
        try {
            // Get voucher details
            $stmt = $mysqli->prepare("SELECT * FROM petty_cash_vouchers WHERE id = ? AND status = 'Pending'");
            $stmt->bind_param('i', $voucherId);
            $stmt->execute();
            $result = $stmt->get_result();
            $voucher = $result->fetch_assoc();
            $stmt->close();
            
            if (!$voucher) {
                throw new Exception('Voucher not found or already posted/cancelled');
            }
            
            $postedBy = $_SESSION['user_id'] ?? null;
            $postedAt = date('Y-m-d H:i:s');
            
            // Update voucher status to Posted
            $updateStmt = $mysqli->prepare("UPDATE petty_cash_vouchers SET 
                status = 'Posted', posted_by = ?, posted_at = ?
                WHERE id = ?");
            $updateStmt->bind_param('isi', $postedBy, $postedAt, $voucherId);
            
            if (!$updateStmt->execute()) {
                throw new Exception('Failed to post voucher');
            }
            $updateStmt->close();
            
            // Create GL entry for posted voucher
            // (This could be expanded to post to actual GL)
            
            $mysqli->commit();
            
            json_response([
                'success' => true,
                'message' => 'Voucher posted successfully',
                'voucher_id' => $voucherId
            ]);
            
        } catch (Exception $e) {
            $mysqli->rollback();
            json_response(['message' => $e->getMessage()], 500);
        }
        break;
        
    case 'cancel_voucher':
        if (!can_create_vouchers()) {
            json_response(['message' => 'Access denied'], 403);
        }
        
        verify_csrf();
        
        $voucherId = intval($_POST['voucher_id'] ?? 0);
        $reason = trim($_POST['reason'] ?? '');
        
        if (!$voucherId) {
            json_response(['message' => 'Voucher ID required'], 400);
        }
        
        $mysqli->begin_transaction();
        
        try {
            // Get voucher details
            $stmt = $mysqli->prepare("SELECT * FROM petty_cash_vouchers WHERE id = ? AND status != 'Cancelled'");
            $stmt->bind_param('i', $voucherId);
            $stmt->execute();
            $result = $stmt->get_result();
            $voucher = $result->fetch_assoc();
            $stmt->close();
            
            if (!$voucher) {
                throw new Exception('Voucher not found or already cancelled');
            }
            
            // If voucher was already deducted from float, restore the balance
            if ($voucher['status'] == 'Pending' || $voucher['status'] == 'Posted') {
                $restoreStmt = $mysqli->prepare("UPDATE petty_cash_float SET current_balance = current_balance + ? WHERE id = ?");
                $restoreStmt->bind_param('di', $voucher['amount'], $voucher['float_id']);
                if (!$restoreStmt->execute()) {
                    throw new Exception('Failed to restore float balance');
                }
                $restoreStmt->close();
            }
            
            // Update voucher status
            $cancelStmt = $mysqli->prepare("UPDATE petty_cash_vouchers SET 
                status = 'Cancelled', narration = CONCAT(narration, ' [Cancelled: ', ?, ']')
                WHERE id = ?");
            $cancelStmt->bind_param('si', $reason, $voucherId);
            
            if (!$cancelStmt->execute()) {
                throw new Exception('Failed to cancel voucher');
            }
            $cancelStmt->close();
            
            $mysqli->commit();
            
            json_response(['success' => true, 'message' => 'Voucher cancelled successfully']);
            
        } catch (Exception $e) {
            $mysqli->rollback();
            json_response(['message' => $e->getMessage()], 500);
        }
        break;
        
    case 'get_reconciliation_data':
        if (!can_post_vouchers()) {
            json_response(['message' => 'Access denied'], 403);
        }
        
        $floatId = intval($_GET['float_id'] ?? 0);
        $dateFrom = trim($_GET['date_from'] ?? date('Y-m-01'));
        $dateTo = trim($_GET['date_to'] ?? date('Y-m-d'));
        
        if (!$floatId) {
            json_response(['message' => 'Float ID required'], 400);
        }
        
        // Get float details
        $floatStmt = $mysqli->prepare("SELECT * FROM petty_cash_float WHERE id = ?");
        $floatStmt->bind_param('i', $floatId);
        $floatStmt->execute();
        $floatResult = $floatStmt->get_result();
        $float = $floatResult->fetch_assoc();
        $floatStmt->close();
        
        if (!$float) {
            json_response(['message' => 'Float not found'], 404);
        }
        
        // Get pending vouchers
        $pendingStmt = $mysqli->prepare("SELECT * FROM petty_cash_vouchers 
            WHERE float_id = ? AND status = 'Pending' 
            AND voucher_date BETWEEN ? AND ?
            ORDER BY voucher_date ASC");
        $pendingStmt->bind_param('iss', $floatId, $dateFrom, $dateTo);
        $pendingStmt->execute();
        $pendingResult = $pendingStmt->get_result();
        $pendingVouchers = [];
        $totalPendingAmount = 0;
        while ($row = $pendingResult->fetch_assoc()) {
            $pendingVouchers[] = $row;
            $totalPendingAmount += $row['amount'];
        }
        $pendingStmt->close();
        
        // Get posted vouchers
        $postedStmt = $mysqli->prepare("SELECT * FROM petty_cash_vouchers 
            WHERE float_id = ? AND status = 'Posted' 
            AND voucher_date BETWEEN ? AND ?
            ORDER BY voucher_date ASC");
        $postedStmt->bind_param('iss', $floatId, $dateFrom, $dateTo);
        $postedStmt->execute();
        $postedResult = $postedStmt->get_result();
        $postedVouchers = [];
        $totalPostedAmount = 0;
        while ($row = $postedResult->fetch_assoc()) {
            $postedVouchers[] = $row;
            $totalPostedAmount += $row['amount'];
        }
        $postedStmt->close();
        
        // Calculate replenishment needed to restore to float limit
        $replenishmentNeeded = max(0, $float['float_limit'] - $float['current_balance']);
        
        json_response([
            'success' => true,
            'data' => [
                'float' => $float,
                'pending_vouchers' => $pendingVouchers,
                'pending_total' => $totalPendingAmount,
                'posted_vouchers' => $postedVouchers,
                'posted_total' => $totalPostedAmount,
                'replenishment_needed' => $replenishmentNeeded,
                'period' => ['from' => $dateFrom, 'to' => $dateTo]
            ]
        ]);
        break;
        
    case 'get_expense_categories':
        if (!can_create_vouchers()) {
            json_response(['message' => 'Access denied'], 403);
        }
        
        $result = $mysqli->query("SELECT * FROM petty_cash_expense_categories WHERE is_active = 1 ORDER BY category_name");
        $categories = [];
        while ($row = $result->fetch_assoc()) {
            $categories[] = $row;
        }
        
        json_response(['success' => true, 'data' => $categories]);
        break;
        
    case 'get_balance_status':
        // Quick endpoint to check if balance is below threshold
        if (!can_create_vouchers()) {
            json_response(['message' => 'Access denied'], 403);
        }
        
        $floatId = intval($_GET['float_id'] ?? 0);
        
        if (!$floatId) {
            json_response(['message' => 'Float ID required'], 400);
        }
        
        $stmt = $mysqli->prepare("SELECT 
            current_balance, 
            warning_threshold, 
            float_limit,
            CASE 
                WHEN current_balance <= warning_threshold THEN 'CRITICAL'
                WHEN current_balance <= (float_limit * 0.3) THEN 'LOW'
                ELSE 'NORMAL'
            END as status
            FROM petty_cash_float WHERE id = ? AND is_active = 1");
        $stmt->bind_param('i', $floatId);
        $stmt->execute();
        $result = $stmt->get_result();
        $status = $result->fetch_assoc();
        $stmt->close();
        
        if (!$status) {
            json_response(['message' => 'Float not found'], 404);
        }
        
        $needsReplenishment = $status['status'] === 'CRITICAL' || $status['status'] === 'LOW';
        
        json_response([
            'success' => true,
            'data' => [
                'current_balance' => $status['current_balance'],
                'warning_threshold' => $status['warning_threshold'],
                'float_limit' => $status['float_limit'],
                'status' => $status['status'],
                'needs_replenishment' => $needsReplenishment,
                'shortfall' => max(0, $status['float_limit'] - $status['current_balance'])
            ]
        ]);
        break;
        
    // ==================== REPLENISHMENT VIA CASH BOOK VOUCHER ====================

    case 'create_replenishment_voucher':
        if (!can_manage_petty_cash()) {
            json_response(['message' => 'Access denied'], 403);
        }
        verify_csrf();

        $floatId           = intval($_POST['float_id'] ?? 0);
        $bankAccountId     = intval($_POST['bank_account_id'] ?? 0);
        $amount            = floatval($_POST['amount'] ?? 0);
        $voucherDate       = trim($_POST['voucher_date'] ?? date('Y-m-d'));
        $referenceNo       = trim($_POST['reference_no'] ?? '');
        $narration         = trim($_POST['narration'] ?? 'Petty Cash Top Up');

        if (!$floatId || !$bankAccountId || $amount <= 0) {
            json_response(['message' => 'Float, bank account and amount are required'], 400);
        }

        // Validate float
        $fs = $mysqli->prepare("SELECT * FROM petty_cash_float WHERE id=? AND is_active=1");
        $fs->bind_param('i', $floatId); $fs->execute();
        $float = $fs->get_result()->fetch_assoc(); $fs->close();
        if (!$float) json_response(['message' => 'Petty cash float not found'], 404);

        if (($float['current_balance'] + $amount) > $float['float_limit']) {
            json_response(['message' => 'Amount would exceed float limit. Max allowed: ' . ($float['float_limit'] - $float['current_balance'])], 400);
        }

        // Validate bank account
        $bs = $mysqli->prepare("SELECT * FROM bank_accounts WHERE id=? AND is_active=1");
        $bs->bind_param('i', $bankAccountId); $bs->execute();
        $bank = $bs->get_result()->fetch_assoc(); $bs->close();
        if (!$bank) json_response(['message' => 'Bank account not found'], 404);

        $mysqli->begin_transaction();
        try {
            // Generate a standard VCH voucher number
            $voucherNumber = 'VCH/' . date('Y', strtotime($voucherDate)) . '/' . date('md', strtotime($voucherDate)) . '/';
            $likeVch = $voucherNumber . '%';
            $vs = $mysqli->prepare("SELECT voucher_number FROM transactions WHERE voucher_number LIKE ? ORDER BY id DESC LIMIT 1");
            $vs->bind_param('s', $likeVch); $vs->execute();
            $vr = $vs->get_result()->fetch_assoc(); $vs->close();
            $nextN = 1;
            if ($vr && preg_match('/\/(\d{4})$/', $vr['voucher_number'], $m)) { $nextN = intval($m[1]) + 1; }
            $voucherNumber .= str_pad($nextN, 4, '0', STR_PAD_LEFT);

            $enteredBy = $_SESSION['user_id'] ?? null;
            $accountHead = 'Petty Cash Top Up';
            $subHead = 'Float: ' . $float['float_name'];
            $approvalStatus = 'Pending';
            $isApproved = 0;

            // Insert into transactions as Payment (debit bank, credit petty cash)
            $ins = $mysqli->prepare("INSERT INTO transactions
                (voucher_number, transaction_date, transaction_type, account_head, sub_account_head,
                 bank_account_id, amount, narration, document_reference,
                 approval_status, is_approved, entered_by)
                VALUES (?,?,'Payment',?,?,?,?,?,?,?,?,?)");
            $ins->bind_param('sssssidsssi',
                $voucherNumber, $voucherDate, $accountHead, $subHead,
                $bankAccountId, $amount, $narration, $referenceNo,
                $approvalStatus, $isApproved, $enteredBy);
            if (!$ins->execute()) throw new Exception('Failed to create cash book voucher: ' . $ins->error);
            $transactionId = $mysqli->insert_id;
            $ins->close();

            // Record in petty_cash_replenishments with Pending status, store transaction id in reference_no
            $contraVoucher = 'PCVCH-' . $voucherNumber;
            $rins = $mysqli->prepare("INSERT INTO petty_cash_replenishments
                (float_id, source_bank_account_id, contra_voucher_number, amount, replenishment_date, reference_no, narration, performed_by, status)
                VALUES (?,?,?,?,?,?,?,?,'Pending')");
            $rins->bind_param('iisdsssi',
                $floatId, $bankAccountId, $contraVoucher, $amount,
                $voucherDate, $referenceNo, $narration, $enteredBy);
            if (!$rins->execute()) throw new Exception('Failed to record replenishment entry');
            $replenId = $mysqli->insert_id;
            $rins->close();

            // Store the cash_book_transaction_id so the approval hook can find this record
            $upd = $mysqli->prepare("UPDATE petty_cash_replenishments SET cash_book_transaction_id=? WHERE id=?");
            $upd->bind_param('ii', $transactionId, $replenId); $upd->execute(); $upd->close();

            $mysqli->commit();
            json_response([
                'success'        => true,
                'message'        => 'Cash book voucher created. Awaiting approval to replenish petty cash.',
                'voucher_number' => $voucherNumber,
                'transaction_id' => $transactionId,
                'replenishment_id' => $replenId
            ]);
        } catch (Exception $e) {
            $mysqli->rollback();
            json_response(['message' => $e->getMessage()], 500);
        }
        break;

    case 'list_replenishment_vouchers':
        if (!can_view_petty_cash()) {
            json_response(['message' => 'Access denied'], 403);
        }
        $floatId = intval($_GET['float_id'] ?? 0);
        $sql = "SELECT r.*, b.account_name AS bank_name, t.approval_status AS voucher_status,
                       t.id AS transaction_id, t.voucher_number AS cash_book_voucher
                FROM petty_cash_replenishments r
                LEFT JOIN bank_accounts b ON b.id = r.source_bank_account_id
                LEFT JOIN transactions t ON t.id = CAST(SUBSTRING_INDEX(REPLACE(r.reference_no,'TXN:',''),CHAR(124),1) AS UNSIGNED)
                WHERE r.status IN ('Pending','Completed')";
        $params = []; $types = '';
        if ($floatId) { $sql .= ' AND r.float_id=?'; $params[] = $floatId; $types .= 'i'; }
        $sql .= ' ORDER BY r.replenishment_date DESC, r.id DESC LIMIT 30';
        if ($params) {
            $st = $mysqli->prepare($sql); $st->bind_param($types, ...$params); $st->execute();
            $res = $st->get_result();
        } else {
            $res = $mysqli->query($sql);
        }
        $rows = [];
        while ($row = $res->fetch_assoc()) { $rows[] = $row; }
        json_response(['success' => true, 'data' => $rows]);
        break;

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