<?php
// Data Import (CSV/XLSX) with preview
require_once __DIR__.'/../auth.php';
auth_require();
require_once __DIR__.'/../db.php';
require_once __DIR__.'/../helpers.php';

// === Compatibility helpers ===
// Base project uses auth_can()/csrf_check()/csrf_input().
// Some pages use can()/csrf_verify()/csrf_field().
if (!function_exists('can')) {
  function can(string $perm): bool {
    return function_exists('auth_can') ? auth_can($perm) : true;
  }
}

// --- Import helpers: DB column names are the source of truth ---
if (!function_exists('import_entity_table')) {
  function import_entity_table($entity) {
    // For now the entity key matches the table name.
    return $entity;
  }
}

if (!function_exists('db_first_safe')) {
  function db_first_safe($sql, $params = []) {
    // Some installs may not have db_first(). Provide a safe fallback.
    if (function_exists('db_first')) {
      return db_first($sql, $params);
    }
    $rows = db_all($sql . ' LIMIT 1', $params);
    return $rows && isset($rows[0]) ? $rows[0] : null;
  }
}

if (!function_exists('db_describe_table')) {
  function db_describe_table($table) {
    $rows = db_all("DESCRIBE `{$table}`");
    $out = [];
    foreach ($rows as $r) {
      $field = $r['Field'];
      $out[$field] = [
        'Field'   => $field,
        'Type'    => $r['Type'],
        'Null'    => $r['Null'],
        'Default' => $r['Default'],
        'Extra'   => $r['Extra'],
      ];
    }
    return $out;
  }
}

if (!function_exists('import_column_ignore_list')) {
  function import_column_ignore_list() {
    // Columns managed by the system or not suitable for bulk import.
    return [
      'id',
      'created_at',
      'updated_at',
      'created_by',
      'updated_by',
      'deleted_at',
    ];
  }
}

if (!function_exists('import_expected_columns')) {
  function import_expected_columns($entity) {
    $table = import_entity_table($entity);
    $desc  = db_describe_table($table);
    $ignore = array_flip(array_map('strtolower', import_column_ignore_list()));
    $expected = [];

    $descLower = [];
    foreach ($desc as $c => $m) { $descLower[strtolower($c)] = $m; }

    foreach ($desc as $col => $meta) {
      $k = strtolower($col);
      if (isset($ignore[$k])) continue;

      // Required = NOT NULL with no default and not auto_increment.
      $isAuto = stripos($meta['Extra'], 'auto_increment') !== false;
      $required = (!$isAuto) && ($meta['Null'] === 'NO') && ($meta['Default'] === null);
      // Use normalized header keys (lowercase) across the importer.
      $expected[$k] = $required;
    }

    // If table has a code/name but DB marks them nullable for any reason, enforce them.
    if (isset($descLower['code'])) $expected['code'] = true;
    if (isset($descLower['name'])) $expected['name'] = true;
    if (isset($descLower['tag']))  $expected['tag']  = true; // common for assets

    return $expected;
  }
}

if (!function_exists('csrf_field')) {
  function csrf_field(): string {
    if (function_exists('csrf_input')) return csrf_input();
    if (session_status() != PHP_SESSION_ACTIVE) @session_start();
    if (!isset($_SESSION['_csrf'])) $_SESSION['_csrf'] = bin2hex(random_bytes(16));
    return '<input type="hidden" name="_csrf" value="'.htmlspecialchars($_SESSION['_csrf'], ENT_QUOTES, 'UTF-8').'">';
  }
}

if (!function_exists('csrf_verify')) {
  function csrf_verify(): void {
    if (function_exists('csrf_check')) { csrf_check(); return; }
    if (session_status() != PHP_SESSION_ACTIVE) @session_start();
    $t = $_POST['_csrf'] ?? '';
    if (!$t || !isset($_SESSION['_csrf']) || !hash_equals($_SESSION['_csrf'], $t)) {
      http_response_code(400);
      die('CSRF check failed');
    }
  }
}


// db_first() helper may be missing in some builds; import relies on it.
if (!function_exists('db_first')) {
  function db_first(string $sql, array $params = []) {
    $sqlTrim = trim($sql);
    // Add LIMIT 1 when not already present.
    if (stripos($sqlTrim, ' limit ') === false && stripos($sqlTrim, "\nlimit ") === false) {
      $sqlTrim .= " LIMIT 1";
    }
    $rows = db_all($sqlTrim, $params);
    return $rows && count($rows) ? $rows[0] : null;
  }
}

