<?php
/**
 * Controller - Monthly System Quantity Snapshots
 * Bulk input system quantities or CSV upload + create monthly snapshots
 */
require_once __DIR__ . '/../includes/init.php';
require_once __DIR__ . '/../lib/ExcelExport.php';
require_once __DIR__ . '/../middleware/role_middleware.php';
controller_only();

$pageTitle = 'Monthly System Qty Snapshots';
$db = Database::getInstance();
$userId = SessionManager::getUserId();

// Filters
$snapshotDateFilter = $_GET['snapshot_date'] ?? '';
$search = trim($_GET['search'] ?? '');

// Handle POST
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    Csrf::check();
    $action = $_POST['action'] ?? '';

    if ($action === 'create_snapshot' || $action === 'upload_csv') {
        $snapshotDateInput = $_POST['snapshot_date'] ?? '';
        if (empty($snapshotDateInput)) {
            set_error('Snapshot date required.');
        } else {
            $snapshotDate = date('Y-m-d', strtotime($snapshotDateInput . ' last day of this month'));
            $dataToProcess = [];

            if ($action === 'create_snapshot') {
                // Manual Form Submission
                foreach ($_POST['system_qty'] ?? [] as $productId => $qty) {
                    $dataToProcess[] = ['id' => (int)$productId, 'qty' => (float)$qty];
                }
            } else if ($action === 'upload_csv' && isset($_FILES['csv_file'])) {
                // CSV/XLSX Upload Logic
                $file = $_FILES['csv_file']['tmp_name'];
                $fileName = $_FILES['csv_file']['name'];
                $fileExtension = strtolower(pathinfo($fileName, PATHINFO_EXTENSION));
                
                if (is_uploaded_file($file)) {
                    $rows = [];
                    
                    if ($fileExtension === 'xlsx') {
                        // Read XLSX file
                        $rows = ExcelExport::readXLSX($file, true);
                    } else {
                        // Read CSV file
                        $rows = ExcelExport::readCSV($file, true);
                    }
                    
                    foreach ($rows as $row) {
                        if (count($row) < 2) continue;
                        $itemCode = trim($row[0]);
                        $qty = (float)$row[1];
                        
                        // Look up product ID by Item Code
                        $product = $db->fetch("SELECT id FROM items WHERE Item_Code = ?", [$itemCode]);
                        if ($product) {
                            $dataToProcess[] = ['id' => $product['id'], 'qty' => $qty, 'code' => $itemCode];
                        }
                    }
                }
            }

            if (empty($dataToProcess)) {
                set_error('No valid data found to process.');
            } else {
                $db->beginTransaction();
                try {
                    $updates = 0;
                    foreach ($dataToProcess as $entry) {
                        $pId = $entry['id'];
                        $newQty = $entry['qty'];

                        // 1. Check if snapshot already exists for this item on this date
                        $exists = $db->fetch("SELECT SnapshotID FROM MonthlyInventorySnapshots WHERE SnapshotDate = ? AND ProductID = ?", [$snapshotDate, $pId]);
                        
                        if (!$exists) {
                            // 2. Insert into Snapshots
                            $db->insert('MonthlyInventorySnapshots', [
                                'SnapshotDate' => $snapshotDate,
                                'ProductID' => $pId,
                                'SystemQty' => $newQty,
                                'RecordedBy' => $userId
                            ]);
                            // 3. Update master items table
                            $db->update('items', ['System_Qty' => $newQty], 'id = ?', [$pId]);
                            
                            log_action($userId, 'snapshot_upload_update', 'MonthlyInventorySnapshots', $pId, [
                                'date' => $snapshotDate,
                                'qty' => $newQty
                            ]);
                            $updates++;
                        }
                    }
                    $db->commit();
                    set_success("Processed " . count($dataToProcess) . " items. Created $updates new snapshots for $snapshotDate.");
                } catch (Exception $e) {
                    $db->rollBack();
                    set_error('Failed to process: ' . $e->getMessage());
                }
            }
        }
    } elseif ($action === 'export') {
        $exporter = new ExcelExport();
        $exporter->setTitle('System Qty Snapshots');
        $exporter->setFilename('system_qty_snapshots_' . date('Ymd'));

        $where = '1=1';
        $params = [];
        if ($snapshotDateFilter) {
            $where .= ' AND SnapshotDate = ?';
            $params[] = $snapshotDateFilter;
        }
        
        $snapshots = $db->fetchAll("
            SELECT mis.*, i.Item_Code, i.Item_Description, i.Item_Group
            FROM MonthlyInventorySnapshots mis
            JOIN items i ON mis.ProductID = i.id
            WHERE $where
            ORDER BY mis.SnapshotDate DESC, i.Item_Code
        ", $params);

        $exporter->setHeaders(['Snapshot Date', 'Item Code', 'Item Description', 'Item Group', 'System Qty']);
        foreach ($snapshots as $s) {
            $exporter->addRow([$s['SnapshotDate'], $s['Item_Code'], $s['Item_Description'], $s['Item_Group'], $s['SystemQty']]);
        }
        ($_POST['format'] ?? 'csv') === 'xlsx' ? $exporter->downloadXLSX() : $exporter->downloadCSV();
        exit;
    }
    redirect($_SERVER['REQUEST_URI']);
}

// Fetch Data for Display
$items = $db->fetchAll("SELECT id, Item_Code, Item_Description, Item_Group, System_Qty FROM items WHERE Status = 'Active' ORDER BY Item_Code");

