<?php
$pageTitle = 'Reports';
require_once('./config/db.php');
require_once("./includes/auth.php");
include('./includes/header.php');

$day = $_GET['day'] ?? (new DateTime('today'))->format('Y-m-d');
$month = $_GET['month'] ?? (new DateTime())->format('Y-m'); // YYYY-MM

// -------------------------
// DAILY REPORT
// -------------------------
$dailyStmt = $pdo->prepare("
  SELECT 
    sm.Date,
    p.Name,
    si.Quantity,
    si.PricePerUnit,
    p.BuyingPrice,
    si.TotalAmount,
    (si.PricePerUnit - p.BuyingPrice) * si.Quantity AS Profit
  FROM sale_items si
  JOIN sales_master sm ON sm.SaleID = si.SaleID
  JOIN products p ON p.ProductID = si.ProductID
  WHERE DATE(sm.Date) = ?
  ORDER BY sm.Date ASC
");
$dailyStmt->execute([$day]);
$dailyRows = $dailyStmt->fetchAll(PDO::FETCH_ASSOC);

// Daily totals
$dailyTotal = array_reduce($dailyRows, fn($a,$r) => $a + $r['TotalAmount'], 0);
$dailyProfit = array_reduce($dailyRows, fn($a,$r) => $a + $r['Profit'], 0);
$dailyItems = array_reduce($dailyRows, fn($a,$r) => $a + $r['Quantity'], 0);

// -------------------------
// MONTHLY REPORT
// -------------------------
$monthStart = $month . '-01';

// Monthly product summary with profit
$monthlyStmt = $pdo->prepare("
  SELECT 
    p.Name,
    SUM(si.Quantity) AS Quantity,
    SUM(si.TotalAmount) AS Revenue,
    SUM((si.PricePerUnit - p.BuyingPrice) * si.Quantity) AS Profit
  FROM sale_items si
  JOIN sales_master sm ON sm.SaleID = si.SaleID
  JOIN products p ON p.ProductID = si.ProductID
  WHERE sm.Date >= ? AND sm.Date < DATE_ADD(?, INTERVAL 1 MONTH)
  GROUP BY si.ProductID
  ORDER BY Revenue DESC
");
$monthlyStmt->execute([$monthStart, $monthStart]);
$monthlyRows = $monthlyStmt->fetchAll(PDO::FETCH_ASSOC);

// Monthly totals
$monthTotalsStmt = $pdo->prepare("
  SELECT 
    SUM(si.TotalAmount) AS Revenue,
    SUM((si.PricePerUnit - p.BuyingPrice) * si.Quantity) AS Profit,
    SUM(si.Quantity) AS Items
  FROM sale_items si
  JOIN sales_master sm ON sm.SaleID = si.SaleID
  JOIN products p ON p.ProductID = si.ProductID
  WHERE sm.Date >= ? AND sm.Date < DATE_ADD(?, INTERVAL 1 MONTH)
");
$monthTotalsStmt->execute([$monthStart, $monthStart]);
$monthTotals = $monthTotalsStmt->fetch(PDO::FETCH_ASSOC);
?>

<div class="row g-4">
  <!-- DAILY REPORT -->
  <div class="col-md-6">
    <div class="card">
      <div class="card-body">
        <h5>Daily Report</h5>

        <form class="row g-2 mb-3">
          <div class="col-auto">
            <input class="form-control" type="date" name="day" value="<?= htmlspecialchars($day) ?>">
          </div>
          <div class="col-auto">
            <button class="btn btn-primary" type="submit">View</button>
          </div>
        </form>

        <p>
          <strong>Total revenue:</strong> <?= number_format($dailyTotal, 2) ?> |
          <strong>Profit:</strong> <?= number_format($dailyProfit, 2) ?> |
          <strong>Items sold:</strong> <?= (int)$dailyItems ?>
        </p>

        <table class="table table-sm">
          <thead>
            <tr>
              <th>Time</th>
              <th>Product</th>
              <th>Qty</th>
              <th>Unit</th>
              <th>Total</th>
              <th>Profit</th>
            </tr>
          </thead>
          <tbody>
            <?php foreach ($dailyRows as $r): ?>
              <tr>
                <td><?= htmlspecialchars($r['Date']) ?></td>
                <td><?= htmlspecialchars($r['Name']) ?></td>
                <td><?= (int)$r['Quantity'] ?></td>
                <td><?= number_format($r['PricePerUnit'], 2) ?></td>
                <td><?= number_format($r['TotalAmount'], 2) ?></td>
                <td><?= number_format($r['Profit'], 2) ?></td>
              </tr>
            <?php endforeach; ?>

            <?php if (empty($dailyRows)): ?>
              <tr><td colspan="6">No sales for this day</td></tr>
            <?php endif; ?>
          </tbody>
        </table>
      </div>
    </div>
  </div>

  <!-- MONTHLY REPORT -->
  <div class="col-md-6">
    <div class="card">
      <div class="card-body">
        <h5>Monthly Report</h5>

        <form class="row g-2 mb-3">
          <div class="col-auto">
            <input class="form-control" type="month" name="month" value="<?= htmlspecialchars($month) ?>">
          </div>
          <div class="col-auto">
            <button class="btn btn-primary" type="submit">View</button>
          </div>
        </form>

        <p>
          <strong>Total revenue:</strong> <?= number_format($monthTotals['Revenue'] ?? 0, 2) ?> |
          <strong>Profit:</strong> <?= number_format($monthTotals['Profit'] ?? 0, 2) ?> |
          <strong>Items sold:</strong> <?= (int)($monthTotals['Items'] ?? 0) ?>
        </p>

        <table class="table table-sm">
          <thead>
            <tr>
              <th>Product</th>
              <th>Qty</th>
              <th>Revenue</th>
              <th>Profit</th>
            </tr>
          </thead>
          <tbody>
            <?php foreach ($monthlyRows as $r): ?>
              <tr>
                <td><?= htmlspecialchars($r['Name']) ?></td>
                <td><?= (int)$r['Quantity'] ?></td>
                <td><?= number_format($r['Revenue'], 2) ?></td>
                <td><?= number_format($r['Profit'], 2) ?></td>
              </tr>
            <?php endforeach; ?>

            <?php if (empty($monthlyRows)): ?>
              <tr><td colspan="4">No sales for this month</td></tr>
            <?php endif; ?>
          </tbody>
        </table>

      </div>
    </div>
  </div>
</div>

<?php include('./includes/footer.php'); ?>
