<?php
declare(strict_types=1);

$configuredTimezone = trim((string)getenv('MAVELA_TIMEZONE'));
date_default_timezone_set($configuredTimezone !== '' ? $configuredTimezone : 'Africa/Harare');

$requestIsHttps = (!empty($_SERVER['HTTPS']) && strtolower((string)$_SERVER['HTTPS']) !== 'off')
  || (string)($_SERVER['HTTP_X_FORWARDED_PROTO'] ?? '') === 'https'
  || (string)($_SERVER['HTTP_X_FORWARDED_SSL'] ?? '') === 'on'
  || ((int)($_SERVER['SERVER_PORT'] ?? 0) === 443);

if (session_status() !== PHP_SESSION_ACTIVE) {
  ini_set('session.use_strict_mode', '1');
  ini_set('session.use_only_cookies', '1');
  ini_set('session.cookie_httponly', '1');
  ini_set('session.cookie_samesite', 'Lax');
  session_name('mavela_session');
  session_set_cookie_params([
    'lifetime' => 0,
    'path' => '/',
    'domain' => '',
    'secure' => $requestIsHttps,
    'httponly' => true,
    'samesite' => 'Lax',
  ]);
  if (headers_sent() === false) {
    session_start();
  } else {
    @session_start();
  }
}

require_once __DIR__ . '/Config.php';

if (!headers_sent()) {
  header_remove('X-Powered-By');
  header('X-Content-Type-Options: nosniff');
  header('X-Frame-Options: SAMEORIGIN');
  header('Referrer-Policy: strict-origin-when-cross-origin');
  header('Permissions-Policy: camera=(), microphone=(), geolocation=(), payment=()');
  header("Content-Security-Policy: default-src 'self'; base-uri 'self'; form-action 'self'; frame-ancestors 'self'; object-src 'none'; img-src 'self' data: https:; style-src 'self' 'unsafe-inline' https://fonts.googleapis.com; font-src 'self' https://fonts.gstatic.com data:; script-src 'self' 'unsafe-inline'; connect-src 'self'");
  if ($requestIsHttps && !MAVELA_IS_LOCAL) {
    header('Strict-Transport-Security: max-age=31536000; includeSubDomains');
  }
}

function csrf_token(): string {
  if (empty($_SESSION['csrf_token']) || !is_string($_SESSION['csrf_token'])) {
    $_SESSION['csrf_token'] = bin2hex(random_bytes(32));
  }
  return $_SESSION['csrf_token'];
}

function csrf_input(): string {
  return '<input type="hidden" name="_csrf_token" value="' . h(csrf_token()) . '">';
}

function csrf_request_is_valid(): bool {
  $submittedToken = (string)($_POST['_csrf_token'] ?? ($_SERVER['HTTP_X_CSRF_TOKEN'] ?? ''));
  return $submittedToken !== '' && hash_equals(csrf_token(), $submittedToken);
}

function enforce_csrf_protection(): void {
  if (strtoupper((string)($_SERVER['REQUEST_METHOD'] ?? 'GET')) !== 'POST') {
    return;
  }
  if (csrf_request_is_valid()) {
    return;
  }

  http_response_code(419);
  header('Cache-Control: no-store, max-age=0');
  exit('<!doctype html><html lang="en"><head><meta charset="utf-8"><meta name="viewport" content="width=device-width,initial-scale=1"><title>Session expired</title></head><body><main style="max-width:620px;margin:12vh auto;padding:28px;font:16px/1.6 system-ui;color:#173122"><h1>Session expired</h1><p>This form was open for too long or failed a security check. Your data was not changed.</p><p><a href="javascript:history.back()">Return to the previous page</a>, refresh it, and submit again.</p></main></body></html>');
}

function mavela_demo_mode(): bool {
  $configured = strtolower(trim((string)getenv('MAVELA_DEMO_MODE')));
  if ($configured !== '') {
    return in_array($configured, ['1', 'true', 'yes', 'on'], true);
  }
  return MAVELA_IS_LOCAL;
}

function mavela_password_is_strong(string $password): bool {
  return strlen($password) >= 10
    && preg_match('/[A-Z]/', $password) === 1
    && preg_match('/[a-z]/', $password) === 1
    && preg_match('/\d/', $password) === 1;
}

function mavela_generate_temporary_password(): string {
  return 'Mv!' . strtoupper(bin2hex(random_bytes(2))) . '-' . strtolower(bin2hex(random_bytes(3))) . '7';
}

function mavela_exception_detail(Throwable $exception): string {
  if (MAVELA_IS_LOCAL) {
    return $exception->getMessage();
  }
  $reference = strtoupper(substr(hash('sha256', $exception->getMessage() . '|' . microtime(true)), 0, 10));
  error_log("Mavela error {$reference}: " . $exception->getMessage() . ' in ' . $exception->getFile() . ':' . $exception->getLine());
  return "A server error occurred (reference {$reference}).";
}

function mavela_transform_html(string $html): string {
  if (stripos($html, '<html') === false || stripos($html, '<body') === false) {
    return $html;
  }

  $tokenField = '<input type="hidden" name="_csrf_token" value="' . h(csrf_token()) . '">';
  $html = preg_replace_callback(
    '/<form\b(?=[^>]*\bmethod\s*=\s*(?:"post"|\'post\'|post)(?:\s|>))[^>]*>/i',
    static fn(array $match): string => $match[0] . $tokenField,
    $html
  ) ?? $html;

  if (stripos($html, 'data-mavela-modern-ui') === false) {
    $headAssets = '<meta name="color-scheme" content="light dark" data-mavela-modern-ui>'
      . '<link rel="stylesheet" href="Assets/modern-ui.css?v=20260730">';
    $html = preg_replace('/<\/head>/i', $headAssets . '</head>', $html, 1) ?? $html;

    $role = preg_replace('/[^a-z0-9_-]/i', '', (string)($_SESSION['role'] ?? $_SESSION['tp_role'] ?? 'guest'));
    $passwordNotice = !empty($_SESSION['must_change_password'])
      ? '<div class="mavela-security-notice" role="alert"><strong>Security action required:</strong> change the temporary password in your profile before continuing with sensitive work.</div>'
      : '';
    $skipLink = '<a class="mavela-skip-link" href="#mavela-main-content">Skip to main content</a>' . $passwordNotice;
    $html = preg_replace('/<body([^>]*)>/i', '<body$1 data-user-role="' . h($role) . '">' . $skipLink, $html, 1) ?? $html;
    // Inject before the LAST </body> so document.write() template literals inside
    // <script> blocks (which also contain </body>) don't receive the injection.
    $lastBodyPos = strrpos(strtolower($html), '</body>');
    if ($lastBodyPos !== false) {
      $html = substr($html, 0, $lastBodyPos)
        . '<script src="Assets/modern-ui.js?v=20260730" defer></script></body>'
        . substr($html, $lastBodyPos + strlen('</body>'));
    }
  }

  return $html;
}

enforce_csrf_protection();
csrf_token();
if (!defined('MAVELA_HTML_TRANSFORM_STARTED')) {
  define('MAVELA_HTML_TRANSFORM_STARTED', true);
  ob_start('mavela_transform_html');
}

function h($value){
  return htmlspecialchars((string)$value, ENT_QUOTES, 'UTF-8');
}

