<?php
// XLSX Export (PhpSpreadsheet)
// - يدعم قالب Excel بالهوية البصرية (excel_template.xlsx)
// - ورقة "ملخص" (KPIs + Charts)
// - ورقة "البيانات" (RTL + AutoFit + تواريخ موحدة)
// - إحداثيات + رابط Google Maps (بدون فقدان الإحداثيات)

require __DIR__ . '/_common.php';

$entity = trim($_GET['entity'] ?? '');
$allowed = ['oases','aflaj','farms','assets','maintenance','master'];
if (!in_array($entity, $allowed, true)) {
  http_response_code(400);
  echo 'Bad Request';
  exit;
}

// scope=filtered (default) or scope=all
$scope = trim($_GET['scope'] ?? 'filtered');
$all = ($scope === 'all' || $scope === '1' || $scope === 'true');

// Permission per entity
$permByEntity = [
  'oases' => 'export_oases_xlsx',
  'aflaj' => 'export_aflaj_xlsx',
  'farms' => 'export_farms_xlsx',
  'assets' => 'export_assets_xlsx',
  'maintenance' => 'export_maintenance_xlsx',
];
require_perm($permByEntity[$entity] ?? 'view_reports');

if (!class_exists('PhpOffice\\PhpSpreadsheet\\Spreadsheet')) {
  http_response_code(500);
  echo "<div style='font-family:Tahoma,Arial,sans-serif;direction:rtl'>";
  echo "<h2>مكتبة Excel غير مثبتة</h2>";
  echo "<p>هذه الميزة تتطلب تثبيت <b>PhpSpreadsheet</b> عبر Composer.</p>";
  echo "<pre style='background:#f4f4f4;padding:10px;border-radius:8px;direction:ltr'>composer install\n# أو\ncomposer require phpoffice/phpspreadsheet</pre>";
  echo "<p class='small'>بعد التثبيت تأكد من وجود المجلد <code>vendor/</code> في جذر المشروع.</p>";
  echo "</div>";
  exit;
}

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
use PhpOffice\PhpSpreadsheet\Chart\Chart;
use PhpOffice\PhpSpreadsheet\Chart\Legend;
use PhpOffice\PhpSpreadsheet\Chart\PlotArea;
use PhpOffice\PhpSpreadsheet\Chart\DataSeries;
use PhpOffice\PhpSpreadsheet\Chart\DataSeriesValues;
use PhpOffice\PhpSpreadsheet\Chart\Title;

