import express from "express";
import crypto from "node:crypto";
import path from "node:path";
import { fileURLToPath } from "node:url";
import { db, all, one, run, tx, seed, audit, dateOffset } from "./db.js";
const root = path.resolve(path.dirname(fileURLToPath(import.meta.url)), "..");
export const app = express();
app.disable("x-powered-by");
if (process.env.TRUST_PROXY === "1") app.set("trust proxy", 1);
app.use(express.json({ limit: "1mb" }));
app.use((req, res, next) => {
  res.set({
    "X-Content-Type-Options": "nosniff",
    "Referrer-Policy": "same-origin",
    "X-Frame-Options": "DENY",
    "Content-Security-Policy":
      "default-src 'self'; script-src 'self'; style-src 'self' 'unsafe-inline'; img-src 'self' data:; connect-src 'self'; font-src 'self'; object-src 'none'; frame-ancestors 'none'; base-uri 'self'; form-action 'self'",
  });
  if (req.path.startsWith("/api")) res.set("Cache-Control", "no-store");
  if (
    !["GET", "HEAD", "OPTIONS"].includes(req.method) &&
    req.headers["x-stockroom-client"] !== "web"
  )
    return res
      .status(403)
      .json({ error: "Use the Stockroom application to submit changes." });
  const origin = req.headers.origin;
  if (origin && new URL(origin).host !== req.headers.host)
    return res.status(403).json({ error: "Cross-site request rejected." });
  next();
});
const fail = (message, status = 400) => {
  const e = new Error(message);
  e.status = status;
  throw e;
};
const str = (v, label, max = 180) => {
  if (typeof v !== "string" || !v.trim() || v.trim().length > max)
    fail(`${label} is required (maximum ${max} characters).`);
  return v.trim();
};
const int = (v, label, min = 0, max = 1000000) => {
  const n = Number(v);
  if (v === null || v === "" || !Number.isSafeInteger(n) || n < min || n > max)
    fail(`${label} must be a whole number between ${min} and ${max}.`);
  return n;
};
const date = (v, optional = false) => {
  if (optional && !v) return "";
  if (
    typeof v !== "string" ||
    !/^\d{4}-\d{2}-\d{2}$/.test(v) ||
    !Number.isFinite(Date.parse(v)) ||
    new Date(v).toISOString().slice(0, 10) !== v
  )
    fail("Enter a valid date.");
  return v;
};
const email = (v) => {
  v = str(v, "Email", 200).toLowerCase();
  if (!/^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(v))
    fail("Enter a valid email address.");
  return v;
};
const secret = (v) => {
  if (typeof v !== "string" || v.length < 12 || v.length > 128)
    fail("Use a password with 12–128 characters.");
  return v;
};
const hash = (v) => {
  const salt = crypto.randomBytes(16).toString("hex");
  return salt + ":" + crypto.scryptSync(v, salt, 64).toString("hex");
};
const verify = (v, h) => {
  if (typeof v !== "string" || v.length > 256 || !h) return false;
  const [salt, hex] = h.split(":");
  const expected = Buffer.from(hex || "", "hex");
  const actual = crypto.scryptSync(v, salt, 64);
  return (
    expected.length === actual.length &&
    crypto.timingSafeEqual(expected, actual)
  );
};
const digest = (v) => crypto.createHash("sha256").update(v).digest("hex");
const publicUser = (u) => ({
  id: u.id,
  tenant: u.tenant,
  name: u.name,
  email: u.email,
  role: u.role,
});
function login(res, u) {
  const token = crypto.randomBytes(32).toString("hex");
  run(
    "INSERT INTO sessions(token,user,expires) VALUES(?,?,?)",
    digest(token),
    u.id,
    Date.now() + 86400000,
  );
  res.cookie("stockroom", token, {
    httpOnly: true,
    sameSite: "strict",
    secure: process.env.COOKIE_SECURE === "1",
    maxAge: 86400000,
    path: "/",
  });
  return publicUser(u);
}
const attempts = new Map();
const workspaceAttempts = new Map();
function sandboxLimit(req, res, next) {
  const now = Date.now(),
    current = workspaceAttempts.get(req.ip);
  const bucket =
    current && current.until > now
      ? current
      : { count: 0, until: now + 3600000 };
  bucket.count++;
  workspaceAttempts.set(req.ip, bucket);
  if (bucket.count > 10)
    return res
      .status(429)
      .json({
        error:
          "You have created 10 test workspaces this hour. Sign in to an existing workspace or try again later.",
      });
  if (
    one("SELECT COUNT(*) n FROM tenants WHERE demo=1").n >=
    Number(process.env.MAX_DEMO_WORKSPACES || 200)
  )
    return res
      .status(503)
      .json({
        error:
          "The demo server is at capacity. Contact GHT for a test workspace.",
      });
  next();
}
function rate(req, res, next) {
  const k = req.ip;
  const now = Date.now();
  let r = attempts.get(k);
  if (!r || r.until < now) r = { n: 0, until: now + 900000 };
  r.n++;
  attempts.set(k, r);
  if (attempts.size > 10000)
    for (const [key, value] of attempts)
      if (value.until < now) attempts.delete(key);
  if (r.n > 60)
    return res
      .status(429)
      .json({ error: "Too many account attempts. Try again in 15 minutes." });
  next();
}
app.get("/api/health", (req, res) => {
  one("SELECT 1");
  res.json({ ok: true, version: "1.0.0-preview.2" });
});
app.get("/api/config", (req, res) =>
  res.json({
    sandbox: process.env.SANDBOX_ENABLED !== "0",
    version: "1.0.0-preview.2",
  }),
);
app.post("/api/auth/demo", rate, sandboxLimit, (req, res) => {
  if (process.env.SANDBOX_ENABLED === "0")
    fail("Demo creation is disabled.", 403);
  const result = tx(() => {
    const tenant = Number(
      run("INSERT INTO tenants(name,demo) VALUES(?,1)", "Oak & Field Market")
        .lastInsertRowid,
    );
    const id = Number(
      run(
        "INSERT INTO users(tenant,name,email,password,role) VALUES(?,?,?,?,?)",
        tenant,
        "Alex Morgan",
        `demo-${crypto.randomUUID()}@stockroom.example`,
        hash(crypto.randomBytes(24).toString("hex")),
        "admin",
      ).lastInsertRowid,
    );
    seed(tenant);
    return one("SELECT * FROM users WHERE id=?", id);
  });
  res.json({ user: login(res, result) });
});
app.post("/api/auth/register", rate, sandboxLimit, (req, res) => {
  if (process.env.SANDBOX_ENABLED === "0")
    fail("Ask your administrator for an account.", 403);
  const b = req.body;
  const e = email(b.email),
    pwd = hash(secret(b.password)),
    name = str(b.name, "Your name"),
    business = str(b.business, "Business name");
  const recovery = crypto.randomBytes(18).toString("hex");
  const u = tx(() => {
    if (one("SELECT id FROM users WHERE email=?", e))
      fail(
        "This email is already registered. Sign in or recover your account.",
        409,
      );
    const tenant = Number(
      run("INSERT INTO tenants(name,demo) VALUES(?,1)", business)
        .lastInsertRowid,
    );
    const id = Number(
      run(
        "INSERT INTO users(tenant,name,email,password,role,recovery) VALUES(?,?,?,?,?,?)",
        tenant,
        name,
        e,
        pwd,
        "admin",
        digest(recovery),
      ).lastInsertRowid,
    );
    seed(tenant);
    return one("SELECT * FROM users WHERE id=?", id);
  });
  res.json({ user: login(res, u), recovery });
});
app.post("/api/auth/login", rate, (req, res) => {
  const u = one(
    "SELECT * FROM users WHERE email=? AND active=1",
    email(req.body.email),
  );
  if (!u || !verify(req.body.password, u.password))
    fail("Email or password is incorrect.", 401);
  res.json({ user: login(res, u) });
});
app.post("/api/auth/recover", rate, (req, res) => {
  const pwd = secret(req.body.password),
    recovery = str(req.body.recovery, "Recovery code", 100);
  const u = one(
    "SELECT * FROM users WHERE email=? AND active=1",
    email(req.body.email),
  );
  if (!u || u.recovery !== digest(recovery))
    fail("Email or recovery code is incorrect.", 400);
  const next = crypto.randomBytes(18).toString("hex");
  tx(() => {
    run(
      "UPDATE users SET password=?,recovery=? WHERE id=?",
      hash(pwd),
      digest(next),
      u.id,
    );
    run("DELETE FROM sessions WHERE user=?", u.id);
    audit(u, "Account recovered", "Password reset; existing sessions revoked.");
  });
  res.json({ recovery: next });
});
app.use("/api", (req, res, next) => {
  const token = (req.headers.cookie || "")
    .split(";")
    .map((x) => x.trim())
    .find((x) => x.startsWith("stockroom="))
    ?.slice(10);
  const u =
    token &&
    one(
      "SELECT u.* FROM sessions s JOIN users u ON u.id=s.user WHERE s.token=? AND s.expires>? AND u.active=1",
      digest(token),
      Date.now(),
    );
  if (!u)
    return res
      .status(401)
      .json({ error: "Your session has ended. Sign in to continue." });
  req.user = u;
  next();
});
const role = (u, roles) => {
  if (!roles.includes(u.role))
    fail("Your role does not have permission for this action.", 403);
};
const get = (table, id, u) => {
  const r = one(
    `SELECT * FROM ${table} WHERE id=? AND tenant=?`,
    int(id, "Record", 1),
    u.tenant,
  );
  if (!r) fail("Record not found in this workspace.", 404);
  return r;
};
function mutation(req, fn) {
  const key = str(req.headers["idempotency-key"], "Request identifier", 100);
  const fingerprint = digest(req.path + JSON.stringify(req.body));
  return tx(() => {
    const old = one(
      "SELECT * FROM requests WHERE tenant=? AND key=?",
      req.user.tenant,
      key,
    );
    if (old) {
      if (old.fingerprint !== fingerprint)
        fail("Request identifier was reused for different data.", 409);
      return JSON.parse(old.response);
    }
    const result = fn();
    run(
      "INSERT INTO requests(tenant,key,fingerprint,response) VALUES(?,?,?,?)",
      req.user.tenant,
      key,
      fingerprint,
      JSON.stringify(result),
    );
    return result;
  });
}
function addStock(
  u,
  product,
  location,
  code,
  expiry,
  quantity,
  type,
  reference,
  note,
) {
  get("products", product, u);
  get("locations", location, u);
  let lot = one(
    "SELECT * FROM lots WHERE tenant=? AND product=? AND location=? AND code=? AND expiry=?",
    u.tenant,
    product,
    location,
    code,
    expiry,
  );
  if (lot)
    run(
      "UPDATE lots SET quantity=quantity+?,version=version+1 WHERE id=?",
      quantity,
      lot.id,
    );
  else {
    const id = Number(
      run(
        "INSERT INTO lots(tenant,product,location,code,expiry,quantity) VALUES(?,?,?,?,?,?)",
        u.tenant,
        product,
        location,
        code,
        expiry,
        quantity,
      ).lastInsertRowid,
    );
    lot = { id };
  }
  run(
    "INSERT INTO movements(tenant,product,location,lot,type,quantity,reference,note,actor) VALUES(?,?,?,?,?,?,?,?,?)",
    u.tenant,
    product,
    location,
    lot.id,
    type,
    quantity,
    reference,
    note,
    u.name,
  );
  return lot.id;
}
function takeStock(u, lot, qty, type, reference, note) {
  if (lot.quantity < qty)
    fail(
      `Insufficient stock: only ${lot.quantity} available in lot ${lot.code}.`,
      409,
    );
  run(
    "UPDATE lots SET quantity=quantity-?,version=version+1 WHERE id=?",
    qty,
    lot.id,
  );
  run(
    "INSERT INTO movements(tenant,product,location,lot,type,quantity,reference,note,actor) VALUES(?,?,?,?,?,?,?,?,?)",
    u.tenant,
    lot.product,
    lot.location,
    lot.id,
    type,
    -qty,
    reference,
    note,
    u.name,
  );
}
app.get("/api/me", (req, res) =>
  res.json({
    user: publicUser(req.user),
    workspace: one("SELECT * FROM tenants WHERE id=?", req.user.tenant),
  }),
);
app.post("/api/auth/logout", (req, res) => {
  const token = (req.headers.cookie || "")
    .split(";")
    .map((x) => x.trim())
    .find((x) => x.startsWith("stockroom="))
    ?.slice(10);
  if (token) run("DELETE FROM sessions WHERE token=?", digest(token));
  res.clearCookie("stockroom", { path: "/" });
  res.json({ ok: true });
});
app.post("/api/auth/password", (req, res) => {
  if (!verify(req.body.current, req.user.password))
    fail("Current password is incorrect.");
  const h = hash(secret(req.body.password));
  tx(() => {
    run("UPDATE users SET password=? WHERE id=?", h, req.user.id);
    run("DELETE FROM sessions WHERE user=?", req.user.id);
    audit(req.user, "Password changed", "All earlier sessions revoked.");
  });
  res.json({ user: login(res, req.user) });
});
app.post("/api/auth/recovery-code", (req, res) => {
  if (!verify(req.body.password, req.user.password))
    fail("Password is incorrect.");
  const code = crypto.randomBytes(18).toString("hex");
  run("UPDATE users SET recovery=? WHERE id=?", digest(code), req.user.id);
  res.json({ recovery: code });
});
app.get("/api/state", (req, res) => {
  const t = req.user.tenant;
  const products = all(
    `SELECT p.*,s.name supplier_name,COALESCE((SELECT SUM(quantity) FROM lots WHERE product=p.id AND tenant=p.tenant),0) stock,COALESCE((SELECT SUM(quantity) FROM sales WHERE product=p.id AND tenant=p.tenant AND status IN ('reconciled','historical') AND sold_at>=?),0)/28.0 daily FROM products p LEFT JOIN suppliers s ON s.id=p.supplier WHERE p.tenant=? ORDER BY p.name`,
    dateOffset(-28),
    t,
  );
  res.json({
    products,
    locations: all("SELECT * FROM locations WHERE tenant=?", t),
    suppliers: all("SELECT * FROM suppliers WHERE tenant=?", t),
    lots: all("SELECT * FROM lots WHERE tenant=? ORDER BY expiry", t),
    orders: all("SELECT * FROM orders WHERE tenant=? ORDER BY id DESC", t),
    orderItems: all("SELECT * FROM order_items WHERE tenant=?", t),
    counts: all("SELECT * FROM counts WHERE tenant=? ORDER BY id DESC", t),
    movements: all(
      "SELECT * FROM movements WHERE tenant=? ORDER BY id DESC LIMIT 500",
      t,
    ),
    sales: all(
      "SELECT * FROM sales WHERE tenant=? ORDER BY sold_at DESC LIMIT 2000",
      t,
    ),
    audit: all(
      "SELECT * FROM audit WHERE tenant=? ORDER BY id DESC LIMIT 200",
      t,
    ),
    users:
      req.user.role === "admin"
        ? all("SELECT id,name,email,role,active FROM users WHERE tenant=?", t)
        : [],
    serverTime: new Date().toISOString(),
  });
});
app.post("/api/products", (req, res) => {
  role(req.user, ["admin", "manager"]);
  const b = req.body;
  res.json(
    mutation(req, () => {
      const supplier = get("suppliers", b.supplier, req.user).id;
      const reorder = int(b.reorder, "Reorder point"),
        target = int(b.target, "Target stock");
      if (target < reorder) fail("Target must be at least the reorder point.");
      const id = Number(
        run(
          "INSERT INTO products(tenant,sku,barcode,name,category,unit,cost,price,reorder,target,supplier) VALUES(?,?,?,?,?,?,?,?,?,?,?)",
          req.user.tenant,
          str(b.sku, "SKU", 50),
          str(b.barcode, "Barcode", 80),
          str(b.name, "Product"),
          str(b.category, "Category", 60),
          str(b.unit, "Unit", 30),
          int(b.cost, "Cost in cents"),
          int(b.price, "Price in cents"),
          reorder,
          target,
          supplier,
        ).lastInsertRowid,
      );
      audit(req.user, "Product created", b.name);
      return { id };
    }),
  );
});
app.patch("/api/products/:id", (req, res) => {
  role(req.user, ["admin", "manager"]);
  res.json(
    mutation(req, () => {
      const p = get("products", req.params.id, req.user),
        b = req.body;
      const reorder = int(b.reorder, "Reorder point"),
        target = int(b.target, "Target stock");
      if (target < reorder) fail("Target must be at least the reorder point.");
      const supplier = get("suppliers", b.supplier, req.user);
      run(
        "UPDATE products SET name=?,category=?,unit=?,cost=?,price=?,reorder=?,target=?,supplier=? WHERE id=?",
        str(b.name, "Product"),
        str(b.category, "Category"),
        str(b.unit, "Unit"),
        int(b.cost, "Cost"),
        int(b.price, "Price"),
        reorder,
        target,
        supplier.id,
        p.id,
      );
      audit(req.user, "Product updated", p.sku);
      return { ok: true };
    }),
  );
});
app.post("/api/locations", (req, res) => {
  role(req.user, ["admin", "manager"]);
  res.json(
    mutation(req, () => ({
      id: Number(
        run(
          "INSERT INTO locations(tenant,name,site,type) VALUES(?,?,?,?)",
          req.user.tenant,
          str(req.body.name, "Location"),
          str(req.body.site, "Site"),
          str(req.body.type, "Type"),
        ).lastInsertRowid,
      ),
    })),
  );
});
app.post("/api/suppliers", (req, res) => {
  role(req.user, ["admin", "manager"]);
  res.json(
    mutation(req, () => ({
      id: Number(
        run(
          "INSERT INTO suppliers(tenant,name,email,lead_days) VALUES(?,?,?,?)",
          req.user.tenant,
          str(req.body.name, "Supplier"),
          email(req.body.email),
          int(req.body.lead_days, "Lead time", 0, 365),
        ).lastInsertRowid,
      ),
    })),
  );
});
app.post("/api/receive", (req, res) => {
  role(req.user, ["admin", "manager", "operator"]);
  res.json(
    mutation(req, () => {
      const b = req.body,
        p = get("products", b.product, req.user),
        l = get("locations", b.location, req.user),
        qty = int(b.quantity, "Quantity", 1),
        expiry = date(b.expiry, true),
        code = str(b.code, "Lot code", 80),
        reference = str(b.reference, "Delivery reference", 80);
      if (expiry && expiry < dateOffset(0))
        fail(
          "Expired stock cannot be received. Record a supplier exception instead.",
        );
      if (b.order_item) {
        const item = get("order_items", b.order_item, req.user),
          order = get("orders", item.order_id, req.user);
        if (
          item.product !== p.id ||
          !["ordered", "partial"].includes(order.status)
        )
          fail("This order line is not open for this product.");
        if (item.received + qty > item.quantity)
          fail("Receipt exceeds the remaining quantity on this order.", 409);
        run(
          "UPDATE order_items SET received=received+? WHERE id=?",
          qty,
          item.id,
        );
        const remaining = one(
          "SELECT SUM(quantity-received) n FROM order_items WHERE order_id=?",
          order.id,
        ).n;
        run(
          "UPDATE orders SET status=? WHERE id=?",
          remaining === 0 ? "received" : "partial",
          order.id,
        );
      }
      const lot = addStock(
        req.user,
        p.id,
        l.id,
        code,
        expiry,
        qty,
        "receipt",
        reference,
        String(b.note || "").slice(0, 500),
      );
      audit(
        req.user,
        "Stock received",
        `${qty} ${p.unit} · ${p.name} · ${l.name}`,
      );
      return { lot, quantity: qty };
    }),
  );
});
app.post("/api/transfer", (req, res) => {
  role(req.user, ["admin", "manager", "operator"]);
  res.json(
    mutation(req, () => {
      const b = req.body,
        lot = get("lots", b.lot, req.user),
        dest = get("locations", b.destination, req.user),
        qty = int(b.quantity, "Quantity", 1);
      if (lot.location === dest.id) fail("Choose a different destination.");
      const reference = `TR-${crypto.randomUUID().slice(0, 8)}`,
        note = str(b.note, "Transfer reason", 500);
      takeStock(req.user, lot, qty, "transfer-out", reference, note);
      addStock(
        req.user,
        lot.product,
        dest.id,
        lot.code,
        lot.expiry,
        qty,
        "transfer-in",
        reference,
        note,
      );
      audit(req.user, "Stock transferred", `${qty} units · ${reference}`);
      return { reference };
    }),
  );
});
app.post("/api/waste", (req, res) => {
  role(req.user, ["admin", "manager"]);
  res.json(
    mutation(req, () => {
      const lot = get("lots", req.body.lot, req.user),
        qty = int(req.body.quantity, "Quantity", 1),
        note = str(req.body.note, "Waste reason", 500);
      takeStock(req.user, lot, qty, "waste", "WASTE", note);
      audit(req.user, "Waste recorded", `${qty} units · ${note}`);
      return { ok: true };
    }),
  );
});
app.post("/api/counts", (req, res) => {
  role(req.user, ["admin", "manager", "operator"]);
  res.json(
    mutation(req, () => {
      const lot = get("lots", req.body.lot, req.user);
      if (int(req.body.version, "Stock version", 1) !== lot.version)
        fail(
          "Stock changed since this count was opened. Refresh and count again.",
          409,
        );
      if (
        one(
          "SELECT id FROM counts WHERE tenant=? AND lot=? AND status='pending'",
          req.user.tenant,
          lot.id,
        )
      )
        fail("This lot already has a count awaiting review.", 409);
      const id = Number(
        run(
          "INSERT INTO counts(tenant,lot,expected,counted,version,status,note,actor) VALUES(?,?,?,?,?,?,?,?)",
          req.user.tenant,
          lot.id,
          lot.quantity,
          int(req.body.counted, "Counted quantity"),
          lot.version,
          "pending",
          str(req.body.note, "Count note", 500),
          req.user.name,
        ).lastInsertRowid,
      );
      audit(req.user, "Count submitted", `Count ${id} awaits manager review.`);
      return { id };
    }),
  );
});
app.post("/api/counts/:id/review", (req, res) => {
  role(req.user, ["admin", "manager"]);
  res.json(
    mutation(req, () => {
      const count = get("counts", req.params.id, req.user),
        lot = get("lots", count.lot, req.user);
      if (count.status !== "pending")
        fail("This count has already been reviewed.", 409);
      if (!["approve", "reject"].includes(req.body.action))
        fail("Choose approve or reject.");
      const approving = req.body.action === "approve";
      if (approving && lot.version !== count.version)
        fail(
          "Stock moved after this count. Reject it and take a fresh count.",
          409,
        );
      if (approving) {
        const delta = count.counted - lot.quantity;
        run(
          "UPDATE lots SET quantity=?,version=version+1 WHERE id=?",
          count.counted,
          lot.id,
        );
        run(
          "INSERT INTO movements(tenant,product,location,lot,type,quantity,reference,note,actor) VALUES(?,?,?,?,?,?,?,?,?)",
          req.user.tenant,
          lot.product,
          lot.location,
          lot.id,
          "count",
          delta,
          `COUNT-${count.id}`,
          count.note,
          req.user.name,
        );
      }
      run(
        "UPDATE counts SET status=? WHERE id=?",
        approving ? "approved" : "rejected",
        count.id,
      );
      audit(
        req.user,
        "Count reviewed",
        `Count ${count.id} ${approving ? "approved" : "rejected"}`,
      );
      return { ok: true };
    }),
  );
});
app.post("/api/orders", (req, res) => {
  role(req.user, ["admin", "manager"]);
  res.json(
    mutation(req, () => {
      const b = req.body,
        supplier = get("suppliers", b.supplier, req.user);
      if (!Array.isArray(b.items) || !b.items.length || b.items.length > 100)
        fail("Add 1–100 order lines.");
      const seen = new Set();
      const items = b.items.map((i) => {
        const p = get("products", i.product, req.user);
        if (p.supplier !== supplier.id)
          fail(`${p.name} belongs to a different supplier.`);
        if (seen.has(p.id)) fail("Combine duplicate product lines.");
        seen.add(p.id);
        return {
          product: p.id,
          quantity: int(i.quantity, "Order quantity", 1),
          cost: p.cost,
        };
      });
      const id = Number(
        run(
          "INSERT INTO orders(tenant,supplier,status,expected,note) VALUES(?,?,?,?,?)",
          req.user.tenant,
          supplier.id,
          "draft",
          date(b.expected),
          String(b.note || "").slice(0, 500),
        ).lastInsertRowid,
      );
      for (const i of items)
        run(
          "INSERT INTO order_items(tenant,order_id,product,quantity,cost) VALUES(?,?,?,?,?)",
          req.user.tenant,
          id,
          i.product,
          i.quantity,
          i.cost,
        );
      audit(req.user, "Purchase order drafted", `PO-${id}`);
      return { id };
    }),
  );
});
app.post("/api/orders/:id/status", (req, res) => {
  role(req.user, ["admin", "manager"]);
  res.json(
    mutation(req, () => {
      const o = get("orders", req.params.id, req.user),
        s = req.body.status;
      if (!(
        (s === "ordered" && o.status === "draft") ||
        (s === "cancelled" && ["draft", "ordered"].includes(o.status))
      ))
        fail("This order cannot make that status change.", 409);
      run("UPDATE orders SET status=? WHERE id=?", s, o.id);
      audit(
        req.user,
        "Purchase order updated",
        `PO-${o.id}: ${s}. No supplier email sent.`,
      );
      return { ok: true };
    }),
  );
});
function reconcile(u, s) {
  const lots = all(
    "SELECT * FROM lots WHERE tenant=? AND product=? AND location=? AND quantity>0 AND (expiry='' OR expiry>=?) ORDER BY CASE WHEN expiry='' THEN 1 ELSE 0 END,expiry,id",
    u.tenant,
    s.product,
    s.location,
    dateOffset(0),
  );
  if (lots.reduce((sum, l) => sum + l.quantity, 0) < s.quantity)
    return "Insufficient unexpired stock at this location. Receive or transfer stock, then retry.";
  let left = s.quantity;
  for (const lot of lots) {
    if (!left) break;
    const qty = Math.min(left, lot.quantity);
    takeStock(u, lot, qty, "sale", s.external_id, "POS import");
    left -= qty;
  }
  return "";
}
app.post("/api/sales/import", (req, res) => {
  role(req.user, ["admin", "manager"]);
  res.json(
    mutation(req, () => {
      const rows = req.body.rows;
      if (!Array.isArray(rows) || !rows.length || rows.length > 500)
        fail("Import 1–500 sales rows.");
      let imported = 0,
        exceptions = 0,
        duplicates = 0;
      for (const row of rows) {
        const external = str(row.external_id, "Sale reference", 100),
          p = one(
            "SELECT * FROM products WHERE tenant=? AND sku=?",
            req.user.tenant,
            str(row.sku, "SKU", 50),
          );
        if (!p)
          fail(
            `Unknown SKU ${row.sku}. Correct the file and retry; no rows were imported.`,
          );
        const l = get("locations", row.location, req.user),
          quantity = int(row.quantity, "Sale quantity", 1),
          sold_at = date(row.date);
        if (sold_at > dateOffset(0))
          fail("Sale dates cannot be in the future.");
        const revenue = int(row.revenue, "Revenue in cents", 0, 100000000);
        const prior = one(
          "SELECT * FROM sales WHERE tenant=? AND external_id=?",
          req.user.tenant,
          external,
        );
        if (prior) {
          if (
            prior.product !== p.id ||
            prior.location !== l.id ||
            prior.quantity !== quantity ||
            prior.revenue !== revenue ||
            prior.sold_at !== sold_at
          )
            fail(`Sale ${external} already exists with different data.`, 409);
          duplicates++;
          continue;
        }
        const s = {
          product: p.id,
          location: l.id,
          quantity,
          external_id: external,
        };
        const error = reconcile(req.user, s);
        run(
          "INSERT INTO sales(tenant,external_id,product,location,quantity,revenue,sold_at,status,error) VALUES(?,?,?,?,?,?,?,?,?)",
          req.user.tenant,
          external,
          p.id,
          l.id,
          quantity,
          revenue,
          sold_at,
          error ? "exception" : "reconciled",
          error,
        );
        if (error) exceptions++;
        else imported++;
      }
      audit(
        req.user,
        "Sales imported",
        `${imported} reconciled, ${exceptions} exceptions, ${duplicates} duplicate rows skipped.`,
      );
      return { imported, exceptions, duplicates };
    }),
  );
});
app.post("/api/sales/:id/retry", (req, res) => {
  role(req.user, ["admin", "manager"]);
  res.json(
    mutation(req, () => {
      const s = get("sales", req.params.id, req.user);
      if (s.status !== "exception") fail("Only exceptions can be retried.");
      const error = reconcile(req.user, s);
      if (error) fail(error, 409);
      run("UPDATE sales SET status='reconciled',error='' WHERE id=?", s.id);
      audit(req.user, "Sale reconciled", s.external_id);
      return { ok: true };
    }),
  );
});
app.post("/api/users", (req, res) => {
  role(req.user, ["admin"]);
  res.json(
    tx(() => {
      const b = req.body;
      if (!["admin", "manager", "operator", "viewer"].includes(b.role))
        fail("Choose a valid role.");
      const e = email(b.email);
      if (one("SELECT id FROM users WHERE email=?", e))
        fail("Email is already registered.", 409);
      const recovery = crypto.randomBytes(18).toString("hex");
      const id = Number(
        run(
          "INSERT INTO users(tenant,name,email,password,role,recovery) VALUES(?,?,?,?,?,?)",
          req.user.tenant,
          str(b.name, "Name"),
          e,
          hash(secret(b.password)),
          b.role,
          digest(recovery),
        ).lastInsertRowid,
      );
      audit(req.user, "Team account created", `${e} · ${b.role}`);
      return { id, recovery };
    }),
  );
});
app.patch("/api/users/:id", (req, res) => {
  role(req.user, ["admin"]);
  res.json(
    mutation(req, () => {
      const u = get("users", req.params.id, req.user);
      if (u.id === req.user.id)
        fail("You cannot change your own role or deactivate yourself.");
      if (!["admin", "manager", "operator", "viewer"].includes(req.body.role))
        fail("Choose a valid role.");
      const active = req.body.active === true ? 1 : 0;
      run(
        "UPDATE users SET role=?,active=? WHERE id=?",
        req.body.role,
        active,
        u.id,
      );
      run("DELETE FROM sessions WHERE user=?", u.id);
      audit(
        req.user,
        "Team access updated",
        `${u.email} · ${req.body.role} · ${active ? "active" : "disabled"}`,
      );
      return { ok: true };
    }),
  );
});
app.patch("/api/workspace", (req, res) => {
  role(req.user, ["admin"]);
  res.json(
    mutation(req, () => {
      const name = str(req.body.name, "Workspace name");
      run("UPDATE tenants SET name=? WHERE id=?", name, req.user.tenant);
      audit(req.user, "Workspace renamed", name);
      return { ok: true };
    }),
  );
});
app.get("/api/export", (req, res) => {
  role(req.user, ["admin", "manager"]);
  const tables = [
    "products",
    "locations",
    "suppliers",
    "lots",
    "movements",
    "orders",
    "order_items",
    "counts",
    "sales",
    "audit",
  ];
  res.set(
    "Content-Disposition",
    'attachment; filename="stockroom-workspace-export.json"',
  );
  res.json({
    format: "stockroom-export-v1",
    exported: new Date().toISOString(),
    workspace: one("SELECT * FROM tenants WHERE id=?", req.user.tenant),
    ...Object.fromEntries(
      tables.map((t) => [
        t,
        all(`SELECT * FROM ${t} WHERE tenant=?`, req.user.tenant),
      ]),
    ),
  });
});
app.use("/api", (req, res) =>
  res.status(404).json({ error: "This action does not exist." }),
);
app.use(
  express.static(path.join(root, "dist"), {
    maxAge: "1h",
    setHeaders: (res, p) => {
      if (p.endsWith("index.html") || p.endsWith("sw.js"))
        res.set("Cache-Control", "no-cache");
    },
  }),
);
app.use('/downloads', (req, res) => res.status(404).type('text').send('This Stockroom download does not exist. Visit /downloads/ for current files.'));
app.get("/{*path}", (req, res) =>
  res.sendFile(path.join(root, "dist/index.html")),
);
app.use((err, req, res, next) => {
  const constraint = String(err.message).includes("UNIQUE constraint");
  const status = err.status || (constraint ? 409 : 500);
  if (status === 500) console.error(err);
  res.status(status).json({
    error: constraint
      ? "A record with that SKU, barcode, or email already exists."
      : status === 500
        ? "The request could not be saved. Try again; contact your administrator if this continues."
        : err.message,
  });
});
const port = Number(process.env.PORT || 4188);
if (process.env.NODE_ENV !== "test") {
  const listener = app.listen(port, process.env.HOST || "127.0.0.1", () =>
    console.log(
      `Stockroom ready on ${process.env.HOST || "127.0.0.1"}:${listener.address().port}`,
    ),
  );
}
