/**
 * Server-side storage for the workspace book.
 *
 * Why this exists: the app shipped with the whole book in `localStorage`
 * (src/lib/store.ts). That is per-browser and per-device, and a failed read
 * would let the store persist its SEED default straight over real data. This
 * middleware makes Postgres the source of truth; localStorage stays on as an
 * offline cache.
 *
 * Why a Nitro middleware rather than a TanStack server route: `serverDir:
 * "./server"` in vite.config.ts already auto-registers this directory (see
 * grok-pwa.ts), and it is stable across the Start betas this app is pinned to.
 *
 * Why FIELDBOOK_DATABASE_URL and not DATABASE_URL: setting DATABASE_URL makes
 * src/lib/auth/verify.server.ts throw on purpose — auth is disabled here, and
 * it refuses to serve a shared dev user off a real database. That invariant is
 * worth keeping, so this uses its own variable and its own connection, and
 * never touches src/lib/db.ts.
 *
 * Access control is Apache basic auth in front (see the vhost), which passes
 * the authenticated user through as x-remote-user.
 */
import pg from "pg";

interface BookEvent {
  url: URL;
  req: {
    method: string;
    headers: Headers;
    json: () => Promise<unknown>;
  };
}

/** Rows are keyed by the Apache basic-auth user. */
const FALLBACK_USER = "default";

/** Reject absurd payloads before they reach Postgres. */
const MAX_BODY_BYTES = 8 * 1024 * 1024;

/** How long superseded versions stay recoverable. */
const HISTORY_RETENTION_DAYS = 90;

// The pool lives on globalThis: dev HMR and Nitro's module graph can both
// evaluate this file more than once, and a second pool would quietly double
// the connection count against Postgres.
const globalRef = globalThis as typeof globalThis & {
  __fieldbookPool__?: pg.Pool;
};

function pool(): pg.Pool | null {
  const url = process.env.FIELDBOOK_DATABASE_URL?.trim();
  if (!url) return null;
  if (!globalRef.__fieldbookPool__) {
    globalRef.__fieldbookPool__ = new pg.Pool({
      connectionString: url,
      max: 4,
      idleTimeoutMillis: 30_000,
      connectionTimeoutMillis: 5_000,
    });
    // A pool that emits 'error' with no listener takes the process down, and
    // Postgres closing an idle connection is routine, not fatal.
    globalRef.__fieldbookPool__.on("error", (err) => {
      console.error("[book-api] idle client error:", err.message);
    });
  }
  return globalRef.__fieldbookPool__;
}

function userId(event: BookEvent): string {
  const raw = event.req.headers.get("x-remote-user")?.trim();
  // Apache sends the basic-auth username; anything else is untrusted input, so
  // keep it to a conservative shape rather than passing it straight to SQL as
  // an identity.
  if (!raw || raw.length > 128 || !/^[A-Za-z0-9._@-]+$/.test(raw)) return FALLBACK_USER;
  return raw;
}

function json(body: unknown, status = 200): Response {
  return new Response(JSON.stringify(body), {
    status,
    headers: {
      "content-type": "application/json; charset=utf-8",
      // The book is per-user mutable state; never let a proxy hold onto it.
      "cache-control": "no-store",
    },
  });
}

/**
 * The client sends the whole workspace. Validate the envelope shape only — the
 * field-level schema is the client's business, and rejecting unknown keys here
 * would break the app every time a new feature adds one.
 */
function validBook(data: unknown): data is Record<string, unknown> {
  if (data === null || typeof data !== "object" || Array.isArray(data)) return false;
  const d = data as Record<string, unknown>;
  for (const key of ["projects", "tasks", "notes", "reminders", "boards"]) {
    if (!Array.isArray(d[key])) return false;
  }
  // Optional: books saved before these sections existed do not have them.
  for (const key of ["waiting", "scratch"]) {
    if (d[key] !== undefined && !Array.isArray(d[key])) return false;
  }
  return true;
}

