<?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;
}

if(!table_exists($db,'audit_logs')){
  echo "audit_logs table missing\n";
  exit;
}

$defaultOrg=(int)$db->query("SELECT id FROM organizations ORDER BY id ASC LIMIT 1")->fetchColumn();
if($defaultOrg<=0) $defaultOrg=1;

echo "Pack204.7 audit isolation (defaultOrg={$defaultOrg})\n";

if(!col_exists($db,'audit_logs','org_id')){
  $db->exec("ALTER TABLE audit_logs ADD COLUMN org_id BIGINT UNSIGNED NULL");
  $db->exec("ALTER TABLE audit_logs ADD KEY idx_org_id (org_id)");
  echo "Added audit_logs.org_id\n";
}

echo "Backfilling audit_logs.org_id...\n";

/**
 * Backfill rules:
 * - entity_type='conference' -> conferences.org_id
 * - entity_type='submission' -> submissions.org_id (or conferences.org_id via join)
 * - entity_type='review_assignment' -> review_assignments.org_id (or submissions.org_id)
 * Otherwise defaultOrg.
 */
$db->exec("
  UPDATE audit_logs a
  JOIN conferences c ON a.entity_type='conference' AND a.entity_id=c.id
  SET a.org_id=c.org_id
  WHERE a.org_id IS NULL
");

if(table_exists($db,'submissions') && col_exists($db,'submissions','org_id')){
  $db->exec("
    UPDATE audit_logs a
    JOIN submissions s ON a.entity_type IN ('submission','submissions') AND a.entity_id=s.id
    SET a.org_id=s.org_id
    WHERE a.org_id IS NULL
  ");
} else {
  $db->exec("
    UPDATE audit_logs a
    JOIN submissions s ON a.entity_type IN ('submission','submissions') AND a.entity_id=s.id
    JOIN conferences c ON c.id=s.conference_id
    SET a.org_id=c.org_id
    WHERE a.org_id IS NULL
  ");
}

if(table_exists($db,'review_assignments') && col_exists($db,'review_assignments','org_id')){
  $db->exec("
    UPDATE audit_logs a
    JOIN review_assignments ra ON a.entity_type IN ('review_assignment','review_assignments') AND a.entity_id=ra.id
    SET a.org_id=ra.org_id
    WHERE a.org_id IS NULL
  ");
} else {
  $db->exec("
    UPDATE audit_logs a
    JOIN review_assignments ra ON a.entity_type IN ('review_assignment','review_assignments') AND a.entity_id=ra.id
    JOIN submissions s ON s.id=ra.submission_id
    JOIN conferences c ON c.id=s.conference_id
    SET a.org_id=c.org_id
    WHERE a.org_id IS NULL
  ");
}

$db->exec("UPDATE audit_logs SET org_id={$defaultOrg} WHERE org_id IS NULL");
echo "Backfill done.\n";

// Trigger: default org_id to defaultOrg if null (safer than reading PHP session from MySQL)
try { $db->exec("DROP TRIGGER IF EXISTS trg_audit_org_ins"); } catch(\Throwable $e){}
$db->exec("
  CREATE TRIGGER trg_audit_org_ins
  BEFORE INSERT ON audit_logs
  FOR EACH ROW
  BEGIN
    IF NEW.org_id IS NULL THEN
      SET NEW.org_id = {$defaultOrg};
    END IF;
  END
");
echo "Trigger created.\n";

echo "Pack204.7 done\n";
