<?php
// photo_helper.php — robust photo resolver without requiring a DB column

// Web base like "/admission3" computed from current script path
$__WEB_BASE = rtrim(dirname($_SERVER['SCRIPT_NAME'] ?? ''), '/');
if ($__WEB_BASE === '' || $__WEB_BASE === '.') $__WEB_BASE = '';

// Filesystem base = your app folder (where this file is)
$__APP_FS = rtrim(__DIR__, '/');

function __web_join($path) {
  global $__WEB_BASE;
  $prefix = rtrim($__WEB_BASE, '/');
  return ($prefix === '' ? '' : $prefix) . '/' . ltrim($path, '/');
}

/** Cache-aware check for a table column */
function db_column_exists(mysqli $db, string $table, string $column): bool {
  static $cache = [];
  $key = strtolower("$table.$column");
  if (array_key_exists($key, $cache)) return $cache[$key];

  $sql = "SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
          WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME = ?
          LIMIT 1";
  $exists = false;
  if ($stmt = $db->prepare($sql)) {
    $stmt->bind_param("ss", $table, $column);
    $stmt->execute();
    $exists = (bool)($stmt->get_result()->fetch_row()[0] ?? null);
    $stmt->close();
  }
  $cache[$key] = $exists;
  return $exists;
}

/**
 * Get a browser URL for a student's personal photo.
 * Order:
 *  1) students.personal_photo (if the column exists)
 *  2) uploads/<id>_P.(jpg|jpeg|png|webp)
 *  3) uploads/<id>.(jpg|jpeg|png|webp)
 *  4) img/avatar.png
 */
function get_student_photo_url(mysqli $db, string $studentId): string {
  global $__APP_FS;

  // 1) Optional DB column (skip if it doesn't exist)
  if (db_column_exists($db, 'students', 'personal_photo')) {
    if ($stmt = $db->prepare("SELECT personal_photo FROM students WHERE student_id=? LIMIT 1")) {
      $stmt->bind_param("s", $studentId);
      $stmt->execute();
      $val = (string)($stmt->get_result()->fetch_row()[0] ?? '');
      $stmt->close();
      if ($val !== '') {
        if (preg_match('~^https?://~i', $val) || $val[0] === '/') return $val; // absolute URL/path
        return __web_join($val); // relative under app base
      }
    }
  }

  // 2) Probe filesystem: new naming <id>_P.<ext>
  foreach (['jpg','jpeg','png','webp','JPG','JPEG','PNG','WEBP'] as $ext) {
    $rel = "uploads/{$studentId}_P.$ext";
    if (file_exists($__APP_FS . '/' . $rel)) return __web_join($rel);
  }

  // 3) Legacy naming <id>.<ext>
  foreach (['jpg','jpeg','png','webp','JPG','JPEG','PNG','WEBP'] as $ext) {
    $rel = "uploads/{$studentId}.$ext";
    if (file_exists($__APP_FS . '/' . $rel)) return __web_join($rel);
  }

  // 4) Fallback avatar
  return __web_join('img/avatar.png');
}

/* Back-compat shims */
if (!function_exists('get_student_photo')) {
  function get_student_photo(mysqli $db, string $studentId, string $attachmentsTable = 'student_attachments2') {
    return get_student_photo_url($db, $studentId);
  }
}
if (!function_exists('photo_or_fallback')) {
  function photo_or_fallback($raw, string $fallback = 'img/avatar.png') {
    $val = (string)$raw;
    if ($val === '') return __web_join($fallback);
    if (preg_match('~^https?://~i', $val) || $val[0] === '/') return $val;
    return __web_join($val);
  }
}
