// Items API — Task 2 of docs/superpowers/plans/2026-08-03-inventory.md (spec:
// docs/superpowers/specs/2026-08-03-inventory-design.md). Same route-module
// pattern as customers.ts/vendors.ts/bills.ts: everything under /api/c/
// (company scope from X-Company-Id via companyContextPlugin), every query
// inside db.withCompany() so RLS is the real guard (CLAUDE.md §4/§12).
//
//   GET    /api/c/items?kind=&includeInactive=1&asOf=YYYY-MM-DD
//   GET    /api/c/items/:id
//   POST   /api/c/items
//   PATCH  /api/c/items/:id
//
// NOTHING IS CACHED. Quantity on hand, average cost and value come from
// db/migrations/0018_inventory.sql's inventory_valuation() / item_qty_on_hand()
// / item_average_cost() at query time, derived from inventory_movements — the
// same way every account balance derives from journal_lines (CLAUDE.md §3.4).
// There is deliberately no quantity column on `items` to read instead.
//
// ---------------------------------------------------------------------------
// DECISION: there is NO delete route, on purpose.
//
// 0018 refuses to delete an item that has stock history, in English. But an
// item with no movements can still be referenced by purchase_order_lines,
// bill_lines and invoice_lines, and those are plain composite foreign keys with
// no ON DELETE behavior and no English trigger — deleting such an item surfaces
// raw Postgres foreign-key text, which CLAUDE.md §9 does not permit. Rather
// than build a delete route that works for one narrow case and produces
// gibberish for another, items follow the same rule customers and vendors
// already follow: they are DEACTIVATED (active = false via PATCH), never
// deleted. "You never delete an item, you retire it" is one sentence a user can
// hold in their head; "you can delete an item unless it has been used, for
// several different meanings of used" is not.
// ---------------------------------------------------------------------------

import type { FastifyInstance } from "fastify";
import { z } from "zod";
import type { Db } from "./db.js";
import type { PoolClient } from "pg";
import { mapPgError } from "./errors.js";
import { blankToUndefined } from "./lib/textNormalize.js";

// ---------------------------------------------------------------------------
// validation
// ---------------------------------------------------------------------------

const idSchema = z.string().regex(/^\d{1,18}$/, "That isn't something we recognise — go back and pick it again.");
const idParamSchema = z.object({ id: idSchema });

const isoDateSchema = z.iso.date({ error: "must be a date like 2024-01-15 (YYYY-MM-DD)" });

export const ITEM_KINDS = ["inventory", "non_inventory", "service"] as const;
export type ItemKind = (typeof ITEM_KINDS)[number];

// The stored values stay inventory/non_inventory/service; the SENTENCE does
// not put those tokens in front of a user (CLAUDE.md §9).
const kindSchema = z.enum(ITEM_KINDS, {
  error: "Pick what kind of item this is: something you keep in stock, something you buy and sell without stocking, or a service.",
});

const nameSchema = z.string({ error: "An item needs a name." })
  .trim().min(1, "An item needs a name.").max(200, "That name is too long.");
const skuSchema = z.preprocess(blankToUndefined, z.string().max(100, "That SKU is too long.").optional());
const descriptionSchema = z.preprocess(blankToUndefined, z.string().max(1000).optional());

// items.sales_price / items.purchase_cost are numeric(19,4). Four decimal
// places are accepted (not two) because sub-cent costs are real — a washer
// costs $0.0042, which is the same reason 0018 gave inventory_movements.
// unit_cost six decimals. Deliberately a regex, never Number()/parseFloat()
// (CLAUDE.md §2). Zero is allowed; the column's CHECK is >= 0.
const priceSchema = z.preprocess(
  blankToUndefined,
  z.string()
    .regex(
      /^(0|[1-9]\d{0,12})(\.\d{1,4})?$/,
      "Enter an amount like 12 or 12.50 (up to 4 decimal places, no leading zeros, no more than 13 digits before the decimal point).",
    )
    .optional(),
);

const itemBodySchema = z.object({
  name: nameSchema,
  sku: skuSchema,
  kind: kindSchema,
  incomeAccountId: idSchema.optional(),
  cogsAccountId: idSchema.optional(),
  inventoryAccountId: idSchema.optional(),
  salesPrice: priceSchema,
  purchaseCost: priceSchema,
  description: descriptionSchema,
});

// PATCH replaces the whole editable set (mirrors vendors.ts's convention — the
// edit form always resubmits every field, so "omitted" and "cleared" are never
// ambiguous). `kind` and `inventoryAccountId` are accepted here, but 0018's
// items_frozen_once_moved trigger refuses to change either one once the item
// has stock history — surfaced in English by errors.ts.
const itemUpdateSchema = itemBodySchema.extend({ active: z.boolean().optional() });

