<?php
declare(strict_types=1);

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

use App\Core\Database;

$db = Database::getInstance();

function table_exists($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 col_exists($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;
}

echo "Pack200: multi-tenant upgrade start\n";

/* 1) Core tables */
if (!table_exists($db,'organizations')) {
  $db->exec("
    CREATE TABLE organizations (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      name VARCHAR(255) NOT NULL,
      code VARCHAR(80) NOT NULL UNIQUE,
      contact_email VARCHAR(190) NULL,
      logo_path VARCHAR(255) NULL,
      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)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  ");
  echo "Created organizations\n";
} else echo "organizations exists\n";

if (!table_exists($db,'plans')) {
  $db->exec("
    CREATE TABLE plans (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      name VARCHAR(120) NOT NULL,
      code VARCHAR(80) NOT NULL UNIQUE,
      price_monthly DECIMAL(10,2) NOT NULL DEFAULT 0.00,
      limits_json JSON NULL,
      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)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  ");
  echo "Created plans\n";
} else echo "plans exists\n";

if (!table_exists($db,'org_subscriptions')) {
  $db->exec("
    CREATE TABLE org_subscriptions (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      org_id BIGINT UNSIGNED NOT NULL,
      plan_id BIGINT UNSIGNED NOT NULL,
      status ENUM('trial','active','past_due','suspended','canceled') NOT NULL DEFAULT 'trial',
      starts_at DATE NULL,
      ends_at DATE NULL,
      limits_override_json JSON NULL,
      notes TEXT NULL,
      created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY(id),
      KEY idx_org (org_id),
      KEY idx_plan (plan_id),
      CONSTRAINT fk_sub_org FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE CASCADE,
      CONSTRAINT fk_sub_plan FOREIGN KEY (plan_id) REFERENCES plans(id) ON DELETE RESTRICT
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  ");
  echo "Created org_subscriptions\n";
} else echo "org_subscriptions exists\n";

if (!table_exists($db,'org_users')) {
  $db->exec("
    CREATE TABLE org_users (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      org_id BIGINT UNSIGNED NOT NULL,
      user_id BIGINT UNSIGNED NOT NULL,
      role_code ENUM('org_admin','chair','tpc','reviewer','author') NOT NULL,
      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),
      UNIQUE KEY uniq_org_user (org_id, user_id),
      KEY idx_org_role (org_id, role_code),
      CONSTRAINT fk_ou_org FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE CASCADE,
      CONSTRAINT fk_ou_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  ");
  echo "Created org_users\n";
} else echo "org_users exists\n";

/* 2) conferences.org_id */
if (table_exists($db,'conferences') && !col_exists($db,'conferences','org_id')) {
  $db->exec("ALTER TABLE conferences ADD COLUMN org_id BIGINT UNSIGNED NULL AFTER id");
  $db->exec("ALTER TABLE conferences ADD KEY idx_org_id (org_id)");
  echo "Added conferences.org_id\n";
} else echo "conferences.org_id exists\n";

/* 3) Seed plans */
$db->exec("
  INSERT IGNORE INTO plans(code,name,price_monthly,limits_json) VALUES
  ('basic','Basic',0.00, JSON_OBJECT('max_conferences',1,'max_reviewers',30,'storage_mb',500,'email_per_day',200)),
  ('pro','Pro',39.00, JSON_OBJECT('max_conferences',10,'max_reviewers',300,'storage_mb',5000,'email_per_day',2000)),
  ('enterprise','Enterprise',0.00, JSON_OBJECT('max_conferences',999,'max_reviewers',9999,'storage_mb',50000,'email_per_day',20000));
");
echo "Seeded plans\n";

/* 4) Create default org (jpu) */
$db->exec("INSERT IGNORE INTO organizations(code,name,contact_email,is_active) VALUES ('jpu','Jerash University','admin@jpu.edu.jo',1)");
echo "Seeded org jpu\n";

/* 5) Ensure subscription for jpu -> enterprise */
$db->exec("
  INSERT IGNORE INTO org_subscriptions(org_id,plan_id,status,starts_at)
  SELECT o.id, p.id, 'active', CURDATE()
  FROM organizations o JOIN plans p ON p.code='enterprise'
  WHERE o.code='jpu'
");
echo "Seeded org_subscriptions\n";

/* 6) Attach user#1 as org_admin */
$db->exec("
  INSERT IGNORE INTO org_users(org_id,user_id,role_code,is_active)
  SELECT o.id, 1, 'org_admin', 1 FROM organizations o WHERE o.code='jpu'
");
echo "Seeded org_users\n";

/* 7) Migration: set all existing conferences to jpu */
$db->exec("
  UPDATE conferences
  SET org_id=(SELECT id FROM organizations WHERE code='jpu' LIMIT 1)
  WHERE org_id IS NULL
");
echo "Migrated conferences.org_id\n";

echo "Pack200: done\n";
