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

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

$allowed_roles = ['SUPER_ADMIN', 'HOSPITAL_ADMIN', 'FINANCE_MANAGER'];
if (!in_array($_SESSION['role_code'], $allowed_roles)) {
    header('Location: dashboard.php');
    exit();
}

$user_id = $_SESSION['user_id'];

// Filters
$category_filter = $_GET['category'] ?? '';
$date_from = $_GET['date_from'] ?? date('Y-m-01');
$date_to = $_GET['date_to'] ?? date('Y-m-d');
$report_type = $_GET['report_type'] ?? 'summary'; // summary, detailed, pdf

// Build report query (sum expenses from journal_entry_lines joined to chart_of_accounts)
$report_sql = "SELECT ca.account_name, ca.account_category, SUM(jel.debit_amount) as total_expense,
                      COUNT(DISTINCT je.journal_id) as transaction_count
               FROM journal_entry_lines jel
               JOIN chart_of_accounts ca ON jel.account_id = ca.account_id
               JOIN journal_entries je ON jel.journal_id = je.journal_id
               WHERE ca.account_type = 'EXPENSE' AND je.entry_date BETWEEN ? AND ?";

$params = [$date_from, $date_to];
$types = 'ss';

if (!empty($category_filter)) {
    $report_sql .= " AND ca.account_category = ?";
    $params[] = $category_filter;
    $types .= 's';
}

$report_sql .= " GROUP BY ca.account_id ORDER BY total_expense DESC";

$stmt = $conn->prepare($report_sql);
$stmt->bind_param($types, ...$params);
$stmt->execute();
$report_result = $stmt->get_result();

// Handle PDF export (using FPDF)
if ($report_type === 'pdf') {
    require_once 'fpdf/fpdf.php'; // Assume FPDF in lib
    
    $pdf = new FPDF();
    $pdf->AddPage();
    $pdf->SetFont('Arial', 'B', 16);
    $pdf->Cell(0, 10, 'Expense Report - ' . date('M Y', strtotime($date_from)), 0, 1, 'C');
    $pdf->Ln(10);
    
    $pdf->SetFont('Arial', 'B', 12);
    $pdf->Cell(50, 10, 'Category', 1);
    $pdf->Cell(50, 10, 'Account', 1);
    $pdf->Cell(40, 10, 'Total (KES)', 1);
    $pdf->Cell(50, 10, 'Transactions', 1);
    $pdf->Ln();
    
    $grand_total = 0;
    while ($row = $report_result->fetch_assoc()) {
        $pdf->SetFont('Arial', '', 10);
        $pdf->Cell(50, 10, $row['account_category'] ?? 'N/A', 1);
        $pdf->Cell(50, 10, $row['account_name'], 1);
        $pdf->Cell(40, 10, number_format($row['total_expense'], 2), 1, 0, 'R');
        $pdf->Cell(50, 10, $row['transaction_count'], 1);
        $pdf->Ln();
        $grand_total += $row['total_expense'];
    }
    
    $pdf->SetFont('Arial', 'B', 12);
    $pdf->Cell(140, 10, 'Grand Total', 1, 0, 'R');
    $pdf->Cell(40, 10, number_format($grand_total, 2), 1, 0, 'R');
    $pdf->Cell(50, 10, '', 1);
    $pdf->Ln();
    
    $pdf->Output('D', 'expense_report_' . date('Ymd') . '.pdf');
    exit();
}

// Fetch categories for filter
$cat_sql = "SELECT DISTINCT account_category FROM chart_of_accounts WHERE account_type = 'EXPENSE' ORDER BY account_category";
$cat_result = $conn->query($cat_sql);
?>

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

