<?php
session_start();

if (!isset($_SESSION['loggedin']) || $_SESSION['loggedin'] !== true) {
    header('Location: login.php');
    exit;
}

require_once 'db_connect.php';

$loan_no = $_GET['loan_no'] ?? '';

if (empty($loan_no)) {
    die("Error: Loan Number is required.");
}

// 1. Auto-detect if this is a Standard CA or Interest CA based on prefix
$is_interest = (strpos($loan_no, 'CAI-') === 0);

$main_table = $is_interest ? 'cash_advances_interest' : 'cash_advances';
$sched_table = $is_interest ? 'cash_advance_schedule_interest' : 'cash_advance_schedule';

try {
    // 2. Fetch Main Loan Data & Employee Details
    $sql = "SELECT ca.*, 
                   an.full_name, an.hub, an.bank, an.account_number 
            FROM `$main_table` ca
            LEFT JOIN `account_numbers` an ON ca.rider_ops_id = an.rider_ops_id
            WHERE ca.loan_no = ?";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$loan_no]);
    $loan = $stmt->fetch(PDO::FETCH_ASSOC);

    if (!$loan) {
        die("Error: Loan record not found.");
    }

    // 3. Fetch Schedule & Payment History
    $sql_sched = "SELECT * FROM `$sched_table` WHERE loan_no = ? ORDER BY payment_date ASC";
    $stmt_sched = $pdo->prepare($sql_sched);
    $stmt_sched->execute([$loan_no]);
    $schedules = $stmt_sched->fetchAll(PDO::FETCH_ASSOC);

    // 4. Calculate Totals
    $total_paid = 0;
    foreach ($schedules as $sched) {
        if ($sched['status'] === 'Paid') {
            $total_paid += (float)$sched['amount_paid'];
        }
    }

    $grand_total_loan = (float)$loan['amount'];
    $top_up_amount = (float)$loan['additional_loan_amount'];
    $base_loan = $grand_total_loan - $top_up_amount;
    $remaining_balance = $grand_total_loan - $total_paid;
    
    $display_name = !empty($loan["full_name"]) ? str_replace('?', 'Ñ', $loan["full_name"]) : str_replace('?', 'Ñ', $loan["name"]);

} catch (Exception $e) {
    die("Database Error: " . $e->getMessage());
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>SOA - <?php echo htmlspecialchars($loan_no); ?></title>
    <script src="https://cdn.tailwindcss.com"></script>
    <link href="https://fonts.googleapis.com/css2?family=Roboto:wght@300;400;700&display=swap" rel="stylesheet">
    <style>
        body { font-family: 'Roboto', sans-serif; background: #f3f4f6; }
        .print-container { max-width: 800px; margin: 40px auto; background: white; padding: 40px; box-shadow: 0 10px 15px -3px rgba(0, 0, 0, 0.1); border-radius: 8px; }
        @media print {
            body { background: white; -webkit-print-color-adjust: exact; print-color-adjust: exact; }
            .print-container { margin: 0; padding: 20px; box-shadow: none; border-radius: 0; width: 100%; max-width: 100%; }
            .no-print { display: none !important; }
        }
    </style>
</head>
<body class="text-gray-800">

    <div class="print-container">
        
        <!-- Header -->
        <div class="flex justify-between items-start border-b-2 border-gray-300 pb-6 mb-6">
            <div>
                <!-- Change to your actual logo path -->
                <img src="logo9.png" alt="RGServe Logo" class="h-16 mb-2" onerror="this.style.display='none'">
                <h1 class="text-2xl font-bold text-gray-900 uppercase tracking-wider">Statement of Account</h1>
                <p class="text-sm text-gray-500">Date Generated: <?php echo date('F d, Y h:i A'); ?></p>
            </div>
            <div class="text-right">
                <h2 class="text-xl font-bold text-gray-800"><?php echo htmlspecialchars($loan_no); ?></h2>
                <p class="text-sm font-semibold text-<?php echo $is_interest ? 'blue' : 'green'; ?>-600">
                    <?php echo $is_interest ? 'Interest-Based CA' : 'Standard CA'; ?>
                </p>
                <button onclick="window.print()" class="no-print mt-4 px-4 py-2 bg-gray-800 text-white text-sm font-bold rounded shadow hover:bg-gray-700">
                    Print Document
                </button>
            </div>
        </div>

        <!-- Employee Info -->
        <div class="bg-gray-50 p-4 rounded-lg border border-gray-200 mb-8">
            <h3 class="text-sm font-bold text-gray-500 uppercase mb-3 border-b pb-1">Employee Details</h3>
            <div class="grid grid-cols-2 gap-4 text-sm">
                <div>
                    <p><span class="font-semibold text-gray-600 w-24 inline-block">Name:</span> <strong class="text-lg"><?php echo htmlspecialchars($display_name); ?></strong></p>
                    <p><span class="font-semibold text-gray-600 w-24 inline-block">Rider ID:</span> <?php echo htmlspecialchars($loan['rider_ops_id']); ?></p>
                    <p><span class="font-semibold text-gray-600 w-24 inline-block">Account:</span> <?php echo htmlspecialchars($loan['account']); ?></p>
                </div>
                <div>
                    <p><span class="font-semibold text-gray-600 w-24 inline-block">Hub:</span> <?php echo htmlspecialchars($loan['hub'] ?? 'N/A'); ?></p>
                    <p><span class="font-semibold text-gray-600 w-24 inline-block">Bank:</span> <?php echo htmlspecialchars($loan['bank'] ?? 'N/A'); ?></p>
                    <p><span class="font-semibold text-gray-600 w-24 inline-block">Acct No:</span> <?php echo htmlspecialchars($loan['account_number'] ?? 'N/A'); ?></p>
                </div>
            </div>
        </div>

        <!-- Breakdown & Summary -->
        <div class="flex flex-wrap md:flex-nowrap gap-6 mb-8">
            
            <!-- Left Side: Breakdown -->
            <div class="w-full md:w-1/2 bg-white border-2 border-gray-100 rounded-lg p-4">
                <h3 class="text-sm font-bold text-gray-500 uppercase mb-3 border-b pb-1">Loan Breakdown</h3>
                
                <div class="space-y-2 text-sm">
                    <div class="flex justify-between">
                        <span class="text-gray-600">Base Loan Amount:</span>
                        <span class="font-semibold">₱ <?php echo number_format($base_loan, 2); ?></span>
                    </div>
                    
                    <?php if ($loan['is_topped_up']): ?>
                    <div class="flex justify-between text-blue-600">
                        <span>Additional Top-up:</span>
                        <span class="font-bold">+ ₱ <?php echo number_format($top_up_amount, 2); ?></span>
                    </div>
                    <?php endif; ?>
                    
                    <div class="flex justify-between border-t border-gray-200 pt-2 mt-2">
                        <span class="font-bold text-gray-800">Grand Total Loan:</span>
                        <span class="font-bold text-gray-900 text-lg">₱ <?php echo number_format($grand_total_loan, 2); ?></span>
                    </div>
                </div>
            </div>

            <!-- Right Side: Current Status -->
            <div class="w-full md:w-1/2 bg-white border-2 border-gray-100 rounded-lg p-4">
                <h3 class="text-sm font-bold text-gray-500 uppercase mb-3 border-b pb-1">Account Status</h3>
                
                <div class="space-y-2 text-sm">
                    <div class="flex justify-between text-green-700">
                        <span>Total Amount Paid:</span>
                        <span class="font-bold">- ₱ <?php echo number_format($total_paid, 2); ?></span>
                    </div>
                    
                    <div class="flex justify-between border-t border-gray-200 pt-2 mt-2 bg-red-50 p-2 rounded">
                        <span class="font-bold text-red-700">Remaining Balance:</span>
                        <span class="font-bold text-red-700 text-xl">₱ <?php echo number_format($remaining_balance, 2); ?></span>
                    </div>
                </div>
            </div>
        </div>

        <!-- Payment History Table -->
        <h3 class="text-lg font-bold text-gray-800 mb-3 border-b-2 border-gray-800 pb-1">Payment History</h3>
        <table class="w-full text-left text-sm border-collapse mb-8">
            <thead>
                <tr class="bg-gray-100 border-b border-gray-300">
                    <th class="py-3 px-4 font-semibold text-gray-700 uppercase tracking-wider text-xs">Due Date</th>
                    <th class="py-3 px-4 font-semibold text-gray-700 uppercase tracking-wider text-xs text-right">Amount Due</th>
                    <th class="py-3 px-4 font-semibold text-gray-700 uppercase tracking-wider text-xs text-center">Date Paid</th>
                    <th class="py-3 px-4 font-semibold text-gray-700 uppercase tracking-wider text-xs text-right">Amount Paid</th>
                    <th class="py-3 px-4 font-semibold text-gray-700 uppercase tracking-wider text-xs text-center">Status</th>
                </tr>
            </thead>
            <tbody>
                <?php if (count($schedules) > 0): ?>
                    <?php foreach ($schedules as $sched): ?>
                        <?php 
                            $is_paid = $sched['status'] === 'Paid';
                            $row_bg = $is_paid ? 'bg-green-50/30' : 'bg-white';
                        ?>
                        <tr class="border-b border-gray-100 <?php echo $row_bg; ?>">
                            <td class="py-3 px-4 font-medium text-gray-800"><?php echo htmlspecialchars($sched['payment_date']); ?></td>
                            <td class="py-3 px-4 text-right font-bold text-gray-600">₱ <?php echo number_format((float)$sched['amount_due'], 2); ?></td>
                            <td class="py-3 px-4 text-center text-gray-600"><?php echo $sched['paid_date'] ? htmlspecialchars($sched['paid_date']) : '--'; ?></td>
                            <td class="py-3 px-4 text-right font-bold <?php echo $is_paid ? 'text-green-700' : 'text-gray-400'; ?>">
                                <?php echo $is_paid ? '₱ ' . number_format((float)$sched['amount_paid'], 2) : '--'; ?>
                            </td>
                            <td class="py-3 px-4 text-center">
                                <?php if ($is_paid): ?>
                                    <span class="inline-block px-2 py-1 bg-green-100 text-green-800 text-xs font-bold rounded">PAID</span>
                                <?php else: ?>
                                    <span class="inline-block px-2 py-1 bg-yellow-100 text-yellow-800 text-xs font-bold rounded">PENDING</span>
                                <?php endif; ?>
                            </td>
                        </tr>
                    <?php endforeach; ?>
                <?php else: ?>
                    <tr>
                        <td colspan="5" class="py-4 text-center text-gray-500 italic">No schedule records found.</td>
                    </tr>
                <?php endif; ?>
            </tbody>
        </table>

        <!-- Footer -->
        <div class="text-center text-xs text-gray-400 mt-10 border-t border-gray-200 pt-4">
            <p>This is a system-generated statement. For inquiries or discrepancies, please contact your Hub Coordinator or HR Representative.</p>
        </div>

    </div>

</body>
</html>