<?php
declare(strict_types=1);
session_start();

require_once '../assets/db_connect.php';

// Start output buffering
ob_start();

require_once '../includes/header.php';
require_once '../includes/sidebar.php';

// Authentication check
if (!isset($_SESSION['user'])) { 
    echo "<div class='col-md-10 ml-sm-auto px-4 py-4'><div class='alert alert-danger'>Access denied.</div></div>";
    require_once '../includes/footer.php';
    exit; 
}

$user_role = $_SESSION['user']['role'] ?? 'cashier';
$user_shop_id = (int)($_SESSION['user']['shop_id'] ?? 0);

// Only admin and pharmacist can access stock reports
if (!in_array($user_role, ['admin', 'pharmacist'])) {
    echo "<div class='col-md-10 ml-sm-auto px-4 py-4'><div class='alert alert-danger'>Access denied. Required role: Admin or Pharmacist.</div></div>";
    require_once '../includes/footer.php';
    exit;
}

// Get filter parameters
$threshold = (int)($_GET['threshold'] ?? 20);
$shop_id = $_GET['shop_id'] ?? 0;
$category = $_GET['category'] ?? '';
$include_zero = $_GET['include_zero'] ?? 1; // 1 = include out of stock, 0 = exclude

// Validate shop filter
if ($user_role === 'admin') {
    $shop_id = (int)$shop_id;
} else {
    $shop_id = $user_shop_id;
}

/* ---- FETCH SHOPS FOR ADMIN ---- */
$shops = [];
if ($user_role === 'admin') {
    $shop_stmt = $pdo->prepare("SELECT id, name FROM shops ORDER BY name");
    $shop_stmt->execute();
    $shops = $shop_stmt->fetchAll(PDO::FETCH_ASSOC);
}

/* ---- FETCH PRODUCT CATEGORIES ---- */
$categories_stmt = $pdo->prepare("SELECT DISTINCT category FROM products WHERE category IS NOT NULL AND category != '' ORDER BY category");
$categories_stmt->execute();
$categories = $categories_stmt->fetchAll(PDO::FETCH_COLUMN, 0);

/* ---- MAIN LOW STOCK QUERY WITH ANALYTICS ---- */
$sql = "
WITH stock_summary AS (
    SELECT 
        p.id as product_id,
        p.sku,
        p.name as product_name,
        p.generic_name,
        p.category,
        p.unit,
        p.buying_price,
        p.selling_price,
        p.shop_id,
        sh.name as shop_name,
        COALESCE(SUM(pb.qty), 0) as total_quantity,
        COUNT(pb.id) as batch_count,
        MIN(pb.expiry_date) as earliest_expiry,
        MAX(pb.expiry_date) as latest_expiry,
        COALESCE(MIN(DATEDIFF(pb.expiry_date, CURDATE())), NULL) as min_days_to_expiry,
        AVG(pb.cost_price) as avg_cost_price,
        AVG(pb.sell_price) as avg_sell_price,
        GROUP_CONCAT(DISTINCT s.name SEPARATOR ', ') as suppliers,
        GROUP_CONCAT(
            CONCAT(pb.batch_no, ':', pb.qty, ':', pb.expiry_date) 
            SEPARATOR '|'
        ) as batch_details,
        SUM(CASE WHEN pb.qty <= 0 THEN 1 ELSE 0 END) as out_of_stock_batches,
        SUM(CASE WHEN pb.qty > 0 AND pb.qty <= 5 THEN 1 ELSE 0 END) as critical_batches,
        SUM(CASE WHEN pb.qty > 5 AND pb.qty <= 10 THEN 1 ELSE 0 END) as low_batches
    FROM products p
    LEFT JOIN product_batches pb ON p.id = pb.product_id AND pb.is_active = 1
    LEFT JOIN suppliers s ON pb.supplier_id = s.id
    LEFT JOIN shops sh ON p.shop_id = sh.id
    WHERE p.is_active = 1
";

$params = [];

if ($shop_id > 0) {
    $sql .= " AND p.shop_id = :shop_id";
    $params[':shop_id'] = $shop_id;
} elseif ($user_role !== 'admin' && $user_shop_id > 0) {
    $sql .= " AND p.shop_id = :shop_id";
    $params[':shop_id'] = $user_shop_id;
}

if ($category) {
    $sql .= " AND p.category = :category";
    $params[':category'] = $category;
}

$sql .= "
    GROUP BY p.id, p.sku, p.name, p.generic_name, p.category, p.unit, 
             p.buying_price, p.selling_price, p.shop_id, sh.name
)
SELECT 
    *,
    CASE 
        WHEN total_quantity <= 0 THEN 'Out of Stock'
        WHEN total_quantity <= 5 THEN 'Critical Stock'
        WHEN total_quantity <= 10 THEN 'Very Low'
        WHEN total_quantity <= :threshold THEN 'Low Stock'
        ELSE 'Normal'
    END as stock_status,
    ROUND((avg_sell_price - avg_cost_price) * total_quantity, 2) as potential_profit,
    ROUND((total_quantity / :threshold2) * 100, 1) as stock_percentage
