import axios from 'axios';
import { query, execute } from '../lib/db';
import { v4 as uuidv4 } from 'uuid';
import { enqueueOfflineRequest, useOfflineQueue } from '../lib/offlineQueue';

const api = axios.create({ baseURL: '/api' });

api.interceptors.request.use((config) => {
  const storage = localStorage.getItem('auth-storage');
  if (storage) {
    try {
      const parsed = JSON.parse(storage);
      const token = parsed?.state?.token;
      if (token) {
        config.headers.Authorization = `Bearer ${token}`;
      }
    } catch (e) {
      console.error('Failed to parse auth storage', e);
    }
  }
  return config;
});

// ---------------------------------------------------------------------------
// Response interceptor — network error → offline route
//
// On any network failure:
//   • GET  requests are routed to `handleOfflineGet()` (explicit allowlist).
//   • non-GET requests are pushed onto the offline queue and a synthetic
//     202-Accepted response is returned. The DeviceSyncEngine in
//     lib/sync.ts is the OLDER path that wrote to local SQLite and pushed
//     to /api/sync/push on a timer; it remains in place for the explicit
//     tables it covers (attendance, payments, events, …). This interceptor
//     is the NEW path that handles UNKNOWN routes safely — see B.2/B.3.
// ---------------------------------------------------------------------------
api.interceptors.response.use(
  (response) => response,
  async (error) => {
    const isNetworkError =
      !error.response || error.code === 'ERR_NETWORK' || error.message?.includes('Network Error');
    if (!isNetworkError) {
      return Promise.reject(error);
    }

    const config = error.config || {};
    const method = (config.method || 'get').toLowerCase();
    console.warn('⚠️ Network failure.', method.toUpperCase(), config.url);

    if (method !== 'get') {
      // B.3 — POST/PUT/PATCH/DELETE → offline queue. We deliberately do NOT
      // touch the local SQLite for unknown writes anymore; the queue
      // replays against the real server when the network returns. The
      // 202 status + `queued:true` body shape is what the page components
      // already check (see e.g. Notices/Events submit handlers).
      try {
        const queued = enqueueOfflineRequest(config);
        return {
          data: queued.data,
          status: queued.status,
          statusText: queued.statusText,
          headers: { 'x-offline-queued': 'true' },
          config,
        };
      } catch (queueErr) {
        console.error('❌ Failed to enqueue offline write', queueErr);
        return Promise.reject(error);
      }
    }

    // GET → explicit allowlist against the local SQLite cache.
    try {
      const offlineData = await handleOfflineGet(config);
      // handleOfflineGet returns [] for both "unknown route" and "handler
      // errored" cases (the B.2 deny-by-default policy). Treat an empty
      // array as a successful offline hit so the caller still gets a 200
      // and a shape it can render.
      if (offlineData === undefined) {
        return {
          data: [],
          status: 200,
          statusText: 'OK (Offline - Unknown Route)',
          headers: { 'x-offline-fallback': 'true' },
          config,
        };
      }
      return {
        data: offlineData,
        status: 200,
        statusText: 'OK (Offline Cache)',
        headers: { 'x-offline-fallback': 'true' },
        config,
      };
    } catch (dbErr: any) {
      // A handler matched the route but the SQL failed (e.g. the local
      // table is missing from the worker schema). Deny by default rather
      // than re-throw — the page sees a 200 with [] and renders an
      // empty state, which is the safer failure mode.
      console.error('❌ Offline handler error — denying by default:', dbErr);
      // Surface the error message in the response so tests / debuggers
      // can see it without spelunking the console.
      const errMsg = dbErr?.message || String(dbErr);
      try {
        const last = JSON.parse(localStorage.getItem('offline-last-error') || '[]');
        last.push({ url: config?.url, err: errMsg, ts: Date.now() });
        localStorage.setItem('offline-last-error', JSON.stringify(last.slice(-5)));
      } catch { /* localStorage may be unavailable */ }
      return {
        data: [],
        status: 200,
        statusText: 'OK (Offline - Handler Error)',
        headers: { 'x-offline-fallback': 'true', 'x-offline-error': '1', 'x-offline-error-msg': errMsg.slice(0, 200) },
        config,
      };
    }
  }
);

