<?php
// hps_dashboard.php  (UPDATED)
session_start();
require "db.php";

error_reporting(E_ALL);
ini_set('display_errors', 1);

function e($v){ return htmlspecialchars((string)($v ?? ''), ENT_QUOTES, 'UTF-8'); }
function q1($conn,$sql){
    $r = mysqli_query($conn,$sql);
    if(!$r) die("<pre style='color:red'>MYSQL ERROR:\n".htmlspecialchars(mysqli_error($conn))."\n\nSQL:\n".htmlspecialchars($sql)."</pre>");
    $row = mysqli_fetch_row($r);
    return $row ? $row[0] : 0;
}
function qall($conn,$sql){
    $r = mysqli_query($conn,$sql);
    if(!$r) die("<pre style='color:red'>MYSQL ERROR:\n".htmlspecialchars(mysqli_error($conn))."\n\nSQL:\n".htmlspecialchars($sql)."</pre>");
    $out = [];
    while($row=mysqli_fetch_assoc($r)) $out[] = $row;
    return $out;
}
function n0($v){ return is_numeric($v) ? (float)$v : 0; }
// ✅ NO PAISAS
function money0($v){ return number_format((float)$v, 0); }

$today = date('Y-m-d');

/* =========================
   KPI QUERIES (REAL DATA)
========================= */

