<?php
declare(strict_types=1);

require __DIR__ . '/../../config/bootstrap.php';

use App\Core\Database;

$db = Database::getInstance();

function tableExists(PDO $db, string $t): bool {
  $st = $db->prepare("SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=:t");
  $st->execute([':t'=>$t]);
  return (int)$st->fetchColumn() > 0;
}

function colExists(PDO $db, string $t, string $c): bool {
  $st = $db->prepare("SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=:t AND COLUMN_NAME=:c");
  $st->execute([':t'=>$t, ':c'=>$c]);
  return (int)$st->fetchColumn() > 0;
}

function addCol(PDO $db, string $t, string $def): void {
  $db->exec("ALTER TABLE `$t` ADD COLUMN $def");
}

$primary = 'conference_homepage_blocks';
$alt1 = 'conference_blocks';
$alt2 = 'homepage_blocks';

$use = $primary;
if (!tableExists($db,$primary)) {
  if (tableExists($db,$alt1)) $use = $alt1;
  elseif (tableExists($db,$alt2)) $use = $alt2;
  else $use = $primary;
}

if ($use === $primary && !tableExists($db,$primary)) {
  $db->exec("
    CREATE TABLE `conference_homepage_blocks` (
      `id` bigint unsigned NOT NULL AUTO_INCREMENT,
      `org_id` bigint unsigned DEFAULT NULL,
      `conference_id` bigint unsigned NOT NULL,
      `block_type` enum('hero_slide','panel','topic','section_html','important_date') COLLATE utf8mb4_unicode_ci NOT NULL,
      `block_key` varchar(120) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `title_en` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `title_ar` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `body_en` longtext COLLATE utf8mb4_unicode_ci,
      `body_ar` longtext COLLATE utf8mb4_unicode_ci,
      `image_path` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `link_url` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `sort_order` int NOT NULL DEFAULT 0,
      `is_active` tinyint(1) NOT NULL DEFAULT 1,
      `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
      `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (`id`),
      KEY `idx_org_id` (`org_id`),
      KEY `idx_conf_id` (`conference_id`),
      CONSTRAINT `fk_chb_conf` FOREIGN KEY (`conference_id`) REFERENCES `conferences` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  ");
  echo "Created $primary\n";
  $use = $primary;
} else {
  echo "Using blocks table: $use\n";
}

/**
 * Ensure columns exist on chosen table.
 * (We won't DROP/RENAME—only add missing columns.)
 */
$need = [
  "org_id bigint unsigned DEFAULT NULL",
  "conference_id bigint unsigned NOT NULL",
  "block_type enum('hero_slide','panel','topic','section_html','important_date') COLLATE utf8mb4_unicode_ci NOT NULL",
  "block_key varchar(120) COLLATE utf8mb4_unicode_ci DEFAULT NULL",
  "title_en varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL",
  "title_ar varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL",
  "body_en longtext COLLATE utf8mb4_unicode_ci",
  "body_ar longtext COLLATE utf8mb4_unicode_ci",
  "image_path varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL",
  "link_url varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL",
  "sort_order int NOT NULL DEFAULT 0",
  "is_active tinyint(1) NOT NULL DEFAULT 1",
];

foreach ($need as $def) {
  preg_match('/^([a-zA-Z0-9_]+)/', $def, $m);
  $col = $m[1] ?? '';
  if ($col && !colExists($db,$use,$col)) {
    addCol($db,$use,$def);
    echo "Added $use.$col\n";
  }
}

echo "DONE Pack205.10 (table=$use)\n";