function check_login($role = null){
  if(!isset($_SESSION['user_id']) || !isset($_SESSION['role'])){
    header('Location: index.php');
    exit;
  }

  // Dashboards show live data (enrolment counts, balances, etc.) - prevent
  // browsers, proxies, and any CDN/edge cache from serving a stale copy.
  header('Cache-Control: no-store, no-cache, must-revalidate, max-age=0');
  header('Pragma: no-cache');
  header('Expires: 0');

  $isAllowed = true;
  if ($role !== null) {
    if (is_array($role)) {
      $isAllowed = in_array($_SESSION['role'], $role, true);
    } else {
      $isAllowed = $_SESSION['role'] === $role;
    }
  }

  if(!$isAllowed){
    if($_SESSION['role'] === 'teacher'){
      header('Location: teacher.php');
      exit;
    }
    if($_SESSION['role'] === 'hop'){
      header('Location: hop.php');
      exit;
    }
    if($_SESSION['role'] === 'admin'){
      header('Location: admin.php');
      exit;
    }
    if($_SESSION['role'] === 'accountant'){
      header('Location: accountant.php');
      exit;
    }
    if($_SESSION['role'] === 'ancillary'){
      header('Location: ancillary.php');
      exit;
    }
    if($_SESSION['role'] === 'learner'){
      header('Location: learner.php');
      exit;
    }
    if($_SESSION['role'] === 'parent'){
      header('Location: parent.php');
      exit;
    }

    logout_user();
    header('Location: index.php');
    exit;
  }

  // Ensure common workflow tables exist and run annual promotion when due.
  try {
    global $pdo;
    if ($pdo instanceof PDO) {
      ensure_auth_security_columns($pdo);
      ensure_workflow_tables($pdo);
      auto_promote_learners_if_due($pdo);
    }
  } catch (Throwable $e) {
    // Keep auth flow resilient even if maintenance tasks fail.
  }
}

function login_user($user){
  session_regenerate_id(true);
  $_SESSION['user_id'] = $user['id'];
  $_SESSION['name'] = $user['name'];
  $_SESSION['role'] = $user['role'];
  $_SESSION['must_change_password'] = !empty($user['must_change_password']);

  // Refresh FX rates at login so accounting uses current conversion values.
  try {
    global $pdo;
    if ($pdo instanceof PDO) {
      refresh_exchange_rates_online($pdo, true);
    }
  } catch (Throwable $e) {
    // Keep login resilient even when rate service is unavailable.
  }
}

function logout_user(){
  $_SESSION = [];
  if (ini_get('session.use_cookies')) {
    $params = session_get_cookie_params();
    setcookie(session_name(), '', time() - 42000, $params['path'], $params['domain'], $params['secure'], $params['httponly']);
  }
  session_destroy();
}

function output_auto_capitalize_script(): void {
  echo '<script>
    document.addEventListener("DOMContentLoaded", function () {
      const shouldAutoCapitalize = (element) => {
        if (!element) {
          return false;
        }
        const tag = (element.tagName || "").toLowerCase();
        if (!["input", "textarea"].includes(tag)) {
          return false;
        }
        const type = (element.type || "text").toLowerCase();
        if (["email", "password", "number", "date", "datetime-local", "file", "hidden", "checkbox", "radio", "search", "tel", "url"].includes(type)) {
          return false;
        }
        const name = (element.name || "").toLowerCase();
        const id = (element.id || "").toLowerCase();
        if (name.includes("email") || name.includes("username") || name.includes("password") || id.includes("email") || id.includes("username") || id.includes("password")) {
          return false;
        }
        return true;
      };
      const applyAutoCapitalize = (element) => {
        if (!shouldAutoCapitalize(element)) {
          return;
        }
        element.value = (element.value || "").toUpperCase();
      };
      document.querySelectorAll("input, textarea").forEach((element) => {
        if (shouldAutoCapitalize(element)) {
          element.addEventListener("input", function () {
            applyAutoCapitalize(this);
          });
          element.addEventListener("blur", function () {
            applyAutoCapitalize(this);
          });
        }
      });
      document.addEventListener("submit", function () {
        document.querySelectorAll("input, textarea").forEach((element) => {
          applyAutoCapitalize(element);
        });
      }, true);
    });
  </script>';
}


function output_select_search_script(): void {
  echo <<<'HTML'
<style>
  .auto-select-search-wrap {
    display: grid;
    gap: 6px;
    width: 100%;
    min-width: 0;
  }

  .auto-select-search-wrap input[type="search"] {
    width: 100%;
    min-width: 0;
  }
</style>
<script>
  (function () {
    function derivePlaceholder(select) {
      const label = select.id ? document.querySelector('label[for="' + select.id + '"]') : null;
      const labelText = label ? label.textContent.trim().replace(/\s+/g, ' ') : '';
      if (labelText !== '') {
        return 'Search ' + labelText.toLowerCase().replace(/^select\s+/, '') + '...';
      }
      return 'Search options...';
    }

    function shouldEnhance(select) {
      if (!select || select.multiple || select.dataset.selectSearchEnhanced === '1' || select.dataset.skipSelectSearch === '1') {
        return false;
      }

      if (select.closest('.record-filter-bar') || select.closest('table') || select.closest('.auto-select-search-wrap')) {
        return false;
      }

      const field = select.closest('.field');
      if (field && field.querySelector('input[type="search"]')) {
        return false;
      }

      const parent = select.parentElement;
      if (parent) {
        const hasSiblingSearch = Array.from(parent.children).some(function (child) {
          return child !== select
            && child.tagName === 'INPUT'
            && String(child.type || '').toLowerCase() === 'search';
        });
        if (hasSiblingSearch) {
          return false;
        }
      }

      const realOptionCount = Array.from(select.options).filter(function (option) {
        return String(option.value || '').trim() !== '';
      }).length;

      return realOptionCount > 1;
    }

    function enhanceSelect(select) {
      const wrapper = document.createElement('div');
      wrapper.className = 'auto-select-search-wrap';

      const search = document.createElement('input');
      search.type = 'search';
      search.className = 'auto-select-search';
      search.placeholder = derivePlaceholder(select);
      search.autocomplete = 'off';

      const parent = select.parentNode;
      if (!parent) {
        return;
      }

      parent.insertBefore(wrapper, select);
      wrapper.appendChild(search);
      wrapper.appendChild(select);
      select.dataset.selectSearchEnhanced = '1';

      search.addEventListener('keydown', function (event) {
        if (event.key === 'ArrowDown') {
          event.preventDefault();
          select.focus();
        }
      });

      search.addEventListener('input', function () {
        const term = search.value.trim().toLowerCase();
        Array.from(select.options).forEach(function (option, index) {
          if (index === 0 || String(option.value || '').trim() === '') {
            option.hidden = false;
            return;
          }

          const optionText = String(option.textContent || '').toLowerCase();
          option.hidden = term !== '' && !optionText.includes(term);
        });
      });
    }

    function bootSelectSearches() {
      Array.from(document.querySelectorAll('select')).forEach(function (select) {
        if (shouldEnhance(select)) {
          enhanceSelect(select);
        }
      });
    }

    if (document.readyState === 'loading') {
      document.addEventListener('DOMContentLoaded', bootSelectSearches);
    } else {
      bootSelectSearches();
    }
  })();
</script>
HTML;
}
function get_client_ip_address(): string {
  $trustProxy = in_array(strtolower(trim((string)getenv('MAVELA_TRUST_PROXY'))), ['1', 'true', 'yes', 'on'], true);
  $keys = $trustProxy
    ? ['HTTP_CF_CONNECTING_IP', 'HTTP_X_FORWARDED_FOR', 'HTTP_X_REAL_IP', 'REMOTE_ADDR']
    : ['REMOTE_ADDR'];
  foreach ($keys as $key) {
    $value = trim((string)($_SERVER[$key] ?? ''));
    if ($value !== '') {
      if (strpos($value, ',') !== false) {
        $value = trim(strstr($value, ',', true) ?: $value);
      }
      return $value;
    }
  }
  return 'unknown';
}