FROM stock_summary
WHERE total_quantity <= :threshold3
";

if ($include_zero == 0) {
    $sql .= " AND total_quantity > 0";
}

$sql .= " ORDER BY 
    CASE 
        WHEN total_quantity <= 0 THEN 1
        WHEN total_quantity <= 5 THEN 2
        WHEN total_quantity <= 10 THEN 3
        ELSE 4
    END,
    total_quantity ASC,
    product_name ASC";

try {
    $stmt = $pdo->prepare($sql);
    
    // Bind parameters (need to bind threshold multiple times)
    $stmt->bindValue(':threshold', $threshold);
    $stmt->bindValue(':threshold2', $threshold);
    $stmt->bindValue(':threshold3', $threshold);
    
    foreach ($params as $key => $value) {
        $stmt->bindValue($key, $value);
    }
    
    $stmt->execute();
    $low_stock_products = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Convert numeric strings to appropriate types
    foreach ($low_stock_products as &$product) {
        $product['total_quantity'] = (float)($product['total_quantity'] ?? 0);
        $product['avg_cost_price'] = (float)($product['avg_cost_price'] ?? 0);
        $product['avg_sell_price'] = (float)($product['avg_sell_price'] ?? 0);
        $product['potential_profit'] = (float)($product['potential_profit'] ?? 0);
        $product['stock_percentage'] = (float)($product['stock_percentage'] ?? 0);
        $product['batch_count'] = (int)($product['batch_count'] ?? 0);
        $product['out_of_stock_batches'] = (int)($product['out_of_stock_batches'] ?? 0);
        $product['critical_batches'] = (int)($product['critical_batches'] ?? 0);
        $product['low_batches'] = (int)($product['low_batches'] ?? 0);
        $product['min_days_to_expiry'] = $product['min_days_to_expiry'] !== null ? (int)$product['min_days_to_expiry'] : null;
    }
    unset($product); // Break the reference
    
} catch (PDOException $e) {
    error_log("Low stock query failed: " . $e->getMessage());
    $low_stock_products = [];
}

/* ---- CALCULATE SUMMARY STATISTICS ---- */
$summary = [
    'total_products' => 0,
    'total_quantity' => 0.0,
    'out_of_stock' => 0,
    'critical_stock' => 0, // ≤5 units
    'very_low_stock' => 0, // 6-10 units
    'low_stock' => 0, // 11-threshold units
    'total_batches' => 0,
    'total_value_cost' => 0.0,
    'total_value_sell' => 0.0,
    'categories_affected' => []
];

if (!empty($low_stock_products)) {
    $summary['total_products'] = count($low_stock_products);
    $summary['total_quantity'] = array_sum(array_column($low_stock_products, 'total_quantity'));
    $summary['total_batches'] = array_sum(array_column($low_stock_products, 'batch_count'));
    
    foreach ($low_stock_products as $product) {
        // Calculate total value
        $summary['total_value_cost'] += $product['total_quantity'] * $product['avg_cost_price'];
        $summary['total_value_sell'] += $product['total_quantity'] * $product['avg_sell_price'];
        
        // Count by stock status
        if ($product['total_quantity'] <= 0) {
            $summary['out_of_stock']++;
        } elseif ($product['total_quantity'] <= 5) {
            $summary['critical_stock']++;
        } elseif ($product['total_quantity'] <= 10) {
            $summary['very_low_stock']++;
        } else {
            $summary['low_stock']++;
        }
        
        // Track affected categories
        $category = $product['category'] ?? 'Uncategorized';
        if (!isset($summary['categories_affected'][$category])) {
            $summary['categories_affected'][$category] = 0;
        }
        $summary['categories_affected'][$category]++;
    }
    
    // Sort categories by count
    arsort($summary['categories_affected']);
}