// Normalize integer-like values from CSV/XLSX (handles commas, spaces, Arabic digits)
if (!function_exists('norm_int')) {
  function norm_int($v): ?int {
    if ($v === null) return null;

    // Already numeric
    if (is_int($v)) return $v;
    if (is_float($v)) return (int)round($v);

    $s = trim((string)$v);
    if ($s === '') return null;

    // Convert Arabic-Indic digits to Western digits
    $arabic = ['٠','١','٢','٣','٤','٥','٦','٧','٨','٩','۰','۱','۲','۳','۴','۵','۶','۷','۸','۹'];
    $western = ['0','1','2','3','4','5','6','7','8','9','0','1','2','3','4','5','6','7','8','9'];
    $s = str_replace($arabic, $western, $s);

    // Remove thousand separators and spaces
    $s = str_replace([',', ' ', "\t"], '', $s);

    // Keep optional leading minus, strip other non-digits
    $neg = false;
    if (strlen($s) && $s[0] === '-') { $neg = true; $s = substr($s, 1); }
    $s = preg_replace('/[^0-9]/', '', $s);
    if ($s === '' || $s === null) return null;

    $n = (int)$s;
    return $neg ? -$n : $n;
  }
}


// Permission: reuse reports permission
if (!can('view_reports')) {
  http_response_code(403);
  echo "<div class='card'><h2>غير مصرح</h2><p>ليس لديك صلاحية للوصول.</p></div>";
  return;
}

$entity = $_GET['entity'] ?? ($_POST['entity'] ?? 'oases');
if (!in_array($entity, ['oases','farms','assets'], true)) $entity = 'oases';

$storageDir = __DIR__ . '/../../storage/imports';
if (!is_dir($storageDir)) { @mkdir($storageDir, 0775, true); }

