<?php
$lifetime = 60 * 60 * 24 * 30; 
session_set_cookie_params($lifetime, '/');
session_start();

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

require_once '../db_connect.php';

// -----------------------------------------------------------------------------
// FILTER LOGIC
// -----------------------------------------------------------------------------
// Check for a specific Date filter, otherwise default to today
$filter_date = $_GET['filter_date'] ?? date('Y-m-d'); 
$filter_type = $_GET['filter_type'] ?? 'all';

// Extract year and month from the specific selected date
$selected_year = date('Y', strtotime($filter_date));
$selected_month_num = date('m', strtotime($filter_date));

// Base Queries for Metrics
// 1. Daily Collection based on the specific selected date
$q_day_std = "SELECT COALESCE(SUM(amount_paid), 0) FROM cash_advance_schedule WHERE paid_date = ? AND status = 'Paid'";
$q_day_int = "SELECT COALESCE(SUM(amount_paid), 0) FROM cash_advance_schedule_interest WHERE paid_date = ? AND status = 'Paid'";

// 2. Monthly Collection based on the month of the selected date
$q_month_std = "SELECT COALESCE(SUM(amount_paid), 0) FROM cash_advance_schedule WHERE MONTH(paid_date) = ? AND YEAR(paid_date) = ? AND status = 'Paid'";
$q_month_int = "SELECT COALESCE(SUM(amount_paid), 0) FROM cash_advance_schedule_interest WHERE MONTH(paid_date) = ? AND YEAR(paid_date) = ? AND status = 'Paid'";

// 3. Expected Monthly Collection based on the month of the selected date
$q_expect_std = "SELECT COALESCE(SUM(amount_due), 0) FROM cash_advance_schedule WHERE MONTH(payment_date) = ? AND YEAR(payment_date) = ? AND status = 'Pending'";
$q_expect_int = "SELECT COALESCE(SUM(amount_due), 0) FROM cash_advance_schedule_interest WHERE MONTH(payment_date) = ? AND YEAR(payment_date) = ? AND status = 'Pending'";

// 4. Remaining Balance (Total outstanding regardless of date)
$q_rem_std = "SELECT COALESCE(SUM(amount_due), 0) FROM cash_advance_schedule WHERE status = 'Pending'";
$q_rem_int = "SELECT COALESCE(SUM(amount_due), 0) FROM cash_advance_schedule_interest WHERE status = 'Pending'";

// Base Queries for Data Table
$sql_table_std = "SELECT ca.loan_no, ca.rider_ops_id, REPLACE(ca.name, '?', 'Ñ') as employee_name, 'Without Interest' as loan_type, 
                  ca.amount as base_loan, ca.additional_loan_amount as topup,
                  (SELECT COALESCE(SUM(amount_paid),0) FROM cash_advance_schedule WHERE loan_no = ca.loan_no AND status='Paid') as total_paid,
                  (SELECT COALESCE(SUM(amount_due),0) FROM cash_advance_schedule WHERE loan_no = ca.loan_no AND status='Pending') as remaining_balance
                  FROM cash_advances ca";

$sql_table_int = "SELECT ca.loan_no, ca.rider_ops_id, REPLACE(ca.name, '?', 'Ñ') as employee_name, 'With Interest' as loan_type, 
                  ca.amount as base_loan, ca.additional_loan_amount as topup,
                  (SELECT COALESCE(SUM(amount_paid),0) FROM cash_advance_schedule_interest WHERE loan_no = ca.loan_no AND status='Paid') as total_paid,
                  (SELECT COALESCE(SUM(amount_due),0) FROM cash_advance_schedule_interest WHERE loan_no = ca.loan_no AND status='Pending') as remaining_balance
                  FROM cash_advances_interest ca";

// Helper function to execute queries with parameters
function fetchMetric($pdo, $query, $params = []) {
    $stmt = $pdo->prepare($query);
    $stmt->execute($params);
    return $stmt->fetchColumn();
}

