<?php
require_once 'config/db.php';
require_once 'includes/auth.php';
require_once 'includes/functions.php';

requireAdminPage();

// Update overdue loans
updateOverdueLoans($conn);

// Get report data
// Fix 8: single grouped query instead of 5 separate getAllLoans calls
$loan_counts = getLoanCountsByStatus($conn);
$count_pending  = intval($loan_counts['pending']['cnt']  ?? 0);
$count_approved = intval($loan_counts['approved']['cnt'] ?? 0);
$count_released = intval($loan_counts['released']['cnt'] ?? 0);
$count_overdue  = intval($loan_counts['overdue']['cnt']  ?? 0);
$count_paid     = intval($loan_counts['paid']['cnt']     ?? 0);
$count_active   = $count_pending + $count_approved + $count_released;

// Still need full rows for the active & overdue tables
$active_loans  = array_merge(getAllLoans($conn, 'approved'), getAllLoans($conn, 'released'));
$pending_loans = getAllLoans($conn, 'pending');
$overdue_loans = getAllLoans($conn, 'overdue');

// Get monthly collections
$year = intval($_GET['year'] ?? date('Y'));
$month = intval($_GET['month'] ?? date('m'));

$monthly_data = getMonthlyCollectionsReport($conn, $year);
$daily_data = getDailyCollectionsReport($conn, date('Y-m-01'), date('Y-m-d'));

// Calculate totals
$total_active_loans = getCount($conn, "SELECT SUM(loan_amount) FROM loans WHERE status IN ('pending', 'approved', 'released')");
$total_active_balance = getCount($conn, "SELECT SUM(balance) FROM loans WHERE status IN ('pending', 'approved', 'released')");
$total_collected = getCount($conn, "SELECT SUM(amount + penalty_paid) FROM payments WHERE MONTH(payment_date) = ? AND YEAR(payment_date) = ?", [$month, $year], "ii");
$total_monthly_target = getCount($conn, "SELECT SUM(installment_amount) FROM loans WHERE status IN ('released','overdue')");
$total_interest_collected = getCount($conn, "SELECT SUM(p.amount) FROM payments p JOIN loans l ON p.loan_id=l.id WHERE p.payment_type='interest_only' AND MONTH(p.payment_date)=? AND YEAR(p.payment_date)=?", [$month, $year], "ii");

include 'includes/header.php';
include 'includes/sidebar.php';
?>

<div class="row mb-4">
    <div class="col-lg-12">
        <h3><i class="fas fa-file-alt"></i> Reports</h3>
    </div>
</div>

<!-- Period Selector -->
<div class="card mb-4">
    <div class="card-body">
        <div class="row align-items-end">
            <div class="col-md-4">
                <label for="reportYear" class="form-label">Year</label>
                <select id="reportYear" class="form-select" onchange="updateReport()">
                    <?php 
                    $current_year = date('Y');
                    for ($y = $current_year - 5; $y <= $current_year; $y++):
                    ?>
                        <option value="<?php echo $y; ?>" <?php echo $y == $year ? 'selected' : ''; ?>>
                            <?php echo $y; ?>
                        </option>
                    <?php endfor; ?>
                </select>
            </div>
            <div class="col-md-4">
                <label for="reportMonth" class="form-label">Month</label>
                <select id="reportMonth" class="form-select" onchange="updateReport()">
                    <?php for ($m = 1; $m <= 12; $m++): ?>
                        <option value="<?php echo $m; ?>" <?php echo $m == $month ? 'selected' : ''; ?>>
                            <?php echo date('F', mktime(0, 0, 0, $m, 1)); ?>
                        </option>
                    <?php endfor; ?>
                </select>
            </div>
            <div class="col-md-4">
                <button class="btn btn-primary w-100" onclick="exportToCSV()">
                    <i class="fas fa-download"></i> Export CSV
                </button>
            </div>
        </div>
    </div>
</div>