/* ---- CSV EXPORT ---- */
if (isset($_GET['export']) && $_GET['export'] === 'csv') {
    // Clean output buffer
    ob_clean();
    
    // Set headers
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename=low_stock_report_' . date('Y-m-d') . '.csv');
    
    // Output BOM for UTF-8 compatibility
    echo "\xEF\xBB\xBF";
    
    $output = fopen('php://output', 'w');
    
    // Add report header
    fputcsv($output, ['Low Stock Products Report']);
    fputcsv($output, ['Generated:', date('Y-m-d H:i:s')]);
    fputcsv($output, ['Threshold:', $threshold . ' units']);
    
    // Get shop name for CSV
    $shop_name = 'All Shops';
    if ($shop_id > 0) {
        foreach ($shops as $shop) {
            if ($shop['id'] == $shop_id) {
                $shop_name = $shop['name'];
                break;
            }
        }
    }
    fputcsv($output, ['Shop:', $shop_name]);
    fputcsv($output, ['Category:', $category ?: 'All Categories']);
    fputcsv($output, ['Include Zero Stock:', $include_zero ? 'Yes' : 'No']);
    fputcsv($output, []); // Empty line
    
    // Column headers
    fputcsv($output, [
        'SKU',
        'Product Name',
        'Generic Name',
        'Category',
        'Unit',
        'Current Stock',
        'Stock Threshold',
        'Stock %',
        'Stock Status',
        'Batch Count',
        'Avg Cost Price',
        'Avg Sell Price',
        'Potential Profit',
        'Earliest Expiry',
        'Days to Expiry',
        'Suppliers',
        'Shop'
    ]);
    
    // Data rows
    foreach ($low_stock_products as $product) {
        fputcsv($output, [
            $product['sku'] ?? '',
            $product['product_name'] ?? '',
            $product['generic_name'] ?? '',
            $product['category'] ?? '',
            $product['unit'] ?? '',
            $product['total_quantity'],
            $threshold,
            $product['stock_percentage'],
            $product['stock_status'] ?? '',
            $product['batch_count'],
            $product['avg_cost_price'],
            $product['avg_sell_price'],
            $product['potential_profit'],
            $product['earliest_expiry'] ?? '',
            $product['min_days_to_expiry'] ?? '',
            $product['suppliers'] ?? '',
            $product['shop_name'] ?? ''
        ]);
    }
    
    // Summary rows
    fputcsv($output, []); // Empty line
    fputcsv($output, ['SUMMARY', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '']);
    fputcsv($output, [
        'Total Products: ' . $summary['total_products'],
        '',
        '',
        '',
        '',
        'Total Quantity: ' . $summary['total_quantity'],
        '',
        '',
        '',
        'Total Batches: ' . $summary['total_batches'],
        '',
        '',
        'Total Potential Profit: ' . number_format($summary['total_value_sell'] - $summary['total_value_cost'], 2),
        '',
        '',
        '',
        ''
    ]);
    
    // Stock status breakdown
    fputcsv($output, [
        'STATUS BREAKDOWN',
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        ''
    ]);
    fputcsv($output, [
        'Out of Stock: ' . $summary['out_of_stock'],
        'Critical (≤5): ' . $summary['critical_stock'],
        'Very Low (6-10): ' . $summary['very_low_stock'],
        'Low Stock (11-' . $threshold . '): ' . $summary['low_stock'],
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        ''
    ]);
    
    fclose($output);
    exit;
}

?>

<!-- CSS Styles -->
<style>
@media print {
    body * { 
        visibility: hidden; 
        margin: 0;
        padding: 0;
    }
    #print-area, #print-area * { 
        visibility: visible; 
    }
    #print-area { 
        position: absolute; 
        left: 0; 
        top: 0; 
        width: 100%; 
        font-size: 11px;
    }
    .no-print { 
        display: none !important; 
    }
    .summary-cards {
        display: none;
    }
    .filter-group {
        display: none;
    }
}

/* Back button styling */
.back-btn {
    display: inline-flex;
    align-items: center;
    padding: 8px 16px;
    background-color: #6c757d;
    color: white;
    text-decoration: none;
    border-radius: 4px;
    border: none;
    cursor: pointer;
    font-size: 14px;
    margin-bottom: 15px;
    transition: background-color 0.2s;
    gap: 5px;
}

.back-btn:hover {
    background-color: #5a6268;
    color: white;
    text-decoration: none;
}

/* Summary cards */
.summary-card {
    border: 1px solid #dee2e6;
    border-radius: 8px;
    padding: 15px;
    margin-bottom: 15px;
    background: #f8f9fa;
    transition: transform 0.2s;
    height: 100%;
}

.summary-card:hover {
    transform: translateY(-2px);
    box-shadow: 0 4px 6px rgba(0,0,0,0.1);
}

/* Status badges */
.status-badge {
    padding: 3px 8px;
    border-radius: 12px;
    font-size: 12px;
    font-weight: 500;
    display: inline-block;
}

