<?php
require __DIR__ . '/../app/bootstrap.php';

auth_require();
if (!auth_is_admin()) {
  die('Forbidden');
}

$logs = [];
function ulog($s){ global $logs; $logs[]=$s; }

try {
  db();
  ulog('DB: OK');

  // Tables: inspectors
  db_exec("CREATE TABLE IF NOT EXISTS inspectors (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(32) NULL,
    name VARCHAR(200) NOT NULL,
    phone VARCHAR(40) NULL,
    email VARCHAR(200) NULL,
    emirates_id VARCHAR(15) NULL,
    notes TEXT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NULL,
    UNIQUE KEY uq_inspectors_code (code),
    UNIQUE KEY uq_inspectors_eid (emirates_id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
  ulog('inspectors: created/exists');

  db_exec("CREATE TABLE IF NOT EXISTS inspector_assignments (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    inspector_id INT UNSIGNED NOT NULL,
    entity_type VARCHAR(20) NOT NULL,
    entity_id INT UNSIGNED NOT NULL,
    role_title VARCHAR(120) NULL,
    notes TEXT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NULL,
    UNIQUE KEY uq_inspector_entity (inspector_id, entity_type, entity_id),
    INDEX idx_inspector_id (inspector_id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
  ulog('inspector_assignments: created/exists');

  db_exec("CREATE TABLE IF NOT EXISTS inspector_tasks (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    inspector_id INT UNSIGNED NOT NULL,
    entity_type VARCHAR(20) NOT NULL,
    entity_id INT UNSIGNED NOT NULL,
    task_title VARCHAR(200) NOT NULL,
    task_desc TEXT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'open',
    due_date DATE NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NULL,
    INDEX idx_itask_inspector (inspector_id),
    INDEX idx_itask_status (status)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
  ulog('inspector_tasks: created/exists');

  // Permissions
  $perms = [
    ['manage_db_tools','الأمان','إدارة نسخ/استعادة قاعدة البيانات'],
    ['view_inspectors','المراقبون','عرض المراقبين'],
    ['manage_inspectors','المراقبون','إدارة المراقبين'],
    ['view_inspector_tasks','المراقبون','عرض مهام المراقبين'],
    ['manage_inspector_tasks','المراقبون','إدارة مهام المراقبين'],
  ];
  foreach ($perms as $p) {
    db_exec("INSERT IGNORE INTO permissions (perm_key, perm_group, label, description, created_at)
             VALUES (?,?,?,?,?)", [$p[0], $p[1], $p[2], null, now_dt()]);
  }
  ulog('permissions: inserted/exists');

  // Grant new perms to roles that already can manage permissions (plus admin)
  $roles = ['admin'];
  $rp = db_all("SELECT DISTINCT role FROM role_permissions WHERE perm_key='manage_permissions' AND allowed=1");
  foreach ($rp as $r) {
    $role = (string)($r['role'] ?? '');
    if ($role !== '' && !in_array($role, $roles, true)) $roles[] = $role;
  }
  foreach ($roles as $role) {
    foreach ($perms as $p) {
      db_exec("INSERT IGNORE INTO role_permissions (role, perm_key, allowed, created_at, updated_at)
               VALUES (?,?,?,?,?)", [$role, $p[0], 1, now_dt(), null]);
    }
  }
  ulog('role_permissions: granted');

  ulog('DONE');
} catch (Throwable $e) {
  ulog('ERROR: ' . $e->getMessage());
}

?><!doctype html>
<html lang="ar" dir="rtl">
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <title>ترقية v0.3.8.0</title>
  <style>
    body{font-family:Arial,Helvetica,sans-serif;background:#f7f7f7;margin:0;padding:24px;}
    .card{max-width:900px;margin:0 auto;background:#fff;border:1px solid #eee;border-radius:12px;padding:18px;}
    pre{background:#111;color:#0f0;padding:12px;border-radius:10px;overflow:auto}
    a{color:#0066cc}
  </style>
</head>
<body>
  <div class="card">
    <h2 style="margin-top:0">ترقية قاعدة البيانات إلى v0.3.8.0</h2>
    <p>ينشئ جداول <b>inspectors</b> و <b>inspector_assignments</b> و <b>inspector_tasks</b> ويضيف الصلاحيات المطلوبة.</p>
    <pre><?php echo htmlspecialchars(implode("\n", $logs), ENT_QUOTES, 'UTF-8'); ?></pre>
    <p><a href="<?php echo e(url('/index.php?page=dashboard')); ?>">العودة إلى لوحة التحكم</a></p>
  </div>
</body>
</html>
