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

require_once '../assets/db_connect.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>";
    exit; 
}

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

// Only admin and pharmacist can access stock levels
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>";
    exit;
}

// Get filter parameters
$shop_id = $_GET['shop_id'] ?? 0;
$category = $_GET['category'] ?? '';
$stock_filter = $_GET['stock_filter'] ?? 'all'; // all, low, out, expiring
$expiry_days = (int)($_GET['expiry_days'] ?? 30);

// 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 STOCK QUERY WITH ANALYTICS ---- */
$sql = "
WITH batch_summary AS (
    SELECT 
        pb.product_id,
        pb.id as batch_id,
        pb.batch_no,
        pb.qty,
        pb.cost_price,
        pb.sell_price,
        pb.expiry_date,
        pb.shop_id,
        pb.is_active,
        pb.supplier_id,
        s.name as supplier_name,
        DATEDIFF(pb.expiry_date, CURDATE()) as days_to_expiry,
        CASE 
            WHEN pb.qty <= 0 THEN 'out_of_stock'
            WHEN pb.qty <= 10 THEN 'low_stock'
            WHEN DATEDIFF(pb.expiry_date, CURDATE()) <= :expiry_days THEN 'expiring_soon'
            ELSE 'normal'
        END as stock_status
    FROM product_batches pb
    LEFT JOIN suppliers s ON pb.supplier_id = s.id
    WHERE pb.is_active = 1
        AND pb.qty IS NOT NULL
";

$params = [
    ':expiry_days' => $expiry_days
];

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

$sql .= "),
product_summary AS (
    SELECT 
        p.id as product_id,
        p.sku,
        p.name as product_name,
        p.generic_name,
        p.unit,
        p.category,
        p.buying_price,
        p.selling_price,
        p.is_active as product_active,
        bs.shop_id,
        COALESCE(SUM(bs.qty), 0) as total_quantity,
        COUNT(bs.batch_id) as batch_count,
        MIN(bs.expiry_date) as earliest_expiry,
        MAX(bs.expiry_date) as latest_expiry,
        MIN(bs.days_to_expiry) as min_days_to_expiry,
        AVG(bs.cost_price) as avg_cost_price,
        AVG(bs.sell_price) as avg_sell_price,
        GROUP_CONCAT(DISTINCT bs.supplier_name SEPARATOR ', ') as suppliers,
        GROUP_CONCAT(
            CONCAT(bs.batch_no, ':', bs.qty, ':', bs.expiry_date) 
            SEPARATOR '|'
        ) as batch_details,
        SUM(CASE WHEN bs.qty <= 0 THEN 1 ELSE 0 END) as out_of_stock_batches,
        SUM(CASE WHEN bs.qty <= 10 AND bs.qty > 0 THEN 1 ELSE 0 END) as low_stock_batches,
        SUM(CASE WHEN bs.days_to_expiry <= :expiry_days2 AND bs.days_to_expiry > 0 THEN 1 ELSE 0 END) as expiring_batches
    FROM products p
    LEFT JOIN batch_summary bs ON p.id = bs.product_id
    WHERE p.is_active = 1
";

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

$sql .= "
    GROUP BY p.id, p.sku, p.name, p.generic_name, p.unit, p.category, 
             p.buying_price, p.selling_price, p.is_active, bs.shop_id
)
SELECT 
    *,
    CASE 
        WHEN total_quantity <= 0 THEN 'Out of Stock'
        WHEN total_quantity <= 10 THEN 'Low Stock'
        WHEN expiring_batches > 0 THEN 'Expiring Soon'
        ELSE 'In Stock'
    END as overall_status,
    ROUND((avg_sell_price - avg_cost_price) * total_quantity, 2) as potential_profit
FROM product_summary
WHERE 1=1
";

// Apply stock filter
if ($stock_filter === 'low') {
    $sql .= " AND total_quantity <= 10 AND total_quantity > 0";
} elseif ($stock_filter === 'out') {
    $sql .= " AND total_quantity <= 0";
} elseif ($stock_filter === 'expiring') {
    $sql .= " AND expiring_batches > 0";
} elseif ($stock_filter === 'active') {
    $sql .= " AND total_quantity > 0";
}

$sql .= " ORDER BY 
    CASE overall_status 
        WHEN 'Out of Stock' THEN 1
        WHEN 'Low Stock' THEN 2
        WHEN 'Expiring Soon' THEN 3
        ELSE 4
    END,
    min_days_to_expiry ASC,
    product_name ASC";

try {
    $stmt = $pdo->prepare($sql);
    
    // Bind parameters (need to bind expiry_days twice for CTE)
    $stmt->bindValue(':expiry_days', $expiry_days);
    $stmt->bindValue(':expiry_days2', $expiry_days);
    
    foreach ($params as $key => $value) {
        if ($key !== ':expiry_days' && $key !== ':expiry_days2') {
            $stmt->bindValue($key, $value);
        }
    }
    
    $stmt->execute();
    $stock_data = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Convert numeric strings to appropriate types
    foreach ($stock_data as &$item) {
        $item['total_quantity'] = (float)($item['total_quantity'] ?? 0);
        $item['avg_cost_price'] = (float)($item['avg_cost_price'] ?? 0);
        $item['avg_sell_price'] = (float)($item['avg_sell_price'] ?? 0);
        $item['potential_profit'] = (float)($item['potential_profit'] ?? 0);
        $item['batch_count'] = (int)($item['batch_count'] ?? 0);
        $item['out_of_stock_batches'] = (int)($item['out_of_stock_batches'] ?? 0);
        $item['low_stock_batches'] = (int)($item['low_stock_batches'] ?? 0);
        $item['expiring_batches'] = (int)($item['expiring_batches'] ?? 0);
        $item['min_days_to_expiry'] = $item['min_days_to_expiry'] !== null ? (int)$item['min_days_to_expiry'] : null;
    }
    unset($item); // Break the reference
    
} catch (PDOException $e) {
    error_log("Stock query failed: " . $e->getMessage());
    $stock_data = [];
}

/* ---- CALCULATE SUMMARY STATISTICS ---- */
$summary = [
    'total_products' => 0,
    'total_quantity' => 0.0,
    'total_value_cost' => 0.0,
    'total_value_sell' => 0.0,
    'total_batches' => 0,
    'out_of_stock' => 0,
    'low_stock' => 0,
    'expiring_soon' => 0,
    'in_stock' => 0,
    'categories_count' => 0
];

if (!empty($stock_data)) {
    $summary['total_products'] = count($stock_data);
    $summary['total_quantity'] = array_sum(array_column($stock_data, 'total_quantity'));
    $summary['total_batches'] = array_sum(array_column($stock_data, 'batch_count'));
    
    // Calculate total value at cost and sell price
    foreach ($stock_data as $item) {
        $summary['total_value_cost'] += $item['total_quantity'] * $item['avg_cost_price'];
        $summary['total_value_sell'] += $item['total_quantity'] * $item['avg_sell_price'];
        
        // Count status
        if ($item['total_quantity'] <= 0) {
            $summary['out_of_stock']++;
        } elseif ($item['total_quantity'] <= 10) {
            $summary['low_stock']++;
        } elseif ($item['expiring_batches'] > 0) {
            $summary['expiring_soon']++;
        } else {
            $summary['in_stock']++;
        }
    }
    
    // Count unique categories
    $unique_categories = array_filter(array_unique(array_column($stock_data, 'category')));
    $summary['categories_count'] = count($unique_categories);
}

/* ---- 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=stock_levels_' . 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, ['Stock Levels Report']);
    fputcsv($output, ['Generated:', date('Y-m-d H:i:s')]);
    
    // 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, ['Stock Filter:', ucfirst($stock_filter)]);
    fputcsv($output, ['Expiry Days:', $expiry_days]);
    fputcsv($output, []); // Empty line
    
    // Column headers
    fputcsv($output, [
        'SKU',
        'Product Name',
        'Generic Name',
        'Category',
        'Unit',
        'Total Quantity',
        'Batch Count',
        'Avg Cost Price',
        'Avg Sell Price',
        'Potential Profit',
        'Earliest Expiry',
        'Days to Expiry',
        'Suppliers',
        'Stock Status',
        'Out of Stock Batches',
        'Low Stock Batches',
        'Expiring Batches'
    ]);
    
    // Data rows
    foreach ($stock_data as $item) {
        fputcsv($output, [
            $item['sku'] ?? '',
            $item['product_name'] ?? '',
            $item['generic_name'] ?? '',
            $item['category'] ?? '',
            $item['unit'] ?? '',
            $item['total_quantity'],
            $item['batch_count'],
            $item['avg_cost_price'],
            $item['avg_sell_price'],
            $item['potential_profit'],
            $item['earliest_expiry'] ?? '',
            $item['min_days_to_expiry'] ?? '',
            $item['suppliers'] ?? '',
            $item['overall_status'] ?? '',
            $item['out_of_stock_batches'],
            $item['low_stock_batches'],
            $item['expiring_batches']
        ]);
    }
    
    // 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),
        '',
        '',
        '',
        'In Stock: ' . $summary['in_stock'],
        'Out of Stock: ' . $summary['out_of_stock'],
        'Low Stock: ' . $summary['low_stock'],
        'Expiring Soon: ' . $summary['expiring_soon']
    ]);
    
    fclose($output);
    exit;
}

/* ---- PDF EXPORT ---- */
if (isset($_GET['export']) && $_GET['export'] === 'pdf') {
    // Check if TCPDF exists
    $tcpdf_path = __DIR__ . '/../tcpdf/tcpdf.php';
    if (!file_exists($tcpdf_path)) {
        die('TCPDF library not found. Please install TCPDF first.');
    }
    
    require_once $tcpdf_path;
    
    // Create new PDF document
    $pdf = new TCPDF('L', 'mm', 'A4', true, 'UTF-8', false);
    
    // Set document information
    $pdf->SetCreator('Pharmacy System');
    $pdf->SetAuthor('Pharmacy Management');
    $pdf->SetTitle('Stock Levels Report');
    $pdf->SetSubject('Current Stock Status');
    
    // Remove default header/footer
    $pdf->setPrintHeader(false);
    $pdf->setPrintFooter(false);
    
    // Set margins
    $pdf->SetMargins(10, 10, 10);
    $pdf->SetAutoPageBreak(true, 10);
    
    // Add a page
    $pdf->AddPage();
    
    // Title
    $pdf->SetFont('helvetica', 'B', 16);
    $pdf->Cell(0, 10, 'Stock Levels Report', 0, 1, 'C');
    $pdf->SetFont('helvetica', '', 10);
    $pdf->Cell(0, 5, 'Generated: ' . date('d-M-Y H:i:s'), 0, 1, 'C');
    
    // Shop info
    $shop_name = 'All Shops';
    if ($shop_id > 0) {
        foreach ($shops as $shop) {
            if ($shop['id'] == $shop_id) {
                $shop_name = $shop['name'];
                break;
            }
        }
    }
    $pdf->Cell(0, 5, 'Shop: ' . $shop_name, 0, 1);
    $pdf->Cell(0, 5, 'Category: ' . ($category ?: 'All Categories'), 0, 1);
    $pdf->Ln(5);
    
    // Summary table
    $pdf->SetFont('helvetica', 'B', 12);
    $pdf->Cell(0, 8, 'Summary', 0, 1);
    $pdf->SetFont('helvetica', '', 10);
    
    $summary_html = '<table border="0.5" cellpadding="4">
        <tr>
            <td width="25%"><b>Total Products:</b></td>
            <td width="25%">' . $summary['total_products'] . '</td>
            <td width="25%"><b>Total Quantity:</b></td>
            <td width="25%">' . number_format($summary['total_quantity']) . '</td>
        </tr>
        <tr>
            <td><b>In Stock:</b></td>
            <td>' . $summary['in_stock'] . '</td>
            <td><b>Low Stock:</b></td>
            <td>' . $summary['low_stock'] . '</td>
        </tr>
        <tr>
            <td><b>Out of Stock:</b></td>
            <td>' . $summary['out_of_stock'] . '</td>
            <td><b>Expiring Soon:</b></td>
            <td>' . $summary['expiring_soon'] . '</td>
        </tr>
        <tr>
            <td><b>Total Batches:</b></td>
            <td>' . $summary['total_batches'] . '</td>
            <td><b>Categories:</b></td>
            <td>' . $summary['categories_count'] . '</td>
        </tr>
    </table>';
    
    $pdf->writeHTML($summary_html, true, false, true, false, '');
    $pdf->Ln(10);
    
    // Main stock table
    $pdf->SetFont('helvetica', 'B', 12);
    $pdf->Cell(0, 8, 'Stock Details', 0, 1);
    
    // Create table header
    $header = array('Product', 'SKU', 'Category', 'Qty', 'Batches', 'Cost', 'Sell', 'Expiry', 'Status');
    $widths = array(40, 25, 25, 15, 15, 20, 20, 25, 25);
    
    // Set font for table
    $pdf->SetFont('helvetica', '', 8);
    
    // Add header
    for ($i = 0; $i < count($header); $i++) {
        $pdf->Cell($widths[$i], 7, $header[$i], 1, 0, 'C');
    }
    $pdf->Ln();
    
    // Add data rows
    $pdf->SetFont('helvetica', '', 7);
    foreach ($stock_data as $item) {
        // Check if we need a new page
        if ($pdf->GetY() > 180) {
            $pdf->AddPage();
            // Add header again
            $pdf->SetFont('helvetica', '', 8);
            for ($i = 0; $i < count($header); $i++) {
                $pdf->Cell($widths[$i], 7, $header[$i], 1, 0, 'C');
            }
            $pdf->Ln();
            $pdf->SetFont('helvetica', '', 7);
        }
        
        $pdf->Cell($widths[0], 6, substr($item['product_name'], 0, 30), 1, 0, 'L');
        $pdf->Cell($widths[1], 6, $item['sku'] ?? '', 1, 0, 'C');
        $pdf->Cell($widths[2], 6, substr($item['category'] ?? '', 0, 15), 1, 0, 'L');
        $pdf->Cell($widths[3], 6, number_format($item['total_quantity']), 1, 0, 'R');
        $pdf->Cell($widths[4], 6, $item['batch_count'], 1, 0, 'C');
        $pdf->Cell($widths[5], 6, number_format($item['avg_cost_price'], 2), 1, 0, 'R');
        $pdf->Cell($widths[6], 6, number_format($item['avg_sell_price'], 2), 1, 0, 'R');
        $pdf->Cell($widths[7], 6, $item['earliest_expiry'] ? date('d-M-y', strtotime($item['earliest_expiry'])) : '-', 1, 0, 'C');
        
        // Status with color
        $status = $item['overall_status'] ?? '';
        if ($status === 'Out of Stock') {
            $pdf->SetTextColor(220, 53, 69);
        } elseif ($status === 'Low Stock') {
            $pdf->SetTextColor(255, 193, 7);
        } elseif ($status === 'Expiring Soon') {
            $pdf->SetTextColor(253, 126, 20);
        } else {
            $pdf->SetTextColor(40, 167, 69);
        }
        $pdf->Cell($widths[8], 6, $status, 1, 0, 'C');
        $pdf->SetTextColor(0, 0, 0); // Reset color
        
        $pdf->Ln();
    }
    
    // Output PDF
    $pdf->Output('stock_levels_' . date('Y-m-d') . '.pdf', 'I');
    exit;
}

/* ---- LOAD HTML TEMPLATE ---- */
require_once '../includes/header.php';
require_once '../includes/sidebar.php';
?>

<!-- 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;
    }
}