const listQuerySchema = z.object({
  kind: kindSchema.optional(),
  includeInactive: z.coerce.boolean().optional(),
  asOf: isoDateSchema.optional(),
});

const detailQuerySchema = z.object({
  movementLimit: z.coerce.number().int().min(1).max(500).optional(),
});

function zodMessage(issues: Array<{ message: string }>): string {
  return issues.map((i) => i.message).join("; ");
}

const NOT_FOUND_ITEM = { error: "We couldn't find that item." };

// ---------------------------------------------------------------------------
// account rules (0018's items_accounts_typed + the two table CHECKs, restated
// in English here so a bad pick is a friendly 422 rather than a trigger-raised
// error — the trigger and the CHECKs remain the real guards, per CLAUDE.md §12)
// ---------------------------------------------------------------------------

interface AccountRow { id: string; name: string; type: string }

async function lookupAccount(
  client: PoolClient, companyId: string, accountId: string,
  allowInactive = false,
): Promise<AccountRow | null> {
  const res = await client.query<AccountRow>(
    `SELECT id, name, type FROM accounts
      WHERE company_id = $1 AND id = $2 AND (active = true OR $3::boolean)`,
    [companyId, accountId, allowInactive],
  );
  return res.rows[0] ?? null;
}

interface AccountChoice {
  incomeAccountId: string | null;
  cogsAccountId: string | null;
  inventoryAccountId: string | null;
}

/**
 * Validates the three account pointers against the item's kind, in the same
 * order and with the same rules 0018 enforces:
 *   - inventory_account_id is required for, and only for, kind='inventory'
 *     (items_inventory_account_iff_inventory) and must be an asset
 *   - a cost account is required unless kind='service'
 *     (items_cogs_account_required) and must be an expense
 *   - income_account_id is optional everywhere and must be an income account
 */
async function resolveItemAccounts(
  client: PoolClient, companyId: string, kind: ItemKind,
  body: { incomeAccountId?: string; cogsAccountId?: string; inventoryAccountId?: string },
  // What the item already points at, on a PATCH. A pointer the caller did not
  // change is not re-held to the "must still be active" rule — otherwise
  // renaming an item whose cost account was deactivated years ago fails with a
  // message about a field the user never touched. (The account must still
  // EXIST and be of the right TYPE either way; only the activeness rule is
  // about picking something new.)
  previous: Partial<AccountChoice> = {},
): Promise<AccountChoice | { status: number; error: string }> {
  if (kind === "inventory" && body.inventoryAccountId === undefined) {
    return {
      status: 422,
      error: "Pick the account that holds this item's value on the balance sheet (an asset account, usually Inventory).",
    };
  }
  if (kind !== "inventory" && body.inventoryAccountId !== undefined) {
    return {
      status: 422,
      error: "Only an item you stock has an inventory account — switch this item to inventory, or leave that account blank.",
    };
  }
  if (kind !== "service" && body.cogsAccountId === undefined) {
    return {
      status: 422,
      error: "Pick the account that records what this item costs you (an expense account, usually Cost of Goods Sold).",
    };
  }

  const checks: Array<{
    id: string | undefined; type: string; label: string; unchanged: boolean;
  }> = [
    { id: body.inventoryAccountId, type: "asset", label: "inventory account",
      unchanged: body.inventoryAccountId === (previous.inventoryAccountId ?? undefined) },
    { id: body.cogsAccountId, type: "expense", label: "cost account",
      unchanged: body.cogsAccountId === (previous.cogsAccountId ?? undefined) },
    { id: body.incomeAccountId, type: "income", label: "income account",
      unchanged: body.incomeAccountId === (previous.incomeAccountId ?? undefined) },
  ];
  const article: Record<string, string> = {
    asset: "an asset", expense: "an expense", income: "an income",
    liability: "a liability", equity: "an equity",
  };

  for (const check of checks) {
    if (check.id === undefined) continue;
    const account = await lookupAccount(client, companyId, check.id, check.unchanged);
    if (!account) {
      return { status: 422, error: `We couldn't find the account you picked for the ${check.label}.` };
    }
    if (account.type !== check.type) {
      return {
        status: 422,
        error: `The ${check.label} has to be ${article[check.type]} account — "${account.name}" is ${article[account.type] ?? `a ${account.type}`} account.`,
      };
    }
  }

  return {
    incomeAccountId: body.incomeAccountId ?? null,
    cogsAccountId: body.cogsAccountId ?? null,
    inventoryAccountId: body.inventoryAccountId ?? null,
  };
}

