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

// Summary - jobs created in period
$stmt = $pdo->prepare("SELECT COUNT(*) FROM jobs WHERE created_at BETWEEN ? AND ?");
$stmt->execute([$dtFrom, $dtTo]);
$totalJobs = (int)$stmt->fetchColumn();

$stmt = $pdo->prepare("SELECT COUNT(*) FROM jobs WHERE status='completed' AND completed_at BETWEEN ? AND ?");
$stmt->execute([$dtFrom, $dtTo]);
$completedJobs = (int)$stmt->fetchColumn();

$stmt = $pdo->prepare("SELECT COUNT(*) FROM jobs WHERE status IN ('accepted','in_progress') AND created_at BETWEEN ? AND ?");
$stmt->execute([$dtFrom, $dtTo]);
$ongoingJobs = (int)$stmt->fetchColumn();

$stmt = $pdo->prepare("SELECT COUNT(*) FROM jobs WHERE status='cancelled' AND created_at BETWEEN ? AND ?");
$stmt->execute([$dtFrom, $dtTo]);
$cancelledJobs = (int)$stmt->fetchColumn();

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

$completionRate = $totalJobs > 0 ? round(($completedJobs / $totalJobs) * 100, 1) : 0;

// By status
$stmt = $pdo->prepare("SELECT status, COUNT(*) cnt FROM jobs WHERE created_at BETWEEN ? AND ? GROUP BY status");
$stmt->execute([$dtFrom, $dtTo]);
$byStatus = $stmt->fetchAll(PDO::FETCH_ASSOC);

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

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

// Recent jobs
$stmt = $pdo->prepare("
  SELECT j.*, v.vendor_code, v.name vendor_name, l.title lead_title, l.location, c.name category_name
  FROM jobs j
  JOIN vendors v ON v.id=j.vendor_id
  JOIN leads l ON l.id=j.lead_id
  LEFT JOIN categories c ON c.id=l.category_id
  WHERE j.created_at BETWEEN ? AND ?
  ORDER BY j.created_at DESC LIMIT 50
");
$stmt->execute([$dtFrom, $dtTo]);
$recentJobs = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>
<div class="page-header">
  <h1>Jobs Report</h1>
  <p class="subtitle">Job lifecycle and completion analytics</p>
</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 Jobs</div><div class="value"><?php echo $totalJobs; ?></div></div>
  <div class="summary-item"><div class="label">Completed</div><div class="value text-success"><?php echo $completedJobs; ?></div></div>
  <div class="summary-item"><div class="label">Ongoing</div><div class="value text-info"><?php echo $ongoingJobs; ?></div></div>
  <div class="summary-item"><div class="label">Cancelled</div><div class="value text-danger"><?php echo $cancelledJobs; ?></div></div>
  <div class="summary-item"><div class="label">Completion Rate</div><div class="value"><?php echo $completionRate; ?>%</div></div>
  <div class="summary-item"><div class="label">Total Earnings</div><div class="value">₹<?php echo number_format($totalEarnings); ?></div></div>
</div>

<div class="row g-4">
  <div class="col-lg-6">
    <div class="card">
      <div class="card-header">Jobs by Status</div>
      <div class="card-body">
        <div class="table-responsive">
          <table class="table">
            <thead><tr><th>Status</th><th>Count</th></tr></thead>
            <tbody>
              <?php foreach ($byStatus as $r): ?>
              <tr><td><span class="badge bg-secondary"><?php echo $r['status']; ?></span></td><td><?php echo $r['cnt']; ?></td></tr>
              <?php endforeach; ?>
              <?php if (empty($byStatus)): ?><tr><td colspan="2" 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">Completed Jobs by Category</div>
      <div class="card-body">
        <div class="table-responsive">
          <table class="table">
            <thead><tr><th>Category</th><th>Jobs</th><th>Earnings</th></tr></thead>
            <tbody>
              <?php foreach ($byCategory as $r): ?>
              <tr><td><?php echo htmlspecialchars($r['name'] ?: 'Uncategorized'); ?></td><td><?php echo $r['cnt']; ?></td><td>₹<?php echo number_format($r['earnings']); ?></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>

<div class="card mt-4">
  <div class="card-header">Daily Completed Jobs</div>
  <div class="card-body">
    <div class="table-responsive">
      <table class="table">
        <thead><tr><th>Date</th><th>Completed</th><th>Earnings</th></tr></thead>
        <tbody>
          <?php foreach ($dailyCompleted as $r): ?>
          <tr><td><?php echo $r['d']; ?></td><td><?php echo $r['cnt']; ?></td><td>₹<?php echo number_format($r['amt']); ?></td></tr>
          <?php endforeach; ?>
          <?php if (empty($dailyCompleted)): ?><tr><td colspan="3" class="text-muted">No data</td></tr><?php endif; ?>
        </tbody>
      </table>
    </div>
  </div>
</div>

<div class="card mt-4">
  <div class="card-header">Recent Jobs (Last 50)</div>
  <div class="card-body p-0">
    <div class="table-responsive">
      <table class="table mb-0">
        <thead><tr><th>ID</th><th>Vendor</th><th>Lead</th><th>Category</th><th>Status</th><th>Earning</th><th>Created</th></tr></thead>
        <tbody>
          <?php foreach ($recentJobs as $j): ?>
          <tr>
            <td>#<?php echo $j['id']; ?></td>
            <td><?php echo htmlspecialchars($j['vendor_code']); ?></td>
            <td><?php echo htmlspecialchars($j['lead_title']); ?></td>
            <td><?php echo htmlspecialchars($j['category_name'] ?? '-'); ?></td>
            <td><span class="badge bg-<?php echo $j['status']==='completed'?'success':($j['status']==='in_progress'?'primary':'warning'); ?>"><?php echo $j['status']; ?></span></td>
            <td>₹<?php echo number_format($j['earning_amount']); ?></td>
            <td class="small"><?php echo date('M d, H:i', strtotime($j['created_at'])); ?></td>
          </tr>
          <?php endforeach; ?>
          <?php if (empty($recentJobs)): ?><tr><td colspan="7" class="text-muted">No jobs in period</td></tr><?php endif; ?>
        </tbody>
      </table>
    </div>
  </div>
</div>
<?php require_once __DIR__ . '/../partials/bottom.php'; ?>