.status-out { background-color: #dc3545; color: white; }
.status-critical { background-color: #dc3545; color: white; }
.status-verylow { background-color: #fd7e14; color: white; }
.status-low { background-color: #ffc107; color: #212529; }

/* Stock level indicators */
.stock-indicator {
    height: 8px;
    border-radius: 4px;
    margin-top: 3px;
}

/* Table styling */
.table-hover tbody tr:hover {
    background-color: rgba(0,0,0,0.025);
}

.text-end {
    text-align: right !important;
}

/* Filter controls */
.filter-group {
    background-color: #f8f9fa;
    border-radius: 8px;
    padding: 15px;
    margin-bottom: 20px;
}

/* Progress bars */
.progress-thin {
    height: 6px;
    margin-top: 5px;
}

/* Color coding for stock levels */
.stock-out { background-color: #dc3545; }
.stock-critical { background-color: #dc3545; }
.stock-verylow { background-color: #fd7e14; }
.stock-low { background-color: #ffc107; }

/* Alert banner */
.alert-banner {
    border-left: 5px solid #dc3545;
    background-color: #f8d7da;
    padding: 15px;
    margin-bottom: 20px;
    border-radius: 4px;
}

/* Priority indicators */
.priority-high { border-left: 5px solid #dc3545; }
.priority-medium { border-left: 5px solid #fd7e14; }
.priority-low { border-left: 5px solid #ffc107; }
</style>

<div class="col-md-10 ml-sm-auto px-4 py-4">

<!-- BACK BUTTON 1: Top of the page -->
<div class="no-print mb-3">
    <a href="daily_sales.php" class="back-btn">
        <i class="fas fa-arrow-left"></i> Back to Reports
    </a>
</div>

<h3 class="text-danger mb-4">Low Stock Alert Report</h3>

<!-- ALERT BANNER -->
<?php if (!empty($low_stock_products)): ?>
<div class="alert-banner no-print">
    <div class="d-flex align-items-center">
        <i class="fas fa-exclamation-triangle fa-2x text-danger mr-3"></i>
        <div>
            <h5 class="mb-1">Stock Alert: <?= $summary['total_products'] ?> products below threshold</h5>
            <p class="mb-0">
                <strong><?= $summary['out_of_stock'] ?></strong> out of stock, 
                <strong><?= $summary['critical_stock'] ?></strong> critical (≤5 units), 
                <strong><?= $summary['very_low_stock'] ?></strong> very low (6-10 units), 
                <strong><?= $summary['low_stock'] ?></strong> low stock (11-<?= $threshold ?> units)
            </p>
        </div>
    </div>
</div>
<?php endif; ?>

<!-- FILTER CONTROLS -->
<div class="filter-group no-print">
    <form method="GET" class="row g-3">
        <div class="col-md-2">
            <label for="threshold" class="form-label">Threshold</label>
            <input type="number" id="threshold" name="threshold" value="<?= $threshold ?>" 
                   class="form-control form-control-sm" min="1" max="100">
        </div>
        
        <?php if ($user_role === 'admin'): ?>
        <div class="col-md-2">
            <label for="shop_id" class="form-label">Shop</label>
            <select id="shop_id" name="shop_id" class="form-select form-select-sm">
                <option value="0">All Shops</option>
                <?php foreach($shops as $shop): ?>
                    <option value="<?= (int)$shop['id'] ?>" 
                        <?= ($shop_id === (int)$shop['id']) ? 'selected' : '' ?>>
                        <?= htmlspecialchars($shop['name']) ?>
                    </option>
                <?php endforeach; ?>
            </select>
        </div>
        <?php else: ?>
            <input type="hidden" name="shop_id" value="<?= $shop_id ?>">
        <?php endif; ?>
        
        <div class="col-md-2">
            <label for="category" class="form-label">Category</label>
            <select id="category" name="category" class="form-select form-select-sm">
                <option value="">All Categories</option>
                <?php foreach($categories as $cat): ?>
                    <option value="<?= htmlspecialchars($cat) ?>" 
                        <?= ($category === $cat) ? 'selected' : '' ?>>
                        <?= htmlspecialchars($cat) ?>
                    </option>
                <?php endforeach; ?>
            </select>
        </div>
        
        <div class="col-md-2">
            <label for="include_zero" class="form-label">Show Zero Stock</label>
            <select id="include_zero" name="include_zero" class="form-select form-select-sm">
                <option value="1" <?= $include_zero ? 'selected' : '' ?>>Include (0 units)</option>
                <option value="0" <?= !$include_zero ? 'selected' : '' ?>>Exclude (0 units)</option>
            </select>
        </div>
        
        <div class="col-md-4 d-flex align-items-end">
            <div class="btn-group w-100" role="group">
                <button type="submit" class="btn btn-danger btn-sm">
                    <i class="fas fa-filter"></i> Apply Filters
                </button>
                <a href="stock_low.php" class="btn btn-secondary btn-sm">
                    <i class="fas fa-sync"></i> Reset
                </a>
                <a href="?export=csv&threshold=<?= $threshold ?>&shop_id=<?= $shop_id ?>&category=<?= urlencode($category) ?>&include_zero=<?= $include_zero ?>" 
                   class="btn btn-success btn-sm">
                    <i class="fas fa-file-csv"></i> CSV
                </a>
                <button onclick="window.print()" class="btn btn-dark btn-sm">
                    <i class="fas fa-print"></i> Print
                </button>
            </div>
        </div>
    </form>
</div>

<!-- SUMMARY CARDS -->
<?php if (!empty($low_stock_products)): ?>
<div class="row mb-4 no-print">
    <div class="col-md-3">
        <div class="summary-card text-center border-danger">
            <h6 class="text-muted mb-2">Total Alert Products</h6>
            <h3 class="text-danger mb-0"><?= number_format($summary['total_products']) ?></h3>
            <small class="text-muted">Below <?= $threshold ?> units</small>
        </div>
    </div>
    <div class="col-md-3">
        <div class="summary-card text-center border-warning">
            <h6 class="text-muted mb-2">Critical Stock (≤5)</h6>
            <h3 class="text-danger mb-0"><?= number_format($summary['critical_stock']) ?></h3>
            <small class="text-muted">Urgent reorder needed</small>
        </div>
    </div>
    <div class="col-md-3">
        <div class="summary-card text-center border-warning">
            <h6 class="text-muted mb-2">Very Low (6-10)</h6>
            <h3 class="text-warning mb-0"><?= number_format($summary['very_low_stock']) ?></h3>
            <small class="text-muted">Reorder recommended</small>
        </div>
    </div>
    <div class="col-md-3">
        <div class="summary-card text-center border-info">
            <h6 class="text-muted mb-2">Total Stock Value</h6>
            <h3 class="text-info mb-0"><?= number_format($summary['total_value_sell'], 2) ?></h3>
            <small class="text-muted">Potential profit: <?= number_format($summary['total_value_sell'] - $summary['total_value_cost'], 2) ?></small>
        </div>
    </div>
</div>

<!-- CATEGORY BREAKDOWN -->
<div class="row mb-4 no-print">
    <div class="col-12">
        <div class="card">
            <div class="card-header bg-light">
                <h6 class="mb-0">Most Affected Categories</h6>
            </div>
            <div class="card-body p-3">
                <div class="row">
                    <?php 
                    $counter = 0;
                    foreach ($summary['categories_affected'] as $category_name => $count): 
                        if ($counter >= 6) break; // Show top 6 categories
                        $percentage = ($count / max($summary['total_products'], 1)) * 100;
                    ?>
                    <div class="col-md-4 mb-2">
                        <div class="d-flex justify-content-between align-items-center">
                            <span class="text-truncate" style="max-width: 120px;" title="<?= htmlspecialchars($category_name) ?>">
                                <?= htmlspecialchars($category_name ?: 'Uncategorized') ?>
                            </span>
                            <span class="badge bg-danger">
                                <?= $count ?> products
                            </span>
                        </div>
                        <div class="progress progress-thin">
                            <div class="progress-bar bg-danger" role="progressbar" 
                                 style="width: <?= $percentage ?>%" 
                                 aria-valuenow="<?= $percentage ?>" 
                                 aria-valuemin="0" 
                                 aria-valuemax="100"></div>
                        </div>
                        <small class="text-muted d-block">
                            <?= number_format($percentage, 1) ?>% of alert products
                        </small>
                    </div>
                    <?php 
                        $counter++;
                    endforeach; 
                    ?>
                </div>
            </div>
        </div>
    </div>
</div>
<?php endif; ?>

<!-- PRINT AREA START -->
<div id="print-area">
    <div class="text-center mb-4">
        <h4 class="mb-1 text-danger"><strong>Low Stock Alert Report</strong></h4>
        <p class="text-muted mb-0">
            Threshold: <strong><?= $threshold ?> units</strong> | 
            <?php if ($shop_id == 0): ?>
                All Shops
            <?php else: 
                $shop_name = '';
                if (!empty($shops)) {
                    foreach ($shops as $shop) {
                        if ($shop['id'] == $shop_id) {
                            $shop_name = $shop['name'];
                            break;
                        }
                    }
                }
            ?>
                Shop: <?= htmlspecialchars($shop_name) ?>
            <?php endif; ?>
            <?php if ($category): ?> | Category: <?= htmlspecialchars($category) ?><?php endif; ?>
        </p>
        <p class="text-muted"><small>Generated: <?= date('d-M-Y H:i') ?> | Zero Stock: <?= $include_zero ? 'Included' : 'Excluded' ?></small></p>
        <?php if (!empty($low_stock_products)): ?>
        <p class="text-muted mb-0">
            <small>
                Alert: <?= number_format($summary['total_products']) ?> products below threshold | 
                Critical: <?= $summary['critical_stock'] ?> | 
                Very Low: <?= $summary['very_low_stock'] ?> | 
                Low: <?= $summary['low_stock'] ?>
            </small>
        </p>
        <?php endif; ?>
    </div>

    <div class="table-responsive">
        <table class="table table-bordered table-sm table-hover" id="lowStockTable">
            <thead class="table-danger">
                <tr>
                    <th>#</th>
                    <th>Product Details</th>
                    <th class="text-end">Current Stock</th>
                    <th class="text-end">Threshold</th>
                    <th class="text-end">Stock %</th>
                    <th>Earliest Expiry</th>
                    <th>Days Left</th>
                    <th>Batch Status</th>
                    <th>Stock Status</th>
                    <th>Priority</th>
                </tr>
            </thead>

            <tbody>
                <?php if (empty($low_stock_products)): ?>
                    <tr>
                        <td colspan="10" class="text-center py-4">
                            <div class="text-success">
                                <i class="fas fa-check-circle fa-2x mb-2"></i><br>
                                <strong>No low stock products found!</strong><br>
                                <small class="text-muted">All products are above the <?= $threshold ?> unit threshold.</small>
                            </div>
                        </td>
                    </tr>
                <?php else: ?>
                    <?php foreach($low_stock_products as $index => $product): 
                        // Determine priority
                        if ($product['total_quantity'] <= 0) {
                            $priority_class = 'priority-high';
                            $priority_text = 'HIGH';
                        } elseif ($product['total_quantity'] <= 5) {
                            $priority_class = 'priority-high';
                            $priority_text = 'HIGH';
                        } elseif ($product['total_quantity'] <= 10) {
                            $priority_class = 'priority-medium';
                            $priority_text = 'MEDIUM';
                        } else {
                            $priority_class = 'priority-low';
                            $priority_text = 'LOW';
                        }
                        
                        // Determine status class
                        if ($product['total_quantity'] <= 0) {
                            $status_class = 'status-out';
                        } elseif ($product['total_quantity'] <= 5) {
                            $status_class = 'status-critical';
                        } elseif ($product['total_quantity'] <= 10) {
                            $status_class = 'status-verylow';
                        } else {
                            $status_class = 'status-low';
                        }
                    ?>
                        <tr class="<?= $priority_class ?>">
                            <td><?= $index + 1 ?></td>
                            <td>
                                <div class="fw-bold"><?= htmlspecialchars($product['product_name']) ?></div>
                                <div class="small text-muted">
                                    SKU: <?= htmlspecialchars($product['sku'] ?? 'N/A') ?> | 
                                    Generic: <?= htmlspecialchars($product['generic_name'] ?? 'N/A') ?> | 
                                    Category: <?= htmlspecialchars($product['category'] ?? '') ?> | 
                                    Unit: <?= htmlspecialchars($product['unit'] ?? '') ?>
                                </div>
                                <div class="small text-muted">
                                    Suppliers: <?= htmlspecialchars($product['suppliers'] ?? 'No supplier') ?>
                                </div>
                                <div class="progress progress-thin mt-1">
                                    <div class="progress-bar 
                                        <?= $product['total_quantity'] <= 0 ? 'stock-out' : 
                                           ($product['total_quantity'] <= 5 ? 'stock-critical' : 
                                           ($product['total_quantity'] <= 10 ? 'stock-verylow' : 'stock-low')) ?>" 
                                        role="progressbar" 
                                        style="width: <?= $product['stock_percentage'] ?>%" 
                                        aria-valuenow="<?= $product['stock_percentage'] ?>" 
                                        aria-valuemin="0" 
                                        aria-valuemax="100"></div>
                                </div>
                                <small class="text-muted">
                                    <?= $product['batch_count'] ?> batch(es) | 
                                    Avg Cost: <?= number_format($product['avg_cost_price'], 2) ?> | 
                                    Avg Sell: <?= number_format($product['avg_sell_price'], 2) ?>
                                </small>
                            </td>
                            <td class="text-end fw-bold 
                                <?= $product['total_quantity'] <= 0 ? 'text-danger' : 
                                   ($product['total_quantity'] <= 5 ? 'text-danger' : 
                                   ($product['total_quantity'] <= 10 ? 'text-warning' : 'text-warning')) ?>">
                                <?= number_format((float)$product['total_quantity']) ?>
                            </td>
                            <td class="text-end"><?= $threshold ?></td>
                            <td class="text-end fw-bold 
                                <?= $product['stock_percentage'] <= 25 ? 'text-danger' : 
                                   ($product['stock_percentage'] <= 50 ? 'text-warning' : 'text-info') ?>">
                                <?= number_format((float)$product['stock_percentage'], 1) ?>%
                            </td>
                            <td>
                                <?php if ($product['earliest_expiry']): ?>
                                    <?= date('d-M-Y', strtotime($product['earliest_expiry'])) ?>
                                <?php else: ?>
                                    <span class="text-muted">-</span>
                                <?php endif; ?>
                            </td>
                            <td class="<?= ($product['min_days_to_expiry'] !== null && $product['min_days_to_expiry'] <= 30) ? 'text-warning fw-bold' : '' ?>">
                                <?php if ($product['min_days_to_expiry'] !== null): ?>
                                    <?= $product['min_days_to_expiry'] ?> days
                                <?php else: ?>
                                    <span class="text-muted">-</span>
                                <?php endif; ?>
                            </td>
                            <td class="small">
                                <div class="d-flex flex-column">
                                    <div>
                                        <span class="status-indicator status-out"></span>
                                        Out: <?= $product['out_of_stock_batches'] ?>
                                    </div>
                                    <div>
                                        <span class="status-indicator status-critical"></span>
                                        Critical: <?= $product['critical_batches'] ?>
                                    </div>
                                    <div>
                                        <span class="status-indicator status-low"></span>
                                        Low: <?= $product['low_batches'] ?>
                                    </div>
                                </div>
                            </td>
                            <td>
                                <span class="status-badge <?= $status_class ?>">
                                    <?= $product['stock_status'] ?>
                                </span>
                            </td>
                            <td class="text-center">
                                <span class="badge 
                                    <?= $priority_text === 'HIGH' ? 'bg-danger' : 
                                       ($priority_text === 'MEDIUM' ? 'bg-warning' : 'bg-info') ?>">
                                    <?= $priority_text ?>
                                </span>
                            </td>
                        </tr>
                    <?php endforeach; ?>
                <?php endif; ?>
            </tbody>

            <?php if (!empty($low_stock_products)): ?>
            <tfoot class="table-active">
                <tr>
                    <th colspan="2" class="text-end">SUMMARY:</th>
                    <th class="text-end"><?= number_format($summary['total_quantity']) ?></th>
                    <th class="text-end"><?= $threshold ?></th>
                    <th class="text-end">
                        Avg: <?= $summary['total_products'] > 0 ? 
                            number_format(array_sum(array_column($low_stock_products, 'stock_percentage')) / $summary['total_products'], 1) : '0' ?>%
                    </th>
                    <th></th>
                    <th></th>
                    <th>
                        <div class="small">
                            Total: <?= $summary['total_batches'] ?> batches
                        </div>
                    </th>
                    <th>
                        <div class="small">
                            <span class="status-badge status-out">Out: <?= $summary['out_of_stock'] ?></span>
                            <span class="status-badge status-critical">Critical: <?= $summary['critical_stock'] ?></span>
                            <span class="status-badge status-verylow">Very Low: <?= $summary['very_low_stock'] ?></span>
                            <span class="status-badge status-low">Low: <?= $summary['low_stock'] ?></span>
                        </div>
                    </th>
                    <th>
                        <div class="small">
                            High: <?= $summary['out_of_stock'] + $summary['critical_stock'] ?><br>
                            Medium: <?= $summary['very_low_stock'] ?>
                        </div>
                    </th>
                </tr>
            </tfoot>
            <?php endif; ?>
        </table>
    </div>
    
    <!-- RECOMMENDATIONS SECTION -->
    <?php if (!empty($low_stock_products)): ?>
    <div class="mt-4 no-print">
        <div class="card border-warning">
            <div class="card-header bg-warning text-white">
                <h6 class="mb-0"><i class="fas fa-lightbulb mr-2"></i> Recommendations</h6>
            </div>
            <div class="card-body">
                <div class="row">
                    <div class="col-md-4">
                        <h6 class="text-danger"><i class="fas fa-exclamation-circle mr-2"></i> Immediate Action Needed</h6>
                        <ul class="small">
                            <li>Reorder <strong><?= $summary['out_of_stock'] + $summary['critical_stock'] ?></strong> critical products immediately</li>
                            <li>Check supplier lead times for urgent items</li>
                            <li>Consider temporary substitutions if available</li>
                        </ul>
                    </div>
                    <div class="col-md-4">
                        <h6 class="text-warning"><i class="fas fa-clock mr-2"></i> Plan Reorders</h6>
                        <ul class="small">
                            <li>Plan reorders for <strong><?= $summary['very_low_stock'] ?></strong> very low stock products</li>
                            <li>Review sales velocity for accurate forecasting</li>
                            <li>Consider bulk purchasing for frequently low items</li>
                        </ul>
                    </div>
                    <div class="col-md-4">
                        <h6 class="text-info"><i class="fas fa-chart-line mr-2"></i> Preventive Measures</h6>
                        <ul class="small">
                            <li>Adjust reorder points for <strong><?= $summary['low_stock'] ?></strong> low stock products</li>
                            <li>Review minimum stock levels by category</li>
                            <li>Monitor fast-moving items more frequently</li>
                        </ul>
                    </div>
                </div>
            </div>
        </div>
    </div>
    <?php endif; ?>
</div>
<!-- PRINT AREA END -->

<!-- BACK BUTTON 2: Bottom of the page -->
<div class="no-print mt-4">
    <a href="daily_sales.php" class="back-btn">
        <i class="fas fa-arrow-left"></i> Back to Reports
    </a>
</div>

<!-- SEARCH SCRIPT -->
<script>
document.addEventListener('DOMContentLoaded', function() {
    const table = document.getElementById('lowStockTable');
    if (!table) return;
    
    const rows = table.getElementsByTagName('tbody')[0].getElementsByTagName('tr');
    const searchContainer = document.createElement('div');
    
    // Add search box to the page
    searchContainer.className = 'mb-3 no-print';
    searchContainer.innerHTML = `
        <div class="row">
            <div class="col-md-6">
                <input type="text" id="tableSearch" placeholder="Search products, SKU, category..." class="form-control mb-2">
            </div>
            <div class="col-md-3">
                <select id="priorityFilter" class="form-select mb-2">
                    <option value="">All Priorities</option>
                    <option value="HIGH">High Priority</option>
                    <option value="MEDIUM">Medium Priority</option>
                    <option value="LOW">Low Priority</option>
                </select>
            </div>
            <div class="col-md-3">
                <select id="statusFilter" class="form-select mb-2">
                    <option value="">All Status</option>
                    <option value="Out of Stock">Out of Stock</option>
                    <option value="Critical Stock">Critical Stock</option>
                    <option value="Very Low">Very Low</option>
                    <option value="Low Stock">Low Stock</option>
                </select>
            </div>
        </div>
    `;
    
    // Insert search container before the table
    table.parentNode.insertBefore(searchContainer, table);
    
    const searchBox = document.getElementById('tableSearch');
    const priorityFilter = document.getElementById('priorityFilter');
    const statusFilter = document.getElementById('statusFilter');
    
    function filterTable() {
        const searchValue = searchBox.value.toLowerCase();
        const priorityValue = priorityFilter.value;
        const statusValue = statusFilter.value;
        
        for (let i = 0; i < rows.length; i++) {
            const row = rows[i];
            const cells = row.getElementsByTagName('td');
            let matchText = false;
            let matchPriority = false;
            let matchStatus = false;
            
            // Skip the "no results" row
            if (cells.length === 0) continue;
            
            // Text search (search in all cells)
            for (let j = 0; j < cells.length; j++) {
                if (cells[j].textContent.toLowerCase().includes(searchValue)) {
                    matchText = true;
                    break;
                }
            }
            
            // Priority filter (priority is in the last cell)
            if (!priorityValue) {
                matchPriority = true;
            } else {
                const priorityCell = cells[cells.length - 1];
                if (priorityCell.textContent.includes(priorityValue)) {
                    matchPriority = true;
                }
            }
            
            // Status filter (status is in the second last cell)
            if (!statusValue) {
                matchStatus = true;
            } else {
                const statusCell = cells[cells.length - 2];
                if (statusCell.textContent.includes(statusValue)) {
                    matchStatus = true;
                }
            }
            
            row.style.display = (matchText && matchPriority && matchStatus) ? '' : 'none';
        }
    }
    
    searchBox.addEventListener('keyup', filterTable);
    priorityFilter.addEventListener('change', filterTable);
    statusFilter.addEventListener('change', filterTable);
    
    // Initial filter
    filterTable();
});
</script>

</div>

<?php 
require_once '../includes/footer.php'; 
ob_end_flush();
?>