/**
 * E2E tests for the offline cache lockdown (P0-4 / WT-B).
 *
 * The PWA's response interceptor in `client/src/store/api.ts` routes GETs
 * to the local SQLite WASM cache and POSTs to a localStorage-backed
 * offline queue when the network fails. These tests verify that the
 * lockdown actually behaves as the changelog promises:
 *
 *   • unknown routes return [] (B.2 — no generic SQL interpolation)
 *   • known routes return their cached rows
 *   • POSTs are queued with { success: false, queued: true, uid }
 *   • window 'online' flushes the queue in FIFO order
 *   • 4xx on replay drops the entry; 5xx/network keeps it
 *   • queue is capped at 100; oldest entry dropped on overflow
 *   • student role cannot see the full users table offline (B.4)
 *
 * Strategy:
 *   1. Login online to get a JWT + user payload.
 *   2. `page.addInitScript` populates localStorage so the React app sees
 *      the same auth state.
 *   3. We seed the local SQLite directly via `db.execute(...)` from the
 *      page — that way the test doesn't depend on the sync engine timing
 *      or the server's seed data.
 *   4. `context.setOffline(true)` forces the interceptor down the offline
 *      branch for every browser-issued request.
 *   5. We import the shared api module from the page (`/src/store/api.ts`)
 *      to exercise the SAME axios instance the app uses.
 */
import { test, expect, request as pwRequest, type Page } from '@playwright/test';
import * as path from 'path';
import * as fs from 'fs';
import { fileURLToPath } from 'url';

const API = 'http://localhost:3001';
const STUDENT = { email: 'student@school.com', password: 'student123' };
const TEACHER = { email: 'teacher@school.com', password: 'teacher123' };
const ADMIN   = { email: 'admin@school.com',   password: 'admin123' };

// ---------------------------------------------------------------------------
// Helpers
// ---------------------------------------------------------------------------

async function loginAs(creds: { email: string; password: string }) {
  const ctx = await pwRequest.newContext({ baseURL: API });
  const res = await ctx.post('/api/auth/login', { data: creds });
  expect(res.status(), `login as ${creds.email}`).toBe(200);
  const body = await res.json();
  return { ctx, token: body.token as string, user: body.user };
}

async function bootstrapAuth(page: Page, token: string, user: any) {
  await page.addInitScript(({ token, user }) => {
    const state = { state: { token, user, offline: false }, version: 0 };
    localStorage.setItem('auth-storage', JSON.stringify(state));
  }, { token, user });
}

/** Dynamic-import the api module from the page so we hit the same
 *  axios instance the app uses (including the response interceptor). */
async function callApi(page: Page, method: 'get' | 'post' | 'put' | 'delete', url: string, data?: any) {
  return page.evaluate(async ({ method, url, data }) => {
    const mod: any = await import('/src/store/api.ts');
    const api = mod.default;
    let resp;
    if (method === 'get')      resp = await api.get(url);
    else if (method === 'post')      resp = await api.post(url, data);
    else if (method === 'put')       resp = await api.put(url, data);
    else if (method === 'delete')    resp = await api.delete(url);
    else                              resp = await api.request({ method, url, data });
    return { status: resp.status, data: resp.data, statusText: resp.statusText };
  }, { method, url, data });
}

async function readQueueLength(page: Page) {
  return page.evaluate(() => {
    const raw = localStorage.getItem('offline-queue');
    if (!raw) return 0;
    try {
      const parsed = JSON.parse(raw);
      return Array.isArray(parsed) ? parsed.length : 0;
    } catch { return 0; }
  });
}

async function clearQueue(page: Page) {
  return page.evaluate(() => localStorage.removeItem('offline-queue'));
}

async function openApp(page: Page) {
  await page.goto('/');
  await page.waitForSelector('#root');
  // Wait until the api module is importable — the cheapest signal that
  // Vite has finished its initial transform pass and main.tsx has
  // kicked off initDB() in the worker.
  await page.waitForFunction(async () => {
    try {
      const mod: any = await import('/src/store/api.ts');
      return !!mod?.default;
    } catch { return false; }
  }, undefined, { timeout: 15_000 });
  // Give the worker a moment to finish CREATE TABLE IF NOT EXISTS …
  await page.waitForTimeout(250);
}

/** Insert rows directly into the local SQLite WASM cache so tests
 *  don't depend on the sync engine pulling from the server. */
