<?php
require_once __DIR__ . '/../config.php';
requireRole(['admin', 'manager']);

$report = $_GET['report'] ?? 'inventory';
$format = $_GET['format'] ?? 'json';

$pdo = getDBConnection();

switch ($report) {
    case 'inventory':
        $data = $pdo->query("
            SELECT i.ItemCode, i.ItemName, i.Category, i.UnitOfMeasure, i.ReorderLevel,
                   COALESCE(SUM(t.quantity_in), 0) - COALESCE(SUM(t.quantity_out), 0) as StockOnHand,
                   i.UnitCost,
                   (COALESCE(SUM(t.quantity_in), 0) - COALESCE(SUM(t.quantity_out), 0)) * i.UnitCost as StockValue
            FROM items_master i
            LEFT JOIN inventory_transaction t ON i.ItemCode = t.item_code
            GROUP BY i.ItemCode
            ORDER BY i.Category, i.ItemName
        ")->fetchAll();
        $filename = 'inventory_report_' . date('Y-m-d');
        break;
        
    case 'procurement':
        $data = $pdo->query("
            SELECT o.OrderID, o.ItemCode, o.ItemDescription, o.Quantity, o.UnitPrice,
                   o.Supplier, o.PO_Date, o.PO_DateApproved,
                   g.Quantity_Received, g.GRV_Date,
                   CASE WHEN g.Quantity_Received IS NOT NULL THEN 'Received' ELSE 'Pending' END as Status
            FROM orders o
            LEFT JOIN grv g ON o.Reference = g.Order_No
            ORDER BY o.PO_Date DESC
        ")->fetchAll();
        $filename = 'procurement_report_' . date('Y-m-d');
        break;
        
    case 'suppliers':
        $data = $pdo->query("
            SELECT s.SupplierID, s.SupplierName, s.ContactPerson, s.Phone, s.Email,
                   s.LeadTime_Days, s.Rating,
                   COUNT(o.OrderID) as TotalOrders,
                   COALESCE(SUM(o.Quantity * o.UnitPrice), 0) as TotalValue
            FROM suppliers s
            LEFT JOIN orders o ON s.SupplierID = o.Supplier
            GROUP BY s.SupplierID
            ORDER BY TotalValue DESC
        ")->fetchAll();
        $filename = 'supplier_report_' . date('Y-m-d');
        break;
        
    case 'transactions':
        $data = $pdo->query("
            SELECT t.id, t.item_code, t.item_description, t.source_type,
                   t.transaction_date, t.trans_type, t.reference,
                   t.quantity_in, t.quantity_out, t.unit_cost,
                   t.warehouse, t.division
            FROM inventory_transaction t
            ORDER BY t.transaction_date DESC
            LIMIT 1000
        ")->fetchAll();
        $filename = 'transactions_report_' . date('Y-m-d');
        break;
        
    default:
        jsonResponse(['error' => 'Invalid report type'], 400);
}

if ($format === 'csv') {
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="' . $filename . '.csv"');
    
    if (!empty($data)) {
        echo implode(',', array_keys($data[0])) . "\n";
        foreach ($data as $row) {
            echo implode(',', array_map(fn($v) => '"' . str_replace('"', '""', $v) . '"', $row)) . "\n";
        }
    }
} else {
    jsonResponse(['report' => $report, 'data' => $data, 'generated' => date('Y-m-d H:i:s')]);
}
?>