<?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 get_cols($db,string $t):array{
  $st=$db->prepare("SELECT column_name FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name=:t");
  $st->execute([':t'=>$t]);
  return $st->fetchAll(PDO::FETCH_COLUMN,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.7B 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";
} else {
  echo "audit_logs.org_id exists\n";
}

$cols = array_map('strtolower', get_cols($db,'audit_logs'));
$hasActor = in_array('actor_user_id', $cols, true);

// Detect entity columns if they exist (we won't assume names)
$typeCols = ['entity_type','entity_name','entity','module_name'];
$idCols   = ['entity_id','entity_pk','entityid','entity_key','record_id'];

$typeCol = null; $idCol=null;
foreach($typeCols as $c){ if(in_array($c,$cols,true)){ $typeCol=$c; break; } }
foreach($idCols as $c){ if(in_array($c,$cols,true)){ $idCol=$c; break; } }

echo "Detected columns: typeCol=".($typeCol?:'NONE')." idCol=".($idCol?:'NONE')." actor_user_id=".($hasActor?'YES':'NO')."\n";

echo "Backfill org_id...\n";

// 1) If we have actor_user_id, use org_users mapping (best reliable fallback)
if($hasActor && table_exists($db,'org_users')){
  // pick active org role row; if multiple orgs, it will pick max(org_id) deterministically
  $db->exec("
    UPDATE audit_logs a
    JOIN (
      SELECT user_id, MAX(org_id) AS org_id
      FROM org_users
      WHERE is_active=1
      GROUP BY user_id
    ) ou ON ou.user_id = a.actor_user_id
    SET a.org_id = ou.org_id
    WHERE a.org_id IS NULL
  ");
  echo "Backfilled from actor_user_id -> org_users\n";
}

// 2) If entity columns exist, try to refine using conferences/submissions/review_assignments when possible
if($typeCol && $idCol){
  // conference
  if(table_exists($db,'conferences')){
    $db->exec("
      UPDATE audit_logs a
      JOIN conferences c ON a.`$typeCol`='conference' AND a.`$idCol`=c.id
      SET a.org_id=c.org_id
      WHERE a.org_id IS NULL
    ");
    echo "Backfilled from conferences via {$typeCol}/{$idCol}\n";
  }

  // submission
  if(table_exists($db,'submissions')){
    // if submissions has org_id use it; else join via conferences
    if(col_exists($db,'submissions','org_id')){
      $db->exec("
        UPDATE audit_logs a
        JOIN submissions s ON a.`$typeCol` IN ('submission','submissions') AND a.`$idCol`=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.`$typeCol` IN ('submission','submissions') AND a.`$idCol`=s.id
        JOIN conferences c ON c.id=s.conference_id
        SET a.org_id=c.org_id
        WHERE a.org_id IS NULL
      ");
    }
    echo "Backfilled from submissions via {$typeCol}/{$idCol}\n";
  }

  // review_assignment
  if(table_exists($db,'review_assignments')){
    if(col_exists($db,'review_assignments','org_id')){
      $db->exec("
        UPDATE audit_logs a
        JOIN review_assignments ra ON a.`$typeCol` IN ('review_assignment','review_assignments') AND a.`$idCol`=ra.id
        SET a.org_id=ra.org_id
        WHERE a.org_id IS NULL
      ");
    } else if(table_exists($db,'submissions')){
      $db->exec("
        UPDATE audit_logs a
        JOIN review_assignments ra ON a.`$typeCol` IN ('review_assignment','review_assignments') AND a.`$idCol`=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
      ");
    }
    echo "Backfilled from review_assignments via {$typeCol}/{$idCol}\n";
  }
}

// 3) Any remaining NULL -> default org
$db->exec("UPDATE audit_logs SET org_id={$defaultOrg} WHERE org_id IS NULL");
echo "Filled remaining NULL org_id with defaultOrg\n";

// Trigger: keep non-null org_id (safe default)
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 trg_audit_org_ins created\n";

echo "Pack204.7B done\n";