<?php $total_interest_collected = getCount($conn, "SELECT COALESCE(SUM(p.amount),0) FROM payments p JOIN loans l ON p.loan_id=l.id WHERE p.payment_type='interest_only' AND MONTH(p.payment_date)=? AND YEAR(p.payment_date)=?", [$month,$year], "ii"); ?>
<!-- Key Metrics -->
<div class="row mb-4">
    <div class="col-md-3">
        <div class="card stat-card info">
            <div class="stat-label">Active Loans</div>
            <div class="stat-number"><?php echo number_format($count_active); ?></div>
            <small>Total Outstanding: ₱ <?php echo number_format($total_active_loans, 2); ?></small>
        </div>
    </div>

    <div class="col-md-3">
        <div class="card stat-card success">
            <div class="stat-label">Paid Loans</div>
            <div class="stat-number"><?php echo number_format($count_paid); ?></div>
            <small>Completed</small>
        </div>
    </div>

    <div class="col-md-3">
        <div class="card stat-card danger">
            <div class="stat-label">Overdue Loans</div>
            <div class="stat-number"><?php echo number_format($count_overdue); ?></div>
            <small>Action Needed</small>
        </div>
    </div>

    <div class="col-md-3">
        <div class="card stat-card warning">
            <div class="stat-label">This Month Collections</div>
            <div class="stat-number">₱ <?php echo number_format($total_collected, 0); ?></div>
            <small><?php echo date('F Y', mktime(0, 0, 0, $month, 1, $year)); ?></small>
        </div>
    </div>
    <!-- Fix 15: interest collected breakdown -->
    <div class="col-md-3" style="margin-top:10px;">
        <div class="card" style="border-left:4px solid #6366f1;">
            <div class="card-body py-2">
                <div class="text-muted" style="font-size:12px;">Interest Collected (This Month)</div>
                <div class="fw-bold fs-5">₱ <?php echo number_format($total_interest_collected ?? 0, 2); ?></div>
            </div>
        </div>
    </div>
    <div class="col-md-3" style="margin-top:10px;">
        <div class="card" style="border-left:4px solid #057a55;">
            <div class="card-body py-2">
                <div class="text-muted" style="font-size:12px;">Monthly Installment Target</div>
                <div class="fw-bold fs-5">₱ <?php echo number_format($total_monthly_target ?? 0, 2); ?></div>
            </div>
        </div>
    </div>
</div>

<!-- Monthly Collections Chart -->
<div class="row mb-4">
    <div class="col-lg-8">
        <div class="card">
            <div class="card-header">
                <h5 class="mb-0">Monthly Collections - <?php echo $year; ?></h5>
            </div>
            <div class="card-body">
                <canvas id="monthlyChart"></canvas>
            </div>
        </div>
    </div>

    <!-- Loan Status Distribution -->
    <div class="col-lg-4">
        <div class="card">
            <div class="card-header">
                <h5 class="mb-0">Loan Status Distribution</h5>
            </div>
            <div class="card-body">
                <canvas id="statusChart"></canvas>
            </div>
        </div>
    </div>
</div>

<!-- Active Loans Report -->
<div class="row mb-4">
    <div class="col-lg-12">
        <div class="card">
            <div class="card-header">
                <h5 class="mb-0">Active Loans</h5>
            </div>
            <div class="card-body p-0">
                <?php if (!empty($active_loans) || !empty($pending_loans)): ?>
                    <div class="table-responsive">
                        <table class="table table-hover mb-0">
                            <thead>
                                <tr>
                                    <th>Borrower</th>
                                    <th>Loan Amount</th>
                                    <th>Balance</th>
                                    <th>Due Date</th>
                                    <th>Status</th>
                                    <th>Payment Progress</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php 
                                $all_active = array_merge($active_loans, $pending_loans);
                                foreach ($all_active as $loan):
                                    $progress = ($loan['loan_amount'] - $loan['balance']) / $loan['loan_amount'] * 100;
                                ?>
                                    <tr>
                                        <td><strong><?php echo htmlspecialchars($loan['first_name'] . ' ' . $loan['last_name']); ?></strong></td>
                                        <td>₱ <?php echo number_format($loan['loan_amount'], 2); ?></td>
                                        <td>₱ <?php echo number_format($loan['balance'], 2); ?></td>
                                        <td><?php echo in_array($loan['status'],['pending','approved']) ? '<span class="text-muted">— (not released)</span>' : date('M d, Y', strtotime($loan['due_date'])); ?></td>
                                        <td>
                                            <?php $bc2 = match($loan['status']) { 'released'=>'bg-primary','approved'=>'bg-info','pending'=>'bg-warning text-dark',default=>'bg-secondary' }; ?>
                                            <span class="badge <?php echo $bc2; ?>">
                                                <?php echo ucfirst($loan['status']); ?>
                                            </span>
                                        </td>
                                        <td>
                                            <div class="progress" style="height: 20px;">
                                                <div class="progress-bar bg-success" role="progressbar" 
                                                     style="width: <?php echo $progress; ?>%">
                                                    <?php echo number_format($progress, 0); ?>%
                                                </div>
                                            </div>
                                        </td>
                                    </tr>
                                <?php endforeach; ?>
                            </tbody>
                        </table>
                    </div>
                <?php else: ?>
                    <div class="empty-state p-4">
                        <i class="fas fa-inbox"></i>
                        <h5>No Active Loans</h5>
                    </div>
                <?php endif; ?>
            </div>
        </div>
    </div>
