<?php
/* ============================================================
   منصة خبرة — بنية قاعدة البيانات
   جداول مطبّعة: كل كيان في جدوله، مع مفاتيح خارجية وحذف متسلسل
   ============================================================ */

declare(strict_types=1);

function exp_schema(): array {
  return [

  /* ---------- المسؤولون ---------- */
  "CREATE TABLE IF NOT EXISTS exp_users (
     id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
     username    VARCHAR(64)  NOT NULL,
     display     VARCHAR(120) NOT NULL DEFAULT '',
     pass_hash   VARCHAR(255) NOT NULL,
     is_active   TINYINT(1)   NOT NULL DEFAULT 1,
     last_login  DATETIME     NULL,
     created_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
     PRIMARY KEY (id),
     UNIQUE KEY uq_username (username)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

  /* ---------- محاولات الدخول ---------- */
  "CREATE TABLE IF NOT EXISTS exp_login_attempts (
     id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
     ip         VARCHAR(45)  NOT NULL,
     username   VARCHAR(64)  NOT NULL DEFAULT '',
     success    TINYINT(1)   NOT NULL DEFAULT 0,
     created_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
     PRIMARY KEY (id),
     KEY idx_ip_time (ip, created_at),
     KEY idx_attempt_time (created_at)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

  /* ---------- إعدادات المنصة (مفتاح/قيمة) ---------- */
  "CREATE TABLE IF NOT EXISTS exp_settings (
     k          VARCHAR(64) NOT NULL,
     v          TEXT        NOT NULL,
     updated_at TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
     PRIMARY KEY (k)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

  /* ---------- المسابقات ---------- */
  "CREATE TABLE IF NOT EXISTS exp_competitions (
     id          VARCHAR(64)  NOT NULL,
     title       VARCHAR(200) NOT NULL DEFAULT '',
     slogan      VARCHAR(300) NOT NULL DEFAULT '',
     label       VARCHAR(200) NOT NULL DEFAULT '',
     intro       TEXT         NULL,
     status      ENUM('draft','voting','results','archived') NOT NULL DEFAULT 'draft',
     accent      VARCHAR(9)   NOT NULL DEFAULT '#00AAA4',
     icon        VARCHAR(255) NOT NULL DEFAULT '',
     top_n       SMALLINT     NOT NULL DEFAULT 3,
     display_sec SMALLINT     NOT NULL DEFAULT 12,
     score_mode  ENUM('manual','computed') NOT NULL DEFAULT 'manual',
     reveal_at   VARCHAR(40)  NOT NULL DEFAULT '',
     teaser      VARCHAR(300) NOT NULL DEFAULT '',
     cover       VARCHAR(255) NOT NULL DEFAULT '',
     cover_text  VARCHAR(600) NOT NULL DEFAULT '',
     cover_sec   SMALLINT     NOT NULL DEFAULT 8,
     cover_fit   VARCHAR(10)  NOT NULL DEFAULT 'cover',
     cover_size  VARCHAR(6)   NOT NULL DEFAULT 'm',
     prize_note  VARCHAR(500) NOT NULL DEFAULT '',
     vote_json   TEXT         NULL,
     ui_json     TEXT         NULL,
     sort_order  SMALLINT     NOT NULL DEFAULT 0,
     updated_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
     PRIMARY KEY (id),
     KEY idx_comp_status (status, sort_order)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

  /* ---------- القطاعات ---------- */
  "CREATE TABLE IF NOT EXISTS exp_sectors (
     comp_id    VARCHAR(64)  NOT NULL,
     id         VARCHAR(64)  NOT NULL,
     name       VARCHAR(200) NOT NULL DEFAULT '',
     descr      VARCHAR(500) NOT NULL DEFAULT '',
     tags       VARCHAR(500) NOT NULL DEFAULT '',
     sort_order SMALLINT     NOT NULL DEFAULT 0,
     PRIMARY KEY (comp_id, id),
     CONSTRAINT fk_sector_comp FOREIGN KEY (comp_id)
       REFERENCES exp_competitions(id) ON DELETE CASCADE ON UPDATE CASCADE
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

  /* ---------- أنواع المسابقة ---------- */
  "CREATE TABLE IF NOT EXISTS exp_categories (
     comp_id    VARCHAR(64)  NOT NULL,
     id         VARCHAR(64)  NOT NULL,
     title      VARCHAR(200) NOT NULL DEFAULT '',
     color      VARCHAR(9)   NOT NULL DEFAULT '#00AAA4',
     sort_order SMALLINT     NOT NULL DEFAULT 0,
     PRIMARY KEY (comp_id, id),
     CONSTRAINT fk_cat_comp FOREIGN KEY (comp_id)
       REFERENCES exp_competitions(id) ON DELETE CASCADE ON UPDATE CASCADE
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

  /* ---------- مؤشرات التقييم ---------- */
  "CREATE TABLE IF NOT EXISTS exp_metrics (
     id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
     comp_id    VARCHAR(64)  NOT NULL,
     cat_id     VARCHAR(64)  NOT NULL,
     label      VARCHAR(200) NOT NULL DEFAULT '',
     weight     DECIMAL(6,4) NOT NULL DEFAULT 0,
     sort_order SMALLINT     NOT NULL DEFAULT 0,
     PRIMARY KEY (id),
     KEY idx_metric_cat (comp_id, cat_id),
     CONSTRAINT fk_metric_cat FOREIGN KEY (comp_id, cat_id)
       REFERENCES exp_categories(comp_id, id) ON DELETE CASCADE ON UPDATE CASCADE
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

  /* ---------- المرشحون ---------- */
  "CREATE TABLE IF NOT EXISTS exp_candidates (
     id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
     comp_id    VARCHAR(64)  NOT NULL,
     uid        VARCHAR(64)  NOT NULL,
     cat_id     VARCHAR(64)  NOT NULL DEFAULT '',
     sector_id  VARCHAR(64)  NOT NULL DEFAULT '',
     name       VARCHAR(160) NOT NULL DEFAULT '',
     project    VARCHAR(200) NOT NULL DEFAULT '',
     score      DECIMAL(6,4) NOT NULL DEFAULT 0,
     photo      VARCHAR(255) NOT NULL DEFAULT '',
     vote_url   VARCHAR(500) NOT NULL DEFAULT '',
     sort_order SMALLINT     NOT NULL DEFAULT 0,
     PRIMARY KEY (id),
     UNIQUE KEY uq_cand_uid (comp_id, uid),
     KEY idx_cand_cat  (comp_id, cat_id),
     KEY idx_cand_score (comp_id, cat_id, score),
     CONSTRAINT fk_cand_comp FOREIGN KEY (comp_id)
       REFERENCES exp_competitions(id) ON DELETE CASCADE ON UPDATE CASCADE
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

  /* ---------- تفصيل مؤشرات المرشح ---------- */
  "CREATE TABLE IF NOT EXISTS exp_candidate_details (
     id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
     candidate_id INT UNSIGNED NOT NULL,
     label        VARCHAR(200) NOT NULL DEFAULT '',
     value        DECIMAL(6,4) NOT NULL DEFAULT 0,
     sort_order   SMALLINT     NOT NULL DEFAULT 0,
     PRIMARY KEY (id),
     KEY idx_det_cand (candidate_id),
     CONSTRAINT fk_det_cand FOREIGN KEY (candidate_id)
       REFERENCES exp_candidates(id) ON DELETE CASCADE ON UPDATE CASCADE
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

  /* ---------- عناصر الجائزة ---------- */
  "CREATE TABLE IF NOT EXISTS exp_perks (
     id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
     comp_id    VARCHAR(64)  NOT NULL,
     icon       VARCHAR(255) NOT NULL DEFAULT '',
     text       VARCHAR(300) NOT NULL DEFAULT '',
     sort_order SMALLINT     NOT NULL DEFAULT 0,
     PRIMARY KEY (id),
     KEY idx_perk_comp (comp_id),
     CONSTRAINT fk_perk_comp FOREIGN KEY (comp_id)
       REFERENCES exp_competitions(id) ON DELETE CASCADE ON UPDATE CASCADE
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

  /* ---------- أصوات الموظفين ----------
     القيد الفريد يمنع تكرار التصويت لنفس الفئة من نفس الناخب
  --------------------------------- */
  "CREATE TABLE IF NOT EXISTS exp_votes (
     id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
     comp_id    VARCHAR(64)  NOT NULL,
     cat_id     VARCHAR(64)  NOT NULL,
     cand_uid   VARCHAR(64)  NOT NULL,
     voter_key  VARCHAR(120) NOT NULL,
     ip         VARCHAR(45)  NOT NULL DEFAULT '',
     created_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
     PRIMARY KEY (id),
     UNIQUE KEY uq_voter (comp_id, cat_id, voter_key),
     KEY idx_vote_cand (comp_id, cand_uid),
     KEY idx_vote_ip (ip, created_at),
     CONSTRAINT fk_vote_cand FOREIGN KEY (comp_id, cand_uid)
       REFERENCES exp_candidates(comp_id, uid) ON DELETE CASCADE ON UPDATE CASCADE
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

  /* ---------- سجل التعديلات ---------- */
  "CREATE TABLE IF NOT EXISTS exp_audit (
     id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
     user_id    INT UNSIGNED NULL,
     username   VARCHAR(64)  NOT NULL DEFAULT '',
     action     VARCHAR(64)  NOT NULL DEFAULT '',
     details    VARCHAR(500) NOT NULL DEFAULT '',
     ip         VARCHAR(45)  NOT NULL DEFAULT '',
     created_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
     PRIMARY KEY (id),
     KEY idx_audit_time (created_at)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
  ];
}

