<?php
/* ============================================================
   منصة خبرة — قراءة وكتابة المنصة من قاعدة البيانات
   ============================================================ */

declare(strict_types=1);

/* ============================================================
   القراءة — تُبنى نفس بنية JSON التي تفهمها الواجهة
   ============================================================ */
function exp_load_platform(PDO $db): array {

  /* الإعدادات */
  $out = [
    'version'  => 2,
    'org'      => ['name'=>'خبرة الإعلانية','platform'=>'منصة خبرة',
                   'en'=>'KHIBRAH','tagline'=>''],
    'live'     => ['enabled'=>true,'intervalSeconds'=>8,'showDeltas'=>true],
    'activeId' => '',
    'competitions' => [],
  ];

  foreach ($db->query("SELECT k, v FROM exp_settings") as $r) {
    $val = json_decode((string)$r['v'], true);
    if ($r['k'] === 'org'  && is_array($val)) $out['org']  = $val + $out['org'];
    if ($r['k'] === 'live' && is_array($val)) $out['live'] = $val + $out['live'];
    if ($r['k'] === 'activeId') $out['activeId'] = (string)($val ?? '');
  }

  /* المسابقات */
  $comps = [];
  $st = $db->query("SELECT * FROM exp_competitions ORDER BY sort_order, id");
  foreach ($st as $c) {
    $vote = json_decode((string)($c['vote_json'] ?? ''), true);
    $comps[$c['id']] = [
      'id'      => $c['id'],
      'title'   => $c['title'],
      'slogan'  => $c['slogan'],
      'label'   => $c['label'],
      'intro'   => (string)($c['intro'] ?? ''),
      'status'  => $c['status'],
      'accent'  => $c['accent'],
      'icon'    => $c['icon'],
      'topN'    => (int)$c['top_n'],
      'displaySeconds' => (int)$c['display_sec'],
      'scoreMode' => (($c['score_mode'] ?? 'manual') === 'computed') ? 'computed' : 'manual',
      'revealAt'  => (string)($c['reveal_at'] ?? ''),
      'teaser'    => (string)($c['teaser'] ?? ''),
      'cover'      => (string)($c['cover'] ?? ''),
      'coverText'  => (string)($c['cover_text'] ?? ''),
      'coverSeconds' => (int)($c['cover_sec'] ?? 8),
      'coverFit'  => (string)($c['cover_fit'] ?? 'cover'),
      'coverSize' => (string)($c['cover_size'] ?? 'm'),
      'sectors'    => new stdClass(),
      'sectorInfo' => new stdClass(),
      'categories' => [],
      'winners'    => [],
      'prize'   => ['note' => (string)$c['prize_note'], 'perks' => []],
      'ui'      => (function($j){ $u = json_decode((string)$j, true); return is_array($u) ? $u : null; })($c['ui_json'] ?? ''),
      'vote'    => is_array($vote) ? $vote : [
        'enabled'=>false,'system'=>'platform','mode'=>'single','url'=>'',
        'label'=>'صوّت له','note'=>'','deadline'=>'',
        'requireId'=>true,'showCounts'=>true,'allowList'=>[],'domain'=>''
      ],
    ];
  }
  if (!$comps) { $out['competitions'] = []; return $out; }

  /* القطاعات */
  $sectors = []; $sectorInfo = [];
  foreach ($db->query("SELECT * FROM exp_sectors ORDER BY comp_id, sort_order") as $s) {
    $sectors[$s['comp_id']][$s['id']] = $s['name'];
    $tags = $s['tags'] !== '' ? array_values(array_filter(array_map('trim', explode('|', (string)$s['tags'])))) : [];
    $sectorInfo[$s['comp_id']][$s['id']] = ['desc' => $s['descr'], 'tags' => $tags];
  }

  /* الفئات ومؤشراتها */
  $cats = [];
  foreach ($db->query("SELECT * FROM exp_categories ORDER BY comp_id, sort_order") as $c) {
    $cats[$c['comp_id']][$c['id']] = [
      'id' => $c['id'], 'title' => $c['title'], 'color' => $c['color'], 'metrics' => []
    ];
  }
  foreach ($db->query("SELECT * FROM exp_metrics ORDER BY comp_id, cat_id, sort_order") as $m) {
    if (isset($cats[$m['comp_id']][$m['cat_id']])) {
      $cats[$m['comp_id']][$m['cat_id']]['metrics'][] = [
        'k' => $m['label'], 'w' => (float)$m['weight']
      ];
    }
  }

  /* المرشحون وتفاصيلهم */
  $details = [];
  foreach ($db->query("SELECT * FROM exp_candidate_details ORDER BY candidate_id, sort_order") as $d) {
    $details[(int)$d['candidate_id']][] = ['k' => $d['label'], 'v' => (float)$d['value']];
  }
  /* عدد أصوات كل مرشح */
  $tally = [];
  try {
    foreach ($db->query("SELECT comp_id, cand_uid, COUNT(*) n FROM exp_votes
                          GROUP BY comp_id, cand_uid") as $v) {
      $tally[$v['comp_id'] . '|' . $v['cand_uid']] = (int)$v['n'];
    }
  } catch (Throwable $e) { /* الجدول غير موجود في نسخة قديمة */ }

  $winners = [];
  foreach ($db->query("SELECT * FROM exp_candidates ORDER BY comp_id, sort_order, id") as $w) {
    $row = [
      'uid'     => $w['uid'],
      'cat'     => $w['cat_id'],
      'name'    => $w['name'],
      'project' => $w['project'],
      'sector'  => $w['sector_id'],
      'score'   => (float)$w['score'],
      'votes'   => $tally[$w['comp_id'] . '|' . $w['uid']] ?? 0,
    ];
    if ($w['photo']    !== '') $row['photo']   = $w['photo'];
    if ($w['vote_url'] !== '') $row['voteUrl'] = $w['vote_url'];
    if (!empty($details[(int)$w['id']])) $row['details'] = $details[(int)$w['id']];
    $winners[$w['comp_id']][] = $row;
  }

  /* عناصر الجائزة */
  $perks = [];
  foreach ($db->query("SELECT * FROM exp_perks ORDER BY comp_id, sort_order") as $p) {
    $perks[$p['comp_id']][] = ['ic' => $p['icon'], 't' => $p['text']];
  }

  /* التجميع */
  foreach ($comps as $id => &$c) {
    $c['sectors']    = (object)($sectors[$id] ?? []);
    $c['sectorInfo'] = (object)($sectorInfo[$id] ?? []);
    $c['categories'] = array_values($cats[$id] ?? []);
    $c['winners']    = $winners[$id] ?? [];
    $c['prize']['perks'] = $perks[$id] ?? [];
  }
  unset($c);

  $out['competitions'] = array_values($comps);
  if ($out['activeId'] === '' || !isset($comps[$out['activeId']])) {
    $out['activeId'] = (string)array_key_first($comps);
  }
  return $out;
}

/* ============================================================
   الكتابة — استبدال كامل داخل معاملة واحدة
   إمّا أن تنجح كل التعديلات أو لا يتغيّر شيء
   ============================================================ */
function exp_save_platform(PDO $db, array $d): array {

  $comps = is_array($d['competitions'] ?? null) ? $d['competitions'] : [];
  if (count($comps) > 200) throw new RuntimeException('عدد المسابقات كبير جدًا');

  $db->beginTransaction();
  try {
    /* --- الإعدادات --- */
    $set = $db->prepare("INSERT INTO exp_settings (k,v) VALUES (?,?)
                         ON DUPLICATE KEY UPDATE v = VALUES(v)");

    $org = is_array($d['org'] ?? null) ? $d['org'] : [];
    $set->execute(['org', json_encode([
      'name'     => exp_str($org['name']     ?? '', 120),
      'platform' => exp_str($org['platform'] ?? '', 120),
      'en'       => exp_str($org['en']       ?? '', 120),
      'tagline'  => exp_str($org['tagline']  ?? '', 300),
    ], JSON_UNESCAPED_UNICODE)]);

    $live = is_array($d['live'] ?? null) ? $d['live'] : [];
    $set->execute(['live', json_encode([
      'enabled'         => !empty($live['enabled']),
      'intervalSeconds' => max(5, min(3600, (int)($live['intervalSeconds'] ?? 8))),
      'showDeltas'      => !empty($live['showDeltas']),
    ], JSON_UNESCAPED_UNICODE)]);

    $set->execute(['activeId', json_encode(exp_id($d['activeId'] ?? ''), JSON_UNESCAPED_UNICODE)]);

    /* --- حذف المسابقات التي أُزيلت --- */
    $keep = [];
    foreach ($comps as $c) { $id = exp_id($c['id'] ?? ''); if ($id !== '') $keep[] = $id; }

    if ($keep) {
      $in = implode(',', array_fill(0, count($keep), '?'));
      $db->prepare("DELETE FROM exp_competitions WHERE id NOT IN ($in)")->execute($keep);
    } else {
      $db->exec("DELETE FROM exp_competitions");
    }

    /* --- إدراج/تحديث كل مسابقة --- */
    $upComp = $db->prepare(
      "INSERT INTO exp_competitions
         (id,title,slogan,label,intro,status,accent,icon,top_n,display_sec,score_mode,
          reveal_at,teaser,cover,cover_text,cover_sec,cover_fit,cover_size,prize_note,vote_json,ui_json,sort_order)
       VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
       ON DUPLICATE KEY UPDATE
         title=VALUES(title), slogan=VALUES(slogan), label=VALUES(label), intro=VALUES(intro),
         status=VALUES(status), accent=VALUES(accent), icon=VALUES(icon), top_n=VALUES(top_n),
         display_sec=VALUES(display_sec), score_mode=VALUES(score_mode),
         reveal_at=VALUES(reveal_at), teaser=VALUES(teaser),
         cover=VALUES(cover), cover_text=VALUES(cover_text), cover_sec=VALUES(cover_sec),
         cover_fit=VALUES(cover_fit), cover_size=VALUES(cover_size),
         prize_note=VALUES(prize_note),
         vote_json=VALUES(vote_json), ui_json=VALUES(ui_json), sort_order=VALUES(sort_order)"
    );

    $statuses = ['draft','voting','results','archived'];
    $order = 0;

    foreach ($comps as $c) {
      $cid = exp_id($c['id'] ?? '');
      if ($cid === '') continue;

      $vote = is_array($c['vote'] ?? null) ? $c['vote'] : [];

      /* قائمة البريد المسموح لها بالتصويت */
      $allow = [];
      if (is_array($vote['allowList'] ?? null)) {
        foreach (array_slice($vote['allowList'], 0, 5000) as $a) {
          $a = mb_strtolower(exp_str($a, 120));
          if ($a !== '') $allow[] = $a;
        }
        $allow = array_values(array_unique($allow));
      }
      /* نطاق البريد المؤسسي — بلا @ ولا مسافات */
      $vdomain = mb_strtolower(exp_str($vote['domain'] ?? '', 80));
      $vdomain = ltrim(trim($vdomain), '@');

      $voteJson = json_encode([
        'enabled'    => !empty($vote['enabled']),
        'system'     => (($vote['system'] ?? 'platform') === 'external') ? 'external' : 'platform',
        'mode'       => (($vote['mode'] ?? 'single') === 'per') ? 'per' : 'single',
        'url'        => exp_url($vote['url'] ?? ''),
        'label'      => exp_str($vote['label'] ?? 'صوّت له', 60),
        'note'       => exp_str($vote['note'] ?? '', 300),
        'deadline'   => exp_str($vote['deadline'] ?? '', 40),
        'requireId'  => !array_key_exists('requireId', $vote) || !empty($vote['requireId']),
        'showCounts' => !array_key_exists('showCounts', $vote) || !empty($vote['showCounts']),
        'allowList'  => $allow,
        'domain'     => $vdomain,
      ], JSON_UNESCAPED_UNICODE);

      $status    = in_array($c['status'] ?? '', $statuses, true) ? $c['status'] : 'draft';
      $coverFit  = (($c['coverFit'] ?? 'cover') === 'contain') ? 'contain' : 'cover';
      $coverSize = in_array($c['coverSize'] ?? 'm', ['s','m','l','full'], true)
                 ? (string)$c['coverSize'] : 'm';

      $upComp->execute([
        $cid,
        exp_str($c['title'] ?? '', 200),
        exp_str($c['slogan'] ?? '', 300),
        exp_str($c['label'] ?? '', 200),
        exp_str($c['intro'] ?? '', 4000),
        $status,
        exp_color($c['accent'] ?? ''),
        exp_icon($c['icon'] ?? ''),
        max(1, min(999, (int)($c['topN'] ?? 3))),
        max(4, min(600, (int)($c['displaySeconds'] ?? 12))),
        (($c['scoreMode'] ?? 'manual') === 'computed') ? 'computed' : 'manual',
        exp_str($c['revealAt'] ?? '', 40),
        exp_str($c['teaser'] ?? '', 300),
        exp_photo($c['cover'] ?? ''),
        exp_str($c['coverText'] ?? '', 600),
        max(3, min(120, (int)($c['coverSeconds'] ?? 8))),
        $coverFit,
        $coverSize,
        exp_str(($c['prize']['note'] ?? ''), 500),
        $voteJson,
        json_encode(is_array($c['ui'] ?? null) ? $c['ui'] : new stdClass(), JSON_UNESCAPED_UNICODE),
        $order++,
      ]);

      /* الأبناء عديمة الحالة: حذف ثم إدراج */
      foreach (['exp_sectors','exp_categories','exp_perks'] as $t) {
        $db->prepare("DELETE FROM $t WHERE comp_id = ?")->execute([$cid]);
      }

      /* المرشحون: لا تُحذف ثم تُعاد — الأصوات مرتبطة بها.
         نحذف من غادر فقط، ونُحدّث الباقي في مكانه. */
      $uids = [];
      foreach ((array)($c['winners'] ?? []) as $w) {
        $u = exp_str($w['uid'] ?? '', 64);
        if ($u !== '') $uids[] = $u;
      }
      if ($uids) {
        $in = implode(',', array_fill(0, count($uids), '?'));
        $db->prepare("DELETE FROM exp_candidates WHERE comp_id = ? AND uid NOT IN ($in)")
           ->execute(array_merge([$cid], $uids));
      } else {
        $db->prepare("DELETE FROM exp_candidates WHERE comp_id = ?")->execute([$cid]);
      }

      /* القطاعات */
      $sec = (array)($c['sectors'] ?? []);
      $inf = (array)($c['sectorInfo'] ?? []);
      $iS = $db->prepare("INSERT INTO exp_sectors (comp_id,id,name,descr,tags,sort_order)
                          VALUES (?,?,?,?,?,?)");
      $i = 0;
      foreach ($sec as $sid => $sname) {
        $sid = exp_id($sid); if ($sid === '') continue;
        $info = (array)($inf[$sid] ?? []);
        $tags = is_array($info['tags'] ?? null)
          ? implode('|', array_map(fn($t) => exp_str($t, 80), array_slice($info['tags'], 0, 30)))
          : '';
        $iS->execute([$cid, $sid, exp_str($sname, 200), exp_str($info['desc'] ?? '', 500),
                      mb_substr($tags, 0, 500), $i++]);
      }

      /* الفئات والمؤشرات */
      $iC = $db->prepare("INSERT INTO exp_categories (comp_id,id,title,color,sort_order)
                          VALUES (?,?,?,?,?)");
      $iM = $db->prepare("INSERT INTO exp_metrics (comp_id,cat_id,label,weight,sort_order)
                          VALUES (?,?,?,?,?)");
      $validCats = [];
      $i = 0;
      foreach ((array)($c['categories'] ?? []) as $cat) {
        $catId = exp_id($cat['id'] ?? ''); if ($catId === '') continue;
        if (isset($validCats[$catId])) continue;
        $validCats[$catId] = true;
        $iC->execute([$cid, $catId, exp_str($cat['title'] ?? '', 200),
                      exp_color($cat['color'] ?? ''), $i++]);
        $j = 0;
        foreach ((array)($cat['metrics'] ?? []) as $m) {
          $iM->execute([$cid, $catId, exp_str($m['k'] ?? '', 200), exp_num($m['w'] ?? 0), $j++]);
        }
      }

      /* الجائزة */
      $iP = $db->prepare("INSERT INTO exp_perks (comp_id,icon,text,sort_order) VALUES (?,?,?,?)");
      $i = 0;
      foreach ((array)($c['prize']['perks'] ?? []) as $p) {
        $iP->execute([$cid, exp_icon($p['ic'] ?? ''), exp_str($p['t'] ?? '', 300), $i++]);
      }

      /* المرشحون — إدراج أو تحديث مع الحفاظ على الأصوات */
      $iW = $db->prepare(
        "INSERT INTO exp_candidates
           (comp_id,uid,cat_id,sector_id,name,project,score,photo,vote_url,sort_order)
         VALUES (?,?,?,?,?,?,?,?,?,?)
         ON DUPLICATE KEY UPDATE
           cat_id=VALUES(cat_id), sector_id=VALUES(sector_id), name=VALUES(name),
           project=VALUES(project), score=VALUES(score), photo=VALUES(photo),
           vote_url=VALUES(vote_url), sort_order=VALUES(sort_order)"
      );
      $getId = $db->prepare("SELECT id FROM exp_candidates WHERE comp_id = ? AND uid = ? LIMIT 1");
      $delD  = $db->prepare("DELETE FROM exp_candidate_details WHERE candidate_id = ?");
      $iD    = $db->prepare("INSERT INTO exp_candidate_details (candidate_id,label,value,sort_order)
                             VALUES (?,?,?,?)");
      $i = 0;
      foreach ((array)($c['winners'] ?? []) as $w) {
        $uid = exp_str($w['uid'] ?? '', 64);
        if ($uid === '') $uid = bin2hex(random_bytes(8));

        $catId = exp_id($w['cat'] ?? '');
        if ($catId !== '' && !isset($validCats[$catId])) $catId = '';   // فئة محذوفة

        $iW->execute([
          $cid, $uid, $catId, exp_id($w['sector'] ?? ''),
          exp_str($w['name'] ?? '', 160),
          exp_str($w['project'] ?? '', 200),
          exp_num($w['score'] ?? 0),
          exp_photo($w['photo'] ?? ''),
          exp_url($w['voteUrl'] ?? ''),
          $i++,
        ]);

        $getId->execute([$cid, $uid]);
        $row = $getId->fetch();
        if (!$row) continue;
        $wid = (int)$row['id'];

        $delD->execute([$wid]);
        $j = 0;
        foreach ((array)($w['details'] ?? []) as $dt) {
          $lbl = exp_str($dt['k'] ?? '', 200);
          if ($lbl === '') continue;
          $iD->execute([$wid, $lbl, exp_num($dt['v'] ?? 0), $j++]);
        }
      }
    }

    $db->commit();
  } catch (Throwable $e) {
    if ($db->inTransaction()) $db->rollBack();
    throw $e;
  }

  return exp_load_platform($db);
}