// --------------------- MASTER REPORT (one workbook with multiple sheets) ---------------------
if ($entity === 'master') {
  // Gate: at least view_reports
  if (function_exists('require_perm')) {
    require_perm('view_reports');
  }

  $root = dirname(__DIR__, 2); // project root
  $publicDir = $root . '/public';
  $templatePath = $publicDir . '/assets/branding/excel_template.xlsx';
  $logoPath = $publicDir . '/assets/branding/municipality-logo.png';

  // Build spreadsheet from template if exists
  try {
    if (file_exists($templatePath)) {
      $spreadsheet = IOFactory::load($templatePath);
    } else {
      $spreadsheet = new Spreadsheet();
    }
  } catch (Throwable $e) {
    $spreadsheet = new Spreadsheet();
  }

  // Default font (Arabic-friendly, per your template)
  try {
    $spreadsheet->getDefaultStyle()->getFont()->setName('Calibri')->setSize(11);
  } catch (Throwable $e) {}

  // Utility: safe permission check (works even if auth_can not loaded for some reason)
  $can = function(string $perm): bool {
    if (function_exists('auth_can')) return auth_can($perm);
    if (function_exists('can')) return can($perm);
    return true;
  };

  // scope:
  // - scope=all: export everything (up to internal limits)
  // - scope=filters: allow passing limited filters (month/from/to/oasis_id)
  $month = trim($_GET['month'] ?? ''); // YYYY-MM
  $from  = trim($_GET['from'] ?? '');  // YYYY-MM-DD
  $to    = trim($_GET['to'] ?? '');    // YYYY-MM-DD
  $oasis_id = intval($_GET['oasis_id'] ?? 0);

  // Helpers: fetch additional entities safely (if tables exist)
  $safeAll = function(string $sql, array $params = []) {
    try { return db_all($sql, $params); } catch (Throwable $e) { return []; }
  };

  // Fetch datasets (respect scope in a minimal, consistent way)
  // NOTE: xlsx_fetch_rows() already applies filters based on querystring for some entities.
  // For master, we pass only shared filters via $month/from/to/oasis_id where possible.
  $datasets = [];
  $skipped = [];

  // OASES
  if ($can('manage_oases')) {
    // If oasis_id filter is set, export only that oasis
    if ($oasis_id > 0 && !$all) {
      $datasets['oases'] = $safeAll("SELECT * FROM oases WHERE id=? LIMIT 1", [$oasis_id]);
    } else {
      $datasets['oases'] = xlsx_fetch_rows('oases', $all);
    }
  } else { $skipped[] = 'الواحات'; }

  // FARMS
  if ($can('manage_farms')) {
    if ($oasis_id > 0 && !$all) {
      $datasets['farms'] = $safeAll("SELECT f.*, o.name AS oasis_name, o.code AS oasis_code
                                     FROM farms f JOIN oases o ON o.id=f.oasis_id
                                     WHERE f.oasis_id=? ORDER BY f.id DESC LIMIT 50000", [$oasis_id]);
    } else {
      $datasets['farms'] = xlsx_fetch_rows('farms', $all);
    }
  } else { $skipped[] = 'المزارع'; }

  // AFLAJ
  if ($can('manage_aflaj')) {
    if ($oasis_id > 0 && !$all) {
      $datasets['aflaj'] = $safeAll("SELECT a.*, o.name AS oasis_name, o.code AS oasis_code
                                     FROM aflaj a JOIN oases o ON o.id=a.oasis_id
                                     WHERE a.oasis_id=? ORDER BY a.id DESC LIMIT 50000", [$oasis_id]);
    } else {
      $datasets['aflaj'] = xlsx_fetch_rows('aflaj', $all);
    }
  } else { $skipped[] = 'الأفلاج'; }

  // ASSETS
  if ($can('manage_assets')) {
    if ($oasis_id > 0 && !$all) {
      // assets owned by oasis directly OR through farm/falaj belonging to oasis
      $datasets['assets'] = $safeAll("
        SELECT s.*,
          CASE
            WHEN s.owner_type='oasis' THEN o.name
            WHEN s.owner_type='farm' THEN f.owner_name
            WHEN s.owner_type='falaj' THEN a.name
            ELSE NULL
          END AS owner_name,
          CASE
            WHEN s.owner_type='oasis' THEN o.code
            WHEN s.owner_type='farm' THEN f.code
            WHEN s.owner_type='falaj' THEN a.code
            ELSE NULL
          END AS owner_code
        FROM assets s
        LEFT JOIN oases o ON (s.owner_type='oasis' AND o.id=s.owner_id)
        LEFT JOIN farms f ON (s.owner_type='farm' AND f.id=s.owner_id)
        LEFT JOIN aflaj a ON (s.owner_type='falaj' AND a.id=s.owner_id)
        WHERE (
          (s.owner_type='oasis' AND s.owner_id=?)
          OR (s.owner_type='farm' AND f.oasis_id=?)
          OR (s.owner_type='falaj' AND a.oasis_id=?)
        )
        ORDER BY s.id DESC LIMIT 50000
      ", [$oasis_id,$oasis_id,$oasis_id]);
    } else {
      $datasets['assets'] = xlsx_fetch_rows('assets', $all);
    }
  } else { $skipped[] = 'الأصول'; }

  // MAINTENANCE
  if ($can('manage_maintenance')) {
    $rows = xlsx_fetch_rows('maintenance', $all);
    // apply lightweight date filter if provided and not exporting all
    if (!$all && ($month !== '' || ($from !== '' && $to !== ''))) {
      $filtered = [];
      foreach ($rows as $r) {
        $dt = (string)($r['reported_at'] ?? '');
        $d = substr($dt, 0, 10);
        if ($month !== '') {
          if (substr($d, 0, 7) === $month) $filtered[] = $r;
        } elseif ($from !== '' && $to !== '') {
          if ($d >= $from && $d <= $to) $filtered[] = $r;
        }
      }
      $rows = $filtered;
    }
    $datasets['maintenance'] = $rows;
  } else { $skipped[] = 'الصيانة'; }

  // CONTRACTORS + CONTRACTS (+ SERVICES)
  if ($can('manage_contractors')) {
    $datasets['contractors'] = $safeAll("SELECT * FROM contractors ORDER BY id DESC LIMIT 50000", []);
  } else { $skipped[] = 'المقاولون'; }

  if ($can('manage_contracts')) {
    $datasets['contracts'] = $safeAll("SELECT c.*, k.name AS contractor_name, k.code AS contractor_code
                                       FROM contracts c JOIN contractors k ON k.id=c.contractor_id
                                       ORDER BY c.id DESC LIMIT 50000", []);
    $datasets['contract_services'] = $safeAll("SELECT s.*, c.contract_no, c.title AS contract_title
                                               FROM contract_services s JOIN contracts c ON c.id=s.contract_id
                                               ORDER BY s.id DESC LIMIT 50000", []);
  } else { $skipped[] = 'العقود/الخدمات'; }

  // INSPECTORS + TASKS (optional if exists)
  if ($can('manage_inspectors')) {
    $datasets['inspectors'] = $safeAll("SELECT * FROM inspectors ORDER BY id DESC LIMIT 50000", []);
    $datasets['inspector_tasks'] = $safeAll("SELECT t.*, i.name AS inspector_name, i.code AS inspector_code
                                            FROM inspector_tasks t JOIN inspectors i ON i.id=t.inspector_id
                                            ORDER BY t.id DESC LIMIT 50000", []);
  } else {
    // if permission doesn't exist in old versions, try still; if table missing, returns empty
    $datasets['inspectors'] = $safeAll("SELECT * FROM inspectors ORDER BY id DESC LIMIT 50000", []);
    $datasets['inspector_tasks'] = $safeAll("SELECT t.*, i.name AS inspector_name, i.code AS inspector_code
                                            FROM inspector_tasks t JOIN inspectors i ON i.id=t.inspector_id
                                            ORDER BY t.id DESC LIMIT 50000", []);
    if (empty($datasets['inspectors']) && empty($datasets['inspector_tasks'])) $skipped[] = 'المراقبون/المهام';
  }

  // Prepare sheets: keep theme but rebuild order
  // Remove all sheets, then create fresh in correct order
  while ($spreadsheet->getSheetCount() > 1) {
    $spreadsheet->removeSheetByIndex(1);
  }

  $readmeSheet = $spreadsheet->getActiveSheet();
  $readmeSheet->setTitle('README');
  $readmeSheet->setRightToLeft(true);

  // README content
  $generatedAt = date('Y-m-d H:i');
  $readmeSheet->setCellValue('A1', 'تقرير Excel شامل — نظام إدارة الواحات والأفلاج');
  $readmeSheet->setCellValue('A2', 'تاريخ التوليد: ' . $generatedAt);
  $readmeSheet->setCellValue('A3', 'نطاق البيانات: ' . ($all ? 'كل البيانات' : 'حسب الفلاتر'));
  $flt = [];
  if ($month !== '') $flt[] = 'الشهر: ' . $month;
  if ($from !== '' && $to !== '') $flt[] = 'الفترة: ' . $from . ' إلى ' . $to;
  if ($oasis_id > 0) $flt[] = 'oasis_id=' . $oasis_id;
  $readmeSheet->setCellValue('A4', 'الفلاتر: ' . (count($flt) ? implode(' | ', $flt) : 'لا يوجد'));
  $readmeSheet->setCellValue('A6', 'الأوراق داخل الملف:');
  $readmeSheet->setCellValue('A7', '1) ملخص تنفيذي (KPIs + رسوم)');
  $readmeSheet->setCellValue('A8', '2) العلاقات (Cross-Links)');
  $readmeSheet->setCellValue('A9', '3) أوراق البيانات التفصيلية لكل كيان');
  if (count($skipped)) {
    $readmeSheet->setCellValue('A11', 'تنبيه: تم إخفاء بعض الأوراق بسبب الصلاحيات/عدم توفر الجداول:');
    $readmeSheet->setCellValue('A12', implode('، ', $skipped));
  }
  $readmeSheet->getStyle('A1')->getFont()->setBold(true)->setSize(14);
  $readmeSheet->getColumnDimension('A')->setWidth(80);
  xlsx_add_logo_if_exists($readmeSheet, $logoPath);

  // Summary sheet (KPIs)
  $summarySheet = $spreadsheet->createSheet();
  $summarySheet->setTitle('ملخص');
  $summarySheet->setRightToLeft(true);
  xlsx_add_logo_if_exists($summarySheet, $logoPath);
  $summarySheet->setCellValue('A1', 'الملخص التنفيذي (KPIs)');
  $summarySheet->getStyle('A1')->getFont()->setBold(true)->setSize(14);

  // Compute KPIs for main entities if present
  $kpiRow = 3;
  $summarySheet->setCellValue('A'.$kpiRow, 'الكيان');
  $summarySheet->setCellValue('B'.$kpiRow, 'الإجمالي');
  $summarySheet->setCellValue('C'.$kpiRow, 'غير مكتمل');
  $summarySheet->setCellValue('D'.$kpiRow, 'نقص إحداثيات');
  $summarySheet->setCellValue('E'.$kpiRow, 'مفتوح/فعّال');
  $summarySheet->getStyle('A'.$kpiRow.':E'.$kpiRow)->getFont()->setBold(true);
  $kpiRow++;

  $titleFor = function(string $e): string {
    $m = [
      'oases'=>'الواحات','farms'=>'المزارع','assets'=>'الأصول','aflaj'=>'الأفلاج',
      'maintenance'=>'الصيانة','contractors'=>'المقاولون','contracts'=>'العقود','inspectors'=>'المراقبون','inspector_tasks'=>'مهام المراقبين'
    ];
    return $m[$e] ?? $e;
  };

  $kpiEntities = ['oases','farms','assets','aflaj','maintenance','contractors','contracts','inspectors','inspector_tasks'];
  $statusForChart = null;

  foreach ($kpiEntities as $e) {
    if (!isset($datasets[$e]) || !is_array($datasets[$e]) || count($datasets[$e])===0) continue;

    // For entities without lat/lng or status, compute minimal
    if (in_array($e, ['contractors','contracts','inspectors','inspector_tasks','contract_services'], true)) {
      $total = count($datasets[$e]);
      $summarySheet->setCellValue('A'.$kpiRow, $titleFor($e));
      $summarySheet->setCellValue('B'.$kpiRow, $total);
      $summarySheet->setCellValue('C'.$kpiRow, '');
      $summarySheet->setCellValue('D'.$kpiRow, '');
      $summarySheet->setCellValue('E'.$kpiRow, '');
      $kpiRow++;
      continue;
    }

    $k = xlsx_compute_kpis($e, $datasets[$e]);
    $summarySheet->setCellValue('A'.$kpiRow, $titleFor($e));
    $summarySheet->setCellValue('B'.$kpiRow, $k['total']);
    $summarySheet->setCellValue('C'.$kpiRow, $k['incomplete']);
    $summarySheet->setCellValue('D'.$kpiRow, $k['missing_geo']);
    $summarySheet->setCellValue('E'.$kpiRow, $k['active_open']);
    $kpiRow++;

    if ($e === 'maintenance') $statusForChart = $k['status_counts'];
  }

  // Add maintenance status pie chart if available
  if (is_array($statusForChart) && count($statusForChart)) {
    // Return: [startRow, endRow, labelsRange, countsRange]
    [$tblStartRow, $tblEndRow, $labelsRange, $countsRange] = xlsx_build_status_table(
      $summarySheet,
      'maintenance',
      $statusForChart,
      max(13, $kpiRow + 2)
    );

    // Place pie chart on the right side of the Summary sheet
    // Chart position is approximate; adjust if you change header height.
    $topLeft     = 'D' . (int)$tblStartRow;
    $bottomRight = 'H' . ((int)$tblStartRow + 12);
    xlsx_add_pie_chart($summarySheet, 'حالات الصيانة', $labelsRange, $countsRange, $topLeft, $bottomRight);
  }
  $summarySheet->getColumnDimension('A')->setWidth(26);
  $summarySheet->getColumnDimension('B')->setWidth(14);
  $summarySheet->getColumnDimension('C')->setWidth(14);
  $summarySheet->getColumnDimension('D')->setWidth(16);
  $summarySheet->getColumnDimension('E')->setWidth(14);

  // Relationships sheet
  $relSheet = $spreadsheet->createSheet();
  $relSheet->setTitle('العلاقات');
  $relSheet->setRightToLeft(true);
  xlsx_add_logo_if_exists($relSheet, $logoPath);
  $relSheet->setCellValue('A1', 'العلاقات (Cross-Links)');
  $relSheet->getStyle('A1')->getFont()->setBold(true)->setSize(14);

  // Build relationships by oasis (if oases exist)
  $relRows = [];
  try {
    $relRows = $safeAll("
      SELECT o.id, o.code, o.name,
        COALESCE(f.farms_count,0) AS farms_count,
        COALESCE(a.aflaj_count,0) AS aflaj_count,
        COALESCE(s.assets_count,0) AS assets_count,
        COALESCE(m.open_maint,0) AS open_maintenance,
        COALESCE(t.open_tasks,0) AS open_tasks
      FROM oases o
      LEFT JOIN (SELECT oasis_id, COUNT(*) farms_count FROM farms GROUP BY oasis_id) f ON f.oasis_id=o.id
      LEFT JOIN (SELECT oasis_id, COUNT(*) aflaj_count FROM aflaj GROUP BY oasis_id) a ON a.oasis_id=o.id
      LEFT JOIN (
        SELECT oasis_id, COUNT(*) assets_count FROM (
          SELECT CASE
            WHEN x.owner_type='oasis' THEN x.owner_id
            WHEN x.owner_type='farm' THEN ff.oasis_id
            WHEN x.owner_type='falaj' THEN aa.oasis_id
            ELSE NULL
          END AS oasis_id
          FROM assets x
          LEFT JOIN farms ff ON (x.owner_type='farm' AND ff.id=x.owner_id)
          LEFT JOIN aflaj aa ON (x.owner_type='falaj' AND aa.id=x.owner_id)
        ) z WHERE oasis_id IS NOT NULL GROUP BY oasis_id
      ) s ON s.oasis_id=o.id
      LEFT JOIN (
        SELECT oasis_id, COUNT(*) open_maint FROM (
          SELECT mr.id,
            CASE
              WHEN x.owner_type='oasis' THEN x.owner_id
              WHEN x.owner_type='farm' THEN ff.oasis_id
              WHEN x.owner_type='falaj' THEN aa.oasis_id
              ELSE NULL
            END AS oasis_id
          FROM maintenance_requests mr
          JOIN assets x ON x.id=mr.asset_id
          LEFT JOIN farms ff ON (x.owner_type='farm' AND ff.id=x.owner_id)
          LEFT JOIN aflaj aa ON (x.owner_type='falaj' AND aa.id=x.owner_id)
          WHERE mr.status IN ('open','assigned','in_progress')
        ) y WHERE oasis_id IS NOT NULL GROUP BY oasis_id
      ) m ON m.oasis_id=o.id
      LEFT JOIN (
        SELECT oasis_id, COUNT(*) open_tasks FROM (
          SELECT CASE
            WHEN t.entity_type='oasis' THEN t.entity_id
            WHEN t.entity_type='farm' THEN f.oasis_id
            ELSE NULL
          END AS oasis_id
          FROM inspector_tasks t
          LEFT JOIN farms f ON (t.entity_type='farm' AND f.id=t.entity_id)
          WHERE t.status IN ('open','in_progress')
        ) q WHERE oasis_id IS NOT NULL GROUP BY oasis_id
      ) t ON t.oasis_id=o.id
      ORDER BY o.id DESC
    ", []);
  } catch (Throwable $e) {
    $relRows = [];
  }

  $relHeaders = ['id'=>'ID','code'=>'الكود','name'=>'اسم الواحة','farms_count'=>'عدد المزارع','assets_count'=>'عدد الأصول','aflaj_count'=>'عدد الأفلاج','open_maintenance'=>'صيانات مفتوحة','open_tasks'=>'مهام مفتوحة'];
  $relStartHeaderRow = 4;
  $colIndex = 1;
  foreach ($relHeaders as $k=>$label) {
	    $relSheet->setCellValue(Coordinate::stringFromColumnIndex((int)$colIndex) . (int)$relStartHeaderRow, $label);
    $colIndex++;
  }
  $lastCol = Coordinate::stringFromColumnIndex(count($relHeaders));
  $relSheet->getStyle('A'.$relStartHeaderRow.':'.$lastCol.$relStartHeaderRow)->getFont()->setBold(true);
  $relSheet->setAutoFilter('A'.$relStartHeaderRow.':'.$lastCol.$relStartHeaderRow);

  $rrow = $relStartHeaderRow + 1;
  foreach ($relRows as $r) {
    $c=1;
    foreach ($relHeaders as $k=>$label) {
	      $relSheet->setCellValue(Coordinate::stringFromColumnIndex((int)$c) . (int)$rrow, $r[$k] ?? '');
      $c++;
    }
    $rrow++;
  }
  // Auto widths
  $relSheet->getColumnDimension('A')->setWidth(8);
  $relSheet->getColumnDimension('B')->setWidth(14);
  $relSheet->getColumnDimension('C')->setWidth(28);
  foreach (['D','E','F','G','H'] as $L) $relSheet->getColumnDimension($L)->setWidth(16);

  // Data sheet writer (reusable)
  $writeDataSheet = function($sheet, string $entityKey, array $rows, array $cols) use ($logoPath) {
    $sheet->setRightToLeft(true);
    xlsx_add_logo_if_exists($sheet, $logoPath);
    $sheet->setCellValue('A1', 'بيانات: ' . $entityKey);
    $sheet->getStyle('A1')->getFont()->setBold(true)->setSize(13);

    $headers = [];
    foreach ($cols as $c) $headers[] = (string)$c['label'];
    $startHeaderRow = 4;
    $startDataRow = 5;

    // header
    $col = 1;
    foreach ($headers as $h) {
	      $sheet->setCellValue(Coordinate::stringFromColumnIndex((int)$col) . (int)$startHeaderRow, $h);
      $col++;
    }
    $endColLetter = Coordinate::stringFromColumnIndex(count($headers));
    $sheet->getStyle('A'.$startHeaderRow.':'.$endColLetter.$startHeaderRow)->getFont()->setBold(true);
    $sheet->setAutoFilter('A'.$startHeaderRow.':'.$endColLetter.$startHeaderRow);

    $outRows = [];
    foreach ($rows as $r) {
      $row = [];
      foreach ($cols as $c) {
        $k = (string)$c['key'];
        if ($k === 'maps_link') {
          $lat = isset($r['lat']) ? trim((string)$r['lat']) : '';
          $lng = isset($r['lng']) ? trim((string)$r['lng']) : '';
          if ($lat !== '' && $lng !== '' && $lat !== '0' && $lng !== '0') $row[] = 'فتح الخريطة';
          else $row[] = '';
          continue;
        }
        if ($k === 'details_link') {
          $id = intval($r['id'] ?? 0);
          $row[] = $id>0 ? 'فتح التفاصيل' : '';
          continue;
        }

        $val = $r[$k] ?? '';
        if (in_array($k, ['status','priority','owner_type','type'], true)) {
          $val = xlsx_label_status($entityKey, $k, $val);
        }
        if (in_array($k, ['lat','lng'], true)) {
          $v = is_null($val) ? '' : trim((string)$val);
          $row[] = ($v==='') ? '' : number_format((float)$v, 6, '.', '');
          continue;
        }
        if (xlsx_is_date_key($k)) {
          $row[] = xlsx_format_date_value($val);
          continue;
        }
        $row[] = is_null($val) ? '' : (string)$val;
      }
      $outRows[] = $row;
    }

    // write
    $rrow = $startDataRow;
    foreach ($outRows as $row) {
      $cc=1;
      foreach ($row as $v) {
	        $sheet->setCellValue(Coordinate::stringFromColumnIndex((int)$cc) . (int)$rrow, $v);
        $cc++;
      }
      $rrow++;
    }

    // hyperlinks for maps/details
    $rowCount = count($outRows);
    if ($rowCount>0) {
      $colIndex = 1;
      foreach ($cols as $c) {
        $k = (string)$c['key'];
        $letter = Coordinate::stringFromColumnIndex($colIndex);
        if ($k === 'maps_link') {
          for ($i=0; $i<$rowCount; $i++) {
            $rIndex = $startDataRow + $i;
            $lat = isset($rows[$i]['lat']) ? trim((string)$rows[$i]['lat']) : '';
            $lng = isset($rows[$i]['lng']) ? trim((string)$rows[$i]['lng']) : '';
            if ($lat !== '' && $lng !== '' && $lat !== '0' && $lng !== '0') {
              $url = 'https://www.google.com/maps?q=' . $lat . ',' . $lng;
              $sheet->getCell($letter.$rIndex)->getHyperlink()->setUrl($url);
              $sheet->getStyle($letter.$rIndex)->getFont()->getColor()->setARGB('FF1155CC');
              $sheet->getStyle($letter.$rIndex)->getFont()->setUnderline(true);
            }
          }
        }
        if ($k === 'details_link') {
          for ($i=0; $i<$rowCount; $i++) {
            $rIndex = $startDataRow + $i;
            $id = intval($rows[$i]['id'] ?? 0);
            if ($id>0) {
              $url = 'index.php?page=' . urlencode($entityKey) . '&action=edit&id=' . $id;
              $sheet->getCell($letter.$rIndex)->getHyperlink()->setUrl($url);
              $sheet->getStyle($letter.$rIndex)->getFont()->getColor()->setARGB('FF1155CC');
              $sheet->getStyle($letter.$rIndex)->getFont()->setUnderline(true);
            }
          }
        }
        $colIndex++;
      }
    }

    // auto width
    xlsx_auto_width($sheet, $headers, $outRows, $startDataRow);

    // freeze header
    $sheet->freezePane('A'.$startDataRow);
  };

  // Create data sheets in a clear order
  $order = [
    'oases' => ['sheet' => 'الواحات', 'cols' => xlsx_columns('oases')],
    'farms' => ['sheet' => 'المزارع', 'cols' => xlsx_columns('farms')],
    'assets' => ['sheet' => 'الأصول', 'cols' => xlsx_columns('assets')],
    'aflaj' => ['sheet' => 'الأفلاج', 'cols' => xlsx_columns('aflaj')],
    'maintenance' => ['sheet' => 'الصيانة', 'cols' => xlsx_columns('maintenance')],
  ];

  // add extra sheets (contractors/contracts/inspectors/tasks) with custom columns (db column names)
  $extraCols = [
    'contractors' => [
      ['key'=>'id','label'=>'ID'],['key'=>'code','label'=>'الكود'],['key'=>'name','label'=>'اسم المقاول'],
      ['key'=>'phone','label'=>'الهاتف'],['key'=>'email','label'=>'البريد'],['key'=>'license_no','label'=>'رقم الرخصة'],
      ['key'=>'services','label'=>'الخدمات'],['key'=>'is_active','label'=>'فعّال'],['key'=>'created_at','label'=>'تاريخ الإضافة'],['key'=>'details_link','label'=>'التفاصيل']
    ],
    'contracts' => [
      ['key'=>'id','label'=>'ID'],['key'=>'contract_no','label'=>'رقم العقد'],['key'=>'title','label'=>'عنوان العقد'],
      ['key'=>'contractor_code','label'=>'كود المقاول'],['key'=>'contractor_name','label'=>'اسم المقاول'],
      ['key'=>'start_date','label'=>'بداية'],['key'=>'end_date','label'=>'نهاية'],['key'=>'value_amount','label'=>'القيمة'],
      ['key'=>'status','label'=>'الحالة'],['key'=>'created_at','label'=>'تاريخ الإضافة'],['key'=>'details_link','label'=>'التفاصيل']
    ],
    'contract_services' => [
      ['key'=>'id','label'=>'ID'],['key'=>'contract_no','label'=>'رقم العقد'],['key'=>'contract_title','label'=>'عنوان العقد'],
      ['key'=>'service_name','label'=>'الخدمة'],['key'=>'unit','label'=>'الوحدة'],['key'=>'unit_price','label'=>'سعر الوحدة'],
      ['key'=>'is_active','label'=>'فعّال'],['key'=>'created_at','label'=>'تاريخ الإضافة']
    ],
    'inspectors' => [
      ['key'=>'id','label'=>'ID'],['key'=>'code','label'=>'الكود'],['key'=>'name','label'=>'اسم المراقب'],
      ['key'=>'phone','label'=>'الهاتف'],['key'=>'email','label'=>'البريد'],['key'=>'emirates_id','label'=>'هوية إماراتية'],
      ['key'=>'is_active','label'=>'فعّال'],['key'=>'created_at','label'=>'تاريخ الإضافة'],['key'=>'details_link','label'=>'التفاصيل']
    ],
    'inspector_tasks' => [
      ['key'=>'id','label'=>'ID'],['key'=>'inspector_code','label'=>'كود المراقب'],['key'=>'inspector_name','label'=>'اسم المراقب'],
      ['key'=>'entity_type','label'=>'الجهة'],['key'=>'entity_id','label'=>'ID الجهة'],
      ['key'=>'task_title','label'=>'عنوان المهمة'],['key'=>'status','label'=>'الحالة'],['key'=>'due_date','label'=>'الاستحقاق'],
      ['key'=>'created_at','label'=>'تاريخ الإضافة']
    ],
  ];

  // Add standard sheets
  foreach ($order as $k => $meta) {
    if (!isset($datasets[$k]) || !is_array($datasets[$k]) || count($datasets[$k])===0) continue;
    $s = $spreadsheet->createSheet();
    $s->setTitle($meta['sheet']);
    // add details link column to the right if not exists
    $cols = $meta['cols'];
    $hasDetails = false;
    foreach ($cols as $c) if (($c['key']??'')==='details_link') $hasDetails=true;
    if (!$hasDetails) $cols[] = ['key'=>'details_link','label'=>'التفاصيل'];
    $writeDataSheet($s, $k, $datasets[$k], $cols);
  }

  // Add extra sheets
  $extraOrder = [
    ['key'=>'contractors','title'=>'المقاولون'],
    ['key'=>'contracts','title'=>'العقود'],
    ['key'=>'contract_services','title'=>'خدمات العقود'],
    ['key'=>'inspectors','title'=>'المراقبون'],
    ['key'=>'inspector_tasks','title'=>'مهام المراقبين'],
  ];
  foreach ($extraOrder as $meta) {
    $k = $meta['key'];
    if (!isset($datasets[$k]) || !is_array($datasets[$k]) || count($datasets[$k])===0) continue;
    $s = $spreadsheet->createSheet();
    $s->setTitle($meta['title']);
    $cols = $extraCols[$k];
    $writeDataSheet($s, $k, $datasets[$k], $cols);
  }

  // Set active sheet to Summary
  $spreadsheet->setActiveSheetIndex(1);

  // Output
  $fname = 'master_report_' . date('Y-m-d') . '.xlsx';
  header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
  header('Content-Disposition: attachment; filename="'.$fname.'"');
  header('Cache-Control: max-age=0');
  $writer = new Xlsx($spreadsheet);
  $writer->setIncludeCharts(true);
  $writer->save('php://output');
  exit;
}
// --------------------- END MASTER REPORT ---------------------

function xlsx_label_status(string $entity, string $key, $val): string {
  $v = (string)$val;
  $maps = [
    'oases' => ['active'=>'نشطة','inactive'=>'غير نشطة','under_maintenance'=>'تحت الصيانة'],
    'farms' => ['active'=>'نشطة','inactive'=>'غير نشطة','blocked'=>'محجوبة'],
    'aflaj' => ['operational'=>'تشغيلي','partial'=>'جزئي','stopped'=>'متوقف','under_maintenance'=>'تحت الصيانة'],
    'assets' => ['ok'=>'سليم','needs_service'=>'بحاجة صيانة','out_of_order'=>'متعطل'],
    'maintenance' => ['open'=>'مفتوحة','assigned'=>'مسندة','in_progress'=>'قيد التنفيذ','done'=>'منتهية','cancelled'=>'ملغاة','closed'=>'مغلقة'],
  ];
  if ($key === 'status' && isset($maps[$entity][$v])) return $maps[$entity][$v];
  if ($key === 'priority') {
    $pm = ['low'=>'منخفض','medium'=>'متوسط','high'=>'مرتفع','critical'=>'حرج'];
    return $pm[$v] ?? $v;
  }
  if ($key === 'owner_type') {
    $om = ['oasis'=>'واحة','falaj'=>'فلج','farm'=>'مزرعة'];
    return $om[$v] ?? $v;
  }
  if ($key === 'type') {
    $tm = ['surface'=>'سطحي','underground'=>'تحت الأرض','mixed'=>'مختلط'];
    return $tm[$v] ?? $v;
  }
  return $v;
}

function xlsx_is_date_key(string $key): bool {
  if (in_array($key, ['installed_at','reported_at','created_at','updated_at','completed_at'], true)) return true;
  if (str_ends_with($key, '_at')) return true;
  if (str_ends_with($key, '_date')) return true;
  if ($key === 'due_date') return true;
  return false;
}

function xlsx_format_date_value($val): string {
  $v = is_null($val) ? '' : trim((string)$val);
  if ($v === '' || $v === '0000-00-00' || $v === '0000-00-00 00:00:00') return '';
  // Normalize: keep first 19 chars if datetime
  if (strlen($v) >= 19 && $v[4] === '-' && $v[7] === '-' && ($v[10] === ' ' || $v[10] === 'T')) {
    $v = str_replace('T', ' ', $v);
    return substr($v, 0, 19);
  }
  // Date only
  if (strlen($v) >= 10 && $v[4] === '-' && $v[7] === '-') return substr($v, 0, 10);
  return $v;
}

function xlsx_fetch_rows(string $entity, bool $all): array {
  // NOTE: filtered mode respects the current list filters, all mode ignores them
  if ($entity === 'oases') {
    if ($all) {
      return db_all("SELECT * FROM oases ORDER BY id DESC LIMIT 50000", []);
    }
    $q = trim($_GET['q'] ?? '');
    $st = trim($_GET['status'] ?? '');
    $where = []; $params = [];
    if ($q !== '') {
      $where[] = "(code LIKE ? OR name LIKE ? OR area LIKE ?)";
      $params[] = "%$q%"; $params[] = "%$q%"; $params[] = "%$q%";
    }
    if ($st !== '' && in_array($st, ['active','inactive','under_maintenance'], true)) {
      $where[] = "status = ?";
      $params[] = $st;
    }
    $whereSql = $where ? ("WHERE " . implode(" AND ", $where)) : "";
    return db_all("SELECT * FROM oases $whereSql ORDER BY id DESC LIMIT 50000", $params);
  }

  if ($entity === 'aflaj') {
    if ($all) {
      return db_all("SELECT a.*, o.name AS oasis_name, o.code AS oasis_code
                     FROM aflaj a JOIN oases o ON o.id=a.oasis_id
                     ORDER BY a.id DESC LIMIT 50000", []);
    }
    $q = trim($_GET['q'] ?? '');
    $st = trim($_GET['status'] ?? '');
    $oasis_id = intval($_GET['oasis_id'] ?? 0);

    $where=[]; $params=[];
    if ($q!=='') { $where[]="(a.code LIKE ? OR a.name LIKE ?)"; $params[]="%$q%"; $params[]="%$q%"; }
    if ($st!=='' && in_array($st,['operational','partial','stopped','under_maintenance'],true)) { $where[]="a.status=?"; $params[]=$st; }
    if ($oasis_id>0) { $where[]="a.oasis_id=?"; $params[]=$oasis_id; }

    $whereSql=$where?("WHERE ".implode(" AND ",$where)):"";
    return db_all("SELECT a.*, o.name AS oasis_name, o.code AS oasis_code
                   FROM aflaj a JOIN oases o ON o.id=a.oasis_id
                   $whereSql
                   ORDER BY a.id DESC LIMIT 50000", $params);
  }

  if ($entity === 'farms') {
    if ($all) {
      return db_all("SELECT f.*, o.name AS oasis_name, o.code AS oasis_code
                     FROM farms f JOIN oases o ON o.id=f.oasis_id
                     ORDER BY f.id DESC LIMIT 50000", []);
    }
    $q = trim($_GET['q'] ?? '');
    $st = trim($_GET['status'] ?? '');
    $oasis_id = intval($_GET['oasis_id'] ?? 0);

    $where=[]; $params=[];
    if ($q!=='') { $where[]="(f.code LIKE ? OR f.owner_name LIKE ?)"; $params[]="%$q%"; $params[]="%$q%"; }
    if ($st!=='' && in_array($st,['active','inactive','blocked'],true)) { $where[]="f.status=?"; $params[]=$st; }
    if ($oasis_id>0) { $where[]="f.oasis_id=?"; $params[]=$oasis_id; }

    $whereSql=$where?("WHERE ".implode(" AND ",$where)):"";
    return db_all("SELECT f.*, o.name AS oasis_name, o.code AS oasis_code
                   FROM farms f JOIN oases o ON o.id=f.oasis_id
                   $whereSql
                   ORDER BY f.id DESC LIMIT 50000", $params);
  }

  if ($entity === 'assets') {
    if ($all) {
      return db_all("SELECT * FROM assets ORDER BY id DESC LIMIT 50000", []);
    }
    $q = trim($_GET['q'] ?? '');
    $st = trim($_GET['status'] ?? '');
    $owner_type = trim($_GET['owner_type'] ?? '');
    $owner_id = intval($_GET['owner_id'] ?? 0);

    $where=[]; $params=[];
    if ($q!=='') {
      $where[]="(tag LIKE ? OR category LIKE ? OR serial_number LIKE ?)";
      $params[]="%$q%"; $params[]="%$q%"; $params[]="%$q%";
    }
    if ($st!=='' && in_array($st,['ok','needs_service','out_of_order'],true)) { $where[]="status=?"; $params[]=$st; }
    if ($owner_type!=='' && in_array($owner_type,['oasis','falaj','farm'],true)) { $where[]="owner_type=?"; $params[]=$owner_type; }
    if ($owner_id>0) { $where[]="owner_id=?"; $params[]=$owner_id; }

    $whereSql = $where ? ("WHERE ".implode(" AND ",$where)) : "";
    return db_all("SELECT * FROM assets $whereSql ORDER BY id DESC LIMIT 50000", $params);
  }

  // maintenance
  if ($all) {
    $sql = "SELECT mr.*,
          a.tag AS asset_tag, a.owner_type, a.owner_id,
          u.name AS assigned_name,
          ct.name AS contractor_name, ct.code AS contractor_code,
          c.contract_no
          FROM maintenance_requests mr
          JOIN assets a ON a.id=mr.asset_id
          LEFT JOIN users u ON u.id=mr.assigned_to
          LEFT JOIN contractors ct ON ct.id=mr.contractor_id
          LEFT JOIN contracts c ON c.id=mr.contract_id
          ORDER BY mr.reported_at DESC LIMIT 80000";
    return db_all($sql, []);
  }

  $q = trim($_GET['q'] ?? '');
  $status = trim($_GET['status'] ?? '');
  $priority = trim($_GET['priority'] ?? '');
  $assigned_to = intval($_GET['assigned_to'] ?? 0);
  $contractor_id = intval($_GET['contractor_id'] ?? 0);
  $from = trim($_GET['from'] ?? '');
  $to = trim($_GET['to'] ?? '');

  $params = [];
  $sql = "SELECT mr.*,
          a.tag AS asset_tag, a.owner_type, a.owner_id,
          u.name AS assigned_name,
          ct.name AS contractor_name, ct.code AS contractor_code,
          c.contract_no
          FROM maintenance_requests mr
          JOIN assets a ON a.id=mr.asset_id
          LEFT JOIN users u ON u.id=mr.assigned_to
          LEFT JOIN contractors ct ON ct.id=mr.contractor_id
          LEFT JOIN contracts c ON c.id=mr.contract_id
          WHERE 1=1";

  if ($q!=='') {
    $sql .= " AND (a.tag LIKE ? OR mr.title LIKE ? OR mr.category LIKE ?)";
    $like="%{$q}%";
    array_push($params,$like,$like,$like);
  }
  if ($status!=='') { $sql .= " AND mr.status=?"; $params[]=$status; }
  if ($priority!=='') { $sql .= " AND mr.priority=?"; $params[]=$priority; }
  if ($assigned_to>0) { $sql .= " AND mr.assigned_to=?"; $params[]=$assigned_to; }
  if ($contractor_id>0) { $sql .= " AND mr.contractor_id=?"; $params[]=$contractor_id; }
  if ($from!=='') { $sql .= " AND mr.reported_at >= ?"; $params[]=$from.' 00:00:00'; }
  if ($to!=='') { $sql .= " AND mr.reported_at <= ?"; $params[]=$to.' 23:59:59'; }

  $sql .= " ORDER BY mr.reported_at DESC LIMIT 80000";
  return db_all($sql, $params);
}

function xlsx_columns(string $entity): array {
  $hasGeo = in_array($entity, ['oases','farms','assets'], true);
  $base = [];
  switch ($entity) {
    case 'oases':
      $base = [
        ['key'=>'id','label'=>'المعرف'],
        ['key'=>'code','label'=>'الرمز'],
        ['key'=>'name','label'=>'الاسم'],
        ['key'=>'area','label'=>'المنطقة'],
        ['key'=>'palm_trees_count','label'=>'عدد النخيل'],
        ['key'=>'falaj_outlets_count','label'=>'مخارج الفلج'],
        ['key'=>'status','label'=>'الحالة'],
        ['key'=>'lat','label'=>'Latitude'],
        ['key'=>'lng','label'=>'Longitude'],
      ];
      break;
    case 'aflaj':
      $base = [
        ['key'=>'id','label'=>'المعرف'],
        ['key'=>'oasis_code','label'=>'رمز الواحة'],
        ['key'=>'oasis_name','label'=>'اسم الواحة'],
        ['key'=>'code','label'=>'رمز الفلج'],
        ['key'=>'name','label'=>'اسم الفلج'],
        ['key'=>'type','label'=>'النوع'],
        ['key'=>'length_m','label'=>'الطول (م)'],
        ['key'=>'status','label'=>'الحالة'],
      ];
      break;
    case 'farms':
      $base = [
        ['key'=>'id','label'=>'المعرف'],
        ['key'=>'oasis_code','label'=>'رمز الواحة'],
        ['key'=>'oasis_name','label'=>'اسم الواحة'],
        ['key'=>'code','label'=>'رمز المزرعة'],
        ['key'=>'owner_name','label'=>'المالك'],
        ['key'=>'owner_phone','label'=>'هاتف'],
        ['key'=>'owner_emirates_id','label'=>'هوية إماراتية'],
        ['key'=>'area_m2','label'=>'المساحة (م²)'],
        ['key'=>'status','label'=>'الحالة'],
        ['key'=>'lat','label'=>'Latitude'],
        ['key'=>'lng','label'=>'Longitude'],
      ];
      break;
    case 'assets':
      $base = [
        ['key'=>'id','label'=>'المعرف'],
        ['key'=>'tag','label'=>'Tag'],
        ['key'=>'category','label'=>'التصنيف'],
        ['key'=>'owner_type','label'=>'الجهة'],
        ['key'=>'owner_id','label'=>'Owner ID'],
        ['key'=>'status','label'=>'الحالة'],
        ['key'=>'serial_number','label'=>'Serial'],
        ['key'=>'installed_at','label'=>'تاريخ التركيب'],
        ['key'=>'lat','label'=>'Latitude'],
        ['key'=>'lng','label'=>'Longitude'],
      ];
      break;
    default: // maintenance
      $base = [
        ['key'=>'id','label'=>'المعرف'],
        ['key'=>'asset_tag','label'=>'الأصل (Tag)'],
        ['key'=>'title','label'=>'العنوان'],
        ['key'=>'category','label'=>'التصنيف'],
        ['key'=>'priority','label'=>'الأولوية'],
        ['key'=>'status','label'=>'الحالة'],
        ['key'=>'reported_at','label'=>'تاريخ البلاغ'],
        ['key'=>'due_date','label'=>'تاريخ الاستحقاق'],
        ['key'=>'assigned_name','label'=>'المكلّف الداخلي'],
        ['key'=>'contractor_name','label'=>'المقاول'],
        ['key'=>'contract_no','label'=>'رقم العقد'],
        ['key'=>'estimated_cost','label'=>'تكلفة تقديرية'],
        ['key'=>'actual_cost','label'=>'تكلفة فعلية'],
      ];
      break;
  }

  // Append maps link column for entities with geo
  if ($hasGeo) {
    $base[] = ['key'=>'maps_link','label'=>'رابط Google Maps'];
  }

  // Common fields
  $base[] = ['key'=>'notes','label'=>'ملاحظات'];
  $base[] = ['key'=>'created_at','label'=>'تاريخ الإنشاء'];

  return $base;
}

function xlsx_compute_kpis(string $entity, array $rows): array {
  $total = count($rows);
  $missingGeo = 0;
  $activeOpen = 0;
  $incomplete = 0;

  $statusCounts = [];
  $priorityCounts = [];

  foreach ($rows as $r) {
    $lat = isset($r['lat']) ? trim((string)$r['lat']) : '';
    $lng = isset($r['lng']) ? trim((string)$r['lng']) : '';
    if (in_array($entity, ['oases','farms','assets'], true)) {
      if ($lat === '' || $lng === '' || $lat === '0' || $lng === '0') $missingGeo++;
    }

    $st = (string)($r['status'] ?? '');
    if ($st !== '') $statusCounts[$st] = ($statusCounts[$st] ?? 0) + 1;

    if ($entity === 'maintenance') {
      $p = (string)($r['priority'] ?? '');
      if ($p !== '') $priorityCounts[$p] = ($priorityCounts[$p] ?? 0) + 1;
      if (in_array($st, ['open','assigned','in_progress'], true)) $activeOpen++;
      // incomplete if missing key fields
      if (trim((string)($r['title'] ?? '')) === '' || trim((string)($r['asset_tag'] ?? '')) === '') $incomplete++;
    } else {
      if (in_array($st, ['active','operational','ok'], true)) $activeOpen++;
      if ($entity === 'oases') {
        if (trim((string)($r['code'] ?? '')) === '' || trim((string)($r['name'] ?? '')) === '') $incomplete++;
      } elseif ($entity === 'aflaj') {
        if (trim((string)($r['code'] ?? '')) === '' || trim((string)($r['name'] ?? '')) === '') $incomplete++;
      } elseif ($entity === 'farms') {
        if (trim((string)($r['code'] ?? '')) === '' || trim((string)($r['owner_name'] ?? '')) === '') $incomplete++;
      } elseif ($entity === 'assets') {
        if (trim((string)($r['tag'] ?? '')) === '' || trim((string)($r['category'] ?? '')) === '') $incomplete++;
      }
    }
  }

  return [
    'total' => $total,
    'missing_geo' => $missingGeo,
    'active_open' => $activeOpen,
    'incomplete' => $incomplete,
    'status_counts' => $statusCounts,
    'priority_counts' => $priorityCounts,
  ];
}

function xlsx_apply_header_style($sheet, string $range): void {
  $sheet->getStyle($range)->getFont()->setBold(true)->setColor(new Color('FFFFFFFF'));
  $sheet->getStyle($range)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('FF1F6D8C');
  $sheet->getStyle($range)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER)->setVertical(Alignment::VERTICAL_CENTER);
  $sheet->getStyle($range)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN)->setColor(new Color('FFD0D7DE'));
}

function xlsx_auto_width($sheet, array $headers, array $out, int $startRow): void {
  $colCount = count($headers);
  for ($i = 1; $i <= $colCount; $i++) {
    $letter = Coordinate::stringFromColumnIndex($i);
    $max = mb_strlen((string)($headers[$i-1] ?? ''), 'UTF-8');
    $limit = min(250, count($out));
    for ($j = 0; $j < $limit; $j++) {
      $v = (string)($out[$j][$i-1] ?? '');
      $len = mb_strlen($v, 'UTF-8');
      if ($len > $max) $max = $len;
    }
    $width = max(10, min(55, (int)ceil($max * 1.15)));
    $sheet->getColumnDimension($letter)->setWidth($width);
  }
}

function xlsx_build_status_table($summarySheet, string $entity, array $statusCounts, int $startRow = 13): array {
  // Table columns A=label, B=count
  $summarySheet->setCellValue('A'.$startRow, 'الحالة');
  $summarySheet->setCellValue('B'.$startRow, 'العدد');
  $summarySheet->getStyle('A'.$startRow.':B'.$startRow)->getFont()->setBold(true);
  $summarySheet->getStyle('A'.$startRow.':B'.$startRow)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('FFF3F6F9');
  $summarySheet->getStyle('A'.$startRow.':B'.$startRow)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN)->setColor(new Color('FFD0D7DE'));

  $row = $startRow + 1;
  $labels = [];
  $counts = [];
  foreach ($statusCounts as $st => $cnt) {
    $labels[] = xlsx_label_status($entity, 'status', $st);
    $counts[] = (int)$cnt;
    $summarySheet->setCellValue('A'.$row, end($labels));
    $summarySheet->setCellValue('B'.$row, (int)$cnt);
    $summarySheet->getStyle('A'.$row.':B'.$row)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN)->setColor(new Color('FFD0D7DE'));
    $row++;
  }
  return [$startRow, $row - 1, $labels, $counts];
}

function xlsx_add_pie_chart($summarySheet, int $startRow, int $endRow, string $title, string $posTopLeft, string $posBottomRight): void {
  if ($endRow < $startRow + 1) return;

  $dataSeriesLabels = [
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, "'ملخص'!\$B\${$startRow}", null, 1),
  ];
  $xAxisTickValues = [
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, "'ملخص'!\$A\$".($startRow+1).":\$A\${$endRow}", null, $endRow-($startRow)),
  ];
  $dataSeriesValues = [
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, "'ملخص'!\$B\$".($startRow+1).":\$B\${$endRow}", null, $endRow-($startRow)),
  ];

  $series = new DataSeries(
    DataSeries::TYPE_PIECHART,
    null,
    range(0, count($dataSeriesValues)-1),
    $dataSeriesLabels,
    $xAxisTickValues,
    $dataSeriesValues
  );

  $plotArea = new PlotArea(null, [$series]);
  $legend = new Legend(Legend::POSITION_RIGHT, null, false);
  $chartTitle = new Title($title);

  $chart = new Chart('chart_status', $chartTitle, $legend, $plotArea, true, 0, null, null);
  $chart->setTopLeftPosition($posTopLeft);
  $chart->setBottomRightPosition($posBottomRight);
  $summarySheet->addChart($chart);
}

/**
 * إضافة شعار بلدية العين في أعلى الصفحة.
 *
 * نقبل Spreadsheet أو Worksheet لأن بعض الاستدعاءات تمرّر Worksheet مباشرة.
 */
function xlsx_add_logo_if_exists($target, string $logoPath): void {
  if (!file_exists($logoPath)) return;
  // target can be Spreadsheet or Worksheet
  if ($target instanceof \PhpOffice\PhpSpreadsheet\Spreadsheet) {
    $sheet = $target->getSheet(0);
  } elseif ($target instanceof \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet) {
    $sheet = $target;
  } else {
    return;
  }
  $drawing = new Drawing();
  $drawing->setName('Logo');
  $drawing->setDescription('Logo');
  $drawing->setPath($logoPath);
  $drawing->setHeight(52);
  // RTL: put at H1 if possible
  $drawing->setCoordinates('H1');
  $drawing->setOffsetX(5);
  $drawing->setOffsetY(5);
  $drawing->setWorksheet($sheet);
}

// --------------------- build workbook ---------------------

$rows = xlsx_fetch_rows($entity, $all);
$cols = xlsx_columns($entity);

$sheetTitleMap = [
  'oases' => 'الواحات',
  'aflaj' => 'الأفلاج',
  'farms' => 'المزارع',
  'assets' => 'الأصول',
  'maintenance' => 'الصيانة',
];

$root = dirname(__DIR__, 2); // project root
$publicDir = $root . '/public';
$templatePath = $publicDir . '/assets/branding/excel_template.xlsx';
$logoPath = $publicDir . '/assets/branding/municipality-logo.png';

$spreadsheet = null;
try {
  if (file_exists($templatePath)) {
    $spreadsheet = IOFactory::load($templatePath);
  }
} catch (\Throwable $e) {
  $spreadsheet = null;
}

if (!$spreadsheet) {
  $spreadsheet = new Spreadsheet();
  // Ensure two sheets: summary + data
  $spreadsheet->getActiveSheet()->setTitle('ملخص');
  $spreadsheet->createSheet()->setTitle('البيانات');
}

if ($spreadsheet->getSheetCount() < 2) {
  $spreadsheet->createSheet()->setTitle('البيانات');
}

$summarySheet = $spreadsheet->getSheet(0);
$dataSheet = $spreadsheet->getSheet(1);

$summarySheet->setTitle('ملخص');
$dataSheet->setTitle('البيانات');

$summarySheet->setRightToLeft(true);
$dataSheet->setRightToLeft(true);

$spreadsheet->getDefaultStyle()->getFont()->setName('Arial')->setSize(11);

$generatedAt = date('Y-m-d H:i');
$reportTitle = 'تقرير ' . ($sheetTitleMap[$entity] ?? 'تصدير') . ' (Excel)';
$scopeLabel = $all ? 'كل البيانات' : 'حسب الفلاتر الحالية';

// Fill template header areas if present
$summarySheet->setCellValue('A1', $reportTitle);
$summarySheet->setCellValue('A4', 'الجهة: قسم الواحات والأفلاج — بلدية العين    |    تاريخ التوليد: ' . $generatedAt . '    |    النطاق: ' . $scopeLabel);

// KPIs
$kpis = xlsx_compute_kpis($entity, $rows);
// KPI cards positions (based on template excel_template.xlsx)
$summarySheet->setCellValue('A8', $kpis['total']);
$summarySheet->setCellValue('C8', $kpis['missing_geo']);
$summarySheet->setCellValue('E8', $kpis['active_open']);
$summarySheet->setCellValue('G8', $kpis['incomplete']);

// Status distribution table + chart
$statusCounts = $kpis['status_counts'];
arsort($statusCounts);
list($tblStart, $tblEnd) = xlsx_build_status_table($summarySheet, $entity, $statusCounts, 13);

// Chart placement (avoid overlap with table A:B)
xlsx_add_pie_chart($summarySheet, $tblStart, $tblEnd, 'توزيع حسب الحالة', 'D13', 'H29');

// Insert logo if exists
xlsx_add_logo_if_exists($spreadsheet, $logoPath);

// --------------------- data sheet ---------------------
$dataSheet->setCellValue('A1', 'البيانات التفصيلية — ' . ($sheetTitleMap[$entity] ?? ''));
$dataSheet->mergeCells('A1:K2');
$dataSheet->getStyle('A1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER)->setVertical(Alignment::VERTICAL_CENTER);
$dataSheet->getStyle('A1')->getFont()->setBold(true)->setSize(16)->setColor(new Color('FF0E3A53'));

// Header row at row 4
$headers = [];
foreach ($cols as $c) $headers[] = (string)$c['label'];

$startHeaderRow = 4;
$dataSheet->fromArray($headers, null, 'A'.$startHeaderRow);
$endHeaderCol = Coordinate::stringFromColumnIndex(count($headers));
xlsx_apply_header_style($dataSheet, 'A'.$startHeaderRow.':'.$endHeaderCol.$startHeaderRow);
$dataSheet->freezePane('A'.($startHeaderRow+1));
$dataSheet->setAutoFilter('A'.$startHeaderRow.':'.$endHeaderCol.$startHeaderRow);

// Data rows start at row 5
$out = [];
foreach ($rows as $r) {
  $row = [];
  foreach ($cols as $c) {
    $k = (string)$c['key'];

    if ($k === 'maps_link') {
      $lat = isset($r['lat']) ? trim((string)$r['lat']) : '';
      $lng = isset($r['lng']) ? trim((string)$r['lng']) : '';
      if ($lat !== '' && $lng !== '' && $lat !== '0' && $lng !== '0') {
        $row[] = 'فتح الخريطة';
      } else {
        $row[] = '';
      }
      continue;
    }

    $val = $r[$k] ?? '';
    if (in_array($k, ['status','priority','owner_type','type'], true)) {
      $val = xlsx_label_status($entity, $k, $val);
    }

    if (in_array($k, ['lat','lng'], true)) {
      $v = is_null($val) ? '' : trim((string)$val);
      if ($v === '') $row[] = '';
      else $row[] = number_format((float)$v, 6, '.', '');
      continue;
    }

    if (xlsx_is_date_key($k)) {
      $row[] = xlsx_format_date_value($val);
      continue;
    }

    $row[] = is_null($val) ? '' : (string)$val;
  }
  $out[] = $row;
}

$startDataRow = $startHeaderRow + 1;
if ($out) {
  $dataSheet->fromArray($out, null, 'A'.$startDataRow);
}

// Hyperlinks for maps_link column (entities with geo)
$hasGeo = in_array($entity, ['oases','farms','assets'], true);
if ($hasGeo) {
  $mapsColIndex = null;
  for ($i=0; $i<count($cols); $i++) {
    if ($cols[$i]['key'] === 'maps_link') { $mapsColIndex = $i+1; break; }
  }
  if ($mapsColIndex) {
    $latIndex = null; $lngIndex = null;
    for ($i=0; $i<count($cols); $i++) {
      if ($cols[$i]['key'] === 'lat') $latIndex = $i+1;
      if ($cols[$i]['key'] === 'lng') $lngIndex = $i+1;
    }
    $mapsLetter = Coordinate::stringFromColumnIndex($mapsColIndex);
    $latLetter = $latIndex ? Coordinate::stringFromColumnIndex($latIndex) : null;
    $lngLetter = $lngIndex ? Coordinate::stringFromColumnIndex($lngIndex) : null;

    if ($latLetter && $lngLetter) {
      $maxRow = $startDataRow + count($out) - 1;
      for ($r=$startDataRow; $r<=$maxRow; $r++) {
        $text = (string)$dataSheet->getCell($mapsLetter.$r)->getValue();
        if ($text === 'فتح الخريطة') {
          $lat = (string)$dataSheet->getCell($latLetter.$r)->getValue();
          $lng = (string)$dataSheet->getCell($lngLetter.$r)->getValue();
          if ($lat !== '' && $lng !== '') {
            $url = 'https://www.google.com/maps?q=' . rawurlencode($lat . ',' . $lng);
            $dataSheet->getCell($mapsLetter.$r)->getHyperlink()->setUrl($url);
            $dataSheet->getStyle($mapsLetter.$r)->getFont()->getColor()->setARGB('FF1A73E8');
            $dataSheet->getStyle($mapsLetter.$r)->getFont()->setUnderline(true);
          }
        }
      }
    }
  }
}

// Auto width
xlsx_auto_width($dataSheet, $headers, $out, $startDataRow);

// Filename
$fname = $entity . '_' . date('Y-m-d') . '.xlsx';

// Output
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="'.$fname.'"');
header('Cache-Control: max-age=0');

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