<?php
requirePermission($conn, 'customer_report', 'view');
$search = trim($_GET['search'] ?? '');
$month = trim($_GET['month'] ?? date('Y-m'));
$monthSafe = preg_match('/^\d{4}-\d{2}$/', $month) ? $month : date('Y-m');
$start = $monthSafe . '-01';
$end = date('Y-m-d', strtotime($start . ' +1 month'));
$safeSearch = sanitize($conn, $search);
$whereCustomer = "c.account_id=$account_id";
if ($safeSearch !== '') $whereCustomer .= " AND (c.customer_name LIKE '%$safeSearch%' OR c.business_name LIKE '%$safeSearch%' OR c.phone LIKE '%$safeSearch%' OR c.email LIKE '%$safeSearch%')";
$leadDate = columnExists($conn,'leads','date_created') ? 'l.date_created' : 'l.id';
$quoteDate = columnExists($conn,'quotations','date_created') ? 'q.date_created' : 'q.id';
$sql = "SELECT c.id,c.customer_name,c.business_name,c.phone,c.email,
COUNT(DISTINCT l.id) leads_total,
COUNT(DISTINCT CASE WHEN st.is_end=1 AND st.result_type='won' THEN l.id END) leads_won,
COUNT(DISTINCT CASE WHEN st.is_end=1 AND st.result_type='lost' THEN l.id END) leads_lost,
COUNT(DISTINCT CASE WHEN (st.id IS NULL OR IFNULL(st.is_end,0)=0) THEN l.id END) leads_open,
COUNT(DISTINCT q.id) quotations_total,
COUNT(DISTINCT CASE WHEN q.status='accepted' THEN q.id END) quotations_accepted,
COUNT(DISTINCT CASE WHEN q.status='rejected' THEN q.id END) quotations_rejected,
COUNT(DISTINCT CASE WHEN q.status IN ('draft','sent') THEN q.id END) quotations_pending,
IFNULL(SUM(CASE WHEN q.status='accepted' THEN q.total ELSE 0 END),0) accepted_value
FROM customers c
LEFT JOIN leads l ON l.customer_id=c.id AND l.account_id=$account_id AND (l.datetime_deleted IS NULL) AND ($leadDate >= '$start' AND $leadDate < '$end')
LEFT JOIN stage st ON st.id=l.stage_id
LEFT JOIN quotations q ON q.customer_id=c.id AND q.account_id=$account_id AND ($quoteDate >= '$start' AND $quoteDate < '$end')
WHERE $whereCustomer GROUP BY c.id ORDER BY accepted_value DESC, leads_total DESC, c.customer_name ASC LIMIT 300";
$list=$conn->query($sql);
$summary=['customers'=>0,'leads'=>0,'won'=>0,'lost'=>0,'open'=>0,'quotes'=>0,'accepted'=>0,'rejected'=>0,'pending'=>0,'value'=>0];
$rows=[];
while($list && $r=$list->fetch_assoc()){
  $rows[]=$r; $summary['customers']++; $summary['leads']+=(int)$r['leads_total']; $summary['won']+=(int)$r['leads_won']; $summary['lost']+=(int)$r['leads_lost']; $summary['open']+=(int)$r['leads_open']; $summary['quotes']+=(int)$r['quotations_total']; $summary['accepted']+=(int)$r['quotations_accepted']; $summary['rejected']+=(int)$r['quotations_rejected']; $summary['pending']+=(int)$r['quotations_pending']; $summary['value']+=(float)$r['accepted_value'];
}
$top=array_slice($rows,0,8); $chartLabels=[]; $chartLeads=[]; $chartValues=[];
foreach($top as $r){ $chartLabels[]=mb_strimwidth($r['customer_name'],0,18,'...'); $chartLeads[]=(int)$r['leads_total']; $chartValues[]=(float)$r['accepted_value']; }
?>
<div class="fade-in customer-report-page">
  <div class="toolbar" style="justify-content:space-between;gap:12px;flex-wrap:wrap"><div><span class="card-title">Customer Report</span><div class="text-xs text-muted">Graphs for customer activity, won leads, quotations and accepted value.</div></div></div>
  <form method="get" class="card report-filter" style="padding:14px 16px;margin-bottom:16px"><input type="hidden" name="page" value="customer_report"><div class="form-grid compact-grid"><div class="form-group"><label class="form-label">Search</label><input class="form-control" name="search" value="<?= htmlspecialchars($search) ?>" placeholder="Customer, business, phone, email"></div><div class="form-group"><label class="form-label">Month</label><input class="form-control" type="month" name="month" value="<?= htmlspecialchars($monthSafe) ?>"></div><div class="form-group" style="align-self:end"><button class="btn btn-primary">Filter</button> <a class="btn btn-secondary" href="index.php?page=customer_report">Clear</a></div></div></form>
  <div class="stats-grid mb-20"><div class="stat-card"><div class="stat-value"><?= number_format($summary['customers']) ?></div><div class="stat-label">Customers</div></div><div class="stat-card"><div class="stat-value"><?= number_format($summary['leads']) ?></div><div class="stat-label">Leads</div></div><div class="stat-card"><div class="stat-value"><?= number_format($summary['won']) ?></div><div class="stat-label">Won Leads</div></div><div class="stat-card"><div class="stat-value"><?= number_format($summary['quotes']) ?></div><div class="stat-label">Quotations</div></div><div class="stat-card"><div class="stat-value"><?= number_format($summary['accepted']) ?></div><div class="stat-label">Accepted</div></div><div class="stat-card"><div class="stat-value">$<?= number_format($summary['value'],2) ?></div><div class="stat-label">Accepted Value</div></div></div>
  <div class="report-graphs">
    <div class="card graph-card"><div class="card-header"><span class="card-title">Top customers by leads</span><span class="text-xs text-muted"><?= htmlspecialchars(date('F Y', strtotime($start))) ?></span></div><canvas id="leadsChart" height="230"></canvas></div>
    <div class="card graph-card"><div class="card-header"><span class="card-title">Lead results</span></div><div class="donut-wrap"><div class="donut" style="--won:<?= (int)$summary['won'] ?>;--lost:<?= (int)$summary['lost'] ?>;--open:<?= (int)$summary['open'] ?>"></div><div class="legend"><span><b class="dot won"></b> Won <?= (int)$summary['won'] ?></span><span><b class="dot lost"></b> Lost <?= (int)$summary['lost'] ?></span><span><b class="dot open"></b> Open <?= (int)$summary['open'] ?></span></div></div></div>
    <div class="card graph-card"><div class="card-header"><span class="card-title">Accepted value by customer</span></div><canvas id="valueChart" height="230"></canvas></div>
    <div class="card graph-card"><div class="card-header"><span class="card-title">Quotations status</span></div><div class="bar-stack"><div><label>Accepted</label><span style="width:<?= $summary['quotes']?max(4,($summary['accepted']/$summary['quotes']*100)):0 ?>%"></span><b><?= (int)$summary['accepted'] ?></b></div><div><label>Pending</label><span style="width:<?= $summary['quotes']?max(4,($summary['pending']/$summary['quotes']*100)):0 ?>%"></span><b><?= (int)$summary['pending'] ?></b></div><div><label>Rejected</label><span style="width:<?= $summary['quotes']?max(4,($summary['rejected']/$summary['quotes']*100)):0 ?>%"></span><b><?= (int)$summary['rejected'] ?></b></div></div></div>
  </div>
  <div class="card" style="margin-top:16px"><div class="card-header"><span class="card-title">Customer details</span><span class="text-xs text-muted">Click view for full customer actions.</span></div><div class="table-wrapper"><table class="crm-table"><thead><tr><th>Customer</th><th>Leads</th><th>Won</th><th>Lost</th><th>Quotations</th><th>Accepted</th><th>Value</th><th>Action</th></tr></thead><tbody><?php if(!$rows): ?><tr><td colspan="8" class="text-center text-muted" style="padding:32px">No data found</td></tr><?php else: foreach($rows as $r): ?><tr><td><strong><?= htmlspecialchars($r['customer_name']) ?></strong><div class="text-xs text-muted"><?= htmlspecialchars($r['business_name'] ?: $r['phone'] ?: '—') ?></div></td><td><?= (int)$r['leads_total'] ?></td><td><span class="badge badge-green"><?= (int)$r['leads_won'] ?></span></td><td><span class="badge badge-red"><?= (int)$r['leads_lost'] ?></span></td><td><?= (int)$r['quotations_total'] ?></td><td><span class="badge badge-green"><?= (int)$r['quotations_accepted'] ?></span></td><td>$<?= number_format((float)$r['accepted_value'],2) ?></td><td><a class="btn btn-secondary btn-sm" href="index.php?page=customer_view&id=<?= crmEid($r['id']) ?>">View</a></td></tr><?php endforeach; endif; ?></tbody></table></div></div>
