<?php
// dashboard.php
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
function shopFilterSQL($selected_shop, &$params, $alias='s') {
    if ($selected_shop > 0) { $params[] = $selected_shop; return " AND $alias.shop_id = ? "; }
    return "";
}

// 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')); // last 7 days including today

// --------- TOTAL SALES ---------
$params = [$today];
$sql = "SELECT COALESCE(SUM(total),0) FROM sales s WHERE DATE(s.sale_date)=?";
$sql .= shopFilterSQL($selected_shop, $params);
$stmt = $pdo->prepare($sql); $stmt->execute($params); $total_sales_today = (float)$stmt->fetchColumn();

$params = [$month_start, $today];
$sql = "SELECT COALESCE(SUM(total),0) FROM sales s WHERE DATE(s.sale_date) BETWEEN ? AND ?";
$sql .= shopFilterSQL($selected_shop, $params);
$stmt = $pdo->prepare($sql); $stmt->execute($params); $total_sales_month = (float)$stmt->fetchColumn();

$params = [$year_start, $today];
$sql = "SELECT COALESCE(SUM(total),0) FROM sales s WHERE DATE(s.sale_date) BETWEEN ? AND ?";
$sql .= shopFilterSQL($selected_shop, $params);
$stmt = $pdo->prepare($sql); $stmt->execute($params); $total_sales_year = (float)$stmt->fetchColumn();

// --------- TOTAL PURCHASES ---------
$params = [$today];
$sql = "SELECT COALESCE(SUM(total),0) FROM purchases p WHERE DATE(p.purchase_date)=?";
$sql .= shopFilterSQL($selected_shop, $params, 'p');
$stmt = $pdo->prepare($sql); $stmt->execute($params); $total_purchases_today = (float)$stmt->fetchColumn();

$params = [$month_start, $today];
$sql = "SELECT COALESCE(SUM(total),0) FROM purchases p WHERE DATE(p.purchase_date) BETWEEN ? AND ?";
$sql .= shopFilterSQL($selected_shop, $params, 'p');
$stmt = $pdo->prepare($sql); $stmt->execute($params); $total_purchases_month = (float)$stmt->fetchColumn();

$params = [$year_start, $today];
$sql = "SELECT COALESCE(SUM(total),0) FROM purchases p WHERE DATE(p.purchase_date) BETWEEN ? AND ?";
$sql .= shopFilterSQL($selected_shop, $params, 'p');
$stmt = $pdo->prepare($sql); $stmt->execute($params); $total_purchases_year = (float)$stmt->fetchColumn();

// --------- STOCK & PROFIT USING VIEWS (EXCLUDE EXPIRED) ---------
$params = [];
if ($selected_shop > 0) {
    $sql = "SELECT 
               COALESCE(SUM(ps.total_qty), 0) AS total_units,
               COALESCE(SUM(ps.total_qty * p.buying_price), 0) AS stock_cost_value,
               COALESCE(SUM(ps.total_qty * p.selling_price), 0) AS stock_sell_value
            FROM product_stock_view ps
            JOIN products p ON ps.product_id = p.id
            WHERE ps.shop_id = ?";
    $params[] = $selected_shop;
} else {
    $sql = "SELECT 
               COALESCE(SUM(total_qty), 0) AS total_units,
               COALESCE(SUM(ps.total_qty * p.buying_price), 0) AS stock_cost_value,
               COALESCE(SUM(ps.total_qty * p.selling_price), 0) AS stock_sell_value
            FROM product_stock_view ps
            JOIN products p ON ps.product_id = p.id";
}
$stmt = $pdo->prepare($sql); $stmt->execute($params); $stock = $stmt->fetch(PDO::FETCH_ASSOC);

$total_stock_units = (int)$stock['total_units'];
$stock_cost_value = (float)$stock['stock_cost_value'];
$expected_profit = (float)($stock['stock_sell_value'] - $stock['stock_cost_value']);