</div>

<!-- Overdue Loans Report -->
<div class="row">
    <div class="col-lg-12">
        <div class="card">
            <div class="card-header">
                <h5 class="mb-0">Overdue Loans</h5>
            </div>
            <div class="card-body p-0">
                <?php if (!empty($overdue_loans)): ?>
                    <div class="table-responsive">
                        <table class="table table-hover mb-0">
                            <thead>
                                <tr>
                                    <th>Borrower</th>
                                    <th>Loan Amount</th>
                                    <th>Balance</th>
                                    <th>Due Date</th>
                                    <th>Days Overdue</th>
                                    <th>Contact</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php foreach ($overdue_loans as $loan):
                                    $days_overdue = (strtotime(date('Y-m-d')) - strtotime($loan['due_date'])) / (60 * 60 * 24);
                                ?>
                                    <tr>
                                        <td><strong><?php echo htmlspecialchars($loan['first_name'] . ' ' . $loan['last_name']); ?></strong></td>
                                        <td>₱ <?php echo number_format($loan['loan_amount'], 2); ?></td>
                                        <td><strong>₱ <?php echo number_format($loan['balance'], 2); ?></strong></td>
                                        <td><?php echo date('M d, Y', strtotime($loan['due_date'])); ?></td>
                                        <td>
                                            <span class="badge bg-danger"><?php echo intval($days_overdue); ?> days</span>
                                        </td>
                                        <td><?php echo htmlspecialchars($loan['contact']); ?></td>
                                    </tr>
                                <?php endforeach; ?>
                            </tbody>
                        </table>
                    </div>
                <?php else: ?>
                    <div class="empty-state p-4">
                        <i class="fas fa-check-circle"></i>
                        <h5>No Overdue Loans</h5>
                        <p class="text-muted">Great job! All loans are current</p>
                    </div>
                <?php endif; ?>
            </div>
        </div>
    </div>
</div>

<script src="https://cdn.jsdelivr.net/npm/chart.js@3.9.1/dist/chart.min.js"></script>

<script>
// Monthly Collections Chart
const monthlyCtx = document.getElementById('monthlyChart').getContext('2d');
const monthlyChart = new Chart(monthlyCtx, {
    type: 'bar',
    data: {
        labels: [
            <?php 
            foreach ($monthly_data as $data) {
                echo "'" . date('M', mktime(0, 0, 0, $data['month'], 1)) . "',";
            }
            ?>
        ],
        datasets: [{
            label: 'Collections',
            data: [<?php echo implode(',', array_column($monthly_data, 'total')); ?>],
            backgroundColor: '#667eea',
            borderColor: '#667eea',
            borderWidth: 1,
            borderRadius: 4
        }]
    },
    options: {
        responsive: true,
        plugins: {
            legend: {
                display: false
            }
        },
        scales: {
            y: {
                beginAtZero: true,
                ticks: {
                    callback: function(value) {
                        return '₱' + value.toLocaleString();
                    }
                }
            }
        }
    }
});

// Loan Status Chart
const statusCtx = document.getElementById('statusChart').getContext('2d');
const statusChart = new Chart(statusCtx, {
    type: 'doughnut',
    data: {
        labels: ['Pending', 'Approved', 'Released', 'Paid', 'Overdue'],
        datasets: [{
            data: [
                <?php echo $count_pending; ?>,
                <?php echo $count_approved; ?>,
                <?php echo $count_released; ?>,
                <?php echo $count_paid; ?>,
                <?php echo $count_overdue; ?>
            ],
            backgroundColor: [
                '#ffc107',
                '#17a2b8',
                '#1a56db',
                '#28a745',
                '#dc3545'
            ],
            borderColor: '#fff',
            borderWidth: 2
        }]
    },
    options: {
        responsive: true,
        plugins: {
            legend: {
                position: 'bottom'
            }
        }
    }
});

function updateReport() {
    const year = document.getElementById('reportYear').value;
    const month = document.getElementById('reportMonth').value;
    window.location.href = `/cash_lending/reports.php?year=${year}&month=${month}`;
}

function exportToCSV() {
    const year = document.getElementById('reportYear').value;
    const month = document.getElementById('reportMonth').value;
    window.location.href = `/cash_lending/api/export-report.php?year=${year}&month=${month}`;
}
</script>

<?php include 'includes/footer.php'; ?>