// ---------------------------------------------------------------------------
// Offline GET handler — explicit allowlist only.
//
// Generic SQL interpolation is unsafe; explicit allowlist only. Unknown
// routes return [] (deny-by-default). Per-table handlers below apply
// role-based WHERE clauses so a student cannot read the entire users or
// grades table when the server is offline.
// ---------------------------------------------------------------------------
async function handleOfflineGet(config: any): Promise<any> {
  const url: string = config.url || '';
  const cleanPath = url.replace(/^\/?api/, '').split('?')[0];

  const getParams = () => {
    if (config.params) return config.params;
    const parts = url.split('?');
    if (parts.length > 1) {
      const q = new URLSearchParams(parts[1]);
      const obj: any = {};
      q.forEach((v, k) => { obj[k] = v; });
      return obj;
    }
    return {};
  };

  const getAuthUser = () => {
    try {
      const storage = localStorage.getItem('auth-storage');
      if (storage) {
        const parsed = JSON.parse(storage);
        return parsed?.state?.user || null;
      }
    } catch (e) {
      console.error(e);
    }
    return null;
  };

  const params = getParams();
  const user = getAuthUser();
  const role: string = user?.role || '';
  const userId: number | null = user?.id ?? null;

  // ---- /classes ---------------------------------------------------------
  if (cleanPath === '/classes' || cleanPath === 'classes') {
    if (isStaff(role)) {
      return await query("SELECT * FROM classes WHERE is_deleted = 0");
    }
    if (role === 'teacher') {
      // teacher sees classes they teach OR are class_teacher of. The
      // worker's `courses` table has no `class_id` column, so the
      // "courses I teach" branch isn't modelable offline — the
      // `class_teacher_id` link is the only path from teacher → class
      // that the worker schema has.
      if (userId == null) return [];
      return await query(
        "SELECT * FROM classes WHERE is_deleted = 0 AND class_teacher_id = ?",
        [userId]
      );
    }
    if (role === 'student' && userId != null) {
      return await query(
        "SELECT * FROM classes WHERE is_deleted = 0 AND id IN (SELECT class_id FROM enrollments WHERE student_id = ? AND is_deleted = 0 AND status = 'active')",
        [userId]
      );
    }
    if (role === 'parent' && userId != null) {
      return await query(
        "SELECT * FROM classes WHERE is_deleted = 0 AND id IN (SELECT class_id FROM enrollments WHERE student_id IN (SELECT student_id FROM parent_students WHERE parent_id = ? AND is_deleted = 0) AND is_deleted = 0 AND status = 'active')",
        [userId]
      );
    }
    return [];
  }

  // ---- /users (B.4 — hardened) -----------------------------------------
  // Students see self + my teachers + my classmates. Teachers see self +
  // students in their classes + colleagues + staff. Parents see self + their
  // children + their children's teachers. Admins/HR/principal/systems_admin
  // see all. Anything else is restricted to self.
  if (cleanPath === '/users' || cleanPath === 'users') {
    if (!user || userId == null) return [];

    // Privileged roles see the full user table (or filtered by ?role=).
    if (isStaffPrivileged(role)) {
      if (params.role) {
        return await query(
          'SELECT * FROM users WHERE role = ? AND is_deleted = 0',
          [params.role]
        );
      }
      return await query('SELECT * FROM users WHERE is_deleted = 0');
    }

    if (role === 'teacher') {
      // self + students in my classes + colleagues in my department + staff
      return await query(
        `SELECT * FROM users WHERE is_deleted = 0 AND (
          id = ?
          OR role = 'student' AND id IN (
            SELECT student_id FROM enrollments
            WHERE class_id IN (
              SELECT id FROM classes WHERE class_teacher_id = ?
            ) AND is_deleted = 0 AND status = 'active'
          )
          OR (role = 'teacher' AND department_id IS NOT NULL AND department_id = (
            SELECT department_id FROM users WHERE id = ? AND is_deleted = 0
          ))
          OR role IN ('school_admin','systems_admin','principal','hr','bursar','nurse','librarian','clubs_head')
        )`,
        [userId, userId, userId, userId]
      );
    }

    if (role === 'student') {
      // self + my class_teachers + my classmates.
      // Note: the worker schema doesn't have a `course_enrolments` table,
      // so we can't model "subject teachers" offline — only the single
      // `class_teacher_id` on the classes table. That's still the right
      // answer for the demo: one teacher is the homeroom contact, and
      // subjects are visible in the assignments page by class anyway.
      return await query(
        `SELECT * FROM users WHERE is_deleted = 0 AND (
          id = ?
          OR role = 'teacher' AND id IN (
            SELECT class_teacher_id FROM classes
            WHERE id IN (
              SELECT class_id FROM enrollments
              WHERE student_id = ? AND is_deleted = 0 AND status = 'active'
            ) AND is_deleted = 0 AND class_teacher_id IS NOT NULL
          )
          OR role = 'student' AND id IN (
            SELECT student_id FROM enrollments
            WHERE class_id IN (
              SELECT class_id FROM enrollments
              WHERE student_id = ? AND is_deleted = 0 AND status = 'active'
            ) AND is_deleted = 0 AND status = 'active'
          )
        )`,
        [userId, userId, userId]
      );
    }

    if (role === 'parent') {
      // self + my children (students) + their teachers + staff
      return await query(
        `SELECT * FROM users WHERE is_deleted = 0 AND (
          id = ?
          OR role = 'student' AND id IN (
            SELECT student_id FROM parent_students
            WHERE parent_id = ? AND is_deleted = 0
          )
          OR role = 'teacher' AND id IN (
            SELECT class_teacher_id FROM classes
            WHERE id IN (
              SELECT class_id FROM enrollments
              WHERE student_id IN (
                SELECT student_id FROM parent_students WHERE parent_id = ? AND is_deleted = 0
              ) AND is_deleted = 0 AND status = 'active'
            ) AND is_deleted = 0
          )
          OR role IN ('school_admin','systems_admin','principal','hr','bursar','nurse','librarian','clubs_head')
        )`,
        [userId, userId, userId]
      );
    }

    if (role === 'nurse' || role === 'dining_staff') {
      // self + all students (nurse/dining_hall need to see student names
      // and emergency info — they already get this through the explicit
      // /dining/allergies endpoint, but a basic lookup is useful)
      return await query(
        `SELECT * FROM users WHERE is_deleted = 0 AND (id = ? OR role = 'student')`,
        [userId]
      );
    }

    // Default: just self (driver, groundsman, matron, boarding_master, security, librarian, clubs_head)
    return await query('SELECT * FROM users WHERE id = ? AND is_deleted = 0', [userId]);
  }

  // ---- /attendance ------------------------------------------------------
  if (cleanPath === '/attendance' || cleanPath === 'attendance') {
    if (isStaff(role)) {
      let q = "SELECT * FROM attendance WHERE is_deleted = 0";
      const b: any[] = [];
      if (params.date) { q += " AND date = ?"; b.push(params.date); }
      if (params.class_id) { q += " AND class_id = ?"; b.push(parseInt(params.class_id)); }
      return await query(q, b);
    }
    if (role === 'teacher' && userId != null) {
      let q = `SELECT a.* FROM attendance a
               WHERE a.is_deleted = 0
                 AND a.class_id IN (
                   SELECT id FROM classes WHERE class_teacher_id = ?
                 )`;
      const b: any[] = [userId];
      if (params.date) { q += " AND a.date = ?"; b.push(params.date); }
      if (params.class_id) { q += " AND a.class_id = ?"; b.push(parseInt(params.class_id)); }
      return await query(q, b);
    }
    if (role === 'student' && userId != null) {
      return await query(
        'SELECT * FROM attendance WHERE student_id = ? AND is_deleted = 0',
        [userId]
      );
    }
    if (role === 'parent' && userId != null) {
      return await query(
        `SELECT * FROM attendance WHERE student_id IN (
          SELECT student_id FROM parent_students WHERE parent_id = ? AND is_deleted = 0
        ) AND is_deleted = 0`,
        [userId]
      );
    }
    return [];
  }

  // ---- /grades ----------------------------------------------------------
  if (cleanPath === '/grades' || cleanPath === 'grades') {
    if (isStaff(role)) {
      let q = "SELECT * FROM grades WHERE is_deleted = 0";
      const b: any[] = [];
      if (params.student_id) { q += " AND student_id = ?"; b.push(parseInt(params.student_id)); }
      return await query(q, b);
    }
    if (role === 'teacher' && userId != null) {
      let q = `SELECT g.* FROM grades g
               WHERE g.is_deleted = 0
                 AND g.student_id IN (
                   SELECT student_id FROM enrollments
                   WHERE class_id IN (
                     SELECT id FROM classes WHERE class_teacher_id = ?
                   ) AND is_deleted = 0 AND status = 'active'
                 )`;
      const b: any[] = [userId];
      if (params.student_id) { q += " AND g.student_id = ?"; b.push(parseInt(params.student_id)); }
      return await query(q, b);
    }
    if (role === 'student' && userId != null) {
      return await query(
        'SELECT * FROM grades WHERE student_id = ? AND is_deleted = 0',
        [userId]
      );
    }
    if (role === 'parent' && userId != null) {
      return await query(
        `SELECT * FROM grades WHERE student_id IN (
          SELECT student_id FROM parent_students WHERE parent_id = ? AND is_deleted = 0
        ) AND is_deleted = 0`,
        [userId]
      );
    }
    return [];
  }

  // ---- /exams -----------------------------------------------------------
  if (cleanPath === '/exams' || cleanPath === 'exams') {
    if (isStaff(role)) {
      return await query("SELECT * FROM exams WHERE is_deleted = 0");
    }
    if (role === 'teacher' && userId != null) {
      return await query(
        `SELECT * FROM exams WHERE is_deleted = 0 AND (
          created_by = ?
          OR class_id IN (
            SELECT id FROM classes WHERE class_teacher_id = ?
          )
        )`,
        [userId, userId]
      );
    }
    if (role === 'student' && userId != null) {
      return await query(
        `SELECT * FROM exams WHERE is_deleted = 0 AND class_id IN (
          SELECT class_id FROM enrollments WHERE student_id = ? AND is_deleted = 0 AND status = 'active'
        )`,
        [userId]
      );
    }
    if (role === 'parent' && userId != null) {
      return await query(
        `SELECT * FROM exams WHERE is_deleted = 0 AND class_id IN (
          SELECT class_id FROM enrollments WHERE student_id IN (
            SELECT student_id FROM parent_students WHERE parent_id = ? AND is_deleted = 0
          ) AND is_deleted = 0 AND status = 'active'
        )`,
        [userId]
      );
    }
    return [];
  }

  // ---- /hostels ---------------------------------------------------------
  if (cleanPath === '/hostels' || cleanPath === 'hostels') {
    if (['school_admin', 'systems_admin', 'principal', 'boarding_master', 'hr'].includes(role)) {
      return await query("SELECT * FROM hostels WHERE is_deleted = 0");
    }
    if (role === 'matron' && userId != null) {
      return await query(
        'SELECT * FROM hostels WHERE is_deleted = 0 AND (warden_id = ? OR warden_id IS NULL)',
        [userId]
      );
    }
    // students/parents/teachers — empty (no offline hostel lookup path in the current UI)
    return [];
  }

  // ---- /courses ---------------------------------------------------------
  if (cleanPath === '/courses' || cleanPath === 'courses') {
    if (isStaff(role)) {
      return await query("SELECT * FROM courses WHERE is_deleted = 0");
    }
    if (role === 'teacher' && userId != null) {
      return await query(
        'SELECT * FROM courses WHERE teacher_id = ? AND is_deleted = 0',
        [userId]
      );
    }
    if (role === 'student' && userId != null) {
      return await query(
        `SELECT * FROM courses WHERE is_deleted = 0 AND class_id IN (
          SELECT class_id FROM enrollments WHERE student_id = ? AND is_deleted = 0 AND status = 'active'
        )`,
        [userId]
      );
    }
    if (role === 'parent' && userId != null) {
      return await query(
        `SELECT * FROM courses WHERE is_deleted = 0 AND class_id IN (
          SELECT class_id FROM enrollments WHERE student_id IN (
            SELECT student_id FROM parent_students WHERE parent_id = ? AND is_deleted = 0
          ) AND is_deleted = 0 AND status = 'active'
        )`,
        [userId]
      );
    }
    return [];
  }

  // ---- /assignments -----------------------------------------------------
  if (cleanPath === '/assignments' || cleanPath === 'assignments') {
    if (isStaff(role)) {
      return await query("SELECT * FROM assignments WHERE is_deleted = 0");
    }
    if (role === 'teacher' && userId != null) {
      return await query(
        `SELECT * FROM assignments WHERE is_deleted = 0 AND course_id IN (
          SELECT id FROM courses WHERE teacher_id = ? AND is_deleted = 0
        )`,
        [userId]
      );
    }
    if (role === 'student' && userId != null) {
      return await query(
        `SELECT * FROM assignments WHERE is_deleted = 0 AND course_id IN (
          SELECT id FROM courses WHERE is_deleted = 0 AND class_id IN (
            SELECT class_id FROM enrollments WHERE student_id = ? AND is_deleted = 0 AND status = 'active'
          )
        )`,
        [userId]
      );
    }
    if (role === 'parent' && userId != null) {
      return await query(
        `SELECT * FROM assignments WHERE is_deleted = 0 AND course_id IN (
          SELECT id FROM courses WHERE is_deleted = 0 AND class_id IN (
            SELECT class_id FROM enrollments WHERE student_id IN (
              SELECT student_id FROM parent_students WHERE parent_id = ? AND is_deleted = 0
            ) AND is_deleted = 0 AND status = 'active'
          )
        )`,
        [userId]
      );
    }
    return [];
  }

  // ---- /submissions -----------------------------------------------------
  if (cleanPath === '/submissions' || cleanPath === 'submissions') {
    if (isStaff(role)) {
      return await query("SELECT * FROM submissions WHERE is_deleted = 0");
    }
    if (role === 'teacher' && userId != null) {
      return await query(
        `SELECT * FROM submissions WHERE is_deleted = 0 AND assignment_id IN (
          SELECT id FROM assignments WHERE is_deleted = 0 AND course_id IN (
            SELECT id FROM courses WHERE teacher_id = ? AND is_deleted = 0
          )
        )`,
        [userId]
      );
    }
    if (role === 'student' && userId != null) {
      return await query(
        'SELECT * FROM submissions WHERE student_id = ? AND is_deleted = 0',
        [userId]
      );
    }
    if (role === 'parent' && userId != null) {
      return await query(
        `SELECT * FROM submissions WHERE student_id IN (
          SELECT student_id FROM parent_students WHERE parent_id = ? AND is_deleted = 0
        ) AND is_deleted = 0`,
        [userId]
      );
    }
    return [];
  }

  // ---- /messages --------------------------------------------------------
  // Always restrict to messages where the caller is sender or recipient.
  if (cleanPath === '/messages' || cleanPath === 'messages') {
    if (userId == null) return [];
    return await query(
      `SELECT * FROM messages
       WHERE is_deleted = 0
         AND (sender_id = ? OR recipient_id = ?)
       ORDER BY created_at DESC`,
      [userId, userId]
    );
  }

  // ---- /events, /calendar ----------------------------------------------
  if (cleanPath === '/events' || cleanPath === 'events' || cleanPath === '/calendar' || cleanPath === 'calendar') {
    // Public events are visible to all; private events only to their creator.
    return await query(
      `SELECT * FROM events
       WHERE is_deleted = 0
         AND (is_public = 1 OR created_by = ?)
       ORDER BY event_date ASC`,
      [userId ?? -1]
    );
  }

  // ---- /notices (audience-scoped, unchanged) ----------------------------
  if (cleanPath === '/notices' || cleanPath === 'notices') {
    const isAdmin = ['school_admin', 'systems_admin', 'principal'].includes(role);
    if (isAdmin) {
      return await query("SELECT * FROM notices WHERE is_deleted = 0 ORDER BY is_pinned DESC, created_at DESC");
    }
    let audience = 'All';
    if (role === 'teacher') audience = 'Teachers';
    else if (role === 'student') audience = 'Students';
    else if (role === 'parent') audience = 'Parents';

    if (role === 'teacher' || role === 'student' || role === 'parent') {
      return await query(
        "SELECT * FROM notices WHERE is_deleted = 0 AND (audience = 'All' OR audience = ?) ORDER BY is_pinned DESC, created_at DESC",
        [audience]
      );
    }
    return await query(
      "SELECT * FROM notices WHERE is_deleted = 0 AND audience = 'All' ORDER BY is_pinned DESC, created_at DESC"
    );
  }

  // ---- /departments -----------------------------------------------------
  if (cleanPath === '/departments' || cleanPath === 'departments') {
    if (['school_admin', 'systems_admin', 'principal', 'hr'].includes(role)) {
      return await query("SELECT * FROM departments WHERE is_deleted = 0");
    }
    if (userId != null) {
      // staff see their own department
      return await query(
        `SELECT * FROM departments WHERE is_deleted = 0 AND id = (
          SELECT department_id FROM users WHERE id = ? AND is_deleted = 0
        )`,
        [userId]
      );
    }
    return [];
  }

  // ---- /enrollments -----------------------------------------------------
  if (cleanPath === '/enrollments' || cleanPath === 'enrollments') {
    if (['school_admin', 'systems_admin', 'principal', 'hr'].includes(role)) {
      return await query("SELECT * FROM enrollments WHERE is_deleted = 0");
    }
    if (role === 'teacher' && userId != null) {
      return await query(
        `SELECT * FROM enrollments WHERE is_deleted = 0 AND class_id IN (
          SELECT id FROM classes WHERE class_teacher_id = ?
        )`,
        [userId]
      );
    }
    if (role === 'student' && userId != null) {
      return await query(
        'SELECT * FROM enrollments WHERE student_id = ? AND is_deleted = 0',
        [userId]
      );
    }
    if (role === 'parent' && userId != null) {
      return await query(
        `SELECT * FROM enrollments WHERE student_id IN (
          SELECT student_id FROM parent_students WHERE parent_id = ? AND is_deleted = 0
        ) AND is_deleted = 0`,
        [userId]
      );
    }
    return [];
  }

  // ---- /student_fees ----------------------------------------------------
  if (cleanPath === '/student_fees' || cleanPath === 'student_fees') {
    if (['school_admin', 'systems_admin', 'principal', 'bursar', 'hr'].includes(role)) {
      return await query("SELECT * FROM student_fees WHERE is_deleted = 0");
    }
    if (role === 'student' && userId != null) {
      return await query(
        'SELECT * FROM student_fees WHERE student_id = ? AND is_deleted = 0',
        [userId]
      );
    }
    if (role === 'parent' && userId != null) {
      return await query(
        `SELECT * FROM student_fees WHERE student_id IN (
          SELECT student_id FROM parent_students WHERE parent_id = ? AND is_deleted = 0
        ) AND is_deleted = 0`,
        [userId]
      );
    }
    return [];
  }

  // ---- /fee_groups ------------------------------------------------------
  if (cleanPath === '/fee_groups' || cleanPath === 'fee_groups') {
    // fee_groups is a published catalog; all roles can see it
    return await query("SELECT * FROM fee_groups WHERE is_deleted = 0");
  }

  // ---- /payments --------------------------------------------------------
  if (cleanPath === '/payments' || cleanPath === 'payments') {
    if (['school_admin', 'systems_admin', 'principal', 'bursar', 'hr'].includes(role)) {
      return await query("SELECT * FROM payments WHERE is_deleted = 0");
    }
    if (role === 'student' && userId != null) {
      return await query(
        `SELECT p.* FROM payments p
         JOIN student_fees sf ON sf.id = p.student_fee_id
         WHERE sf.student_id = ? AND p.is_deleted = 0`,
        [userId]
      );
    }
    if (role === 'parent' && userId != null) {
      return await query(
        `SELECT p.* FROM payments p
         JOIN student_fees sf ON sf.id = p.student_fee_id
         WHERE sf.student_id IN (
           SELECT student_id FROM parent_students WHERE parent_id = ? AND is_deleted = 0
         ) AND p.is_deleted = 0`,
        [userId]
      );
    }
    return [];
  }

  // ---- Student Medical --------------------------------------------------
  if (cleanPath === '/dining/allergies' || cleanPath === 'medical/dining/allergies') {
    return await query(`
      SELECT p.uid, p.student_id, p.blood_group, p.allergies,
             p.dietary_restrictions, p.emergency_instructions,
             u.first_name, u.last_name,
             e.class_id, c.name AS class_name
      FROM student_medical_profiles p
      JOIN users u ON u.id = p.student_id
      LEFT JOIN enrollments e ON e.student_id = u.id AND e.is_deleted = 0 AND e.status = 'active'
      LEFT JOIN classes c ON c.id = e.class_id
      WHERE p.is_deleted = 0 AND u.is_deleted = 0
      ORDER BY c.name, u.last_name, u.first_name
    `);
  }

  // /medical/profiles/:student_uid — guard so a caller can only see their
  // own profile (or, for parents, their children's; for staff, anyone in
  // their scope).
  if (cleanPath.startsWith('/medical/profiles/') || cleanPath.startsWith('medical/profiles/')) {
    const idOrUid = cleanPath.split('/').pop();
    if (!idOrUid || userId == null) return [];
    const targetUserId = await resolveStudentId(idOrUid);
    if (targetUserId == null) return [];
    if (!(await canViewMedical(user, role, targetUserId))) return [];
    return await query(
      'SELECT * FROM student_medical_profiles WHERE student_id = ? AND is_deleted = 0',
      [targetUserId]
    );
  }

  if (cleanPath.startsWith('/medical/logs/') || cleanPath.startsWith('medical/logs/')) {
    const idOrUid = cleanPath.split('/').pop();
    if (!idOrUid || userId == null) return [];
    const targetUserId = await resolveStudentId(idOrUid);
    if (targetUserId == null) return [];
    if (!(await canViewMedical(user, role, targetUserId))) return [];
    return await query(
      'SELECT * FROM student_medication_logs WHERE student_id = ? AND is_deleted = 0 ORDER BY administered_at DESC',
      [targetUserId]
    );
  }

  if (cleanPath.startsWith('/medical/history/') || cleanPath.startsWith('medical/history/')) {
    const idOrUid = cleanPath.split('/').pop();
    if (!idOrUid || userId == null) return [];
    const targetUserId = await resolveStudentId(idOrUid);
    if (targetUserId == null) return [];
    if (!(await canViewMedical(user, role, targetUserId))) return [];
    return await query(
      'SELECT * FROM student_medical_history WHERE student_id = ? AND is_deleted = 0 ORDER BY onset_date DESC, created_at DESC',
      [targetUserId]
    );
  }

  // ---- Unknown route — B.2: deny-by-default -----------------------------
  // Generic SQL interpolation is unsafe; explicit allowlist only. Returning
  // [] here is the contract. Callers that need a 404 to behave like a 404
  // will see an empty array, which is the same shape the explicit handlers
  // return on no-results.
  console.warn(`[offline] No explicit handler for GET ${cleanPath} — returning []`);
  return [];
}