// ---------------------------------------------------------------------------
// row shapes
// ---------------------------------------------------------------------------

interface ItemRow {
  id: string;
  sku: string | null;
  name: string;
  kind: string;
  income_account_id: string | null;
  cogs_account_id: string | null;
  inventory_account_id: string | null;
  income_account_name: string | null;
  cogs_account_name: string | null;
  inventory_account_name: string | null;
  sales_price: string | null;
  purchase_cost: string | null;
  description: string | null;
  active: boolean;
  qty_on_hand: string | null;
  average_cost: string | null;
  value: string | null;
}

function toItemBody(r: ItemRow) {
  return {
    id: r.id,
    sku: r.sku,
    name: r.name,
    kind: r.kind,
    incomeAccountId: r.income_account_id,
    incomeAccountName: r.income_account_name,
    cogsAccountId: r.cogs_account_id,
    cogsAccountName: r.cogs_account_name,
    inventoryAccountId: r.inventory_account_id,
    inventoryAccountName: r.inventory_account_name,
    salesPrice: r.sales_price,
    purchaseCost: r.purchase_cost,
    description: r.description,
    active: r.active,
    // Only a stocked item has any of these; a service has no quantity at all,
    // and reporting 0 for it would read as "we're out of it".
    qtyOnHand: r.qty_on_hand,
    averageCost: r.average_cost,
    value: r.value,
  };
}

// Shared SELECT list + joins for both the list and the detail route.
// inventory_valuation() (0018) is the single definition of qty/average/value;
// it returns one row per kind='inventory' item, so non-stocked items simply
// LEFT JOIN to NULLs. Joined once for the whole result set — never one call
// per item.
const ITEM_SELECT = `
  SELECT i.id, i.sku, i.name, i.kind,
         i.income_account_id, i.cogs_account_id, i.inventory_account_id,
         ia.name AS income_account_name,
         ca.name AS cogs_account_name,
         va.name AS inventory_account_name,
         i.sales_price, i.purchase_cost, i.description, i.active,
         v.qty_on_hand, v.average_cost, v.value
    FROM items i
    LEFT JOIN accounts ia ON ia.company_id = i.company_id AND ia.id = i.income_account_id
    LEFT JOIN accounts ca ON ca.company_id = i.company_id AND ca.id = i.cogs_account_id
    LEFT JOIN accounts va ON va.company_id = i.company_id AND va.id = i.inventory_account_id
    LEFT JOIN inventory_valuation($1, $2::date) v ON v.item_id = i.id`;

// ---------------------------------------------------------------------------
// route registration
// ---------------------------------------------------------------------------

