<?php declare(strict_types=1);

/**
 * EdenRMS Notifications Engine
 * - Creates/fetches notifications for users (admins/accountants/coordinators etc.)
 * - Fan-out when staff creates an approval request
 * - Notify requester on approve/reject
 */

if (session_status() === PHP_SESSION_NONE) { session_start(); }

/* ---- DB helpers ---- */
if (!function_exists('ec_db')) {
  require_once __DIR__ . '/db.php'; // should define ec_db() or $conn
}

function ecn_db(?mysqli $db = null): mysqli {
  if ($db instanceof mysqli) return $db;
  $db = function_exists('ec_db') ? ec_db() : null;
  if (!$db) { $db = @new mysqli('localhost','root','','edencamp_edencampus'); }
  if ($db instanceof mysqli) { @$db->set_charset('utf8mb4'); }
  return $db;
}

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

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

/* ---- Ensure table ---- */
function ecn_ensure_tables(?mysqli $db = null): void {
  $db = ecn_db($db);
  @$db->query("CREATE TABLE IF NOT EXISTS notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    recipient_user_id INT NOT NULL,
    branch_id INT NULL,
    type VARCHAR(40) NOT NULL,              -- 'approval_request','approval_update','system'
    event VARCHAR(40) NOT NULL,             -- 'created','approved','rejected'
    ref_type VARCHAR(40) NULL,              -- 'approval_requests'
    ref_id INT NULL,
    title VARCHAR(200) NOT NULL,
    body TEXT NULL,
    link_url VARCHAR(255) NULL,
    payload JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    read_at DATETIME NULL,
    INDEX idx_recipient_created (recipient_user_id, created_at),
    INDEX idx_ref (ref_type, ref_id),
    UNIQUE KEY uniq_target (recipient_user_id, ref_type, ref_id, event)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
}

/* ---- Internal: resolve user table and key columns ---- */
function ecn_user_table(mysqli $db): array {
  // Prefer 'users'; fallback to 'elms_users'
  $table = ecn_t_exists($db, 'users') ? 'users' : ( ecn_t_exists($db, 'elms_users') ? 'elms_users' : '' );
  if (!$table) return ['', '', '', '']; // table, idCol, roleCol, branchCol
  // Common column names with safe fallbacks
  $idCol     = ecn_c_exists($db, $table, 'id') ? 'id' : '';
  $roleCol   = ecn_c_exists($db, $table, 'role') ? 'role' : '';
  $branchCol = ecn_c_exists($db, $table, 'branch_id') ? 'branch_id' : '';
  return [$table, $idCol, $roleCol, $branchCol];
}

/* ---- Fan-out when a staff creates an approval request ---- */
function noti_from_approval_created(?mysqli $db, int $approval_id): void {
  $db = ecn_db($db); ecn_ensure_tables($db);
  if (!ecn_t_exists($db, 'approval_requests')) return;

  $q = "SELECT ar.id, ar.type, ar.requested_by,
               COALESCE(ar.student_id, ar.ref_id) AS sid,
               ar.requested_at,
               ".(ecn_t_exists($db,'studentss') ? "COALESCE(s.full_name,'Student')" : "'Student'")." AS student_name,
               ".(ecn_t_exists($db,'studentss') && ecn_c_exists($db, 'studentss', 'branch_id') ? "s.branch_id" : "NULL")." AS s_branch,
               ".(ecn_t_exists($db,'courses') ? "c.course_name" : "NULL")." AS course_name
        FROM approval_requests ar
        ".(ecn_t_exists($db,'studentss') ? "LEFT JOIN studentss s ON s.id = COALESCE(ar.student_id, ar.ref_id)" : "")."
        ".(ecn_t_exists($db,'courses')   ? "LEFT JOIN courses   c ON c.id = ar.course_id" : "")."
        WHERE ar.id = ? LIMIT 1";
  $st = $db->prepare($q); if (!$st) return;
  $st->bind_param('i', $approval_id);
  $st->execute(); $rs = $st->get_result();
  $ar = $rs ? $rs->fetch_assoc() : null; $st->close();
  if (!$ar) return;

  $title = 'New approval request';
  $type  = (string)($ar['type'] ?? 'request');
  $body  = ucwords(str_replace('_',' ', $type));
  $student = trim((string)($ar['student_name'] ?? 'Student'));
  $course  = trim((string)($ar['course_name'] ?? ''));
  $body   .= ' — ' . $student . ($course ? (' · ' . $course) : '');

  [$userTbl, $idCol, $roleCol, $branchCol] = ecn_user_table($db);
  if (!$userTbl || !$idCol) return;

  // Who to notify
  $allowed = ['super_admin','branch_admin','admin','accountant','coordinator'];
  $conds = [];
  if ($roleCol)     { $conds[] = "$roleCol IN ('".implode("','",$allowed)."')"; }
  if ($branchCol && isset($ar['s_branch']) && $ar['s_branch'] !== null) {
    $branchId = (int)$ar['s_branch'];
    $conds[] = "($branchCol IS NULL OR $branchCol = ".(int)$branchId.")";
  }
  if (ecn_c_exists($db, $userTbl, 'status')) {
    $conds[] = "status <> 'disabled'";
  }
  $sqlUsers = "SELECT $idCol AS id".($branchCol? ", $branchCol AS b":"")." FROM `$userTbl`".( $conds ? (' WHERE '.implode(' AND ', $conds)) : '' );
  $U = $db->query($sqlUsers); if (!$U) return;
  $recipients = [];
  while($r = $U->fetch_assoc()){ $recipients[] = [(int)$r['id'], $branchCol ? (int)($r['b'] ?? 0) : null]; }

  $reqBy = (int)($ar['requested_by'] ?? 0);
  $link  = "/edenrms/pages/approval_view.php?id=".$approval_id;

  $ins = $db->prepare("INSERT IGNORE INTO notifications
    (recipient_user_id, branch_id, type, event, ref_type, ref_id, title, body, link_url)
    VALUES (?,?,?,?,?,?,?,?,?)");

  foreach ($recipients as [$uid, $b]) {
    if ($uid === $reqBy) continue; // Skip requester
    $t='approval_request'; $e='created'; $rt='approval_requests'; $rid=$approval_id;
    $ins->bind_param('iisssisss', $uid, $b, $t, $e, $rt, $rid, $title, $body, $link);
    $ins->execute();
  }
  $ins && $ins->close();
}

/* ---- Notify requester when approved/rejected ---- */
function noti_from_approval_updated(?mysqli $db, int $approval_id, string $newStatus, ?int $actorUserId = null): void {
  $db = ecn_db($db); ecn_ensure_tables($db);
  if (!ecn_t_exists($db, 'approval_requests')) return;

  $q = "SELECT ar.id, ar.type, ar.requested_by,
               COALESCE(ar.student_id, ar.ref_id) AS sid,
               ".(ecn_t_exists($db,'studentss') ? "COALESCE(s.full_name,'Student')" : "'Student'")." AS student_name,
               ".(ecn_t_exists($db,'courses')   ? "c.course_name" : "NULL")." AS course_name
        FROM approval_requests ar
        ".(ecn_t_exists($db,'studentss') ? "LEFT JOIN studentss s ON s.id = COALESCE(ar.student_id, ar.ref_id)" : "")."
        ".(ecn_t_exists($db,'courses')   ? "LEFT JOIN courses   c ON c.id = ar.course_id" : "")."
        WHERE ar.id = ? LIMIT 1";
  $st = $db->prepare($q); if (!$st) return;
  $st->bind_param('i', $approval_id);
  $st->execute(); $rs = $st->get_result();
  $ar = $rs ? $rs->fetch_assoc() : null; $st->close();
  if (!$ar) return;

  $requester = (int)($ar['requested_by'] ?? 0);
  if ($requester <= 0) return;

  $title = 'Your request was '.strtolower($newStatus);
  $type  = (string)($ar['type'] ?? 'request');
  $body  = ucwords(str_replace('_',' ', $type));
  $student = trim((string)($ar['student_name'] ?? 'Student'));
  $course  = trim((string)($ar['course_name'] ?? ''));
  $body   .= ' — ' . $student . ($course ? (' · ' . $course) : '');

  $link  = "/edenrms/pages/approval_view.php?id=".$approval_id;
  $branch = null;

  $ins = $db->prepare("INSERT IGNORE INTO notifications
    (recipient_user_id, branch_id, type, event, ref_type, ref_id, title, body, link_url)
    VALUES (?,?,?,?,?,?,?,?,?)");
  $t='approval_update';
  $e = (strtolower($newStatus)==='approved') ? 'approved' : 'rejected';
  $rt='approval_requests'; $rid=$approval_id;
  $ins->bind_param('iisssisss', $requester, $branch, $t, $e, $rt, $rid, $title, $body, $link);
  $ins->execute(); $ins && $ins->close();
}

/* ---- Public fetch/mark APIs ---- */
function noti_fetch_for_user(?mysqli $db, int $userId, int $limit = 30, bool $unreadOnly = false): array {
  if ($userId <= 0) return [];
  $db = ecn_db($db); ecn_ensure_tables($db);
  $sql = "SELECT id, type, event, title, body, link_url, created_at, read_at
          FROM notifications
          WHERE recipient_user_id = ? ".($unreadOnly ? "AND read_at IS NULL " : "")."
          ORDER BY created_at DESC, id DESC
          LIMIT ?";
  $st = $db->prepare($sql);
  $st->bind_param('ii', $userId, $limit);
  $st->execute(); $rs = $st->get_result();
  $items = [];
  while ($r = $rs->fetch_assoc()) {
    $items[] = [
      'id'    => 'noti-'.$r['id'],
      'title' => (string)$r['title'],
      'body'  => (string)($r['body'] ?? ''),
      'time'  => (string)($r['created_at'] ?? ''),
      'read'  => !empty($r['read_at']),
      'href'  => (string)($r['link_url'] ?? ''),
      'type'  => (string)($r['type'] ?? ''),
      'event' => (string)($r['event'] ?? ''),
    ];
  }
  $st->close();
  return $items;
}

function noti_mark_read(?mysqli $db, int $notifId, int $userId): bool {
  if ($notifId <= 0 || $userId <= 0) return false;
  $db = ecn_db($db); ecn_ensure_tables($db);
  $st = $db->prepare("UPDATE notifications SET read_at = NOW() WHERE id = ? AND recipient_user_id = ? AND read_at IS NULL");
  $st->bind_param('ii', $notifId, $userId);
  $ok = $st->execute(); $st->close();
  return (bool)$ok;
}

function noti_mark_all_read(?mysqli $db, int $userId): bool {
  if ($userId <= 0) return false;
  $db = ecn_db($db); ecn_ensure_tables($db);
  $st = $db->prepare("UPDATE notifications SET read_at = NOW() WHERE recipient_user_id = ? AND read_at IS NULL");
  $st->bind_param('i', $userId);
  $ok = $st->execute(); $st->close();
  return (bool)$ok;
}