// Apply Filters
try {
    if ($filter_type === 'standard') {
        $day_collection    = fetchMetric($pdo, $q_day_std, [$filter_date]);
        $month_collection  = fetchMetric($pdo, $q_month_std, [$selected_month_num, $selected_year]);
        $expect_collection = fetchMetric($pdo, $q_expect_std, [$selected_month_num, $selected_year]);
        $total_remaining   = fetchMetric($pdo, $q_rem_std);
        
        $table_data = $pdo->query($sql_table_std . " ORDER BY ca.date_of_voucher DESC")->fetchAll(PDO::FETCH_ASSOC);

    } elseif ($filter_type === 'interest') {
        $day_collection    = fetchMetric($pdo, $q_day_int, [$filter_date]);
        $month_collection  = fetchMetric($pdo, $q_month_int, [$selected_month_num, $selected_year]);
        $expect_collection = fetchMetric($pdo, $q_expect_int, [$selected_month_num, $selected_year]);
        $total_remaining   = fetchMetric($pdo, $q_rem_int);
        
        $table_data = $pdo->query($sql_table_int . " ORDER BY ca.date_of_voucher DESC")->fetchAll(PDO::FETCH_ASSOC);

    } else { // 'all'
        $day_collection    = fetchMetric($pdo, $q_day_std, [$filter_date]) + fetchMetric($pdo, $q_day_int, [$filter_date]);
        $month_collection  = fetchMetric($pdo, $q_month_std, [$selected_month_num, $selected_year]) + fetchMetric($pdo, $q_month_int, [$selected_month_num, $selected_year]);
        $expect_collection = fetchMetric($pdo, $q_expect_std, [$selected_month_num, $selected_year]) + fetchMetric($pdo, $q_expect_int, [$selected_month_num, $selected_year]);
        $total_remaining   = fetchMetric($pdo, $q_rem_std) + fetchMetric($pdo, $q_rem_int);
        
        $sql_combined = "($sql_table_std) UNION ALL ($sql_table_int) ORDER BY loan_no DESC";
        $table_data = $pdo->query($sql_combined)->fetchAll(PDO::FETCH_ASSOC);
    }
} catch (Exception $e) {
    die("Database Error: " . $e->getMessage());
}

