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

try {
  require_once __DIR__ . '/../includes/auth.php';
  if (function_exists('ec_session_start')) ec_session_start();

  $role = function_exists('ec_role') ? strtolower((string)ec_role()) : '';
  $hardDeleteRoles = ['admin', 'super_admin'];
  $isAdmin = in_array($role, $hardDeleteRoles, true);

  // DB
  $db = function_exists('ec_db') ? ec_db() : null;
  if (!$db instanceof mysqli) {
    throw new RuntimeException('DB connection not available');
  }

  $db->set_charset('utf8mb4');
  mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

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

  $sid    = (int)($_POST['student_id'] ?? $_POST['id'] ?? 0);
  $reason = trim((string)($_POST['reason'] ?? ''));

  if ($sid <= 0) {
    echo json_encode(['ok' => false, 'error' => 'Missing student_id']);
    exit;
  }

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

  // ---------- ADMIN => hard delete now ----------
  if ($isAdmin) {
    $db->begin_transaction();

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

    $pickCols = [];
    if ($cExists($stuTbl, 'photograph'))    $pickCols[] = 'photograph';
    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();

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

    $deleteMap = [
      ['payments', 'student_id'],
      ['payment_schedule', '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'],
      ['approval_requests', 'student_id'],
    ];

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

    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();
    $aff = $d->affected_rows;
    $d->close();

    $db->commit();

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

    echo json_encode([
      'ok'      => true,
      'hard'    => true,
      'message' => 'Student and all related records deleted successfully'
    ]);
    exit;
  }

  // ---------- STAFF => create approval request ----------
  $db->query("CREATE TABLE IF NOT EXISTS approval_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    type VARCHAR(64) NOT NULL,
    status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
    student_id INT NULL,
    payload JSON NULL,
    reason TEXT NULL,
    requested_by INT NULL,
    requested_by_name VARCHAR(191) NULL,
    requested_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    decided_by INT NULL,
    decided_at TIMESTAMP NULL,
    KEY idx_status (status),
    KEY idx_type (type),
    KEY idx_student (student_id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

  $snap = ['student_id' => $sid];
  foreach (['center_reg_no','reference_no','full_name','email','mobile'] as $c) {
    if ($cExists($stuTbl, $c)) $snap[$c] = null;
  }

  $pick = array_keys(array_diff_key($snap, ['student_id' => true]));
  if ($pick) {
    $sql = "SELECT " . implode(',', array_map(fn($c) => "`$c`", $pick)) . " FROM `$stuTbl` WHERE id=? LIMIT 1";
    $st = $db->prepare($sql);
    $st->bind_param('i', $sid);
    $st->execute();
    if ($r = $st->get_result()) {
      if ($row = $r->fetch_assoc()) $snap = array_merge($snap, $row);
    }
    $st->close();
  }

  if ($reason !== '') $snap['reason'] = $reason;
  $payload = json_encode($snap, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);

  $uid = (int)($_SESSION['user']['id'] ?? $_SESSION['admin']['id'] ?? $_SESSION['branch_admin']['id'] ?? $_SESSION['super_admin']['id'] ?? 0);
  $uname = (string)(
    $_SESSION['user']['full_name'] ??
    $_SESSION['admin']['full_name'] ??
    $_SESSION['branch_admin']['full_name'] ??
    $_SESSION['super_admin']['full_name'] ??
    $_SESSION['user']['name'] ??
    $_SESSION['admin']['name'] ??
    $_SESSION['user']['username'] ??
    $_SESSION['admin']['username'] ??
    ''
  );

  $cols  = ['type', 'status', 'student_id', 'payload'];
  $vals  = ['delete_student', 'pending', $sid, $payload];
  $types = 'ssis';

  if ($cExists('approval_requests', 'reason')) {
    $cols[] = 'reason';
    $vals[] = $reason;
    $types .= 's';
  }
  if ($cExists('approval_requests', 'requested_by')) {
    $cols[] = 'requested_by';
    $vals[] = (int)$uid;
    $types .= 'i';
  }
  if ($cExists('approval_requests', 'requested_by_name')) {
    $cols[] = 'requested_by_name';
    $vals[] = (string)$uname;
    $types .= 's';
  }

  $placeholders = implode(',', array_fill(0, count($cols), '?'));
  $sql = "INSERT INTO approval_requests (" . implode(',', $cols) . ") VALUES ($placeholders)";

  $st = $db->prepare($sql);
  $st->bind_param($types, ...$vals);
  $st->execute();
  $reqId = $st->insert_id;
  $st->close();

  echo json_encode([
    'ok'         => true,
    'hard'       => false,
    'request_id' => $reqId,
    'message'    => 'Delete request sent for approval.'
  ]);
  exit;

} catch (Throwable $e) {
  http_response_code(200);
  echo json_encode([
    'ok'    => false,
    'error' => $e->getMessage()
  ]);
}