/**
 * {{APP_NAME}} — server component for the UI + database template.
 *
 * Plain HTTP CRUD over Postgres. No realtime sync — the UI fetches on mount
 * and re-fetches after mutations. If you outgrow that pattern (multiple
 * clients, offline support, low-latency reads), graduate to the realtime
 * template.
 *
 * The endpoints mirror the two resources used by the example UI: categories
 * and the todos that belong to them. The shape is intentionally simple so
 * customers can use it as a starting point — copy a route, change the table
 * name, change the columns, ship.
 *
 * Notes on the SQL:
 *   - We use postgres.js's tagged-template form everywhere (sql`...`). Every
 *     interpolated value is bound as a parameter, not concatenated — this
 *     is the SQL-injection-safe pattern. Never use `sql.unsafe` here.
 *   - `RETURNING *` plus a CTE-join pattern lets the API return the same
 *     denormalised shape (todo + category name + category color) the UI
 *     reads on GET. The UI never has to reconcile two response shapes.
 *   - DELETEs of categories null out the foreign key on their todos rather
 *     than cascading. That's a UX choice; flip it if you prefer cascade.
 */

import { serve } from '@hono/node-server';
import { Hono } from 'hono';
import { cors } from 'hono/cors';
import postgres from 'postgres';

const databaseUrl = process.env.DATABASE_URL;
if (!databaseUrl) throw new Error('DATABASE_URL is required');

const sql = postgres(databaseUrl);
const app = new Hono();

// CORS is wide-open here because the UI is served from the same origin in
// production (the platform routes both surfaces under one host). Tighten
// this if you ever expose the API to third-party clients.
app.use('*', cors());

// Liveness probe. Stays cheap on purpose — no DB query — so a slow query
// doesn't get this endpoint marked unhealthy.
app.get('/health', (c) => c.json({ ok: true }));

// ── Categories ───────────────────────────────────────────────────────────

app.get('/categories', async (c) => {
  // LEFT JOIN + COUNT lets us return the sidebar count alongside the
  // category in one round-trip — the UI doesn't have to make N+1 calls.
  const rows = await sql`
    SELECT c.id, c.name, c.color, COUNT(t.id)::int AS todo_count
    FROM categories c
    LEFT JOIN todos t ON t.category_id = c.id
    GROUP BY c.id
    ORDER BY c.name
  `;
  return c.json(rows);
});

app.post('/categories', async (c) => {
  const body = await c.req.json<{ name?: string; color?: string }>();
  const name = body.name?.trim();
  if (!name) return c.json({ error: 'Name is required' }, 400);

  const [category] = await sql`
    INSERT INTO categories (name, color)
    VALUES (${name}, ${body.color || '#6366f1'})
    RETURNING id, name, color, 0::int AS todo_count
  `;
  return c.json(category, 201);
});

app.patch('/categories/:id', async (c) => {
  const id = Number(c.req.param('id'));
  if (!Number.isFinite(id)) return c.json({ error: 'Invalid category id' }, 400);

  const body = await c.req.json<{ name?: string; color?: string }>();
  const name = body.name?.trim();
  const color = body.color?.trim();
  if (name === undefined && color === undefined) {
    return c.json({ error: 'No fields to update' }, 400);
  }

  // COALESCE(${value ?? null}, name) means "use the value if the client
  // sent one, otherwise keep what's already there". This is the cleanest
  // way to support partial updates without writing one route per field.
  const [category] = await sql`
    UPDATE categories
    SET
      name = COALESCE(${name ?? null}, name),
      color = COALESCE(${color ?? null}, color)
    WHERE id = ${id}
    RETURNING id, name, color, (
      SELECT COUNT(*)::int FROM todos WHERE category_id = ${id}
    ) AS todo_count
  `;
  if (!category) return c.json({ error: 'Category not found' }, 404);
  return c.json(category);
});

app.delete('/categories/:id', async (c) => {
  const id = Number(c.req.param('id'));
  if (!Number.isFinite(id)) return c.json({ error: 'Invalid category id' }, 400);

  // Detach todos from the category instead of deleting them. Swap to
  // `DELETE FROM todos WHERE category_id = ${id}` if you prefer cascade.
  await sql`UPDATE todos SET category_id = NULL, updated_at = now() WHERE category_id = ${id}`;
  await sql`DELETE FROM categories WHERE id = ${id}`;
  return c.json({ ok: true });
});

