<?php
// dashboard.php - Fixed to properly track purchases from all sources
session_start();
require_once '../config/db_connect.php';
require_once '../includes/header.php';
require_once '../includes/sidebar.php';

if (!isset($_SESSION['user'])) {
    header("Location: ../auth/login.php");
    exit;
}

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

// Admin can filter by shop
$selected_shop = ($user_role === 'admin' && isset($_GET['shop_id'])) ? intval($_GET['shop_id']) : $user_shop_id;

// Helper for shop filter - FIXED: Always create new params array for each query
function shopFilterSQL($selected_shop, $alias='s') {
    if ($selected_shop > 0) { 
        return [" AND $alias.shop_id = ? ", $selected_shop];
    }
    return ["", null];
}

// Date ranges
$today = date('Y-m-d');
$month_start = date('Y-m-01');
$year_start = date('Y-01-01');
$week_start = date('Y-m-d', strtotime('-6 days'));

// --------- TOTAL SALES ---------
// Get shop filter for sales queries
list($shop_filter_sql, $shop_filter_param) = shopFilterSQL($selected_shop, 's');

$sql = "SELECT COALESCE(SUM(total),0) FROM sales s WHERE DATE(s.sale_date)=?";
$params = [$today];
if ($shop_filter_param !== null) {
    $sql .= $shop_filter_sql;
    $params[] = $shop_filter_param;
}
$stmt = $pdo->prepare($sql); 
$stmt->execute($params); 
$total_sales_today = (float)$stmt->fetchColumn();

// Month sales
$sql = "SELECT COALESCE(SUM(total),0) FROM sales s WHERE DATE(s.sale_date) BETWEEN ? AND ?";
$params = [$month_start, $today];
if ($shop_filter_param !== null) {
    $sql .= $shop_filter_sql;
    $params[] = $shop_filter_param;
}
$stmt = $pdo->prepare($sql); 
$stmt->execute($params); 
$total_sales_month = (float)$stmt->fetchColumn();

// Year sales
$sql = "SELECT COALESCE(SUM(total),0) FROM sales s WHERE DATE(s.sale_date) BETWEEN ? AND ?";
$params = [$year_start, $today];
if ($shop_filter_param !== null) {
    $sql .= $shop_filter_sql;
    $params[] = $shop_filter_param;
}
$stmt = $pdo->prepare($sql); 
$stmt->execute($params); 
$total_sales_year = (float)$stmt->fetchColumn();

// --------- TOTAL PURCHASES (FROM purchases TABLE) ---------
// Get shop filter for purchase queries
list($purchase_filter_sql, $purchase_filter_param) = shopFilterSQL($selected_shop, 'p');

// Today's purchases
$sql = "SELECT COALESCE(SUM(total),0) FROM purchases p WHERE DATE(p.purchase_date)=?";
$params = [$today];
if ($purchase_filter_param !== null) {
    $sql .= $purchase_filter_sql;
    $params[] = $purchase_filter_param;
}
$stmt = $pdo->prepare($sql); 
$stmt->execute($params); 
$total_purchases_today = (float)$stmt->fetchColumn();

// Month purchases
$sql = "SELECT COALESCE(SUM(total),0) FROM purchases p WHERE DATE(p.purchase_date) BETWEEN ? AND ?";
$params = [$month_start, $today];
if ($purchase_filter_param !== null) {
    $sql .= $purchase_filter_sql;
    $params[] = $purchase_filter_param;
}
$stmt = $pdo->prepare($sql); 
$stmt->execute($params); 
$total_purchases_month = (float)$stmt->fetchColumn();

// Year purchases
$sql = "SELECT COALESCE(SUM(total),0) FROM purchases p WHERE DATE(p.purchase_date) BETWEEN ? AND ?";
$params = [$year_start, $today];
if ($purchase_filter_param !== null) {
    $sql .= $purchase_filter_sql;
    $params[] = $purchase_filter_param;
}
$stmt = $pdo->prepare($sql); 
$stmt->execute($params); 
$total_purchases_year = (float)$stmt->fetchColumn();

// --------- PURCHASE COUNT STATISTICS ---------
// Number of purchases today
$sql = "SELECT COUNT(*) FROM purchases p WHERE DATE(p.purchase_date)=?";
$params = [$today];
if ($purchase_filter_param !== null) {
    $sql .= $purchase_filter_sql;
    $params[] = $purchase_filter_param;
}
$stmt = $pdo->prepare($sql); 
$stmt->execute($params); 
$purchase_count_today = (int)$stmt->fetchColumn();

// Average purchase value today
$avg_purchase_today = $purchase_count_today > 0 ? $total_purchases_today / $purchase_count_today : 0;

// --------- TOTAL STOCK UNITS (FROM ALL SOURCES - BOTH TABLES) ---------
// This calculates total stock from inventory movements (most accurate)
if ($selected_shop > 0) {
    $sql = "SELECT 
               COALESCE(SUM(
                   CASE 
                       WHEN im.movement_type IN ('PURCHASE', 'RETURN') THEN im.change_qty
                       WHEN im.movement_type IN ('SALE', 'ADJUSTMENT') THEN -im.change_qty
                       ELSE 0
                   END
               ), 0) as total_units,
               COALESCE(SUM(
                   CASE 
                       WHEN im.movement_type = 'PURCHASE' THEN im.change_qty
                       ELSE 0
                   END
               ), 0) as total_purchased_units,
               COALESCE(SUM(
                   CASE 
                       WHEN im.movement_type = 'SALE' THEN im.change_qty
                       ELSE 0
                   END
               ), 0) as total_sold_units
            FROM inventory_movements im
            JOIN product_batches pb ON im.batch_id = pb.id
            WHERE pb.shop_id = ? 
              AND pb.is_active = 1
              AND pb.expiry_date >= CURDATE()"; // Only non-expired
    $params = [$selected_shop];
} else {
    $sql = "SELECT 
               COALESCE(SUM(
                   CASE 
                       WHEN im.movement_type IN ('PURCHASE', 'RETURN') THEN im.change_qty
                       WHEN im.movement_type IN ('SALE', 'ADJUSTMENT') THEN -im.change_qty
                       ELSE 0
                   END
               ), 0) as total_units,
               COALESCE(SUM(
                   CASE 
                       WHEN im.movement_type = 'PURCHASE' THEN im.change_qty
                       ELSE 0
                   END
               ), 0) as total_purchased_units,
               COALESCE(SUM(
                   CASE 
                       WHEN im.movement_type = 'SALE' THEN im.change_qty
                       ELSE 0
                   END
               ), 0) as total_sold_units
            FROM inventory_movements im
            JOIN product_batches pb ON im.batch_id = pb.id
            WHERE pb.is_active = 1
              AND pb.expiry_date >= CURDATE()"; // Only non-expired
    $params = [];
}

$stmt = $pdo->prepare($sql); 
$stmt->execute($params); 
$stock_stats = $stmt->fetch(PDO::FETCH_ASSOC);

$total_stock_units = (int)$stock_stats['total_units'];
$total_purchased_units = (int)$stock_stats['total_purchased_units'];
$total_sold_units = (int)$stock_stats['total_sold_units'];

// --------- STOCK VALUE CALCULATION ---------
// Calculate stock value from active batches
if ($selected_shop > 0) {
    $sql = "SELECT 
               COALESCE(SUM(pb.qty * pb.cost_price), 0) AS stock_cost_value,
               COALESCE(SUM(pb.qty * pb.sell_price), 0) AS stock_sell_value,
               COUNT(DISTINCT pb.product_id) as unique_products,
               COUNT(DISTINCT pb.id) as active_batches
            FROM product_batches pb
            WHERE pb.shop_id = ?
              AND pb.is_active = 1
              AND pb.expiry_date >= CURDATE()
              AND pb.qty > 0";
    $params = [$selected_shop];
} else {
    $sql = "SELECT 
               COALESCE(SUM(pb.qty * pb.cost_price), 0) AS stock_cost_value,
               COALESCE(SUM(pb.qty * pb.sell_price), 0) AS stock_sell_value,
               COUNT(DISTINCT pb.product_id) as unique_products,
               COUNT(DISTINCT pb.id) as active_batches
            FROM product_batches pb
            WHERE pb.is_active = 1
              AND pb.expiry_date >= CURDATE()
              AND pb.qty > 0";
    $params = [];
}

$stmt = $pdo->prepare($sql); 
$stmt->execute($params); 
$stock_value = $stmt->fetch(PDO::FETCH_ASSOC);

$stock_cost_value = (float)$stock_value['stock_cost_value'];
$expected_profit = (float)($stock_value['stock_sell_value'] - $stock_cost_value);
$unique_products = (int)$stock_value['unique_products'];
$active_batches = (int)$stock_value['active_batches'];



