<?php
// public/reports.php
require_once __DIR__ . '/../helpers/auth.php';
require_role(['admin', 'staff']);

$pdo = db();
$title = "Reports - IMS";

// Helpers
function get_str(string $k): string { return trim((string)($_GET[$k] ?? '')); }

$date_from = get_str('from'); // YYYY-MM-DD
$date_to   = get_str('to');   // YYYY-MM-DD

$from_dt = $date_from !== '' ? ($date_from . ' 00:00:00') : null;
$to_dt   = $date_to !== '' ? ($date_to . ' 23:59:59') : null;

// Build time filters
$deliveryWhere = " WHERE 1=1 ";
$moveWhere = " WHERE 1=1 ";
$paramsDel = [];
$paramsMov = [];

if ($from_dt) {
  $deliveryWhere .= " AND d.created_at >= :from ";
  $moveWhere .= " AND m.created_at >= :from ";
  $paramsDel[':from'] = $from_dt;
  $paramsMov[':from'] = $from_dt;
}
if ($to_dt) {
  $deliveryWhere .= " AND d.created_at <= :to ";
  $moveWhere .= " AND m.created_at <= :to ";
  $paramsDel[':to'] = $to_dt;
  $paramsMov[':to'] = $to_dt;
}

// -------------------------------------
// Inventory Summary
// -------------------------------------
$inv = $pdo->query("
  SELECT
    COUNT(*) AS total_products,
    SUM(CASE WHEN is_active=1 THEN 1 ELSE 0 END) AS active_products
  FROM products
")->fetch();

$lowStockStmt = $pdo->query("
  SELECT COUNT(*) AS low_count
  FROM (
    SELECT
      p.id,
      (
        COALESCE(SUM(CASE WHEN m.movement_type='IN' THEN m.quantity ELSE 0 END),0)
        - COALESCE(SUM(CASE WHEN m.movement_type='OUT' THEN m.quantity ELSE 0 END),0)
        + COALESCE(SUM(CASE WHEN m.movement_type='ADJUST' THEN m.quantity ELSE 0 END),0)
      ) AS stock,
      p.reorder_level
    FROM products p
    LEFT JOIN inventory_movements m ON m.product_id = p.id
    GROUP BY p.id
  ) x
  WHERE x.reorder_level > 0 AND x.stock <= x.reorder_level
")->fetch();

$total_products = (int)($inv['total_products'] ?? 0);
$active_products = (int)($inv['active_products'] ?? 0);
$low_count = (int)($lowStockStmt['low_count'] ?? 0);

// -------------------------------------
// Deliveries Summary
// -------------------------------------
$delTotalStmt = $pdo->prepare("SELECT COUNT(*) AS cnt FROM deliveries d $deliveryWhere");
$delTotalStmt->execute($paramsDel);
$deliveries_total = (int)($delTotalStmt->fetch()['cnt'] ?? 0);

$delStatusSql = "
  SELECT d.status, COUNT(*) AS cnt
  FROM deliveries d
  $deliveryWhere
  GROUP BY d.status
  ORDER BY cnt DESC
";
$delStatusStmt = $pdo->prepare($delStatusSql);
$delStatusStmt->execute($paramsDel);
$delByStatus = $delStatusStmt->fetchAll();

// -------------------------------------
// Monthly Deliveries Chart (last 12 months if no range, otherwise based on filter)
// -------------------------------------
if (!$from_dt && !$to_dt) {
  // default: last 12 months including current month
  $chartFrom = date('Y-m-01 00:00:00', strtotime('-11 months'));
  $chartTo = date('Y-m-t 23:59:59'); // end of this month
  $chartWhere = " WHERE d.created_at >= :cfrom AND d.created_at <= :cto ";
  $chartParams = [':cfrom' => $chartFrom, ':cto' => $chartTo];
} else {
  $chartWhere = " WHERE 1=1 ";
  $chartParams = [];
  if ($from_dt) { $chartWhere .= " AND d.created_at >= :cfrom "; $chartParams[':cfrom'] = $from_dt; }
  if ($to_dt)   { $chartWhere .= " AND d.created_at <= :cto ";   $chartParams[':cto']   = $to_dt; }
}

$monthlySql = "
  SELECT DATE_FORMAT(d.created_at, '%Y-%m') AS ym, COUNT(*) AS cnt
  FROM deliveries d
  $chartWhere
  GROUP BY ym
  ORDER BY ym ASC
";
$monthlyStmt = $pdo->prepare($monthlySql);
$monthlyStmt->execute($chartParams);
$monthlyRows = $monthlyStmt->fetchAll();

// -------------------------------------
// Monthly Profit Chart (based on OUT movements × current product prices)
// -------------------------------------
if (!$from_dt && !$to_dt) {
  $pFrom = date('Y-m-01 00:00:00', strtotime('-11 months'));
  $pTo = date('Y-m-t 23:59:59');
  $pWhere = " WHERE m.created_at >= :pfrom AND m.created_at <= :pto ";
  $pParams = [':pfrom' => $pFrom, ':pto' => $pTo];
} else {
  $pWhere = " WHERE 1=1 ";
  $pParams = [];
  if ($from_dt) { $pWhere .= " AND m.created_at >= :pfrom "; $pParams[':pfrom'] = $from_dt; }
  if ($to_dt)   { $pWhere .= " AND m.created_at <= :pto ";   $pParams[':pto']   = $to_dt; }
}

$profitMonthlySql = "
  SELECT
    DATE_FORMAT(m.created_at, '%Y-%m') AS ym,
    COALESCE(SUM(m.quantity * p.sell_price),0) AS revenue,
    COALESCE(SUM(m.quantity * p.cost_price),0) AS cost,
    COALESCE(SUM((m.quantity * p.sell_price) - (m.quantity * p.cost_price)),0) AS profit
  FROM inventory_movements m
  JOIN products p ON p.id = m.product_id
  $pWhere
    AND m.movement_type = 'OUT'
  GROUP BY ym
  ORDER BY ym ASC
";
$profitMonthlyStmt = $pdo->prepare($profitMonthlySql);
$profitMonthlyStmt->execute($pParams);
$profitMonthlyRows = $profitMonthlyStmt->fetchAll();

$profitMonthlyLabels = [];
$profitMonthlyProfit = [];
$profitMonthlyRevenue = [];
$profitMonthlyCost = [];

foreach ($profitMonthlyRows as $r) {
  $profitMonthlyLabels[] = (string)$r['ym'];
  $profitMonthlyRevenue[] = (float)$r['revenue'];
  $profitMonthlyCost[] = (float)$r['cost'];
  $profitMonthlyProfit[] = (float)$r['profit'];
}


$monthlyLabels = [];
$monthlyCounts = [];
foreach ($monthlyRows as $r) {
  $monthlyLabels[] = (string)$r['ym'];
  $monthlyCounts[] = (int)$r['cnt'];
}

// -------------------------------------
// Top Products by OUT (filtered movements)
// -------------------------------------
$topOutSql = "
  SELECT
    p.sku, p.name,
    SUM(m.quantity) AS total_out
  FROM inventory_movements m
  JOIN products p ON p.id = m.product_id
  $moveWhere
    AND m.movement_type = 'OUT'
  GROUP BY p.id
  ORDER BY total_out DESC
  LIMIT 10
";
$topOutStmt = $pdo->prepare($topOutSql);
$topOutStmt->execute($paramsMov);
$topOut = $topOutStmt->fetchAll();

// -------------------------------------
// Top Customers by deliveries
// -------------------------------------
$topCustSql = "
  SELECT
    c.name,
    COUNT(*) AS total_deliveries,
    SUM(CASE WHEN d.status='DELIVERED' THEN 1 ELSE 0 END) AS delivered_count
  FROM deliveries d
  LEFT JOIN customers c ON c.id = d.customer_id
  $deliveryWhere
  GROUP BY d.customer_id
  ORDER BY total_deliveries DESC
  LIMIT 10
";
$topCustStmt = $pdo->prepare($topCustSql);
$topCustStmt->execute($paramsDel);
$topCustomers = $topCustStmt->fetchAll();

// -------------------------------------
// Top Drivers by delivered deliveries
// -------------------------------------
$topDrvSql = "
  SELECT
    u.full_name,
    COUNT(*) AS assigned_count,
    SUM(CASE WHEN d.status='DELIVERED' THEN 1 ELSE 0 END) AS delivered_count
  FROM deliveries d
  LEFT JOIN users u ON u.id = d.assigned_driver_id
  $deliveryWhere
  GROUP BY d.assigned_driver_id
  ORDER BY delivered_count DESC, assigned_count DESC
  LIMIT 10
";
$topDrvStmt = $pdo->prepare($topDrvSql);
$topDrvStmt->execute($paramsDel);
$topDrivers = $topDrvStmt->fetchAll();

// -------------------------------------
// Profit Report (based on OUT movements × product prices)
// NOTE: uses current product cost_price/sell_price (simple approach)
// -------------------------------------
$profitSql = "
  SELECT
    p.id,
    p.sku,
    p.name,
    SUM(m.quantity) AS qty_out,
    (SUM(m.quantity) * p.sell_price) AS revenue,
    (SUM(m.quantity) * p.cost_price) AS cost,
    ((SUM(m.quantity) * p.sell_price) - (SUM(m.quantity) * p.cost_price)) AS profit
  FROM inventory_movements m
  JOIN products p ON p.id = m.product_id
  $moveWhere
    AND m.movement_type = 'OUT'
  GROUP BY p.id
  ORDER BY profit DESC
  LIMIT 10
";
$profitStmt = $pdo->prepare($profitSql);
$profitStmt->execute($paramsMov);
$profitTop = $profitStmt->fetchAll();

// Totals
$profitTotalSql = "
  SELECT
    COALESCE(SUM(m.quantity),0) AS qty_out,
    COALESCE(SUM(m.quantity * p.sell_price),0) AS revenue,
    COALESCE(SUM(m.quantity * p.cost_price),0) AS cost,
    COALESCE(SUM((m.quantity * p.sell_price) - (m.quantity * p.cost_price)),0) AS profit
  FROM inventory_movements m
  JOIN products p ON p.id = m.product_id
  $moveWhere
    AND m.movement_type = 'OUT'
";
$profitTotalStmt = $pdo->prepare($profitTotalSql);
$profitTotalStmt->execute($paramsMov);
$profitTotals = $profitTotalStmt->fetch();

$profit_qty = (int)($profitTotals['qty_out'] ?? 0);
$profit_rev = (float)($profitTotals['revenue'] ?? 0);
$profit_cost = (float)($profitTotals['cost'] ?? 0);
$profit_profit = (float)($profitTotals['profit'] ?? 0);

// Build export link with same filters
$exportUrl = "reports_export_csv.php";
$qs = $_GET ? ("?" . http_build_query($_GET)) : "";
$exportUrl .= $qs;

require_once __DIR__ . '/../templates/header.php';
require_once __DIR__ . '/../templates/sidebar.php';
?>

<main class="col-12 col-md-9 col-lg-10 p-3 p-md-4">
  <div class="d-flex flex-wrap gap-2 align-items-center justify-content-between mb-3">
    <h4 class="mb-0">Reports</h4>

    <div class="d-flex flex-wrap gap-2">
      <form class="d-flex gap-2" method="GET">
        <input class="form-control form-control-sm" type="date" name="from" value="<?= htmlspecialchars($date_from) ?>">
        <input class="form-control form-control-sm" type="date" name="to" value="<?= htmlspecialchars($date_to) ?>">
        <button class="btn btn-sm btn-outline-primary" type="submit">Apply</button>
        <a class="btn btn-sm btn-outline-secondary" href="reports.php">Reset</a>
      </form>

      <a class="btn btn-sm btn-success" href="<?= htmlspecialchars($exportUrl) ?>">Export CSV</a>
      <a class="btn btn-sm btn-danger" href="<?= htmlspecialchars('reports_export_pdf.php' . $qs) ?>">Export PDF</a>

    </div>
  </div>

  <!-- Summary cards -->
  <div class="row g-3 mb-3">
    <div class="col-12 col-md-4">
      <div class="card shadow-sm">
        <div class="card-body">
          <div class="text-muted small">Products</div>
          <div class="fs-4 fw-semibold"><?= number_format($total_products) ?></div>
          <div class="text-muted small">Active: <?= number_format($active_products) ?> | Low stock: <?= number_format($low_count) ?></div>
        </div>
      </div>
    </div>

    <div class="col-12 col-md-4">
      <div class="card shadow-sm">
        <div class="card-body">
          <div class="text-muted small">Deliveries</div>
          <div class="fs-4 fw-semibold"><?= number_format($deliveries_total) ?></div>
          <div class="text-muted small"><?= ($date_from || $date_to) ? 'Filtered range' : 'All time' ?></div>
        </div>
      </div>
    </div>

    <div class="col-12 col-md-4">
      <div class="card shadow-sm">
        <div class="card-body">
          <div class="text-muted small">Profit Summary (OUT)</div>
          <div class="fs-4 fw-semibold"><?= number_format($profit_profit, 2) ?></div>
          <div class="text-muted small">
            Revenue: <?= number_format($profit_rev, 2) ?> | Cost: <?= number_format($profit_cost, 2) ?>
          </div>
        </div>
      </div>
    </div>
  </div>

  <!-- Monthly chart -->
  <div class="card shadow-sm mb-3">
    <div class="card-body">
      <div class="d-flex align-items-center justify-content-between mb-2">
        <h6 class="mb-0">Monthly Deliveries</h6>
        <div class="small text-muted">
          <?= (!$from_dt && !$to_dt) ? "Last 12 months" : "Filtered range" ?>
        </div>
      </div>
      <div style="height:280px;">
        <canvas id="monthlyDeliveriesChart"></canvas>
      </div>
      <div class="text-muted small mt-2">
        
      </div>
    </div>
  </div>

  <div class="card shadow-sm mb-3">
  <div class="card-body">
    <div class="d-flex align-items-center justify-content-between mb-2">
      <h6 class="mb-0">Monthly Profit (OUT)</h6>
      <div class="small text-muted">
        <?= (!$from_dt && !$to_dt) ? "Last 12 months" : "Filtered range" ?>
      </div>
    </div>

    <div style="height:280px;">
      <canvas id="monthlyProfitChart"></canvas>
    </div>

    <div class="text-muted small mt-2">
      Revenue/Cost/Profit are computed from OUT movements × current product prices.
    </div>
  </div>
</div>


  <!-- Deliveries by status -->
  <div class="card shadow-sm mb-3">
    <div class="card-body">
      <h6 class="mb-3">Deliveries by Status</h6>
      <div class="table-responsive">
        <table class="table table-sm align-middle mb-0">
          <thead>
            <tr>
              <th>Status</th>
              <th class="text-end">Count</th>
            </tr>
          </thead>
          <tbody>
            <?php if (!$delByStatus): ?>
              <tr><td colspan="2" class="text-muted">No deliveries found.</td></tr>
            <?php else: ?>
              <?php foreach ($delByStatus as $row): ?>
                <tr>
                  <td><span class="badge text-bg-secondary"><?= htmlspecialchars($row['status']) ?></span></td>
                  <td class="text-end fw-semibold"><?= (int)$row['cnt'] ?></td>
                </tr>
              <?php endforeach; ?>
            <?php endif; ?>
          </tbody>
        </table>
      </div>
    </div>
  </div>

  <div class="row g-3">
    <!-- Top OUT -->
    <div class="col-12 col-lg-6">
      <div class="card shadow-sm">
        <div class="card-body">
          <h6 class="mb-3">Top Products by Stock OUT</h6>
          <div class="table-responsive">
            <table class="table table-sm align-middle mb-0">
              <thead>
                <tr>
                  <th>SKU</th>
                  <th>Product</th>
                  <th class="text-end">Total OUT</th>
                </tr>
              </thead>
              <tbody>
                <?php if (!$topOut): ?>
                  <tr><td colspan="3" class="text-muted">No OUT movements found.</td></tr>
                <?php else: ?>
                  <?php foreach ($topOut as $r): ?>
                    <tr>
                      <td><?= htmlspecialchars($r['sku']) ?></td>
                      <td><?= htmlspecialchars($r['name']) ?></td>
                      <td class="text-end fw-semibold"><?= (int)$r['total_out'] ?></td>
                    </tr>
                  <?php endforeach; ?>
                <?php endif; ?>
              </tbody>
            </table>
          </div>
        </div>
      </div>
    </div>

    <!-- Profit Top -->
    <div class="col-12 col-lg-6">
      <div class="card shadow-sm">
        <div class="card-body">
          <h6 class="mb-3">Top Products by Profit (OUT)</h6>
          <div class="table-responsive">
            <table class="table table-sm align-middle mb-0">
              <thead>
                <tr>
                  <th>SKU</th>
                  <th>Product</th>
                  <th class="text-end">Qty OUT</th>
                  <th class="text-end">Revenue</th>
                  <th class="text-end">Cost</th>
                  <th class="text-end">Profit</th>
                </tr>
              </thead>
              <tbody>
                <?php if (!$profitTop): ?>
                  <tr><td colspan="6" class="text-muted">No OUT movements found.</td></tr>
                <?php else: ?>
                  <?php foreach ($profitTop as $r): ?>
                    <tr>
                      <td><?= htmlspecialchars($r['sku']) ?></td>
                      <td><?= htmlspecialchars($r['name']) ?></td>
                      <td class="text-end fw-semibold"><?= (int)$r['qty_out'] ?></td>
                      <td class="text-end"><?= number_format((float)$r['revenue'], 2) ?></td>
                      <td class="text-end"><?= number_format((float)$r['cost'], 2) ?></td>
                      <td class="text-end fw-semibold"><?= number_format((float)$r['profit'], 2) ?></td>
                    </tr>
                  <?php endforeach; ?>
                <?php endif; ?>
              </tbody>
            </table>
          </div>

          <div class="text-muted small mt-2">
            
          </div>
        </div>
      </div>
    </div>

    <!-- Top Customers -->
    <div class="col-12 col-lg-6">
      <div class="card shadow-sm">
        <div class="card-body">
          <h6 class="mb-3">Top Customers by Deliveries</h6>
          <div class="table-responsive">
            <table class="table table-sm align-middle mb-0">
              <thead>
                <tr>
                  <th>Customer</th>
                  <th class="text-end">Total</th>
                  <th class="text-end">Delivered</th>
                </tr>
              </thead>
              <tbody>
                <?php if (!$topCustomers): ?>
                  <tr><td colspan="3" class="text-muted">No deliveries found.</td></tr>
                <?php else: ?>
                  <?php foreach ($topCustomers as $r): ?>
                    <tr>
                      <td><?= htmlspecialchars($r['name'] ?? 'Unknown') ?></td>
                      <td class="text-end fw-semibold"><?= (int)$r['total_deliveries'] ?></td>
                      <td class="text-end"><?= (int)$r['delivered_count'] ?></td>
                    </tr>
                  <?php endforeach; ?>
                <?php endif; ?>
              </tbody>
            </table>
          </div>
        </div>
      </div>
    </div>

    <!-- Top Drivers -->
    <div class="col-12 col-lg-6">
      <div class="card shadow-sm">
        <div class="card-body">
          <h6 class="mb-3">Top Drivers</h6>
          <div class="table-responsive">
            <table class="table table-sm align-middle mb-0">
              <thead>
                <tr>
                  <th>Driver</th>
                  <th class="text-end">Assigned</th>
                  <th class="text-end">Delivered</th>
                </tr>
              </thead>
              <tbody>
                <?php if (!$topDrivers): ?>
                  <tr><td colspan="3" class="text-muted">No driver deliveries found.</td></tr>
                <?php else: ?>
                  <?php foreach ($topDrivers as $r): ?>
                    <tr>
                      <td><?= htmlspecialchars($r['full_name'] ?? 'Unassigned') ?></td>
                      <td class="text-end fw-semibold"><?= (int)$r['assigned_count'] ?></td>
                      <td class="text-end"><?= (int)$r['delivered_count'] ?></td>
                    </tr>
                  <?php endforeach; ?>
                <?php endif; ?>
              </tbody>
            </table>
          </div>
          <div class="text-muted small mt-2">
            Delivered count uses delivery status = <b>DELIVERED</b>.
          </div>
        </div>
      </div>
    </div>
  </div>

  <script src="https://cdn.jsdelivr.net/npm/chart.js@4.4.1/dist/chart.umd.min.js"></script>
  <script>
    const labels = <?= json_encode($monthlyLabels) ?>;
    const counts = <?= json_encode($monthlyCounts) ?>;

    const ctx = document.getElementById('monthlyDeliveriesChart');
    if (ctx) {
      new Chart(ctx, {
        type: 'line',
        data: {
          labels: labels,
          datasets: [{
            label: 'Deliveries',
            data: counts,
            tension: 0.25
          }]
        },
        options: {
          responsive: true,
          maintainAspectRatio: false,
          scales: {
            y: { beginAtZero: true }
          }
        }
      });
    }

     const pLabels = <?= json_encode($profitMonthlyLabels) ?>;
  const pRevenue = <?= json_encode($profitMonthlyRevenue) ?>;
  const pCost = <?= json_encode($profitMonthlyCost) ?>;
  const pProfit = <?= json_encode($profitMonthlyProfit) ?>;

  const pctx = document.getElementById('monthlyProfitChart');
  if (pctx) {
    new Chart(pctx, {
      type: 'line',
      data: {
        labels: pLabels,
        datasets: [
          { label: 'Revenue', data: pRevenue, tension: 0.25 },
          { label: 'Cost', data: pCost, tension: 0.25 },
          { label: 'Profit', data: pProfit, tension: 0.25 }
        ]
      },
      options: {
        responsive: true,
        maintainAspectRatio: false,
        scales: {
          y: { beginAtZero: true }
        }
      }
    });
  }
  </script>

</main>

<?php require_once __DIR__ . '/../templates/footer.php'; ?>
