<?php
/**
 * Bank Reconciliation API
 * Handles bank statement upload, auto-matching, and reconciliation processing
 */

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 = $_POST['action'] ?? $_GET['action'] ?? 'process';

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

// Helper: Generate reconciliation reference
function generate_recon_ref() {
    return 'RECON-' . date('Ymd') . '-' . strtoupper(substr(uniqid(), -4));
}

// Auto-match algorithm
function auto_match_transactions($mysqli, $stmtLines, $bankAccountId, $dateFrom, $dateTo) {
    // Get uncleared system transactions for this bank account in date range
    $stmt = $mysqli->prepare("SELECT 
            t.id, t.voucher_number, t.transaction_date, t.amount, 
            t.document_reference, t.narration, t.transaction_type,
            t.cheque_number, t.mode_of_payment
        FROM transactions t
        WHERE t.bank_account_id = ?
        AND t.transaction_date BETWEEN ? AND ?
        AND t.is_reconciled = 0
        AND t.is_cancelled = 0
        AND t.approval_status = 'Approved'
        ORDER BY t.transaction_date ASC");
    
    $stmt->bind_param('iss', $bankAccountId, $dateFrom, $dateTo);
    $stmt->execute();
    $result = $stmt->get_result();
    
    $systemTxns = [];
    while ($row = $result->fetch_assoc()) {
        $systemTxns[] = $row;
    }
    $stmt->close();
    
    $matched = [];
    $unmatched = [];
    $matchedSystemIds = [];
    
    // Process each statement line
    foreach ($stmtLines as $line) {
        $line['amount'] = floatval($line['amount']);
        $found = false;
        
        foreach ($systemTxns as $idx => $txn) {
            // Skip already matched
            if (in_array($txn['id'], $matchedSystemIds)) continue;
            
            $txn['amount'] = floatval($txn['amount']);
            
            // Determine direction based on transaction type
            $txnDirection = 1; // Credit/Receipt
            if ($txn['transaction_type'] === 'Payment') {
                $txnDirection = -1; // Debit/Payment
            }
            
            // Statement amount: positive = credit (deposit), negative = debit (withdrawal)
            $stmtDirection = $line['amount'] >= 0 ? 1 : -1;
            
            // Check date match (within 3 days tolerance)
            $dateMatch = false;
            $stmtDate = strtotime($line['date']);
            $txnDate = strtotime($txn['transaction_date']);
            $dateDiff = abs($stmtDate - $txnDate) / (60 * 60 * 24); // Days difference
            if ($dateDiff <= 3) {
                $dateMatch = true;
            }
            
            // Check amount match (exact or with tolerance)
            $amountMatch = false;
            $stmtAmount = abs($line['amount']);
            $txnAmount = abs($txn['amount']);
            $amountTolerance = 0.01; // 1 cent tolerance
            if (abs($stmtAmount - $txnAmount) <= $amountTolerance) {
                $amountMatch = true;
            }
            
            // Check reference/cheque number match
            $refMatch = false;
            $stmtRef = strtoupper(trim($line['reference'] ?? ''));
            $txnRefs = [
                strtoupper(trim($txn['document_reference'] ?? '')),
                strtoupper(trim($txn['cheque_number'] ?? '')),
                strtoupper(trim($txn['voucher_number'] ?? ''))
            ];
            
            // Remove empty references
            $txnRefs = array_filter($txnRefs);
            
            if (!empty($stmtRef) && !empty($txnRefs)) {
                foreach ($txnRefs as $txnRef) {
                    if (strpos($txnRef, $stmtRef) !== false || 
                        strpos($stmtRef, $txnRef) !== false ||
                        similar_text($stmtRef, $txnRef) / max(strlen($stmtRef), strlen($txnRef)) > 0.8) {
                        $refMatch = true;
                        break;
                    }
                }
            }
            
            // Determine if it's a match
            // Priority: Reference match + Amount match > Date match + Amount match > Strong reference similarity
            $isMatch = false;
            $matchScore = 0;
            
            if ($amountMatch) {
                if ($refMatch) {
                    $isMatch = true;
                    $matchScore = 100; // Perfect match
                } elseif ($dateMatch) {
                    $isMatch = true;
                    $matchScore = 80; // Good match
                }
            } elseif ($refMatch && $dateMatch && abs($stmtAmount - $txnAmount) <= 1.00) {
                // Amount within 1.00 tolerance with ref and date match
                $isMatch = true;
                $matchScore = 70;
            }
            
            if ($isMatch) {
                $matched[] = [
                    'statement_line_id' => $line['line_no'],
                    'date' => $line['date'],
                    'reference' => $line['reference'],
                    'description' => $line['description'],
                    'amount' => $line['amount'],
                    'debit' => $line['debit'],
                    'credit' => $line['credit'],
                    'transaction_id' => $txn['id'],
                    'voucher_number' => $txn['voucher_number'],
                    'transaction_date' => $txn['transaction_date'],
                    'match_score' => $matchScore
                ];
                
                $matchedSystemIds[] = $txn['id'];
                $found = true;
                break;
            }
        }
        
        if (!$found) {
            $unmatched[] = [
                'id' => $line['line_no'],
                'source' => 'statement',
                'date' => $line['date'],
                'reference' => $line['reference'],
                'description' => $line['description'],
                'amount' => $line['amount'],
                'debit' => $line['debit'],
                'credit' => $line['credit']
            ];
        }
    }
    
    // Add unmatched system transactions
    foreach ($systemTxns as $txn) {
        if (!in_array($txn['id'], $matchedSystemIds)) {
            $unmatched[] = [
                'id' => 'SYS-' . $txn['id'],
                'source' => 'system',
                'date' => $txn['transaction_date'],
                'reference' => $txn['document_reference'] ?: $txn['cheque_number'] ?: $txn['voucher_number'],
                'description' => $txn['narration'],
                'amount' => $txn['transaction_type'] === 'Payment' ? -$txn['amount'] : $txn['amount'],
                'transaction_id' => $txn['id'],
                'voucher_number' => $txn['voucher_number']
            ];
        }
    }
    
    return [
        'matched' => $matched,
        'unmatched' => $unmatched,
        'total_matched' => count($matched),
        'total_unmatched' => count($unmatched),
        'system_transactions_count' => count($systemTxns)
    ];
}

// API Actions
switch ($action) {
    case 'process_reconciliation':
        if (!can_reconcile()) {
            json_response(['message' => 'Access denied'], 403);
        }
        
        verify_csrf();
        
        $bankAccountId = intval($_POST['bank_account_id'] ?? 0);
        $dateFrom = trim($_POST['date_from'] ?? '');
        $dateTo = trim($_POST['date_to'] ?? '');
        $statementData = json_decode($_POST['statement_data'] ?? '[]', true);
        $totalCredits = floatval($_POST['total_credits'] ?? 0);
        $totalDebits = floatval($_POST['total_debits'] ?? 0);
        
        if (!$bankAccountId || !$dateFrom || !$dateTo) {
            json_response(['message' => 'Bank account and date range are required'], 400);
        }
        
        if (empty($statementData)) {
            json_response(['message' => 'No statement data provided'], 400);
        }
        
        // Get bank account balance
        $bankStmt = $mysqli->prepare("SELECT current_balance, account_name FROM bank_accounts WHERE id = ?");
        $bankStmt->bind_param('i', $bankAccountId);
        $bankStmt->execute();
        $bankResult = $bankStmt->get_result();
        $bankAccount = $bankResult->fetch_assoc();
        $bankStmt->close();
        
        if (!$bankAccount) {
            json_response(['message' => 'Bank account not found'], 404);
        }
        
        // Run auto-matching algorithm
        $matchResult = auto_match_transactions($mysqli, $statementData, $bankAccountId, $dateFrom, $dateTo);
        
        // Calculate statement balance
        $statementBalance = $totalCredits - $totalDebits;
        
        // Calculate uncleared amounts
        $unclearedDr = 0;
        $unclearedCr = 0;
        foreach ($matchResult['unmatched'] as $item) {
            if ($item['source'] === 'system') {
                if ($item['amount'] < 0) {
                    $unclearedDr += abs($item['amount']);
                } else {
                    $unclearedCr += abs($item['amount']);
                }
            }
        }
        
        $systemBalance = floatval($bankAccount['current_balance']);
        $adjustedBalance = $systemBalance - $unclearedDr + $unclearedCr;
        
        json_response([
            'success' => true,
            'data' => [
                'matched' => $matchResult['matched'],
                'unmatched' => $matchResult['unmatched'],
                'bank_account_id' => $bankAccountId,
                'bank_account_name' => $bankAccount['account_name'],
                'system_balance' => $systemBalance,
                'statement_balance' => $statementBalance,
                'statement_opening' => 0, // Would be calculated from previous reconciliation
                'total_credits' => $totalCredits,
                'total_debits' => $totalDebits,
                'uncleared_dr' => $unclearedDr,
                'uncleared_cr' => $unclearedCr,
                'adjusted_balance' => $adjustedBalance,
                'date_from' => $dateFrom,
                'date_to' => $dateTo,
                'generated_at' => date('Y-m-d H:i:s')
            ]
        ]);
        break;
        
    case 'confirm_reconciliation':
        if (!can_reconcile()) {
            json_response(['message' => 'Access denied'], 403);
        }
        
        verify_csrf();
        
        $matches = json_decode($_POST['matches'] ?? '[]', true);
        
        if (empty($matches)) {
            json_response(['message' => 'No matches to confirm'], 400);
        }
        
        $mysqli->begin_transaction();
        
        try {
            $clearedCount = 0;
            $reconciledBy = $_SESSION['user_id'] ?? null;
            $reconciledDate = date('Y-m-d');
            
            $updateStmt = $mysqli->prepare("UPDATE transactions SET 
                is_reconciled = 1, reconciled_date = ?, reconciled_by = ?
                WHERE id = ? AND is_reconciled = 0");
            
            foreach ($matches as $match) {
                if (!empty($match['transaction_id'])) {
                    $txnId = intval($match['transaction_id']);
                    $updateStmt->bind_param('sii', $reconciledDate, $reconciledBy, $txnId);
                    
                    if ($updateStmt->execute() && $updateStmt->affected_rows > 0) {
                        $clearedCount++;
                    }
                }
            }
            
            $updateStmt->close();
            
            $mysqli->commit();
            
            json_response([
                'success' => true,
                'message' => "$clearedCount transactions marked as Cleared",
                'cleared_count' => $clearedCount
            ]);
            
        } catch (Exception $e) {
            $mysqli->rollback();
            json_response(['message' => 'Failed to confirm: ' . $e->getMessage()], 500);
        }
        break;
        
    case 'manual_match':
        if (!can_reconcile()) {
            json_response(['message' => 'Access denied'], 403);
        }
        
        verify_csrf();
        
        $stmtId = trim($_POST['statement_id'] ?? '');
        $transactionId = intval($_POST['transaction_id'] ?? 0);
        
        if (!$transactionId) {
            json_response(['message' => 'Transaction ID required'], 400);
        }
        
        $mysqli->begin_transaction();
        
        try {
            $reconciledBy = $_SESSION['user_id'] ?? null;
            $reconciledDate = date('Y-m-d');
            
            // Mark transaction as reconciled
            $updateStmt = $mysqli->prepare("UPDATE transactions SET 
                is_reconciled = 1, reconciled_date = ?, reconciled_by = ?
                WHERE id = ? AND is_reconciled = 0");
            $updateStmt->bind_param('sii', $reconciledDate, $reconciledBy, $transactionId);
            
            if (!$updateStmt->execute()) {
                throw new Exception('Failed to mark as reconciled');
            }
            $updateStmt->close();
            
            // Get transaction details
            $txnStmt = $mysqli->prepare("SELECT voucher_number, transaction_date, amount FROM transactions WHERE id = ?");
            $txnStmt->bind_param('i', $transactionId);
            $txnStmt->execute();
            $txnResult = $txnStmt->get_result();
            $txn = $txnResult->fetch_assoc();
            $txnStmt->close();
            
            $mysqli->commit();
            
            json_response([
                'success' => true,
                'message' => 'Transaction matched and cleared',
                'data' => [
                    'transaction_id' => $transactionId,
                    'voucher_number' => $txn['voucher_number'],
                    'date' => $txn['transaction_date'],
                    'amount' => $txn['amount'],
                    'cleared_at' => date('Y-m-d H:i:s')
                ]
            ]);
            
        } catch (Exception $e) {
            $mysqli->rollback();
            json_response(['message' => 'Failed to match: ' . $e->getMessage()], 500);
        }
        break;
        
    case 'manual_clear':
        if (!can_reconcile()) {
            json_response(['message' => 'Access denied'], 403);
        }
        
        verify_csrf();
        
        $transactionId = intval($_POST['transaction_id'] ?? 0);
        $notes = trim($_POST['notes'] ?? 'Manual clear without statement match');
        
        if (!$transactionId) {
            json_response(['message' => 'Transaction ID required'], 400);
        }
        
        $reconciledBy = $_SESSION['user_id'] ?? null;
        $reconciledDate = date('Y-m-d');
        
        $stmt = $mysqli->prepare("UPDATE transactions SET 
            is_reconciled = 1, reconciled_date = ?, reconciled_by = ?, notes = CONCAT(IFNULL(notes,''), ' [Manual Clear: ', ?, ']')
            WHERE id = ? AND is_reconciled = 0");
        $stmt->bind_param('sisi', $reconciledDate, $reconciledBy, $notes, $transactionId);
        
        if ($stmt->execute() && $stmt->affected_rows > 0) {
            $stmt->close();
            json_response([
                'success' => true,
                'message' => 'Transaction marked as manually cleared'
            ]);
        } else {
            $stmt->close();
            json_response(['message' => 'Transaction not found or already cleared'], 400);
        }
        break;
        
    case 'get_reconciliation_history':
        if (!can_reconcile()) {
            json_response(['message' => 'Access denied'], 403);
        }
        
        $bankAccountId = intval($_GET['bank_account_id'] ?? 0);
        $dateFrom = trim($_GET['date_from'] ?? '');
        $dateTo = trim($_GET['date_to'] ?? '');
        
        $where = ['1=1'];
        $params = [];
        $types = '';
        
        if ($bankAccountId) {
            $where[] = 'bank_account_id = ?';
            $params[] = $bankAccountId;
            $types .= 'i';
        }
        
        if ($dateFrom && $dateTo) {
            $where[] = 'reconciled_date BETWEEN ? AND ?';
            $params[] = $dateFrom;
            $params[] = $dateTo;
            $types .= 'ss';
        }
        
        $sql = "SELECT 
                t.id, t.voucher_number, t.transaction_date, t.amount, 
                t.document_reference, t.narration, t.reconciled_date,
                t.transaction_type,
                ba.account_name as bank_account_name,
                u.username as reconciled_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.reconciled_by
            WHERE t.is_reconciled = 1";
        
        if (!empty($where) && $where[0] !== '1=1') {
            $sql .= " AND " . implode(' AND ', $where);
        }
        
        $sql .= " ORDER BY t.reconciled_date DESC, t.id DESC LIMIT 100";
        
        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;
        
    default:
        json_response(['message' => 'Unknown action: ' . $action], 400);
}

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