<?php
$reportActive = 'sales_analytics';
$pageTitle = 'Sales & Analytics Report';
require_once __DIR__ . '/../partials/top.php';

$pdo = db();
$dateFrom = $_GET['date_from'] ?? date('Y-m-d', strtotime('-30 days'));
$dateTo = $_GET['date_to'] ?? date('Y-m-d');
$dtFrom = $dateFrom . ' 00:00:00';
$dtTo = $dateTo . ' 23:59:59';

// Revenue & Commission
$stmt = $pdo->prepare("
  SELECT COALESCE(SUM(l.lead_price),0) lead_value, COALESCE(SUM(j.earning_amount),0) vendor_earning, COUNT(j.id) jobs
  FROM jobs j JOIN leads l ON l.id=j.lead_id
  WHERE j.status='completed' AND j.completed_at BETWEEN ? AND ?
");
$stmt->execute([$dtFrom, $dtTo]);
$row = $stmt->fetch(PDO::FETCH_ASSOC);
$totalLeadValue = (int)$row['lead_value'];
$vendorEarning = (int)$row['vendor_earning'];
$jobsCount = (int)$row['jobs'];
$platformCommission = (int)round($totalLeadValue * 0.2);

// Daily trend
$stmt = $pdo->prepare("
  SELECT DATE(j.completed_at) d, COUNT(*) jobs, COALESCE(SUM(l.lead_price),0) lead_val, COALESCE(SUM(j.earning_amount),0) vendor_amt
  FROM jobs j JOIN leads l ON l.id=j.lead_id
  WHERE j.status='completed' AND j.completed_at BETWEEN ? AND ?
  GROUP BY DATE(j.completed_at) ORDER BY d
");
$stmt->execute([$dtFrom, $dtTo]);
$dailyTrend = $stmt->fetchAll(PDO::FETCH_ASSOC);

// By category
$stmt = $pdo->prepare("
  SELECT c.name, COUNT(j.id) jobs, COALESCE(SUM(l.lead_price),0) lead_val, COALESCE(SUM(j.earning_amount),0) vendor_amt
  FROM jobs j JOIN leads l ON l.id=j.lead_id LEFT JOIN categories c ON c.id=l.category_id
  WHERE j.status='completed' AND j.completed_at BETWEEN ? AND ?
  GROUP BY l.category_id, c.name ORDER BY lead_val DESC
");
$stmt->execute([$dtFrom, $dtTo]);
$byCategory = $stmt->fetchAll(PDO::FETCH_ASSOC);

// By payment mode
$stmt = $pdo->prepare("
  SELECT COALESCE(j.payment_mode,l.payment_mode,'online') pm, COUNT(*) cnt, COALESCE(SUM(j.earning_amount),0) amt
  FROM jobs j JOIN leads l ON l.id=j.lead_id
  WHERE j.status='completed' AND j.completed_at BETWEEN ? AND ?
  GROUP BY pm
");
$stmt->execute([$dtFrom, $dtTo]);
$byPayment = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>
<div class="page-header">
  <h1>Sales & Analytics Report</h1>
  <p class="subtitle">Revenue, commission & performance analytics</p>
</div>

<div class="report-filters">
  <form method="get" class="row g-3 align-items-end">
    <div class="col-md-2"><label class="form-label">From</label><input type="date" name="date_from" class="form-control" value="<?php echo htmlspecialchars($dateFrom); ?>"></div>
    <div class="col-md-2"><label class="form-label">To</label><input type="date" name="date_to" class="form-control" value="<?php echo htmlspecialchars($dateTo); ?>"></div>
    <div class="col-md-2"><button type="submit" class="btn btn-dark">Apply</button></div>
  </form>
</div>

<div class="report-summary">
  <div class="summary-item"><div class="label">Total Lead Value</div><div class="value">₹<?php echo number_format($totalLeadValue); ?></div></div>
  <div class="summary-item"><div class="label">Platform Commission (20%)</div><div class="value text-warning">₹<?php echo number_format($platformCommission); ?></div></div>
  <div class="summary-item"><div class="label">Vendor Earning (80%)</div><div class="value text-success">₹<?php echo number_format($vendorEarning); ?></div></div>
  <div class="summary-item"><div class="label">Completed Jobs</div><div class="value"><?php echo $jobsCount; ?></div></div>
</div>

<div class="row g-4 mb-4">
  <div class="col-lg-8">
    <div class="card">
      <div class="card-header">Daily Sales Trend</div>
      <div class="card-body">
        <div class="chart-container"><canvas id="dailyChart"></canvas></div>
      </div>
    </div>
  </div>
  <div class="col-lg-4">
    <div class="card">
      <div class="card-header">Commission vs Vendor</div>
      <div class="card-body">
        <div class="chart-container"><canvas id="splitChart"></canvas></div>
      </div>
    </div>
  </div>
</div>

<div class="row g-4">
  <div class="col-lg-6">
    <div class="card">
      <div class="card-header">Revenue by Category</div>
      <div class="card-body">
        <div class="chart-container"><canvas id="catChart"></canvas></div>
      </div>
    </div>
  </div>
  <div class="col-lg-6">
    <div class="card">
      <div class="card-header">Payment Mode</div>
      <div class="card-body">
        <div class="chart-container"><canvas id="payChart"></canvas></div>
      </div>
    </div>
  </div>
</div>

<script>
const daily = <?php echo json_encode($dailyTrend); ?>;
const byCat = <?php echo json_encode($byCategory); ?>;
const byPay = <?php echo json_encode($byPayment); ?>;
new Chart(document.getElementById('dailyChart'), {
  type: 'line',
  data: {
    labels: daily.map(r=>r.d),
    datasets: [
      { label: 'Lead Value', data: daily.map(r=>r.lead_val), borderColor: '#3B82F6', fill: false, tension: 0.3 },
      { label: 'Vendor Earning', data: daily.map(r=>r.vendor_amt), borderColor: '#10B981', fill: false, tension: 0.3 }
    ]
  },
  options: { responsive: true, maintainAspectRatio: false, scales: { y: { beginAtZero: true } } }
});
new Chart(document.getElementById('splitChart'), {
  type: 'doughnut',
  data: { labels: ['Commission 20%', 'Vendor 80%'], datasets: [{ data: [<?php echo $platformCommission; ?>, <?php echo $vendorEarning; ?>], backgroundColor: ['#F59E0B','#10B981'] }] },
  options: { responsive: true, maintainAspectRatio: false }
});
new Chart(document.getElementById('catChart'), {
  type: 'bar',
  data: { labels: byCat.map(r=>r.name||'Uncategorized'), datasets: [{ label: 'Revenue', data: byCat.map(r=>r.lead_val), backgroundColor: 'rgba(59,130,246,0.7)', borderRadius: 4 }] },
  options: { indexAxis: 'y', responsive: true, maintainAspectRatio: false, scales: { x: { beginAtZero: true } } }
});
new Chart(document.getElementById('payChart'), {
  type: 'doughnut',
  data: { labels: byPay.map(r=>r.pm), datasets: [{ data: byPay.map(r=>r.amt), backgroundColor: ['#3B82F6','#64748b'] }] },
  options: { responsive: true, maintainAspectRatio: false }
});
</script>
<?php require_once __DIR__ . '/../partials/bottom.php'; ?>