// Total Sales = SUM(items) + SUM(documentation_charges) for Approved sales
$total_items = (float)q1($conn,"
    SELECT IFNULL(SUM(si.price*si.qty),0)
    FROM hps_sales s
    JOIN hps_sale_items si ON si.sale_id=s.id
    WHERE s.status='Approved'
");
$total_doc = (float)q1($conn,"
    SELECT IFNULL(SUM(documentation_charges),0)
    FROM hps_sales
    WHERE status='Approved'
");
$total_sales = $total_items + $total_doc;

// Total Collection from customer ledger (all credits)
$total_collection = (float)q1($conn,"
    SELECT IFNULL(SUM(cr),0)
    FROM hps_customer_ledger
");

// Today Collection
$today_collection = (float)q1($conn,"
    SELECT IFNULL(SUM(cr),0)
    FROM hps_customer_ledger
    WHERE trans_date='$today'
");

// Outstanding & Overpaid
$raw_outstanding = $total_sales - $total_collection;
$outstanding = ($raw_outstanding > 0) ? $raw_outstanding : 0;
$overpaid    = ($raw_outstanding < 0) ? abs($raw_outstanding) : 0;

// Active Customers = customers with at least one approved sale
$active_customers = (int)q1($conn,"
    SELECT COUNT(DISTINCT customer_id)
    FROM hps_sales
    WHERE status='Approved'
");

// Active HP Sales = approved sales having pending installments
$active_hp_sales = (int)q1($conn,"
    SELECT COUNT(*)
    FROM hps_sales s
    WHERE s.status='Approved'
      AND EXISTS(
        SELECT 1
        FROM hps_installment_schedule sch
        WHERE sch.sale_id=s.id AND sch.status='Pending'
      )
");

// Pending Sales (awaiting approval)
$pending_sales = (int)q1($conn,"
    SELECT COUNT(*)
    FROM hps_sales
    WHERE status='Pending'
");

// Low stock count
$low_stock_items = (int)q1($conn,"
    SELECT COUNT(*)
    FROM hps_products p
    JOIN hps_stock s ON s.product_id=p.id
    WHERE s.qty_on_hand <= p.reorder_level
");

// Total Stock Value
$total_stock_value = (float)q1($conn,"
    SELECT IFNULL(SUM(s.qty_on_hand * s.avg_cost),0)
    FROM hps_stock s
");

// Receivables due next 7 days (pending installments sum)
$due_next7 = (float)q1($conn,"
    SELECT IFNULL(SUM(inst_amount - paid_amount),0)
    FROM hps_installment_schedule
    WHERE status='Pending'
      AND due_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY)
");

/* =========================
   CHART DATA (LAST 12 MONTHS)
========================= */

// Sales by month (approved)
$sales_monthly = qall($conn,"
    SELECT ym, SUM(sale_total) AS total
    FROM (
        SELECT
            DATE_FORMAT(s.sale_date,'%Y-%m') AS ym,
            (
                (SELECT IFNULL(SUM(price*qty),0) FROM hps_sale_items WHERE sale_id=s.id)
                + IFNULL(s.documentation_charges,0)
            ) AS sale_total
        FROM hps_sales s
        WHERE s.status='Approved'
          AND s.sale_date >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 11 MONTH),'%Y-%m-01')
    ) x
    GROUP BY ym
    ORDER BY ym
");

// Collection by month (ledger credits)
$collect_monthly = qall($conn,"
    SELECT DATE_FORMAT(trans_date,'%Y-%m') AS ym, IFNULL(SUM(cr),0) AS total
    FROM hps_customer_ledger
    WHERE trans_date >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 11 MONTH),'%Y-%m-01')
    GROUP BY ym
    ORDER BY ym
");

// Expenses by month
$expense_monthly = qall($conn,"
    SELECT DATE_FORMAT(exp_date,'%Y-%m') AS ym, IFNULL(SUM(amount),0) AS total
    FROM hps_expenses
    WHERE exp_date >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 11 MONTH),'%Y-%m-01')
    GROUP BY ym
    ORDER BY ym
");

// Merge months (ensure all 12 months exist)
$months = [];
for($i=11;$i>=0;$i--){
    $months[] = date('Y-m', strtotime("-$i months"));
}
$mapSales = []; foreach($sales_monthly as $r){ $mapSales[$r['ym']] = (float)$r['total']; }
$mapColl  = []; foreach($collect_monthly as $r){ $mapColl[$r['ym']]  = (float)$r['total']; }
$mapExp   = []; foreach($expense_monthly as $r){ $mapExp[$r['ym']]   = (float)$r['total']; }

$chart_labels = [];
$chart_sales  = [];
$chart_coll   = [];
$chart_exp    = [];

foreach($months as $m){
    $chart_labels[] = $m;
    $chart_sales[]  = (float)($mapSales[$m] ?? 0);
    $chart_coll[]   = (float)($mapColl[$m] ?? 0);
    $chart_exp[]    = (float)($mapExp[$m] ?? 0);
}

// Top 10 stock value products
$top_stock = qall($conn,"
    SELECT p.name, (s.qty_on_hand*s.avg_cost) AS val
    FROM hps_products p
    JOIN hps_stock s ON s.product_id=p.id
    ORDER BY val DESC
    LIMIT 10
");
$top_labels = [];
$top_vals   = [];
foreach($top_stock as $r){
    $top_labels[] = $r['name'];
    $top_vals[]   = (float)$r['val'];
}

/* =========================
   TABLES (NEW)
========================= */

// Upcoming installments (next 10)
$upcoming = qall($conn,"
    SELECT sch.due_date, sch.inst_no, sch.inst_amount, sch.paid_amount,
           (sch.inst_amount - sch.paid_amount) AS balance,
           s.sale_no, c.name AS customer, c.mobile
    FROM hps_installment_schedule sch
    JOIN hps_sales s ON s.id=sch.sale_id
    JOIN hps_customers c ON c.id=s.customer_id
    WHERE sch.status='Pending'
      AND sch.due_date >= CURDATE()
    ORDER BY sch.due_date ASC
    LIMIT 10
");

// Recent approved sales (last 10)
$recent_sales = qall($conn,"
    SELECT s.id, s.sale_no, s.sale_date, c.name AS customer, c.mobile,
           (
             (SELECT IFNULL(SUM(price*qty),0) FROM hps_sale_items WHERE sale_id=s.id)
             + IFNULL(s.documentation_charges,0)
           ) AS sale_total
    FROM hps_sales s
    JOIN hps_customers c ON c.id=s.customer_id
    WHERE s.status='Approved'
    ORDER BY s.id DESC
    LIMIT 10
");

// ✅ Recent Expenses (last 10)
$recent_exp = qall($conn,"
    SELECT e.id, e.exp_date, h.name AS head, sh.name AS sub_head,
           e.amount, e.pay_mode, e.details
    FROM hps_expenses e
    LEFT JOIN hps_expense_heads h ON h.id=e.head_id
    LEFT JOIN hps_expense_sub_heads sh ON sh.id=e.sub_head_id
    ORDER BY e.id DESC
    LIMIT 10
");

// ✅ Salesperson performance (Approved sales)
$salespersons = qall($conn,"
    SELECT
        sp.id,
        sp.name,
        COUNT(DISTINCT s.id) AS total_sales,
        IFNULL(SUM((si.price*si.qty)),0) + IFNULL(SUM(s.documentation_charges),0) AS sale_amount
    FROM hps_salespersons sp
    LEFT JOIN hps_sales s
        ON s.salesperson_id=sp.id AND s.status='Approved'
    LEFT JOIN hps_sale_items si
        ON si.sale_id=s.id
    WHERE sp.status='Active'
    GROUP BY sp.id
    ORDER BY sale_amount DESC
");

// ✅ Top 10 Defaulters (highest overdue balance)
// NOTE: based on installment schedule overdue balances
$defaulters = qall($conn,"
    SELECT
        c.id AS customer_id,
        c.name,
        c.mobile,
        COUNT(*) AS overdue_count,
        IFNULL(SUM(sch.inst_amount - sch.paid_amount),0) AS overdue_amount,
        MIN(sch.due_date) AS oldest_due
    FROM hps_installment_schedule sch
    JOIN hps_sales s ON s.id=sch.sale_id AND s.status='Approved'
    JOIN hps_customers c ON c.id=s.customer_id
    WHERE sch.status='Pending'
      AND sch.due_date < CURDATE()
      AND (sch.inst_amount - sch.paid_amount) > 0
    GROUP BY c.id
    ORDER BY overdue_amount DESC
    LIMIT 10
");

// Salesperson chart (top 10)
$sp_labels = [];
$sp_vals   = [];
$k=0;
foreach($salespersons as $r){
    if($k>=10) break;
    $sp_labels[] = $r['name'];
    $sp_vals[]   = (float)$r['sale_amount'];
    $k++;
}

?>
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>HPS Dashboard</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet">
<script src="https://cdn.jsdelivr.net/npm/chart.js@4.4.1/dist/chart.umd.min.js"></script>

<style>
body{background:#f4f7fb}
.wrap{max-width:1400px;margin:18px auto;padding:0 16px;}
.cardx{
    background:#fff;border-radius:16px;
    box-shadow:0 10px 22px rgba(0,0,0,.08);
    border:1px solid rgba(0,0,0,.06);
}
.kpi{
    padding:12px 12px;border-radius:14px;border:1px solid rgba(0,0,0,.06);
    background:#fff;box-shadow:0 8px 16px rgba(0,0,0,.05);height:100%;
}
.kpi .label{font-size:11px;color:#6c757d}
.kpi .val{font-size:20px;font-weight:800;line-height:1.05}
.kpi .hint{font-size:11px;color:#6c757d}
.kpi .badge{font-size:10px}
.hrline{height:1px;background:rgba(0,0,0,.06);margin:10px 0}
.table-sm td,.table-sm th{padding:.40rem .50rem;font-size:12px}
.small-muted{font-size:11px;color:#6c757d}
canvas{max-height:280px}
h4{font-size:20px}
@media print{
    .no-print{display:none}
    body{background:#fff}
    .cardx{box-shadow:none}
}
</style>
</head>
<body>

<div class="wrap">

    <div class="d-flex align-items-center justify-content-between mb-2">
        <div>
            <h4 class="mb-0">📊 Dashboard Overview</h4>
            <div class="small-muted">Live data from database • Updated: <?=e(date('d-M-Y h:i A'))?></div>
        </div>
        <div class="no-print">
            <button class="btn btn-sm btn-dark" onclick="window.print()">Print</button>
        </div>
    </div>

    <!-- KPIs -->
    <div class="row g-2 mb-2">
        <div class="col-6 col-lg-2">
            <div class="kpi">
                <div class="label">Total Sales (Approved)</div>
                <div class="val"><?=money0($total_sales)?></div>
                <div class="hint">Items + Documentation</div>
            </div>
        </div>
        <div class="col-6 col-lg-2">
            <div class="kpi">
                <div class="label">Total Collection</div>
                <div class="val"><?=money0($total_collection)?></div>
                <div class="hint">Customer Ledger Credits</div>
            </div>
        </div>
        <div class="col-6 col-lg-2">
            <div class="kpi">
                <div class="label">Outstanding</div>
                <div class="val"><?=money0($outstanding)?></div>
                <div class="hint">
                    <?php if($overpaid>0): ?>
                        <span class="badge bg-success">Overpaid: <?=money0($overpaid)?></span>
                    <?php else: ?>
                        Balance Receivable
                    <?php endif; ?>
                </div>
            </div>
        </div>
        <div class="col-6 col-lg-2">
            <div class="kpi">
                <div class="label">Today Collection</div>
                <div class="val"><?=money0($today_collection)?></div>
                <div class="hint"><?=$today?></div>
            </div>
        </div>
        <div class="col-6 col-lg-2">
            <div class="kpi">
                <div class="label">Active Customers</div>
                <div class="val"><?=number_format($active_customers,0)?></div>
                <div class="hint">With Approved Sales</div>
            </div>
        </div>
        <div class="col-6 col-lg-2">
            <div class="kpi">
                <div class="label">Active HP Sales</div>
                <div class="val"><?=number_format($active_hp_sales,0)?></div>
                <div class="hint">Pending Installments</div>
            </div>
        </div>
    </div>

    <!-- Secondary KPIs -->
    <div class="row g-2 mb-3">
        <div class="col-12 col-lg-3">
            <div class="kpi">
                <div class="label">Pending Sales for Approval</div>
                <div class="val"><?=number_format($pending_sales,0)?></div>
                <div class="hint">Status: Pending</div>
            </div>
        </div>
        <div class="col-12 col-lg-3">
            <div class="kpi">
                <div class="label">Low Stock Items</div>
                <div class="val"><?=number_format($low_stock_items,0)?></div>
                <div class="hint">Qty ≤ Reorder Level</div>
            </div>
        </div>
        <div class="col-12 col-lg-3">
            <div class="kpi">
                <div class="label">Total Stock Value</div>
                <div class="val"><?=money0($total_stock_value)?></div>
                <div class="hint">Qty × Avg Cost</div>
            </div>
        </div>
        <div class="col-12 col-lg-3">
            <div class="kpi">
                <div class="label">Installments Due (Next 7 Days)</div>
                <div class="val"><?=money0($due_next7)?></div>
                <div class="hint">Pending balances only</div>
            </div>
        </div>
    </div>

    <!-- CHARTS -->
    <div class="row g-3 mb-3">
        <div class="col-12 col-lg-8">
            <div class="cardx p-3">
                <div class="d-flex align-items-center justify-content-between">
                    <div class="fw-bold">Sales / Collection / Expenses (Last 12 Months)</div>
                    <div class="small-muted"><?=e($months[0])?> → <?=e($months[count($months)-1])?></div>
                </div>
                <div class="hrline"></div>
                <canvas id="chartTrend"></canvas>
            </div>
        </div>
        <div class="col-12 col-lg-4">
            <div class="cardx p-3">
                <div class="fw-bold">Salesperson Performance (Top 10)</div>
                <div class="small-muted">Approved sales amount</div>
                <div class="hrline"></div>
                <canvas id="chartSP"></canvas>
            </div>
        </div>
    </div>

    <div class="row g-3 mb-3">
        <div class="col-12 col-lg-4">
            <div class="cardx p-3">
                <div class="fw-bold">Top Stock Value Products</div>
                <div class="small-muted">Top 10 by Qty×AvgCost</div>
                <div class="hrline"></div>
                <canvas id="chartStockTop"></canvas>
            </div>
        </div>

        <div class="col-12 col-lg-8">
            <div class="cardx p-3">
                <div class="fw-bold">Top 10 Defaulters (Overdue Installments)</div>
                <div class="small-muted">Highest overdue balances</div>
                <div class="hrline"></div>

                <div class="table-responsive">
                    <table class="table table-bordered table-sm align-middle mb-0">
                        <thead class="table-dark">
                            <tr>
                                <th>#</th>
                                <th>Customer</th>
                                <th>Mobile</th>
                                <th class="text-center">Overdue</th>
                                <th>Oldest Due</th>
                                <th class="text-end">Amount</th>
                            </tr>
                        </thead>
                        <tbody>
                        <?php if(count($defaulters)==0): ?>
                            <tr><td colspan="6" class="text-center text-muted">No defaulters ✅</td></tr>
                        <?php else: $x=1; foreach($defaulters as $d): ?>
                            <tr>
                                <td><?=$x++?></td>
                                <td><?=e($d['name'])?></td>
                                <td><?=e($d['mobile'])?></td>
                                <td class="text-center fw-bold"><?=number_format((int)$d['overdue_count'],0)?></td>
                                <td><?=e(date('d-M-Y', strtotime($d['oldest_due'])))?></td>
                                <td class="text-end fw-bold"><?=money0($d['overdue_amount'])?></td>
                            </tr>
                        <?php endforeach; endif; ?>
                        </tbody>
                    </table>
                </div>

            </div>
        </div>
    </div>

    <!-- TABLES -->
    <div class="row g-3">
        <div class="col-12 col-lg-4">
            <div class="cardx p-3">
                <div class="fw-bold">Upcoming Installments</div>
                <div class="small-muted">Next 10 pending due dates</div>
                <div class="hrline"></div>

                <div class="table-responsive">
                    <table class="table table-bordered table-sm align-middle mb-0">
                        <thead class="table-dark">
                            <tr>
                                <th>Due</th>
                                <th>Sale</th>
                                <th>Customer</th>
                                <th class="text-end">Balance</th>
                            </tr>
                        </thead>
                        <tbody>
                        <?php if(count($upcoming)==0): ?>
                            <tr><td colspan="4" class="text-center text-muted">No upcoming installments ✅</td></tr>
                        <?php else: foreach($upcoming as $u): ?>
                            <tr>
                                <td><?=e(date('d-M-Y', strtotime($u['due_date'])))?> <span class="badge bg-secondary">#<?=e($u['inst_no'])?></span></td>
                                <td><?=e($u['sale_no'])?></td>
                                <td>
                                    <?=e($u['customer'])?><br>
                                    <span class="small-muted"><?=e($u['mobile'])?></span>
                                </td>
                                <td class="text-end fw-bold"><?=money0($u['balance'])?></td>
                            </tr>
                        <?php endforeach; endif; ?>
                        </tbody>
                    </table>
                </div>

            </div>
        </div>

        <div class="col-12 col-lg-4">
            <div class="cardx p-3">
                <div class="fw-bold">Recent Approved Sales</div>
                <div class="small-muted">Last 10 approved invoices</div>
                <div class="hrline"></div>

                <div class="table-responsive">
                    <table class="table table-bordered table-sm align-middle mb-0">
                        <thead class="table-dark">
                            <tr>
                                <th>Date</th>
                                <th>Sale No</th>
                                <th>Customer</th>
                                <th class="text-end">Total</th>
                            </tr>
                        </thead>
                        <tbody>
                        <?php if(count($recent_sales)==0): ?>
                            <tr><td colspan="4" class="text-center text-muted">No approved sales found.</td></tr>
                        <?php else: foreach($recent_sales as $s): ?>
                            <tr>
                                <td><?=e(date('d-M-Y', strtotime($s['sale_date'])))?></td>
                                <td><?=e($s['sale_no'])?></td>
                                <td>
                                    <?=e($s['customer'])?><br>
                                    <span class="small-muted"><?=e($s['mobile'])?></span>
                                </td>
                                <td class="text-end fw-bold"><?=money0($s['sale_total'])?></td>
                            </tr>
                        <?php endforeach; endif; ?>
                        </tbody>
                    </table>
                </div>

            </div>
        </div>

        <div class="col-12 col-lg-4">
            <div class="cardx p-3">
                <div class="fw-bold">Recent Expenses</div>
                <div class="small-muted">Last 10 expenses entries</div>
                <div class="hrline"></div>

                <div class="table-responsive">
                    <table class="table table-bordered table-sm align-middle mb-0">
                        <thead class="table-dark">
                            <tr>
                                <th>Date</th>
                                <th>Head</th>
                                <th class="text-end">Amount</th>
                            </tr>
                        </thead>
                        <tbody>
                        <?php if(count($recent_exp)==0): ?>
                            <tr><td colspan="3" class="text-center text-muted">No expenses found.</td></tr>
                        <?php else: foreach($recent_exp as $ex): ?>
                            <tr>
                                <td><?=e($ex['exp_date'] ? date('d-M-Y', strtotime($ex['exp_date'])) : '')?></td>
                                <td>
                                    <?=e($ex['head'] ?: 'N/A')?><?= $ex['sub_head'] ? " / ".e($ex['sub_head']) : "" ?>
                                    <?php if($ex['details']): ?>
                                        <div class="small-muted"><?=e($ex['details'])?></div>
                                    <?php endif; ?>
                                </td>
                                <td class="text-end fw-bold"><?=money0($ex['amount'])?></td>
                            </tr>
                        <?php endforeach; endif; ?>
                        </tbody>
                    </table>
                </div>

            </div>
        </div>
    </div>

    <!-- Salesperson table -->
    <div class="row g-3 mt-3">
        <div class="col-12">
            <div class="cardx p-3">
                <div class="fw-bold">Salesperson Performance (Details)</div>
                <div class="small-muted">Approved sales count + total amount</div>
                <div class="hrline"></div>

                <div class="table-responsive">
                    <table class="table table-bordered table-sm align-middle mb-0">
                        <thead class="table-dark">
                            <tr>
                                <th>#</th>
                                <th>Salesperson</th>
                                <th class="text-center">Total Sales</th>
                                <th class="text-end">Sales Amount</th>
                            </tr>
                        </thead>
                        <tbody>
                        <?php if(count($salespersons)==0): ?>
                            <tr><td colspan="4" class="text-center text-muted">No salespersons found.</td></tr>
                        <?php else: $z=1; foreach($salespersons as $sp): ?>
                            <tr>
                                <td><?=$z++?></td>
                                <td><?=e($sp['name'])?></td>
                                <td class="text-center fw-bold"><?=number_format((int)$sp['total_sales'],0)?></td>
                                <td class="text-end fw-bold"><?=money0($sp['sale_amount'])?></td>
                            </tr>
                        <?php endforeach; endif; ?>
                        </tbody>
                    </table>
                </div>

            </div>
        </div>
    </div>

</div>

<script>
// Format ticks without decimals + compact
function fmt0(v){
  try { return (new Intl.NumberFormat('en-US', {maximumFractionDigits:0}).format(v)); }
  catch(e){ return v; }
}

const labels = <?=json_encode($chart_labels)?>;
const sales  = <?=json_encode($chart_sales)?>;
const coll   = <?=json_encode($chart_coll)?>;
const exp    = <?=json_encode($chart_exp)?>;

new Chart(document.getElementById('chartTrend'), {
  type: 'line',
  data: {
    labels: labels,
    datasets: [
      { label: 'Sales', data: sales, tension: 0.25 },
      { label: 'Collection', data: coll, tension: 0.25 },
      { label: 'Expenses', data: exp, tension: 0.25 }
    ]
  },
  options: {
    responsive: true,
    plugins: { legend: { display: true } },
    scales: {
      y: {
        beginAtZero: true,
        ticks: { callback: (v)=>fmt0(v) }
      }
    }
  }
});

const topLabels = <?=json_encode($top_labels)?>;
const topVals   = <?=json_encode($top_vals)?>;

new Chart(document.getElementById('chartStockTop'), {
  type: 'bar',
  data: { labels: topLabels, datasets: [{ label: 'Stock Value', data: topVals }] },
  options: {
    responsive: true,
    plugins: { legend: { display: false } },
    scales: {
      y: { beginAtZero: true, ticks:{ callback:(v)=>fmt0(v) } }
    }
  }
});

const spLabels = <?=json_encode($sp_labels)?>;
const spVals   = <?=json_encode($sp_vals)?>;

new Chart(document.getElementById('chartSP'), {
  type: 'bar',
  data: { labels: spLabels, datasets: [{ label: 'Sales Amount', data: spVals }] },
  options: {
    responsive: true,
    plugins: { legend: { display: false } },
    scales: {
      y: { beginAtZero: true, ticks:{ callback:(v)=>fmt0(v) } }
    }
  }
});
</script>

</body>
</html>