<?php
// /elms/includes/assign_filter.php
// Build visibility filters for lecturers using lms_lecturer_assignments.
// Usage:
//   require_once __DIR__.'/assign_filter.php';
//   [$joinSubs, $joinAssess, $whereSubs, $whereAssess, $types, $vals] = assign_filter($mysqli, $lecturerId);
//
// Then add: FROM ... $joinSubs WHERE $whereSubs ... and bind $types/$vals

declare(strict_types=1);

/**
 * Returns array: [$joinSubs, $joinAssess, $whereSubs, $whereAssess, $types, $vals]
 * - $joinSubs:  JOIN/LEFT JOIN for queries that read lms_submissions (alias s) and lms_assessments (alias a)
 * - $joinAssess: JOIN/LEFT JOIN for queries that only read lms_assessments (alias a)
 * - $whereSubs/$whereAssess: visibility WHERE clause fragments
 * - $types/$vals: bind params (first is lecturer_id)
 */
function assign_filter(mysqli $db, int $lecturerId): array {
  // basic guards
  $exists = function(string $t) use ($db): bool {
    $t = $db->real_escape_string($t);
    $r = $db->query("SHOW TABLES LIKE '$t'");
    $ok = $r && $r->num_rows>0; if($r) $r->free(); return $ok;
  };
  $has = function(string $t, string $c) use ($db): bool {
    $t = str_replace('`','``',$t); $c=$db->real_escape_string($c);
    $r = $db->query("SHOW COLUMNS FROM `$t` LIKE '$c'");
    $ok = $r && $r->num_rows>0; if($r) $r->free(); return $ok;
  };

  $tblAssign   = $exists('lms_lecturer_assignments') ? 'lms_lecturer_assignments' : null;
  $tblSubs     = $exists('lms_submissions') ? 'lms_submissions' : null;
  $tblAssess   = $exists('lms_assessments') ? 'lms_assessments' : null;

  // If no assignment table => do nothing (backward compatible)
  if (!$tblAssign) {
    return ['', '', '1', '1', '', []];
  }

  // We’ll use a permissive match: an assignment matches when each scope present lines up:
  // - course: la.course_id is NULL/0 OR matches a.course_id (or s.course_id if a.course_id missing)
  // - batch : la.batch_id  is NULL/0 OR matches s.batch_id
  // - module: la.module_id is NULL/0 OR matches COALESCE(s.module_id, a.module_id)
  // - unit  : la.unit_id   is NULL/0 OR matches s.unit_id (if the column exists)
  $hasSubBatch  = $tblSubs  && $has($tblSubs,'batch_id');
  $hasSubMod    = $tblSubs  && ($has($tblSubs,'module_id') || $has($tblSubs,'course_module_id'));
  $subModCol    = $tblSubs  && $has($tblSubs,'module_id') ? 's.module_id' : ($tblSubs && $has($tblSubs,'course_module_id') ? 's.course_module_id' : 'NULL');
  $hasSubCourse = $tblSubs  && $has($tblSubs,'course_id');
  $hasSubUnit   = $tblSubs  && $has($tblSubs,'unit_id');

  $hasAssCourse = $tblAssess && $has($tblAssess,'course_id');
  $hasAssMod    = $tblAssess && $has($tblAssess,'module_id');

  // expression helpers (use COALESCE when needed)
  $exprCourse = $hasAssCourse ? 'a.course_id' : ($hasSubCourse ? 's.course_id' : 'NULL');
  $exprModule = $hasAssMod ? 'a.module_id' : $subModCol;

  // JOIN for submissions queries (we have s JOIN a already in most pages)
  $joinSubs = "
    JOIN `$tblAssign` la
      ON la.lecturer_id = ?
     AND la.active = 1
     AND (la.course_id IS NULL OR la.course_id=0 OR ".($exprCourse)." = la.course_id)
     ".($hasSubBatch ? "AND (la.batch_id IS NULL OR la.batch_id=0 OR s.batch_id = la.batch_id)" : "AND (la.batch_id IS NULL OR la.batch_id=0)")."
     AND (la.module_id IS NULL OR la.module_id=0 OR ".($exprModule)." = la.module_id)
     ".($hasSubUnit ? "AND (la.unit_id IS NULL OR la.unit_id=0 OR s.unit_id = la.unit_id)" : "AND (la.unit_id IS NULL OR la.unit_id=0)")."
  ";

  // JOIN for assessments-only queries (no s available)
  $joinAssess = "
    JOIN `$tblAssign` la
      ON la.lecturer_id = ?
     AND la.active = 1
     AND (la.course_id IS NULL OR la.course_id=0 OR ".($hasAssCourse?'a.course_id':'NULL')." = la.course_id)
     AND (la.module_id IS NULL OR la.module_id=0 OR ".($hasAssMod?'a.module_id':'NULL')." = la.module_id)
  ";

  // WHERE fragments (la join already narrows, but keep a simple 1=1)
  $whereSubs   = "1";
  $whereAssess = "1";

  $types = 'i';
  $vals  = [$lecturerId];

  return [$joinSubs, $joinAssess, $whereSubs, $whereAssess, $types, $vals];
}