$snapshotsWhere = '1=1';
$snapshotsParams = [];
if ($snapshotDateFilter) {
    $snapshotsWhere .= ' AND SnapshotDate = ?';
    $snapshotsParams[] = $snapshotDateFilter;
}
if ($search) {
    $snapshotsWhere .= ' AND (i.Item_Code LIKE ? OR i.Item_Description LIKE ?)';
    $snapshotsParams[] = "%$search%"; $snapshotsParams[] = "%$search%";
}

$snapshots = $db->fetchAll("
    SELECT mis.*, i.Item_Code, i.Item_Description, i.Item_Group, u.full_name as recorded_by_name
    FROM MonthlyInventorySnapshots mis
    JOIN items i ON mis.ProductID = i.id
    LEFT JOIN users u ON mis.RecordedBy = u.id
    WHERE $snapshotsWhere
    ORDER BY mis.SnapshotDate DESC, i.Item_Code
    LIMIT 500
", $snapshotsParams);

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

<div class="toolbar">
    <a href="<?= APP_URL ?>/controller/dashboard.php" class="btn btn-outline-secondary">&larr; Dashboard</a>
</div>

<div class="row">
    <!-- Manual Input / CSV Upload Column -->
    <div class="col-md-5">
        <div class="card mb-4">
            <div class="card-header"><h3>Upload System Qty (CSV/XLSX)</h3></div>
            <div class="card-body">
                <form method="POST" enctype="multipart/form-data">
                    <?= Csrf::field() ?>
                    <input type="hidden" name="action" value="upload_csv">
                    <div class="form-group mb-3">
                        <label class="form-label">Step 1: Select Month-End Date</label>
                        <input type="month" name="snapshot_date" class="form-control" required>
                    </div>
                    <div class="form-group mb-3">
                        <label class="form-label">Step 2: Select CSV or XLSX File</label>
                        <input type="file" name="csv_file" class="form-control" accept=".csv,.xlsx" required>
                        <small class="text-muted">Format: Item_Code, System_Qty (No headers required)</small>
                    </div>
                    <button type="submit" class="btn btn-primary w-100">Upload & Create Snapshot</button>
                </form>
            </div>
        </div>

        <div class="card">
            <div class="card-header"><h3>Manual Bulk Update</h3></div>
            <div class="card-body">
                <form method="POST">
                    <?= Csrf::field() ?>
                    <input type="hidden" name="action" value="create_snapshot">
                    <div class="form-group mb-3">
                        <label class="form-label">Snapshot Month-End</label>
                        <input type="month" name="snapshot_date" class="form-control" required>
                    </div>
                    <div style="max-height: 400px; overflow-y: auto; border: 1px solid #eee; padding: 10px; border-radius: 4px;">
                        <table class="table table-sm">
                            <thead>
                                <tr><th>Code</th><th>Current</th><th>New Qty</th></tr>
                            </thead>
                            <tbody>
                                <?php foreach ($items as $item): ?>
                                <tr>
                                    <td><small><?= e($item['Item_Code']) ?></small></td>
                                    <td><?= format_number($item['System_Qty']) ?></td>
                                    <td><input type="number" step="0.01" name="system_qty[<?= $item['id'] ?>]" value="<?= $item['System_Qty'] ?>" class="form-control form-control-sm" style="width:80px"></td>
                                </tr>
                                <?php endforeach; ?>
                            </tbody>
                        </table>
                    </div>
                    <button type="submit" class="btn btn-success w-100 mt-3">Submit Manual Snapshot</button>
                </form>
            </div>
        </div>
    </div>

    <!-- History / Listing Column -->
    <div class="col-md-7">
        <div class="card">
            <div class="card-header" style="display:flex; justify-content:space-between; align-items:center;">
                <h3>History</h3>
                <div style="display:flex; gap:5px;">
                    <form method="GET" style="display:flex; gap:5px;">
                        <input type="month" name="snapshot_date" value="<?= e($snapshotDateFilter) ?>" class="form-control form-control-sm" style="width:140px">
                        <button type="submit" class="btn btn-sm btn-outline-primary">Filter</button>
                    </form>
                    <form method="POST">
                        <?= Csrf::field() ?>
                        <input type="hidden" name="action" value="export">
                        <input type="hidden" name="snapshot_date" value="<?= e($snapshotDateFilter) ?>">
                        <button type="submit" class="btn btn-sm btn-success">Export</button>
                    </form>
                </div>
            </div>
            <div class="table-container">
                <table class="table">
                    <thead>
                        <tr>
                            <th>Date</th>
                            <th>Item</th>
                            <th>Qty</th>
                            <th>By</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php if (empty($snapshots)): ?>
                        <tr><td colspan="4" class="text-center p-4 text-muted">No records found.</td></tr>
                        <?php else: foreach ($snapshots as $s): ?>
                        <tr>
                            <td><small><?= format_date($s['SnapshotDate'], 'Y-m-d') ?></small></td>
                            <td>
                                <strong><?= e($s['Item_Code']) ?></strong><br>
                                <small class="text-muted"><?= truncate($s['Item_Description'], 30) ?></small>
                            </td>
                            <td><strong><?= format_number($s['SystemQty']) ?></strong></td>
                            <td><small><?= e($s['recorded_by_name'] ?? 'System') ?></small></td>
                        </tr>
                        <?php endforeach; endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
</div>

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