// --------- GET EXPIRED STOCK ---------
if ($selected_shop > 0) {
    $sql = "SELECT 
               COUNT(*) as expired_batches, 
               COALESCE(SUM(pb.qty), 0) as expired_units,
               COALESCE(SUM(pb.qty * pb.cost_price), 0) as expired_value
            FROM product_batches pb
            WHERE pb.expiry_date < CURDATE()  -- Expired items
              AND pb.qty > 0  -- Only those with stock
              AND pb.is_active = 1
              AND pb.shop_id = ?";
    $params = [$selected_shop];
} else {
    $sql = "SELECT 
               COUNT(*) as expired_batches, 
               COALESCE(SUM(pb.qty), 0) as expired_units,
               COALESCE(SUM(pb.qty * pb.cost_price), 0) as expired_value
            FROM product_batches pb
            WHERE pb.expiry_date < CURDATE()  -- Expired items
              AND pb.qty > 0  -- Only those with stock
              AND pb.is_active = 1";
    $params = [];
}
$stmt = $pdo->prepare($sql); 
$stmt->execute($params); 
$expired_data = $stmt->fetch(PDO::FETCH_ASSOC);
$expired_stock_units = (int)$expired_data['expired_units'];
$expired_value = (float)$expired_data['expired_value'];

// --------- LOW STOCK PRODUCTS ---------
$low_threshold = 20;
if ($selected_shop > 0) {
    $sql = "SELECT 
               p.id,
               p.name,
               p.sku,
               COALESCE(SUM(pb.qty), 0) as total_qty
            FROM products p
            LEFT JOIN product_batches pb ON p.id = pb.product_id 
                AND pb.is_active = 1
                AND pb.expiry_date >= CURDATE()
            WHERE p.shop_id = ?
              AND p.is_active = 1
            GROUP BY p.id, p.name, p.sku
            HAVING total_qty > 0  /* Only products with stock */
               AND total_qty <= ?
            ORDER BY total_qty ASC";
    $params = [$selected_shop, $low_threshold];
} else {
    $sql = "SELECT 
               p.id,
               p.name,
               p.sku,
               COALESCE(SUM(pb.qty), 0) as total_qty
            FROM products p
            LEFT JOIN product_batches pb ON p.id = pb.product_id 
                AND pb.is_active = 1
                AND pb.expiry_date >= CURDATE()
            WHERE p.is_active = 1
            GROUP BY p.id, p.name, p.sku
            HAVING total_qty > 0  /* Only products with stock */
               AND total_qty <= ?
            ORDER BY total_qty ASC";
    $params = [$low_threshold];
}

$stmt = $pdo->prepare($sql); 
$stmt->execute($params); 
$low_stock = $stmt->fetchAll(PDO::FETCH_ASSOC);

// --------- NEAR EXPIRY ---------
if ($selected_shop > 0) {
    $sql = "SELECT 
               pb.id as batch_id,
               p.name AS product_name,
               pb.batch_no,
               pb.qty,
               pb.expiry_date,
               pb.cost_price,
               DATEDIFF(pb.expiry_date, CURDATE()) AS days_until_expiry
            FROM product_batches pb
            JOIN products p ON pb.product_id = p.id
            WHERE pb.expiry_date > CURDATE()  /* Not expired */
              AND pb.expiry_date <= DATE_ADD(CURDATE(), INTERVAL 90 DAY)  /* Within 90 days */
              AND pb.qty > 0  /* Only items with stock */
              AND pb.is_active = 1
              AND pb.shop_id = ?
            ORDER BY pb.expiry_date ASC 
            LIMIT 50";
    $params = [$selected_shop];
} else {
    $sql = "SELECT 
               pb.id as batch_id,
               p.name AS product_name,
               pb.batch_no,
               pb.qty,
               pb.expiry_date,
               pb.cost_price,
               DATEDIFF(pb.expiry_date, CURDATE()) AS days_until_expiry
            FROM product_batches pb
            JOIN products p ON pb.product_id = p.id
            WHERE pb.expiry_date > CURDATE()  /* Not expired */
              AND pb.expiry_date <= DATE_ADD(CURDATE(), INTERVAL 90 DAY)  /* Within 90 days */
              AND pb.qty > 0  /* Only items with stock */
              AND pb.is_active = 1
            ORDER BY pb.expiry_date ASC 
            LIMIT 50";
    $params = [];
}

$stmt = $pdo->prepare($sql); 
$stmt->execute($params); 
$near_expiry = $stmt->fetchAll(PDO::FETCH_ASSOC);

// --------- RECENT PURCHASES (Past 7 Days) ---------
$sql = "SELECT p.id, p.invoice_no, p.purchase_date, p.total, 
               COALESCE(s.name, 'Direct Batch') as supplier_name, 
               u.fullname AS created_by
        FROM purchases p 
        LEFT JOIN suppliers s ON p.supplier_id = s.id
        LEFT JOIN users u ON p.created_by = u.id
        WHERE DATE(p.purchase_date) BETWEEN ? AND ?";
$params = [$week_start, $today];
if ($selected_shop > 0) { 
    $sql .= " AND p.shop_id = ?"; 
    $params[] = $selected_shop; 
}
$sql .= " ORDER BY p.purchase_date DESC LIMIT 10";
$stmt = $pdo->prepare($sql); 
$stmt->execute($params); 
$recent_purchases = $stmt->fetchAll(PDO::FETCH_ASSOC);

// --------- RECENT SALES (Past 7 Days) - FIXED: Include paid amount ---------
$sql = "SELECT s.id, s.invoice_no, s.sale_date, s.total, s.paid, u.fullname AS cashier
        FROM sales s 
        LEFT JOIN users u ON s.created_by = u.id
        WHERE DATE(s.sale_date) BETWEEN ? AND ?";
$params = [$week_start, $today];
if ($selected_shop > 0) { 
    $sql .= " AND s.shop_id = ?"; 
    $params[] = $selected_shop; 
}
$sql .= " ORDER BY s.sale_date DESC LIMIT 10";
$stmt = $pdo->prepare($sql); 
$stmt->execute($params); 
$recent_sales = $stmt->fetchAll(PDO::FETCH_ASSOC);

// --------- SIMPLE CHART DATA FOR LAST 14 DAYS ---------
$days = 14; 
$start_date = date('Y-m-d', strtotime("-" . ($days - 1) . " days"));

$sql = "SELECT DATE(s.sale_date) AS dt, COALESCE(SUM(s.total), 0) AS total 
        FROM sales s 
        WHERE DATE(s.sale_date) BETWEEN ? AND ?";
$params = [$start_date, $today];
if ($selected_shop > 0) { 
    $sql .= " AND s.shop_id = ?"; 
    $params[] = $selected_shop; 
}
$sql .= " GROUP BY DATE(s.sale_date) ORDER BY DATE(s.sale_date)";
$stmt = $pdo->prepare($sql); 
$stmt->execute($params); 
$daily_sales = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);

// --------- PURCHASE CHART DATA FOR LAST 14 DAYS ---------
$sql = "SELECT DATE(p.purchase_date) AS dt, COALESCE(SUM(p.total), 0) AS total 
        FROM purchases p 
        WHERE DATE(p.purchase_date) BETWEEN ? AND ?";
$params = [$start_date, $today];
if ($selected_shop > 0) { 
    $sql .= " AND p.shop_id = ?"; 
    $params[] = $selected_shop; 
}
$sql .= " GROUP BY DATE(p.purchase_date) ORDER BY DATE(p.purchase_date)";
$stmt = $pdo->prepare($sql); 
$stmt->execute($params); 
$daily_purchases = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);

// Fill in missing days for sales chart
$chart_labels = [];
$sales_chart_data = [];
$purchase_chart_data = [];
for ($i = 0; $i < $days; $i++) {
    $d = date('Y-m-d', strtotime("-" . ($days - 1 - $i) . " days"));
    $chart_labels[] = date('d M', strtotime($d));
    $sales_chart_data[] = isset($daily_sales[$d]) ? (float)$daily_sales[$d] : 0;
    $purchase_chart_data[] = isset($daily_purchases[$d]) ? (float)$daily_purchases[$d] : 0;
}

// Calculate chart statistics
$total_period_sales = array_sum($sales_chart_data);
$total_period_purchases = array_sum($purchase_chart_data);
$days_with_sales = count(array_filter($sales_chart_data, function($val) { return $val > 0; }));
$days_with_purchases = count(array_filter($purchase_chart_data, function($val) { return $val > 0; }));
$avg_sales = $days_with_sales > 0 ? $total_period_sales / $days_with_sales : 0;
$avg_purchases = $days_with_purchases > 0 ? $total_period_purchases / $days_with_purchases : 0;
$max_sale = max($sales_chart_data);
$max_purchase = max($purchase_chart_data);

