<?php
$reportActive = 'management';
$pageTitle = 'Management 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');

// Executive Summary
$stmt = $pdo->prepare("SELECT COALESCE(SUM(earning_amount),0) FROM jobs WHERE status='completed' AND completed_at BETWEEN ? AND ?");
$stmt->execute([$dateFrom . ' 00:00:00', $dateTo . ' 23:59:59']);
$totalRevenue = (int)$stmt->fetchColumn();

$stmt = $pdo->prepare("SELECT COUNT(*) FROM jobs WHERE status='completed' AND completed_at BETWEEN ? AND ?");
$stmt->execute([$dateFrom . ' 00:00:00', $dateTo . ' 23:59:59']);
$jobsCompleted = (int)$stmt->fetchColumn();

$stmt = $pdo->prepare("SELECT COUNT(*) FROM leads WHERE created_at BETWEEN ? AND ?");
$stmt->execute([$dateFrom . ' 00:00:00', $dateTo . ' 23:59:59']);
$leadsCreated = (int)$stmt->fetchColumn();

$stmt = $pdo->prepare("SELECT COUNT(*) FROM leads WHERE is_available=0 AND created_at BETWEEN ? AND ?");
$stmt->execute([$dateFrom . ' 00:00:00', $dateTo . ' 23:59:59']);
$leadsConverted = (int)$stmt->fetchColumn();

$stmt = $pdo->prepare("SELECT COUNT(*) FROM vendors WHERE created_at BETWEEN ? AND ?");
$stmt->execute([$dateFrom . ' 00:00:00', $dateTo . ' 23:59:59']);
$vendorsCreated = (int)$stmt->fetchColumn();

$stmt = $pdo->prepare("SELECT COALESCE(SUM(amount),0) FROM withdrawal_requests WHERE status='pending' AND created_at BETWEEN ? AND ?");
$stmt->execute([$dateFrom . ' 00:00:00', $dateTo . ' 23:59:59']);
$pendingWithdrawals = (int)$stmt->fetchColumn();

// Platform commission (debits from lead picks)
$stmt = $pdo->prepare("SELECT COALESCE(SUM(amount),0) FROM wallet_transactions WHERE type='debit' AND ref_type='lead' AND created_at BETWEEN ? AND ?");
$stmt->execute([$dateFrom . ' 00:00:00', $dateTo . ' 23:59:59']);
$platformCommission = (int)$stmt->fetchColumn();

// User orders (app) - if tables exist
$userOrdersCount = 0;
$userOrdersRevenue = 0;
$newAppUsers = 0;
try {
  $stmt = $pdo->prepare("SELECT COUNT(*), COALESCE(SUM(total_amount),0) FROM user_orders WHERE created_at BETWEEN ? AND ? AND payment_status='paid'");
  $stmt->execute([$dateFrom . ' 00:00:00', $dateTo . ' 23:59:59']);
  $r = $stmt->fetch();
  $userOrdersCount = (int)$r[0];
  $userOrdersRevenue = (int)$r[1];
  $stmt = $pdo->prepare("SELECT COUNT(*) FROM users WHERE created_at BETWEEN ? AND ?");
  $stmt->execute([$dateFrom . ' 00:00:00', $dateTo . ' 23:59:59']);
  $newAppUsers = (int)$stmt->fetchColumn();
} catch (Exception $e) {}

