<?php
session_start();
include 'layouts/dbconn.php';
include 'layouts/session.php';

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

// Default to current year
$year = isset($_GET['year']) ? (int) $_GET['year'] : date('Y');

// Fetch monthly revenue
$sql = "SELECT 
            DATE_FORMAT(invoice_date, '%m') as month_num,
            DATE_FORMAT(invoice_date, '%M') as month_name,
            COUNT(*) as invoice_count,
            SUM(total_amount) as total_invoiced,
            SUM(amount_paid) as total_collected,
            SUM(total_amount - amount_paid) as outstanding
        FROM invoices 
        WHERE YEAR(invoice_date) = ?
        GROUP BY month_num
        ORDER BY month_num ASC";
$stmt = $conn->prepare($sql);
$stmt->bind_param("i", $year);
$stmt->execute();
$result = $stmt->get_result();

$months = [];
$invoiced = [];
$collected = [];
$data = [];

while ($row = $result->fetch_assoc()) {
    $data[$row['month_num']] = $row;
}

// Fill missing months
for ($i = 1; $i <= 12; $i++) {
    $m = str_pad($i, 2, '0', STR_PAD_LEFT);
    $dateObj = DateTime::createFromFormat('!m', $m);
    $monthName = $dateObj->format('F');
    $months[] = $monthName;

    if (isset($data[$m])) {
        $invoiced[] = (float) $data[$m]['total_invoiced'];
        $collected[] = (float) $data[$m]['total_collected'];
    } else {
        $invoiced[] = 0;
        $collected[] = 0;
    }
}
?>

<?php include 'layouts/main.php'; ?>

<head>
    <?php includeFileWithVariables('layouts/title-meta.php', array('title' => 'Monthly Financial Reports')); ?>
    <?php include 'layouts/head-css.php'; ?>
    <link href="assets/libs/datatables.net-bs5/css/dataTables.bootstrap5.min.css" rel="stylesheet" />
</head>

<body>
    <div id="layout-wrapper">
        <?php include 'layouts/menu.php'; ?>
        <div class="main-content">
            <div class="page-content">
                <div class="container-fluid">
                    <div class="row">
                        <div class="col-12">
                            <div class="page-title-box d-sm-flex align-items-center justify-content-between">
                                <h4 class="mb-sm-0">Monthly Financial Reports - <?php echo $year; ?></h4>
                                <div class="page-title-right">
                                    <form method="GET" class="d-flex gap-2">
                                        <select name="year" class="form-select form-select-sm"
                                            onchange="this.form.submit()">
                                            <?php for ($y = date('Y'); $y >= 2020; $y--): ?>
                                                <option value="<?php echo $y; ?>" <?php echo ($year == $y) ? 'selected' : ''; ?>><?php echo $y; ?></option>
                                            <?php endfor; ?>
                                        </select>
                                    </form>
                                </div>
                            </div>
                        </div>
                    </div>

                    <div class="row">
                        <div class="col-xl-12">
                            <div class="card">
                                <div class="card-header border-0 align-items-center d-flex">
                                    <h4 class="card-title mb-0 flex-grow-1">Revenue Overview</h4>
                                </div>
                                <div class="card-body">
                                    <div id="revenue-chart" class="apex-charts" dir="ltr"></div>
                                </div>
                            </div>
                        </div>
                    </div>

                    <div class="row">
                        <div class="col-12">
                            <div class="card">
                                <div class="card-body">
                                    <table id="datatable"
                                        class="table table-bordered dt-responsive nowrap table-striped align-middle">
                                        <thead>
                                            <tr>
                                                <th>Month</th>
                                                <th>Invoices</th>
                                                <th>Total Invoiced</th>
                                                <th>Total Collected</th>
                                                <th>Outstanding</th>
                                                <th>Collection Rate</th>
                                            </tr>
                                        </thead>
                                        <tbody>
                                            <?php for ($i = 0; $i < 12; $i++):
                                                $m = str_pad($i + 1, 2, '0', STR_PAD_LEFT);
                                                if (isset($data[$m])):
                                                    $row = $data[$m];
                                                    $rate = ($row['total_invoiced'] > 0) ? ($row['total_collected'] / $row['total_invoiced']) * 100 : 0;
                                                    ?>
                                                    <tr>
                                                        <td><?php echo $months[$i]; ?></td>
                                                        <td><?php echo number_format($row['invoice_count']); ?></td>
                                                        <td><?php echo number_format($row['total_invoiced'], 2); ?></td>
                                                        <td><?php echo number_format($row['total_collected'], 2); ?></td>
                                                        <td><?php echo number_format($row['outstanding'], 2); ?></td>
                                                        <td>
                                                            <div class="progress progress-sm">
                                                                <div class="progress-bar bg-<?php echo ($rate >= 80 ? 'success' : ($rate >= 50 ? 'warning' : 'danger')); ?>"
                                                                    role="progressbar" style="width: <?php echo $rate; ?>%">
                                                                </div>
                                                            </div>
                                                            <small><?php echo number_format($rate, 1); ?>%</small>
                                                        </td>
                                                    </tr>
                                                <?php endif; endfor; ?>
                                        </tbody>
                                        <tfoot>
                                            <tr class="table-light fw-bold">
                                                <td>TOTAL</td>
                                                <td><?php echo number_format(array_sum(array_column($data, 'invoice_count'))); ?>
                                                </td>
                                                <td><?php echo number_format(array_sum($invoiced), 2); ?></td>
                                                <td><?php echo number_format(array_sum($collected), 2); ?></td>
                                                <td><?php echo number_format(array_sum($invoiced) - array_sum($collected), 2); ?>
                                                </td>
                                                <td>-</td>
                                            </tr>
                                        </tfoot>
                                    </table>
                                </div>
                            </div>
                        </div>
                    </div>

                </div>
            </div>
            <?php include 'layouts/footer.php'; ?>
        </div>
    </div>
    <?php include 'layouts/vendor-scripts.php'; ?>
    <script src="assets/libs/apexcharts/apexcharts.min.js"></script>
    <script src="assets/libs/datatables.net/js/jquery.dataTables.min.js"></script>
    <script src="assets/libs/datatables.net-bs5/js/dataTables.bootstrap5.min.js"></script>

    <script>
        var options = {
            series: [{
                name: 'Invoiced',
                data: <?php echo json_encode($invoiced); ?>
            }, {
                name: 'Collected',
                data: <?php echo json_encode($collected); ?>
            }],
            chart: {
                type: 'bar',
                height: 350,
                toolbar: { show: false }
            },
            plotOptions: {
                bar: {
                    horizontal: false,
                    columnWidth: '55%',
                    endingShape: 'rounded'
                },
            },
            dataLabels: { enabled: false },
            stroke: {
                show: true,
                width: 2,
                colors: ['transparent']
            },
            xaxis: {
                categories: <?php echo json_encode($months); ?>,
            },
            yaxis: {
                title: { text: '$ (thousands)' }
            },
            fill: { opacity: 1 },
            colors: ['#3b82f6', '#10b981'],
            tooltip: {
                y: {
                    formatter: function (val) {
                        return "$ " + val.toLocaleString()
                    }
                }
            }
        };

        var chart = new ApexCharts(document.querySelector("#revenue-chart"), options);
        chart.render();

        $('#datatable').DataTable({
            dom: 'Bfrtip',
            buttons: ['copy', 'csv', 'excel', 'pdf', 'print']
        });
    </script>
</body>

</html>