function exp_install_schema(PDO $db): void {
  foreach (exp_schema() as $sql) $db->exec($sql);
  exp_upgrade_schema($db);
}

/* ترقية قواعد بيانات أُنشئت بنسخة أقدم */
function exp_upgrade_schema(PDO $db): void {
  $adds = [
    'exp_competitions' => [
      'ui_json'    => "ALTER TABLE exp_competitions ADD COLUMN ui_json TEXT NULL AFTER vote_json",
      'score_mode' => "ALTER TABLE exp_competitions ADD COLUMN score_mode ENUM('manual','computed') NOT NULL DEFAULT 'manual' AFTER ui_json",
      'reveal_at'  => "ALTER TABLE exp_competitions ADD COLUMN reveal_at VARCHAR(40) NOT NULL DEFAULT '' AFTER score_mode",
      'teaser'     => "ALTER TABLE exp_competitions ADD COLUMN teaser VARCHAR(300) NOT NULL DEFAULT '' AFTER reveal_at",
      'cover'      => "ALTER TABLE exp_competitions ADD COLUMN cover VARCHAR(255) NOT NULL DEFAULT '' AFTER teaser",
      'cover_text' => "ALTER TABLE exp_competitions ADD COLUMN cover_text VARCHAR(600) NOT NULL DEFAULT '' AFTER cover",
      'cover_sec'  => "ALTER TABLE exp_competitions ADD COLUMN cover_sec SMALLINT NOT NULL DEFAULT 8 AFTER cover_text",
      'cover_fit'  => "ALTER TABLE exp_competitions ADD COLUMN cover_fit VARCHAR(10) NOT NULL DEFAULT 'cover' AFTER cover_sec",
      'cover_size' => "ALTER TABLE exp_competitions ADD COLUMN cover_size VARCHAR(6) NOT NULL DEFAULT 'm' AFTER cover_fit",
    ],
  ];
  /* توسيع أعمدة الأيقونات لتقبل مسار صورة — جُمل ثابتة بلا أي متغيّر */
  $widen = [
    "ALTER TABLE exp_competitions MODIFY COLUMN icon VARCHAR(255) NOT NULL DEFAULT ''",
    "ALTER TABLE exp_perks        MODIFY COLUMN icon VARCHAR(255) NOT NULL DEFAULT ''",
  ];
  foreach ($widen as $sql) {
    try { $db->exec($sql); }
    catch (Throwable $e) { error_log('widen icon: ' . $e->getMessage()); }
  }

  /* الأعمدة الناقصة — تُضاف فقط إن لم تكن موجودة */
  foreach ($adds as $table => $cols) {
    foreach ($cols as $col => $sql) {
      try {
        $st = $db->prepare("SELECT COUNT(*) c FROM information_schema.COLUMNS
                             WHERE TABLE_SCHEMA = DATABASE()
                               AND TABLE_NAME = ? AND COLUMN_NAME = ?");
        $st->execute([$table, $col]);
        if ((int)$st->fetch()['c'] === 0) $db->exec($sql);
      } catch (Throwable $e) {
        error_log("upgrade $table.$col: " . $e->getMessage());
      }
    }
  }

  /* ---------- الفهارس ----------
     idx_vote_ip      : يخدم حدّ الأصوات لكل جهاز في الساعة
     idx_comp_status  : يخدم ترتيب المسابقات المعروضة للجمهور
  --------------------------------- */
  $indexes = [
    ['exp_votes',        'idx_vote_ip',     "ALTER TABLE exp_votes ADD INDEX idx_vote_ip (ip, created_at)"],
    ['exp_competitions', 'idx_comp_status',  "ALTER TABLE exp_competitions ADD INDEX idx_comp_status (status, sort_order)"],
    ['exp_login_attempts', 'idx_attempt_time', "ALTER TABLE exp_login_attempts ADD INDEX idx_attempt_time (created_at)"],
  ];
  foreach ($indexes as [$table, $name, $sql]) {
    try {
      $st = $db->prepare("SELECT COUNT(*) c FROM information_schema.STATISTICS
                           WHERE TABLE_SCHEMA = DATABASE()
                             AND TABLE_NAME = ? AND INDEX_NAME = ?");
      $st->execute([$table, $name]);
      if ((int)$st->fetch()['c'] === 0) $db->exec($sql);
    } catch (Throwable $e) {
      error_log("index $table.$name: " . $e->getMessage());
    }
  }
}

function exp_audit(PDO $db, ?array $user, string $action, string $details = ''): void {
  try {
    $st = $db->prepare(
      "INSERT INTO exp_audit (user_id, username, action, details, ip) VALUES (?,?,?,?,?)"
    );
    $st->execute([
      $user['id'] ?? null,
      mb_substr((string)($user['user'] ?? ''), 0, 64),
      mb_substr($action, 0, 64),
      mb_substr($details, 0, 500),
      exp_client_ip(),
    ]);
  } catch (Throwable $e) {
    error_log('audit failed: ' . $e->getMessage());
  }
}
