<?php
/**
 * Oasis Demo Seeder (v6)
 * Fix: auto-generate values for ANY single-column UNIQUE fields (e.g. code, tag, slug)
 * when missing/empty, to avoid "Duplicate entry '' for key ...".
 *
 * Put in: public/seed_demo.php
 * Run:    /seed_demo.php?run=1
 * Delete file after success.
 */

header('Content-Type: text/html; charset=utf-8');

function h($s){ return htmlspecialchars((string)$s, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8'); }

function read_env_file($path){
    if (!$path || !file_exists($path) || !is_readable($path)) return [[], "not_readable"];
    $lines = file($path, FILE_IGNORE_NEW_LINES);
    if ($lines === false) return [[], "read_failed"];

    $env = [];
    foreach ($lines as $line){
        $line = trim($line);
        if ($line === '' || str_starts_with($line, '#')) continue;
        if (str_starts_with($line, 'export ')) $line = trim(substr($line, 7));
        $pos = strpos($line, '=');
        if ($pos === false) continue;

        $k = trim(substr($line, 0, $pos));
        $v = trim(substr($line, $pos + 1));

        if ((str_starts_with($v, '"') && str_ends_with($v, '"')) || (str_starts_with($v, "'") && str_ends_with($v, "'"))){
            $v = substr($v, 1, -1);
        }
        $k = preg_replace('/^\xEF\xBB\xBF/', '', $k);
        $env[$k] = $v;
    }
    return [$env, "ok"];
}

function find_env_path(){
    $candidates = [
        __DIR__ . '/../.env',
        __DIR__ . '/../../.env',
        dirname(__DIR__) . '/.env',
        dirname(__DIR__, 2) . '/.env',
        getcwd() . '/.env',
    ];
    foreach ($candidates as $p){
        if (file_exists($p)) return realpath($p);
    }
    return null;
}

$envPath = find_env_path();
[$env, $envStatus] = read_env_file($envPath);

echo "<h2>Oasis Demo Seeder (v6)</h2>";
echo "<div style='padding:10px;border:1px solid #ddd;border-radius:8px;margin-bottom:12px'>";
echo "<div><b>Script:</b> <code>" . h(__FILE__) . "</code></div>";
echo "<div><b>.env detected:</b> <code>" . h($envPath ?: 'NOT FOUND') . "</code></div>";
echo "<div><b>.env status:</b> <code>" . h($envStatus) . "</code></div>";
echo "</div>";

if (!$envPath){ echo "<h3 style='color:#b00'>لم أجد ملف .env</h3>"; exit; }
if ($envStatus !== "ok"){ echo "<h3 style='color:#b00'>لا يمكن قراءة ملف .env</h3>"; exit; }

$dbHost = $env['DB_HOST'] ?? getenv('DB_HOST') ?: '127.0.0.1';
$dbPort = $env['DB_PORT'] ?? getenv('DB_PORT') ?: '3306';

$dbName = $env['DB_DATABASE'] ?? $env['DB_NAME'] ?? getenv('DB_DATABASE') ?: (getenv('DB_NAME') ?: '');
$dbUser = $env['DB_USERNAME'] ?? $env['DB_USER'] ?? getenv('DB_USERNAME') ?: (getenv('DB_USER') ?: '');
$dbPass = $env['DB_PASSWORD'] ?? $env['DB_PASS'] ?? getenv('DB_PASSWORD') ?: (getenv('DB_PASS') ?: '');

echo "<div style='padding:10px;border:1px solid #eee;border-radius:8px;margin-bottom:12px;background:#fafafa'>";
echo "<div><b>DB_HOST:</b> <code>" . h($dbHost) . "</code></div>";
echo "<div><b>DB_PORT:</b> <code>" . h($dbPort) . "</code></div>";
echo "<div><b>DB_DATABASE / DB_NAME:</b> <code>" . h($dbName ?: '[EMPTY]') . "</code></div>";
echo "<div><b>DB_USERNAME / DB_USER:</b> <code>" . h($dbUser ?: '[EMPTY]') . "</code></div>";
echo "<div><b>DB_PASSWORD / DB_PASS:</b> <code>" . h($dbPass === '' ? '[EMPTY]' : str_repeat('•', min(12, strlen($dbPass)))) . "</code></div>";
echo "</div>";

if ($dbName === '' || $dbUser === ''){
    echo "<h3 style='color:#b00'>خطأ: متغيرات قاعدة البيانات ناقصة داخل .env</h3>";
    exit;
}

if (!isset($_GET['run']) || $_GET['run'] !== '1'){
    echo "<h3>للتنفيذ افتح:</h3><pre>?run=1</pre><p><b>مهم:</b> بعد النجاح احذف الملف.</p>";
    exit;
}

try{
    $dsn = "mysql:host={$dbHost};port={$dbPort};dbname={$dbName};charset=utf8mb4";
    $pdo = new PDO($dsn, $dbUser, $dbPass, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);
} catch (Throwable $e){
    echo "<h3 style='color:#b00'>فشل الاتصال بقاعدة البيانات</h3><pre>" . h($e->getMessage()) . "</pre>";
    exit;
}

function table_exists(PDO $pdo, $table){
    $stmt = $pdo->prepare("SELECT COUNT(*) c FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = ?");
    $stmt->execute([$table]);
    return (int)$stmt->fetch()['c'] > 0;
}

function get_columns(PDO $pdo, $table){
    $stmt = $pdo->prepare("SELECT column_name FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = ?");
    $stmt->execute([$table]);
    $cols = [];
    foreach ($stmt->fetchAll() as $r) $cols[$r['column_name']] = true;
    return $cols;
}

/**
 * Return single-column UNIQUE fields (excluding PRIMARY).
 * This catches cases like UNIQUE(code) or UNIQUE(tag).
 */
function get_single_unique_columns(PDO $pdo, $table){
    $sql = "
        SELECT s.index_name, COUNT(*) AS col_count
        FROM information_schema.statistics s
        WHERE s.table_schema = DATABASE()
          AND s.table_name = ?
          AND s.non_unique = 0
          AND s.index_name <> 'PRIMARY'
        GROUP BY s.index_name
        HAVING COUNT(*) = 1
    ";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$table]);
    $idx = $stmt->fetchAll();

    $uniqueCols = [];
    foreach ($idx as $row){
        $indexName = $row['index_name'];
        $stmt2 = $pdo->prepare("
            SELECT column_name
            FROM information_schema.statistics
            WHERE table_schema = DATABASE()
              AND table_name = ?
              AND index_name = ?
              AND non_unique = 0
            LIMIT 1
        ");
        $stmt2->execute([$table, $indexName]);
        $col = $stmt2->fetchColumn();
        if ($col) $uniqueCols[$col] = true;
    }
    return $uniqueCols;
}

function delete_all(PDO $pdo, $table){
    if (!table_exists($pdo, $table)) return;
    $pdo->exec("DELETE FROM `{$table}`");
    try{ $pdo->exec("ALTER TABLE `{$table}` AUTO_INCREMENT=1"); }catch(Throwable $e){}
}

function make_unique_value($table, $col, $id){
    $t = strtoupper(preg_replace('/[^A-Z0-9]+/', '-', $table));
    $c = strtoupper(preg_replace('/[^A-Z0-9]+/', '-', $col));
    $idPart = $id !== null ? (string)$id : uniqid();
    // extra entropy to be safe even if same id is reused
    $rand = substr(bin2hex(random_bytes(3)), 0, 6);
    return "DEMO-{$t}-{$c}-{$idPart}-{$rand}";
}

function insert_row(PDO $pdo, $table, $row, &$cacheCols, &$cacheUnique, &$stats){
    if (!table_exists($pdo, $table)){
        $stats['skipped'][$table] = true;
        return;
    }
    if (!isset($cacheCols[$table])) $cacheCols[$table] = get_columns($pdo, $table);
    if (!isset($cacheUnique[$table])) $cacheUnique[$table] = get_single_unique_columns($pdo, $table);

    $cols = $cacheCols[$table];
    $u = $cacheUnique[$table];

    $final = [];
    foreach ($row as $k => $v){
        if (isset($cols[$k])) $final[$k] = $v;
    }

    $idForUnique = $final['id'] ?? ($row['id'] ?? null);

    // Ensure any single-column UNIQUE fields are non-empty
    foreach ($u as $ucol => $_){
        if (!isset($cols[$ucol])) continue;
        if (!isset($final[$ucol]) || $final[$ucol] === null || $final[$ucol] === ''){
            $final[$ucol] = make_unique_value($table, $ucol, $idForUnique);
        }
    }

    if (count($final) === 0){
        $stats['empty'][$table] = true;
        return;
    }

    $fields = array_keys($final);
    $placeholders = implode(',', array_fill(0, count($fields), '?'));
    $sql = "INSERT INTO `{$table}` (`" . implode('`,`', $fields) . "`) VALUES ({$placeholders})";
    $stmt = $pdo->prepare($sql);
    $stmt->execute(array_values($final));
    $stats['inserted'][$table] = ($stats['inserted'][$table] ?? 0) + 1;
}

$tables = ['maintenance_logs','maintenance_requests','contract_services','services','contracts','contractors','assets','farms','aflaj','oases'];
$stats = ['inserted'=>[], 'skipped'=>[], 'empty'=>[]];
$cacheCols = [];
$cacheUnique = [];

$now = "2025-12-26 22:32:25";

echo "<p>Server: <code>" . h($pdo->query('SELECT VERSION() v')->fetch()['v']) . "</code></p>";

try{
    $pdo->exec("SET FOREIGN_KEY_CHECKS=0");
    foreach ($tables as $t) delete_all($pdo, $t);
    $pdo->exec("SET FOREIGN_KEY_CHECKS=1");

    $pdo->beginTransaction();

    $oases = [
      ['id'=>1,'name'=>'واحة العين','code'=>'OAS-AIN','latitude'=>24.2190,'longitude'=>55.7600,'address'=>'العين – وسط المدينة','notes'=>'بيانات تجريبية لعرض الخريطة والواجهات.','created_at'=>$now,'updated_at'=>$now],
      ['id'=>2,'name'=>'واحة الجيمي','code'=>'OAS-JIMI','latitude'=>24.2485,'longitude'=>55.7420,'address'=>'العين – الجيمي','notes'=>'تتضمن مزارع وأصول وأوامر صيانة متنوعة.','created_at'=>$now,'updated_at'=>$now],
      ['id'=>3,'name'=>'واحة الهيلي','code'=>'OAS-HELI','latitude'=>24.2855,'longitude'=>55.7865,'address'=>'العين – الهيلي','notes'=>'بيانات تجريبية للتقارير والتصدير.','created_at'=>$now,'updated_at'=>$now],
    ];

    $aflaj = [
      ['id'=>1,'oasis_id'=>1,'name'=>'فلج العين (تجريبي)','falaj_type'=>'داوودي','length_m'=>1850,'water_source'=>'عين مائية','latitude'=>24.2198,'longitude'=>55.7593,'notes'=>'مسار تجريبي.','created_at'=>$now,'updated_at'=>$now],
      ['id'=>2,'oasis_id'=>2,'name'=>'فلج الجيمي (تجريبي)','falaj_type'=>'عيني','length_m'=>1320,'water_source'=>'بئر/ضخ','latitude'=>24.2492,'longitude'=>55.7408,'notes'=>'يتضمن نقاط صيانة دورية.','created_at'=>$now,'updated_at'=>$now],
      ['id'=>3,'oasis_id'=>3,'name'=>'فلج الهيلي (تجريبي)','falaj_type'=>'داوودي','length_m'=>2100,'water_source'=>'قناة رئيسية','latitude'=>24.2860,'longitude'=>55.7858,'notes'=>'للاختبار على الخريطة.','created_at'=>$now,'updated_at'=>$now],
    ];

    $farms = [
      ['id'=>1,'oasis_id'=>1,'name'=>'مزرعة النخيل 01','code'=>'F-AIN-01','latitude'=>24.2186,'longitude'=>55.7611,'area_sqm'=>6200,'owner_name'=>'بلدية العين','notes'=>'بيانات تجريبية.','created_at'=>$now,'updated_at'=>$now],
      ['id'=>2,'oasis_id'=>1,'name'=>'مزرعة النخيل 02','code'=>'F-AIN-02','latitude'=>24.2201,'longitude'=>55.7582,'area_sqm'=>5400,'owner_name'=>'بلدية العين','notes'=>'مرتبطة بأصل ضخ وفلتر.','created_at'=>$now,'updated_at'=>$now],
      ['id'=>3,'oasis_id'=>2,'name'=>'مزرعة الجيمي 01','code'=>'F-JIMI-01','latitude'=>24.2478,'longitude'=>55.7429,'area_sqm'=>7100,'owner_name'=>'بلدية العين','notes'=>'لاختبار تعدد الأصول.','created_at'=>$now,'updated_at'=>$now],
    ];

    $assets = [
      ['id'=>1,'farm_id'=>1,'name'=>'مضخة ري رئيسية','asset_type'=>'Pump','serial_no'=>'PMP-AIN-0001','status'=>'Active','latitude'=>24.2182,'longitude'=>55.7616,'install_date'=>'2024-05-20','notes'=>'تشغيل طبيعي.','created_at'=>$now,'updated_at'=>$now],
      ['id'=>2,'farm_id'=>1,'name'=>'صمام تحكم 2 إنش','asset_type'=>'Valve','serial_no'=>'VLV-AIN-1020','status'=>'Active','latitude'=>24.2189,'longitude'=>55.7607,'install_date'=>'2023-11-02','notes'=>'فحص تسريب.','created_at'=>$now,'updated_at'=>$now],
    ];

    $contractors = [
      ['id'=>1,'name'=>'مقاولات الواحات الحديثة','phone'=>'+971500000111','email'=>'demo1@contractor.test','trade_license'=>'TL-ALAIN-0001','address'=>'العين – المنطقة الصناعية','notes'=>'مقاول تجريبي.','created_at'=>$now,'updated_at'=>$now],
    ];

    $contracts = [
      ['id'=>1,'contractor_id'=>1,'title'=>'عقد صيانة دورية – واحة العين','contract_no'=>'CNT-2025-0001','start_date'=>'2025-01-01','end_date'=>'2025-12-31','value_aed'=>180000,'status'=>'Active','notes'=>'عقد تجريبي.','created_at'=>$now,'updated_at'=>$now],
    ];

    $maintenance_requests = [
      ['id'=>1,'contract_id'=>1,'oasis_id'=>1,'farm_id'=>1,'asset_id'=>1,'falaj_id'=>1,'request_no'=>'MR-0001','order_no'=>'MR-0001','title'=>'فحص مضخة الري الرئيسية + قياس ضغط','priority'=>'High','status'=>'Open','scheduled_date'=>'2025-12-28','notes'=>'أولوية عالية بسبب تذبذب الضغط.','created_at'=>$now,'updated_at'=>$now],
    ];

    $maintenance_logs = [
      ['id'=>1,'maintenance_request_id'=>1,'log_date'=>'2025-12-27 10:15:00','status'=>'InProgress','note'=>'تمت معاينة الموقع وبدء الفحص.','created_at'=>$now,'updated_at'=>$now],
    ];

    foreach ($oases as $r) insert_row($pdo,'oases',$r,$cacheCols,$cacheUnique,$stats);
    foreach ($aflaj as $r) insert_row($pdo,'aflaj',$r,$cacheCols,$cacheUnique,$stats);
    foreach ($farms as $r) insert_row($pdo,'farms',$r,$cacheCols,$cacheUnique,$stats);
    foreach ($assets as $r) insert_row($pdo,'assets',$r,$cacheCols,$cacheUnique,$stats);
    foreach ($contractors as $r) insert_row($pdo,'contractors',$r,$cacheCols,$cacheUnique,$stats);
    foreach ($contracts as $r) insert_row($pdo,'contracts',$r,$cacheCols,$cacheUnique,$stats);
    foreach ($maintenance_requests as $r) insert_row($pdo,'maintenance_requests',$r,$cacheCols,$cacheUnique,$stats);
    foreach ($maintenance_logs as $r) insert_row($pdo,'maintenance_logs',$r,$cacheCols,$cacheUnique,$stats);

    $pdo->commit();

} catch(Throwable $e){
    if ($pdo->inTransaction()) $pdo->rollBack();
    echo "<h3 style='color:#b00'>فشل إدخال بيانات الديمو</h3><pre>" . h($e->getMessage()) . "</pre>";
    echo "<p>تلميح: هذا الإصدار يولّد تلقائياً قيم UNIQUE مثل code/tag إذا كانت فارغة.</p>";
    exit;
}

echo "<h3 style='color:#0a0'>تم إدخال بيانات الديمو بنجاح ✅</h3>";
echo "<h4>ملخص الإدخال</h4><ul>";
foreach ($stats['inserted'] as $t=>$c) echo "<li><b>" . h($t) . ":</b> " . h($c) . "</li>";
echo "</ul>";
if (!empty($stats['skipped'])){
    echo "<h4>جداول غير موجودة (تم تجاوزها)</h4><ul>";
    foreach (array_keys($stats['skipped']) as $t) echo "<li>" . h($t) . "</li>";
    echo "</ul>";
}
if (!empty($stats['empty'])){
    echo "<h4>جداول موجودة لكن لا يوجد تطابق أعمدة (تم تجاوز الإدخال)</h4><ul>";
    foreach (array_keys($stats['empty']) as $t) echo "<li>" . h($t) . "</li>";
    echo "</ul>";
}
echo "<hr><p><b>مهم:</b> احذف الملف <code>seed_demo.php</code> الآن.</p>";