/* 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-low { background-color: #ffc107; color: #212529; }
.status-expiring { background-color: #fd7e14; color: white; }
.status-normal { background-color: #28a745; color: white; }

/* 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-high { background-color: #28a745; }
.stock-medium { background-color: #ffc107; }
.stock-low { background-color: #fd7e14; }
.stock-out { background-color: #dc3545; }
</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-success mb-4">Current Stock Levels</h3>

<!-- FILTER CONTROLS -->
<div class="filter-group no-print">
    <form method="GET" class="row g-3">
        <?php if ($user_role === 'admin'): ?>
        <div class="col-md-3">
            <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-3">
            <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="stock_filter" class="form-label">Stock Filter</label>
            <select id="stock_filter" name="stock_filter" class="form-select form-select-sm">
                <option value="all" <?= $stock_filter === 'all' ? 'selected' : '' ?>>All Products</option>
                <option value="active" <?= $stock_filter === 'active' ? 'selected' : '' ?>>In Stock Only</option>
                <option value="low" <?= $stock_filter === 'low' ? 'selected' : '' ?>>Low Stock (≤10)</option>
                <option value="out" <?= $stock_filter === 'out' ? 'selected' : '' ?>>Out of Stock</option>
                <option value="expiring" <?= $stock_filter === 'expiring' ? 'selected' : '' ?>>Expiring Soon</option>
            </select>
        </div>
        
        <div class="col-md-2">
            <label for="expiry_days" class="form-label">Expiry Days</label>
            <select id="expiry_days" name="expiry_days" class="form-select form-select-sm">
                <option value="7" <?= $expiry_days == 7 ? 'selected' : '' ?>>7 days</option>
                <option value="15" <?= $expiry_days == 15 ? 'selected' : '' ?>>15 days</option>
                <option value="30" <?= $expiry_days == 30 ? 'selected' : '' ?>>30 days</option>
                <option value="60" <?= $expiry_days == 60 ? 'selected' : '' ?>>60 days</option>
                <option value="90" <?= $expiry_days == 90 ? 'selected' : '' ?>>90 days</option>
            </select>
        </div>
        
        <div class="col-md-2 d-flex align-items-end">
            <div class="btn-group w-100" role="group">
                <button type="submit" class="btn btn-primary btn-sm">
                    <i class="fas fa-filter"></i> Filter
                </button>
                <a href="stock_levels.php" class="btn btn-secondary btn-sm">
                    <i class="fas fa-sync"></i> Reset
                </a>
            </div>
        </div>
    </form>
</div>

<!-- EXPORT BUTTONS -->
<div class="mb-3 no-print">
    <div class="btn-group" role="group">
        <a href="?export=csv&shop_id=<?= $shop_id ?>&category=<?= urlencode($category) ?>&stock_filter=<?= $stock_filter ?>&expiry_days=<?= $expiry_days ?>" 
           class="btn btn-success btn-sm">
            <i class="fas fa-file-csv"></i> Export CSV
        </a>
        <a href="?export=pdf&shop_id=<?= $shop_id ?>&category=<?= urlencode($category) ?>&stock_filter=<?= $stock_filter ?>&expiry_days=<?= $expiry_days ?>" 
           class="btn btn-danger btn-sm" target="_blank">
            <i class="fas fa-file-pdf"></i> Export PDF
        </a>
        <button onclick="window.print()" class="btn btn-dark btn-sm">
            <i class="fas fa-print"></i> Print
        </button>
    </div>
</div>

<!-- SUMMARY CARDS -->
<?php if (!empty($stock_data)): ?>
<div class="row mb-4 no-print">
    <div class="col-md-2">
        <div class="summary-card text-center">
            <h6 class="text-muted mb-2">Total Products</h6>
            <h3 class="text-primary mb-0"><?= number_format($summary['total_products']) ?></h3>
            <small class="text-muted"><?= $summary['categories_count'] ?> categories</small>
        </div>
    </div>
    <div class="col-md-2">
        <div class="summary-card text-center">
            <h6 class="text-muted mb-2">Total Quantity</h6>
            <h3 class="text-info mb-0"><?= number_format($summary['total_quantity']) ?></h3>
            <small class="text-muted"><?= $summary['total_batches'] ?> batches</small>
        </div>
    </div>
    <div class="col-md-2">
        <div class="summary-card text-center">
            <h6 class="text-muted mb-2">In Stock</h6>
            <h3 class="text-success mb-0"><?= number_format($summary['in_stock']) ?></h3>
            <small class="text-muted">Ready to sell</small>
        </div>
    </div>
    <div class="col-md-2">
        <div class="summary-card text-center">
            <h6 class="text-muted mb-2">Low Stock</h6>
            <h3 class="text-warning mb-0"><?= number_format($summary['low_stock']) ?></h3>
            <small class="text-muted">≤10 units</small>
        </div>
    </div>
    <div class="col-md-2">
        <div class="summary-card text-center">
            <h6 class="text-muted mb-2">Out of Stock</h6>
            <h3 class="text-danger mb-0"><?= number_format($summary['out_of_stock']) ?></h3>
            <small class="text-muted">Need reorder</small>
        </div>
    </div>
    <div class="col-md-2">
        <div class="summary-card text-center">
            <h6 class="text-muted mb-2">Expiring Soon</h6>
            <h3 class="text-orange mb-0"><?= number_format($summary['expiring_soon']) ?></h3>
            <small class="text-muted">≤<?= $expiry_days ?> days</small>
        </div>
    </div>
</div>
<?php endif; ?>

<!-- PRINT AREA START -->
<div id="print-area">
    <div class="text-center mb-4">
        <h4 class="mb-1"><strong>Stock Levels Report</strong></h4>
        <p class="text-muted mb-0">
            <?php if ($shop_id == 0): ?>
                <em>All Shops</em>
            <?php else: 
                $shop_name = '';
                if (!empty($shops)) {
                    foreach ($shops as $shop) {
                        if ($shop['id'] == $shop_id) {
                            $shop_name = $shop['name'];
                            break;
                        }
                    }
                }
            ?>
                <em>Shop: <?= htmlspecialchars($shop_name) ?></em>
            <?php endif; ?>
            <?php if ($category): ?> | Category: <?= htmlspecialchars($category) ?><?php endif; ?>
            <?php if ($stock_filter !== 'all'): ?> | Filter: <?= ucfirst($stock_filter) ?><?php endif; ?>
        </p>
        <p class="text-muted"><small>Generated: <?= date('d-M-Y H:i') ?> | Expiry Alert: <?= $expiry_days ?> days</small></p>
        <?php if (!empty($stock_data)): ?>
        <p class="text-muted mb-0">
            <small>
                Showing <?= number_format($summary['total_products']) ?> products, 
                <?= number_format($summary['total_quantity']) ?> units, 
                <?= number_format($summary['total_batches']) ?> batches
            </small>
        </p>
        <?php endif; ?>
    </div>

    <div class="table-responsive">
        <table class="table table-bordered table-sm table-hover" id="stockTable">
            <thead class="table-light">
                <tr>
                    <th>#</th>
                    <th>Product Details</th>
                    <th class="text-end">Total Qty</th>
                    <th class="text-end">Batches</th>
                    <th class="text-end">Avg Cost</th>
                    <th class="text-end">Avg Sell</th>
                    <th>Earliest Expiry</th>
                    <th>Days Left</th>
                    <th>Suppliers</th>
                    <th>Status</th>
                </tr>
            </thead>

            <tbody>
                <?php if (empty($stock_data)): ?>
                    <tr>
                        <td colspan="10" class="text-center py-4">
                            <div class="text-muted">
                                <i class="fas fa-boxes fa-2x mb-2"></i><br>
                                No stock data found for the selected filters
                            </div>
                        </td>
                    </tr>
                <?php else: ?>
                    <?php foreach($stock_data as $index => $item): 
                        // Determine stock level indicator
                        $stock_percentage = $item['total_quantity'] <= 0 ? 0 : min(($item['total_quantity'] / 100) * 100, 100);
                        $status_class = '';
                        if ($item['total_quantity'] <= 0) {
                            $status_class = 'status-out';
                        } elseif ($item['total_quantity'] <= 10) {
                            $status_class = 'status-low';
                        } elseif ($item['expiring_batches'] > 0) {
                            $status_class = 'status-expiring';
                        } else {
                            $status_class = 'status-normal';
                        }
                    ?>
                        <tr>
                            <td><?= $index + 1 ?></td>
                            <td>
                                <div class="fw-bold"><?= htmlspecialchars($item['product_name']) ?></div>
                                <div class="small text-muted">
                                    SKU: <?= htmlspecialchars($item['sku'] ?? 'N/A') ?> | 
                                    Generic: <?= htmlspecialchars($item['generic_name'] ?? 'N/A') ?> | 
                                    Category: <?= htmlspecialchars($item['category'] ?? '') ?> | 
                                    Unit: <?= htmlspecialchars($item['unit'] ?? '') ?>
                                </div>
                                <div class="progress progress-thin mt-1">
                                    <div class="progress-bar 
                                        <?= $item['total_quantity'] <= 0 ? 'stock-out' : 
                                           ($item['total_quantity'] <= 10 ? 'stock-low' : 
                                           ($item['expiring_batches'] > 0 ? 'stock-medium' : 'stock-high')) ?>" 
                                        role="progressbar" 
                                        style="width: <?= $stock_percentage ?>%" 
                                        aria-valuenow="<?= $stock_percentage ?>" 
                                        aria-valuemin="0" 
                                        aria-valuemax="100"></div>
                                </div>
                                <small class="text-muted">
                                    <?= $item['batch_count'] ?> batch(es) | 
                                    Out: <?= $item['out_of_stock_batches'] ?>, 
                                    Low: <?= $item['low_stock_batches'] ?>, 
                                    Exp: <?= $item['expiring_batches'] ?>
                                </small>
                            </td>
                            <td class="text-end fw-bold 
                                <?= $item['total_quantity'] <= 0 ? 'text-danger' : 
                                   ($item['total_quantity'] <= 10 ? 'text-warning' : 'text-success') ?>">
                                <?= number_format((float)$item['total_quantity']) ?>
                            </td>
                            <td class="text-end"><?= $item['batch_count'] ?></td>
                            <td class="text-end"><?= number_format((float)$item['avg_cost_price'], 2) ?></td>
                            <td class="text-end"><?= number_format((float)$item['avg_sell_price'], 2) ?></td>
                            <td>
                                <?php if ($item['earliest_expiry']): ?>
                                    <?= date('d-M-Y', strtotime($item['earliest_expiry'])) ?>
                                <?php else: ?>
                                    <span class="text-muted">-</span>
                                <?php endif; ?>
                            </td>
                            <td class="<?= ($item['min_days_to_expiry'] !== null && $item['min_days_to_expiry'] <= $expiry_days) ? 'text-warning fw-bold' : '' ?>">
                                <?php if ($item['min_days_to_expiry'] !== null): ?>
                                    <?= $item['min_days_to_expiry'] ?> days
                                <?php else: ?>
                                    <span class="text-muted">-</span>
                                <?php endif; ?>
                            </td>
                            <td class="small">
                                <?= htmlspecialchars($item['suppliers'] ?? 'No supplier') ?>
                            </td>
                            <td>
                                <span class="status-badge <?= $status_class ?>">
                                    <?= $item['overall_status'] ?>
                                </span>
                            </td>
                        </tr>
                    <?php endforeach; ?>
                <?php endif; ?>
            </tbody>

            <?php if (!empty($stock_data)): ?>
            <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"><?= number_format($summary['total_batches']) ?></th>
                    <th class="text-end">
                        <?= $summary['total_quantity'] > 0 ? 
                            number_format($summary['total_value_cost'] / $summary['total_quantity'], 2) : '0.00' ?>
                    </th>
                    <th class="text-end">
                        <?= $summary['total_quantity'] > 0 ? 
                            number_format($summary['total_value_sell'] / $summary['total_quantity'], 2) : '0.00' ?>
                    </th>
                    <th></th>
                    <th></th>
                    <th></th>
                    <th>
                        <div class="small">
                            <span class="status-badge status-normal">In Stock: <?= $summary['in_stock'] ?></span>
                            <span class="status-badge status-low">Low: <?= $summary['low_stock'] ?></span>
                            <span class="status-badge status-out">Out: <?= $summary['out_of_stock'] ?></span>
                            <span class="status-badge status-expiring">Exp: <?= $summary['expiring_soon'] ?></span>
                        </div>
                    </th>
                </tr>
            </tfoot>
            <?php endif; ?>
        </table>
    </div>
    
    <!-- BATCH DETAILS MODAL (hidden by default) -->
    <?php if (!empty($stock_data)): ?>
    <div class="mt-4">
        <h6 class="border-bottom pb-2">Batch Details Summary</h6>
        <div class="row">
            <?php 
            $batch_summary = [
                'total_batches' => $summary['total_batches'],
                'active_batches' => $summary['total_batches'] - ($summary['out_of_stock'] + $summary['low_stock'] + $summary['expiring_soon']),
                'expiring_batches' => $summary['expiring_soon']
            ];
            ?>
            <div class="col-md-4">
                <div class="card">
                    <div class="card-body">
                        <h6 class="card-title">Batch Status</h6>
                        <div class="d-flex justify-content-between">
                            <span>Active:</span>
                            <span class="fw-bold"><?= $batch_summary['active_batches'] ?></span>
                        </div>
                        <div class="d-flex justify-content-between">
                            <span>Expiring Soon:</span>
                            <span class="fw-bold text-warning"><?= $batch_summary['expiring_batches'] ?></span>
                        </div>
                    </div>
                </div>
            </div>
            <div class="col-md-4">
                <div class="card">
                    <div class="card-body">
                        <h6 class="card-title">Value Analysis</h6>
                        <div class="d-flex justify-content-between">
                            <span>Cost Value:</span>
                            <span class="fw-bold"><?= number_format($summary['total_value_cost'], 2) ?></span>
                        </div>
                        <div class="d-flex justify-content-between">
                            <span>Sell Value:</span>
                            <span class="fw-bold text-success"><?= number_format($summary['total_value_sell'], 2) ?></span>
                        </div>
                        <div class="d-flex justify-content-between">
                            <span>Potential Profit:</span>
                            <span class="fw-bold text-info"><?= number_format($summary['total_value_sell'] - $summary['total_value_cost'], 2) ?></span>
                        </div>
                    </div>
                </div>
            </div>
            <div class="col-md-4">
                <div class="card">
                    <div class="card-body">
                        <h6 class="card-title">Stock Health</h6>
                        <div class="small mb-2">In Stock Rate:</div>
                        <div class="progress mb-2" style="height: 10px;">
                            <div class="progress-bar bg-success" 
                                 style="width: <?= ($summary['in_stock'] / max($summary['total_products'], 1)) * 100 ?>%">
                            </div>
                        </div>
                        <div class="small mb-2">Low Stock Rate:</div>
                        <div class="progress mb-2" style="height: 10px;">
                            <div class="progress-bar bg-warning" 
                                 style="width: <?= ($summary['low_stock'] / max($summary['total_products'], 1)) * 100 ?>%">
                            </div>
                        </div>
                    </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 AND FILTER SCRIPT -->
<script>
document.addEventListener('DOMContentLoaded', function() {
    const table = document.getElementById('stockTable');
    const rows = table.getElementsByTagName('tbody')[0].getElementsByTagName('tr');
    const searchInput = document.createElement('input');
    
    // Add search box to the page
    const searchContainer = document.createElement('div');
    searchContainer.className = 'mb-3 no-print';
    searchContainer.innerHTML = `
        <div class="row">
            <div class="col-md-4">
                <input type="text" id="tableSearch" placeholder="Search products..." class="form-control mb-2">
            </div>
            <div class="col-md-4">
                <select id="statusFilter" class="form-select mb-2">
                    <option value="">All Status</option>
                    <option value="In Stock">In Stock</option>
                    <option value="Low Stock">Low Stock</option>
                    <option value="Out of Stock">Out of Stock</option>
                    <option value="Expiring Soon">Expiring Soon</option>
                </select>
            </div>
            <div class="col-md-4">
                <button id="clearFilters" class="btn btn-secondary btn-sm">Clear Filters</button>
            </div>
        </div>
    `;
    
    // Insert search container before the table
    table.parentNode.insertBefore(searchContainer, table);
    
    const searchBox = document.getElementById('tableSearch');
    const statusFilter = document.getElementById('statusFilter');
    const clearBtn = document.getElementById('clearFilters');
    
    function filterTable() {
        const searchValue = searchBox.value.toLowerCase();
        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 matchStatus = false;
            
            // Text search (search in all cells)
            for (let j = 0; j < cells.length; j++) {
                if (cells[j].textContent.toLowerCase().includes(searchValue)) {
                    matchText = true;
                    break;
                }
            }
            
            // Status filter (status is in the last cell)
            if (!statusValue) {
                matchStatus = true;
            } else {
                const statusCell = cells[cells.length - 1];
                if (statusCell.textContent.includes(statusValue)) {
                    matchStatus = true;
                }
            }
            
            row.style.display = (matchText && matchStatus) ? '' : 'none';
        }
    }
    
    searchBox.addEventListener('keyup', filterTable);
    statusFilter.addEventListener('change', filterTable);
    clearBtn.addEventListener('click', function() {
        searchBox.value = '';
        statusFilter.value = '';
        filterTable();
    });
    
    // Initial filter
    filterTable();
});
</script>

</div>

<?php 
require_once '../includes/footer.php'; 
?>