<?php
// ============================================================
//  MediCore — Returns Report
//  File: accounting/returns-report.php
// ============================================================
$page  = 'accounting/returns-report';
$title = 'Returns Report';
require_once '../includes/header.php';

$cur  = $settings['currency'];
$from = $_GET['from'] ?? date('Y-m-01');
$to   = $_GET['to']   ?? date('Y-m-d');

// ── Sales Returns ──────────────────────────────────────────────
$salesTotal = (float) db_val(
    "SELECT COALESCE(SUM(total_amount),0) FROM sales_returns
      WHERE date BETWEEN ? AND ? AND status!='rejected'", [$from,$to]);
$salesCount = (int) db_val(
    "SELECT COUNT(*) FROM sales_returns
      WHERE date BETWEEN ? AND ? AND status!='rejected'", [$from,$to]);

$salesTopProducts = db_rows(
    "SELECT sri.product_name,
            SUM(sri.qty)    AS qty,
            SUM(sri.amount) AS amount,
            COUNT(DISTINCT sr.id) AS times
       FROM sales_return_items sri
       JOIN sales_returns sr ON sr.id = sri.return_id
      WHERE sr.date BETWEEN ? AND ? AND sr.status!='rejected'
      GROUP BY sri.product_name
      ORDER BY amount DESC LIMIT 10", [$from,$to]);

$salesByCustomer = db_rows(
    "SELECT customer_name, COUNT(*) AS cnt, SUM(total_amount) AS amount
       FROM sales_returns
      WHERE date BETWEEN ? AND ? AND status!='rejected'
      GROUP BY customer_name ORDER BY amount DESC LIMIT 10", [$from,$to]);

// ── Purchase Returns ───────────────────────────────────────────
$purchTotal = (float) db_val(
    "SELECT COALESCE(SUM(total_amount),0) FROM purchase_returns
      WHERE date BETWEEN ? AND ? AND status!='rejected'", [$from,$to]);
$purchCount = (int) db_val(
    "SELECT COUNT(*) FROM purchase_returns
      WHERE date BETWEEN ? AND ? AND status!='rejected'", [$from,$to]);

$purchTopProducts = db_rows(
    "SELECT pri.product_name,
            SUM(pri.qty)    AS qty,
            SUM(pri.amount) AS amount,
            COUNT(DISTINCT pr.id) AS times
       FROM purchase_return_items pri
       JOIN purchase_returns pr ON pr.id = pri.return_id
      WHERE pr.date BETWEEN ? AND ? AND pr.status!='rejected'
      GROUP BY pri.product_name
      ORDER BY amount DESC LIMIT 10", [$from,$to]);

$purchBySupplier = db_rows(
    "SELECT supplier_name, COUNT(*) AS cnt, SUM(total_amount) AS amount
       FROM purchase_returns
      WHERE date BETWEEN ? AND ? AND status!='rejected'
      GROUP BY supplier_name ORDER BY amount DESC LIMIT 10", [$from,$to]);

// ── Summary ────────────────────────────────────────────────────
$totalSales    = (float) db_val(
    "SELECT COALESCE(SUM(final_price),0) FROM sales WHERE date BETWEEN ? AND ?",[$from,$to]);
$totalPurchase = (float) db_val(
    "SELECT COALESCE(SUM(final_price),0) FROM purchases WHERE date BETWEEN ? AND ?",[$from,$to]);
$salesReturnRate = $totalSales    > 0 ? ($salesTotal / $totalSales) * 100  : 0;
$purchReturnRate = $totalPurchase > 0 ? ($purchTotal / $totalPurchase) * 100 : 0;

// ── Combined product analysis ────────────────────────────────────
$productAnalysis = db_rows(
    "SELECT product_name, 'Sales Return' AS type, SUM(qty) AS qty, SUM(amount) AS amount
       FROM sales_return_items sri
       JOIN sales_returns sr ON sr.id=sri.return_id
      WHERE sr.date BETWEEN ? AND ? AND sr.status!='rejected'
      GROUP BY product_name
     UNION ALL
     SELECT product_name, 'Purchase Return' AS type, SUM(qty) AS qty, SUM(amount) AS amount
       FROM purchase_return_items pri
       JOIN purchase_returns pr ON pr.id=pri.return_id
      WHERE pr.date BETWEEN ? AND ? AND pr.status!='rejected'
      GROUP BY product_name
      ORDER BY amount DESC LIMIT 15", [$from,$to,$from,$to]);
?>

<!-- ── Date filter ────────────────────────────────── -->
<form method="GET" style="display:flex;gap:9px;align-items:center;
      flex-wrap:wrap;margin-bottom:14px;padding:10px 14px;
      background:var(--surface);border:1px solid var(--border);border-radius:var(--r)">
  <span style="font-size:12px;font-weight:600;color:var(--text2)">📅 Date Range:</span>
  <input type="date" name="from" value="<?= $from ?>"
    style="padding:5px 8px;border:1px solid var(--border);border-radius:var(--rsm);font-size:12px;outline:none">
  <span style="color:var(--text3);font-size:11px">to</span>
  <input type="date" name="to" value="<?= $to ?>"
    style="padding:5px 8px;border:1px solid var(--border);border-radius:var(--rsm);font-size:12px;outline:none">
  <button type="submit" class="btn btn-primary btn-sm">Apply</button>
</form>

<!-- ── Tabs ───────────────────────────────────────── -->
<div style="display:flex;gap:0;margin-bottom:14px;border-bottom:2px solid var(--border)">
  <?php
  $tabs = [
    'sales' => ['↩', 'Sales Returns', 'var(--danger)'],
    'purchase' => ['📦', 'Purchase Returns', '#ea580c'],
    'summary' => ['📊', 'Summary', 'var(--brand)'],
    'products' => ['🏆', 'Product Analysis', 'var(--teal)'],
  ];
  foreach ($tabs as $id => [$icon, $label, $color]):
  ?>
  <button id="tab-<?= $id ?>" onclick="switchTab('<?= $id ?>')"
    data-color="<?= $color ?>"
    style="padding:9px 20px;font-size:12.5px;font-weight:600;border:none;cursor:pointer;
           background:transparent;border-bottom:2px solid transparent;
           color:var(--text2);margin-bottom:-2px">
    <?= $icon ?> <?= $label ?>
  </button>
  <?php endforeach; ?>
</div>

<!-- ════ TAB: Sales Returns ════════════════════════ -->
<div id="pane-sales" class="tab-pane">
  <div style="display:grid;grid-template-columns:repeat(3,1fr);gap:10px;margin-bottom:14px">
    <div class="sc" style="border-top:3px solid var(--danger)">
      <div class="slabel">Total Sales Returns</div>
      <div class="sval b"><?= $salesCount ?></div>
    </div>
    <div class="sc" style="border-top:3px solid var(--danger)">
      <div class="slabel">Total Return Amount</div>
      <div class="sval b" style="color:var(--danger)"><?= $cur.number_format($salesTotal,2) ?></div>
    </div>
    <div class="sc" style="border-top:3px solid var(--warn)">
      <div class="slabel">Return Rate</div>
      <div class="sval b" style="color:var(--warn)"><?= number_format($salesReturnRate,2) ?>%</div>
      <div style="font-size:10px;color:var(--text3);margin-top:2px">of total sales</div>
    </div>
  </div>

  <div style="display:grid;grid-template-columns:1fr 1fr;gap:12px">
    <div class="card" style="padding:0">
      <div class="ch" style="padding:10px 14px"><div class="ct">🏆 Top Returned Products</div></div>
      <div class="tw">
        <table>
          <thead><tr><th>Product</th><th style="text-align:center">Qty</th>
                 <th style="text-align:center">Times</th><th style="text-align:right">Amount</th></tr></thead>
          <tbody>
            <?php if ($salesTopProducts): foreach ($salesTopProducts as $p): ?>
            <tr>
              <td style="font-size:12px"><?= htmlspecialchars($p['product_name']) ?></td>
              <td class="mono" style="text-align:center"><?= $p['qty'] ?></td>
              <td style="text-align:center"><span class="badge"><?= $p['times'] ?></span></td>
              <td class="mono" style="text-align:right;font-weight:700;color:var(--danger)">
                <?= $cur.number_format($p['amount'],2) ?></td>
            </tr>
            <?php endforeach; else: ?>
            <tr><td colspan="4" class="empty-state">No sales returns in this period</td></tr>
            <?php endif; ?>
          </tbody>
        </table>
      </div>
    </div>

    <div class="card" style="padding:0">
      <div class="ch" style="padding:10px 14px"><div class="ct">👤 Customer Return History</div></div>
      <div class="tw">
        <table>
          <thead><tr><th>Customer</th><th style="text-align:center">Returns</th>
                 <th style="text-align:right">Amount</th></tr></thead>
          <tbody>
            <?php if ($salesByCustomer): foreach ($salesByCustomer as $c): ?>
            <tr>
              <td style="font-weight:600;font-size:12px"><?= htmlspecialchars($c['customer_name']) ?></td>
              <td style="text-align:center"><span class="badge"><?= $c['cnt'] ?></span></td>
              <td class="mono" style="text-align:right;font-weight:700;color:var(--danger)">
                <?= $cur.number_format($c['amount'],2) ?></td>
            </tr>
            <?php endforeach; else: ?>
            <tr><td colspan="3" class="empty-state">No data</td></tr>
            <?php endif; ?>
          </tbody>
        </table>
      </div>
    </div>
  </div>
  <div style="margin-top:10px"><a href="../sale/return-list.php" class="btn btn-sm">📋 View All Sales Returns →</a></div>
</div>

<!-- ════ TAB: Purchase Returns ══════════════════════ -->
<div id="pane-purchase" class="tab-pane" style="display:none">
  <div style="display:grid;grid-template-columns:repeat(3,1fr);gap:10px;margin-bottom:14px">
    <div class="sc" style="border-top:3px solid #ea580c">
      <div class="slabel">Total Purchase Returns</div>
      <div class="sval b"><?= $purchCount ?></div>
    </div>
    <div class="sc" style="border-top:3px solid #ea580c">
      <div class="slabel">Total Return Value</div>
      <div class="sval b" style="color:#ea580c"><?= $cur.number_format($purchTotal,2) ?></div>
    </div>
    <div class="sc" style="border-top:3px solid var(--warn)">
      <div class="slabel">Return Rate</div>
      <div class="sval b" style="color:var(--warn)"><?= number_format($purchReturnRate,2) ?>%</div>
      <div style="font-size:10px;color:var(--text3);margin-top:2px">of total purchases</div>
    </div>
  </div>

  <div style="display:grid;grid-template-columns:1fr 1fr;gap:12px">
    <div class="card" style="padding:0">
      <div class="ch" style="padding:10px 14px"><div class="ct">🏆 Top Returned Products</div></div>
      <div class="tw">
        <table>
          <thead><tr><th>Product</th><th style="text-align:center">Qty</th>
                 <th style="text-align:center">Times</th><th style="text-align:right">Amount</th></tr></thead>
          <tbody>
            <?php if ($purchTopProducts): foreach ($purchTopProducts as $p): ?>
            <tr>
              <td style="font-size:12px"><?= htmlspecialchars($p['product_name']) ?></td>
              <td class="mono" style="text-align:center"><?= $p['qty'] ?></td>
              <td style="text-align:center"><span class="badge"><?= $p['times'] ?></span></td>
              <td class="mono" style="text-align:right;font-weight:700;color:#ea580c">
                <?= $cur.number_format($p['amount'],2) ?></td>
            </tr>
            <?php endforeach; else: ?>
            <tr><td colspan="4" class="empty-state">No purchase returns in this period</td></tr>
            <?php endif; ?>
          </tbody>
        </table>
      </div>
    </div>

    <div class="card" style="padding:0">
      <div class="ch" style="padding:10px 14px"><div class="ct">🏢 Supplier Return History</div></div>
      <div class="tw">
        <table>
          <thead><tr><th>Supplier</th><th style="text-align:center">Returns</th>
                 <th style="text-align:right">Amount</th></tr></thead>
          <tbody>
            <?php if ($purchBySupplier): foreach ($purchBySupplier as $s): ?>
            <tr>
              <td style="font-weight:600;font-size:12px"><?= htmlspecialchars($s['supplier_name']) ?></td>
              <td style="text-align:center"><span class="badge"><?= $s['cnt'] ?></span></td>
              <td class="mono" style="text-align:right;font-weight:700;color:#ea580c">
                <?= $cur.number_format($s['amount'],2) ?></td>
            </tr>
            <?php endforeach; else: ?>
            <tr><td colspan="3" class="empty-state">No data</td></tr>
            <?php endif; ?>
          </tbody>
        </table>
      </div>
    </div>
  </div>
  <div style="margin-top:10px"><a href="../purchase/return-list.php" class="btn btn-sm">📋 View All Purchase Returns →</a></div>
</div>

<!-- ════ TAB: Summary ═══════════════════════════════ -->
<div id="pane-summary" class="tab-pane" style="display:none">
  <div style="display:grid;grid-template-columns:repeat(4,1fr);gap:10px;margin-bottom:14px">
    <div class="sc" style="border-top:3px solid var(--brand)">
      <div class="slabel">Total Sales</div>
      <div class="sval b"><?= $cur.number_format($totalSales,2) ?></div>
    </div>
    <div class="sc" style="border-top:3px solid var(--danger)">
      <div class="slabel">Sales Returns</div>
      <div class="sval b" style="color:var(--danger)"><?= $cur.number_format($salesTotal,2) ?></div>
      <div style="font-size:10px;color:var(--warn);margin-top:2px"><?= number_format($salesReturnRate,2) ?>% return rate</div>
    </div>
    <div class="sc" style="border-top:3px solid var(--brand)">
      <div class="slabel">Total Purchases</div>
      <div class="sval b"><?= $cur.number_format($totalPurchase,2) ?></div>
    </div>
    <div class="sc" style="border-top:3px solid #ea580c">
      <div class="slabel">Purchase Returns</div>
      <div class="sval b" style="color:#ea580c"><?= $cur.number_format($purchTotal,2) ?></div>
      <div style="font-size:10px;color:var(--warn);margin-top:2px"><?= number_format($purchReturnRate,2) ?>% return rate</div>
    </div>
  </div>

  <div class="card">
    <div class="ch"><div class="ct">📈 Net Impact</div></div>
    <div style="display:grid;grid-template-columns:repeat(2,1fr);gap:14px">
      <div style="padding:12px;background:#fde8e8;border-radius:var(--rsm)">
        <div style="font-size:11px;color:var(--danger);margin-bottom:4px">Revenue Lost to Returns</div>
        <div class="mono" style="font-size:18px;font-weight:800;color:var(--danger)">
          <?= $cur.number_format($salesTotal,2) ?>
        </div>
      </div>
      <div style="padding:12px;background:#ffedd5;border-radius:var(--rsm)">
        <div style="font-size:11px;color:#ea580c;margin-bottom:4px">Stock Returned to Suppliers</div>
        <div class="mono" style="font-size:18px;font-weight:800;color:#ea580c">
          <?= $cur.number_format($purchTotal,2) ?>
        </div>
      </div>
    </div>
  </div>
</div>

<!-- ════ TAB: Product Analysis ══════════════════════ -->
<div id="pane-products" class="tab-pane" style="display:none">
  <div class="card" style="padding:0">
    <div class="ch" style="padding:10px 14px"><div class="ct">🏆 Most Returned Products (Combined)</div></div>
    <div class="tw">
      <table>
        <thead><tr>
          <th>#</th><th>Product</th><th style="text-align:center">Type</th>
          <th style="text-align:center">Qty</th><th style="text-align:right">Amount</th>
        </tr></thead>
        <tbody>
          <?php if ($productAnalysis): foreach ($productAnalysis as $i=>$p):
            $isSale = $p['type']==='Sales Return';
            $bg = $isSale?'#fde8e8':'#ffedd5'; $cl=$isSale?'var(--danger)':'#ea580c';
          ?>
          <tr>
            <td style="color:var(--text3)"><?= $i+1 ?></td>
            <td style="font-weight:600;font-size:12px"><?= htmlspecialchars($p['product_name']) ?></td>
            <td style="text-align:center">
              <span class="badge" style="background:<?= $bg ?>;color:<?= $cl ?>;font-weight:700">
                <?= $isSale?'↩ Sale':'📦 Purchase' ?>
              </span>
            </td>
            <td class="mono" style="text-align:center"><?= $p['qty'] ?></td>
            <td class="mono" style="text-align:right;font-weight:700;color:<?= $cl ?>">
              <?= $cur.number_format($p['amount'],2) ?>
            </td>
          </tr>
          <?php endforeach; else: ?>
          <tr><td colspan="5" class="empty-state">No return data in this period</td></tr>
          <?php endif; ?>
        </tbody>
      </table>
    </div>
  </div>
</div>

<script>
function switchTab(id) {
  document.querySelectorAll('.tab-pane').forEach(p => p.style.display='none');
  document.querySelectorAll('[id^="tab-"]').forEach(b => {
    b.style.borderBottomColor='transparent'; b.style.color='var(--text2)';
  });
  document.getElementById('pane-'+id).style.display='block';
  const btn = document.getElementById('tab-'+id);
  const color = btn.dataset.color;
  btn.style.borderBottomColor = color; btn.style.color = color;
}
document.addEventListener('DOMContentLoaded',()=>switchTab('sales'));
</script>

<?php require_once '../includes/footer.php'; ?>