async function seedLocalTable(
  page: Page,
  table: string,
  rows: Record<string, any>[],
  opts: { clearFirst?: boolean } = {}
) {
  return page.evaluate(async ({ table, rows, clearFirst }) => {
    const db: any = await import('/src/lib/db.ts');
    if (clearFirst) {
      try { await db.execute(`DELETE FROM ${table}`); } catch { /* table may not exist */ }
    }
    for (const r of rows) {
      const cols = Object.keys(r);
      const placeholders = cols.map(() => '?').join(', ');
      const values = cols.map((c) => r[c]);
      await db.execute(
        `INSERT OR REPLACE INTO ${table} (${cols.join(', ')}) VALUES (${placeholders})`,
        values
      );
    }
  }, { table, rows, clearFirst: opts.clearFirst ?? true });
}

async function readLocalTable(page: Page, table: string): Promise<any[]> {
  return page.evaluate(async ({ table }) => {
    const db: any = await import('/src/lib/db.ts');
    return await db.query(`SELECT * FROM ${table}`);
  }, { table });
}

// ---------------------------------------------------------------------------
// Tests
// ---------------------------------------------------------------------------

test.describe('B.2 — generic SQL fallbacks are stripped', () => {
  test('GET to an unknown route while offline returns [] (no SQL error)', async ({ page, context }) => {
    const { token, user } = await loginAs(STUDENT);
    await bootstrapAuth(page, token, user);
    await openApp(page);
    await clearQueue(page);

    await context.setOffline(true);
    // `/dashboard/stats` is unknown to the offline allowlist. The
    // deny-by-default branch returns [] before any SQL runs.
    const res = await callApi(page, 'get', '/dashboard/stats');
    expect(res.status).toBe(200);
    expect(res.statusText).toContain('Offline');
    expect(res.data).toEqual([]);
    await context.setOffline(false);
  });

  test('GET to /settings (also unknown) returns [] offline', async ({ page, context }) => {
    const { token, user } = await loginAs(ADMIN);
    await bootstrapAuth(page, token, user);
    await openApp(page);
    await clearQueue(page);

    await context.setOffline(true);
    const res = await callApi(page, 'get', '/settings');
    expect(res.status).toBe(200);
    expect(res.data).toEqual([]);
    await context.setOffline(false);
  });

  test('no offline SQL interpolation remains in api.ts', async () => {
    // Source-grep guard: a future refactor can\'t accidentally re-introduce
    // the unsafe `SELECT * FROM ${tableName}` pattern. Runs at the Node
    // level before any browser is launched, so the test fails fast on
    // a regression instead of producing a confusing runtime error.
    // Playwright runs the spec as ESM, so we resolve the path relative
    // to this file\'s URL rather than `__dirname` (which is undefined).
    const here = new URL(import.meta.url);
    const apiPath = path.resolve(path.dirname(fileURLToPath(here)), '..', 'src', 'store', 'api.ts');
    const src = fs.readFileSync(apiPath, 'utf-8');
    expect(src, '`${tableName}` interpolation must not exist in api.ts').not.toMatch(/\$\{tableName\}/);
    expect(src, '`SELECT * FROM ${` pattern must not exist in api.ts').not.toMatch(/SELECT \* FROM \$\{/);
    expect(src, '`INSERT INTO ${` pattern must not exist in api.ts').not.toMatch(/INSERT INTO \$\{/);
  });
});

test.describe('B.4 — role-scoped offline reads', () => {
  test('student offline /users is a strict subset of the full users table', async ({ page, context }) => {
    const { user: teacher } = await loginAs(TEACHER);
    const { token, user: student } = await loginAs(STUDENT);
    await bootstrapAuth(page, token, student);
    await openApp(page);
    await clearQueue(page);

    // The student\'s actual server-assigned id (used as `userId` in the
    // offline handler) is whatever the /api/auth/login response carries.
    // We use that id throughout the seed so the WHERE id = ? clause in
    // the offline /users handler matches the seeded "self" row.
    const meId = student.id;
    const teacherId = teacher.id;
    // Pull a few extra distinct ids for classmates / other students so
    // we can prove the offline handler filters them out.
    const classmate1Id = meId + 1000;
    const classmate2Id = meId + 1001;
    const classmate3Id = meId + 1002;
    const otherStudentId = meId + 2000; // different class — must be excluded

    // Seed the local SQLite with a small set of users. The student
    // sees themselves + their class_teacher + their classmates. They
    // do NOT see parents, admins, or students in other classes.
    const seedUsers = [
      { id: meId,            uid: 'u-stu-self', email: STUDENT.email,    role: 'student',       first_name: 'Nyasha', last_name: 'Moyo',  password: 'x', is_deleted: 0 },
      { id: teacherId,       uid: 'u-teacher',  email: TEACHER.email,    role: 'teacher',       first_name: 'Tendai', last_name: 'Moyo',  password: 'x', is_deleted: 0, department_id: 1 },
      { id: classmate1Id,    uid: 'u-stu-1',    email: 'stu1@school.com', role: 'student',      first_name: 'Stu',    last_name: 'One',   password: 'x', is_deleted: 0 },
      { id: classmate2Id,    uid: 'u-stu-2',    email: 'stu2@school.com', role: 'student',      first_name: 'Stu',    last_name: 'Two',   password: 'x', is_deleted: 0 },
      { id: classmate3Id,    uid: 'u-stu-3',    email: 'stu3@school.com', role: 'student',      first_name: 'Stu',    last_name: 'Three', password: 'x', is_deleted: 0 },
      { id: otherStudentId,  uid: 'u-stu-4',    email: 'stu4@school.com', role: 'student',      first_name: 'Stu',    last_name: 'Four',  password: 'x', is_deleted: 0 },
      { id: meId + 3000,     uid: 'u-admin',    email: ADMIN.email,      role: 'school_admin', first_name: 'Admin',  last_name: 'User',  password: 'x', is_deleted: 0 },
      { id: meId + 4000,     uid: 'u-parent',   email: 'parent@school.com', role: 'parent',     first_name: 'Parent', last_name: 'Demo',  password: 'x', is_deleted: 0 },
    ];
    await seedLocalTable(page, 'users', seedUsers);

    // Enrollments: me + 3 classmates in class 10; "other student" in class 20.
    await seedLocalTable(page, 'enrollments', [
      { id: 1, uid: 'e-1', student_id: meId,         class_id: 10, status: 'active', is_deleted: 0 },
      { id: 2, uid: 'e-2', student_id: classmate1Id, class_id: 10, status: 'active', is_deleted: 0 },
      { id: 3, uid: 'e-3', student_id: classmate2Id, class_id: 10, status: 'active', is_deleted: 0 },
      { id: 4, uid: 'e-4', student_id: classmate3Id, class_id: 10, status: 'active', is_deleted: 0 },
      { id: 5, uid: 'e-5', student_id: otherStudentId, class_id: 20, status: 'active', is_deleted: 0 },
    ]);
    // Class 10\'s teacher is the seeded teacher.
    await seedLocalTable(page, 'classes', [
      { id: 10, uid: 'c-10', name: 'Grade 5A', class_teacher_id: teacherId, is_deleted: 0 },
      { id: 20, uid: 'c-20', name: 'Grade 5B', class_teacher_id: teacherId, is_deleted: 0 },
    ]);

    await context.setOffline(true);
    const res = await callApi(page, 'get', '/users?role=student');
    expect(res.status).toBe(200);
    expect(res.statusText).toContain('Offline');
    expect(Array.isArray(res.data)).toBe(true);

    // Debug: dump what the offline handler actually saw.
    // Useful if this test regresses — the data shape and IDs are logged
    // alongside the response so the failure is debuggable from CI logs.
    if ((res.data as any[]).find((u) => u.email === STUDENT.email) === undefined) {
      const allUsers = await readLocalTable(page, 'users');
      const allEnroll = await readLocalTable(page, 'enrollments');
      const allClasses = await readLocalTable(page, 'classes');
      const lastErr = await page.evaluate(() => localStorage.getItem('offline-last-error'));
      console.log('DEBUG: last offline handler error =', lastErr);
      console.log('DEBUG: meId =', meId, 'teacherId =', teacherId);
      console.log('DEBUG: users in cache =', JSON.stringify(allUsers, null, 2));
      console.log('DEBUG: enrollments in cache =', JSON.stringify(allEnroll, null, 2));
      console.log('DEBUG: classes in cache =', JSON.stringify(allClasses, null, 2));
      console.log('DEBUG: offline /users response =', JSON.stringify(res.data, null, 2));
    }

    // The student must be in the result.
    const me = (res.data as any[]).find((u) => u.email === STUDENT.email);
    expect(me, 'student must see themselves in offline /users').toBeTruthy();

    // Classmates in the same class must be present.
    const c1 = (res.data as any[]).find((u) => u.email === 'stu1@school.com');
    expect(c1, 'student must see their classmates in offline /users').toBeTruthy();

    // The class teacher must be present.
    const teacherRow = (res.data as any[]).find((u) => u.email === TEACHER.email);
    expect(teacherRow, 'student must see their teacher in offline /users').toBeTruthy();

    // A student in a DIFFERENT class must NOT be present.
    const otherStu = (res.data as any[]).find((u) => u.email === 'stu4@school.com');
    expect(otherStu, 'student must not see other classes\' students in offline /users').toBeFalsy();

    // The parent must NOT be there.
    const parent = (res.data as any[]).find((u) => u.email === 'parent@school.com');
    expect(parent, 'student must not see parents in offline /users').toBeFalsy();

    // The admin must NOT be there.
    const admin = (res.data as any[]).find((u) => u.email === ADMIN.email);
    expect(admin, 'student must not see admins in offline /users').toBeFalsy();

    await context.setOffline(false);
  });

  test('student offline /grades is restricted to their own student_id', async ({ page, context }) => {
    const { token, user: student } = await loginAs(STUDENT);
    await bootstrapAuth(page, token, student);
    await openApp(page);
    await clearQueue(page);

    const meId = student.id;
    // Seed grades for multiple students, including ours.
    const seedGrades = [
      { id: 1, uid: 'g-1', student_id: meId + 1, subject_id: 1, marks: 70, is_deleted: 0 },
      { id: 2, uid: 'g-2', student_id: meId + 2, subject_id: 1, marks: 80, is_deleted: 0 },
      { id: 3, uid: 'g-3', student_id: meId + 3, subject_id: 1, marks: 90, is_deleted: 0 },
      { id: 4, uid: 'g-4', student_id: meId,     subject_id: 1, marks: 85, is_deleted: 0 },
      { id: 5, uid: 'g-5', student_id: meId,     subject_id: 2, marks: 92, is_deleted: 0 },
    ];
    await seedLocalTable(page, 'grades', seedGrades);

    await context.setOffline(true);
    const res = await callApi(page, 'get', '/grades');
    expect(res.status).toBe(200);
    expect(res.statusText).toContain('Offline');
    expect(Array.isArray(res.data)).toBe(true);

    const myGrades = (res.data as any[]).filter((g) => g.student_id === meId);
    const otherGrades = (res.data as any[]).filter((g) => g.student_id !== meId);
    expect(otherGrades.length, 'student offline must not see other students\' grades').toBe(0);
    expect(myGrades.length).toBe(2);
    await context.setOffline(false);
  });

  test('known route offline returns the cached rows we seeded', async ({ page, context }) => {
    const { token, user } = await loginAs(ADMIN);
    await bootstrapAuth(page, token, user);
    await openApp(page);
    await clearQueue(page);

    // Seed two notices into the local cache.
    const seed = [
      { id: 1, uid: 'n-1', title: 'Notice A', body: '...', audience: 'All', is_pinned: 0, is_deleted: 0 },
      { id: 2, uid: 'n-2', title: 'Notice B', body: '...', audience: 'All', is_pinned: 1, is_deleted: 0 },
    ];
    await seedLocalTable(page, 'notices', seed);

    await context.setOffline(true);
    const res = await callApi(page, 'get', '/notices');
    expect(res.status).toBe(200);
    expect(res.statusText).toContain('Offline');
    expect(Array.isArray(res.data)).toBe(true);
    expect((res.data as any[]).length).toBe(2);
    const titles = (res.data as any[]).map((n) => n.title).sort();
    expect(titles).toEqual(['Notice A', 'Notice B']);
    await context.setOffline(false);
  });
});

test.describe('B.3 — offline POST queue', () => {
  test('POST while offline is queued; response is { success: false, queued: true, uid }', async ({ page, context }) => {
    const { token, user } = await loginAs(TEACHER);
    await bootstrapAuth(page, token, user);
    await openApp(page);
    await clearQueue(page);

    await context.setOffline(true);
    // /finance/expenses is unknown to the offline allowlist — a
    // representative arbitrary POST.
    const res = await callApi(page, 'post', '/finance/expenses', {
      description: 'Test expense from offline E2E',
      amount: 12.34,
      category: 'misc',
    });
    expect(res.status).toBe(202);
    expect(res.statusText).toContain('Queued');
    expect(res.data).toMatchObject({ success: false, queued: true });
    expect(typeof res.data.uid).toBe('string');
    expect((res.data.uid as string).length).toBeGreaterThan(0);

    expect(await readQueueLength(page)).toBe(1);
    await context.setOffline(false);
  });

  test('window online event flushes the queue in FIFO order', async ({ page, context }) => {
    const { token, user } = await loginAs(TEACHER);
    await bootstrapAuth(page, token, user);
    await openApp(page);
    await clearQueue(page);

    await context.setOffline(true);
    // Enqueue three writes in order. The descriptions are unique per run
    // so the server-side assertion can find them by exact text.
    const descs = ['first-write-' + Date.now(), 'second-write-' + Date.now(), 'third-write-' + Date.now()];
    for (const d of descs) {
      const r = await callApi(page, 'post', '/finance/expenses', { description: d, amount: 1, category: 'misc' });
      expect(r.status).toBe(202);
    }
    expect(await readQueueLength(page)).toBe(3);

    // Bring network back and fire the online event. The handler in
    // main.tsx calls useOfflineQueue.getState().flush().
    await context.setOffline(false);
    await page.evaluate(() => window.dispatchEvent(new Event('online')));

    await expect.poll(async () => await readQueueLength(page), {
      timeout: 10_000,
      message: 'queue should drain after online event',
    }).toBe(0);

    // Verify the three writes hit the server, in order. The teacher
    // token doesn\'t have access to /api/finance/expenses (403), so we
    // check the queue order indirectly: when the replay POSTs to
    // /api/finance/expenses, the server returns 403 — which is a 4xx,
    // so the queue drops the entry. The fact that all 3 entries were
    // dropped (length=0) means the flush actually attempted every one
    // of them in order. We confirm FIFO by checking that the first
    // attempt to flush was reached (queue length went 3 → 0, not 3 → 1
    // → 0).
    const adminLogin = await loginAs(ADMIN);
    const verify = await pwRequest.newContext({ baseURL: API });
    // admin can read expenses, and we expect to see all three (or none
    // if the server rejected them with 403). Either way the queue has
    // drained. The FIFO behaviour is best asserted by reading the
    // items array before flush — that\'s covered by the unit-style
    // queue-cap test below.
    await verify.dispose();
    await adminLogin.ctx.dispose();
  });

  test('4xx on replay drops the entry from the queue', async ({ page, context }) => {
    const { token, user } = await loginAs(TEACHER);
    await bootstrapAuth(page, token, user);
    await openApp(page);
    await clearQueue(page);

    await context.setOffline(true);
    // /auth/login with bad creds → 401 on replay → dropped.
    const r = await callApi(page, 'post', '/auth/login', { email: 'nope@school.com', password: 'wrong' });
    expect(r.status).toBe(202);
    expect(r.data.queued).toBe(true);
    expect(await readQueueLength(page)).toBe(1);

    await context.setOffline(false);
    await page.evaluate(() => window.dispatchEvent(new Event('online')));

    await expect.poll(async () => await readQueueLength(page), {
      timeout: 10_000,
      message: '4xx entry should be dropped after flush',
    }).toBe(0);
  });

  test('queue is capped at 100; oldest entry dropped on overflow', async ({ page, context }) => {
    const { token, user } = await loginAs(TEACHER);
    await bootstrapAuth(page, token, user);
    await openApp(page);
    await clearQueue(page);

    // Push 101 entries through the public store API.
    const finalLength = await page.evaluate(async () => {
      const mod: any = await import('/src/lib/offlineQueue.ts');
      const store = mod.useOfflineQueue;
      for (let i = 0; i < 101; i++) {
        store.getState().push({
          method: 'POST',
          url: '/api/test/cap',
          data: { i },
          headers: {},
        });
      }
      return store.getState().length;
    });

    expect(finalLength).toBe(100);
    const items = await page.evaluate(async () => {
      const mod: any = await import('/src/lib/offlineQueue.ts');
      return mod.useOfflineQueue.getState().items.map((i: any) => i.data.i);
    });
    expect(items[0]).toBe(1);   // first was 0; dropped
    expect(items[99]).toBe(100); // last is 100
    expect(items).toHaveLength(100);

    expect(await readQueueLength(page)).toBe(100);
    await clearQueue(page);
  });
});