</div>
<style>.report-graphs{display:grid;grid-template-columns:repeat(2,minmax(0,1fr));gap:16px}.graph-card{padding:16px}.donut-wrap{display:flex;align-items:center;justify-content:center;gap:24px;min-height:230px}.donut{width:170px;height:170px;border-radius:50%;background:conic-gradient(#10b981 0 calc((var(--won)/(var(--won) + var(--lost) + var(--open) + .0001))*100%),#ef4444 0 calc(((var(--won) + var(--lost))/(var(--won) + var(--lost) + var(--open) + .0001))*100%),#3b82f6 0);position:relative}.donut:after{content:'';position:absolute;inset:34px;background:#fff;border-radius:50%}.legend{display:grid;gap:10px;font-weight:700}.dot{width:11px;height:11px;display:inline-block;border-radius:50%;margin-right:6px}.dot.won{background:#10b981}.dot.lost{background:#ef4444}.dot.open{background:#3b82f6}.bar-stack{display:grid;gap:16px;padding:22px}.bar-stack div{display:grid;grid-template-columns:90px 1fr 40px;align-items:center;gap:12px}.bar-stack span{height:14px;border-radius:99px;background:linear-gradient(90deg,#2563eb,#93c5fd)}@media(max-width:900px){.report-graphs{grid-template-columns:1fr}.donut-wrap{flex-direction:column}}</style>
<script>
(function(){
 const labels=<?= json_encode($chartLabels, JSON_UNESCAPED_UNICODE) ?>, leads=<?= json_encode($chartLeads) ?>, values=<?= json_encode($chartValues) ?>;
 function drawBars(id,data,prefix){const c=document.getElementById(id); if(!c) return; const ctx=c.getContext('2d'), w=c.width=c.clientWidth, h=c.height=230, max=Math.max(1,...data); ctx.clearRect(0,0,w,h); ctx.font='12px Inter,Arial'; ctx.fillStyle='#7d95b8'; const left=130, right=18, top=16, row=(h-top-20)/Math.max(1,data.length); data.forEach((v,i)=>{const y=top+i*row+6; ctx.fillStyle='#0f1f3d'; ctx.fillText(labels[i]||'',10,y+10); ctx.fillStyle='#e8eef8'; ctx.fillRect(left,y,w-left-right,14); ctx.fillStyle='#2563eb'; ctx.fillRect(left,y,(w-left-right)*(v/max),14); ctx.fillStyle='#0f1f3d'; ctx.fillText((prefix||'')+(prefix?'':v),left+(w-left-right)*(v/max)+6,y+11); if(prefix) ctx.fillText(prefix+Number(v).toFixed(0),left+(w-left-right)*(v/max)+6,y+11); });}
 drawBars('leadsChart',leads,''); drawBars('valueChart',values,'$'); window.addEventListener('resize',()=>{drawBars('leadsChart',leads,''); drawBars('valueChart',values,'$')});
})();
</script>