// ---------------------------------------------------------------------------
// Offline POST handler — explicit allowlist + queue-on-unknown.
//
// B.3 changed the architecture: unknown POST/PUT/DELETE writes are
// intercepted by the response interceptor and pushed onto the offline
// queue (offlineQueue.ts) BEFORE this function is reached. So this
// function is now reserved for the rare case where a page component
// wants to write directly to the local SQLite cache without going
// through the network — e.g. an attendance grid that wants the new
// row visible in the UI immediately. As of WT-B no caller does this;
// the path is kept for future use and documented here.
//
// The known paths are: /attendance/bulk, /users (admin only),
// /calendar and /events. Anything else returns { success: false, queued:
// true } so the UI can surface a "queued" toast.
// ---------------------------------------------------------------------------
async function handleOfflineWrite(config: any, method: 'post' | 'put' | 'delete' | 'patch'): Promise<any> {
  const url: string = config.url || '';
  const cleanPath = url.replace(/^\/?api/, '').split('?')[0];
  let data: any = {};
  if (config.data) {
    data = typeof config.data === 'string' ? JSON.parse(config.data) : config.data;
  }
  const getAuthUser = () => {
    try {
      const storage = localStorage.getItem('auth-storage');
      if (storage) {
        const parsed = JSON.parse(storage);
        return parsed?.state?.user || null;
      }
    } catch (e) {
      console.error(e);
    }
    return null;
  };
  const user = getAuthUser();

  if (method === 'post' && (cleanPath === '/attendance/bulk' || cleanPath === 'attendance/bulk')) {
    const records = data.records || [];
    for (const r of records) {
      const uid = r.uid || uuidv4();
      await execute(`
        INSERT INTO attendance (uid, student_id, class_id, date, status, sync_status, updated_at)
        VALUES (?, ?, ?, ?, ?, 'pending', CURRENT_TIMESTAMP)
        ON CONFLICT(uid) DO UPDATE SET status = EXCLUDED.status, sync_status = 'pending', updated_at = CURRENT_TIMESTAMP
      `, [uid, r.student_id, r.class_id, r.date, r.status]);
    }
    return { success: true, inserted: records.length };
  }

  if (method === 'post' && (cleanPath === '/users' || cleanPath === 'users')) {
    // Only admin-style roles can create users offline.
    const role = user?.role || '';
    if (!['school_admin', 'systems_admin', 'hr'].includes(role)) {
      return { success: false, queued: true, reason: 'forbidden' };
    }
    const bcryptmod = await import('bcryptjs');
    const bcrypt = (bcryptmod.default || bcryptmod) as typeof import('bcryptjs');
    const passwordHash = data.password ? bcrypt.hashSync(data.password, 10) : null;
    const uid = data.uid || uuidv4();
    const gender = data.gender || null;
    if (gender && !['male', 'female', 'other'].includes(gender)) {
      return { success: false, queued: false, reason: 'invalid_gender' };
    }
    await execute(
      `INSERT INTO users (uid, email, password, role, first_name, last_name, phone, gender, department_id, sync_status, is_deleted)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 'pending', 0)`,
      [
        uid,
        data.email,
        passwordHash,
        data.role || 'teacher',
        data.first_name || '',
        data.last_name || '',
        data.phone || null,
        gender,
        data.department_id || null,
      ]
    );
    return { success: true, uid, offline_queued: true };
  }

  if (method === 'post' && (cleanPath === '/calendar' || cleanPath === 'calendar' || cleanPath === '/events' || cleanPath === 'events')) {
    const uid = data.uid || uuidv4();
    const createdBy = user?.id ?? null;
    await execute(`
      INSERT INTO events (uid, title, description, event_date, end_date, event_type, venue, created_by, is_public, academic_year, sync_status, is_deleted)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'pending', 0)
    `, [
      uid,
      data.title,
      data.description || null,
      data.event_date,
      data.end_date || null,
      data.event_type || 'other',
      data.venue || null,
      createdBy,
      data.is_public === undefined ? 1 : (data.is_public ? 1 : 0),
      data.academic_year || null
    ]);
    return { success: true, uid };
  }

  // Unknown write — the response interceptor already enqueued it. Return
  // a synthetic queued-confirmation so callers that expect {success:true}
  // don't break.
  console.warn(`[offline] No explicit handler for ${method.toUpperCase()} ${cleanPath} — already queued`);
  return { success: false, queued: true };
}