function ensure_auth_security_columns(PDO $pdo): void {
  try {
    $columnStmt = $pdo->query("SHOW COLUMNS FROM users LIKE 'must_change_password'");
    if (!$columnStmt->fetch()) {
      $pdo->exec("ALTER TABLE users ADD COLUMN must_change_password TINYINT(1) NOT NULL DEFAULT 0 AFTER password_hash");
      // Accounts from older releases may have used shared starter passwords.
      $pdo->exec("UPDATE users SET must_change_password = 1");
    }
  } catch (Throwable $e) {
    // The table may not exist during the very first installation request.
  }
}

function ensure_login_attempts_table(PDO $pdo): void {
  $pdo->exec("CREATE TABLE IF NOT EXISTS login_attempts (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    identifier_hash CHAR(64) NOT NULL,
    ip_address VARCHAR(80) NOT NULL,
    attempted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_login_attempts_lookup (identifier_hash, ip_address, attempted_at),
    INDEX idx_login_attempts_time (attempted_at)
  )");
}

function login_attempt_is_blocked(PDO $pdo, string $identifier, int $maxAttempts = 5, int $windowMinutes = 15): bool {
  try {
    ensure_login_attempts_table($pdo);
    $identifierHash = hash('sha256', strtolower(trim($identifier)));
    $ipAddress = get_client_ip_address();
    $stmt = $pdo->prepare("SELECT COUNT(*) FROM login_attempts
      WHERE identifier_hash = ? AND ip_address = ?
        AND attempted_at >= (CURRENT_TIMESTAMP - INTERVAL {$windowMinutes} MINUTE)");
    $stmt->execute([$identifierHash, $ipAddress]);
    return (int)$stmt->fetchColumn() >= $maxAttempts;
  } catch (Throwable $e) {
    error_log('Mavela login-rate check failed: ' . $e->getMessage());
    return false;
  }
}

function record_login_failure(PDO $pdo, string $identifier): void {
  try {
    ensure_login_attempts_table($pdo);
    $pdo->exec("DELETE FROM login_attempts WHERE attempted_at < (CURRENT_TIMESTAMP - INTERVAL 2 DAY)");
    $stmt = $pdo->prepare("INSERT INTO login_attempts (identifier_hash, ip_address) VALUES (?, ?)");
    $stmt->execute([hash('sha256', strtolower(trim($identifier))), get_client_ip_address()]);
  } catch (Throwable $e) {
    error_log('Mavela login failure could not be recorded: ' . $e->getMessage());
  }
}

function clear_login_failures(PDO $pdo, string $identifier): void {
  try {
    ensure_login_attempts_table($pdo);
    $stmt = $pdo->prepare("DELETE FROM login_attempts WHERE identifier_hash = ? AND ip_address = ?");
    $stmt->execute([hash('sha256', strtolower(trim($identifier))), get_client_ip_address()]);
  } catch (Throwable $e) {
    error_log('Mavela login failures could not be cleared: ' . $e->getMessage());
  }
}

function ensure_system_logs_table(PDO $pdo): void {
  $pdo->exec("CREATE TABLE IF NOT EXISTS system_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    actor_id INT NULL,
    actor_role VARCHAR(40) NULL,
    action VARCHAR(120) NOT NULL,
    details TEXT NULL,
    entity_type VARCHAR(80) NULL,
    entity_id INT NULL,
    ip_address VARCHAR(80) NULL,
    INDEX idx_system_logs_created_at (created_at),
    INDEX idx_system_logs_action (action)
  )");
}

function append_system_log(PDO $pdo, string $action, string $details = '', ?int $actorId = null, ?string $actorRole = null, ?string $entityType = null, ?int $entityId = null, ?string $ipAddress = null): void {
  ensure_system_logs_table($pdo);

  $stmt = $pdo->prepare("INSERT INTO system_logs (actor_id, actor_role, action, details, entity_type, entity_id, ip_address) VALUES (?, ?, ?, ?, ?, ?, ?)");
  $stmt->execute([
    $actorId,
    $actorRole,
    $action,
    $details,
    $entityType,
    $entityId,
    $ipAddress ?? get_client_ip_address()
  ]);
}

function normalize_whatsapp_phone(string $rawPhone): ?string {
  $clean = preg_replace('/[^0-9+]/', '', trim($rawPhone));
  if ($clean === null || $clean === '') {
    return null;
  }

  if (strpos($clean, '00') === 0) {
    $clean = '+' . substr($clean, 2);
  }

  if (strpos($clean, '+') === 0) {
    $digits = preg_replace('/\D/', '', $clean);
    if ($digits === null || strlen($digits) < 8 || strlen($digits) > 15) {
      return null;
    }
    return '+' . $digits;
  }

  if (strpos($clean, '263') === 0) {
    $digits = preg_replace('/\D/', '', $clean);
    return '+' . $digits;
  }

  if (strpos($clean, '0') === 0) {
    $digits = preg_replace('/\D/', '', $clean);
    if ($digits === null || strlen($digits) < 9) {
      return null;
    }
    return '+263' . substr($digits, 1);
  }

  $digits = preg_replace('/\D/', '', $clean);
  if ($digits === null || strlen($digits) < 8 || strlen($digits) > 15) {
    return null;
  }
  return '+' . $digits;
}

function normalize_person_name_for_match(string $value): string {
  $cleaned = preg_replace('/\s+/', ' ', trim($value));
  if ($cleaned === null || $cleaned === '') {
    return '';
  }

  if (function_exists('mb_strtolower')) {
    return mb_strtolower($cleaned, 'UTF-8');
  }

  return strtolower($cleaned);
}

function normalize_person_dob_for_match(string $value): ?string {
  $candidate = trim($value);
  if ($candidate === '') {
    return null;
  }

  if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $candidate)) {
    return null;
  }

  $date = DateTimeImmutable::createFromFormat('!Y-m-d', $candidate);
  if (!$date || $date->format('Y-m-d') !== $candidate) {
    return null;
  }

  return $candidate;
}

function build_full_name_from_parts(string $name, string $surname): string {
  $parts = array_values(array_filter(array_map('trim', preg_split('/\s+/', $name . ' ' . $surname)), static function ($part): bool {
    return $part !== '';
  }));

  return implode(' ', $parts);
}

function find_duplicate_teacher_by_name_and_dob(PDO $pdo, string $fullName, ?string $dobValue, int $excludeUserId = 0): ?array {
  $stmt = $pdo->prepare("SELECT u.id, u.name, sp.date_of_birth FROM users u LEFT JOIN staff_profiles sp ON sp.user_id = u.id WHERE u.role = 'teacher' AND u.id <> ? ORDER BY u.id");
  $stmt->execute([$excludeUserId]);

  $normalizedFullName = normalize_person_name_for_match($fullName);
  $normalizedDob = normalize_person_dob_for_match((string)($dobValue ?? ''));

  foreach ($stmt->fetchAll() as $row) {
    $candidateName = normalize_person_name_for_match((string)($row['name'] ?? ''));
    $candidateDob = normalize_person_dob_for_match((string)($row['date_of_birth'] ?? ''));

    if ($normalizedFullName !== '' && $candidateName === $normalizedFullName) {
      if ($normalizedDob === null || $candidateDob === null || $normalizedDob === $candidateDob) {
        return $row;
      }
    }
  }

  return null;
}

