<?php
// /edenrms/ajax/approve_delete_student.php
declare(strict_types=1);
header('Content-Type: application/json; charset=utf-8');

try {
  @session_start();
  mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

  // auth
  require_once __DIR__ . '/../includes/auth.php';
  $role = function_exists('ec_role') ? strtolower((string)ec_role()) : '';
  if (
    !in_array($role, ['admin','super_admin','branch_admin','accountant','coordinator'], true) &&
    empty($_SESSION['admin']) &&
    empty($_SESSION['super_admin'])
  ) {
    http_response_code(403);
    echo json_encode(['ok' => false, 'error' => 'Unauthorized']);
    exit;
  }

  // notifications
  $NOTI = __DIR__ . '/../includes/notifications.php';
  if (is_file($NOTI)) require_once $NOTI;

  // DB
  $db = null;
  $inc = __DIR__ . '/../includes/db.php';
  if (is_file($inc)) {
    require_once $inc;
    if (isset($conn) && $conn instanceof mysqli) $db = $conn;
  }
  if (!$db instanceof mysqli) {
    $db = new mysqli('localhost', 'root', '', 'edencamp_edencampus');
  }
  $db->set_charset('utf8mb4');

  // helpers
  $tExists = function(string $t) use ($db): bool {
    $st = $db->prepare("SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=?");
    $st->bind_param('s', $t);
    $st->execute();
    $st->store_result();
    $ok = $st->num_rows > 0;
    $st->free_result();
    $st->close();
    return $ok;
  };

  $cExists = function(string $t, string $c) use ($db, $tExists): bool {
    if (!$tExists($t)) return false;
    $st = $db->prepare("SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=? AND COLUMN_NAME=?");
    $st->bind_param('ss', $t, $c);
    $st->execute();
    $st->store_result();
    $ok = $st->num_rows > 0;
    $st->free_result();
    $st->close();
    return $ok;
  };

  $pickCol = function(string $tbl, array $cands) use ($cExists): ?string {
    foreach ($cands as $c) {
      if ($cExists($tbl, $c)) return $c;
    }
    return null;
  };

  $reqId  = (int)($_POST['request_id'] ?? $_GET['request_id'] ?? 0);
  $action = strtolower((string)($_POST['action'] ?? $_GET['action'] ?? 'approve'));
  $note   = trim((string)($_POST['note'] ?? ''));

  if (!in_array($action, ['approve', 'reject'], true)) {
    echo json_encode(['ok' => false, 'error' => 'action must be approve|reject']);
    exit;
  }
  if ($reqId <= 0) {
    echo json_encode(['ok' => false, 'error' => 'Invalid request id']);
    exit;
  }

  $typeCol = $cExists('approval_requests', 'type')
    ? 'type'
    : ($cExists('approval_requests', 'req_type') ? 'req_type' : 'type');

  $sidCol = $pickCol('approval_requests', ['student_id', 'ref_id', 'sid', 'entity_id']) ?? 'student_id';

  // load request
  $st = $db->prepare("SELECT id, `$typeCol` AS typ, `$sidCol` AS sid, status, requested_by FROM approval_requests WHERE id=? LIMIT 1");
  $st->bind_param('i', $reqId);
  $st->execute();
  $req = $st->get_result()->fetch_assoc() ?: null;
  $st->close();

  if (!$req) {
    echo json_encode(['ok' => false, 'error' => 'Request not found']);
    exit;
  }
  if (strtolower((string)$req['status']) !== 'pending') {
    echo json_encode(['ok' => false, 'error' => 'Request already decided']);
    exit;
  }
  if (strtolower((string)$req['typ']) !== 'delete_student') {
    echo json_encode(['ok' => false, 'error' => 'Unsupported request type']);
    exit;
  }

  $sid = (int)$req['sid'];
  if ($sid <= 0) {
    echo json_encode(['ok' => false, 'error' => 'Request has no student id']);
    exit;
  }

  $stuTbl = $tExists('studentss') ? 'studentss' : ($tExists('students') ? 'students' : null);
  if (!$stuTbl) {
    echo json_encode(['ok' => false, 'error' => 'students table not found']);
    exit;
  }

  $uid = (int)($_SESSION['user']['id'] ?? $_SESSION['admin']['id'] ?? $_SESSION['super_admin']['id'] ?? 0);

  if ($action === 'reject') {
    $cols = ['status' => 'rejected'];
    if ($cExists('approval_requests', 'decided_by')) $cols['decided_by'] = $uid;
    if ($cExists('approval_requests', 'decided_at')) $cols['decided_at'] = date('Y-m-d H:i:s');
    if ($cExists('approval_requests', 'note') && $note !== '') $cols['note'] = $note;

    $set = [];
    $types = '';
    $vals = [];
    foreach ($cols as $k => $v) {
      $set[] = "`$k`=?";
      $types .= ($k === 'decided_by') ? 'i' : 's';
      $vals[] = $v;
    }
    $types .= 'i';
    $vals[] = $reqId;

    $u = $db->prepare("UPDATE approval_requests SET " . implode(', ', $set) . " WHERE id=?");
    $u->bind_param($types, ...$vals);
    $u->execute();
    $u->close();

    if (function_exists('noti_from_approval_updated')) {
      try {
        noti_from_approval_updated($db, $reqId, 'rejected', $uid);
      } catch (Throwable $e) {}
    }

    echo json_encode(['ok' => true, 'message' => 'Delete request rejected', 'status' => 'rejected']);
    exit;
  }

  /* APPROVE */
  $db->begin_transaction();

  $photo = '';
  if ($cExists($stuTbl, 'photograph')) {
    $q = $db->prepare("SELECT photograph FROM `$stuTbl` WHERE id=? LIMIT 1");
    $q->bind_param('i', $sid);
    $q->execute();
    $q->bind_result($photo);
    $q->fetch();
    $q->close();
    $photo = (string)$photo;
  }

  $centerRegNo = '';
  $refNo = '';
  $refNo2 = '';

  $pickCols = [];
  if ($cExists($stuTbl, 'center_reg_no')) $pickCols[] = 'center_reg_no';
  if ($cExists($stuTbl, 'reference_no'))  $pickCols[] = 'reference_no';
  if ($cExists($stuTbl, 'reference_no2')) $pickCols[] = 'reference_no2';

  if ($pickCols) {
    $sql = "SELECT " . implode(',', $pickCols) . " FROM `$stuTbl` WHERE id=? LIMIT 1";
    $q = $db->prepare($sql);
    $q->bind_param('i', $sid);
    $q->execute();
    $row = $q->get_result()->fetch_assoc() ?: [];
    $q->close();

    $centerRegNo = (string)($row['center_reg_no'] ?? '');
    $refNo       = (string)($row['reference_no'] ?? '');
    $refNo2      = (string)($row['reference_no2'] ?? '');
  }

  $rels = [
    ['payments', 'student_id'],
    ['payment_schedule', 'student_id'],
    ['installments', 'student_id'],
    ['student_fee_plans', 'student_id'],
    ['student_batches', 'student_id'],
    ['student_courses', 'student_id'],
    ['student_certifications', 'student_id'],
    ['student_verifications', 'student_id'],
    ['student_exams', 'student_id'],
    ['student_extra_fees', 'student_id'],
    ['certifications', 'student_id'],
    ['enrollments', 'student_id'],
    ['lms_enrollments', 'student_id'],
    ['lms_assessments', 'student_id'],
    ['lms_results', 'student_id'],
  ];

  foreach ($rels as [$t, $c]) {
    if ($cExists($t, $c)) {
      $x = $db->prepare("DELETE FROM `$t` WHERE `$c`=?");
      $x->bind_param('i', $sid);
      $x->execute();
      $x->close();
    }
  }

  // cleanup approval rows for same student if table supports it
  if ($cExists('approval_requests', 'student_id')) {
    $x = $db->prepare("DELETE FROM `approval_requests` WHERE `student_id`=? AND id<>?");
    $x->bind_param('ii', $sid, $reqId);
    $x->execute();
    $x->close();
  }

  // student_numbers cleanup
  if ($tExists('student_numbers')) {
    if ($cExists('student_numbers', 'student_id')) {
      $x = $db->prepare("DELETE FROM `student_numbers` WHERE `student_id`=?");
      $x->bind_param('i', $sid);
      $x->execute();
      $x->close();
    }

    if ($centerRegNo !== '' && $cExists('student_numbers', 'center_reg_no')) {
      $x = $db->prepare("DELETE FROM `student_numbers` WHERE `center_reg_no`=?");
      $x->bind_param('s', $centerRegNo);
      $x->execute();
      $x->close();
    }

    if ($refNo !== '' && $cExists('student_numbers', 'reference_no')) {
      $x = $db->prepare("DELETE FROM `student_numbers` WHERE `reference_no`=?");
      $x->bind_param('s', $refNo);
      $x->execute();
      $x->close();
    }

    if ($refNo2 !== '' && $cExists('student_numbers', 'reference_no2')) {
      $x = $db->prepare("DELETE FROM `student_numbers` WHERE `reference_no2`=?");
      $x->bind_param('s', $refNo2);
      $x->execute();
      $x->close();
    }
  }

  $d = $db->prepare("DELETE FROM `$stuTbl` WHERE id=? LIMIT 1");
  $d->bind_param('i', $sid);
  $d->execute();
  $d->close();

  // mark approved
  $cols = ['status' => 'approved'];
  if ($cExists('approval_requests', 'decided_by')) $cols['decided_by'] = $uid;
  if ($cExists('approval_requests', 'decided_at')) $cols['decided_at'] = date('Y-m-d H:i:s');
  if ($cExists('approval_requests', 'note') && $note !== '') $cols['note'] = $note;

  $set = [];
  $types = '';
  $vals = [];
  foreach ($cols as $k => $v) {
    $set[] = "`$k`=?";
    $types .= ($k === 'decided_by') ? 'i' : 's';
    $vals[] = $v;
  }
  $types .= 'i';
  $vals[] = $reqId;

  $u = $db->prepare("UPDATE approval_requests SET " . implode(', ', $set) . " WHERE id=?");
  $u->bind_param($types, ...$vals);
  $u->execute();
  $u->close();

  $db->commit();

  if ($photo !== '') {
    $uploads = realpath(__DIR__ . '/../uploads');
    if ($uploads) {
      $path = $uploads . DIRECTORY_SEPARATOR . basename($photo);
      if (is_file($path)) @unlink($path);
    }
  }

  if (function_exists('noti_from_approval_updated')) {
    try {
      noti_from_approval_updated($db, $reqId, 'approved', $uid);
    } catch (Throwable $e) {}
  }

  echo json_encode([
    'ok'      => true,
    'message' => 'Student deleted (approved).',
    'deleted' => true,
    'status'  => 'approved'
  ]);
  exit;

} catch (Throwable $e) {
  if (isset($db) && $db instanceof mysqli) {
    try { $db->rollback(); } catch (Throwable $e2) {}
  }
  http_response_code(200);
  echo json_encode(['ok' => false, 'error' => $e->getMessage()]);
  exit;
}