<?php include 'db.php'; ?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Monthly Report</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/css/bootstrap.min.css" rel="stylesheet">
</head>
<body class="bg-light">
<div class="container py-5">
    <div class="card shadow-lg">
        <div class="card-header bg-dark text-white d-flex justify-content-between align-items-center">
            <h4 class="mb-0">📑 Monthly Financial Report</h4>
        </div>
        <div class="card-body">
            <!-- Filter Form -->
            <form method="get" class="row g-3 mb-4">
                <div class="col-md-4">
                    <label class="form-label">Start Date</label>
                    <input type="date" name="start_date" class="form-control" required value="<?= $_GET['start_date'] ?? '' ?>">
                </div>
                <div class="col-md-4">
                    <label class="form-label">End Date</label>
                    <input type="date" name="end_date" class="form-control" required value="<?= $_GET['end_date'] ?? '' ?>">
                </div>
                <div class="col-md-4 d-flex align-items-end">
                    <button class="btn btn-success w-100">Generate Report</button>
                </div>
            </form>

            <?php if(isset($_GET['start_date'], $_GET['end_date'])): 
                $start = $_GET['start_date'];
                $end   = $_GET['end_date'];
            ?>

            <!-- Results -->
            <div id="report-content">
                <h5 class="text-primary">📌 Report from <?= date("d M Y", strtotime($start)) ?> to <?= date("d M Y", strtotime($end)) ?></h5>

                <?php
                $grand_total = 0;

                // 1. Employee Payments
                echo "<h6 class='mt-4 text-success'>Employee Payments</h6>";
                $res = $conn->query("SELECT p.*, e.name FROM payments p 
                                      JOIN employees e ON p.employee_id = e.id 
                                      WHERE p.payment_date BETWEEN '$start' AND '$end'");
                $subtotal = 0;
                echo "<table class='table table-sm table-bordered'><thead><tr>
                        <th>#</th><th>Employee</th><th>Amount</th><th>Date</th><th>Method</th></tr></thead><tbody>";
                $i=1;
                while($row = $res->fetch_assoc()){
                    echo "<tr><td>$i</td><td>{$row['name']}</td><td>".number_format($row['amount'],2)."</td><td>{$row['payment_date']}</td><td>{$row['method']}</td></tr>";
                    $subtotal += $row['amount'];
                    $i++;
                }
                echo "</tbody></table>";
                echo "<p><strong>Subtotal: RM ".number_format($subtotal,2)."</strong></p>";
                $grand_total += $subtotal;

                // 2. Quotations
                echo "<h6 class='mt-4 text-info'>Quotations</h6>";
                $res = $conn->query("SELECT q.id, q.partner_name, q.date_created,
           SUM(qi.quantity * qi.unit_price) AS total
    FROM quotations q
    JOIN quotation_items qi ON q.id = qi.quotation_id
    WHERE q.date_created BETWEEN '$start' AND '$end'
    GROUP BY q.id, q.partner_name, q.date_created");
                $subtotal = 0;
                echo "<table class='table table-sm table-bordered'><thead><tr>
                        <th>#</th><th>Client</th><th>Total</th><th>Date</th></tr></thead><tbody>";
                $i=1;
                while($row = $res->fetch_assoc()){
                    echo "<tr><td>$i</td><td>{$row['partner_name']}</td><td>".number_format($row['total'],2)."</td><td>{$row['date_created']}</td></tr>";
                    $subtotal += $row['total'];
                    $i++;
                }
                echo "</tbody></table>";
                echo "<p><strong>Subtotal: RM ".number_format($subtotal,2)."</strong></p>";
                $grand_total += $subtotal;

                // 3. Invoices
                echo "<h6 class='mt-4 text-warning'>Invoices</h6>";
                $res = $conn->query("SELECT i.id, i.partner_name, i.created_at,
           SUM(ii.quantity * ii.unit_price) AS total
    FROM invoices i
    JOIN invoice_items ii ON i.id = ii.invoice_id
    WHERE i.created_at BETWEEN '$start' AND '$end'
    GROUP BY i.id, i.partner_name, i.created_at");
                $subtotal = 0;
                echo "<table class='table table-sm table-bordered'><thead><tr>
                        <th>#</th><th>Client</th><th>Total</th><th>Date</th></tr></thead><tbody>";
                $i=1;
                while($row = $res->fetch_assoc()){
                    echo "<tr><td>$i</td><td>{$row['partner_name']}</td><td>".number_format($row['total'],2)."</td><td>{$row['created_at']}</td></tr>";
                    $subtotal += $row['total'];
                    $i++;
                }
                echo "</tbody></table>";
                echo "<p><strong>Subtotal: RM ".number_format($subtotal,2)."</strong></p>";
                $grand_total += $subtotal;

                echo "<h4 class='mt-4 text-danger'>💰 Grand Total: RM ".number_format($grand_total,2)."</h4>";
                ?>
            </div>

            <!-- Export PDF Button -->
            <form method="post" action="report_pdf.php" target="_blank">
                <input type="hidden" name="start_date" value="<?= $start ?>">
                <input type="hidden" name="end_date" value="<?= $end ?>">
                <button class="btn btn-outline-primary mt-3">📤 Export to PDF</button>
            </form>

            <!-- Print Log -->
            <hr>
            <h6 class="text-muted">📝 Print Log</h6>
            <ul>
                <?php
                $log = $conn->query("SELECT * FROM print_logs ORDER BY created_at DESC LIMIT 10");
                while($row = $log->fetch_assoc()){
                    echo "<li>{$row['user']} printed a report on ".date("d M Y H:i", strtotime($row['created_at']))." for period {$row['start_date']} - {$row['end_date']}</li>";
                }
                ?>
            </ul>

            <?php endif; ?>
        </div>
    </div>
</div>
</body>
</html>