function find_duplicate_learner_by_name_and_dob(PDO $pdo, string $surname, string $christianNames, ?string $dobValue, int $excludeLearnerId = 0): ?array {
  $stmt = $pdo->prepare("SELECT id, surname, christian_names, date_of_birth_info FROM learners WHERE id <> ? ORDER BY id");
  $stmt->execute([$excludeLearnerId]);

  $normalizedSurname = normalize_person_name_for_match($surname);
  $normalizedChristianNames = normalize_person_name_for_match($christianNames);
  $normalizedDob = normalize_person_dob_for_match((string)($dobValue ?? ''));

  foreach ($stmt->fetchAll() as $row) {
    $candidateSurname = normalize_person_name_for_match((string)($row['surname'] ?? ''));
    $candidateChristianNames = normalize_person_name_for_match((string)($row['christian_names'] ?? ''));
    $candidateDob = normalize_person_dob_for_match((string)($row['date_of_birth_info'] ?? ''));

    if ($normalizedSurname !== '' && $normalizedChristianNames !== '' && $candidateSurname === $normalizedSurname && $candidateChristianNames === $normalizedChristianNames) {
      if ($normalizedDob === null || $candidateDob === null || $normalizedDob === $candidateDob) {
        return $row;
      }
    }
  }

  return null;
}

function get_app_setting(PDO $pdo, string $settingKey, string $defaultValue = ''): string {
  try {
    $stmt = $pdo->prepare("SELECT setting_value FROM app_settings WHERE setting_key = ? LIMIT 1");
    $stmt->execute([$settingKey]);
    $value = $stmt->fetchColumn();
    if ($value === false || $value === null) {
      return $defaultValue;
    }
    return (string)$value;
  } catch (Throwable $e) {
    return $defaultValue;
  }
}

function set_app_setting(PDO $pdo, string $settingKey, string $settingValue, ?int $updatedBy = null): bool {
  try {
    $stmt = $pdo->prepare("INSERT INTO app_settings (setting_key, setting_value, updated_by)
      VALUES (?, ?, ?)
      ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value), updated_by = VALUES(updated_by), updated_at = CURRENT_TIMESTAMP");
    $stmt->execute([$settingKey, $settingValue, $updatedBy]);
    return true;
  } catch (Throwable $e) {
    return false;
  }
}

function normalize_school_hex_color(string $value, string $fallback): string {
  $candidate = strtoupper(trim($value));
  if (preg_match('/^#[0-9A-F]{6}$/', $candidate)) {
    return $candidate;
  }
  return strtoupper($fallback);
}

function get_school_theme_palette(PDO $pdo): array {
  $themePresets = [
    'forest' => [
      'bg_cream' => '#F7FBF7',
      'bg_sand' => '#E5F0E6',
      'ink' => '#173122',
      'muted' => '#4F6656',
      'brand_navy' => '#2E8B45',
      'brand_navy_deep' => '#1D6A33',
      'brand_gold' => '#1E1A1B',
      'brand_gold_soft' => '#D8DDD8',
      'brand_ice' => '#EEF7EF',
      'patch_sky' => '#7CC9FF',
      'patch_sky_soft' => '#D6EEFF',
      'patch_yellow' => '#FFD447',
      'patch_yellow_soft' => '#FFF2BF'
    ],
    'heritage' => [
      'bg_cream' => '#F6EFE0',
      'bg_sand' => '#EADCC0',
      'ink' => '#0A2F63',
      'muted' => '#4F5F7A',
      'brand_navy' => '#0A3B79',
      'brand_navy_deep' => '#082E60',
      'brand_gold' => '#F8B400',
      'brand_gold_soft' => '#D9C39A',
      'brand_ice' => '#E8EEF8',
      'patch_sky' => '#7CC9FF',
      'patch_sky_soft' => '#D6EEFF',
      'patch_yellow' => '#FFD447',
      'patch_yellow_soft' => '#FFF2BF'
    ],
    'sunset' => [
      'bg_cream' => '#FFF2E7',
      'bg_sand' => '#FFDCC5',
      'ink' => '#5A260F',
      'muted' => '#7A4B37',
      'brand_navy' => '#B74F2A',
      'brand_navy_deep' => '#8A3518',
      'brand_gold' => '#F4A300',
      'brand_gold_soft' => '#EFC48D',
      'brand_ice' => '#FFE7D4',
      'patch_sky' => '#FFB486',
      'patch_sky_soft' => '#FFDCC0',
      'patch_yellow' => '#FFD447',
      'patch_yellow_soft' => '#FFF0C7'
    ]
  ];

  $defaultPreset = 'forest';
  $presetKey = trim(get_app_setting($pdo, 'school_theme_preset', $defaultPreset));
  if (!array_key_exists($presetKey, $themePresets)) {
    $presetKey = $defaultPreset;
  }

  $preset = $themePresets[$presetKey];
  $palette = [];
  foreach ($preset as $key => $defaultValue) {
    $palette[$key] = normalize_school_hex_color(
      get_app_setting($pdo, 'palette_' . $key, $defaultValue),
      $defaultValue
    );
  }

  return $palette;
}