// Get expired units separately - FIXED: Using batch_stock_view.current_qty
$params = [];
if ($selected_shop > 0) {
    $sql = "SELECT COUNT(*) as expired_batches, 
                   COALESCE(SUM(bs.current_qty), 0) as expired_units
            FROM batch_stock_view bs
            WHERE bs.expiry_date < CURDATE() AND bs.shop_id = ?";
    $params[] = $selected_shop;
} else {
    $sql = "SELECT COUNT(*) as expired_batches, 
                   COALESCE(SUM(bs.current_qty), 0) as expired_units
            FROM batch_stock_view bs
            WHERE bs.expiry_date < CURDATE()";
}
$stmt = $pdo->prepare($sql); $stmt->execute($params); $expired_data = $stmt->fetch(PDO::FETCH_ASSOC);
$expired_stock_units = (int)$expired_data['expired_units'];

// --------- LOW STOCK PRODUCTS (EXCLUDE EXPIRED) ---------
$low_threshold = 20;
$params = [];

if ($selected_shop > 0) {
    $sql = "SELECT 
               ps.product_id as id,
               ps.name,
               ps.sku,
               ps.total_qty as qty
            FROM product_stock_view ps
            WHERE ps.shop_id = ?
              AND ps.total_qty > 0  -- Only products with stock
              AND ps.total_qty <= ?
            ORDER BY ps.total_qty ASC";
    $params = [$selected_shop, $low_threshold];
} else {
    $sql = "SELECT 
               ps.product_id as id,
               ps.name,
               ps.sku,
               ps.total_qty as qty
            FROM product_stock_view ps
            WHERE ps.total_qty > 0  -- Only products with stock
              AND ps.total_qty <= ?
            ORDER BY ps.total_qty ASC";
    $params = [$low_threshold];
}

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

// --------- NEAR EXPIRY (within 90 days, NOT expired) ---------
$params = [];
if ($selected_shop > 0) {
    $sql = "SELECT 
               pa.batch_id,
               pa.name AS product_name,
               pa.batch_no,
               pa.batch_qty AS qty,
               pa.expiry_date,
               pa.cost_price,
               DATEDIFF(pa.expiry_date, CURDATE()) AS days_until_expiry
            FROM product_availability pa
            WHERE pa.expiry_date > CURDATE()  -- Not expired
              AND pa.expiry_date <= DATE_ADD(CURDATE(), INTERVAL 90 DAY)  -- Within 90 days
              AND pa.batch_qty > 0  -- Only items with stock
              AND pa.shop_id = ?
            ORDER BY pa.expiry_date ASC 
            LIMIT 50";
    $params = [$selected_shop];
} else {
    $sql = "SELECT 
               pa.batch_id,
               pa.name AS product_name,
               pa.batch_no,
               pa.batch_qty AS qty,
               pa.expiry_date,
               pa.cost_price,
               DATEDIFF(pa.expiry_date, CURDATE()) AS days_until_expiry
            FROM product_availability pa
            WHERE pa.expiry_date > CURDATE()  -- Not expired
              AND pa.expiry_date <= DATE_ADD(CURDATE(), INTERVAL 90 DAY)  -- Within 90 days
              AND pa.batch_qty > 0  -- Only items with stock
            ORDER BY pa.expiry_date ASC 
            LIMIT 50";
    $params = [];
}

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

// --------- EXPIRED PRODUCTS (expired today or in the past) ---------
$params = [];
if ($selected_shop > 0) {
    $sql = "SELECT 
               bs.batch_id,
               p.name AS product_name,
               bs.batch_no,
               bs.current_qty AS qty,
               bs.expiry_date,
               bs.cost_price,
               DATEDIFF(CURDATE(), bs.expiry_date) AS days_expired
            FROM batch_stock_view bs
            JOIN products p ON bs.product_id = p.id
            WHERE bs.current_qty > 0  -- Only batches with stock
              AND bs.expiry_date < CURDATE()
              AND bs.shop_id = ?
            ORDER BY bs.expiry_date ASC 
            LIMIT 50";
    $params = [$selected_shop];
} else {
    $sql = "SELECT 
               bs.batch_id,
               p.name AS product_name,
               bs.batch_no,
               bs.current_qty AS qty,
               bs.expiry_date,
               bs.cost_price,
               DATEDIFF(CURDATE(), bs.expiry_date) AS days_expired
            FROM batch_stock_view bs
            JOIN products p ON bs.product_id = p.id
            WHERE bs.current_qty > 0  -- Only batches with stock
              AND bs.expiry_date < CURDATE()
            ORDER BY bs.expiry_date ASC 
            LIMIT 50";
}

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