export function registerItemRoutes(fastify: FastifyInstance, db: Db): void {
  // -------------------------------------------------------------------------
  // GET /api/c/items
  // -------------------------------------------------------------------------
  fastify.get("/api/c/items", async (request, reply) => {
    const companyId = request.companyId!;
    const parsedQuery = listQuerySchema.safeParse(request.query);
    if (!parsedQuery.success) {
      return reply.code(400).send({ error: zodMessage(parsedQuery.error.issues) });
    }
    const { kind, includeInactive, asOf } = parsedQuery.data;

    try {
      return await db.withCompany(companyId, async (client) => {
        const res = await client.query<ItemRow>(
          `${ITEM_SELECT}
            WHERE i.company_id = $1
              AND ($3::text IS NULL OR i.kind = $3)
              AND ($4::boolean OR i.active)
            ORDER BY i.name`,
          [companyId, asOf ?? null, kind ?? null, includeInactive ?? false],
        );

        // "Has this company EVER held stock?" — a DIFFERENT question from
        // "does it have an active stocked item right now", and the one the
        // inventory REPORTS have to be gated on.
        //
        // An item with movement history can never be deleted, only
        // deactivated. Deactivate the last stocked item and the company is
        // still carrying its inventory value on the balance sheet — but an
        // active-only gate takes the Inventory valuation report away at
        // exactly the moment it is most needed, leaving that balance with
        // nothing on screen that explains it. So the nav entries and the
        // pickers stay active-only (a service business should not be shown
        // dead entries), and the reports ask this instead.
        //
        // Two ways to answer yes, because either alone has a hole. Movements
        // can exist for stock long since sold down to nothing, and an
        // inventory account can carry a balance with no movements behind it at
        // all — 0021's case (b), a company that imported full history before
        // its first item existed. check_inventory_ties() is reused rather than
        // re-deriving "which accounts are inventory accounts" here, so this
        // question and the tie always mean the same set of accounts.
        const stockRes = await client.query<{ ever_held_stock: boolean }>(
          `SELECT (EXISTS (SELECT 1 FROM inventory_movements m WHERE m.company_id = $1)
                   OR EXISTS (SELECT 1 FROM check_inventory_ties($1, NULL) t
                               WHERE t.ledger_value <> 0)) AS ever_held_stock`,
          [companyId],
        );

        return {
          items: res.rows.map(toItemBody),
          everHeldStock: stockRes.rows[0]!.ever_held_stock,
        };
      });
    } catch (err) {
      const mapped = mapPgError(err);
      if (mapped.status === 500) request.log.error({ err }, "items list failed");
      return reply.code(mapped.status).send({ error: mapped.message });
    }
  });

  // -------------------------------------------------------------------------
  // GET /api/c/items/:id  (detail + recent movements with a running quantity)
  // -------------------------------------------------------------------------
  fastify.get("/api/c/items/:id", async (request, reply) => {
    const companyId = request.companyId!;
    const parsedParams = idParamSchema.safeParse(request.params);
    if (!parsedParams.success) {
      return reply.code(400).send({ error: zodMessage(parsedParams.error.issues) });
    }
    const parsedQuery = detailQuerySchema.safeParse(request.query);
    if (!parsedQuery.success) {
      return reply.code(400).send({ error: zodMessage(parsedQuery.error.issues) });
    }
    const itemId = parsedParams.data.id;
    const movementLimit = parsedQuery.data.movementLimit ?? 100;

    try {
      const result = await db.withCompany(companyId, async (client) => {
        const headerRes = await client.query<ItemRow>(
          `${ITEM_SELECT} WHERE i.company_id = $1 AND i.id = $3`,
          [companyId, null, itemId],
        );
        const header = headerRes.rows[0];
        if (!header) return { found: false as const };

        // The running quantity is a window over the item's WHOLE history in
        // date order; the LIMIT is applied outside it, so showing the most
        // recent 100 movements never changes what the running total says.
        const movementsRes = await client.query<{
          id: string; moved_on: string; qty: string; unit_cost: string; value: string;
          reason: string; source_kind: string | null; source_id: string | null;
          entry_id: string; entry_no: string | null; entry_memo: string | null;
          running_qty: string;
        }>(
          `SELECT * FROM (
             SELECT m.id, m.moved_on::text AS moved_on, m.qty, m.unit_cost, m.value,
                    m.reason, m.source_kind, m.source_id, m.entry_id,
                    e.entry_no, e.memo AS entry_memo,
                    sum(m.qty) OVER (ORDER BY m.moved_on, m.id
                                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
                      ::numeric(19,4) AS running_qty
               FROM inventory_movements m
               JOIN journal_entries e ON e.company_id = m.company_id AND e.id = m.entry_id
              WHERE m.company_id = $1 AND m.item_id = $2
           ) t
           ORDER BY t.moved_on DESC, t.id DESC
           LIMIT $3`,
          [companyId, itemId, movementLimit],
        );

        return {
          found: true as const,
          item: toItemBody(header),
          movements: movementsRes.rows.map((m) => ({
            id: m.id,
            movedOn: m.moved_on,
            qty: m.qty,
            unitCost: m.unit_cost,
            value: m.value,
            reason: m.reason,
            sourceKind: m.source_kind,
            sourceId: m.source_id,
            entryId: m.entry_id,
            entryNo: m.entry_no,
            entryMemo: m.entry_memo,
            runningQty: m.running_qty,
          })),
        };
      });

      if (!result.found) {
        return reply.code(404).send(NOT_FOUND_ITEM);
      }
      return reply.send({ ...result.item, movements: result.movements });
    } catch (err) {
      const mapped = mapPgError(err);
      if (mapped.status === 500) request.log.error({ err }, "item detail failed");
      return reply.code(mapped.status).send({ error: mapped.message });
    }
  });

  // -------------------------------------------------------------------------
  // POST /api/c/items
  // -------------------------------------------------------------------------
  fastify.post("/api/c/items", async (request, reply) => {
    const companyId = request.companyId!;
    const parsed = itemBodySchema.safeParse(request.body);
    if (!parsed.success) {
      return reply.code(400).send({ error: zodMessage(parsed.error.issues) });
    }
    const body = parsed.data;

    try {
      // Never reply.send() inside db.withCompany()'s callback (see the
      // identical note in entries.ts) — a discriminated result is returned and
      // replied to only once the transaction has committed.
      const result = await db.withCompany(companyId, async (client) => {
        const accounts = await resolveItemAccounts(client, companyId, body.kind, body);
        if ("status" in accounts) return { ok: false as const, ...accounts };

        // Two statements, not one INSERT ... RETURNING wrapped in a CTE: a
        // data-modifying CTE's siblings read the snapshot from the START of
        // the statement, so a SELECT over `items` in the same statement would
        // not see the row just inserted.
        const insertRes = await client.query<{ id: string }>(
          `INSERT INTO items
             (company_id, sku, name, kind, income_account_id, cogs_account_id,
              inventory_account_id, sales_price, purchase_cost, description)
           VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
           RETURNING id`,
          [companyId, body.sku ?? null, body.name, body.kind,
            accounts.incomeAccountId, accounts.cogsAccountId, accounts.inventoryAccountId,
            body.salesPrice ?? null, body.purchaseCost ?? null, body.description ?? null],
        );
        const readRes = await client.query<ItemRow>(
          `${ITEM_SELECT} WHERE i.company_id = $1 AND i.id = $3`,
          [companyId, null, insertRes.rows[0]!.id],
        );
        return { ok: true as const, row: readRes.rows[0]! };
      });

      if (!result.ok) {
        return reply.code(result.status).send({ error: result.error });
      }
      return reply.code(201).send(toItemBody(result.row));
    } catch (err) {
      const mapped = mapPgError(err);
      if (mapped.status === 500) request.log.error({ err }, "create item failed");
      return reply.code(mapped.status).send({ error: mapped.message });
    }
  });

  // -------------------------------------------------------------------------
  // PATCH /api/c/items/:id
  // -------------------------------------------------------------------------
  fastify.patch("/api/c/items/:id", async (request, reply) => {
    const companyId = request.companyId!;
    const parsedParams = idParamSchema.safeParse(request.params);
    if (!parsedParams.success) {
      return reply.code(400).send({ error: zodMessage(parsedParams.error.issues) });
    }
    const parsed = itemUpdateSchema.safeParse(request.body);
    if (!parsed.success) {
      return reply.code(400).send({ error: zodMessage(parsed.error.issues) });
    }
    const itemId = parsedParams.data.id;
    const body = parsed.data;

    try {
      const result = await db.withCompany(companyId, async (client) => {
        // RLS makes another company's item id look exactly like an unknown one
        // (zero rows either way), so look it up first for a clean 404.
        const existingRes = await client.query<{
          id: string; income_account_id: string | null;
          cogs_account_id: string | null; inventory_account_id: string | null;
        }>(
          `SELECT id, income_account_id, cogs_account_id, inventory_account_id
             FROM items WHERE company_id = $1 AND id = $2`,
          [companyId, itemId],
        );
        const existing = existingRes.rows[0];
        if (!existing) return { found: false as const };

        const accounts = await resolveItemAccounts(client, companyId, body.kind, body, {
          incomeAccountId: existing.income_account_id,
          cogsAccountId: existing.cogs_account_id,
          inventoryAccountId: existing.inventory_account_id,
        });
        if ("status" in accounts) return { found: true as const, ok: false as const, ...accounts };

        // Deliberately NOT pre-checked: changing kind or the inventory account
        // once the item has moved raises from items_frozen_once_moved (0018)
        // and is mapped to English by errors.ts. The database is the guard.
        await client.query(
          `UPDATE items
              SET sku = $2, name = $3, kind = $4, income_account_id = $5,
                  cogs_account_id = $6, inventory_account_id = $7,
                  sales_price = $8, purchase_cost = $9, description = $10,
                  active = coalesce($11, active)
            WHERE company_id = $1 AND id = $12`,
          [companyId, body.sku ?? null, body.name, body.kind,
            accounts.incomeAccountId, accounts.cogsAccountId, accounts.inventoryAccountId,
            body.salesPrice ?? null, body.purchaseCost ?? null, body.description ?? null,
            body.active ?? null, itemId],
        );
        // Re-read (a separate statement — see the note in POST above).
        const readRes = await client.query<ItemRow>(
          `${ITEM_SELECT} WHERE i.company_id = $1 AND i.id = $3`,
          [companyId, null, itemId],
        );
        return { found: true as const, ok: true as const, row: readRes.rows[0]! };
      });

      if (!result.found) {
        return reply.code(404).send(NOT_FOUND_ITEM);
      }
      if (!result.ok) {
        return reply.code(result.status).send({ error: result.error });
      }
      return reply.send(toItemBody(result.row));
    } catch (err) {
      const mapped = mapPgError(err);
      if (mapped.status === 500) request.log.error({ err }, "update item failed");
      return reply.code(mapped.status).send({ error: mapped.message });
    }
  });
}