if (!function_exists('import_entity_labels')) {
function import_entity_labels() {
  return [
    'oases' => 'الواحات',
    'farms' => 'المزارع',
    'assets' => 'الأصول',
  ];
}

function normalize_headers($headers) {
  $out = [];
  foreach ($headers as $h) {
    $h = strtolower(trim((string)$h));
    $h = str_replace([' ', '-', "\t"], ['_', '_', '_'], $h);
    // Legacy aliases -> DB-style names (helps when old templates are used)
    if ($h === 'palm_trees') $h = 'palm_trees_count';
    if ($h === 'aflaj_outlets') $h = 'falaj_outlets_count';
    if ($h === 'aflaj_outlets_count') $h = 'falaj_outlets_count';
    // Do NOT force lng/ing here; we'll map using real DB columns later.
    $out[] = $h;
  }
  return $out;
}

// ---------------------------
// Spreadsheet reader (CSV/XLSX)
// ---------------------------
if (!function_exists('spreadsheet_last_error')) {
  function spreadsheet_last_error() {
    return $GLOBALS['__spreadsheet_last_error'] ?? null;
  }
}
if (!function_exists('_spreadsheet_set_error')) {
  function _spreadsheet_set_error($msg) {
    $GLOBALS['__spreadsheet_last_error'] = $msg;
    return null;
  }
}
if (!function_exists('_spreadsheet_detect_delimiter')) {
  function _spreadsheet_detect_delimiter($line) {
    $candidates = [',',';',"\t",'|'];
    $best = ',';
    $bestCount = -1;
    foreach ($candidates as $d) {
      $count = substr_count($line, $d);
      if ($count > $bestCount) { $bestCount = $count; $best = $d; }
    }
    return $best;
  }
}
if (!function_exists('_spreadsheet_col_to_index')) {
  function _spreadsheet_col_to_index($letters) {
    $letters = strtoupper($letters);
    $n = 0;
    $len = strlen($letters);
    for ($i=0; $i<$len; $i++) {
      $ch = ord($letters[$i]);
      if ($ch < 65 || $ch > 90) continue;
      $n = $n * 26 + ($ch - 64);
    }
    return $n - 1; // zero-based
  }
}
if (!function_exists('read_spreadsheet_rows')) {
  /**
   * Read first $limit rows from CSV/XLSX and return as array of associative rows.
   * Uses first row as headers.
   */
  // $sheetKey: optional worksheet name selector for XLSX (e.g., 'oases', 'farms', 'assets').
  function read_spreadsheet_rows($path, $limit=50, $sheetKey=null) {
  $ext = strtolower(pathinfo($path, PATHINFO_EXTENSION));

  // CSV
  if ($ext === 'csv') {
    $rows = [];
    $fh = @fopen($path, 'r');
    if (!$fh) {
      flash_set('err', 'تعذر قراءة الملف.');
      return [];
    }
    $i = 0;
    while (($row = fgetcsv($fh)) !== false) {
      $rows[] = array_map(function($v){ return is_string($v) ? trim($v) : $v; }, $row);
      $i++;
      if ($i >= ($limit + 1)) break; // header + limit rows
    }
    fclose($fh);
    return $rows;
  }

  // XLSX (بدون Composer) — قراءة مبسطة لأول ورقة عمل
  if ($ext === 'xlsx') {
    if (!class_exists('ZipArchive')) {
      flash_set('err', 'ZipArchive غير متوفر في PHP. فعّل ext-zip.');
      return [];
    }

    $zip = new ZipArchive();
    if ($zip->open($path) !== true) {
      flash_set('err', 'تعذر فتح ملف XLSX.');
      return [];
    }

    // sharedStrings (اختياري)
    $shared = [];
    $sharedXml = $zip->getFromName('xl/sharedStrings.xml');
    if ($sharedXml !== false) {
      $sx = @simplexml_load_string($sharedXml);
      if ($sx && isset($sx->si)) {
        foreach ($sx->si as $si) {
          $t = '';
          if (isset($si->t)) {
            $t = (string)$si->t;
          } elseif (isset($si->r)) {
            foreach ($si->r as $r) { $t .= (string)$r->t; }
          }
          $shared[] = $t;
        }
      }
    }

    // اختيار sheet حسب نوع الكيان (إن وُجد) وإلا أول ورقة.
    $sheetName = null;
    $want = is_string($sheetKey) ? trim($sheetKey) : '';
    $wantNorm = $want !== '' ? mb_strtolower($want, 'UTF-8') : '';

    // Aliases لتسمية الأوراق داخل قالب Excel.
    $aliases = [];
    if (in_array($wantNorm, ['oases','oasis'], true)) {
      $aliases = ['oases','oasis','واحات','الواحات','واحة'];
    } elseif (in_array($wantNorm, ['farms','farm'], true)) {
      $aliases = ['farms','farm','مزارع','المزارع','مزرعة'];
    } elseif (in_array($wantNorm, ['assets','asset'], true)) {
      $aliases = ['assets','asset','assets (future)','أصول','الاصول','الأصول','أصل','اصل'];
    } elseif ($wantNorm !== '') {
      $aliases = [$wantNorm, $want];
    }

    // محاولة اختيار الورقة بالاسم عبر workbook.xml
    if (!empty($aliases)) {
      $wbXml = $zip->getFromName('xl/workbook.xml');
      $relsXml = $zip->getFromName('xl/_rels/workbook.xml.rels');
      if ($wbXml !== false && $relsXml !== false) {
        $workbook = @simplexml_load_string($wbXml);
        $rels = @simplexml_load_string($relsXml);
        if ($workbook && $rels) {
          $ns = $workbook->getNamespaces(true);
          $ridToTarget = [];
          foreach ($rels->Relationship as $r) {
            $id = (string)$r['Id'];
            $target = (string)$r['Target'];
            if ($id !== '' && $target !== '') {
              $ridToTarget[$id] = ltrim($target, '/');
            }
          }

          $chosenRid = null;
          foreach ($workbook->sheets->sheet as $s) {
            $name = trim((string)$s['name']);
            $nameNorm = $name !== '' ? mb_strtolower($name, 'UTF-8') : '';
	            // Relationship id is stored as r:id on the <sheet> element.
	            // SimpleXMLElement::attributes expects a namespace URI string, not an array.
	            $ridNs = $ns['r'] ?? 'http://schemas.openxmlformats.org/officeDocument/2006/relationships';
	            $ridAttr = $s->attributes($ridNs);
	            $rid = isset($ridAttr['id']) ? (string)$ridAttr['id'] : '';

            foreach ($aliases as $a) {
              $a = trim((string)$a);
              if ($a === '') continue;
              $aNorm = mb_strtolower($a, 'UTF-8');
              if ($nameNorm === $aNorm || str_starts_with($nameNorm, $aNorm)) {
                $chosenRid = $rid;
                break 2;
              }
            }
          }

          if ($chosenRid && isset($ridToTarget[$chosenRid])) {
            $sheetName = 'xl/' . $ridToTarget[$chosenRid];
          }
        }
      }
    }

    // fallback: اختر sheet1 ثم أول sheet موجود.
    if (!$sheetName) {
      $sheetName = 'xl/worksheets/sheet1.xml';
      if ($zip->getFromName($sheetName) === false) {
        for ($i=0; $i<$zip->numFiles; $i++) {
          $st = $zip->statIndex($i);
          $n = $st['name'] ?? '';
          if (preg_match('~^xl/worksheets/sheet\d+\.xml$~', $n)) {
            $sheetName = $n;
            break;
          }
        }
      }
    }

    $sheetXml = $zip->getFromName($sheetName);
    if ($sheetXml === false) {
      $zip->close();
      flash_set('err', 'تعذر قراءة بيانات الورقة داخل ملف XLSX.');
      return [];
    }

    $sheet = @simplexml_load_string($sheetXml);
    if (!$sheet || !isset($sheet->sheetData)) {
      $zip->close();
      flash_set('err', 'ملف XLSX غير صالح أو لا يحتوي بيانات.');
      return [];
    }

    $letters_to_index = function($letters) {
      $letters = strtoupper((string)$letters);
      $n = 0;
      for ($i=0; $i<strlen($letters); $i++) {
        $n = $n * 26 + (ord($letters[$i]) - 64);
      }
      return $n - 1; // A=>0
    };

    $rows = [];
    foreach ($sheet->sheetData->row as $row) {
      $cells = [];
      foreach ($row->c as $c) {
        $ref = (string)$c['r'];
        if (!preg_match('/^([A-Z]+)/', $ref, $m)) continue;
        $colLetters = $m[1];
        $colIndex = $letters_to_index($colLetters);

        $t = (string)$c['t'];
        $v = '';
        if ($t === 's') {
          $idx = intval((string)$c->v);
          $v = $shared[$idx] ?? '';
        } elseif ($t === 'inlineStr') {
          if (isset($c->is->t)) {
            $v = (string)$c->is->t;
          } elseif (isset($c->is->r)) {
            foreach ($c->is->r as $r) { $v .= (string)$r->t; }
          }
        } else {
          $v = isset($c->v) ? (string)$c->v : '';
        }

        $cells[$colIndex] = is_string($v) ? trim($v) : $v;
      }

      if (!$cells) continue;

      $max = max(array_keys($cells));
      $rowArr = [];
      for ($i=0; $i<=$max; $i++) {
        $rowArr[] = isset($cells[$i]) ? (string)$cells[$i] : '';
      }
      $rows[] = $rowArr;

      if (count($rows) >= ($limit + 1)) break; // header + limit rows
    }

    $zip->close();

    // Convert numeric rows (row 1 = headers) into associative rows (like CSV)
    if (!$rows) return [];

    $headerRow = $rows[0] ?? [];
    $headers = [];
    $seen = [];
    foreach ($headerRow as $h) {
      $k = trim((string)$h);
      if ($k === '') { $headers[] = ''; continue; }
      $key = strtolower($k);
      if (isset($seen[$key])) {
        $seen[$key] += 1;
        $k = $k . '_' . $seen[$key];
      } else {
        $seen[$key] = 1;
      }
      $headers[] = $k;
    }

    $out = [];
    $rowCount = count($rows);
    for ($ri = 1; $ri < $rowCount; $ri++) {
      $r = $rows[$ri];
      if (!is_array($r)) continue;
      $assoc = [];
      $hasAny = false;
      $colCount = count($headers);
      for ($ci = 0; $ci < $colCount; $ci++) {
        $hk = $headers[$ci] ?? '';
        if ($hk === '') continue;
        $val = $r[$ci] ?? '';
        if (is_string($val)) $val = trim($val);
        if ($val !== '' && $val !== null) $hasAny = true;
        $assoc[$hk] = $val;
      }
      if ($hasAny) $out[] = $assoc;
      if (count($out) >= $limit) break;
    }

    return $out;
  }

  flash_set('err','صيغة الملف غير مدعومة. استخدم CSV أو XLSX.');
  return [];
}
}

function import_preview_read($path, $maxRows = 50, $entityKey=null) {
  return read_spreadsheet_rows($path, $maxRows, $entityKey);
}

function import_store_upload($storageDir, $file) {
  if (!isset($file) || !is_array($file) || ($file['error'] ?? UPLOAD_ERR_NO_FILE) !== UPLOAD_ERR_OK) {
    return [null, 'الرجاء اختيار ملف صالح.'];
  }
  $ext = strtolower(pathinfo($file['name'] ?? '', PATHINFO_EXTENSION));
  if (!in_array($ext, ['csv','xlsx'], true)) {
    return [null, 'صيغة الملف غير مدعومة. استخدم CSV أو XLSX.'];
  }
  $name = 'import_' . date('Ymd_His') . '_' . bin2hex(random_bytes(4)) . '.' . $ext;
  $target = rtrim($storageDir, '/\\') . DIRECTORY_SEPARATOR . $name;
  if (!move_uploaded_file($file['tmp_name'], $target)) {
    return [null, 'فشل رفع الملف.'];
  }
  return [$target, null];
}

function import_validate_columns($entity, $rows) {
  if (!$rows || !is_array($rows) || count($rows) === 0) return 'الملف فارغ أو غير قابل للقراءة.';
  $headers = array_keys($rows[0]);
  $expected = import_expected_columns($entity);
  $missing = [];
  foreach ($expected as $col => $required) {
    if ($required && !in_array($col, $headers, true)) $missing[] = $col;
  }
  if ($missing) {
    return 'حقول مفقودة في الملف: ' . implode(', ', $missing);
  }
  return null;
}

function import_resolve_oasis_id_by_code($code) {
  $code = trim((string)$code);
  if ($code === '') return null;
  $row = db_first('SELECT id FROM oases WHERE code=?', [$code]);
  return $row ? intval($row['id']) : null;
}

function import_insert_rows($entity, $path, &$summary, $sheetName = null) {
  $summary = [
    'total' => 0,
    'inserted' => 0,      // إدخال/تحديث
    'skipped' => 0,
    'errors' => [],
    'skipped_reasons' => [],
    'skipped_rows' => [], // عيّنة (حتى 50)
  ];

  // سجل سبب التخطي + احتفظ بعينة صغيرة
  $skip = function (int $lineNo, string $reason, string $details = '') use (&$summary) {
    $summary['skipped']++;
    $summary['skipped_reasons'][$reason] = ($summary['skipped_reasons'][$reason] ?? 0) + 1;
    if (count($summary['skipped_rows']) < 50) {
      $summary['skipped_rows'][] = ['line' => $lineNo, 'reason' => $reason, 'details' => $details];
    }
  };

  $table = import_entity_table($entity);
  $desc = db_describe_table($table); // exact column names
  if (!$desc) return 'تعذر قراءة بنية الجدول: ' . htmlspecialchars($table);

  // Build maps (normalized => exact)
  $dbNormToExact = [];
  $typeByNorm = [];
  $ignore = array_flip(array_map('strtolower', import_column_ignore_list()));
  foreach ($desc as $exact => $meta) {
    $norm = strtolower($exact);
    $dbNormToExact[$norm] = $exact;
    $typeByNorm[$norm] = $meta['Type'] ?? '';
  }

  // Read rows (first sheet name defaults to entity)
  $sheet = $sheetName ?: $entity;
  $rows = read_spreadsheet_rows($path, 500000, $sheet);
  if (!$rows) return 'الملف فارغ أو غير قابل للقراءة.';

  // Normalize headers (case/spacing)
  $normRows = [];
  $firstKeys = array_keys($rows[0]);
  $normKeys = normalize_headers($firstKeys);

  // Map common lng/ing based on real DB columns
  $hasLng = isset($dbNormToExact['lng']);
  $hasIng = isset($dbNormToExact['ing']) && !$hasLng;

  foreach ($rows as $r) {
    $nr = [];
    $i = 0;
    foreach ($r as $k => $v) {
      $nk = $normKeys[$i] ?? strtolower(trim((string)$k));
      // lng/ing mapping depending on DB
      if ($nk === 'ing' and $hasLng) $nk = 'lng';
      if ($nk === 'lng' and $hasIng) $nk = 'ing';
      $nr[$nk] = is_string($v) ? trim($v) : $v;
      $i++;
    }
    $normRows[] = $nr;
  }
  $rows = $normRows;

  // Validate headers vs DB required columns
  $err = import_validate_columns($entity, $rows);
  if ($err) return $err;

  // Decide key column for upsert
  $keyNorm = null;
  if (isset($dbNormToExact['code'])) $keyNorm = 'code';
  elseif (isset($dbNormToExact['tag'])) $keyNorm = 'tag';

  $now = now_dt();

  foreach ($rows as $idx => $row) {
    $summary['total']++;
    $lineNo = $idx + 2; // + header row

    try {
      // Check required values (based on DB)
      $expected = import_expected_columns($entity);
      $missingVals = [];
      foreach ($expected as $col => $required) {
        if (!$required) continue;
        $val = $row[$col] ?? null;
        if ($val === null) { $missingVals[] = $col; continue; }
        if (is_string($val) && trim($val) === '') { $missingVals[] = $col; continue; }
      }
      if ($missingVals) { $skip($lineNo, 'حقول مطلوبة ناقصة', implode(', ', $missingVals)); continue; }

      // Build insert/update payload strictly from DB columns
      $data = [];
      foreach ($row as $colNorm => $val) {
        if (!isset($dbNormToExact[$colNorm])) continue;           // unknown column
        if (isset($ignore[$colNorm])) continue;                   // managed/system

        $exact = $dbNormToExact[$colNorm];
        $type = strtolower($typeByNorm[$colNorm] ?? '');

        // Empty string -> NULL (except where DB expects NOT NULL and user filled it)
        if (is_string($val) && trim($val) === '') $val = null;

        // Basic type normalization
        if ($val !== null) {
          if (strpos($type, 'int') !== false) {
            $val = norm_int($val);
          } elseif (strpos($type, 'decimal') !== false || strpos($type, 'float') !== false || strpos($type, 'double') !== false) {
            $val = norm_float($val);
          }
        }

        $data[$exact] = $val;
      }

      // Auto stamps if present
      if (isset($dbNormToExact['created_at']) && !isset($data[$dbNormToExact['created_at']])) {
        $data[$dbNormToExact['created_at']] = $now;
      }
      if (isset($dbNormToExact['updated_at'])) {
        $data[$dbNormToExact['updated_at']] = $now;
      }
      if (isset($dbNormToExact['created_by']) && !isset($data[$dbNormToExact['created_by']])) {
        $u = function_exists('auth_user') ? auth_user() : null;
        if ($u && isset($u['id'])) $data[$dbNormToExact['created_by']] = intval($u['id']);
      }

      // Need a key for upsert
      if (!$keyNorm || !isset($row[$keyNorm]) || (is_string($row[$keyNorm]) && trim($row[$keyNorm]) === '')) {
        // No key -> insert only (still allowed)
        $keyNorm = null;
      }

      $existsId = null;
      if ($keyNorm) {
        $keyExact = $dbNormToExact[$keyNorm];
        $keyVal = $data[$keyExact] ?? null;
        if ($keyVal !== null && !(is_string($keyVal) && trim($keyVal) === '')) {
          $found = db_first_safe("SELECT id FROM `{$table}` WHERE `{$keyExact}`=? LIMIT 1", [$keyVal]);
          if ($found && isset($found['id'])) $existsId = intval($found['id']);
        }
      }

      if ($existsId) {
        // UPDATE
        $sets = [];
        $params = [];
        foreach ($data as $col => $val) {
          if (strtolower($col) === 'id') continue;
          // don't change created_at/created_by on update
          $lc = strtolower($col);
          if ($lc === 'created_at' || $lc === 'created_by') continue;
          $sets[] = "`{$col}`=?";
          $params[] = $val;
        }
        if ($sets) {
          $params[] = $existsId;
          db_exec("UPDATE `{$table}` SET " . implode(',', $sets) . " WHERE id=?", $params);
        }
        $summary['inserted']++;
      } else {
        // INSERT
        if (!$data) { $skip($lineNo, 'لا توجد أعمدة صالحة للإدخال'); continue; }
        $cols = array_keys($data);
        $placeholders = implode(',', array_fill(0, count($cols), '?'));
        $sql = "INSERT INTO `{$table}` (`" . implode('`,`', $cols) . "`) VALUES ({$placeholders})";
        db_insert($sql, array_values($data));
        $summary['inserted']++;
      }

    } catch (Exception $e) {
      $summary['errors'][] = 'سطر '.$lineNo.': '.$e->getMessage();
      $skip($lineNo, 'خطأ أثناء الإدخال', $e->getMessage());
    }
  }

  return null;
}
}
$labels = import_entity_labels();

