<?php
require 'header.php';
require 'db.php';
require 'unit_converters.php';

// Date filters
$start_raw = $_GET['start'] ?? '';
$end_raw   = $_GET['end'] ?? '';

function valid_ymd($d){ return preg_match('/^\d{4}-\d{2}-\d{2}$/', $d); }

if ($start_raw && valid_ymd($start_raw)) $start_sql = $conn->real_escape_string($start_raw) . ' 00:00:00';
if ($end_raw && valid_ymd($end_raw)) $end_sql = $conn->real_escape_string($end_raw) . ' 23:59:59';

$where_out = $where_sale = '';
if (!empty($start_sql) && !empty($end_sql)) {
  $where_out = "WHERE o.created_at BETWEEN '{$start_sql}' AND '{$end_sql}'";
  $where_sale = "WHERE sa.sold_at BETWEEN '{$start_sql}' AND '{$end_sql}'";
} elseif (!empty($start_sql)) {
  $where_out = "WHERE o.created_at >= '{$start_sql}'";
  $where_sale = "WHERE sa.sold_at >= '{$start_sql}'";
} elseif (!empty($end_sql)) {
  $where_out = "WHERE o.created_at <= '{$end_sql}'";
  $where_sale = "WHERE sa.sold_at <= '{$end_sql}'";
}

// Combined query
$combined_sql = "
    SELECT o.id, o.stock_id, COALESCE(s.name, '- (deleted) -') AS item_name, 
           o.out_weight_g AS weight_g, o.reason AS note, o.created_at AS at, 'Stock Out' AS type
    FROM stock_out_records o
    LEFT JOIN stock s ON o.stock_id = s.id
    {$where_out}
    UNION ALL
    SELECT sa.id, sa.stock_id, COALESCE(st.name, '- (deleted) -') AS item_name, 
           sa.sold_weight_g AS weight_g, CONCAT('Sale — qty:', sa.qty) AS note, sa.sold_at AS at, 'Sale' AS type
    FROM sales sa
    LEFT JOIN stock st ON sa.stock_id = st.id
    {$where_sale}
    ORDER BY at DESC
";
$combined = $conn->query($combined_sql);

$outRes = $conn->query("SELECT o.*, COALESCE(s.name, '- (deleted) -') as item_name FROM stock_out_records o LEFT JOIN stock s ON o.stock_id = s.id {$where_out} ORDER BY o.created_at DESC");
$sales  = $conn->query("SELECT sa.*, COALESCE(st.name, '- (deleted) -') as item_name FROM sales sa LEFT JOIN stock st ON sa.stock_id = st.id {$where_sale} ORDER BY sa.sold_at DESC");
?>

<style>
@page { size: A4; margin: 10mm; }
@media print {
  body * { visibility: hidden !important; }
  .report-printable, .report-printable * { visibility: visible !important; }
  .report-printable {
    position: absolute; left:0; top:0;
    width:210mm; height:auto;
    -webkit-print-color-adjust: exact;
    print-color-adjust: exact;
  }
  table { page-break-inside:auto; }
  tr { page-break-inside:avoid; }
  thead { display: table-header-group; }
}
.print-controls { margin-bottom: 12px; display:flex; gap:8px; flex-wrap:wrap; }

