<?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';

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';

  $supp_id = (int)$_POST['supplier_id'];
  $stmt = $pdo->prepare("SELECT name FROM suppliers WHERE id=?");
  $stmt->execute([$supp_id]);
  $supp_name = $stmt->fetchColumn() ?: 'مورد غير معروف';

  $movements = [];
  // فواتير المشتريات
  $q1 = $pdo->prepare("
    SELECT pi.*, 'invoice' AS type, s.name AS supplier_name
    FROM purchase_invoices pi
    LEFT JOIN suppliers s ON s.id = pi.supplier_id
    WHERE pi.supplier_id=?
  ");
  $q1->execute([$supp_id]);
  foreach($q1 as $r) $movements[] = $r;

  // دفع للمورد
  $q2 = $pdo->prepare("SELECT *, 'out' AS type FROM supplier_money WHERE supplier_id=? AND type='out'");
  $q2->execute([$supp_id]);
  foreach($q2 as $r) $movements[] = $r;

  // قبض من المورد
  $q3 = $pdo->prepare("SELECT *, 'in' AS type FROM supplier_money WHERE supplier_id=? AND type='in'");
  $q3->execute([$supp_id]);
  foreach($q3 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', '📗 كشف حساب المورد: ' . $supp_name);
  $sheet->mergeCells('A1:F1');
  $sheet->getStyle('A1')->getFont()->setBold(true)->setSize(14);
  $headers = ['التاريخ','البيان','رقم الفاتورة','المبلغ','نوع العملية','الرصيد'];
  $sheet->fromArray($headers, NULL, 'A3');
  $sheet->getStyle('A3:F3')->getFont()->setBold(true);

  $row=4; $run=0;
  foreach($movements as $m) {
    if ($m['type']=='invoice') {
      $desc = "فاتورة رقم {$m['invoice_number']} / إرسالية: {$m['shipping_number']} / سيارة: {$m['car_plate']} / مصروف: {$m['expense_type']}";
      $amount = $m['total'];
      $run -= $amount;
      $type = 'فاتورة مشتريات';
    } elseif ($m['type']=='out') {
      $desc = 'دفع للمورد - '.$m['note'];
      $amount = $m['amount'];
      $run += $amount;
      $type = 'دفع';
    } else {
      $desc = 'قبض من المورد - '.$m['note'];
      $amount = $m['amount'];
      $run -= $amount;
      $type = 'قبض';
    }
    $sheet->fromArray([$m['date'],$desc,$m['id'],$amount,$type,$run], NULL, "A$row");
    $row++;
  }
  foreach(range('A','F') as $c) $sheet->getColumnDimension($c)->setAutoSize(true);
  header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
  header('Content-Disposition: attachment; filename="كشف حساب-'.$supp_name.'.xlsx"');
  $writer = new Xlsx($spreadsheet);
  $writer->save('php://output');
  exit;
}

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

if ($supp_id) {
  // فواتير المشتريات مع تفاصيلها
  $q1 = $pdo->prepare("
    SELECT pi.*, 'invoice' AS type, s.name AS supplier_name
    FROM purchase_invoices pi
    LEFT JOIN suppliers s ON s.id = pi.supplier_id
    WHERE pi.supplier_id = ?
  ");
  $q1->execute([$supp_id]);
  foreach($q1 as $r) {
    $r['items'] = $pdo->query("
      SELECT p.name, i.qty, i.price, i.total 
      FROM purchase_items i
      JOIN products p ON p.id = i.product_id
      WHERE i.invoice_id = {$r['id']}
    ")->fetchAll();
    $movements[] = $r;
  }

  // دفع للمورد
  $q2 = $pdo->prepare("SELECT *, 'out' AS type FROM supplier_money WHERE supplier_id=? AND type='out'");
  $q2->execute([$supp_id]);
  foreach($q2 as $r){ $movements[] = $r; }

  // قبض من المورد
  $q3 = $pdo->prepare("SELECT *, 'in' AS type FROM supplier_money WHERE supplier_id=? AND type='in'");
  $q3->execute([$supp_id]);
  foreach($q3 as $r){ $movements[] = $r; }

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

<h3 class="mb-3">📗 كشف حساب مورد</h3>

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

<?php if($supp_id): ?>
<form method="post">
  <input type="hidden" name="export_excel" value="1">
  <input type="hidden" name="supplier_id" value="<?= $supp_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; $debit=0; $credit=0;
foreach($movements as $m):
  if($m['type']=='invoice'){
    $run -= $m['total']; $credit += $m['total'];
    $details = "رقم: {$m['invoice_number']}<br>إرسالية: {$m['shipping_number']}<br>لوحة: {$m['car_plate']}<br>مصروف: {$m['expense_type']}<br><b>المواد:</b><br>";
    foreach($m['items'] as $it){
      $details .= "• {$it['name']} ({$it['qty']} × {$it['price']}) = <b>{$it['total']}</b><br>";
    }
    $desc = "فاتورة مشتريات";
    $debit_val = 0; $credit_val = $m['total'];
  } elseif($m['type']=='out'){
    $run += $m['amount']; $debit += $m['amount'];
    $desc = "دفع للمورد"; $details = $m['note'];
    $debit_val = $m['amount']; $credit_val = 0;
  } else {
    $run -= $m['amount']; $credit += $m['amount'];
    $desc = "قبض من المورد"; $details = $m['note'];
    $debit_val = 0; $credit_val = $m['amount'];
  }
?>
<tr>
  <td><?= htmlspecialchars($m['date']) ?></td>
  <td><?= htmlspecialchars($desc) ?></td>
  <td><?= strtoupper($m['type']) ?></td>
  <td class="text-start"><?= $details ?></td>
  <td><?= number_format($debit_val,2) ?></td>
  <td><?= number_format($credit_val,2) ?></td>
  <td class="<?= $run<0?'text-danger':'text-success' ?> fw-bold"><?= number_format($run,2) ?></td>
</tr>
<?php endforeach; ?>
</tbody>
<tfoot class="table-light">
<tr>
  <th colspan="4">الإجمالي</th>
  <th><?= number_format($debit,2) ?></th>
  <th><?= number_format($credit,2) ?></th>
  <th><?= number_format($debit-$credit,2) ?></th>
</tr>
</tfoot>
</table>
</div></div></div>
<?php endif; ?>

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