<?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');

// --------- 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 ---------
$params = [];
$sql = "SELECT COALESCE(SUM(pb.qty),0) AS total_units,
               COALESCE(SUM(pb.qty*pb.cost_price),0) AS stock_cost_value,
               COALESCE(SUM(pb.qty*pb.sell_price),0) AS stock_sell_value
        FROM product_batches pb WHERE pb.is_active=1";
if ($selected_shop>0){ $sql .= " AND pb.shop_id=?"; $params[]=$selected_shop; }
$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']);

// --------- LOW STOCK PRODUCTS ---------
$low_threshold = 20;
$params = [$low_threshold];
$sql = "SELECT p.id,p.name,p.sku,COALESCE(SUM(pb.qty),0) AS qty
        FROM products p
        LEFT JOIN product_batches pb ON p.id=pb.product_id AND pb.is_active=1
        GROUP BY p.id HAVING qty <= ? ORDER BY qty ASC";
if ($selected_shop>0){ $params=[$selected_shop,$low_threshold]; $sql="SELECT p.id,p.name,p.sku,COALESCE(SUM(pb.qty),0) AS qty FROM products p LEFT JOIN product_batches pb ON p.id=pb.product_id AND pb.is_active=1 AND pb.shop_id=? GROUP BY p.id HAVING qty<=? ORDER BY qty ASC"; }
$stmt=$pdo->prepare($sql); $stmt->execute($params); $low_stock=$stmt->fetchAll(PDO::FETCH_ASSOC);

// --------- NEAR EXPIRY ---------
$near_date = date('Y-m-d', strtotime('+90 days'));
$params = [$today,$near_date];
$sql = "SELECT pb.id AS batch_id,p.name AS product_name,pb.batch_no,pb.qty,pb.expiry_date
        FROM product_batches pb JOIN products p ON pb.product_id=p.id
        WHERE pb.is_active=1 AND pb.expiry_date BETWEEN ? AND ?";
if($selected_shop>0){ $sql.=" AND pb.shop_id=?"; $params[]=$selected_shop; }
$sql.=" ORDER BY pb.expiry_date ASC LIMIT 50";
$stmt=$pdo->prepare($sql); $stmt->execute($params); $near_expiry=$stmt->fetchAll(PDO::FETCH_ASSOC);

// --------- RECENT SALES ---------
$params = [];
$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 1=1";
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);

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

?>

<div class="col-md-10 ml-sm-auto px-4 py-4">
<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>

<!-- 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></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>

<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</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></tr></thead>
<tbody>
<?php if(!$recent_sales): ?><tr><td colspan="4">No recent sales</td></tr><?php else:
foreach($recent_sales as $s): ?>
<tr>
<td><?=htmlspecialchars($s['invoice_no'])?></td>
<td><?=date('d-M-Y', strtotime($s['sale_date']))?></td>
<td><?=htmlspecialchars($s['cashier'])?></td>
<td><?=number_format($s['total'],2)?></td>
</tr>
<?php endforeach; 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>Near Expiry</h5>
<div style="max-height:220px; overflow:auto;">
<table class="table table-sm table-bordered">
<thead><tr><th>Product</th><th>Batch</th><th>Qty</th><th>Expiry</th></tr></thead>
<tbody>
<?php if(!$near_expiry): ?><tr><td colspan="4">No near expiry</td></tr><?php else: foreach($near_expiry as $b): ?>
<tr>
<td><?=htmlspecialchars($b['product_name'])?></td>
<td><?=htmlspecialchars($b['batch_no'])?></td>
<td><?=intval($b['qty'])?></td>
<td><?=htmlspecialchars($b['expiry_date'])?></td>
</tr>
<?php endforeach; endif; ?>
</tbody>
</table>
</div></div>
</div>
</div>
</div>

<script src="../libraries/chart.js"></script>
<script>
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}}}
});
</script>

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