<?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;
}
function add_org_id($db, string $t): void {
  if(!table_exists($db,$t)) { echo "SKIP missing table $t\n"; return; }
  if(!col_exists($db,$t,'org_id')) {
    $db->exec("ALTER TABLE $t ADD COLUMN org_id BIGINT UNSIGNED NULL");
    $db->exec("ALTER TABLE $t ADD KEY idx_org_id (org_id)");
    echo "Added $t.org_id\n";
  } else {
    echo "$t.org_id exists\n";
  }
}

echo "Pack203.4: add org_id columns\n";

$tables = [
  'submissions',
  'tracks',
  'review_assignments',
  'reviews',
  'decisions',
  'committees',
  'chairs',
  'conference_sessions',
  'proceedings',
  'reviewer_ratings'
];

foreach($tables as $t) add_org_id($db,$t);

// Backfill org_id from conferences
echo "Backfill org_id\n";

// submissions: from conferences
if(table_exists($db,'submissions') && col_exists($db,'submissions','conference_id')) {
  $db->exec("
    UPDATE submissions s
    JOIN conferences c ON c.id=s.conference_id
    SET s.org_id=c.org_id
    WHERE s.org_id IS NULL
  ");
  echo "Backfilled submissions.org_id\n";
}

// tracks: from conferences
if(table_exists($db,'tracks') && col_exists($db,'tracks','conference_id')) {
  $db->exec("
    UPDATE tracks t
    JOIN conferences c ON c.id=t.conference_id
    SET t.org_id=c.org_id
    WHERE t.org_id IS NULL
  ");
  echo "Backfilled tracks.org_id\n";
}

// committees: from conferences
if(table_exists($db,'committees') && col_exists($db,'committees','conference_id')) {
  $db->exec("
    UPDATE committees x
    JOIN conferences c ON c.id=x.conference_id
    SET x.org_id=c.org_id
    WHERE x.org_id IS NULL
  ");
  echo "Backfilled committees.org_id\n";
}

// chairs: from conferences
if(table_exists($db,'chairs') && col_exists($db,'chairs','conference_id')) {
  $db->exec("
    UPDATE chairs x
    JOIN conferences c ON c.id=x.conference_id
    SET x.org_id=c.org_id
    WHERE x.org_id IS NULL
  ");
  echo "Backfilled chairs.org_id\n";
}

// sessions: from conferences
if(table_exists($db,'conference_sessions') && col_exists($db,'conference_sessions','conference_id')) {
  $db->exec("
    UPDATE conference_sessions x
    JOIN conferences c ON c.id=x.conference_id
    SET x.org_id=c.org_id
    WHERE x.org_id IS NULL
  ");
  echo "Backfilled conference_sessions.org_id\n";
}

// proceedings: from conferences
if(table_exists($db,'proceedings') && col_exists($db,'proceedings','conference_id')) {
  $db->exec("
    UPDATE proceedings p
    JOIN conferences c ON c.id=p.conference_id
    SET p.org_id=c.org_id
    WHERE p.org_id IS NULL
  ");
  echo "Backfilled proceedings.org_id\n";
}

// review_assignments: from submissions -> conferences
if(table_exists($db,'review_assignments') && col_exists($db,'review_assignments','submission_id')) {
  $db->exec("
    UPDATE review_assignments ra
    JOIN submissions s ON s.id=ra.submission_id
    SET ra.org_id=s.org_id
    WHERE ra.org_id IS NULL
  ");
  echo "Backfilled review_assignments.org_id\n";
}

// reviews: from review_assignments
if(table_exists($db,'reviews') && col_exists($db,'reviews','assignment_id')) {
  $db->exec("
    UPDATE reviews r
    JOIN review_assignments ra ON ra.id=r.assignment_id
    SET r.org_id=ra.org_id
    WHERE r.org_id IS NULL
  ");
  echo "Backfilled reviews.org_id\n";
}

// decisions: from submissions
if(table_exists($db,'decisions') && col_exists($db,'decisions','submission_id')) {
  $db->exec("
    UPDATE decisions d
    JOIN submissions s ON s.id=d.submission_id
    SET d.org_id=s.org_id
    WHERE d.org_id IS NULL
  ");
  echo "Backfilled decisions.org_id\n";
}

// reviewer_ratings: from review_assignments
if(table_exists($db,'reviewer_ratings') && col_exists($db,'reviewer_ratings','review_assignment_id')) {
  $db->exec("
    UPDATE reviewer_ratings rr
    JOIN review_assignments ra ON ra.id=rr.review_assignment_id
    SET rr.org_id=ra.org_id
    WHERE rr.org_id IS NULL
  ");
  echo "Backfilled reviewer_ratings.org_id\n";
}

echo "Create triggers (keep org_id consistent)\n";

// Drop old triggers if exist (safe try)
$triggers = [
  'trg_submissions_org_ins','trg_submissions_org_upd',
  'trg_ra_org_ins','trg_ra_org_upd',
  'trg_rr_org_ins','trg_rr_org_upd'
];
foreach($triggers as $tg){
  try{ $db->exec("DROP TRIGGER IF EXISTS $tg"); } catch(\Throwable $e){}
}

// submissions: set org_id from conferences
if(table_exists($db,'submissions') && col_exists($db,'submissions','conference_id')) {
  $db->exec("
    CREATE TRIGGER trg_submissions_org_ins
    BEFORE INSERT ON submissions
    FOR EACH ROW
    BEGIN
      IF NEW.org_id IS NULL THEN
        SET NEW.org_id = (SELECT org_id FROM conferences WHERE id=NEW.conference_id LIMIT 1);
      END IF;
    END
  ");
  $db->exec("
    CREATE TRIGGER trg_submissions_org_upd
    BEFORE UPDATE ON submissions
    FOR EACH ROW
    BEGIN
      IF NEW.conference_id <> OLD.conference_id THEN
        SET NEW.org_id = (SELECT org_id FROM conferences WHERE id=NEW.conference_id LIMIT 1);
      END IF;
    END
  ");
  echo "Triggers: submissions org_id\n";
}

// review_assignments: set org_id from submissions
if(table_exists($db,'review_assignments') && col_exists($db,'review_assignments','submission_id')) {
  $db->exec("
    CREATE TRIGGER trg_ra_org_ins
    BEFORE INSERT ON review_assignments
    FOR EACH ROW
    BEGIN
      IF NEW.org_id IS NULL THEN
        SET NEW.org_id = (SELECT org_id FROM submissions WHERE id=NEW.submission_id LIMIT 1);
      END IF;
    END
  ");
  $db->exec("
    CREATE TRIGGER trg_ra_org_upd
    BEFORE UPDATE ON review_assignments
    FOR EACH ROW
    BEGIN
      IF NEW.submission_id <> OLD.submission_id THEN
        SET NEW.org_id = (SELECT org_id FROM submissions WHERE id=NEW.submission_id LIMIT 1);
      END IF;
    END
  ");
  echo "Triggers: review_assignments org_id\n";
}

// reviewer_ratings: set org_id from review_assignments
if(table_exists($db,'reviewer_ratings') && col_exists($db,'reviewer_ratings','review_assignment_id')) {
  $db->exec("
    CREATE TRIGGER trg_rr_org_ins
    BEFORE INSERT ON reviewer_ratings
    FOR EACH ROW
    BEGIN
      IF NEW.org_id IS NULL THEN
        SET NEW.org_id = (SELECT org_id FROM review_assignments WHERE id=NEW.review_assignment_id LIMIT 1);
      END IF;
    END
  ");
  $db->exec("
    CREATE TRIGGER trg_rr_org_upd
    BEFORE UPDATE ON reviewer_ratings
    FOR EACH ROW
    BEGIN
      IF NEW.review_assignment_id <> OLD.review_assignment_id THEN
        SET NEW.org_id = (SELECT org_id FROM review_assignments WHERE id=NEW.review_assignment_id LIMIT 1);
      END IF;
    END
  ");
  echo "Triggers: reviewer_ratings org_id\n";
}

echo "Pack203.4 done\n";
