<?php
session_save_path(__DIR__ . '/../tmp');
if (!is_dir(__DIR__ . '/../tmp')) mkdir(__DIR__ . '/../tmp', 0777, true);
session_start();

if (!isset($_SESSION['user_id'])) {
  header("Location: /wood-system/login.php");
  exit;
}

require __DIR__ . '/../includes/db.php';
require __DIR__ . '/../includes/header.php';

error_reporting(E_ALL);
ini_set('display_errors', 1);

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// ====================================================
// ✅ عند الضغط على زر تصدير Excel
// ====================================================
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['export_excel'])) {
  if (ob_get_length()) ob_end_clean(); // تنظيف أي مخرجات قد تفسد الملف
  require __DIR__ . '/../vendor/autoload.php';

  $product_id = (int)$_POST['product_id'];

  // جلب اسم المنتج
  $stmt = $pdo->prepare("SELECT name FROM products WHERE id=?");
  $stmt->execute([$product_id]);
  $product_name = $stmt->fetchColumn() ?: 'منتج غير معروف';

  // --- جلب الحركات ---
  $movements = [];

  // ✅ المشتريات (الوارد)
  $q1 = $pdo->prepare("
    SELECT pi.date,
           CONCAT('فاتورة مشتريات رقم ', pi.id, ' من المورد ', s.name) AS type,
           it.qty AS qty_in,
           0 AS qty_out,
           (it.price * it.qty) AS value_in,
           0 AS value_out
    FROM purchase_items it
    LEFT JOIN purchase_invoices pi ON pi.id = it.invoice_id
    LEFT JOIN suppliers s ON s.id = pi.supplier_id
    WHERE it.product_id = ?
  ");
  $q1->execute([$product_id]);
  foreach($q1 as $r) $movements[] = $r;

  // ✅ المبيعات (الصادر)
  $q2 = $pdo->prepare("
    SELECT si.date,
           CONCAT('فاتورة مبيعات رقم ', si.id, ' إلى العميل ', c.name) AS type,
           0 AS qty_in,
           it.qty AS qty_out,
           0 AS value_in,
           (it.price * it.qty) AS value_out
    FROM sales_items it
    LEFT JOIN sales_invoices si ON si.id = it.invoice_id
    LEFT JOIN customers c ON c.id = si.customer_id
    WHERE it.product_id = ?
  ");
  $q2->execute([$product_id]);
  foreach($q2 as $r) $movements[] = $r;

  usort($movements, fn($a,$b)=>strcmp($a['date'],$b['date']));

  // --- إنشاء ملف Excel ---
  $spreadsheet = new Spreadsheet();
  $sheet = $spreadsheet->getActiveSheet();
  $sheet->setTitle('كشف حساب مادة');

  // عنوان رئيسي
  $sheet->setCellValue('A1', '📦 كشف حساب المادة: ' . $product_name);
  $sheet->mergeCells('A1:G1');
  $sheet->getStyle('A1')->getFont()->setBold(true)->setSize(14);

  // رؤوس الأعمدة
  $headers = ['التاريخ','البيان','وارد','صادر','الرصيد','قيمة الوارد','قيمة الصادر'];
  $sheet->fromArray($headers, NULL, 'A3');
  $sheet->getStyle('A3:G3')->getFont()->setBold(true);

  // تعبئة البيانات
  $row = 4; $run = 0;
  foreach ($movements as $m) {
    $run += $m['qty_in'] - $m['qty_out'];
    $sheet->setCellValue('A'.$row, $m['date']);
    $sheet->setCellValue('B'.$row, $m['type']);
    $sheet->setCellValue('C'.$row, $m['qty_in']);
    $sheet->setCellValue('D'.$row, $m['qty_out']);
    $sheet->setCellValue('E'.$row, $run);
    $sheet->setCellValue('F'.$row, $m['value_in']);
    $sheet->setCellValue('G'.$row, $m['value_out']);
    $row++;
  }

  // الإجماليات
  $sheet->setCellValue('A'.$row, 'الإجمالي');
  $sheet->setCellValue('C'.$row, '=SUM(C4:C'.($row-1).')');
  $sheet->setCellValue('D'.$row, '=SUM(D4:D'.($row-1).')');
  $sheet->setCellValue('E'.$row, '=C'.$row.'-D'.$row);
  $sheet->setCellValue('F'.$row, '=SUM(F4:F'.($row-1).')');
  $sheet->setCellValue('G'.$row, '=SUM(G4:G'.($row-1).')');
  $sheet->getStyle('A'.$row.':G'.$row)->getFont()->setBold(true);

  // ضبط العرض التلقائي للأعمدة
  foreach (range('A','G') as $col)
    $sheet->getColumnDimension($col)->setAutoSize(true);

  // رؤوس التحميل
  header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
  header('Content-Disposition: attachment; filename="كشف حساب - '.$product_name.'.xlsx"');
  header('Cache-Control: max-age=0');
  header('Pragma: public');

  $writer = new Xlsx($spreadsheet);
  $writer->save('php://output');
  exit;
}

// ====================================================
// ✅ عرض الصفحة العادية
// ====================================================
$product_id = (int)($_GET['product_id'] ?? 0);
$products = $pdo->query("SELECT id, name FROM products ORDER BY name")->fetchAll();
$movements = [];

if ($product_id) {
  $q1 = $pdo->prepare("
    SELECT pi.date,
           CONCAT('فاتورة مشتريات رقم ', pi.id, ' من المورد ', s.name) AS type,
           it.qty AS qty_in,
           0 AS qty_out,
           (it.price * it.qty) AS value_in,
           0 AS value_out
    FROM purchase_items it
    LEFT JOIN purchase_invoices pi ON pi.id = it.invoice_id
    LEFT JOIN suppliers s ON s.id = pi.supplier_id
    WHERE it.product_id = ?
  ");
  $q1->execute([$product_id]);
  foreach($q1 as $r) $movements[] = $r;

  $q2 = $pdo->prepare("
    SELECT si.date,
           CONCAT('فاتورة مبيعات رقم ', si.id, ' إلى العميل ', c.name) AS type,
           0 AS qty_in,
           it.qty AS qty_out,
           0 AS value_in,
           (it.price * it.qty) AS value_out
    FROM sales_items it
    LEFT JOIN sales_invoices si ON si.id = it.invoice_id
    LEFT JOIN customers c ON c.id = si.customer_id
    WHERE it.product_id = ?
  ");
  $q2->execute([$product_id]);
  foreach($q2 as $r) $movements[] = $r;

  usort($movements, fn($a,$b)=>strcmp($a['date'],$b['date']));
}
?>

<h3 class="mb-3 text-primary">📦 كشف حساب مادة (منتج)</h3>

<form class="row g-3 mb-3">
  <div class="col-md-6">
    <label class="form-label">اختر المنتج</label>
    <select name="product_id" class="form-select" onchange="this.form.submit()">
      <option value="">— اختر المنتج —</option>
      <?php foreach($products as $p): ?>
      <option value="<?= $p['id'] ?>" <?= $product_id==$p['id']?'selected':'' ?>><?= htmlspecialchars($p['name']) ?></option>
      <?php endforeach; ?>
    </select>
  </div>
</form>

<?php if($product_id): ?>
<form method="post" action="">
  <input type="hidden" name="export_excel" value="1">
  <input type="hidden" name="product_id" value="<?= $product_id ?>">
  <button class="btn btn-success mb-3">📤 تصدير إلى Excel</button>
</form>

<div class="card shadow-sm"><div class="card-body p-0">
<div class="table-responsive">
<table class="table table-striped mb-0 text-center align-middle">
<thead class="table-dark">
  <tr>
    <th>التاريخ</th>
    <th>البيان</th>
    <th>وارد</th>
    <th>صادر</th>
    <th>الرصيد</th>
    <th>قيمة الوارد</th>
    <th>قيمة الصادر</th>
  </tr>
</thead>
<tbody>
<?php 
$run=0; $in=0; $out=0; $vin=0; $vout=0;
foreach($movements as $m):
  $run += ($m['qty_in'] - $m['qty_out']);
  $in += $m['qty_in']; $out += $m['qty_out'];
  $vin += $m['value_in']; $vout += $m['value_out'];
?>
<tr>
  <td><?= htmlspecialchars($m['date']) ?></td>
  <td><?= htmlspecialchars($m['type']) ?></td>
  <td><?= number_format($m['qty_in'],2) ?></td>
  <td><?= number_format($m['qty_out'],2) ?></td>
  <td><strong><?= number_format($run,2) ?></strong></td>
  <td><?= number_format($m['value_in'],2) ?></td>
  <td><?= number_format($m['value_out'],2) ?></td>
</tr>
<?php endforeach; ?>
</tbody>
<tfoot>
<tr class="table-light fw-bold">
  <th colspan="2">الإجمالي</th>
  <th><?= number_format($in,2) ?></th>
  <th><?= number_format($out,2) ?></th>
  <th><?= number_format($in-$out,2) ?></th>
  <th><?= number_format($vin,2) ?></th>
  <th><?= number_format($vout,2) ?></th>
</tr>
</tfoot>
</table>
</div></div></div>
<?php endif; ?>

<?php require __DIR__ . '/../includes/footer.php'; ?>