// --------- PARTIAL PAYMENTS (DEBITS/CREDIT SALES) ---------
if ($selected_shop > 0) {
    $sql = "SELECT 
               s.id,
               s.invoice_no,
               s.sale_date,
               s.total,
               s.paid,
               (s.total - s.paid) as balance,
               s.status,
               COALESCE(c.name, 'Walk-in Customer') as customer_name,
               c.phone as customer_phone,
               u.fullname as cashier
            FROM sales s
            LEFT JOIN customers c ON s.customer_id = c.id
            LEFT JOIN users u ON s.created_by = u.id
            WHERE s.shop_id = ?
              AND s.status IN ('open', 'partially_paid')
              AND (s.total - s.paid) > 0
            ORDER BY s.sale_date DESC, balance DESC";
    $params = [$selected_shop];
} else {
    $sql = "SELECT 
               s.id,
               s.invoice_no,
               s.sale_date,
               s.total,
               s.paid,
               (s.total - s.paid) as balance,
               s.status,
               COALESCE(c.name, 'Walk-in Customer') as customer_name,
               c.phone as customer_phone,
               u.fullname as cashier
            FROM sales s
            LEFT JOIN customers c ON s.customer_id = c.id
            LEFT JOIN users u ON s.created_by = u.id
            WHERE s.status IN ('open', 'partially_paid')
              AND (s.total - s.paid) > 0
            ORDER BY s.sale_date DESC, balance DESC";
    $params = [];
}

$stmt = $pdo->prepare($sql); 
$stmt->execute($params); 
$partial_payments = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Calculate total outstanding balance
$total_outstanding = 0;
foreach ($partial_payments as $payment) {
    $total_outstanding += $payment['balance'];
}

// --------- TOP SELLING PRODUCTS (Last 30 Days) ---------
$thirty_days_ago = date('Y-m-d', strtotime('-30 days'));
if ($selected_shop > 0) {
    $sql = "SELECT 
               p.name,
               p.sku,
               SUM(sl.qty) as total_sold,
               SUM(sl.line_total) as total_revenue
            FROM sale_lines sl
            JOIN sales s ON sl.sale_id = s.id
            JOIN products p ON sl.product_id = p.id
            WHERE DATE(s.sale_date) BETWEEN ? AND ?
              AND s.shop_id = ?
            GROUP BY sl.product_id, p.name, p.sku
            ORDER BY total_sold DESC
            LIMIT 10";
    $params = [$thirty_days_ago, $today, $selected_shop];
} else {
    $sql = "SELECT 
               p.name,
               p.sku,
               SUM(sl.qty) as total_sold,
               SUM(sl.line_total) as total_revenue
            FROM sale_lines sl
            JOIN sales s ON sl.sale_id = s.id
            JOIN products p ON sl.product_id = p.id
            WHERE DATE(s.sale_date) BETWEEN ? AND ?
            GROUP BY sl.product_id, p.name, p.sku
            ORDER BY total_sold DESC
            LIMIT 10";
    $params = [$thirty_days_ago, $today];
}
$stmt = $pdo->prepare($sql); 
$stmt->execute($params); 
$top_selling = $stmt->fetchAll(PDO::FETCH_ASSOC);

// --------- GROSS PROFIT ESTIMATE (Last 30 Days) ---------
if ($selected_shop > 0) {
    $sql = "SELECT 
               COALESCE(SUM(sl.line_total), 0) as total_sales,
               COALESCE(SUM(sl.qty * pb.cost_price), 0) as total_cost
            FROM sale_lines sl
            JOIN sales s ON sl.sale_id = s.id
            JOIN product_batches pb ON sl.batch_id = pb.id
            WHERE DATE(s.sale_date) BETWEEN ? AND ?
              AND s.shop_id = ?";
    $params = [$thirty_days_ago, $today, $selected_shop];
} else {
    $sql = "SELECT 
               COALESCE(SUM(sl.line_total), 0) as total_sales,
               COALESCE(SUM(sl.qty * pb.cost_price), 0) as total_cost
            FROM sale_lines sl
            JOIN sales s ON sl.sale_id = s.id
            JOIN product_batches pb ON sl.batch_id = pb.id
            WHERE DATE(s.sale_date) BETWEEN ? AND ?";
    $params = [$thirty_days_ago, $today];
}
$stmt = $pdo->prepare($sql); 
$stmt->execute($params); 
$profit_data = $stmt->fetch(PDO::FETCH_ASSOC);
$gross_profit_30days = (float)($profit_data['total_sales'] - $profit_data['total_cost']);
$gross_margin_30days = $profit_data['total_sales'] > 0 ? 
    ($gross_profit_30days / $profit_data['total_sales']) * 100 : 0;

// --------- MONTHLY TREND ANALYSIS ---------
// Calculate month-over-month growth for sales and purchases
$current_month_sales = $total_sales_month;
$current_month_purchases = $total_purchases_month;

// Get last month's sales - FIXED: Using proper parameter arrays
$last_month_start = date('Y-m-01', strtotime('-1 month'));
$last_month_end = date('Y-m-t', strtotime('-1 month'));

// Last month sales
$sql = "SELECT COALESCE(SUM(total),0) FROM sales s WHERE DATE(s.sale_date) BETWEEN ? AND ?";
$params = [$last_month_start, $last_month_end];
if ($shop_filter_param !== null) {
    $sql .= $shop_filter_sql;
    $params[] = $shop_filter_param;
}
$stmt = $pdo->prepare($sql); 
$stmt->execute($params); 
$last_month_sales = (float)$stmt->fetchColumn();

// Last month purchases - FIXED: Using proper parameter arrays
$sql = "SELECT COALESCE(SUM(total),0) FROM purchases p WHERE DATE(p.purchase_date) BETWEEN ? AND ?";
$params = [$last_month_start, $last_month_end];
if ($purchase_filter_param !== null) {
    $sql .= $purchase_filter_sql;
    $params[] = $purchase_filter_param;
}
$stmt = $pdo->prepare($sql); 
$stmt->execute($params); 
$last_month_purchases = (float)$stmt->fetchColumn();

// Calculate growth percentages
$sales_growth = $last_month_sales > 0 ? (($current_month_sales - $last_month_sales) / $last_month_sales) * 100 : 0;
$purchases_growth = $last_month_purchases > 0 ? (($current_month_purchases - $last_month_purchases) / $last_month_purchases) * 100 : 0;

// --------- Calculate expired products ---------
$expired_products = [];
$expired_total_qty = 0;
$expired_total_value = 0;
$expired_total_items = 0;

// Get expired products
if ($selected_shop > 0) {
    $sql = "SELECT 
               p.name AS product_name,
               pb.batch_no,
               pb.qty,
               pb.expiry_date,
               pb.cost_price,
               DATEDIFF(CURDATE(), pb.expiry_date) AS days_expired
            FROM product_batches pb
            JOIN products p ON pb.product_id = p.id
            WHERE pb.expiry_date < CURDATE()
              AND pb.qty > 0
              AND pb.is_active = 1
              AND pb.shop_id = ?
            ORDER BY pb.expiry_date DESC";
    $params = [$selected_shop];
} else {
    $sql = "SELECT 
               p.name AS product_name,
               pb.batch_no,
               pb.qty,
               pb.expiry_date,
               pb.cost_price,
               DATEDIFF(CURDATE(), pb.expiry_date) AS days_expired
            FROM product_batches pb
            JOIN products p ON pb.product_id = p.id
            WHERE pb.expiry_date < CURDATE()
              AND pb.qty > 0
              AND pb.is_active = 1
            ORDER BY pb.expiry_date DESC";
    $params = [];
}

$stmt = $pdo->prepare($sql); 
$stmt->execute($params); 
$expired_products = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($expired_products as $product) {
    $expired_total_qty += $product['qty'];
    $expired_total_value += ($product['qty'] * $product['cost_price']);
    $expired_total_items++;
}

