<?php
/**
 * Controller - Reports
 */
require_once __DIR__ . '/../includes/init.php';
require_once __DIR__ . '/../middleware/role_middleware.php';
require_once __DIR__ . '/../lib/PDFGenerator.php';
require_once __DIR__ . '/../lib/ExcelExport.php';
controller_only();

$pageTitle = 'Reports';
$db = Database::getInstance();

// Handle export
$export = $_GET['export'] ?? '';
$sessionFilter = $_GET['session_id'] ?? '';
$statusFilter = $_GET['status'] ?? '';

$reportWhere = '1=1';
$reportParams = [];
if ($sessionFilter) { $reportWhere .= ' AND a.session_id = ?'; $reportParams[] = $sessionFilter; }
if ($statusFilter) { $reportWhere .= ' AND a.status = ?'; $reportParams[] = $statusFilter; }

if ($export === 'pdf') {
    require_once __DIR__ . '/../lib/PDFGenerator.php';
    $data = $db->fetchAll("
        SELECT a.*, s.name as session_name, i.Item_Code as item_code, i.Item_Description as item_name, i.System_Qty as system_quantity, i.Stores, i.Rack, i.Level, i.Bin, a.zone,
               uc.full_name as counter_name, uk.full_name as checker_name,
               ce.counted_quantity
        FROM assignments a
        JOIN sessions s ON a.session_id = s.id
        JOIN items i ON a.item_id = i.id
        JOIN users uc ON a.counter_id = uc.id
        LEFT JOIN users uk ON a.checker_id = uk.id
        LEFT JOIN count_entries ce ON ce.assignment_id = a.id
        WHERE $reportWhere
        ORDER BY i.Item_Code ASC
    ", $reportParams);

    $pdf = new PDFGenerator();
    $pdf->setLogo(get_setting('report_logo_path', ''));
    $pdf->setTitle('Inventory Count Report');
    $pdf->setSubtitle($sessionFilter ? 'Session: ' . ($db->fetch("SELECT name FROM sessions WHERE id = ?", [$sessionFilter])['name'] ?? 'All') : 'All Sessions');
    $pdf->setFooterText('Generated by ' . APP_NAME);

    $totalAssignments = count($data);
    $approved = count(array_filter($data, fn($r) => $r['status'] === 'approved'));
    $rejected = count(array_filter($data, fn($r) => $r['status'] === 'rejected'));
    $pending = $totalAssignments - $approved - $rejected;

    $pdf->addSummaryBox([
        ['value' => $totalAssignments, 'label' => 'Total Assignments'],
        ['value' => $approved, 'label' => 'Approved'],
        ['value' => $rejected, 'label' => 'Rejected'],
        ['value' => $pending, 'label' => 'Pending'],
    ]);

    $headers = ['Item Code', 'Item Name', 'Location', 'System Qty', 'Counted Qty', 'Variance', 'Counter', 'Status'];
    $rows = [];
    foreach ($data as $d) {
        $variance = $d['counted_quantity'] !== null ? $d['counted_quantity'] - $d['system_quantity'] : null;
        $rows[] = [
            $d['item_code'],
            $d['item_name'],
            format_location($d['Stores'], $d['Rack'], $d['Level'], $d['Bin'], $d['zone']),
            format_number($d['system_quantity']),
            $d['counted_quantity'] !== null ? format_number($d['counted_quantity']) : '-',
            $variance !== null ? ($variance > 0 ? '+' : '') . format_number($variance) : '-',
            $d['counter_name'],
            ucfirst($d['status']),
        ];
    }
    $pdf->addTable($headers, $rows);
    $pdf->download('inventory_count_report');
    exit;
}

if ($export === 'excel') {
    require_once __DIR__ . '/../lib/ExcelExport.php';
    $data = $db->fetchAll("
        SELECT a.*, s.name as session_name, i.Item_Code as item_code, i.Item_Description as item_name, i.System_Qty as system_quantity, i.Stores, i.Rack, i.Level, i.Bin, a.zone,
               uc.full_name as counter_name, uk.full_name as checker_name,
               ce.counted_quantity
        FROM assignments a
        JOIN sessions s ON a.session_id = s.id
        JOIN items i ON a.item_id = i.id
        JOIN users uc ON a.counter_id = uc.id
        LEFT JOIN users uk ON a.checker_id = uk.id
        LEFT JOIN count_entries ce ON ce.assignment_id = a.id
        WHERE $reportWhere
        ORDER BY i.Item_Code ASC
    ", $reportParams);

    $excel = new ExcelExport();
    $excel->setLogo(get_setting('report_logo_path', ''));
    $excel->setTitle('Inventory Count Report');
    $excel->setFilename('inventory_count_report');
    $excel->setHeaders(['Item Code', 'Item Name', 'Location', 'Session', 'System Qty', 'Counted Qty', 'Variance', 'Counter', 'Checker', 'Status']);
    foreach ($data as $d) {
        $variance = $d['counted_quantity'] !== null ? $d['counted_quantity'] - $d['system_quantity'] : null;
        $excel->addRow([
            $d['item_code'], $d['item_name'], format_location($d['Stores'], $d['Rack'], $d['Level'], $d['Bin'], $d['zone']),
            $d['session_name'], $d['system_quantity'],
            $d['counted_quantity'] ?? '-',
            $variance !== null ? $variance : '-',
            $d['counter_name'], $d['checker_name'] ?: '-',
            ucfirst($d['status']),
        ]);
    }
    $excel->downloadCSV();
    exit;
}

// Report summary data
$sessions = $db->fetchAll("SELECT id, name FROM sessions ORDER BY name");

$summaryBySession = $db->fetchAll("
    SELECT s.name as session_name, s.id as session_id,
        COUNT(a.id) as total,
        SUM(CASE WHEN a.status = 'approved' THEN 1 ELSE 0 END) as approved,
        SUM(CASE WHEN a.status = 'rejected' THEN 1 ELSE 0 END) as rejected,
        SUM(CASE WHEN a.status = 'counted' THEN 1 ELSE 0 END) as counted,
        SUM(CASE WHEN a.status = 'pending' THEN 1 ELSE 0 END) as pending
    FROM sessions s
    LEFT JOIN assignments a ON a.session_id = s.id
    GROUP BY s.id
    ORDER BY s.created_at DESC
");

include APP_ROOT . '/templates/header.php';
?>

<div class="page-header">
    <h2>Reports</h2>
</div>

<div class="toolbar">
    <form method="GET" class="toolbar-left" style="display:flex;gap:10px;flex-wrap:wrap">
        <select name="session_id" class="form-control" style="width:auto">
            <option value="">All Sessions</option>
            <?php foreach ($sessions as $s): ?>
            <option value="<?= $s['id'] ?>" <?= $sessionFilter==$s['id']?'selected':'' ?>><?= e($s['name']) ?></option>
            <?php endforeach; ?>
        </select>
        <select name="status" class="form-control" style="width:auto">
            <option value="">All Status</option>
            <?php foreach (['pending','in_progress','counted','approved','rejected','recount'] as $s): ?>
            <option value="<?= $s ?>" <?= $statusFilter===$s?'selected':'' ?>><?= ucwords(str_replace('_',' ',$s)) ?></option>
            <?php endforeach; ?>
        </select>
        <button type="submit" class="btn btn-outline">Filter</button>
    </form>
    <div class="toolbar-right">
        <a href="?export=pdf&session_id=<?= $sessionFilter ?>&status=<?= $statusFilter ?>" class="btn btn-danger">Export PDF</a>
        <a href="?export=excel&session_id=<?= $sessionFilter ?>&status=<?= $statusFilter ?>" class="btn btn-success">Export Excel</a>
    </div>
</div>

<!-- Session Summary -->
    <!-- Monthly Qty Trends -->
    <div class="card mb-3">
        <div class="card-header">
            <h3>Monthly System Qty Trends <small>New Feature</small></h3>
            <a href="<?= APP_URL ?>/controller/system_qty_snapshots.php" class="btn btn-sm btn-outline-primary">Manage Snapshots</a>
        </div>
        <div class="table-container">
            <?php 
            $recentSnapshots = $db->fetchAll("
                SELECT 
                    SnapshotDate,
                    COUNT(*) as items_count,
                    AVG(SystemQty) as avg_qty,
                    SUM(SystemQty) as total_qty
                FROM MonthlyInventorySnapshots 
                GROUP BY SnapshotDate 
                ORDER BY SnapshotDate DESC 
                LIMIT 12
            ");
            ?>
            <table>
                <thead>
                    <tr>
                        <th>Month-End</th>
                        <th>Total Items</th>
                        <th>Avg Qty</th>
                        <th>Total Qty</th>
                    </tr>
                </thead>
                <tbody>
                    <?php if (empty($recentSnapshots)): ?>
                    <tr><td colspan="4" class="text-center text-muted p-5">No snapshots yet. <a href="<?= APP_URL ?>/controller/system_qty_snapshots.php">Create first snapshot</a></td></tr>
                    <?php else: foreach ($recentSnapshots as $snap): ?>
                    <tr>
                        <td><strong><?= format_date($snap['SnapshotDate'], 'M Y') ?></strong></td>
                        <td><?= $snap['items_count'] ?></td>
                        <td><?= format_number($snap['avg_qty']) ?></td>
                        <td><strong><?= format_number($snap['total_qty']) ?></strong></td>
                    </tr>
                    <?php endforeach; endif; ?>
                </tbody>
            </table>
        </div>
    </div>

    <!-- Session Summary -->
    <div class="card mb-3">
        <div class="card-header"><h3>Summary by Session</h3></div>
    <div class="table-container">
        <table>
            <thead>
                <tr>
                    <th>Session</th>
                    <th>Total</th>
                    <th>Pending</th>
                    <th>Counted</th>
                    <th>Approved</th>
                    <th>Rejected</th>
                    <th>Progress</th>
                </tr>
            </thead>
            <tbody>
                <?php if (empty($summaryBySession)): ?>
                <tr><td colspan="7" class="text-center text-muted" style="padding:30px">No data.</td></tr>
                <?php else: foreach ($summaryBySession as $s): ?>
                <tr>
                    <td><strong><?= e($s['session_name']) ?></strong></td>
                    <td><?= $s['total'] ?></td>
                    <td><?= status_badge('pending') ?> <?= $s['pending'] ?></td>
                    <td><?= status_badge('counted') ?> <?= $s['counted'] ?></td>
                    <td><?= status_badge('approved') ?> <?= $s['approved'] ?></td>
                    <td><?= status_badge('rejected') ?> <?= $s['rejected'] ?></td>
                    <td>
                        <?php $pct = $s['total'] > 0 ? round(($s['approved'] / $s['total']) * 100) : 0; ?>
                        <div style="background:#e5e7eb;border-radius:10px;height:8px;width:100px">
                            <div style="background:var(--success);height:8px;border-radius:10px;width:<?= $pct ?>%"></div>
                        </div>
                        <small class="text-muted"><?= $pct ?>% approved</small>
                    </td>
                </tr>
                <?php endforeach; endif; ?>
            </tbody>
        </table>
    </div>
</div>

<?php include APP_ROOT . '/templates/footer.php'; ?>
