<?php
include 'db.php';

// Get filters (simple sanitization for IDs)
$project_id = isset($_GET['project_id']) && $_GET['project_id'] !== '' ? intval($_GET['project_id']) : '';
$expense_type_id = isset($_GET['expense_type_id']) && $_GET['expense_type_id'] !== '' ? intval($_GET['expense_type_id']) : '';
$start_date = $_GET['start_date'] ?? '';
$end_date = $_GET['end_date'] ?? '';
$export = $_GET['export'] ?? ''; // pdf | excel

// Build WHERE
$where = [];
if ($project_id !== '') $where[] = "e.project_id = {$project_id}";
if ($expense_type_id !== '') $where[] = "e.type_id = {$expense_type_id}";
if ($start_date && $end_date) $where[] = "e.expense_date BETWEEN '{$start_date}' AND '{$end_date}'";
$where_sql = $where ? "WHERE " . implode(" AND ", $where) : "";

// Main query (column names corrected to match your schema)
$sql = "
SELECT 
    p.project_name AS project,
    et.type_name AS expense_type,
    SUM(e.amount) AS total_expenses,
    COALESCE(t.target_amount,0) AS target_amount,
    (COALESCE(t.target_amount,0) - SUM(e.amount)) AS variance
FROM expenses e
JOIN projects p ON e.project_id = p.id
JOIN expense_types et ON e.type_id = et.id
LEFT JOIN expense_targets t 
    ON t.expense_type_id = e.type_id
   AND t.month = YEAR(e.expense_date)
   AND t.month_num = MONTH(e.expense_date)
{$where_sql}
GROUP BY p.id, p.project_name, et.id, et.type_name, t.target_amount
ORDER BY p.project_name, et.type_name
";

$result = $conn->query($sql);
if ($result === false) {
    die("Query error: " . $conn->error);
}

// Handle Export: Excel (CSV)
if ($export === "excel") {
    header("Content-Type: text/csv");
    header("Content-Disposition: attachment; filename=expense_report.csv");
    $out = fopen("php://output", "w");
    fputcsv($out, ["Project", "Expense Type", "Total Expenses", "Target", "Variance"]);
    // use a fresh result set in case of streaming; re-run query
    $res2 = $conn->query($sql);
    while ($row = $res2->fetch_assoc()) {
        fputcsv($out, [
            $row['project'],
            $row['expense_type'],
            number_format($row['total_expenses'], 2, '.', ''),
            number_format($row['target_amount'], 2, '.', ''),
            number_format($row['variance'], 2, '.', '')
        ]);
    }
    fclose($out);
    exit;
}

// Handle Export: PDF
if ($export === "pdf") {
    require("fpdf/fpdf.php"); // ensure FPDF is available
    $pdf = new FPDF('L','mm','A4');
    $pdf->AddPage();
    $pdf->SetFont('Arial','B',14);
    $pdf->Cell(0,10,'Expense Report',0,1,'C');

    // header
    $pdf->SetFont('Arial','B',10);
    $pdf->Cell(70,8,'Project',1);
    $pdf->Cell(70,8,'Expense Type',1);
    $pdf->Cell(40,8,'Total Expenses',1);
    $pdf->Cell(40,8,'Target',1);
    $pdf->Cell(40,8,'Variance',1);
    $pdf->Ln();

    $pdf->SetFont('Arial','',10);
    $res2 = $conn->query($sql);
    while ($row = $res2->fetch_assoc()) {
        $pdf->Cell(70,8,substr($row['project'],0,40),1);
        $pdf->Cell(70,8,substr($row['expense_type'],0,40),1);
        $pdf->Cell(40,8,"RM ".number_format($row['total_expenses'],2),1);
        $pdf->Cell(40,8,"RM ".number_format($row['target_amount'],2),1);
        $variance = "RM ".number_format($row['variance'],2);
        $pdf->Cell(40,8,$variance,1);
        $pdf->Ln();
    }

    $pdf->Output();
    exit;
}

// Fetch filters for dropdowns
$projects = $conn->query("SELECT id, project_name FROM projects ORDER BY project_name");
$expense_types = $conn->query("SELECT id, type_name FROM expense_types ORDER BY type_name");
?>
<!DOCTYPE html>
<html>
<head>
    <title>Expense Report for Vsonic</title>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css">
    <style>.negative { color: red; font-weight: bold; }</style>
</head>
<body class="container py-4">
    <h2 class="mb-4">Expense Report for Vsonic Engineering</h2>

    <!-- Filters -->
    <form method="GET" class="row g-3 mb-3">
        <div class="col-md-3">
            <label>Project</label>
            <select name="project_id" class="form-control">
                <option value="">All</option>
                <?php while ($p = $projects->fetch_assoc()): ?>
                    <option value="<?= $p['id'] ?>" <?= ($project_id== $p['id'])?'selected':'' ?>>
                        <?= htmlspecialchars($p['project_name']) ?>
                    </option>
                <?php endwhile; ?>
            </select>
        </div>

        <div class="col-md-3">
            <label>Expense Type</label>
            <select name="expense_type_id" class="form-control">
                <option value="">All</option>
                <?php while ($et = $expense_types->fetch_assoc()): ?>
                    <option value="<?= $et['id'] ?>" <?= ($expense_type_id== $et['id'])?'selected':'' ?>>
                        <?= htmlspecialchars($et['type_name']) ?>
                    </option>
                <?php endwhile; ?>
            </select>
        </div>

        <div class="col-md-2">
            <label>Start Date</label>
            <input type="date" name="start_date" value="<?= htmlspecialchars($start_date) ?>" class="form-control">
        </div>

        <div class="col-md-2">
            <label>End Date</label>
            <input type="date" name="end_date" value="<?= htmlspecialchars($end_date) ?>" class="form-control">
        </div>

        <div class="col-md-2 align-self-end">
            <button type="submit" class="btn btn-primary w-100">Filter</button>
        </div>
    </form>

    <!-- Export Buttons -->
    <div class="mb-3">
        <?php
        // keep the same filter params in export links
        $qs = http_build_query([
            'project_id' => $project_id !== '' ? $project_id : '',
            'expense_type_id' => $expense_type_id !== '' ? $expense_type_id : '',
            'start_date' => $start_date,
            'end_date' => $end_date
        ]);
        ?>
        <a href="?<?= $qs ?>&export=pdf" class="btn btn-danger">⬇ Download PDF</a>
        <a href="?<?= $qs ?>&export=excel" class="btn btn-success">⬇ Export Excel</a>
    </div>

    <!-- Report Table -->
    <table class="table table-bordered">
        <thead class="table-dark">
            <tr>
                <th>Project</th>
                <th>Expense Type</th>
                <th>Total Expenses</th>
                <th>Target</th>
                <th>Variance</th>
            </tr>
        </thead>
        <tbody>
            <?php if ($result && $result->num_rows > 0): ?>
                <?php while ($row = $result->fetch_assoc()): ?>
                    <tr>
                        <td><?= htmlspecialchars($row['project']) ?></td>
                        <td><?= htmlspecialchars($row['expense_type']) ?></td>
                        <td>RM <?= number_format($row['total_expenses'],2) ?></td>
                        <td>RM <?= number_format($row['target_amount'],2) ?></td>
                        <td class="<?= ($row['variance'] < 0)?'negative':'' ?>">
                            RM <?= number_format($row['variance'],2) ?>
                        </td>
                    </tr>
                <?php endwhile; ?>
            <?php else: ?>
                <tr><td colspan="5" class="text-center">No records found</td></tr>
            <?php endif; ?>
        </tbody>
    </table>
</body>
</html>