// Calculate expired summary
$expired_total_items = count($expired_products);
$expired_total_qty = 0;
$expired_total_value = 0;
foreach ($expired_products as $item) {
    $expired_total_qty += $item['qty'];
    $expired_total_value += ($item['qty'] * ($item['cost_price']));
}

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

// --------- SALES CHART LAST 14 DAYS ---------
$days = 14; 
$start_date = date('Y-m-d', strtotime("-" . ($days - 1) . " days"));
$params = [$start_date, $today];
$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 ?";
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);

$chart_labels = [];
$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));
    $chart_data[] = isset($daily_sales[$d]) ? (float)$daily_sales[$d] : 0;
}

// --------- PARTIAL PAYMENTS (DEBITS/CREDIT SALES) ---------
$params = [];
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'];
}

// Handle clear balance action via AJAX (we'll handle this in JavaScript)
// No PHP processing here to avoid page reloads
?>

<style>
.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 area adjustments */
.main-content-wrapper {
    padding: 20px;
    min-height: calc(100vh - 80px);
    background-color: #f5f7fb;
}

/* Dashboard specific styles */
.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 payments styling */
.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;
}

/* Responsive adjustments */
@media (max-width: 768px) {
    .main-content-wrapper {
        padding: 15px 10px;
    }
    
    .card {
        margin-bottom: 15px;
    }
    
    .table-responsive {
        font-size: 14px;
    }
}

/* Modal styling */
.modal-content {
    border-radius: 12px;
    border: none;
}

.modal-header {
    background-color: #f8f9fa;
    border-radius: 12px 12px 0 0;
}

/* Alert messages */
.alert {
    border-radius: 8px;
    border: none;
}

/* Toast notifications */
#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%); }
}
</style>