// State
$preview = null;
$previewError = null;
$importDone = null;
$importSummary = null;

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
  $entity = $_POST['entity'] ?? $entity;
  $op = $_POST['op'] ?? '';
  $isPreview = isset($_POST['do_preview']) || $op === 'preview';
  $isImport  = isset($_POST['do_import']) || $op === 'import';
  if ($isPreview || $isImport) {
    csrf_verify();
  }

  // إذا وصل POST بدون حقول (قد يحدث عند تجاوز post_max_size)، أعطِ رسالة واضحة بدل أن يبدو وكأن لا شيء حصل.
  if (!$isPreview && !$isImport) {
    $previewError = 'لم يتم استلام طلب المعاينة/الاستيراد. تأكد من اختيار الملف ثم اضغط معاينة. وإن استمر الأمر، تحقق من إعدادات PHP: post_max_size و upload_max_filesize.';
  }

  if ($isPreview) {
    [$stored, $err] = import_store_upload($storageDir, $_FILES['file'] ?? null);
    if ($err) {
      $previewError = $err;
    } else {
      $_SESSION['import_last_file'] = $stored;
      $_SESSION['import_last_entity'] = $entity;
      $rows = import_preview_read($stored, 50, $entity);
      // normalize keys
      if ($rows && isset($rows[0])) {
        $norm = [];
        $k = normalize_headers(array_keys($rows[0]));
        foreach ($rows as $r) {
          $nr = [];
          $i=0;
          foreach ($r as $kk=>$vv) { $nr[$k[$i] ?? strtolower(trim((string)$kk))] = is_string($vv)?trim($vv):$vv; $i++; }
          $norm[] = $nr;
        }
        $rows = $norm;
      }
      $err2 = import_validate_columns($entity, $rows ?: []);
      if ($err2) {
        $previewError = $err2;
      } else {
        $preview = $rows;
      }
    }
  }

  if ($isImport) {
    $stored = $_SESSION['import_last_file'] ?? null;
    $entity = $_SESSION['import_last_entity'] ?? $entity;
    if (!$stored || !file_exists($stored)) {
      $previewError = 'لا يوجد ملف مستورد. قم برفع الملف أولاً.';
    } else {
      $err = import_insert_rows($entity, $stored, $importSummary);
      if ($err) {
        $previewError = $err;
      } else {
        $importDone = true;
        // keep the file for audit, but you can clean it manually
      }
    }
  }
}