// ── Todos ────────────────────────────────────────────────────────────────

app.get('/todos', async (c) => {
  const categoryId = c.req.query('category_id');
  // Same denormalised shape regardless of whether the UI filtered by
  // category. The UI doesn't need to know two response variants.
  const rows = categoryId
    ? await sql`
      SELECT t.*, c.name AS category_name, c.color AS category_color
      FROM todos t
      LEFT JOIN categories c ON c.id = t.category_id
      WHERE t.category_id = ${Number(categoryId)}
      ORDER BY t.completed ASC, t.created_at DESC
    `
    : await sql`
      SELECT t.*, c.name AS category_name, c.color AS category_color
      FROM todos t
      LEFT JOIN categories c ON c.id = t.category_id
      ORDER BY t.completed ASC, t.created_at DESC
    `;
  return c.json(rows);
});

app.post('/todos', async (c) => {
  const body = await c.req.json<{ title?: string; category_id?: number | null }>();
  const title = body.title?.trim();
  if (!title) return c.json({ error: 'Title is required' }, 400);

  // CTE + JOIN returns the inserted todo plus its category metadata in one
  // statement. Keeps the response shape identical to GET /todos.
  const [todo] = await sql`
    WITH inserted AS (
      INSERT INTO todos (title, category_id)
      VALUES (${title}, ${body.category_id ?? null})
      RETURNING *
    )
    SELECT i.*, c.name AS category_name, c.color AS category_color
    FROM inserted i
    LEFT JOIN categories c ON c.id = i.category_id
  `;
  return c.json(todo, 201);
});

app.patch('/todos/:id', async (c) => {
  const id = Number(c.req.param('id'));
  if (!Number.isFinite(id)) return c.json({ error: 'Invalid todo id' }, 400);

  const body = await c.req.json<{ title?: string; completed?: boolean; category_id?: number | null }>();
  const title = body.title?.trim();

  // category_id needs its own branch because `null` is a valid value
  // (clearing the category) but `undefined` means "don't touch". COALESCE
  // doesn't distinguish those, so we pick the query at the TS level.
  const [todo] = body.category_id === undefined
    ? await sql`
      WITH updated AS (
        UPDATE todos
        SET
          title = COALESCE(${title ?? null}, title),
          completed = COALESCE(${body.completed ?? null}, completed),
          updated_at = now()
        WHERE id = ${id}
        RETURNING *
      )
      SELECT u.*, c.name AS category_name, c.color AS category_color
      FROM updated u
      LEFT JOIN categories c ON c.id = u.category_id
    `
    : await sql`
      WITH updated AS (
        UPDATE todos
        SET
          title = COALESCE(${title ?? null}, title),
          completed = COALESCE(${body.completed ?? null}, completed),
          category_id = ${body.category_id},
          updated_at = now()
        WHERE id = ${id}
        RETURNING *
      )
      SELECT u.*, c.name AS category_name, c.color AS category_color
      FROM updated u
      LEFT JOIN categories c ON c.id = u.category_id
    `;
  if (!todo) return c.json({ error: 'Todo not found' }, 404);
  return c.json(todo);
});

app.delete('/todos/:id', async (c) => {
  const id = Number(c.req.param('id'));
  if (!Number.isFinite(id)) return c.json({ error: 'Invalid todo id' }, 400);

  await sql`DELETE FROM todos WHERE id = ${id}`;
  return c.json({ ok: true });
});

// PORT and HOST come from the platform. The fallback is for direct local
// invocations.
// ─────────────────────────────────────────────────────────────────────────
//  DO NOT CHANGE THE NEXT 4 LINES.  DO NOT ADD FALLBACKS.  DO NOT HARDCODE.
//  PORT and HOST are injected by `kazzle run`. Missing values must throw.
// ─────────────────────────────────────────────────────────────────────────
if (!process.env.PORT || !process.env.HOST) {
  throw new Error('PORT and HOST must be set by "kazzle run" — never hardcode them.');
}
const port = Number(process.env.PORT);
const hostname = process.env.HOST;
serve({ fetch: app.fetch, port, hostname });