<!-- MAIN CONTENT WRAPPER -->
<div class="main-content-wrapper" id="mainContent">
    <div class="container-fluid">
        <!-- Header -->
        <div class="card p-3 mb-4 shadow-sm">
            <h2>Welcome, <?=htmlspecialchars($user['fullname'] ?? '')?></h2>
            <p>Role: <?=htmlspecialchars(ucfirst($user_role))?></p>
            <?php if($user_role=='admin'): ?>
            <form method="GET" class="form-inline">
                <label class="mr-2">Shop</label>
                <select name="shop_id" class="form-control mr-2">
                    <option value="0" <?= $selected_shop==0?'selected':''?>>All Shops</option>
                    <?php
                    $shops = $pdo->query("SELECT id,name FROM shops 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>
            <?php endif; ?>
        </div>

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

        <!-- Summary Cards -->
        <div class="row mb-3">
            <div class="col-md-3"><div class="card p-3"><h6>Sales Today</h6><h4><?=number_format($total_sales_today,2)?></h4></div></div>
            <div class="col-md-3"><div class="card p-3"><h6>Sales This Month</h6><h4><?=number_format($total_sales_month,2)?></h4></div></div>
            <div class="col-md-3"><div class="card p-3"><h6>Sales This Year</h6><h4><?=number_format($total_sales_year,2)?></h4></div></div>
            <div class="col-md-3"><div class="card p-3"><h6>Purchases Today</h6><h4><?=number_format($total_purchases_today,2)?></h4></div></div>
            <div class="col-md-3"><div class="card p-3"><h6>Purchases This Month</h6><h4><?=number_format($total_purchases_month,2)?></h4></div></div>
            <div class="col-md-3"><div class="card p-3"><h6>Purchases This Year</h6><h4><?=number_format($total_purchases_year,2)?></h4></div></div>
            <div class="col-md-3">
                <div class="card p-3">
                    <h6>Total Stock Units</h6>
                    <h4><?=number_format($total_stock_units)?></h4>
                    <?php if($expired_stock_units > 0): ?>
                    <small class="text-danger">(Excludes <?=number_format($expired_stock_units)?> expired units)</small>
                    <?php endif; ?>
                </div>
            </div>
            <div class="col-md-3"><div class="card p-3"><h6>Expected Profit</h6><h4><?=number_format($expected_profit,2)?></h4></div></div>
            <div class="col-md-3">
                <div class="card p-3">
                    <h6>Outstanding Balance</h6>
                    <h4 class="<?= $total_outstanding > 0 ? 'text-danger' : 'text-success' ?>">
                        <?=number_format($total_outstanding,2)?>
                    </h4>
                    <small><?= count($partial_payments) ?> pending sale(s)</small>
                </div>
            </div>
        </div>

        <!-- Partial Payments Section -->
        <div class="row mt-3">
            <div class="col-md-12">
                <div class="card p-3 mb-4">
                    <h5 class="d-flex justify-content-between align-items-center">
                        <span>Partial Payments / Credit Sales</span>
                        <span class="badge badge-warning">Total Outstanding: <?=number_format($total_outstanding,2)?></span>
                    </h5>
                    
                    <?php if(empty($partial_payments)): ?>
                        <div class="text-center text-muted py-4">
                            <i class="fas fa-check-circle fa-2x mb-2 text-success"></i><br>
                            <h6>No outstanding payments</h6>
                            <small>All sales are fully paid</small>
                        </div>
                    <?php else: ?>
                    <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></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-<?=$payment['id']?>">
                                    <td>
                                        <a href="sales_view.php?id=<?=$payment['id']?>" target="_blank">
                                            <?=htmlspecialchars($payment['invoice_no'] ?? '')?>
                                        </a>
                                    </td>
                                    <td><?=date('d-M-Y', strtotime($payment['sale_date']))?></td>
                                    <td><?=htmlspecialchars($payment['customer_name'] ?? 'Walk-in Customer')?></td>
                                    <td><?=htmlspecialchars($payment['customer_phone'] ?? '')?></td>
                                    <td class="text-right"><?=number_format($payment['total'],2)?></td>
                                    <td class="text-right text-success paid-amount"><?=number_format($payment['paid'],2)?></td>
                                    <td class="text-right font-weight-bold balance-amount">
                                        <span class="<?=$balance_class?>"><?=number_format($balance,2)?></span>
                                    </td>
                                    <td><?=htmlspecialchars($payment['cashier'] ?? '')?></td>
                                    <td>
                                        <span class="badge badge-<?=$payment['status']=='partially_paid'?'warning':'secondary'?>">
                                            <?=str_replace('_', ' ', ucfirst($payment['status']))?>
                                        </span>
                                    </td>
                                    <td>
                                        <!-- <div class="btn-group">
                                            <button class="btn btn-sm btn-success clear-balance-btn" 
                                                    data-toggle="modal" 
                                                    data-target="#clearBalanceModal"
                                                    data-sale-id="<?=$payment['id']?>"
                                                    data-invoice-no="<?=htmlspecialchars($payment['invoice_no'] ?? '')?>"
                                                    data-customer="<?=htmlspecialchars($payment['customer_name'] ?? 'Walk-in Customer')?>"
                                                    data-balance="<?=$balance?>"
                                                    data-total="<?=$payment['total']?>"
                                                    data-paid="<?=$payment['paid']?>">
                                                <i class="fas fa-money-check-alt"></i> Pay
                                            </button>
                                            <button class="btn btn-sm btn-info print-receipt-btn" 
                                                    data-sale-id="<?=$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><?=number_format(array_sum(array_column($partial_payments, 'paid')),2)?></strong></td>
                                    <td class="text-right"><strong class="text-danger"><?=number_format($total_outstanding,2)?></strong></td>
                                    <td colspan="3"></td>
                                </tr>
                            </tfoot>
                        </table>
                    </div>
                    <?php endif; ?>
                </div>
            </div>
        </div>

        <!-- Rest of the dashboard content remains the same -->
        <div class="row mt-3">
            <div class="col-md-8">
                <div class="card p-3 mb-3"><h5>Sales Last 14 Days</h5><canvas id="salesChart" height="120"></canvas></div>

                <div class="card p-3 mb-3"><h5>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>Products (Qty)</th><th>Total</th></tr></thead>
                <tbody>
                <?php
                $grand_total = 0;
                if(!$recent_sales): ?>
                <tr><td colspan="5">No recent sales</td></tr>
                <?php else:
                foreach($recent_sales as $s):
                    $grand_total += $s['total'];

                    // Fetch products and quantities for this sale
                    $stmt2 = $pdo->prepare("SELECT p.name, sl.qty FROM sale_lines sl JOIN products p ON sl.product_id=p.id WHERE sl.sale_id=?");
                    $stmt2->execute([$s['id']]);
                    $products = $stmt2->fetchAll(PDO::FETCH_ASSOC);
                ?>
                <tr>
                <td><?=htmlspecialchars($s['invoice_no'] ?? '')?></td>
                <td><?=date('d-M-Y', strtotime($s['sale_date']))?></td>
                <td><?=htmlspecialchars($s['cashier'] ?? '')?></td>
                <td>
                <?php 
                if(!empty($products)){
                    $product_list = array_map(function($p){ 
                        return htmlspecialchars($p['name'] ?? '') . ' (' . ($p['qty'] ?? 0) . ')'; 
                    }, $products);
                    echo implode(', ', $product_list);
                } else {
                    echo 'N/A';
                }
                ?>
                </td>
                <td><?=number_format($s['total'],2)?></td>
                </tr>
                <?php endforeach; ?>
                <tr class="font-weight-bold">
                <td colspan="4" class="text-right">Grand Total</td>
                <td><?=number_format($grand_total,2)?></td>
                </tr>
                <?php endif; ?>
                </tbody>
                </table></div>
                </div>
            </div>

            <div class="col-md-4">
                <div class="card p-3 mb-3"><h5>Low Stock Products</h5>
                <div style="max-height:300px; 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">No low stock</td></tr><?php else: foreach($low_stock as $p): ?>
                <tr>
                <td><?=htmlspecialchars($p['name'] ?? '')?></td>
                <td><?=htmlspecialchars($p['sku'] ?? '')?></td>
                <td><?=intval($p['qty'])?></td>
                </tr>
                <?php endforeach; endif; ?>
                </tbody>
                </table>
                </div>
                </div>

                <div class="card p-3">
                    <h5>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">
                                Near Expiry <span class="badge badge-warning"><?= 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">
                                Expired <span class="badge badge-danger"><?= $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): ?>
                                    <tr class="near-expiry-row">
                                        <td><?=htmlspecialchars($b['product_name'] ?? '')?></td>
                                        <td><?=htmlspecialchars($b['batch_no'] ?? '')?></td>
                                        <td><?=intval($b['qty'])?></td>
                                        <td>
                                            <span class="badge badge-warning">
                                                <?= $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'];
                                    ?>
                                    <tr class="expired-row">
                                        <td><?=htmlspecialchars($b['product_name'] ?? '')?></td>
                                        <td><?=htmlspecialchars($b['batch_no'] ?? '')?></td>
                                        <td><?=intval($b['qty'])?></td>
                                        <td>
                                            <span class="badge badge-danger">
                                                <?= $days_expired ?> days ago
                                            </span>
                                        </td>
                                        <td class="text-danger"><?= 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><?= $expired_total_qty ?></strong></td>
                                            <td colspan="2" class="text-danger"><strong><?= 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="<?=$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>

