<?php
include 'includes/auth.php';
include 'db.php';

// ==================== Get Filters ====================
$where = "1";
$from = $_GET['from'] ?? '';
$to = $_GET['to'] ?? '';
$status_filter = $_GET['status'] ?? '';
$user_filter = $_GET['user'] ?? '';
$search = $_GET['search'] ?? '';

if($from && $to){
    $where .= " AND r.created_at BETWEEN '$from 00:00:00' AND '$to 23:59:59'";
}
if($status_filter) $where .= " AND r.status='$status_filter'";
if($user_filter) $where .= " AND r.requested_by='$user_filter'";
if($search) $where .= " AND (r.id LIKE '%$search%' OR u.username LIKE '%$search%')";

// ==================== Fetch Requisitions ====================
$requisitions = $conn->query("
    SELECT r.*, u.username AS requested_by_name
    FROM hk_requisitions r
    LEFT JOIN users u ON r.requested_by = u.id
    WHERE $where
    ORDER BY r.id DESC
");

// ==================== Prepare CSV ====================
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename=hk_requisitions_export_'.date('Y-m-d').'.csv');

$output = fopen('php://output', 'w');

// Headers
fputcsv($output, ['ID', 'Requested By', 'Date Requested', 'Items (Requested / Stock / Dispatched / Available / Low Stock)', 'Status', 'Approval Note']);

// ==================== Loop Through Data ====================
while($r = $requisitions->fetch_assoc()){
    // Fetch items for requisition
    $items_text = '';
    $items = $conn->query("
        SELECT 
            ri.*,
            p.name,
            COALESCE(SUM(ps.quantity),0) AS stock_qty,
            COALESCE(d.total_dispatched,0) AS dispatched_qty,
            (COALESCE(SUM(ps.quantity),0) - COALESCE(d.total_dispatched,0)) AS available_stock,
            CASE WHEN (COALESCE(SUM(ps.quantity),0) - COALESCE(d.total_dispatched,0)) < ri.quantity THEN 1 ELSE 0 END AS low_stock
        FROM hk_requisition_items ri
        LEFT JOIN products p ON ri.product_id = p.id
        LEFT JOIN product_stock ps ON ps.product_id = ri.product_id
        LEFT JOIN (
            SELECT product_id, SUM(quantity) AS total_dispatched
            FROM hk_dispatch_items
            GROUP BY product_id
        ) d ON d.product_id = ri.product_id
        WHERE ri.requisition_id=".$r['id']."
        GROUP BY ri.id
    ");
    while($item = $items->fetch_assoc()){
        $items_text .= $item['name']
            ." - Requested: ".$item['quantity']
            .", Stock: ".$item['stock_qty']
            .", Dispatched: ".$item['dispatched_qty']
            .", Available: ".$item['available_stock'];
        if($item['low_stock']) $items_text .= " (Low Stock!)";
        $items_text .= "; ";
    }

    fputcsv($output, [
        $r['id'],
        $r['requested_by_name'],
        $r['request_date'] ?? $r['created_at'] ?? 'N/A',
        $items_text,
        $r['status'] ?? 'Pending',
        $r['approval_note'] ?? ''
    ]);
}

fclose($output);
exit;