// Debug: Log the calculated values
error_log("Dashboard Stats for shop $selected_shop:");
error_log("- Total Stock Units: $total_stock_units");
error_log("- Purchased Units: $total_purchased_units");
error_log("- Sold Units: $total_sold_units");
error_log("- Purchases Today: $total_purchases_today");
error_log("- Purchases This Month: $total_purchases_month");
error_log("- Active Batches: $active_batches");
error_log("- Unique Products: $unique_products");
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Dashboard - Pharmacy Management System</title>
    <!-- Bootstrap CSS -->
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
    <!-- Font Awesome -->
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css">
    <!-- Chart.js -->
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
    <style>
    /* Dashboard specific styles */
    .expired-badge {
        background-color: #dc3545;
        color: white;
        padding: 2px 8px;
        border-radius: 10px;
        font-size: 12px;
        font-weight: bold;
    }
    .expired-row {
        background-color: #f8d7da !important;
    }
    .expired-row:hover {
        background-color: #f5c6cb !important;
    }
    .near-expiry-row {
        background-color: #fff3cd !important;
    }
    .near-expiry-row:hover {
        background-color: #ffeaa7 !important;
    }
    .expired-tab {
        color: #dc3545 !important;
        border-bottom: 2px solid #dc3545 !important;
    }
    .near-expiry-tab {
        color: #ffc107 !important;
        border-bottom: 2px solid #ffc107 !important;
    }
    .main-content-wrapper {
        padding: 20px;
        min-height: calc(100vh - 56px);
        background-color: #f5f7fb;
    }
    .card {
        border-radius: 12px;
        border: none;
        box-shadow: 0 4px 12px rgba(0,0,0,0.08);
        transition: transform 0.2s ease;
    }
    .card:hover {
        transform: translateY(-2px);
        box-shadow: 0 6px 16px rgba(0,0,0,0.12);
    }
    .nav-tabs .nav-link {
        border-radius: 8px 8px 0 0;
        font-weight: 500;
    }
    .badge {
        font-size: 11px;
        padding: 3px 8px;
    }
    .partial-payment-row {
        border-left: 4px solid #ffc107;
    }
    .partial-payment-row:hover {
        background-color: #fff9e6 !important;
    }
    .balance-high {
        background-color: #f8d7da;
        color: #721c24;
        padding: 3px 8px;
        border-radius: 4px;
        font-weight: bold;
    }
    .balance-medium {
        background-color: #fff3cd;
        color: #856404;
        padding: 3px 8px;
        border-radius: 4px;
        font-weight: bold;
    }
    .balance-low {
        background-color: #d1ecf1;
        color: #0c5460;
        padding: 3px 8px;
        border-radius: 4px;
        font-weight: bold;
    }
    .clear-balance-btn {
        font-size: 12px;
        padding: 4px 10px;
    }
    .stat-box {
        padding: 8px;
        border-radius: 6px;
        background: #f8f9fa;
        border: 1px solid #e9ecef;
    }
    .stat-box small {
        font-size: 11px;
        text-transform: uppercase;
        letter-spacing: 0.5px;
    }
    .stat-box strong {
        font-size: 14px;
        color: #2c3e50;

    }
    .btn-group-sm .btn {
        font-size: 12px;
        padding: 2px 10px;
    }
    .btn-group-sm .btn.active {
        background-color: #007bff;
        color: white;
        border-color: #007bff;
    }
    @media (max-width: 768px) {
        .main-content-wrapper { padding: 15px 10px; }
        .card { margin-bottom: 15px; }
        .table-responsive { font-size: 14px; }
    }
    .modal-content {
        border-radius: 12px;
        border: none;
    }
    .modal-header {
        background-color: #f8f9fa;
        border-radius: 12px 12px 0 0;
    }
    .alert {
        border-radius: 8px;
        border: none;
    }
    #toast-container {
        position: fixed;
        top: 20px;
        right: 20px;
        z-index: 9999;
        min-width: 300px;
        max-width: 350px;
    }
    .auto-toast {
        background: white;
        border-radius: 8px;
        box-shadow: 0 4px 12px rgba(0,0,0,0.15);
        margin-bottom: 10px;
        overflow: hidden;
        animation: toastSlideIn 0.3s ease, toastFadeOut 0.5s ease 2.5s forwards;
        border-left: 4px solid #3498db;
    }
    @keyframes toastSlideIn {
        from { transform: translateX(100%); opacity: 0; }
        to { transform: translateX(0); opacity: 1; }
    }
    @keyframes toastFadeOut {
        from { opacity: 1; transform: translateX(0); }
        to { opacity: 0; transform: translateX(100%); }
    }
    .chart-container {
        position: relative;
        height: 300px;
        width: 100%;
    }
    .profit-positive {
        color: #28a745;
        font-weight: bold;
    }
    .profit-negative {
        color: #dc3545;
        font-weight: bold;
    }
    .purchase-info-card {
        background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
        color: white;
    }
    .sales-info-card {
        background: linear-gradient(135deg, #f093fb 0%, #f5576c 100%);
        color: white;
    }
    .stock-info-card {
        background: linear-gradient(135deg, #4facfe 0%, #00f2fe 100%);
        color: white;
    }
    .profit-info-card {
        background: linear-gradient(135deg, #43e97b 0%, #38f9d7 100%);
        color: white;
    }
    .info-card {
        color: white;
        border-radius: 10px;
        padding: 15px;
        margin-bottom: 15px;
    }
    .info-card h6 {
        color: white;
        opacity: 0.9;
        margin-bottom: 5px;
    }
    .info-card h4 {
        color: white;
        font-weight: bold;
        margin-bottom: 5px;
    }
    .info-card small {
        color: rgba(255,255,255,0.8);
        font-size: 12px;
    }
    </style>
</head>
<body class="sb-nav-fixed">
    <!-- MAIN CONTENT WRAPPER -->
    <div class="main-content-wrapper" id="mainContent">
        <div class="container-fluid">
            <!-- Header -->
            <div class="card p-3 mb-4 shadow-sm">
                <div class="d-flex justify-content-between align-items-center">
                    <div>
                        <h2>Welcome, <?php echo htmlspecialchars($user['fullname'] ?? ''); ?></h2>
                        <p class="mb-0">Role: <?php echo htmlspecialchars(ucfirst($user_role)); ?></p>
                    </div>
                    <?php if($user_role=='admin'): ?>
                    <div>
                        <form method="GET" class="form-inline">
                            <label class="mr-2">Shop</label>
                            <select name="shop_id" class="form-control mr-2">
                                <option value="0" <?php echo $selected_shop==0?'selected':''; ?>>All Shops</option>
                                <?php
                                $shops = $pdo->query("SELECT id,name FROM shops WHERE is_active = 1 ORDER BY name")->fetchAll(PDO::FETCH_ASSOC);
                                foreach($shops as $sh){
                                    echo '<option value="'.$sh['id'].'"'.($selected_shop==$sh['id']?' selected':'').'>'.htmlspecialchars($sh['name']).'</option>';
                                }
                                ?>
                            </select>
                            <button class="btn btn-primary">Filter</button>
                        </form>
                    </div>
                    <?php endif; ?>
                </div>
            </div>

            <!-- Toast Container -->
            <div id="toast-container"></div>

            <!-- Summary Cards -->
            <div class="row mb-4">
                <div class="col-md-3 mb-3">
                    <div class="info-card sales-info-card">
                        <h6><i class="fas fa-shopping-cart"></i> Sales Today</h6>
                        <h4>TSh <?php echo number_format($total_sales_today,2); ?></h4>
                        <small><?php echo date('d M Y'); ?></small>
                    </div>
                </div>
                <div class="col-md-3 mb-3">
                    <div class="info-card purchase-info-card">
                        <h6><i class="fas fa-box-open"></i> Purchases Today</h6>
                        <h4>TSh <?php echo number_format($total_purchases_today,2); ?></h4>
                        <small><?php echo $purchase_count_today; ?> purchase(s)</small>
                    </div>
                </div>
                <div class="col-md-3 mb-3">
                    <div class="info-card stock-info-card">
                        <h6><i class="fas fa-cubes"></i> Total Stock Units</h6>
                        <h4><?php echo number_format($total_stock_units); ?></h4>
                        <?php if($expired_stock_units > 0): ?>
                        <small class="text-warning"><?php echo number_format($expired_stock_units); ?> expired</small>
                        <?php else: ?>
                        <small>All stock valid</small>
                        <?php endif; ?>
                    </div>
                </div>
                <div class="col-md-3 mb-3">
                    <div class="info-card profit-info-card">
                        <h6><i class="fas fa-chart-line"></i> 30-Day Profit</h6>
                        <h4 class="<?php echo $gross_profit_30days >= 0 ? 'profit-positive' : 'profit-negative'; ?>">
                            TSh <?php echo number_format($gross_profit_30days,2); ?>
                        </h4>
                        <small>Margin: <?php echo number_format($gross_margin_30days,1); ?>%</small>
                    </div>
                </div>
            </div>

            <!-- Second Row Summary Cards -->
            <div class="row mb-4">
                <div class="col-md-3 mb-3">
                    <div class="card p-3 h-100">
                        <h6><i class="fas fa-calendar-alt text-info"></i> Sales This Month</h6>
                        <h4>TSh <?php echo number_format($total_sales_month,2); ?></h4>
                        <small class="text-muted"><?php echo date('M Y'); ?></small>
                    </div>
                </div>
                <div class="col-md-3 mb-3">
                    <div class="card p-3 h-100">
                        <h6><i class="fas fa-calendar-alt text-primary"></i> Purchases This Month</h6>
                        <h4>TSh <?php echo number_format($total_purchases_month,2); ?></h4>
                        <small class="text-muted"><?php echo date('M Y'); ?></small>
                    </div>
                </div>
                <div class="col-md-3 mb-3">
                    <div class="card p-3 h-100">
                        <h6><i class="fas fa-money-bill-wave text-danger"></i> Outstanding Balance</h6>
                        <h4 class="<?php echo $total_outstanding > 0 ? 'text-danger' : 'text-success'; ?>">
                            TSh <?php echo number_format($total_outstanding,2); ?>
                        </h4>
                        <small><?php echo count($partial_payments); ?> pending sale(s)</small>
                    </div>
                </div>
                <div class="col-md-3 mb-3">
                    <div class="card p-3 h-100">
                        <h6><i class="fas fa-coins text-success"></i> Expected Profit</h6>
                        <h4>TSh <?php echo number_format($expected_profit,2); ?></h4>
                        <small class="text-muted">From current stock</small>
                    </div>
                </div>
            </div>

            <!-- Sales vs Purchases Chart Last 14 Days -->
            <div class="row mb-4">
                <div class="col-md-12">
                    <div class="card p-3">
                        <h5><i class="fas fa-chart-bar"></i> Sales vs Purchases (Last 14 Days)</h5>
                        <div class="chart-container">
                            <canvas id="comparisonChart"></canvas>
                        </div>
                        <div class="row text-center mt-3">
                            <div class="col-md-2 mb-2">
                                <div class="stat-box">
                                    <small class="text-muted d-block">Total Sales</small>
                                    <strong><?php echo number_format($total_period_sales,2); ?></strong>
                                </div>
                            </div>
                            <div class="col-md-2 mb-2">
                                <div class="stat-box">
                                    <small class="text-muted d-block">Total Purchases</small>
                                    <strong><?php echo number_format($total_period_purchases,2); ?></strong>
                                </div>
                            </div>
                            <div class="col-md-2 mb-2">
                                <div class="stat-box">
                                    <small class="text-muted d-block">Avg Daily Sales</small>
                                    <strong><?php echo number_format($avg_sales,2); ?></strong>
                                </div>
                            </div>
                            <div class="col-md-2 mb-2">
                                <div class="stat-box">
                                    <small class="text-muted d-block">Avg Daily Purchases</small>
                                    <strong><?php echo number_format($avg_purchases,2); ?></strong>
                                </div>
                            </div>
                            <div class="col-md-2 mb-2">
                                <div class="stat-box">
                                    <small class="text-muted d-block">Max Sale Day</small>
                                    <strong><?php echo number_format($max_sale,2); ?></strong>
                                </div>
                            </div>
                            <div class="col-md-2 mb-2">
                                <div class="stat-box">
                                    <small class="text-muted d-block">Max Purchase Day</small>
                                    <strong><?php echo number_format($max_purchase,2); ?></strong>
                                </div>
                            </div>
                        </div>
                        <div class="mt-2 text-center">
                            <small class="text-muted">
                                <i class="fas fa-info-circle"></i> 
                                Showing data from <?php echo date('d M', strtotime($start_date)); ?> to <?php echo date('d M Y', strtotime($today)); ?>
                            </small>
                        </div>
                    </div>
                </div>
            </div>

            <!-- Top Selling Products -->
            <?php if(!empty($top_selling)): ?>
            <div class="row mb-4">
                <div class="col-md-6">
                    <div class="card p-3 h-100">
                        <h5><i class="fas fa-chart-line text-success"></i> Top Selling Products (Last 30 Days)</h5>
                        <div class="table-responsive">
                            <table class="table table-sm table-hover">
                                <thead class="thead-light">
                                    <tr>
                                        <th>Product</th>
                                        <th>SKU</th>
                                        <th class="text-center">Units Sold</th>
                                        <th class="text-right">Revenue</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php foreach($top_selling as $product): ?>
                                    <tr>
                                        <td><?php echo htmlspecialchars($product['name']); ?></td>
                                        <td><?php echo htmlspecialchars($product['sku']); ?></td>
                                        <td class="text-center">
                                            <span class="badge badge-primary"><?php echo number_format($product['total_sold']); ?></span>
                                        </td>
                                        <td class="text-right">
                                            <strong>TSh <?php echo number_format($product['total_revenue'], 2); ?></strong>
                                        </td>
                                    </tr>
                                    <?php endforeach; ?>
                                </tbody>
                            </table>
                        </div>
                    </div>
                </div>
                <div class="col-md-6">
                    <div class="card p-3 h-100">
                        <h5><i class="fas fa-boxes text-info"></i> Recent Purchases (Last 7 Days)</h5>
                        <div class="table-responsive">
                            <table class="table table-sm table-hover">
                                <thead class="thead-light">
                                    <tr>
                                        <th>Invoice</th>
                                        <th>Date</th>
                                        <th>Supplier</th>
                                        <th class="text-right">Amount</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php if(empty($recent_purchases)): ?>
                                    <tr>
                                        <td colspan="4" class="text-center text-muted">No recent purchases</td>
                                    </tr>
                                    <?php else: 
                                    foreach($recent_purchases as $purchase):
                                    ?>
                                    <tr>
                                        <td>
                                            <a href="purchase_view.php?id=<?php echo $purchase['id']; ?>" target="_blank">
                                                <?php echo htmlspecialchars($purchase['invoice_no'] ?? 'N/A'); ?>
                                            </a>
                                        </td>
                                        <td><?php echo date('d-M-y', strtotime($purchase['purchase_date'])); ?></td>
                                        <td><?php echo htmlspecialchars($purchase['supplier_name'] ?? 'Direct'); ?></td>
                                        <td class="text-right">
                                            <strong>TSh <?php echo number_format($purchase['total'], 2); ?></strong>
                                        </td>
                                    </tr>
                                    <?php endforeach; endif; ?>
                                </tbody>
                            </table>
                        </div>
                    </div>
                </div>
            </div>
            <?php endif; ?>

            <!-- Partial Payments Section -->
            <?php if(!empty($partial_payments)): ?>
            <div class="row mb-4">
                <div class="col-md-12">
                    <div class="card p-3">
                        <h5 class="d-flex justify-content-between align-items-center">
                            <span><i class="fas fa-credit-card"></i> Partial Payments / Credit Sales</span>
                            <span class="badge badge-warning">Total Outstanding: TSh <?php echo number_format($total_outstanding,2); ?></span>
                        </h5>
                        
                        <div class="table-responsive">
                            <table class="table table-hover table-bordered">
                                <thead class="thead-light">
                                    <tr>
                                        <th>Invoice #</th>
                                        <th>Date</th>
                                        <th>Customer</th>
                                        <th>Contact</th>
                                        <th>Total Amount</th>
                                        <th>Amount Paid</th>
                                        <th>Balance</th>
                                        <th>Cashier</th>
                                        <th>Status</th>
                                        <th>Actions</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php foreach($partial_payments as $payment): 
                                        $balance = $payment['balance'];
                                        $balance_class = '';
                                        if ($balance > 10000) {
                                            $balance_class = 'balance-high';
                                        } elseif ($balance > 5000) {
                                            $balance_class = 'balance-medium';
                                        } else {
                                            $balance_class = 'balance-low';
                                        }
                                    ?>
                                    <tr class="partial-payment-row" id="payment-row-<?php echo $payment['id']; ?>">
                                        <td>
                                            <a href="sales_view.php?id=<?php echo $payment['id']; ?>" target="_blank">
                                                <?php echo htmlspecialchars($payment['invoice_no'] ?? ''); ?>
                                            </a>
                                        </td>
                                        <td><?php echo date('d-M-Y', strtotime($payment['sale_date'])); ?></td>
                                        <td><?php echo htmlspecialchars($payment['customer_name'] ?? 'Walk-in Customer'); ?></td>
                                        <td><?php echo htmlspecialchars($payment['customer_phone'] ?? ''); ?></td>
                                        <td class="text-right">TSh <?php echo number_format($payment['total'],2); ?></td>
                                        <td class="text-right text-success paid-amount">TSh <?php echo number_format($payment['paid'],2); ?></td>
                                        <td class="text-right font-weight-bold balance-amount">
                                            <span class="<?php echo $balance_class; ?>">TSh <?php echo number_format($balance,2); ?></span>
                                        </td>
                                        <td><?php echo htmlspecialchars($payment['cashier'] ?? ''); ?></td>
                                        <td>
                                            <span class="badge badge-<?php echo $payment['status']=='partially_paid'?'warning':'secondary'; ?>">
                                                <?php echo str_replace('_', ' ', ucfirst($payment['status'])); ?>
                                            </span>
                                        </td>
                                        <td>
                                            <div class="btn-group btn-group-sm">
                                                <button class="btn btn-success clear-balance-btn" 
                                                        data-toggle="modal" 
                                                        data-target="#clearBalanceModal"
                                                        data-sale-id="<?php echo $payment['id']; ?>"
                                                        data-invoice-no="<?php echo htmlspecialchars($payment['invoice_no'] ?? ''); ?>"
                                                        data-customer="<?php echo htmlspecialchars($payment['customer_name'] ?? 'Walk-in Customer'); ?>"
                                                        data-balance="<?php echo $balance; ?>"
                                                        data-total="<?php echo $payment['total']; ?>"
                                                        data-paid="<?php echo $payment['paid']; ?>"
                                                        title="Record Payment">
                                                    <i class="fas fa-money-check-alt"></i>
                                                </button>
                                                <button class="btn btn-info print-receipt-btn" 
                                                        data-sale-id="<?php echo $payment['id']; ?>"
                                                        title="Print Receipt">
                                                    <i class="fas fa-print"></i>
                                                </button>
                                            </div>
                                        </td>
                                    </tr>
                                    <?php endforeach; ?>
                                </tbody>
                                <tfoot class="table-warning">
                                    <tr>
                                        <td colspan="5" class="text-right"><strong>Totals:</strong></td>
                                        <td class="text-right"><strong>TSh <?php echo number_format(array_sum(array_column($partial_payments, 'paid')),2); ?></strong></td>
                                        <td class="text-right"><strong class="text-danger">TSh <?php echo number_format($total_outstanding,2); ?></strong></td>
                                        <td colspan="3"></td>
                                    </tr>
                                </tfoot>
                            </table>
                        </div>
                    </div>
                </div>
            </div>
            <?php endif; ?>

            <!-- Recent Sales and Stock Info -->
            <div class="row">
                <div class="col-md-8">
                    <!-- Recent Sales -->
                    <div class="card p-3 mb-3">
                        <h5><i class="fas fa-receipt"></i> Recent Sales (Past 7 Days)</h5>
                        <div class="table-responsive">
                            <table class="table table-sm table-bordered">
                                <thead>
                                    <tr>
                                        <th>Invoice</th>
                                        <th>Date</th>
                                        <th>Cashier</th>
                                        <th>Total</th>
                                        <th>Status</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php
                                    $grand_total = 0;
                                    if(!$recent_sales): ?>
                                    <tr><td colspan="5" class="text-center text-muted">No recent sales</td></tr>
                                    <?php else:
                                    foreach($recent_sales as $s):
                                        $grand_total += $s['total'];
                                        $paid = $s['paid'] ?? 0; // FIX: Added default value for paid
                                    ?>
                                    <tr>
                                        <td>
                                            <a href="sales_view.php?id=<?php echo $s['id']; ?>" target="_blank">
                                                <?php echo htmlspecialchars($s['invoice_no'] ?? ''); ?>
                                            </a>
                                        </td>
                                        <td><?php echo date('d-M-Y', strtotime($s['sale_date'])); ?></td>
                                        <td><?php echo htmlspecialchars($s['cashier'] ?? ''); ?></td>
                                        <td class="text-right">TSh <?php echo number_format($s['total'],2); ?></td>
                                        <td>
                                            <?php 
                                            $status_color = 'success';
                                            if ($s['total'] > $paid) { // FIX: Changed from $s['paid'] to $paid
                                                $status_color = 'warning';
                                            }
                                            ?>
                                            <span class="badge badge-<?php echo $status_color; ?>">
                                                <?php echo $s['total'] > $paid ? 'Partial' : 'Paid'; ?> <!-- FIX: Changed here too -->
                                            </span>
                                        </td>
                                    </tr>
                                    <?php endforeach; ?>
                                    <tr class="font-weight-bold table-light">
                                        <td colspan="3" class="text-right">Grand Total</td>
                                        <td class="text-right">TSh <?php echo number_format($grand_total,2); ?></td>
                                        <td></td>
                                    </tr>
                                    <?php endif; ?>
                                </tbody>
                            </table>
                        </div>
                    </div>
                </div>

                <div class="col-md-4">
                    <!-- Low Stock -->
                    <div class="card p-3 mb-3">
                        <h5><i class="fas fa-exclamation-triangle text-danger"></i> Low Stock Products</h5>
                        <div style="max-height:200px; overflow:auto;">
                            <table class="table table-sm table-bordered">
                                <thead>
                                    <tr>
                                        <th>Product</th>
                                        <th>SKU</th>
                                        <th>Qty</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php if(!$low_stock): ?>
                                    <tr><td colspan="3" class="text-center text-muted">No low stock products</td></tr>
                                    <?php else: 
                                    foreach($low_stock as $p): ?>
                                    <tr>
                                        <td><?php echo htmlspecialchars($p['name'] ?? ''); ?></td>
                                        <td><?php echo htmlspecialchars($p['sku'] ?? ''); ?></td>
                                        <td class="<?php echo $p['total_qty'] <= 5 ? 'text-danger font-weight-bold' : 'text-warning'; ?>">
                                            <?php echo intval($p['total_qty']); ?>
                                        </td>
                                    </tr>
                                    <?php endforeach; endif; ?>
                                </tbody>
                            </table>
                        </div>
                    </div>

                    <!-- Stock Expiry Status -->
                    <div class="card p-3">
                        <h5><i class="fas fa-clock"></i> Stock Expiry Status</h5>
                        
                        <!-- Tab Navigation -->
                        <ul class="nav nav-tabs mb-3" id="expiryTabs" role="tablist">
                            <li class="nav-item">
                                <a class="nav-link active near-expiry-tab" id="near-tab" data-toggle="tab" href="#near" role="tab">
                                    <i class="fas fa-exclamation-triangle"></i> Near Expiry <span class="badge badge-warning"><?php echo count($near_expiry); ?></span>
                                </a>
                            </li>
                            <li class="nav-item">
                                <a class="nav-link expired-tab" id="expired-tab" data-toggle="tab" href="#expired" role="tab">
                                    <i class="fas fa-skull-crossbones"></i> Expired <span class="badge badge-danger"><?php echo $expired_total_items; ?></span>
                                </a>
                            </li>
                        </ul>
                        
                        <!-- Tab Content -->
                        <div class="tab-content" id="expiryTabContent">
                            
                            <!-- Near Expiry Tab -->
                            <div class="tab-pane fade show active" id="near" role="tabpanel">
                                <div class="alert alert-warning mb-2 py-2">
                                    <small><i class="fas fa-exclamation-triangle"></i> Products expiring within 90 days</small>
                                </div>
                                <?php if(empty($near_expiry)): ?>
                                    <div class="text-center text-muted py-3">No products near expiry</div>
                                <?php else: ?>
                                <div style="max-height:200px; overflow:auto;">
                                    <table class="table table-sm table-bordered">
                                        <thead>
                                            <tr>
                                                <th>Product</th>
                                                <th>Batch</th>
                                                <th>Qty</th>
                                                <th>Expires In</th>
                                            </tr>
                                        </thead>
                                        <tbody>
                                        <?php foreach($near_expiry as $b): 
                                            $urgency_class = $b['days_until_expiry'] <= 30 ? 'text-danger' : 'text-warning';
                                        ?>
                                        <tr class="near-expiry-row">
                                            <td><?php echo htmlspecialchars($b['product_name'] ?? ''); ?></td>
                                            <td><?php echo htmlspecialchars($b['batch_no'] ?? ''); ?></td>
                                            <td><?php echo intval($b['qty']); ?></td>
                                            <td>
                                                <span class="badge badge-warning <?php echo $urgency_class; ?>">
                                                    <?php echo $b['days_until_expiry']; ?> days
                                                </span>
                                            </td>
                                        </tr>
                                        <?php endforeach; ?>
                                        </tbody>
                                    </table>
                                </div>
                                <?php endif; ?>
                            </div>
                            
                            <!-- Expired Tab -->
                            <div class="tab-pane fade" id="expired" role="tabpanel">
                                <div class="alert alert-danger mb-2 py-2">
                                    <small><i class="fas fa-skull-crossbones"></i> Expired products (not included in stock count)</small>
                                </div>
                                <?php if(empty($expired_products)): ?>
                                    <div class="text-center text-muted py-3">No expired products</div>
                                <?php else: ?>
                                <div style="max-height:200px; overflow:auto;">
                                    <table class="table table-sm table-bordered">
                                        <thead>
                                            <tr>
                                                <th>Product</th>
                                                <th>Batch</th>
                                                <th>Qty</th>
                                                <th>Expired</th>
                                                <th>Value Lost</th>
                                            </tr>
                                        </thead>
                                        <tbody>
                                        <?php foreach($expired_products as $b): 
                                            $days_expired = $b['days_expired'];
                                            $value_lost = $b['qty'] * $b['cost_price'];
                                            $urgency_class = $days_expired > 90 ? 'bg-dark text-white' : '';
                                        ?>
                                        <tr class="expired-row">
                                            <td><?php echo htmlspecialchars($b['product_name'] ?? ''); ?></td>
                                            <td><?php echo htmlspecialchars($b['batch_no'] ?? ''); ?></td>
                                            <td><?php echo intval($b['qty']); ?></td>
                                            <td>
                                                <span class="badge badge-danger <?php echo $urgency_class; ?>">
                                                    <?php echo $days_expired; ?> days ago
                                                </span>
                                            </td>
                                            <td class="text-danger font-weight-bold">TSh <?php echo number_format($value_lost, 2); ?></td>
                                        </tr>
                                        <?php endforeach; ?>
                                        </tbody>
                                        <tfoot class="table-danger">
                                            <tr>
                                                <td colspan="2" class="text-right"><strong>Total Lost:</strong></td>
                                                <td><strong><?php echo $expired_total_qty; ?></strong></td>
                                                <td colspan="2" class="text-danger"><strong>TSh <?php echo number_format($expired_total_value, 2); ?></strong></td>
                                            </tr>
                                        </tfoot>
                                    </table>
                                </div>
                                <?php endif; ?>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>

    <!-- Clear Balance Modal -->
    <div class="modal fade" id="clearBalanceModal" tabindex="-1" role="dialog" aria-labelledby="clearBalanceModalLabel" aria-hidden="true">
        <div class="modal-dialog" role="document">
            <div class="modal-content">
                <div class="modal-header bg-warning text-white">
                    <h5 class="modal-title" id="clearBalanceModalLabel">Record Payment</h5>
                    <button type="button" class="close" data-dismiss="modal" aria-label="Close">
                        <span aria-hidden="true">&times;</span>
                    </button>
                </div>
                <form id="clearBalanceForm">
                    <div class="modal-body">
                        <input type="hidden" name="sale_id" id="modalSaleId">
                        <input type="hidden" name="shop_id" value="<?php echo $selected_shop; ?>">
                        
                        <div class="alert alert-info">
                            <i class="fas fa-info-circle"></i> Record a payment to clear or reduce the outstanding balance.
                        </div>
                        
                        <div class="form-group">
                            <label>Invoice Number</label>
                            <input type="text" class="form-control" id="modalInvoiceNo" readonly>
                        </div>
                        
                        <div class="form-group">
                            <label>Customer</label>
                            <input type="text" class="form-control" id="modalCustomer" readonly>
                        </div>
                        
                        <div class="row">
                            <div class="col-md-6">
                                <div class="form-group">
                                    <label>Total Amount</label>
                                    <input type="text" class="form-control" id="modalTotal" readonly>
                                </div>
                            </div>
                            <div class="col-md-6">
                                <div class="form-group">
                                    <label>Amount Paid</label>
                                    <input type="text" class="form-control" id="modalPaid" readonly>
                                </div>
                            </div>
                        </div>
                        
                        <div class="form-group">
                            <label>Current Balance</label>
                            <input type="text" class="form-control font-weight-bold text-danger" id="modalBalance" readonly>
                        </div>
                        
                        <div class="form-group">
                            <label>Payment Amount *</label>
                            <input type="number" name="amount" id="modalPaymentAmount" 
                                   class="form-control" step="0.01" min="0.01" 
                                   placeholder="Enter payment amount" required>
                            <small class="form-text text-muted">Enter the amount being paid now.</small>
                        </div>
                        
                        <div class="form-group">
                            <label>Payment Method</label>
                            <select name="payment_method" id="modalPaymentMethod" class="form-control" required>
                                <option value="CASH">Cash</option>
                                <option value="M-PESA">M-PESA</option>
                                <option value="TIGO-PESA">Tigo Pesa</option>
                                <option value="AIRTEL-MONEY">Airtel Money</option>
                                <option value="CARD">Card</option>
                            </select>
                        </div>
                        
                        <div class="form-group">
                            <label>New Balance After Payment</label>
                            <input type="text" class="form-control font-weight-bold" id="modalNewBalance" readonly>
                        </div>
                        
                        <div class="form-check">
                            <input type="checkbox" class="form-check-input" id="printAfterPayment" checked>
                            <label class="form-check-label" for="printAfterPayment">
                                <i class="fas fa-print"></i> Print receipt after payment
                            </label>
                        </div>
                    </div>
                    <div class="modal-footer">
                        <button type="button" class="btn btn-secondary" data-dismiss="modal">Cancel</button>
                        <button type="submit" class="btn btn-success">
                            <i class="fas fa-check-circle"></i> Record Payment
                        </button>
                    </div>
                </form>
            </div>
        </div>
    </div>

    <!-- jQuery and Bootstrap JS -->
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
    <script>
    // Wait for the DOM to be fully loaded
    document.addEventListener('DOMContentLoaded', function() {
        // Check if sidebar is already toggled from localStorage
        if (localStorage.getItem('sb|sidebar-toggle') === 'true') {
            document.body.classList.add('sb-sidenav-toggled');
        }
        
        // Sidebar toggle functionality
        var sidebarToggle = document.getElementById('sidebarToggle');
        if (sidebarToggle) {
            sidebarToggle.addEventListener('click', function(e) {
                e.preventDefault();
                document.body.classList.toggle('sb-sidenav-toggled');
                localStorage.setItem('sb|sidebar-toggle', document.body.classList.contains('sb-sidenav-toggled'));
            });
        }
        
        // Initialize Comparison Chart
        function initComparisonChart() {
            try {
                // Check if Chart.js is loaded
                if (typeof Chart === 'undefined') {
                    console.error('Chart.js library not loaded!');
                    var chartContainer = document.getElementById('comparisonChart');
                    if (chartContainer) {
                        chartContainer.parentElement.innerHTML = '<div class="alert alert-warning">Chart library failed to load. Please check your internet connection.</div>';
                    }
                    return;
                }
                
                var canvas = document.getElementById('comparisonChart');
                if (!canvas) {
                    console.error('Canvas element not found');
                    return;
                }
                
                var ctx = canvas.getContext('2d');
                
                // Get data from PHP
                var chartLabels = <?php echo json_encode($chart_labels, JSON_HEX_TAG | JSON_HEX_APOS | JSON_HEX_QUOT | JSON_HEX_AMP); ?>;
                var salesChartData = <?php echo json_encode($sales_chart_data, JSON_HEX_TAG | JSON_HEX_APOS | JSON_HEX_QUOT | JSON_HEX_AMP); ?>;
                var purchaseChartData = <?php echo json_encode($purchase_chart_data, JSON_HEX_TAG | JSON_HEX_APOS | JSON_HEX_QUOT | JSON_HEX_AMP); ?>;
                
                // Create comparison chart (bar chart with two datasets)
                var comparisonChart = new Chart(ctx, {
                    type: 'bar',
                    data: {
                        labels: chartLabels,
                        datasets: [
                            {
                                label: 'Sales',
                                data: salesChartData,
                                backgroundColor: 'rgba(54, 162, 235, 0.7)',
                                borderColor: 'rgba(54, 162, 235, 1)',
                                borderWidth: 1,
                                borderRadius: 4,
                                borderSkipped: false,
                            },
                            {
                                label: 'Purchases',
                                data: purchaseChartData,
                                backgroundColor: 'rgba(255, 99, 132, 0.7)',
                                borderColor: 'rgba(255, 99, 132, 1)',
                                borderWidth: 1,
                                borderRadius: 4,
                                borderSkipped: false,
                            }
                        ]
                    },
                    options: {
                        responsive: true,
                        maintainAspectRatio: false,
                        plugins: {
                            legend: {
                                display: true,
                                position: 'top',
                            },
                            tooltip: {
                                mode: 'index',
                                intersect: false,
                                callbacks: {
                                    label: function(context) {
                                        return context.dataset.label + ': TSh ' + context.parsed.y.toFixed(2);
                                    }
                                }
                            }
                        },
                        scales: {
                            x: {
                                grid: {
                                    display: false
                                }
                            },
                            y: {
                                beginAtZero: true,
                                grid: {
                                    color: 'rgba(0,0,0,0.05)'
                                },
                                ticks: {
                                    callback: function(value) {
                                        return 'TSh ' + value.toLocaleString('en-TZ');
                                    }
                                }
                            }
                        },
                        interaction: {
                            intersect: false,
                            mode: 'nearest'
                        }
                    }
                });
                
                // Refresh chart on window resize
                window.addEventListener('resize', function() {
                    if (comparisonChart) {
                        comparisonChart.resize();
                    }
                });
                
            } catch (error) {
                console.error('Error initializing chart:', error);
                // Show error message in chart container
                var chartContainer = document.getElementById('comparisonChart');
                if (chartContainer) {
                    chartContainer.parentElement.innerHTML = '<div class="alert alert-danger">Error loading chart: ' + error.message + '</div>';
                }
            }
        }

        // Initialize chart
        initComparisonChart();

        // Initialize Bootstrap tabs - using vanilla JS since jQuery might not be loaded yet
        var expiryTabs = document.querySelectorAll('#expiryTabs a');
        expiryTabs.forEach(function(tab) {
            tab.addEventListener('click', function(e) {
                e.preventDefault();
                // Remove active class from all tabs
                expiryTabs.forEach(function(t) {
                    t.classList.remove('active');
                });
                // Add active class to clicked tab
                this.classList.add('active');
                
                // Show the corresponding tab content
                var targetId = this.getAttribute('href').substring(1);
                var tabContents = document.querySelectorAll('#expiryTabContent .tab-pane');
                tabContents.forEach(function(content) {
                    content.classList.remove('show', 'active');
                });
                var targetContent = document.getElementById(targetId);
                if (targetContent) {
                    targetContent.classList.add('show', 'active');
                }
            });
        });
        
        // Toast notification function (vanilla JS)
        function showToast(message, type = 'info') {
            var toastId = 'toast-' + Date.now();
            var bgColor = {
                'success': '#28a745',
                'warning': '#ffc107',
                'error': '#dc3545',
                'info': '#17a2b8'
            }[type] || '#17a2b8';
            
            var icon = {
                'success': 'fas fa-check-circle',
                'warning': 'fas fa-exclamation-triangle',
                'error': 'fas fa-times-circle',
                'info': 'fas fa-info-circle'
            }[type] || 'fas fa-info-circle';
            
            var toast = document.createElement('div');
            toast.id = toastId;
            toast.className = 'auto-toast';
            toast.style.borderLeftColor = bgColor;
            toast.innerHTML = `
                <div style="padding: 12px 15px; display: flex; align-items: center;">
                    <i class="${icon} mr-2" style="color: ${bgColor}; font-size: 1.2em;"></i>
                    <span style="flex: 1;">${message}</span>
                    <button type="button" class="close ml-2" 
                            style="color: #888; font-size: 1.5em; line-height: 1; border: none; background: none; cursor: pointer;"
                            onclick="document.getElementById('${toastId}').remove()">
                        <span>&times;</span>
                    </button>
                </div>
            `;
            
            var toastContainer = document.getElementById('toast-container');
            if (toastContainer) {
                toastContainer.appendChild(toast);
                
                setTimeout(function() {
                    var toastElement = document.getElementById(toastId);
                    if (toastElement) {
                        toastElement.remove();
                    }
                }, 3000);
            }
        }

        // Clear Balance Modal functionality
        $(document).ready(function() {
            // Clear Balance Modal functionality
            $('#clearBalanceModal').on('show.bs.modal', function (event) {
                var button = $(event.relatedTarget);
                var saleId = button.data('sale-id');
                var invoiceNo = button.data('invoice-no');
                var customer = button.data('customer');
                var balance = parseFloat(button.data('balance'));
                var total = parseFloat(button.data('total'));
                var paid = parseFloat(button.data('paid'));
                
                var modal = $(this);
                modal.find('#modalSaleId').val(saleId);
                modal.find('#modalInvoiceNo').val(invoiceNo);
                modal.find('#modalCustomer').val(customer);
                modal.find('#modalTotal').val('TSh ' + total.toFixed(2));
                modal.find('#modalPaid').val('TSh ' + paid.toFixed(2));
                modal.find('#modalBalance').val('TSh ' + balance.toFixed(2));
                
                // Set max payment amount to current balance
                modal.find('#modalPaymentAmount').attr('max', balance);
                modal.find('#modalPaymentAmount').val(balance.toFixed(2));
                
                // Calculate new balance
                calculateNewBalance();
            });

            // Calculate new balance when payment amount changes
            $('#modalPaymentAmount').on('input', function() {
                calculateNewBalance();
            });

            function calculateNewBalance() {
                var balance = parseFloat($('#modalBalance').val().replace('TSh ', '').replace(',', ''));
                var payment = parseFloat($('#modalPaymentAmount').val()) || 0;
                var newBalance = balance - payment;
                
                if (newBalance < 0) {
                    newBalance = 0;
                    $('#modalPaymentAmount').val(balance.toFixed(2));
                    payment = balance;
                }
                
                $('#modalNewBalance').val('TSh ' + newBalance.toFixed(2));
                
                // Change color based on new balance
                if (newBalance === 0) {
                    $('#modalNewBalance').removeClass('text-warning text-danger').addClass('text-success');
                } else if (newBalance > 0 && newBalance <= 5000) {
                    $('#modalNewBalance').removeClass('text-success text-danger').addClass('text-warning');
                } else if (newBalance > 5000) {
                    $('#modalNewBalance').removeClass('text-success text-warning').addClass('text-danger');
                }
            }

            // Handle form submission via AJAX
            $('#clearBalanceForm').on('submit', function(e) {
                e.preventDefault();
                
                var formData = $(this).serialize();
                var saleId = $('#modalSaleId').val();
                var printReceipt = $('#printAfterPayment').is(':checked');
                
                $.ajax({
                    url: 'process_payment.php',
                    method: 'POST',
                    data: formData,
                    dataType: 'json',
                    beforeSend: function() {
                        $('.modal-footer button').prop('disabled', true);
                        $('.modal-footer button').html('<i class="fas fa-spinner fa-spin"></i> Processing...');
                    },
                    success: function(response) {
                        if (response.success) {
                            // Update the table row
                            var row = $('#payment-row-' + saleId);
                            row.find('.paid-amount').text('TSh ' + response.new_paid);
                            row.find('.balance-amount').html('<span class="' + getBalanceClass(response.new_balance) + '">TSh ' + response.new_balance + '</span>');
                            
                            // Update status badge
                            if (response.new_status === 'paid') {
                                row.find('.badge').removeClass('badge-warning badge-secondary').addClass('badge-success').text('Paid');
                                row.hide('slow', function() {
                                    $(this).remove();
                                    // Update total outstanding
                                    updateTotalOutstanding();
                                });

                            } else {
                                row.find('.badge').removeClass('badge-secondary').addClass('badge-warning').text('Partially Paid');
                            }
                            
                            // Close modal
                            $('#clearBalanceModal').modal('hide');
                            
                            // Show success message
                            showToast(response.message, 'success');
                            
                            // Print receipt if requested
                            if (printReceipt && response.sale_id) {
                                setTimeout(function() {
                                    window.open('print_receipt_thermal.php?sale_id=' + response.sale_id, '_blank');
                                }, 1000);
                            }
                        } else {
                            showToast(response.message || 'Error processing payment', 'error');
                        }
                    },
                    error: function(xhr, status, error) {
                        showToast('Error: ' + error, 'error');
                    },
                    complete: function() {
                        $('.modal-footer button').prop('disabled', false);
                        $('.modal-footer button').html('<i class="fas fa-check-circle"></i> Record Payment');
                    }
                });
            });

            function getBalanceClass(balance) {
                balance = parseFloat(balance);
                if (balance > 10000) return 'balance-high';
                if (balance > 5000) return 'balance-medium';
                return 'balance-low';
            }

            function updateTotalOutstanding() {
                // Recalculate total outstanding from remaining rows
                var total = 0;
                $('.balance-amount').each(function() {
                    var balanceText = $(this).text().replace('TSh ', '').replace(/[^\d.-]/g, '');
                    var balance = parseFloat(balanceText) || 0;
                    total += balance;
                });
                
                // Update the total outstanding badge
                $('.badge-warning:contains("Total Outstanding")').text('Total Outstanding: TSh ' + total.toFixed(2));
                
                // Update the card value
                $('.card:contains("Outstanding Balance") h4').text('TSh ' + total.toFixed(2));
                
                // Update color
                if (total > 0) {
                    $('.card:contains("Outstanding Balance") h4').removeClass('text-success').addClass('text-danger');
                } else {
                    $('.card:contains("Outstanding Balance") h4').removeClass('text-danger').addClass('text-success');
                }
            }

            // Print receipt button
            $('.print-receipt-btn').on('click', function() {
                var saleId = $(this).data('sale-id');
                if (saleId) {
                    window.open('print_receipt_thermal.php?sale_id=' + saleId, '_blank');
                }
            });
        });

        // Auto-dismiss alerts after 5 seconds
        setTimeout(function() {
            var alerts = document.querySelectorAll('.alert');
            alerts.forEach(function(alert) {
                var closeButton = alert.querySelector('.close');
                if (closeButton) {
                    closeButton.click();
                }
            });
        }, 5000);
    });
    </script>

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