<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js"></script>
<script>
$(document).ready(function() {
    // Sales Chart
    const ctx = document.getElementById('salesChart').getContext('2d');
    new Chart(ctx,{
        type:'line',
        data:{
            labels: <?=json_encode($chart_labels)?>,
            datasets:[{label:'Sales', data: <?=json_encode($chart_data)?>, borderColor:'rgba(75,192,192,1)', backgroundColor:'rgba(75,192,192,0.2)', fill:true, tension:0.3}]
        },
        options:{responsive:true, plugins:{legend:{display:true}}}
    });

    // Initialize Bootstrap tabs
    const triggerTabList = [].slice.call(document.querySelectorAll('#expiryTabs a'))
    triggerTabList.forEach(function (triggerEl) {
        const tabTrigger = new bootstrap.Tab(triggerEl)
        triggerEl.addEventListener('click', function (event) {
            event.preventDefault()
            tabTrigger.show()
        })
    });

    // Toast notification function
    function showToast(message, type = 'info') {
        const toastId = 'toast-' + Date.now();
        const bgColor = {
            'success': '#28a745',
            'warning': '#ffc107',
            'error': '#dc3545',
            'info': '#17a2b8'
        }[type] || '#17a2b8';
        
        const 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';
        
        const toast = $(`
            <div id="${toastId}" class="auto-toast" 
                 style="border-left-color: ${bgColor};">
                <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;"
                            onclick="$('#${toastId}').remove()">
                        <span>&times;</span>
                    </button>
                </div>
            </div>
        `);
        
        $('#toast-container').append(toast);
        
        setTimeout(function() {
            $('#' + toastId).remove();
        }, 3000);
    }

    // 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(total.toFixed(2));
        modal.find('#modalPaid').val(paid.toFixed(2));
        modal.find('#modalBalance').val(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());
        var payment = parseFloat($('#modalPaymentAmount').val()) || 0;
        var newBalance = balance - payment;
        
        if (newBalance < 0) {
            newBalance = 0;
            $('#modalPaymentAmount').val(balance.toFixed(2));
            payment = balance;
        }
        
        $('#modalNewBalance').val(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(response.new_paid);
                    row.find('.balance-amount').html('<span class="' + getBalanceClass(response.new_balance) + '">' + 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(/[^\d.-]/g, '');
            var balance = parseFloat(balanceText) || 0;
            total += balance;
        });
        
        // Update the total outstanding badge
        $('.badge-warning:contains("Total Outstanding")').text('Total Outstanding: ' + total.toFixed(2));
        
        // Update the card value
        $('.card:contains("Outstanding Balance") h4').text(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() {
        $('.alert').alert('close');
    }, 5000);
});
</script>

