<?php
/**
 * Controller - Variance Report (Professional PHP Version)
 */
require_once __DIR__ . '/../includes/init.php';
require_once __DIR__ . '/../middleware/role_middleware.php';
require_once __DIR__ . '/../middleware/auth_middleware.php';
require_once __DIR__ . '/../lib/PDFGenerator.php';
require_once __DIR__ . '/../lib/ExcelExport.php';

controller_only();
$pageTitle = 'Variance Report';

// Handle XLSX Import
$importMessage = $importErrors = '';
if (isset($_POST['import']) && Csrf::validateToken($_POST[CSRF_TOKEN_NAME] ?? '')) {

    if (isset($_FILES['xlsx_file']) && $_FILES['xlsx_file']['error'] === UPLOAD_ERR_OK) {
        $fileTmp = $_FILES['xlsx_file']['tmp_name'];
        $fileSize = $_FILES['xlsx_file']['size'];
        $fileExt = strtolower(pathinfo($_FILES['xlsx_file']['name'], PATHINFO_EXTENSION));
        
        if ($fileExt === 'xlsx' && $fileSize < 5000000) {  // 5MB
            $uploadDir = APP_ROOT . '/uploads/imports/';
            if (!is_dir($uploadDir)) mkdir($uploadDir, 0755, true);
            
            $fileName = 'variance_import_' . time() . '_' . uniqid() . '.xlsx';
            $filePath = $uploadDir . $fileName;
            
            if (move_uploaded_file($fileTmp, $filePath)) {
                $excel = new ExcelExport();
                $rows = $excel::readXLSX($filePath, true);  // Skip header
                
                $successCount = $errorCount = 0;
                $db->beginTransaction();
                
                foreach ($rows as $row) {
                    if (count($row) < 4) continue;  // Min: month, item_code, opening, count_qty
                    
                    $monthStr = trim($row[0]);
                    $itemCode = trim($row[1]);
                    $openingQty = (float)($row[2] ?? 0);
                    $countQty = (float)($row[3] ?? 0);
                    $receiptsQty = (float)($row[4] ?? 0);
                    $issuesQty = (float)($row[5] ?? 0);
                    
                    // Parse month to YYYY-MM-01
                    $monthDate = date('Y-m-01', strtotime($monthStr));
                    if (!$monthDate || $monthDate === '1970-01-01') continue;
                    
                    // Validate item exists
                    $item = $db->fetch("SELECT id FROM items WHERE Item_Code = ?", [$itemCode]);
                    if (!$item) {
                        $errorCount++;
                        continue;
                    }
                    
                    // Insert/Update inventory_reports
                    $stmt = $db->prepare("
                        INSERT INTO inventory_reports (report_month, item_code, opening_qty, receipts_qty, issues_qty, count_qty, variance_val, responsible_person)
                        VALUES (?, ?, ?, ?, ?, ?, ?, ?)
                        ON DUPLICATE KEY UPDATE 
                            opening_qty = VALUES(opening_qty),
                            receipts_qty = VALUES(receipts_qty),
                            issues_qty = VALUES(issues_qty),
                            count_qty = VALUES(count_qty),
                            variance_val = VALUES(variance_val),
                            responsible_person = VALUES(responsible_person)
                    ");
                    $varianceVal = ($countQty - ($openingQty + $receiptsQty - $issuesQty)) * 1000;  // unitCost mock
                    $stmt->execute([$monthDate, $itemCode, $openingQty, $receiptsQty, $issuesQty, $countQty, $varianceVal, current_user()['full_name']]);
                    
                    $successCount++;
                }
                
                if ($successCount > 0) {
                    $db->commit();
                    $importMessage = "✅ Imported {$successCount} rows successfully! ({$errorCount} errors)";
                } else {
                    $db->rollback();
                    $importErrors = "No valid rows found.";
                }
                unlink($filePath);  // Cleanup
            } else {
                $importErrors = "Failed to upload file.";
            }
        } else {
            $importErrors = "Invalid file: Must be XLSX < 5MB.";
        }
    } else {
        $importErrors = "No file uploaded.";
    }
    
    // Redirect to prevent resubmit
    $query = http_build_query($_GET);
    header("Location: " . $_SERVER['PHP_SELF'] . "?$query&imported=1");
    exit;
}


$db = Database::getInstance();

// Filters
$monthFilter = $_GET['month'] ?? '';
$searchTerm = trim($_GET['search'] ?? '');

// Handle exports
$export = $_GET['export'] ?? '';
if ($export === 'pdf' || $export === 'excel') {

    $where = '1=1';
    $params = [];
    if ($monthFilter) {
        $where .= ' AND DATE_FORMAT(mis.SnapshotDate, \"%Y-%m\") = ?';
        $params[] = $monthFilter;
    }
    $data = $db->fetchAll("
        SELECT 
            DATE_FORMAT(mis.SnapshotDate, '%Y-%m') as month,
            i.Item_Code as itemCode,
            i.Item_Description as itemDesc,
            i.Item_Group as groupDesc,
            i.Stores as subGroup,
            i.Uom as uom,
            mis.SystemQty as opening,
            COALESCE(mis_next.SystemQty, i.System_Qty) as closing,
            COALESCE(ce.counted_quantity, 0) as countQty,
            1000 as unitCost,  -- Mock; add real table if exists
            0.5 as expectedLossPct  -- Mock
        FROM MonthlyInventorySnapshots mis
        LEFT JOIN MonthlyInventorySnapshots mis_next ON mis.ProductID = mis_next.ProductID AND mis_next.SnapshotDate = DATE_ADD(mis.SnapshotDate, INTERVAL 1 MONTH)
        JOIN items i ON mis.ProductID = i.id
        LEFT JOIN assignments a ON a.item_id = i.id AND a.session_id IN (SELECT id FROM sessions WHERE YEAR(start_date) = YEAR(mis.SnapshotDate) AND MONTH(start_date) = MONTH(mis.SnapshotDate))
        LEFT JOIN count_entries ce ON ce.assignment_id = a.id
        WHERE $where
        " . ($searchTerm ? "HAVING itemCode LIKE ? OR itemDesc LIKE ?" : "") . "
        ORDER BY mis.SnapshotDate DESC, i.Item_Code
        LIMIT 100
    ", array_merge($params, $searchTerm ? ["%$searchTerm%", "%$searchTerm%"] : []));

    if ($export === 'pdf') {
        $pdf = new PDFGenerator();
        $pdf->setLogo(get_setting('report_logo_path', ''));
        $pdf->setTitle('Variance Report');
        $pdf->setSubtitle($monthFilter ? 'Month: ' . $monthFilter : 'All Months');
        $pdf->setFooterText('Generated by ' . APP_NAME);

        // Summary stats
        $totalVal = array_sum(array_map(fn($r) => (($r['countQty'] ?? 0) - ($r['opening'] ?? 0)) * $r['unitCost'], $data));
$highRisk = count(array_filter($data, function($r) {
  $opening = $r['opening'] ?? 0;
  $countQty = $r['countQty'] ?? 0;
  $varPct = ($opening != 0) ? abs(($countQty - $opening) / $opening * 100) : 0;
  return $varPct > ($r['expectedLossPct'] ?? 0);
}));
        $pdf->addSummaryBox([
            ['value' => count($data), 'label' => 'Total Items'],
            ['value' => '$' . number_format($totalVal, 2), 'label' => 'Net Variance Value'],
            ['value' => $highRisk, 'label' => 'High Risk'],
            ['value' => '94%', 'label' => 'Health Score'],
        ]);

        $headers = ['Month', 'Item Code', 'Description', 'Group', 'UOM', 'Opening', 'Count Qty', 'Var Qty', 'Var Value', 'Risk'];
        $rows = [];
        foreach ($data as $r) {
            $sys = $r['opening'] ?? 0;
            $varQty = ($r['countQty'] ?? 0) - $sys;
            $varVal = $varQty * $r['unitCost'];
$varPct = ($sys != 0) ? round($varQty / $sys * 100, 1) : 0;
            $risk = abs($varPct) > $r['expectedLossPct'] * 2 ? 'Critical' : (abs($varPct) > $r['expectedLossPct'] ? 'High' : 'Low');
            $rows[] = [$r['month'], $r['itemCode'], substr($r['itemDesc'], 0, 30), $r['groupDesc'], $r['uom'], number_format($r['opening'], 2), number_format($r['countQty'], 2), $varQty > 0 ? '+' : '' . number_format($varQty, 2), '$' . number_format($varVal, 2), $risk];
        }
        $pdf->addTable($headers, $rows);
        $pdf->download('variance_report_' . date('Ymd'));
        exit;
    } elseif ($export === 'excel') {
        $excel = new ExcelExport();
        $excel->setTitle('Variance Report');
        $excel->setFilename('variance_report');
        $excel->setHeaders(array_keys($data[0] ?? []));
        foreach ($data as $row) $excel->addRow($row);
        $excel->downloadXLSX();
        exit;
    }

}

// Main data for display (limited)
$months = $db->fetchAll("SELECT DISTINCT DATE_FORMAT(SnapshotDate, '%Y-%m') as month FROM MonthlyInventorySnapshots ORDER BY SnapshotDate DESC LIMIT 6");
$where = '1=1';
$params = [];
if ($monthFilter) {
    $where .= ' AND DATE_FORMAT(mis.SnapshotDate, "%Y-%m") = ?';
    $params[] = $monthFilter;
}
$data = $db->fetchAll("
    SELECT 
        DATE_FORMAT(mis.SnapshotDate, '%Y-%m') as month,
        i.Item_Code as itemCode,
        i.Item_Description as itemDesc,
        COALESCE(i.Item_Group, 'Uncategorized') as groupDesc,
        COALESCE(i.Stores, 'Main') as subGroup,
        i.Uom as uom,
        mis.SystemQty as opening,
        i.System_Qty as systemQty,  -- Current for approx
        COALESCE(AVG(ce.counted_quantity), 0) as countQty,
        1000 as unitCost,
        0.5 as expectedLossPct
    FROM MonthlyInventorySnapshots mis
    JOIN items i ON mis.ProductID = i.id
    LEFT JOIN assignments a ON a.item_id = i.id 
    LEFT JOIN count_entries ce ON ce.assignment_id = a.id
    WHERE $where
    GROUP BY i.id, mis.SnapshotDate
    HAVING itemCode LIKE ? OR itemDesc LIKE ?
    ORDER BY mis.SnapshotDate DESC, i.Item_Code
    LIMIT 50
", array_merge($params, ["%$searchTerm%", "%$searchTerm%"]));

// Calculate stats
$totalItems = count($data);
$totalVarVal = 0;
$highRiskCount = 0;
foreach ($data as $row) {
    $sysQty = $row['systemQty'] ?? $row['opening'] ?? 0;
    $varQty = ($row['countQty'] ?? 0) - $sysQty;
$varPct = ($sysQty != 0) ? round($varQty / $sysQty * 100, 1) : 0;
    $totalVarVal += $varQty * $row['unitCost'];
    if (abs($varPct) > $row['expectedLossPct']) $highRiskCount++;
}

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

<style>
.report-header-logo {
    display: flex;
    flex-direction: column;
    align-items: center;
    text-align: center;
    padding: 20px 0;
    border-bottom: 2px solid #eee;
    margin-bottom: 30px;
}
.report-header-logo img {
    width: 100%;
    max-width: 100%;
    height: auto;
    margin-bottom: 10px;
}
</style>

<div class="report-header-logo">
    <?php $logoPath = get_setting('report_logo_path', 'assets/images/PAB Logo.png'); ?>
    <?php if ($logoPath && file_exists(APP_ROOT . '/' . $logoPath)): ?>
    <img src="<?= APP_URL . '/' . $logoPath ?>" alt="<?= get_setting('company_name', 'Company') ?> Logo" style="width:100%;max-width:100%;height:auto;">
    <?php endif; ?>
</div>

<div class="toolbar">
    <div class="toolbar-left">
        <form method="GET" style="display: flex; gap: 12px; align-items: center; flex-wrap: wrap;">
            <div class="search-box">
                <input type="text" name="search" class="form-control" placeholder="Search item code/desc..." value="<?= e($searchTerm) ?>" style="min-width: 300px;">
            </div>
            <select name="month" class="form-control">
                <option value="">All Months</option>
                <?php foreach ($months as $m): ?>
                <option value="<?= $m['month'] ?>" <?= $monthFilter === $m['month'] ? 'selected' : '' ?>><?= $m['month'] ?></option>
                <?php endforeach; ?>
            </select>
            <button type="submit" class="btn btn-outline-primary">Filter</button>
        </form>
    </div>
    <div class="toolbar-right">
        <a href="?export=pdf<?= $_SERVER['QUERY_STRING'] ? '&' . $_SERVER['QUERY_STRING'] : '' ?>" class="btn btn-danger">
            📄 PDF Export
        </a>
        <a href="?export=excel<?= $_SERVER['QUERY_STRING'] ? '&' . $_SERVER['QUERY_STRING'] : '' ?>" class="btn btn-success">
            📊 XLSX Export
        </a>
        <form method="POST" enctype="multipart/form-data" class="d-inline">
            <?= Csrf::field() ?>
            <input type="hidden" name="import" value="1">

            <input type="file" name="xlsx_file" accept=".xlsx" class="form-control d-none" id="importFile" required>
            <label for="importFile" class="btn btn-primary">
                📥 Import XLSX
            </label>
        </form>
    </div>
</div>

<?php if ($importMessage): ?>
<div class="alert alert-success">
    <?= $importMessage ?>
</div>
<?php endif; ?>

<?php if ($importErrors): ?>
<div class="alert alert-danger">
    <?= $importErrors ?>
</div>
<?php endif; ?>

<!-- KPI Stats -->
<div class="stats-grid">

    <div class="stat-card">
        <div class="stat-icon secondary">📦</div>
        <div class="stat-content">
            <div class="stat-value"><?= $totalItems ?></div>
            <div class="stat-label">Total Items</div>
        </div>
    </div>
    <div class="stat-card">
        <div class="stat-icon <?= $totalVarVal >= 0 ? 'success' : 'danger' ?>">
            <?= $totalVarVal >= 0 ? '💹' : '📉' ?>
        </div>
        <div class="stat-content">
            <div class="stat-value">$<?= number_format(abs($totalVarVal), 2) ?></div>
            <div class="stat-label"><?= $totalVarVal >= 0 ? 'Net Gain' : 'Net Loss' ?></div>
        </div>
    </div>
    <div class="stat-card">
        <div class="stat-icon danger">⚠️</div>
        <div class="stat-content">
            <div class="stat-value"><?= $highRiskCount ?></div>
            <div class="stat-label">High Risk Alerts</div>
        </div>
    </div>
    <div class="stat-card">
        <div class="stat-icon success">✅</div>
        <div class="stat-content">
            <div class="stat-value">94.2%</div>
            <div class="stat-label">Inventory Health</div>
        </div>
    </div>
</div>

<!-- Main Table -->
<div class="card">
    <div class="card-header">
        <h3>Variance Details <span class="badge badge-info"><?= $totalItems ?> items</span></h3>
    </div>
    <div class="table-container">
        <?php if (empty($data)): ?>
        <div class="empty-state">
            <div class="icon">📊</div>
            <h3>No Variance Data</h3>
            <p>No matching records found. Try adjusting filters or create snapshots.</p>
            <a href="<?= APP_URL ?>/controller/system_qty_snapshots.php" class="btn btn-primary">Create Snapshot</a>
        </div>
        <?php else: ?>
        <table id="varianceTable" class="display compact">
            <thead>
                <tr>
                    <th>Month</th>
                    <th>Item Code</th>
                    <th>Item Desc</th>
                    <th>Group</th>
                    <th>Sub Group</th>
                    <th>UOM</th>
                    <th class="text-right">Opening</th>
                    <th class="text-right">System Qty</th>
                    <th class="text-right">Count Qty</th>
                    <th class="text-right">Var Qty</th>
                    <th class="text-right">Var Val</th>
                    <th class="text-right">Var %</th>
                    <th>Exp Loss %</th>
                    <th>Risk</th>
                    <th>Loss Nature</th>
                    <th>Comment</th>
                    <th>Action</th>
                    <th>Resp</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach ($data as $row): 
                    $sysQty = $row['systemQty'] ?? $row['opening'] ?? 0;
                    $countQty = $row['countQty'] ?? 0;
                    $varQty = $countQty - $sysQty;
                    $varVal = $varQty * $row['unitCost'];
$varPct = ($sysQty != 0) ? round(($varQty / $sysQty) * 100, 1) : 0;
                    $expLoss = $row['expectedLossPct'];
                    $absVarPct = abs($varPct);
                    $risk = $absVarPct > $expLoss * 2 ? 'critical' : ($absVarPct > $expLoss ? 'high' : ($absVarPct > 0 ? 'medium' : 'low'));
                    $isHighRisk = in_array($risk, ['high', 'critical']);
                ?>
                <tr class="<?= $isHighRisk ? 'high-risk' : '' ?>">
                    <td><?= $row['month'] ?></td>
                    <td><strong><?= e($row['itemCode']) ?></strong></td>
                    <td><?= e(substr($row['itemDesc'], 0, 40)) ?></td>
                    <td><?= e($row['groupDesc']) ?></td>
                    <td><?= e($row['subGroup']) ?></td>
                    <td><strong><?= $row['uom'] ?></strong></td>
                    <td class="text-right"><?= number_format($row['opening'], 2) ?></td>
                    <td class="text-right font-semibold"><?= number_format($sysQty, 2) ?></td>
                    <td class="text-right font-bold"><?= number_format($countQty, 2) ?></td>
                    <td class="text-right font-black <?= $varQty >= 0 ? 'text-success' : 'text-danger' ?>">
                        <?= $varQty >= 0 ? '+' : '' ?><?= number_format($varQty, 2) ?>
                    </td>
                    <td class="text-right font-medium <?= $varVal >= 0 ? 'text-success' : 'text-danger' ?>">
                        $<?= number_format(abs($varVal), 2) ?>
                    </td>
                    <td class="text-right font-bold <?= $isHighRisk ? 'text-danger' : '' ?>">
                        <?= $varPct >= 0 ? '+' : '' ?><?= $varPct ?>%
                    </td>
                    <td class="text-center"><?= $expLoss ?>%</td>
                    <td class="text-center">
                        <span class="risk-badge risk-<?= $risk ?>"><?= ucfirst($risk) ?></span>
                    </td>
                    <td>Handling</td>
                    <td class="italic text-muted">Sample comment</td>
                    <td class="text-primary fw-bold">Review</td>
                    <td><?= current_user()['full_name'] ?></td>
                </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
        <?php endif; ?>
    </div>
</div>

<script>
// DataTables for variance table
document.addEventListener('DOMContentLoaded', function() {
    if ($('#varianceTable').length) {
        $('#varianceTable').DataTable({
            responsive: true,
            pageLength: 25,
            searching: false,  // Client-side handled
            lengthMenu: [[25, 50, 100, -1], [25, 50, 100, 'All']],
            order: [[0, 'desc'], [1, 'asc']],
            columnDefs: [
                { targets: '_all', className: 'text-nowrap' },
                { orderable: false, targets: [15,16,17] }  // Non-sortable last cols
            ],
            language: {
                search: 'Filter table:',
                paginate: { previous: '‹', next: '›' }
            },
            dom: '<"top"l<"variance-search"f>>rt<"bottom"ip><"clear">'
        });
    }
});
</script>

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