/* table base */
.report-table { width:100%; border-collapse:collapse; font-family:Inter,Arial; font-size:14px; }
.report-table th, .report-table td { padding:6px 8px; border-bottom:1px solid #eee; text-align:left; }

/* highlight types */
tr.type-sale { background:#e8f9f0; }          /* light green */
tr.type-sale:hover { background:#d6f4e3; }

tr.type-stockout { background:#ffecec; }       /* light red */
tr.type-stockout:hover { background:#ffd6d6; }

/* printing colors keep visible */
@media print {
  tr.type-sale { background:#c8f2d8 !important; }
  tr.type-stockout { background:#f9c8c8 !important; }
}
</style>

<div class="card">
  <h2>Combined Report (Stock Out & Sales)</h2>

  <form method="get" class="print-controls">
    <label>Start date<br><input type="date" name="start" value="<?php echo htmlspecialchars($start_raw) ?>"></label>
    <label>End date<br><input type="date" name="end" value="<?php echo htmlspecialchars($end_raw) ?>"></label>
    <div style="align-self:end">
      <button type="submit" class="btn btn-primary">Apply</button>
      <a href="reports.php" class="btn">Clear</a>
      <button type="button" onclick="window.print()" class="btn">Print</button>
    </div>
  </form>

  <div class="report-printable">
    <table class="report-table">
      <thead>
        <tr>
          <th>Type</th>
          <th>ID</th>
          <th>Item</th>
          <th>Weight</th>
          <th>Note</th>
          <th>At</th>
        </tr>
      </thead>
      <tbody>
      <?php if ($combined && $combined->num_rows): ?>
        <?php while($r = $combined->fetch_assoc()):
          $u = gramsToUnits(floatval($r['weight_g'] ?? 0));
          $rowClass = ($r['type'] === 'Sale') ? 'type-sale' : 'type-stockout';
        ?>
        <tr class="<?php echo $rowClass ?>">
          <td><?php echo htmlspecialchars($r['type']) ?></td>
          <td><?php echo (int)$r['id'] ?></td>
          <td><?php echo htmlspecialchars($r['item_name']) ?></td>
          <td><?php echo $u['vori'].' ভরি '.$u['ana'].' আনা '.$u['roti'].' রটি '.$u['point'].' পয়েন্ট ('.$u['grams'].' g)' ?></td>
          <td><?php echo htmlspecialchars($r['note'] ?? '') ?></td>
          <td><?php echo htmlspecialchars($r['at']) ?></td>
        </tr>
        <?php endwhile; ?>
      <?php else: ?>
        <tr><td colspan="6">No records found for the selected date range.</td></tr>
      <?php endif; ?>
      </tbody>
    </table>

    <h3 style="margin-top:12px">Stock Out Records</h3>
    <table class="report-table">
      <thead><tr><th>ID</th><th>Item</th><th>Out Weight</th><th>Reason</th><th>At</th></tr></thead>
      <tbody>
        <?php if ($outRes && $outRes->num_rows): ?>
          <?php while($r = $outRes->fetch_assoc()): $u = gramsToUnits(floatval($r['out_weight_g'])); ?>
          <tr class="type-stockout">
            <td><?php echo $r['id'] ?></td>
            <td><?php echo htmlspecialchars($r['item_name']) ?></td>
            <td><?php echo $u['vori'].' ভরি '.$u['ana'].' আনা '.$u['roti'].' রটি '.$u['point'].' পয়েন্ট ('.$u['grams'].' g)' ?></td>
            <td><?php echo htmlspecialchars($r['reason']) ?></td>
            <td><?php echo htmlspecialchars($r['created_at']) ?></td>
          </tr>
          <?php endwhile; ?>
        <?php else: ?><tr><td colspan="5">No stock out records.</td></tr><?php endif; ?>
      </tbody>
    </table>

    <h3 style="margin-top:12px">Sales</h3>
    <table class="report-table">
      <thead><tr><th>ID</th><th>Item</th><th>Qty</th><th>Weight</th><th>At</th></tr></thead>
      <tbody>
        <?php if ($sales && $sales->num_rows): ?>
          <?php while($s = $sales->fetch_assoc()): $u = gramsToUnits(floatval($s['sold_weight_g'])); ?>
          <tr class="type-sale">
            <td><?php echo $s['id'] ?></td>
            <td><?php echo htmlspecialchars($s['item_name']) ?></td>
            <td><?php echo $s['qty'] ?></td>
            <td><?php echo $u['vori'].' ভরি '.$u['ana'].' আনা '.$u['roti'].' রটি '.$u['point'].' পয়েন্ট ('.$u['grams'].' g)' ?></td>
            <td><?php echo htmlspecialchars($s['sold_at']) ?></td>
          </tr>
          <?php endwhile; ?>
        <?php else: ?><tr><td colspan="5">No sales records.</td></tr><?php endif; ?>
      </tbody>
    </table>
  </div>
</div>

<?php require 'footer.php'; ?>