<!-- Create process_payment.php file -->
<?php
// Let's also create the process_payment.php file content
?>
<script>
// This is just to show what process_payment.php should contain
// You need to create this file separately
/*
<?php
// process_payment.php
session_start();
require_once '../config/db_connect.php';

header('Content-Type: application/json');

if (!isset($_SESSION['user'])) {
    echo json_encode(['success' => false, 'message' => 'Not authenticated']);
    exit;
}

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

// Check permissions
if (!in_array($user_role, ['admin', 'pharmacist'])) {
    echo json_encode(['success' => false, 'message' => 'Insufficient permissions']);
    exit;
}

$sale_id = intval($_POST['sale_id'] ?? 0);
$amount = floatval($_POST['amount'] ?? 0);
$payment_method = $_POST['payment_method'] ?? 'CASH';

if ($sale_id <= 0 || $amount <= 0) {
    echo json_encode(['success' => false, 'message' => 'Invalid payment details']);
    exit;
}

try {
    $pdo->beginTransaction();
    
    // Get sale details
    $stmt = $pdo->prepare("SELECT total, paid, shop_id FROM sales WHERE id = ?");
    $stmt->execute([$sale_id]);
    $sale = $stmt->fetch(PDO::FETCH_ASSOC);
    
    if (!$sale) {
        throw new Exception("Sale not found.");
    }
    
    $balance = $sale['total'] - $sale['paid'];
    
    // Validate amount
    if ($amount > $balance) {
        throw new Exception("Payment amount cannot exceed the balance of " . number_format($balance, 2));
    }
    
    $new_paid = $sale['paid'] + $amount;
    $new_balance = $sale['total'] - $new_paid;
    $new_status = ($new_balance <= 0) ? 'paid' : 'partially_paid';
    
    // Update sale
    $stmt = $pdo->prepare("UPDATE sales SET paid = ?, status = ? WHERE id = ?");
    $stmt->execute([$new_paid, $new_status, $sale_id]);
    
    // Record payment
    $stmt = $pdo->prepare("INSERT INTO payments (sale_id, amount, method, created_by, shop_id, paid_at) 
                           VALUES (?, ?, ?, ?, ?, NOW())");
    $stmt->execute([$sale_id, $amount, $payment_method, $user['id'], $sale['shop_id']]);
    
    $pdo->commit();
    
    echo json_encode([
        'success' => true,
        'message' => 'Payment recorded successfully',
        'sale_id' => $sale_id,
        'new_paid' => number_format($new_paid, 2),
        'new_balance' => number_format($new_balance, 2),
        'new_status' => $new_status
    ]);
    
} catch (Exception $e) {
    $pdo->rollBack();
    echo json_encode(['success' => false, 'message' => $e->getMessage()]);
}
?>
*/
</script>

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