Pantry

model.js 234 lines

import ddl from "./schema.js";
import manifest from "./manifest.json";
import { database } from "../../shared/database.js";
import { localDate } from "../../shared/data.js";
import { addDays } from "../../shared/format.js";
export function createPantry(L) {
  const db = database(L, ddl, manifest);
  const product = (data) => {
    db.check("add-product", data);
    if (!data.name.trim()) throw Error("Enter a product name.");
    return data;
  };
  const units = async (data) => {
    for (const key of ["qu_id_purchase", "qu_id_stock"])
      if (data[key] != null && !(await db.get("quantity_units", data[key])))
        throw Error("Choose an existing quantity unit.");
  };
  const addProduct = async (data) => {
    product(data);
    await units(data);
    const row = await db.insert("products", data);
    await db.emit("product-added", row);
    return row;
  };
  const prepareStock = async (data) => {
    db.check("add-stock", data);
    const p = await db.get("products", data.product_id);
    if (!p) throw Error("Choose an existing product.");
    const value = { ...data };
    if (value.purchased_date === undefined) value.purchased_date = localDate();
    if (value.location_id === undefined) value.location_id = p.location_id;
    if (value.best_before_date === undefined && p.default_best_before_days > 0)
      value.best_before_date = addDays(
        value.purchased_date || localDate(),
        p.default_best_before_days,
      );
    if (value.open === 1 && !value.opened_date) value.opened_date = localDate();
    if (value.open === 1 && p.default_best_before_days_after_open > 0) {
      const deadline = addDays(
        value.opened_date,
        p.default_best_before_days_after_open,
      );
      if (!value.best_before_date || deadline < value.best_before_date)
        value.best_before_date = deadline;
    }
    return value;
  };
  const addStock = async (data) => {
    const row = await db.insert("stock_entries", await prepareStock(data));
    await db.emit("stock-added", row);
    return row;
  };
  return {
    ...db,
    list: () =>
      db.ready.then(async () => ({
        products: await db.query(
          "SELECT * FROM products ORDER BY name COLLATE NOCASE",
        ),
        lots: await db.query(
          "SELECT * FROM stock_entries ORDER BY best_before_date IS NULL, best_before_date, id",
        ),
        units: await db.query(
          "SELECT * FROM quantity_units ORDER BY name COLLATE NOCASE",
        ),
      })),
    addProduct: (data) => db.run(() => addProduct(data)),
    addStock: (data) => db.run(() => addStock(data)),
    editProduct: (id, data) =>
      db.run(async () => {
        product(data);
        await units(data);
        return db.update("products", id, data);
      }),
    editStock: (id, data) =>
      db.run(async () => {
        const old = await db.get("stock_entries", id);
        if (!old) throw Error("Stock lot no longer exists.");
        if (data.stock_id !== undefined && data.stock_id !== old.stock_id)
          throw Error("A stock lot keeps its stable stock ID.");
        if (data.product_id !== old.product_id)
          throw Error("A stock lot keeps its product.");
        const value = await prepareStock(data);
        const row = await db.update("stock_entries", id, value);
        if (!old.open && row.open) await db.emit("stock-opened", row);
        return row;
      }),
    unit: (data, id) =>
      db.run(async () => {
        if (!data.name?.trim()) throw Error("Enter a unit name.");
        return id === undefined
          ? db.insert("quantity_units", data)
          : db.update("quantity_units", id, data);
      }),
    removeUnit: (id) =>
      db.run(async () => {
        if (
          (
            await db.query(
              "SELECT id FROM products WHERE qu_id_stock = ? OR qu_id_purchase = ?",
              [id, id],
            )
          ).length
        )
          throw Error(
            "This unit is used by a product. Change the product units first.",
          );
        await db.exec("DELETE FROM quantity_units WHERE id = ?", [id]);
      }),
    removeProduct: (id) =>
      db.run(async () => {
        if (
          (
            await db.query(
              "SELECT id FROM stock_entries WHERE product_id = ?",
              [id],
            )
          ).length
        )
          throw Error("Remove this product’s stock lots first.");
        await db.exec("DELETE FROM products WHERE id = ?", [id]);
      }),
    removeStock: (id) =>
      db.run(() => db.exec("DELETE FROM stock_entries WHERE id = ?", [id])),
    consume: (id, amount, note) =>
      db.run(async () => {
        const row = await db.get("stock_entries", id);
        if (!row) throw Error("Stock lot no longer exists.");
        if (!Number.isFinite(amount) || amount <= 0 || amount > row.amount)
          throw Error(
            "Enter a positive amount no greater than the lot balance.",
          );
        const remainder = row.amount - amount;
        if (amount === row.amount)
          await db.exec("DELETE FROM stock_entries WHERE id = ?", [id]);
        else await db.update("stock_entries", id, { amount: remainder });
        await db.emit("stock-consumed", {
          product_id: row.product_id,
          stock_id: row.stock_id,
          amount,
          note: note ?? row.note,
        });
      }),
    open: (id) =>
      db.run(async () => {
        const row = await db.get("stock_entries", id);
        if (!row) throw Error("Stock lot no longer exists.");
        if (row.open) return;
        const p = await db.get("products", row.product_id),
          opened_date = localDate();
        let best_before_date = row.best_before_date;
        if (p.default_best_before_days_after_open > 0) {
          const deadline = addDays(
            opened_date,
            p.default_best_before_days_after_open,
          );
          if (!best_before_date || deadline < best_before_date)
            best_before_date = deadline;
        }
        const updated = await db.update("stock_entries", id, {
          open: 1,
          opened_date,
          best_before_date,
        });
        await db.emit("stock-opened", updated);
      }),
    sample: () =>
      db.run(async () => {
        const kg = await db.insert("quantity_units", {
            name: "kg",
            name_plural: "kg",
            description: "Kilograms",
          }),
          bottle = await db.insert("quantity_units", {
            name: "bottle",
            name_plural: "bottles",
          }),
          bag = await db.insert("quantity_units", {
            name: "bag",
            name_plural: "bags",
          });
        const oats = await addProduct({
            name: "Rolled oats",
            description: "Breakfast cupboard",
            qu_id_purchase: bag.id,
            qu_id_stock: kg.id,
            min_stock_amount: 0.5,
            default_best_before_days: 180,
          }),
          milk = await addProduct({
            name: "Oat milk",
            description: "Unsweetened · fridge after opening",
            qu_id_purchase: bottle.id,
            qu_id_stock: bottle.id,
            min_stock_amount: 2,
            default_best_before_days: 90,
            default_best_before_days_after_open: 5,
          }),
          lentils = await addProduct({
            name: "Red lentils",
            description: "Dry goods",
            qu_id_purchase: bag.id,
            qu_id_stock: kg.id,
            min_stock_amount: 1,
            default_best_before_days: 365,
          });
        await addStock({
          product_id: oats.id,
          amount: 0.75,
          best_before_date: addDays(localDate(), 45),
          note: "Top shelf",
        });
        await addStock({
          product_id: milk.id,
          amount: 1,
          best_before_date: addDays(localDate(), 2),
          open: 1,
          opened_date: localDate(),
          note: "Opened for breakfast",
        });
        await addStock({
          product_id: milk.id,
          amount: 1,
          best_before_date: addDays(localDate(), 70),
          note: "Unopened reserve",
        });
        await addStock({
          product_id: lentils.id,
          amount: 0.35,
          best_before_date: addDays(localDate(), 110),
        });
      }),
  };
}