<?php
$reportActive = 'sales';
$pageTitle = 'Sales 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';

// Total revenue (job earnings)
$stmt = $pdo->prepare("SELECT COALESCE(SUM(earning_amount),0) FROM jobs WHERE status='completed' AND completed_at BETWEEN ? AND ?");
$stmt->execute([$dtFrom, $dtTo]);
$totalRevenue = (int)$stmt->fetchColumn();

// User orders revenue (app)
$userOrdersRevenue = 0;
try {
  $stmt = $pdo->prepare("SELECT COALESCE(SUM(total_amount),0) FROM user_orders WHERE created_at BETWEEN ? AND ? AND payment_status='paid'");
  $stmt->execute([$dtFrom, $dtTo]);
  $userOrdersRevenue = (int)$stmt->fetchColumn();
} catch (Exception $e) {}

// Online vs COD
$stmt = $pdo->prepare("SELECT payment_mode, COALESCE(SUM(earning_amount),0) amt, COUNT(*) cnt FROM jobs WHERE status='completed' AND completed_at BETWEEN ? AND ? GROUP BY payment_mode");
$stmt->execute([$dtFrom, $dtTo]);
$byPaymentMode = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Revenue by category
$stmt = $pdo->prepare("
  SELECT c.name, COALESCE(SUM(j.earning_amount),0) revenue, COUNT(j.id) jobs
  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 revenue DESC
");
$stmt->execute([$dtFrom, $dtTo]);
$byCategory = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Top vendors by revenue
$stmt = $pdo->prepare("
  SELECT v.vendor_code, v.name, COALESCE(SUM(j.earning_amount),0) revenue, COUNT(j.id) jobs
  FROM jobs j JOIN vendors v ON v.id=j.vendor_id
  WHERE j.status='completed' AND j.completed_at BETWEEN ? AND ?
  GROUP BY j.vendor_id ORDER BY revenue DESC LIMIT 15
");
$stmt->execute([$dtFrom, $dtTo]);
$topVendors = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Daily sales
$stmt = $pdo->prepare("
  SELECT DATE(completed_at) d, COALESCE(SUM(earning_amount),0) revenue, COUNT(*) jobs
  FROM jobs WHERE status='completed' AND completed_at BETWEEN ? AND ?
  GROUP BY DATE(completed_at) ORDER BY d
");
$stmt->execute([$dtFrom, $dtTo]);
$dailySales = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>
<div class="page-header d-flex justify-content-between align-items-start flex-wrap gap-3">
  <div>
    <h1>Sales Report</h1>
    <p class="subtitle">Revenue analytics and sales performance</p>
  </div>
</div>

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

<div class="report-summary">
  <div class="summary-item">
    <div class="label">Total Revenue (Jobs)</div>
    <div class="value text-success">₹<?php echo number_format($totalRevenue); ?></div>
  </div>
  <?php if ($userOrdersRevenue > 0): ?>
  <div class="summary-item">
    <div class="label">User Orders Revenue (App)</div>
    <div class="value text-info">₹<?php echo number_format($userOrdersRevenue); ?></div>
  </div>
  <?php endif; ?>
  <?php foreach ($byPaymentMode as $pm): ?>
  <div class="summary-item">
    <div class="label"><?php echo ucfirst($pm['payment_mode'] ?? 'online'); ?> (<?php echo $pm['cnt']; ?> jobs)</div>
    <div class="value">₹<?php echo number_format($pm['amt']); ?></div>
  </div>
  <?php endforeach; ?>
</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="table-responsive">
          <table class="table">
            <thead><tr><th>Category</th><th>Jobs</th><th>Revenue</th></tr></thead>
            <tbody>
              <?php foreach ($byCategory as $r): ?>
              <tr><td><?php echo htmlspecialchars($r['name'] ?: 'Uncategorized'); ?></td><td><?php echo $r['jobs']; ?></td><td>₹<?php echo number_format($r['revenue']); ?></td></tr>
              <?php endforeach; ?>
              <?php if (empty($byCategory)): ?><tr><td colspan="3" class="text-muted">No data</td></tr><?php endif; ?>
            </tbody>
          </table>
        </div>
      </div>
    </div>
  </div>
  <div class="col-lg-6">
    <div class="card">
      <div class="card-header">Top Vendors by Revenue</div>
      <div class="card-body">
        <div class="table-responsive">
          <table class="table">
            <thead><tr><th>Vendor</th><th>Jobs</th><th>Revenue</th></tr></thead>
            <tbody>
              <?php foreach ($topVendors as $v): ?>
              <tr><td><strong><?php echo htmlspecialchars($v['vendor_code']); ?></strong> <?php echo htmlspecialchars($v['name']); ?></td><td><?php echo $v['jobs']; ?></td><td>₹<?php echo number_format($v['revenue']); ?></td></tr>
              <?php endforeach; ?>
              <?php if (empty($topVendors)): ?><tr><td colspan="3" class="text-muted">No data</td></tr><?php endif; ?>
            </tbody>
          </table>
        </div>
      </div>
    </div>
  </div>
</div>

<div class="card mt-4">
  <div class="card-header">Daily Sales Breakdown</div>
  <div class="card-body">
    <div class="table-responsive">
      <table class="table">
        <thead><tr><th>Date</th><th>Jobs</th><th>Revenue</th></tr></thead>
        <tbody>
          <?php foreach ($dailySales as $r): ?>
          <tr><td><?php echo $r['d']; ?></td><td><?php echo $r['jobs']; ?></td><td>₹<?php echo number_format($r['revenue']); ?></td></tr>
          <?php endforeach; ?>
          <?php if (empty($dailySales)): ?><tr><td colspan="3" class="text-muted">No data for period</td></tr><?php endif; ?>
        </tbody>
      </table>
    </div>
  </div>
</div>
<?php require_once __DIR__ . '/../partials/bottom.php'; ?>
