<?php
declare(strict_types=1);

/**
 * Pack203.4: Auto-apply tenant isolation filters to controllers SQL strings.
 * Works by injecting org filter clauses into common SELECT queries.
 * Creates .bak backups.
 */
$base = '/var/www/html/easychair/app/controllers';
$files = [
  'AdminChairsController.php',
  'AdminCommitteesController.php',
  'AdminProceedingsController.php',
  'AdminReviewAssignmentsCloneController.php',
  'AdminReviewAssignmentsController.php',
  'AdminReviewAssignmentsSuggestController.php',
  'AdminReviewerAssignmentsController.php',
  'AdminReviewersController.php',
  'AdminReviewersListStableController.php',
  'AdminReviewMatrixController.php',
  'AdminReviewRoundsController.php',
  'AdminSessionsController.php',
  'AdminSubmissionsBoardController.php',
  'AdminSubmissionsController.php',
  'AdminSubmissionsStableController.php',
  'AdminSubmissionsWorkflowController.php',
  'AdminTracksController.php',
  'AuthorSubmissionsController.php',
  'ProceedingsController.php',
  'PublicProceedingsController.php',
  'ReviewController.php',
  'ReviewerAssignmentsController.php',
  'ReviewerController.php',
  'SubmissionsController.php',
  'SubmissionsGatewayController.php',
];

function backup(string $path): void {
  $bak = $path . '.bak_' . date('Ymd_His');
  copy($path, $bak);
}

function inject_org_guard(string $txt): string {
  // Add $orgId line after require_admin_area/require_login if not present
  if (strpos($txt, '$orgId = (int) current_org_id();') !== false) return $txt;

  $patterns = [
    "require_admin_area();",
    "require_login();",
    "require_org_context();",
  ];
  foreach ($patterns as $p) {
    $pos = strpos($txt, $p);
    if ($pos !== false) {
      $insertPos = $pos + strlen($p);
      return substr($txt, 0, $insertPos) . "\n        \$orgId = (int) current_org_id();\n" . substr($txt, $insertPos);
    }
  }
  // fallback: inject near start of index()
  return preg_replace('/public function index\(\): void\s*\{/', "public function index(): void\n    {\n        \$orgId = (int) current_org_id();", $txt, 1) ?: $txt;
}

/**
 * Insert tenant filter before ORDER BY/LIMIT (or at end) for SQL strings that contain FROM <table>.
 * We do NOT rely on aliases. We use subqueries.
 */
function add_filter_to_sql(string $sql, string $filter): string {
  if (stripos($sql, 'org_id') !== false) return $sql; // already scoped
  if (stripos($sql, $filter) !== false) return $sql;

  // If already has WHERE, add AND; else add WHERE
  $hasWhere = (stripos($sql, ' where ') !== false);

  // Insert before ORDER BY / GROUP BY / LIMIT if present
  $insertAt = null;
  foreach ([' order by ', ' group by ', ' limit '] as $kw) {
    $p = stripos($sql, $kw);
    if ($p !== false) { $insertAt = $p; break; }
  }
  $clause = ($hasWhere ? " AND " : " WHERE ") . $filter . " ";

  if ($insertAt === null) return $sql . $clause;
  return substr($sql, 0, $insertAt) . $clause . substr($sql, $insertAt);
}

function patch_sql_literals(string $txt, int &$count): string {
  // We patch only double-quoted SQL strings to keep safe concatenation with $orgId
  // Replace occurrences by adding filters using ".$orgId." concatenation.
  $re = '/"([^"]*?\bFROM\b[^"]*?)"/si';
  return preg_replace_callback($re, function($m) use (&$count){
    $s = $m[1];

    // Build filters
    $orgExpr = '".(int)$orgId."'; // we will convert to concatenation below

    // conference-scoped tables (have conference_id)
    $confFilter = "conference_id IN (SELECT id FROM conferences WHERE org_id=".$orgExpr.")";
    $subFilter  = "submission_id IN (SELECT id FROM submissions WHERE conference_id IN (SELECT id FROM conferences WHERE org_id=".$orgExpr."))";
    $raFilter   = "id IN (SELECT id FROM review_assignments WHERE submission_id IN (SELECT id FROM submissions WHERE conference_id IN (SELECT id FROM conferences WHERE org_id=".$orgExpr.")))";

    $orig = $s;

    // conferences list
    if (preg_match('/\bFROM\s+conferences\b/i', $s)) {
      $s = add_filter_to_sql($s, "org_id=".$orgExpr);
    }

    // tables with conference_id
    foreach (['tracks','conference_sessions','committees','chairs','conference_homepage_blocks','conference_blocks','proceedings','conference_proceedings'] as $t) {
      if (preg_match('/\bFROM\s+'.preg_quote($t,'/').'\b/i', $s)) {
        $s = add_filter_to_sql($s, $confFilter);
      }
    }

    // submissions table
    if (preg_match('/\bFROM\s+submissions\b/i', $s)) {
      $s = add_filter_to_sql($s, $confFilter);
    }

    // review_assignments table
    if (preg_match('/\bFROM\s+review_assignments\b/i', $s)) {
      $s = add_filter_to_sql($s, $subFilter);
    }

    // reviewer_ratings table: via review_assignments
    if (preg_match('/\bFROM\s+reviewer_ratings\b/i', $s)) {
      $s = add_filter_to_sql($s, "review_assignment_id IN (SELECT id FROM review_assignments WHERE ".$subFilter.")");
    }

    if ($s !== $orig) {
      $count++;
      // Convert the injected orgExpr placeholder (which contains quotes) to real concatenation
      // We used orgExpr = '".(int)$orgId."' so now we need to replace it with ".$orgId."
      $s = str_replace('".(int)$orgId."', '".(int)$orgId."', $s);
      // Now wrap string with concatenation: " ... org_id=".(int)$orgId." ..."
      // Replace occurrences of org_id=".(int)$orgId." inside current string by splitting:
      // Simplify: we already injected orgExpr, so we now transform it:
      $s = str_replace('org_id='.'"'.(int)$orgId.'"', 'org_id=' . '".(int)$orgId."', $s);
    }
    return '"'.$s.'"';
  }, $txt) ?: $txt;
}

foreach ($files as $f) {
  $path = $base . '/' . $f;
  if (!file_exists($path)) { echo "SKIP missing: $f\n"; continue; }

  $txt = file_get_contents($path);
  $before = $txt;

  $txt = inject_org_guard($txt);

  $count = 0;
  $txt = patch_sql_literals($txt, $count);

  if ($txt !== $before) {
    backup($path);
    file_put_contents($path, $txt);
    echo "PATCHED $f (sql edits=$count)\n";
  } else {
    echo "NOCHANGE $f\n";
  }
}

echo "Pack203.4 patch done\n";