// -----------------------------------------------------------------------------
// CSV EXPORT LOGIC
// -----------------------------------------------------------------------------
if (isset($_GET['export']) && $_GET['export'] === 'csv') {
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename=RGServe_Loan_Report_' . date('Ymd') . '.csv');
    
    $output = fopen('php://output', 'w');
    // Write CSV Headers
    fputcsv($output, ['Loan No', 'Rider ID', 'Employee Name', 'Loan Type', 'Base/Main Loan', 'Top-up Amount', 'Total Paid', 'Remaining Balance']);
    
    // Write CSV Rows
    foreach ($table_data as $row) {
        fputcsv($output, [
            $row['loan_no'],
            $row['rider_ops_id'],
            $row['employee_name'],
            $row['loan_type'],
            $row['base_loan'],
            $row['topup'],
            $row['total_paid'],
            $row['remaining_balance']
        ]);
    }
    fclose($output);
    exit;
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>RGServe HRIS - Financial Reports</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">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/5.15.4/css/all.min.css">
    
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap/4.6.0/css/bootstrap.min.css"> 
    
    <style>
        .table-wrapper { overflow-x: auto; max-height: 600px; }
        .table-wrapper::-webkit-scrollbar { height: 10px; width: 10px; background-color: #f1f1f1; border-radius: 6px; }
        .table-wrapper::-webkit-scrollbar-thumb { background-color: #888; border-radius: 6px; }
        thead th { position: sticky; top: 0; z-index: 10; background-color: #f9fafb; }
    </style>
</head>
<body class="bg-gray-100">

    <div class="flex h-screen">
        <aside class="w-64 bg-gray-800 text-white flex-shrink-0 shadow-xl z-20">
            <div class="p-4 border-b border-gray-700 flex justify-center"><img src="../logo9.png" alt="Logo" class="h-15 w-auto" onerror="this.style.display='none'"></div>
            <nav class="p-4 space-y-2">
                <a href="cash_advances.php" class="flex items-center space-x-3 py-2.5 px-4 rounded-lg text-gray-300 hover:bg-gray-700 hover:text-white transition duration-200"><i class="fas fa-money-check-alt w-5 h-5"></i><span>Standard CA</span></a>
                <a href="cash_advances_interest.php" class="flex items-center space-x-3 py-2.5 px-4 rounded-lg text-gray-300 hover:bg-gray-700 hover:text-white transition duration-200"><i class="fas fa-percentage w-5 h-5"></i><span>CA With Interest</span></a>
                <a href="ca_reports.php" class="flex items-center space-x-3 py-2.5 px-4 rounded-lg bg-blue-600 text-white transition duration-200 shadow"><i class="fas fa-chart-pie w-5 h-5"></i><span>Financial Reports</span></a>
            </nav>
        </aside>

        <main class="flex-1 overflow-y-auto p-8 relative">
            
            <div class="flex justify-between items-center mb-8 pb-4 border-b border-gray-200 bg-white p-4 rounded-lg shadow-sm">
                <div>
                    <h1 class="text-3xl font-bold text-gray-900">Financial Reports & Collections</h1>
                    <p class="text-gray-500 text-sm mt-1">Overview of cash advances, top-ups, and payments.</p>
                </div>
                <div class="flex items-center space-x-4">
                    <div class="text-right hidden md:block">
                        <p class="text-sm font-bold text-gray-800"><?php echo htmlspecialchars($_SESSION['full_name'] ?? 'User'); ?></p>
                        <p class="text-xs text-gray-500 uppercase"><?php echo htmlspecialchars($_SESSION['username'] ?? 'Dept'); ?></p>
                    </div>
                    <div class="h-10 w-10 rounded-full bg-blue-600 flex items-center justify-center text-white font-bold text-lg shadow">
                        <i class="fas fa-user"></i>
                    </div>
                    <div class="border-l border-gray-300 h-8 mx-2"></div>
                    <a href="../ca/login.php" class="px-4 py-2 bg-red-50 hover:bg-red-100 text-red-600 text-sm font-bold rounded-lg transition flex items-center">
                        <i class="fas fa-sign-out-alt mr-2"></i> Logout
                    </a>
                </div>
            </div>

            <div class="flex justify-between items-end mb-6 bg-white p-4 rounded-lg shadow border border-gray-200">
                <form method="GET" action="ca_reports.php" class="flex items-end space-x-4 w-full md:w-auto">
                    <div class="mb-0">
                        <label class="block text-sm font-semibold text-gray-700 mb-1">Select Exact Date</label>
                        <input type="date" name="filter_date" value="<?php echo htmlspecialchars($filter_date); ?>" class="form-control border-gray-300 text-gray-700 text-sm focus:ring-blue-500 focus:border-blue-500 block w-48 shadow-sm" onchange="this.form.submit()">
                    </div>

                    <div class="mb-0">
                        <label class="block text-sm font-semibold text-gray-700 mb-1">Filter Loan Type</label>
                        <select name="filter_type" class="form-control border-gray-300 text-gray-700 text-sm focus:ring-blue-500 focus:border-blue-500 block w-64 shadow-sm" onchange="this.form.submit()">
                            <option value="all" <?php echo $filter_type === 'all' ? 'selected' : ''; ?>>Show All Loans</option>
                            <option value="standard" <?php echo $filter_type === 'standard' ? 'selected' : ''; ?>>Without Interest (Standard CA)</option>
                            <option value="interest" <?php echo $filter_type === 'interest' ? 'selected' : ''; ?>>With Interest (CAI)</option>
                        </select>
                    </div>
                </form>
                
                <a href="ca_reports.php?filter_type=<?php echo $filter_type; ?>&filter_date=<?php echo $filter_date; ?>&export=csv" class="px-5 py-2 btn btn-success font-bold shadow transition mt-4 md:mt-0 flex items-center h-full">
                    <i class="fas fa-file-csv mr-2"></i> Export to CSV
                </a>
            </div>

            <div class="grid grid-cols-1 md:grid-cols-4 gap-6 mb-8">
                <div class="bg-white rounded-lg shadow p-6 border-l-4 border-blue-500">
                    <div class="flex items-center">
                        <div class="p-3 rounded-full bg-blue-100 text-blue-600"><i class="fas fa-calendar-day fa-2x"></i></div>
                        <div class="ml-4">
                            <p class="mb-1 text-sm font-semibold text-gray-500 uppercase">Collected (<?php echo date('M d', strtotime($filter_date)); ?>)</p>
                            <h3 class="text-2xl font-bold text-gray-800">₱ <?php echo number_format((float)$day_collection, 2); ?></h3>
                        </div>
                    </div>
                </div>

                <div class="bg-white rounded-lg shadow p-6 border-l-4 border-indigo-500">
                    <div class="flex items-center">
                        <div class="p-3 rounded-full bg-indigo-100 text-indigo-600"><i class="fas fa-wallet fa-2x"></i></div>
                        <div class="ml-4">
                            <p class="mb-1 text-sm font-semibold text-gray-500 uppercase">Collected (<?php echo date('M Y', strtotime($filter_date)); ?>)</p>
                            <h3 class="text-2xl font-bold text-gray-800">₱ <?php echo number_format((float)$month_collection, 2); ?></h3>
                        </div>
                    </div>
                </div>

                <div class="bg-white rounded-lg shadow p-6 border-l-4 border-yellow-500">
                    <div class="flex items-center">
                        <div class="p-3 rounded-full bg-yellow-100 text-yellow-600"><i class="fas fa-clock fa-2x"></i></div>
                        <div class="ml-4">
                            <p class="mb-1 text-sm font-semibold text-gray-500 uppercase">Expected (<?php echo date('M Y', strtotime($filter_date)); ?>)</p>
                            <h3 class="text-2xl font-bold text-gray-800">₱ <?php echo number_format((float)$expect_collection, 2); ?></h3>
                            <p class="text-xs text-gray-400 mt-1">Pending dues for selected month</p>
                        </div>
                    </div>
                </div>

                <div class="bg-white rounded-lg shadow p-6 border-l-4 border-red-500">
                    <div class="flex items-center">
                        <div class="p-3 rounded-full bg-red-100 text-red-600"><i class="fas fa-exclamation-triangle fa-2x"></i></div>
                        <div class="ml-4">
                            <p class="mb-1 text-sm font-semibold text-gray-500 uppercase">Total Remaining Bal.</p>
                            <h3 class="text-2xl font-bold text-red-600">₱ <?php echo number_format((float)$total_remaining, 2); ?></h3>
                        </div>
                    </div>
                </div>
            </div>

            <div class="bg-white shadow rounded-lg border border-gray-200">
                <div class="p-4 border-b border-gray-200 bg-gray-50 rounded-t-lg">
                    <h2 class="text-lg font-bold text-gray-800 mb-0">Loan Summaries <span class="text-sm font-normal text-gray-500">(Filtered: <?php echo ucfirst($filter_type); ?>)</span></h2>
                </div>
                
                <div class="table-wrapper">
                    <table class="min-w-full divide-y divide-gray-200 text-sm text-left mb-0">
                        <thead class="bg-gray-100 text-gray-600 uppercase font-semibold text-xs shadow-sm">
                            <tr>
                                <th class="px-6 py-4 tracking-wider border-0">Loan No.</th>
                                <th class="px-6 py-4 tracking-wider border-0">Employee Name</th>
                                <th class="px-6 py-4 tracking-wider border-0">Type</th>
                                <th class="px-6 py-4 tracking-wider text-right border-0">Base Loan</th>
                                <th class="px-6 py-4 tracking-wider text-right border-0">Top-up</th>
                                <th class="px-6 py-4 tracking-wider text-right border-0">Total Paid</th>
                                <th class="px-6 py-4 tracking-wider text-right text-red-600 border-0">Remaining Bal.</th>
                            </tr>
                        </thead>
                        <tbody class="divide-y divide-gray-200 bg-white text-gray-700">
                            <?php if (count($table_data) > 0): ?>
                                <?php foreach ($table_data as $row): 
                                    $rem = (float)$row['remaining_balance'];
                                    $is_fully_paid = ($rem <= 0);
                                    $row_bg = $is_fully_paid ? 'bg-green-50' : 'hover:bg-gray-50';
                                ?>
                                    <tr class="<?php echo $row_bg; ?> transition">
                                        <td class="px-6 py-3 font-semibold border-0 align-middle"><?php echo htmlspecialchars($row['loan_no']); ?></td>
                                        <td class="px-6 py-3 border-0 align-middle">
                                            <div class="font-bold text-gray-900"><?php echo htmlspecialchars($row['employee_name']); ?></div>
                                            <div class="text-xs text-gray-500"><?php echo htmlspecialchars($row['rider_ops_id']); ?></div>
                                        </td>
                                        <td class="px-6 py-3 border-0 align-middle">
                                            <?php if ($row['loan_type'] === 'With Interest'): ?>
                                                <span class="px-2 py-1 bg-purple-100 text-purple-800 text-xs font-bold rounded">With Interest</span>
                                            <?php else: ?>
                                                <span class="px-2 py-1 bg-gray-200 text-gray-700 text-xs font-bold rounded">Standard</span>
                                            <?php endif; ?>
                                        </td>
                                        <td class="px-6 py-3 text-right border-0 align-middle">₱ <?php echo number_format((float)$row['base_loan'], 2); ?></td>
                                        <td class="px-6 py-3 text-right text-blue-600 font-medium border-0 align-middle">
                                            <?php echo (float)$row['topup'] > 0 ? '+ ₱ ' . number_format((float)$row['topup'], 2) : '--'; ?>
                                        </td>
                                        <td class="px-6 py-3 text-right text-green-700 font-bold border-0 align-middle">₱ <?php echo number_format((float)$row['total_paid'], 2); ?></td>
                                        <td class="px-6 py-3 text-right font-bold border-0 align-middle <?php echo $is_fully_paid ? 'text-gray-400' : 'text-red-600'; ?>">
                                            <?php echo $is_fully_paid ? 'PAID' : '₱ ' . number_format($rem, 2); ?>
                                        </td>
                                    </tr>
                                <?php endforeach; ?>
                            <?php else: ?>
                                <tr>
                                    <td colspan="7" class="px-6 py-8 text-center text-gray-500 italic border-0">No loan records found for this filter.</td>
                                </tr>
                            <?php endif; ?>
                        </tbody>
                    </table>
                </div>
            </div>

        </main>
    </div>

</body>
</html>