// Daily breakdown
$dailyBreakdown = $pdo->prepare("
  SELECT DATE(j.completed_at) d, COUNT(*) jobs, COALESCE(SUM(j.earning_amount),0) revenue
  FROM jobs j
  WHERE j.status='completed' AND j.completed_at BETWEEN ? AND ?
  GROUP BY DATE(j.completed_at) ORDER BY d
");
$dailyBreakdown->execute([$dateFrom . ' 00:00:00', $dateTo . ' 23:59:59']);
$dailyData = $dailyBreakdown->fetchAll(PDO::FETCH_ASSOC);

// Category performance
$catPerf = $pdo->prepare("
  SELECT c.name, COUNT(j.id) jobs, COALESCE(SUM(j.earning_amount),0) revenue
  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
");
$catPerf->execute([$dateFrom . ' 00:00:00', $dateTo . ' 23:59:59']);
$categoryPerformance = $catPerf->fetchAll(PDO::FETCH_ASSOC);
?>
<div class="page-header d-flex justify-content-between align-items-start flex-wrap gap-3">
  <div>
    <h1>Management Report</h1>
    <p class="subtitle">Executive summary and key performance indicators</p>
  </div>
  <a href="management_report.php?date_from=<?php echo urlencode($dateFrom); ?>&date_to=<?php echo urlencode($dateTo); ?>&export=csv" class="btn btn-outline-dark btn-export">📥 Export CSV</a>
</div>

<?php
$dateFrom = $_GET['date_from'] ?? date('Y-m-d', strtotime('-30 days'));
$dateTo = $_GET['date_to'] ?? date('Y-m-d');
?>
<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 d-flex gap-2"><button type="submit" class="btn btn-dark">Apply</button><a href="?" class="btn btn-outline-secondary">Reset</a></div>
  </form>
</div>

<div class="report-summary">
  <div class="summary-item">
    <div class="label">Total Revenue</div>
    <div class="value text-success">₹<?php echo number_format($totalRevenue); ?></div>
  </div>
  <div class="summary-item">
    <div class="label">Jobs Completed</div>
    <div class="value"><?php echo $jobsCompleted; ?></div>
  </div>
  <div class="summary-item">
    <div class="label">Leads Created</div>
    <div class="value"><?php echo $leadsCreated; ?></div>
  </div>
  <div class="summary-item">
    <div class="label">Leads Converted</div>
    <div class="value"><?php echo $leadsConverted; ?></div>
  </div>
  <div class="summary-item">
    <div class="label">New Vendors</div>
    <div class="value"><?php echo $vendorsCreated; ?></div>
  </div>
  <div class="summary-item">
    <div class="label">Platform Commission</div>
    <div class="value">₹<?php echo number_format($platformCommission); ?></div>
  </div>
  <div class="summary-item">
    <div class="label">Pending Withdrawals</div>
    <div class="value text-warning">₹<?php echo number_format($pendingWithdrawals); ?></div>
  </div>
  <?php if (isset($userOrdersCount)): ?>
  <div class="summary-item">
    <div class="label">User Orders (Paid)</div>
    <div class="value"><?php echo $userOrdersCount; ?></div>
  </div>
  <div class="summary-item">
    <div class="label">User Orders Revenue</div>
    <div class="value text-success">₹<?php echo number_format($userOrdersRevenue); ?></div>
  </div>
  <div class="summary-item">
    <div class="label">New App Users</div>
    <div class="value"><?php echo $newAppUsers; ?></div>
  </div>
  <?php endif; ?>
</div>

<div class="row g-4">
  <div class="col-lg-8">
    <div class="card">
      <div class="card-header">Daily Revenue & Jobs</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 ($dailyData as $r): ?>
              <tr>
                <td><?php echo htmlspecialchars($r['d']); ?></td>
                <td><?php echo $r['jobs']; ?></td>
                <td>₹<?php echo number_format($r['revenue']); ?></td>
              </tr>
              <?php endforeach; ?>
              <?php if (empty($dailyData)): ?><tr><td colspan="3" class="text-muted">No data for selected period</td></tr><?php endif; ?>
            </tbody>
          </table>
        </div>
      </div>
    </div>
  </div>
  <div class="col-lg-4">
    <div class="card">
      <div class="card-header">Performance by Category</div>
      <div class="card-body">
        <div class="table-responsive">
          <table class="table table-sm">
            <thead><tr><th>Category</th><th>Jobs</th><th>Revenue</th></tr></thead>
            <tbody>
              <?php foreach ($categoryPerformance 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($categoryPerformance)): ?><tr><td colspan="3" class="text-muted">No data</td></tr><?php endif; ?>
            </tbody>
          </table>
        </div>
      </div>
    </div>
  </div>
</div>
<?php require_once __DIR__ . '/../partials/bottom.php'; ?>