// Page UI
$entityLabel = $labels[$entity] ?? 'البيانات';
?>

<div class="card">
  <div class="card-header">
    <h1>الاستيراد</h1>
    <div class="muted">استيراد دفعات كبيرة (CSV/XLSX) مع معاينة قبل الإدخال.</div>
  </div>

  <?php if ($previewError): ?>
    <div class="alert alert-danger"><?= e($previewError) ?></div>
  <?php endif; ?>

  <?php if ($importDone): ?>
    <div class="alert alert-success">تم تنفيذ الاستيراد بنجاح.</div>
  <?php endif; ?>

  <?php if ($importSummary): ?>
    <div class="alert alert-info">
      <b>ملخص:</b>
      إجمالي: <?= e($importSummary['total']) ?> |
      تم إدخال/تحديث: <?= e($importSummary['inserted']) ?> |
      تم تخطي: <?= e($importSummary['skipped']) ?>

      <?php if (!empty($importSummary['skipped_reasons'])): ?>
        <div style="margin-top:10px">
          <b>أسباب التخطي:</b>
          <div style="overflow:auto; max-width:100%">
            <table class="table" style="margin-top:8px">
              <thead><tr><th>السبب</th><th>العدد</th></tr></thead>
              <tbody>
                <?php foreach ($importSummary['skipped_reasons'] as $reason => $cnt): ?>
                  <tr><td><?= e($reason) ?></td><td><?= e($cnt) ?></td></tr>
                <?php endforeach; ?>
              </tbody>
            </table>
          </div>
        </div>
      <?php endif; ?>

      <?php if (!empty($importSummary['skipped_rows'])): ?>
        <div style="margin-top:10px">
          <b>عيّنة من السجلات المتخطّاة (أول <?= e(min(50, count($importSummary['skipped_rows']))) ?>):</b>
          <ul style="margin-top:8px">
            <?php foreach (array_slice($importSummary['skipped_rows'], 0, 50) as $sr): ?>
              <li>
                سطر <?= e($sr['line']) ?>: <?= e($sr['reason']) ?>
                <?php if (!empty($sr['details'])): ?>
                  — <?= e($sr['details']) ?>
                <?php endif; ?>
              </li>
            <?php endforeach; ?>
          </ul>
        </div>
      <?php endif; ?>

      <?php if (!empty($importSummary['errors'])): ?>
        <div style="margin-top:8px">
          <b>أخطاء (أول 20):</b>
          <ul>
            <?php foreach (array_slice($importSummary['errors'], 0, 20) as $er): ?>
              <li><?= e($er) ?></li>
            <?php endforeach; ?>
          </ul>
        </div>
      <?php endif; ?>
    </div>
  <?php endif; ?>

  <div class="tabs" style="margin-bottom:10px">
    <?php foreach ($labels as $k => $t): ?>
      <a class="tab <?= $k===$entity?'active':'' ?>" href="<?= e(url('/index.php?page=import&entity='.$k)) ?>"><?= e($t) ?></a>
    <?php endforeach; ?>
  </div>

  <div class="card" style="padding:14px">
    <h3 style="margin:0 0 10px">رفع ملف <?= e($entityLabel) ?></h3>
    <form method="post" enctype="multipart/form-data">
      <?= csrf_field() ?>
      <input type="hidden" name="op" value="preview">
      <input type="hidden" name="entity" value="<?= e($entity) ?>">
      <div class="grid" style="grid-template-columns: 1fr auto; gap:10px; align-items:end">
        <div>
          <label>ملف CSV أو XLSX</label>
          <input type="file" name="file" accept=".csv,.xlsx" required>
          <div class="muted" style="margin-top:6px">
            يفضّل وجود صف عناوين (Headers) باللغة الإنجليزية.
          </div>
        </div>
        <div>
          <button class="btn" name="do_preview" value="1">معاينة</button>
        </div>
      </div>
    </form>
  </div>

  <div class="card" style="padding:14px; margin-top:12px">
    <h3 style="margin:0 0 10px">قالب الأعمدة المطلوبة</h3>
    <?php $exp = import_expected_columns($entity); ?>
    <div class="muted" style="margin-bottom:8px">
      الأعمدة المطلوبة مميزة بـ <b>*</b>. يمكن ترك الأعمدة الاختيارية فارغة.
    </div>
    <div style="overflow:auto">
      <table class="table">
        <thead><tr><th>العمود</th><th>مطلوب؟</th></tr></thead>
        <tbody>
          <?php foreach ($exp as $col => $req): ?>
            <tr><td><?= e($col) ?><?= $req ? ' *' : '' ?></td><td><?= $req ? 'نعم' : 'اختياري' ?></td></tr>
          <?php endforeach; ?>
        </tbody>
      </table>
    </div>
  </div>

  <?php if ($preview): ?>
    <div class="card" style="padding:14px; margin-top:12px">
      <h3 style="margin:0 0 10px">معاينة (أول 50 صف)</h3>
      <div style="overflow:auto; max-height:420px">
        <table class="table">
          <thead>
            <tr>
              <?php foreach (array_keys($preview[0]) as $h): ?>
                <th><?= e($h) ?></th>
              <?php endforeach; ?>
            </tr>
          </thead>
          <tbody>
            <?php foreach ($preview as $r): ?>
              <tr>
                <?php foreach ($r as $v): ?>
                  <td><?= e(is_scalar($v) ? (string)$v : json_encode($v)) ?></td>
                <?php endforeach; ?>
              </tr>
            <?php endforeach; ?>
          </tbody>
        </table>
      </div>
      <form method="post" style="margin-top:10px">
        <?= csrf_field() ?>
  <input type="hidden" name="op" value="import">
        <input type="hidden" name="entity" value="<?= e($entity) ?>">
        <button class="btn btn-primary" name="do_import" value="1" onclick="return confirm('تأكيد الاستيراد؟ سيتم إدخال/تحديث البيانات.');">تأكيد الإدخال</button>
      </form>
    </div>
  <?php endif; ?>

</div>