function ensure_workflow_tables(PDO $pdo): void {
  $pdo->exec("CREATE TABLE IF NOT EXISTS learner_promotions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    learner_id INT NOT NULL,
    from_class_id INT NOT NULL,
    to_class_id INT NOT NULL,
    promotion_year INT NOT NULL,
    promoted_at DATETIME NOT NULL,
    note VARCHAR(255) NULL,
    CONSTRAINT fk_lp_learner FOREIGN KEY (learner_id) REFERENCES learners(id) ON DELETE CASCADE,
    INDEX idx_lp_year (promotion_year),
    INDEX idx_lp_learner (learner_id)
  )");

  $pdo->exec("CREATE TABLE IF NOT EXISTS transfer_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    parent_user_id INT NOT NULL,
    learner_id INT NOT NULL,
    destination_school VARCHAR(180) NOT NULL,
    reason TEXT NOT NULL,
    requested_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status ENUM('pending_admin','clearance_requested','approved','rejected','completed') NOT NULL DEFAULT 'pending_admin',
    teacher_status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
    teacher_comment TEXT NULL,
    teacher_reviewed_by INT NULL,
    teacher_reviewed_at DATETIME NULL,
    accountant_status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
    accountant_comment TEXT NULL,
    accountant_reviewed_by INT NULL,
    accountant_reviewed_at DATETIME NULL,
    admin_comment TEXT NULL,
    reviewed_by_admin INT NULL,
    reviewed_at_admin DATETIME NULL,
    CONSTRAINT fk_tr_parent FOREIGN KEY (parent_user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_tr_learner FOREIGN KEY (learner_id) REFERENCES learners(id) ON DELETE CASCADE,
    INDEX idx_tr_status (status),
    INDEX idx_tr_parent (parent_user_id),
    INDEX idx_tr_learner (learner_id)
  )");

  $pdo->exec("CREATE TABLE IF NOT EXISTS transfer_letters (
    id INT AUTO_INCREMENT PRIMARY KEY,
    transfer_request_id INT NOT NULL UNIQUE,
    letter_html LONGTEXT NOT NULL,
    generated_by INT NULL,
    generated_at DATETIME NOT NULL,
    CONSTRAINT fk_tl_request FOREIGN KEY (transfer_request_id) REFERENCES transfer_requests(id) ON DELETE CASCADE
  )");

  $pdo->exec("CREATE TABLE IF NOT EXISTS teacher_leave_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    teacher_id INT NOT NULL,
    leave_type VARCHAR(60) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    reason TEXT NOT NULL,
    supporting_document_path VARCHAR(255) NULL,
    supporting_document_name VARCHAR(255) NULL,
    supporting_document_mime VARCHAR(120) NULL,
    supporting_document_size BIGINT NULL,
    status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
    admin_comment TEXT NULL,
    reviewed_by INT NULL,
    reviewed_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_tlr_teacher FOREIGN KEY (teacher_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_tlr_teacher (teacher_id),
    INDEX idx_tlr_status (status)
  )");

  $pdo->exec("CREATE TABLE IF NOT EXISTS ancillary_leave_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ancillary_id INT NOT NULL,
    leave_type VARCHAR(60) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    reason TEXT NOT NULL,
    supporting_document_path VARCHAR(255) NULL,
    supporting_document_name VARCHAR(255) NULL,
    supporting_document_mime VARCHAR(120) NULL,
    supporting_document_size BIGINT NULL,
    status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
    admin_comment TEXT NULL,
    reviewed_by INT NULL,
    reviewed_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_alr_ancillary FOREIGN KEY (ancillary_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_alr_ancillary (ancillary_id),
    INDEX idx_alr_status (status)
  )");

  $pdo->exec("CREATE TABLE IF NOT EXISTS ancillary_salary_register (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ancillary_id INT NOT NULL,
    salary_month CHAR(7) NOT NULL,
    base_salary DECIMAL(12,2) NOT NULL DEFAULT 0,
    allowance_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
    deduction_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
    net_salary DECIMAL(12,2) NOT NULL DEFAULT 0,
    pay_date DATE NULL,
    note VARCHAR(255) NULL,
    recorded_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_asr_ancillary FOREIGN KEY (ancillary_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_asr_recorded_by FOREIGN KEY (recorded_by) REFERENCES users(id) ON DELETE SET NULL,
    UNIQUE KEY uq_asr_worker_month (ancillary_id, salary_month),
    INDEX idx_asr_month (salary_month)
  )");

  $pdo->exec("CREATE TABLE IF NOT EXISTS learner_applications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    surname VARCHAR(100) NOT NULL,
    christian_names VARCHAR(150) NOT NULL,
    full_name VARCHAR(150) NOT NULL,
    date_of_birth_info VARCHAR(40) NULL,
    birth_certificate_number VARCHAR(80) NULL,
    religious_denomination VARCHAR(120) NULL,
    gender ENUM('Male','Female','Other') NOT NULL DEFAULT 'Other',
    date_of_admission DATE NULL,
    guardian_name VARCHAR(120) NULL,
    guardian_national_id VARCHAR(60) NULL,
    guardian_address VARCHAR(255) NULL,
    guardian_phone VARCHAR(40) NULL,
    parent_email VARCHAR(150) NULL,
    parent_whatsapp_phone VARCHAR(40) NULL,
    preferred_class_id INT NOT NULL,
    assigned_class_id INT NULL,
    assigned_teacher_id INT NULL,
    student_photo_path VARCHAR(255) NULL,
    status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
    admin_comment TEXT NULL,
    reviewed_by INT NULL,
    reviewed_at DATETIME NULL,
    offer_letter_html LONGTEXT NULL,
    offer_sent_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_la_preferred_class FOREIGN KEY (preferred_class_id) REFERENCES classes(id) ON DELETE CASCADE,
    CONSTRAINT fk_la_assigned_class FOREIGN KEY (assigned_class_id) REFERENCES classes(id) ON DELETE SET NULL,
    CONSTRAINT fk_la_assigned_teacher FOREIGN KEY (assigned_teacher_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_la_status (status),
    INDEX idx_la_created (created_at)
  )");

  try {
    $pdo->exec("ALTER TABLE learners ADD COLUMN assigned_teacher_id INT NULL");
  } catch (PDOException $e) {
    // Ignore duplicate column errors.
  }
  try {
    $pdo->exec("ALTER TABLE learners ADD COLUMN birth_certificate_number VARCHAR(80) NULL");
  } catch (PDOException $e) {
    // Ignore duplicate column errors.
  }
  try {
    $pdo->exec("ALTER TABLE admissions_pending ADD COLUMN birth_certificate_number VARCHAR(80) NULL");
  } catch (PDOException $e) {
    // Ignore duplicate column errors.
  }
  try {
    $pdo->exec("ALTER TABLE learner_applications ADD COLUMN birth_certificate_number VARCHAR(80) NULL");
  } catch (PDOException $e) {
    // Ignore duplicate column errors.
  }
  try {
    $pdo->exec("ALTER TABLE learners ADD COLUMN is_frozen TINYINT(1) NOT NULL DEFAULT 0");
  } catch (PDOException $e) {
    // Ignore duplicate column errors.
  }
  try {
    $pdo->exec("ALTER TABLE learners ADD COLUMN frozen_reason VARCHAR(255) NULL");
  } catch (PDOException $e) {
    // Ignore duplicate column errors.
  }
  try {
    $pdo->exec("ALTER TABLE learners ADD COLUMN frozen_at DATETIME NULL");
  } catch (PDOException $e) {
    // Ignore duplicate column errors.
  }
  try {
    $pdo->exec("ALTER TABLE learners ADD INDEX idx_learners_is_frozen (is_frozen)");
  } catch (PDOException $e) {
    // Ignore duplicate index errors.
  }
  try {
    $pdo->exec("ALTER TABLE teacher_leave_requests ADD COLUMN supporting_document_path VARCHAR(255) NULL");
  } catch (PDOException $e) {
    // Ignore duplicate column errors.
  }
  try {
    $pdo->exec("ALTER TABLE teacher_leave_requests ADD COLUMN supporting_document_name VARCHAR(255) NULL");
  } catch (PDOException $e) {
    // Ignore duplicate column errors.
  }
  try {
    $pdo->exec("ALTER TABLE teacher_leave_requests ADD COLUMN supporting_document_mime VARCHAR(120) NULL");
  } catch (PDOException $e) {
    // Ignore duplicate column errors.
  }
  try {
    $pdo->exec("ALTER TABLE teacher_leave_requests ADD COLUMN supporting_document_size BIGINT NULL");
  } catch (PDOException $e) {
    // Ignore duplicate column errors.
  }
  try {
    $pdo->exec("ALTER TABLE learners ADD CONSTRAINT fk_learners_assigned_teacher FOREIGN KEY (assigned_teacher_id) REFERENCES users(id) ON DELETE SET NULL");
  } catch (PDOException $e) {
    // Ignore if constraint already exists or cannot be added safely.
  }
}

function auto_promote_learners_if_due(PDO $pdo): array {
  $today = date('Y-m-d');
  $year = (int)date('Y');
  if (date('m-d', strtotime($today)) !== '01-01') {
    return ['ok' => true, 'skipped' => true, 'reason' => 'not_january_first'];
  }

  $lastYear = (int)get_app_setting($pdo, 'last_auto_promotion_year', '0');
  if ($lastYear === $year) {
    return ['ok' => true, 'skipped' => true, 'reason' => 'already_run'];
  }

  $classes = $pdo->query("SELECT id FROM classes ORDER BY id ASC")->fetchAll();
  if (count($classes) < 2) {
    set_app_setting($pdo, 'last_auto_promotion_year', (string)$year, null);
    return ['ok' => true, 'skipped' => true, 'reason' => 'insufficient_classes'];
  }

  $classIds = array_map(static fn($row) => (int)$row['id'], $classes);
  $promotions = [];
  for ($i = 0; $i < count($classIds) - 1; $i++) {
    $promotions[] = ['from' => $classIds[$i], 'to' => $classIds[$i + 1]];
  }

  $movedCount = 0;
  try {
    $pdo->beginTransaction();

    $learnerFetch = $pdo->prepare("SELECT id FROM learners WHERE class_id = ? AND COALESCE(is_frozen, 0) = 0");
    $insertLog = $pdo->prepare("INSERT INTO learner_promotions
      (learner_id, from_class_id, to_class_id, promotion_year, promoted_at, note)
      VALUES (?, ?, ?, ?, NOW(), ?)");
    $updateClass = $pdo->prepare("UPDATE learners SET class_id = ? WHERE id = ?");

    foreach ($promotions as $step) {
      $learnerFetch->execute([$step['from']]);
      $rows = $learnerFetch->fetchAll();
      foreach ($rows as $row) {
        $learnerId = (int)$row['id'];
        $updateClass->execute([$step['to'], $learnerId]);
        $insertLog->execute([$learnerId, $step['from'], $step['to'], $year, 'Auto-promoted on 1 January. Existing balances carried forward.']);
        $movedCount++;
      }
    }

    set_app_setting($pdo, 'last_auto_promotion_year', (string)$year, null);
    $pdo->commit();
  } catch (Throwable $e) {
    if ($pdo->inTransaction()) {
      $pdo->rollBack();
    }
    return ['ok' => false, 'error' => $e->getMessage()];
  }

  return ['ok' => true, 'moved' => $movedCount, 'year' => $year];
}

function build_transfer_letter_html(array $schoolMeta, array $learner, array $transfer): string {
  $schoolName = trim((string)($schoolMeta['name'] ?? 'School'));
  $schoolAddress = trim((string)($schoolMeta['address'] ?? ''));
  $schoolLogoPath = trim((string)($schoolMeta['logo_path'] ?? ''));
  $dateText = date('d M Y');
  $destination = trim((string)($transfer['destination_school'] ?? 'Receiving School'));
  $reason = nl2br(h((string)($transfer['reason'] ?? '')));
  $learnerName = h((string)($learner['full_name'] ?? 'Learner'));
  $regNumber = h((string)($learner['reg_number'] ?? '-'));
  $className = h((string)($learner['class_name'] ?? '-'));
  $palette = is_array($schoolMeta['palette'] ?? null) ? $schoolMeta['palette'] : [];
  $ink = normalize_school_hex_color((string)($palette['ink'] ?? '#173122'), '#173122');
  $muted = normalize_school_hex_color((string)($palette['muted'] ?? '#4F6656'), '#4F6656');
  $brand = normalize_school_hex_color((string)($palette['brand_navy'] ?? '#2E8B45'), '#2E8B45');
  $brandDeep = normalize_school_hex_color((string)($palette['brand_navy_deep'] ?? '#1D6A33'), '#1D6A33');
  $brandIce = normalize_school_hex_color((string)($palette['brand_ice'] ?? '#EEF7EF'), '#EEF7EF');
  $line = normalize_school_hex_color((string)($palette['patch_sky_soft'] ?? '#D6EEFF'), '#D6EEFF');

  $logoHtml = $schoolLogoPath !== ''
    ? '<img src="' . h($schoolLogoPath) . '" alt="School logo" style="width:72px;height:72px;object-fit:contain;border:1px solid #d8d8d8;border-radius:10px;padding:4px;background:#fff;">'
    : '';

  return '<!DOCTYPE html><html><head><meta charset="UTF-8"><title>Transfer Letter</title>'
    . '<style>body{font-family:Arial,sans-serif;padding:20px;color:' . h($ink) . ';} .head{display:flex;gap:12px;align-items:center;margin-bottom:14px;} .school{font-size:22px;font-weight:700;color:' . h($brandDeep) . ';} .muted{color:' . h($muted) . ';} .box{border:1px solid ' . h($line) . ';padding:12px;border-radius:8px;background:' . h($brandIce) . ';} h2{color:' . h($brand) . ';} </style>'
    . '</head><body><div class="head">' . $logoHtml . '<div><div class="school">' . h($schoolName) . '</div><div class="muted">' . h($schoolAddress) . '</div><div style="margin-top:6px;">Date: ' . h($dateText) . '</div></div></div>'
    . '<h2>Student Transfer Letter</h2>'
    . '<p>This letter confirms that <strong>' . $learnerName . '</strong> (Reg No: <strong>' . $regNumber . '</strong>) from <strong>' . $className . '</strong> has been cleared for transfer.</p>'
    . '<div class="box"><p><strong>Destination School:</strong> ' . h($destination) . '</p><p><strong>Reason for Transfer:</strong><br>' . $reason . '</p></div>'
    . '<p style="margin-top:20px;">Approved by Administration after teacher and accountant clearance.</p>'
    . '<p style="margin-top:40px;">______________________________<br>School Administrator</p>'
    . '</body></html>';
}

function build_offer_letter_text(array $schoolMeta, array $application, string $className): string {
  $school = trim((string)($schoolMeta['name'] ?? 'School'));
  $learnerName = trim((string)($application['full_name'] ?? 'Learner'));
  return "OFFER LETTER\n"
    . $school . "\n"
    . "Date: " . date('Y-m-d') . "\n\n"
    . "Dear Parent/Guardian,\n"
    . "We are pleased to offer " . $learnerName . " admission into " . $className . ".\n"
    . "Please visit the school office to complete onboarding requirements.\n\n"
    . "Regards,\n"
    . "School Administration";
}

function supported_currencies(): array {
  return ['USD', 'ZIG', 'RAND'];
}

function ensure_currency_tables(PDO $pdo): void {
  $pdo->exec("CREATE TABLE IF NOT EXISTS currency_rates (
    currency_code VARCHAR(10) NOT NULL PRIMARY KEY,
    base_currency VARCHAR(10) NOT NULL DEFAULT 'USD',
    rate_to_base DECIMAL(18,8) NOT NULL,
    source VARCHAR(80) NULL,
    fetched_at DATETIME NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  )");

  $seedStmt = $pdo->prepare("INSERT INTO currency_rates (currency_code, base_currency, rate_to_base, source, fetched_at)
    VALUES (?, 'USD', ?, 'seed', NOW())
    ON DUPLICATE KEY UPDATE rate_to_base = VALUES(rate_to_base), source = COALESCE(currency_rates.source, VALUES(source)), fetched_at = currency_rates.fetched_at");

  $seedData = [
    'USD' => 1.0,
    'RAND' => 0.055,
    'ZIG' => 0.03
  ];
  foreach ($seedData as $code => $rate) {
    $seedStmt->execute([$code, $rate]);
  }
}

function get_currency_rate_to_usd(PDO $pdo, string $currencyCode): float {
  ensure_currency_tables($pdo);
  $code = strtoupper(trim($currencyCode));
  if (!in_array($code, supported_currencies(), true)) {
    return 1.0;
  }

  $stmt = $pdo->prepare("SELECT rate_to_base FROM currency_rates WHERE currency_code = ? LIMIT 1");
  $stmt->execute([$code]);
  $rate = (float)$stmt->fetchColumn();
  if ($rate <= 0) {
    return 1.0;
  }
  return $rate;
}

function get_exchange_rate_mode(PDO $pdo): string {
  $mode = strtolower(trim(get_app_setting($pdo, 'exchange_rate_mode', 'auto')));
  if (!in_array($mode, ['auto', 'manual'], true)) {
    return 'auto';
  }
  return $mode;
}

function set_exchange_rate_mode(PDO $pdo, string $mode, ?int $updatedBy = null): bool {
  $normalizedMode = strtolower(trim($mode));
  if (!in_array($normalizedMode, ['auto', 'manual'], true)) {
    return false;
  }
  return set_app_setting($pdo, 'exchange_rate_mode', $normalizedMode, $updatedBy);
}

function refresh_exchange_rates_online(PDO $pdo, bool $force = false): array {
  ensure_currency_tables($pdo);

  if (get_exchange_rate_mode($pdo) === 'manual') {
    return ['ok' => true, 'source' => 'manual', 'message' => 'Manual exchange rate mode is active'];
  }

  if (!$force) {
    $lastFetchStmt = $pdo->query("SELECT MAX(fetched_at) FROM currency_rates");
    $lastFetch = (string)$lastFetchStmt->fetchColumn();
    if ($lastFetch !== '' && strtotime($lastFetch) !== false) {
      $secondsSince = time() - (int)strtotime($lastFetch);
      if ($secondsSince < 600) {
        return ['ok' => true, 'source' => 'cache', 'message' => 'Using cached rates'];
      }
    }
  }

  $context = stream_context_create([
    'http' => [
      'method' => 'GET',
      'timeout' => 10,
      'ignore_errors' => true,
      'header' => "Accept: application/json\r\nUser-Agent: NAPS-FX/1.0\r\n"
    ]
  ]);

  $json = @file_get_contents('https://open.er-api.com/v6/latest/USD', false, $context);
  if ($json === false || trim($json) === '') {
    return ['ok' => false, 'source' => 'online', 'message' => 'Rate API unreachable'];
  }

  $payload = json_decode($json, true);
  if (!is_array($payload) || !isset($payload['rates']) || !is_array($payload['rates'])) {
    return ['ok' => false, 'source' => 'online', 'message' => 'Invalid rate payload'];
  }

  $rates = $payload['rates'];
  $usdToZar = isset($rates['ZAR']) ? (float)$rates['ZAR'] : 0.0;
  $usdToZig = 0.0;
  if (isset($rates['ZWG'])) {
    $usdToZig = (float)$rates['ZWG'];
  } elseif (isset($rates['ZWL'])) {
    $usdToZig = (float)$rates['ZWL'];
  }

  $newRates = [
    'USD' => 1.0,
    'RAND' => $usdToZar > 0 ? (1 / $usdToZar) : get_currency_rate_to_usd($pdo, 'RAND'),
    'ZIG' => $usdToZig > 0 ? (1 / $usdToZig) : get_currency_rate_to_usd($pdo, 'ZIG')
  ];

  $upsert = $pdo->prepare("INSERT INTO currency_rates (currency_code, base_currency, rate_to_base, source, fetched_at)
    VALUES (?, 'USD', ?, ?, NOW())
    ON DUPLICATE KEY UPDATE rate_to_base = VALUES(rate_to_base), source = VALUES(source), fetched_at = VALUES(fetched_at)");
  foreach ($newRates as $code => $rate) {
    $upsert->execute([$code, $rate, 'open.er-api.com']);
  }

  return ['ok' => true, 'source' => 'online', 'message' => 'Rates updated'];
}

function get_whatsapp_config(?PDO $pdo = null): array {
  $dbToken = '';
  $dbPhoneNumberId = '';
  $dbApiVersion = '';

  if ($pdo instanceof PDO) {
    $dbToken = trim(get_app_setting($pdo, 'whatsapp_token', ''));
    $dbPhoneNumberId = trim(get_app_setting($pdo, 'whatsapp_phone_number_id', ''));
    $dbApiVersion = trim(get_app_setting($pdo, 'whatsapp_api_version', ''));
  }

  $token = $dbToken !== '' ? $dbToken : trim((string)(getenv('MAVELA_WHATSAPP_TOKEN') ?: getenv('NAPS_WHATSAPP_TOKEN')));
  $phoneNumberId = $dbPhoneNumberId !== '' ? $dbPhoneNumberId : trim((string)(getenv('MAVELA_WHATSAPP_PHONE_NUMBER_ID') ?: getenv('NAPS_WHATSAPP_PHONE_NUMBER_ID')));
  $apiVersion = $dbApiVersion !== '' ? $dbApiVersion : trim((string)(getenv('MAVELA_WHATSAPP_API_VERSION') ?: getenv('NAPS_WHATSAPP_API_VERSION')));

  if ($apiVersion === '') {
    $apiVersion = 'v22.0';
  }

  return [
    'token' => $token,
    'phone_number_id' => $phoneNumberId,
    'api_version' => $apiVersion,
    'source' => ($dbToken !== '' || $dbPhoneNumberId !== '' || $dbApiVersion !== '') ? 'database' : 'environment'
  ];
}

function whatsapp_notifications_enabled(?PDO $pdo = null): bool {
  if (!($pdo instanceof PDO)) {
    global $pdo;
  }
  $cfg = get_whatsapp_config($pdo instanceof PDO ? $pdo : null);
  return trim((string)$cfg['token']) !== '' && trim((string)$cfg['phone_number_id']) !== '';
}

function send_whatsapp_message_to_phone(string $phoneE164, string $message, ?PDO $pdo = null): array {
  if (!($pdo instanceof PDO)) {
    global $pdo;
  }
  $usePdo = $pdo instanceof PDO ? $pdo : null;

  if (!whatsapp_notifications_enabled($usePdo)) {
    return ['ok' => false, 'error' => 'not_configured'];
  }

  if (!function_exists('curl_init')) {
    return ['ok' => false, 'error' => 'curl_missing'];
  }

  $cfg = get_whatsapp_config($usePdo);
  $token = (string)$cfg['token'];
  $phoneNumberId = (string)$cfg['phone_number_id'];
  $apiVersion = (string)$cfg['api_version'];

  $to = ltrim($phoneE164, '+');
  $endpoint = 'https://graph.facebook.com/' . rawurlencode($apiVersion) . '/' . rawurlencode($phoneNumberId) . '/messages';

  $payload = [
    'messaging_product' => 'whatsapp',
    'recipient_type' => 'individual',
    'to' => $to,
    'type' => 'text',
    'text' => [
      'preview_url' => false,
      'body' => $message
    ]
  ];

  $ch = curl_init($endpoint);
  if ($ch === false) {
    return ['ok' => false, 'error' => 'curl_init_failed'];
  }

  curl_setopt_array($ch, [
    CURLOPT_POST => true,
    CURLOPT_RETURNTRANSFER => true,
    CURLOPT_TIMEOUT => 15,
    CURLOPT_HTTPHEADER => [
      'Authorization: Bearer ' . $token,
      'Content-Type: application/json'
    ],
    CURLOPT_POSTFIELDS => json_encode($payload, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES)
  ]);

  $responseBody = curl_exec($ch);
  $curlError = curl_error($ch);
  $statusCode = (int)curl_getinfo($ch, CURLINFO_RESPONSE_CODE);
  curl_close($ch);

  if ($responseBody === false) {
    return ['ok' => false, 'error' => $curlError !== '' ? $curlError : 'request_failed'];
  }

  $responseJson = json_decode($responseBody, true);
  if ($statusCode >= 200 && $statusCode < 300) {
    return ['ok' => true, 'status' => $statusCode, 'response' => $responseJson];
  }

  $errorMessage = 'http_' . $statusCode;
  if (is_array($responseJson) && isset($responseJson['error']['message'])) {
    $errorMessage = (string)$responseJson['error']['message'];
  }

  return ['ok' => false, 'status' => $statusCode, 'error' => $errorMessage, 'response' => $responseJson];
}

function get_parent_whatsapp_recipients_for_scope(PDO $pdo, ?int $targetClassId, ?int $targetLearnerId): array {
  $scopeSql = [];
  $params = [];

  if ($targetClassId !== null && $targetClassId > 0) {
    $scopeSql[] = 'l.class_id = ?';
    $params[] = $targetClassId;
  }
  if ($targetLearnerId !== null && $targetLearnerId > 0) {
    $scopeSql[] = 'l.id = ?';
    $params[] = $targetLearnerId;
  }

  $whereScope = '';
  if (!empty($scopeSql)) {
    $whereScope = ' AND ' . implode(' AND ', $scopeSql);
  }

  $sql = "SELECT u.id AS parent_user_id, u.name AS parent_name,
      MAX(NULLIF(TRIM(COALESCE(l.guardian_phone, '')), '')) AS guardian_phone,
      GROUP_CONCAT(DISTINCT CONCAT(TRIM(COALESCE(l.full_name, 'Learner')), ' (', l.reg_number, ')') ORDER BY l.full_name SEPARATOR ', ') AS learner_list
    FROM parent_learner_links pll
    JOIN users u ON u.id = pll.parent_user_id AND u.role = 'parent'
    JOIN learners l ON l.id = pll.learner_id
    WHERE 1=1" . $whereScope . "
    GROUP BY u.id, u.name
    ORDER BY u.name";

  $stmt = $pdo->prepare($sql);
  $stmt->execute($params);
  return $stmt->fetchAll();
}

function notify_parents_announcement_whatsapp(PDO $pdo, string $title, string $body, ?int $targetClassId = null, ?int $targetLearnerId = null): array {
  $recipients = get_parent_whatsapp_recipients_for_scope($pdo, $targetClassId, $targetLearnerId);
  $stats = [
    'recipients' => count($recipients),
    'sent' => 0,
    'failed' => 0,
    'skipped' => 0,
    'errors' => []
  ];

  $safeTitle = trim($title);
  $safeBody = trim($body);

  foreach ($recipients as $recipient) {
    $rawPhone = (string)($recipient['guardian_phone'] ?? '');
    $phone = normalize_whatsapp_phone($rawPhone);
    if ($phone === null) {
      $stats['skipped']++;
      continue;
    }

    $learnerList = trim((string)($recipient['learner_list'] ?? ''));
    $message = "MAVELA GOVERNMENT HIGH NOTICE\n"
      . "Title: " . $safeTitle . "\n"
      . "Message: " . $safeBody;
    if ($learnerList !== '') {
      $message .= "\nChild(ren): " . $learnerList;
    }

    $result = send_whatsapp_message_to_phone($phone, $message, $pdo);
    if (!empty($result['ok'])) {
      $stats['sent']++;
    } else {
      $stats['failed']++;
      $stats['errors'][] = [
        'parent_user_id' => (int)($recipient['parent_user_id'] ?? 0),
        'phone' => $phone,
        'error' => (string)($result['error'] ?? 'send_failed')
      ];
    }
  }

  return $stats;
}

function notify_parents_payment_whatsapp(PDO $pdo, int $learnerId, float $amountPaid, string $paymentDate, string $paymentMethod, ?string $referenceNo = null, string $currencyCode = 'USD', float $amountPaidUsd = 0.0, ?string $feeComponent = null): array {
  $stmt = $pdo->prepare("SELECT u.id AS parent_user_id, u.name AS parent_name,
      l.full_name AS learner_name, l.reg_number, l.guardian_phone
    FROM parent_learner_links pll
    JOIN users u ON u.id = pll.parent_user_id AND u.role = 'parent'
    JOIN learners l ON l.id = pll.learner_id
    WHERE l.id = ?
    ORDER BY u.name");
  $stmt->execute([$learnerId]);
  $recipients = $stmt->fetchAll();

  $totalsStmt = $pdo->prepare("SELECT
      COALESCE((SELECT SUM(fi.amount_due) FROM fee_invoices fi WHERE fi.learner_id = ?), 0) AS total_invoiced,
      COALESCE((SELECT SUM(COALESCE(fp.amount_paid_usd, fp.amount_paid)) FROM fee_payments fp WHERE fp.learner_id = ?), 0) AS total_paid");
  $totalsStmt->execute([$learnerId, $learnerId]);
  $totalsRow = $totalsStmt->fetch() ?: ['total_invoiced' => 0, 'total_paid' => 0];
  $totalInvoiced = (float)$totalsRow['total_invoiced'];
  $totalPaid = (float)$totalsRow['total_paid'];
  $totalOwed = max(0, $totalInvoiced - $totalPaid);

  $stats = [
    'recipients' => count($recipients),
    'sent' => 0,
    'failed' => 0,
    'skipped' => 0,
    'owed' => $totalOwed,
    'errors' => []
  ];

  foreach ($recipients as $recipient) {
    $rawPhone = (string)($recipient['guardian_phone'] ?? '');
    $phone = normalize_whatsapp_phone($rawPhone);
    if ($phone === null) {
      $stats['skipped']++;
      continue;
    }

    $usdValue = $amountPaidUsd > 0 ? $amountPaidUsd : $amountPaid;
    $message = "MAVELA GOVERNMENT HIGH PAYMENT RECEIPT\n"
      . "Child: " . trim((string)$recipient['learner_name']) . " (" . (string)$recipient['reg_number'] . ")\n"
      . "Amount receipted: " . strtoupper($currencyCode) . " " . number_format($amountPaid, 2) . "\n"
      . "Converted to USD: $" . number_format($usdValue, 2) . "\n"
      . "Date: " . $paymentDate . "\n"
      . "Method: " . strtoupper(str_replace('_', ' ', $paymentMethod)) . "\n";

    if ($feeComponent !== null && trim($feeComponent) !== '') {
      $message .= "Fee type: " . strtoupper(trim($feeComponent)) . "\n";
    }

    if ($referenceNo !== null && trim($referenceNo) !== '') {
      $message .= "Reference: " . trim($referenceNo) . "\n";
    }

    $message .= "Total owed: $" . number_format($totalOwed, 2);

    $result = send_whatsapp_message_to_phone($phone, $message, $pdo);
    if (!empty($result['ok'])) {
      $stats['sent']++;
    } else {
      $stats['failed']++;
      $stats['errors'][] = [
        'parent_user_id' => (int)($recipient['parent_user_id'] ?? 0),
        'phone' => $phone,
        'error' => (string)($result['error'] ?? 'send_failed')
      ];
    }
  }

  return $stats;
}

function notify_parents_owed_update_whatsapp(PDO $pdo, int $learnerId, string $title, string $detailLine): array {
  $stmt = $pdo->prepare("SELECT u.id AS parent_user_id, u.name AS parent_name,
      l.full_name AS learner_name, l.reg_number, l.guardian_phone
    FROM parent_learner_links pll
    JOIN users u ON u.id = pll.parent_user_id AND u.role = 'parent'
    JOIN learners l ON l.id = pll.learner_id
    WHERE l.id = ?
    ORDER BY u.name");
  $stmt->execute([$learnerId]);
  $recipients = $stmt->fetchAll();

  $totalsStmt = $pdo->prepare("SELECT
      COALESCE((SELECT SUM(fi.amount_due) FROM fee_invoices fi WHERE fi.learner_id = ?), 0) AS total_invoiced,
      COALESCE((SELECT SUM(COALESCE(fp.amount_paid_usd, fp.amount_paid)) FROM fee_payments fp WHERE fp.learner_id = ?), 0) AS total_paid");
  $totalsStmt->execute([$learnerId, $learnerId]);
  $totalsRow = $totalsStmt->fetch() ?: ['total_invoiced' => 0, 'total_paid' => 0];
  $totalInvoiced = (float)$totalsRow['total_invoiced'];
  $totalPaid = (float)$totalsRow['total_paid'];
  $totalOwed = max(0, $totalInvoiced - $totalPaid);

  $stats = [
    'recipients' => count($recipients),
    'sent' => 0,
    'failed' => 0,
    'skipped' => 0,
    'owed' => $totalOwed,
    'errors' => []
  ];

  foreach ($recipients as $recipient) {
    $rawPhone = (string)($recipient['guardian_phone'] ?? '');
    $phone = normalize_whatsapp_phone($rawPhone);
    if ($phone === null) {
      $stats['skipped']++;
      continue;
    }

    $message = "MAVELA GOVERNMENT HIGH FEE UPDATE\n"
      . "Child: " . trim((string)$recipient['learner_name']) . " (" . (string)$recipient['reg_number'] . ")\n"
      . "" . trim($title) . "\n"
      . trim($detailLine) . "\n"
      . "Total owed: $" . number_format($totalOwed, 2);

    $result = send_whatsapp_message_to_phone($phone, $message, $pdo);
    if (!empty($result['ok'])) {
      $stats['sent']++;
    } else {
      $stats['failed']++;
      $stats['errors'][] = [
        'parent_user_id' => (int)($recipient['parent_user_id'] ?? 0),
        'phone' => $phone,
        'error' => (string)($result['error'] ?? 'send_failed')
      ];
    }
  }

  return $stats;
}
?>