// ---------------------------------------------------------------------------
// Helpers
// ---------------------------------------------------------------------------
function isStaffPrivileged(role: string): boolean {
  return ['school_admin', 'systems_admin', 'principal', 'hr'].includes(role);
}
function isStaff(role: string): boolean {
  return [
    'school_admin', 'systems_admin', 'principal', 'hr', 'bursar',
    'librarian', 'clubs_head',
  ].includes(role);
}

async function resolveStudentId(idOrUid: string): Promise<number | null> {
  if (/^\d+$/.test(idOrUid)) {
    const n = parseInt(idOrUid, 10);
    return Number.isFinite(n) ? n : null;
  }
  const rows = await query('SELECT id FROM users WHERE uid = ? AND is_deleted = 0', [idOrUid]);
  return rows && rows.length > 0 ? rows[0].id : null;
}

async function canViewMedical(
  user: { id: number; role: string } | null,
  role: string,
  targetStudentId: number
): Promise<boolean> {
  if (!user) return false;
  // The target user themselves
  if (user.id === targetStudentId) return true;
  // Staff with medical read access (nurse, dining_staff for the kitchen roster)
  if (['nurse', 'dining_staff', 'school_admin', 'systems_admin', 'principal'].includes(role)) return true;
  // Parents — only their own children
  if (role === 'parent') {
    const rows = await query(
      'SELECT 1 AS x FROM parent_students WHERE parent_id = ? AND student_id = ? AND is_deleted = 0 LIMIT 1',
      [user.id, targetStudentId]
    );
    return rows && rows.length > 0;
  }
  // Teachers — only students in their classes
  if (role === 'teacher') {
    const rows = await query(
      `SELECT 1 AS x FROM enrollments
       WHERE student_id = ? AND is_deleted = 0 AND status = 'active'
         AND class_id IN (
           SELECT id FROM classes WHERE class_teacher_id = ?
         ) LIMIT 1`,
      [targetStudentId, user.id]
    );
    return rows && rows.length > 0;
  }
  return false;
}

export default api;
export { api, useOfflineQueue };