export default async function bookApiMiddleware(
  event: BookEvent,
  next: () => unknown | Promise<unknown>,
): Promise<unknown> {
  const path = event.url.pathname;
  if (path !== "/api/book" && !path.startsWith("/api/book/")) return next();

  const db = pool();
  if (!db) {
    // Explicit and loud: without this the client would silently fall back to
    // browser-only storage, which is the failure mode this whole change exists
    // to remove.
    return json({ error: "FIELDBOOK_DATABASE_URL is not configured" }, 503);
  }

  const method = (event.req.method ?? "GET").toUpperCase();
  const user = userId(event);

  try {
    // --- GET /api/book -----------------------------------------------------
    if (path === "/api/book" && method === "GET") {
      const rows = await db.query<{ data: unknown; revision: string; updated_at: Date }>(
        "select data, revision, updated_at from book where user_id = $1",
        [user],
      );
      if (rows.rowCount === 0) {
        // 200 with revision 0, not 404: "you have no book yet" is a normal
        // first-run state, and the client uses revision 0 to mean "create".
        return json({ data: null, revision: 0, updatedAt: null });
      }
      const row = rows.rows[0];
      return json({
        data: row.data,
        revision: Number(row.revision),
        updatedAt: row.updated_at.toISOString(),
      });
    }

    // --- GET /api/book/history --------------------------------------------
    if (path === "/api/book/history" && method === "GET") {
      const rows = await db.query<{ id: string; revision: string; saved_at: Date }>(
        `select id, revision, saved_at from book_history
          where user_id = $1 order by saved_at desc limit 100`,
        [user],
      );
      return json({
        versions: rows.rows.map((r) => ({
          id: Number(r.id),
          revision: Number(r.revision),
          savedAt: r.saved_at.toISOString(),
        })),
      });
    }

    // --- GET /api/book/history/:id ----------------------------------------
    const historyMatch = /^\/api\/book\/history\/(\d+)$/.exec(path);
    if (historyMatch && method === "GET") {
      const rows = await db.query<{ data: unknown; revision: string; saved_at: Date }>(
        "select data, revision, saved_at from book_history where id = $1 and user_id = $2",
        [Number(historyMatch[1]), user],
      );
      if (rows.rowCount === 0) return json({ error: "no such version" }, 404);
      const row = rows.rows[0];
      return json({
        data: row.data,
        revision: Number(row.revision),
        savedAt: row.saved_at.toISOString(),
      });
    }

    // --- PUT /api/book -----------------------------------------------------
    if (path === "/api/book" && method === "PUT") {
      let body: unknown;
      try {
        body = await event.req.json();
      } catch {
        return json({ error: "body is not valid JSON" }, 400);
      }
      const envelope = body as { data?: unknown; revision?: unknown };
      if (!validBook(envelope?.data)) {
        return json({ error: "data is not a workspace book" }, 400);
      }
      const baseRevision = Number(envelope?.revision ?? 0);
      if (!Number.isSafeInteger(baseRevision) || baseRevision < 0) {
        return json({ error: "revision must be a non-negative integer" }, 400);
      }

      const payload = JSON.stringify(envelope.data);
      if (payload.length > MAX_BODY_BYTES) {
        return json({ error: "book too large" }, 413);
      }

      const client = await db.connect();
      try {
        await client.query("begin");

        // Revision 0 means "I have never seen a server copy". Insert, and let
        // the conflict tell us someone else got there first.
        if (baseRevision === 0) {
          const ins = await client.query<{ revision: string }>(
            `insert into book (user_id, data, revision) values ($1, $2::jsonb, 1)
               on conflict (user_id) do nothing
             returning revision`,
            [user, payload],
          );
          if (ins.rowCount === 0) {
            await client.query("rollback");
            const cur = await db.query<{ data: unknown; revision: string }>(
              "select data, revision from book where user_id = $1",
              [user],
            );
            return json(
              {
                error: "conflict",
                data: cur.rows[0].data,
                revision: Number(cur.rows[0].revision),
              },
              409,
            );
          }
          await client.query(
            "insert into book_history (user_id, data, revision) values ($1, $2::jsonb, 1)",
            [user, payload],
          );
          await client.query("commit");
          return json({ revision: 1 });
        }

        // Compare-and-swap. If the row moved on since the client last read it,
        // refuse and hand back what is actually stored — never overwrite.
        const upd = await client.query<{ revision: string }>(
          `update book set data = $2::jsonb, revision = revision + 1, updated_at = now()
            where user_id = $1 and revision = $3
           returning revision`,
          [user, payload, baseRevision],
        );
        if (upd.rowCount === 0) {
          await client.query("rollback");
          const cur = await db.query<{ data: unknown; revision: string }>(
            "select data, revision from book where user_id = $1",
            [user],
          );
          if (cur.rowCount === 0) return json({ error: "book disappeared" }, 409);
          return json(
            {
              error: "conflict",
              data: cur.rows[0].data,
              revision: Number(cur.rows[0].revision),
            },
            409,
          );
        }

        const newRevision = Number(upd.rows[0].revision);
        await client.query(
          "insert into book_history (user_id, data, revision) values ($1, $2::jsonb, $3)",
          [user, payload, newRevision],
        );
        await client.query(
          `delete from book_history
            where user_id = $1 and saved_at < now() - ($2 || ' days')::interval`,
          [user, String(HISTORY_RETENTION_DAYS)],
        );
        await client.query("commit");
        return json({ revision: newRevision });
      } catch (err) {
        await client.query("rollback").catch(() => {});
        throw err;
      } finally {
        client.release();
      }
    }

    return json({ error: "method not allowed" }, 405);
  } catch (err) {
    console.error("[book-api]", err);
    // Deliberately vague to the client, detailed in the journal: a save that
    // fails must look like a failure so the client keeps its local copy and
    // retries, rather than assuming the write landed.
    return json({ error: "storage unavailable" }, 500);
  }
}