<head>
    <?php includeFileWithVariables('layouts/title-meta.php', array('title' => 'Expense Reports')); ?>
    <?php include 'layouts/head-css.php'; ?>
    <link href="assets/libs/sweetalert2/sweetalert2.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">Expense Reports</h4>
                                <div class="page-title-right">
                                    <ol class="breadcrumb m-0">
                                        <li class="breadcrumb-item"><a href="dashboard.php">Dashboard</a></li>
                                        <li class="breadcrumb-item active">Reports</li>
                                    </ol>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Filters -->
                    <div class="expense-card">
                        <div class="expense-header">
                            <h5 class="mb-0"><i class="ri-filter-line me-2"></i>Report Filters</h5>
                        </div>
                        <div class="card-body">
                            <form method="GET" action="">
                                <div class="row">
                                    <div class="col-md-3 mb-3">
                                        <label class="form-label">Category</label>
                                        <select class="form-select" name="category">
                                            <option value="">All Categories</option>
                                            <?php while ($cat = $cat_result->fetch_assoc()): ?>
                                            <option value="<?php echo htmlspecialchars($cat['account_category']); ?>" <?php echo $category_filter === $cat['account_category'] ? 'selected' : ''; ?>>
                                                <?php echo htmlspecialchars($cat['account_category']); ?>
                                            </option>
                                            <?php endwhile; ?>
                                        </select>
                                    </div>
                                    <div class="col-md-3 mb-3">
                                        <label class="form-label">From Date</label>
                                        <input type="date" class="form-control" name="date_from" value="<?php echo $date_from; ?>">
                                    </div>
                                    <div class="col-md-3 mb-3">
                                        <label class="form-label">To Date</label>
                                        <input type="date" class="form-control" name="date_to" value="<?php echo $date_to; ?>">
                                    </div>
                                    <div class="col-md-3 mb-3">
                                        <label class="form-label">Format</label>
                                        <select class="form-select" name="report_type">
                                            <option value="summary" <?php echo $report_type === 'summary' ? 'selected' : ''; ?>>Summary</option>
                                            <option value="detailed">Detailed</option>
                                            <option value="pdf">PDF Export</option>
                                        </select>
                                    </div>
                                </div>
                                <button type="submit" class="btn btn-primary">
                                    <i class="ri-search-line me-1"></i>Generate Report
                                </button>
                            </form>
                        </div>
                    </div>

                    <!-- Report Table -->
                    <div class="expense-card">
                        <div class="expense-header">
                            <h5 class="mb-0"><i class="ri-bar-chart-line me-2"></i>Expense Summary Report</h5>
                        </div>
                        <div class="card-body p-0">
                            <?php if ($report_result->num_rows > 0): ?>
                            <div class="table-responsive">
                                <table class="table table-sm mb-0">
                                    <thead class="table-light">
                                        <tr>
                                            <th>Category</th>
                                            <th>Account</th>
                                            <th>Total Expense (KES)</th>
                                            <th>Transactions</th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                        <?php $grand_total = 0; while ($row = $report_result->fetch_assoc()): ?>
                                        <tr>
                                            <td><?php echo htmlspecialchars($row['account_category'] ?? 'Uncategorized'); ?></td>
                                            <td><?php echo htmlspecialchars($row['account_name']); ?></td>
                                            <td class="text-end text-danger"><strong><?php echo number_format($row['total_expense'], 2); ?></strong></td>
                                            <td><?php echo $row['transaction_count']; ?></td>
                                        </tr>
                                        <?php $grand_total += $row['total_expense']; endwhile; ?>
                                        <tr class="table-dark">
                                            <td colspan="2"><strong>Grand Total</strong></td>
                                            <td class="text-end text-danger"><strong><?php echo number_format($grand_total, 2); ?></strong></td>
                                            <td></td>
                                        </tr>
                                    </tbody>
                                </table>
                            </div>
                            <?php else: ?>
                            <div class="text-center py-5">
                                <i class="ri-bar-chart-line" style="font-size: 3rem; color: #cbd5e1;"></i>
                                <p class="text-muted mt-2">No expense data for selected period</p>
                            </div>
                            <?php endif; ?>
                        </div>
                    </div>
                </div>
            </div>
            <?php include 'layouts/footer.php'; ?>
        </div>
    </div>

    <?php include 'layouts/vendor-scripts.php'; ?>
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <script src="assets/libs/sweetalert2/sweetalert2.min.js"></script>
    <script src="assets/js/app.js"></script>
</body>
</html>