import { DatabaseSync } from "node:sqlite";
import { mkdirSync } from "node:fs";
import { dirname } from "node:path";
export const file = process.env.DB_PATH || "./data/stockroom.sqlite";
if (file !== ":memory:") mkdirSync(dirname(file), { recursive: true });
export const db = new DatabaseSync(file);
db.exec(`PRAGMA journal_mode=WAL; PRAGMA foreign_keys=ON; PRAGMA busy_timeout=5000;
CREATE TABLE IF NOT EXISTS tenants(id INTEGER PRIMARY KEY, name TEXT NOT NULL, demo INTEGER NOT NULL DEFAULT 0, created TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS users(id INTEGER PRIMARY KEY, tenant INTEGER REFERENCES tenants(id), name TEXT NOT NULL, email TEXT UNIQUE NOT NULL, password TEXT NOT NULL, role TEXT NOT NULL CHECK(role IN ('admin','manager','operator','viewer')), recovery TEXT, active INTEGER DEFAULT 1);
CREATE TABLE IF NOT EXISTS sessions(token TEXT PRIMARY KEY, user INTEGER REFERENCES users(id), expires INTEGER NOT NULL);
CREATE TABLE IF NOT EXISTS suppliers(id INTEGER PRIMARY KEY, tenant INTEGER REFERENCES tenants(id), name TEXT NOT NULL, email TEXT NOT NULL, lead_days INTEGER NOT NULL CHECK(lead_days>=0));
CREATE TABLE IF NOT EXISTS locations(id INTEGER PRIMARY KEY, tenant INTEGER REFERENCES tenants(id), name TEXT NOT NULL, site TEXT NOT NULL, type TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS products(id INTEGER PRIMARY KEY, tenant INTEGER REFERENCES tenants(id), sku TEXT NOT NULL, barcode TEXT NOT NULL, name TEXT NOT NULL, category TEXT NOT NULL, unit TEXT NOT NULL, cost INTEGER NOT NULL CHECK(cost>=0), price INTEGER NOT NULL CHECK(price>=0), reorder INTEGER NOT NULL CHECK(reorder>=0), target INTEGER NOT NULL CHECK(target>=reorder), supplier INTEGER REFERENCES suppliers(id), active INTEGER NOT NULL DEFAULT 1, UNIQUE(tenant,sku), UNIQUE(tenant,barcode));
CREATE TABLE IF NOT EXISTS lots(id INTEGER PRIMARY KEY, tenant INTEGER REFERENCES tenants(id), product INTEGER REFERENCES products(id), location INTEGER REFERENCES locations(id), code TEXT NOT NULL, expiry TEXT NOT NULL DEFAULT '', quantity INTEGER NOT NULL CHECK(quantity>=0), version INTEGER NOT NULL DEFAULT 1, UNIQUE(tenant,product,location,code,expiry));
CREATE TABLE IF NOT EXISTS movements(id INTEGER PRIMARY KEY, tenant INTEGER REFERENCES tenants(id), product INTEGER REFERENCES products(id), location INTEGER REFERENCES locations(id), lot INTEGER REFERENCES lots(id), type TEXT NOT NULL, quantity INTEGER NOT NULL, reference TEXT NOT NULL, note TEXT NOT NULL, actor TEXT NOT NULL, created TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS orders(id INTEGER PRIMARY KEY, tenant INTEGER REFERENCES tenants(id), supplier INTEGER REFERENCES suppliers(id), status TEXT NOT NULL CHECK(status IN ('draft','ordered','partial','received','cancelled')), expected TEXT NOT NULL, note TEXT NOT NULL, created TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS order_items(id INTEGER PRIMARY KEY, tenant INTEGER REFERENCES tenants(id), order_id INTEGER REFERENCES orders(id), product INTEGER REFERENCES products(id), quantity INTEGER NOT NULL CHECK(quantity>0), received INTEGER NOT NULL DEFAULT 0 CHECK(received>=0 AND received<=quantity), cost INTEGER NOT NULL);
CREATE TABLE IF NOT EXISTS counts(id INTEGER PRIMARY KEY, tenant INTEGER REFERENCES tenants(id), lot INTEGER REFERENCES lots(id), expected INTEGER NOT NULL, counted INTEGER NOT NULL CHECK(counted>=0), version INTEGER NOT NULL, status TEXT NOT NULL CHECK(status IN ('pending','approved','rejected')), note TEXT NOT NULL, actor TEXT NOT NULL, created TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS sales(id INTEGER PRIMARY KEY, tenant INTEGER REFERENCES tenants(id), external_id TEXT NOT NULL, product INTEGER REFERENCES products(id), location INTEGER REFERENCES locations(id), quantity INTEGER NOT NULL, revenue INTEGER NOT NULL, sold_at TEXT NOT NULL, status TEXT NOT NULL, error TEXT NOT NULL DEFAULT '', UNIQUE(tenant,external_id));
CREATE TABLE IF NOT EXISTS audit(id INTEGER PRIMARY KEY, tenant INTEGER REFERENCES tenants(id), actor TEXT NOT NULL, action TEXT NOT NULL, detail TEXT NOT NULL, created TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS requests(tenant INTEGER REFERENCES tenants(id), key TEXT NOT NULL, fingerprint TEXT NOT NULL, response TEXT NOT NULL, PRIMARY KEY(tenant,key));
CREATE INDEX IF NOT EXISTS lots_tenant ON lots(tenant,product,location);
CREATE INDEX IF NOT EXISTS movements_tenant ON movements(tenant,created);
CREATE INDEX IF NOT EXISTS sales_tenant ON sales(tenant,sold_at);
PRAGMA user_version=1;`);
export const all = (sql, ...args) => db.prepare(sql).all(...args);
export const one = (sql, ...args) => db.prepare(sql).get(...args);
export const run = (sql, ...args) => db.prepare(sql).run(...args);
export function tx(fn) {
  db.exec("BEGIN IMMEDIATE");
  try {
    const r = fn();
    db.exec("COMMIT");
    return r;
  } catch (e) {
    db.exec("ROLLBACK");
    throw e;
  }
}
export const dateOffset = (n) =>
  new Date(Date.now() + n * 86400000).toISOString().slice(0, 10);
export function audit(u, action, detail) {
  run(
    "INSERT INTO audit(tenant,actor,action,detail) VALUES(?,?,?,?)",
    u.tenant,
    u.name,
    action,
    detail,
  );
}
export function seed(tenant) {
  const supplierIds = [
    ["Valley Fresh Produce", "orders@valleyfresh.example", 2],
    ["Southern Dairy Co.", "orders@southerndairy.example", 3],
    ["Harvest Pantry Wholesale", "orders@harvestpantry.example", 5],
    ["Everyday Essentials", "orders@everyday.example", 4],
  ].map((x) =>
    Number(
      run(
        "INSERT INTO suppliers(tenant,name,email,lead_days) VALUES(?,?,?,?)",
        tenant,
        ...x,
      ).lastInsertRowid,
    ),
  );
  const locIds = [
    ["Produce floor", "Market · Downtown", "Sales floor"],
    ["Dry storage", "Market · Downtown", "Backroom"],
    ["Walk-in cooler", "Market · Downtown", "Cold storage"],
    ["Main stockroom", "Market · Westside", "Backroom"],
    ["Grocery aisles", "Market · Westside", "Sales floor"],
  ].map((x) =>
    Number(
      run(
        "INSERT INTO locations(tenant,name,site,type) VALUES(?,?,?,?)",
        tenant,
        ...x,
      ).lastInsertRowid,
    ),
  );
  const items = [
    ["Honeycrisp apples", "Produce", "case", 2850, 4400, 12, 36, 0],
    ["Hass avocados", "Produce", "case", 3400, 5600, 10, 30, 0],
    ["Organic bananas", "Produce", "case", 1900, 3200, 10, 35, 0],
    ["Baby spinach", "Produce", "case", 2200, 3800, 8, 24, 0],
    ["Roma tomatoes", "Produce", "case", 2100, 3600, 8, 26, 0],
    ["Blueberries · 12 pack", "Produce", "case", 2700, 4600, 8, 24, 0],
    ["Whole milk · 1 gallon", "Dairy", "unit", 310, 499, 30, 90, 1],
    ["Oat milk · original", "Dairy", "case", 2600, 4200, 14, 40, 1],
    ["Greek yogurt · plain", "Dairy", "case", 1850, 3200, 12, 36, 1],
    ["Salted butter", "Dairy", "case", 3600, 5200, 6, 20, 1],
    ["Free range eggs", "Dairy", "case", 4400, 6400, 8, 28, 1],
    ["Sharp cheddar", "Dairy", "case", 2800, 4500, 6, 22, 1],
    ["Maple granola", "Pantry", "case", 3200, 5100, 10, 32, 2],
    ["Penne pasta", "Pantry", "case", 1450, 2600, 12, 40, 2],
    ["Jasmine rice · 5 lb", "Pantry", "unit", 510, 899, 20, 60, 2],
    ["Extra virgin olive oil", "Pantry", "case", 6900, 9900, 6, 20, 2],
    ["Black beans", "Pantry", "case", 1850, 3000, 10, 36, 2],
    ["Sourdough crackers", "Pantry", "case", 2400, 3800, 8, 28, 2],
    ["Sparkling water · lime", "Beverages", "case", 950, 1599, 18, 60, 2],
    ["Cold brew coffee", "Beverages", "case", 2800, 4400, 12, 40, 2],
    ["Orange juice", "Beverages", "case", 2100, 3499, 10, 30, 1],
    ["Green tea", "Beverages", "case", 1800, 3000, 8, 26, 2],
    ["Paper towels · 6 roll", "Household", "unit", 580, 999, 15, 48, 3],
    ["Dish soap", "Household", "case", 2400, 4000, 8, 28, 3],
    ["Compostable bags", "Household", "case", 3800, 6200, 6, 22, 3],
    ["Laundry detergent", "Household", "unit", 890, 1499, 10, 35, 3],
    ["Frozen blueberries", "Frozen", "case", 3600, 5600, 8, 24, 0],
    ["Frozen peas", "Frozen", "case", 2100, 3600, 10, 30, 0],
    ["Artisan bread", "Bakery", "case", 2600, 4300, 8, 24, 2],
    ["Croissants · 6 pack", "Bakery", "case", 3200, 4800, 6, 20, 2],
    ["Local honey", "Pantry", "case", 4500, 7200, 8, 26, 2],
    ["Sea salt chips", "Pantry", "case", 1900, 3200, 12, 38, 2],
    ["Almond flour", "Pantry", "case", 4200, 6600, 6, 20, 2],
    ["Peanut butter", "Pantry", "case", 2800, 4600, 8, 26, 2],
    ["Coconut water", "Beverages", "case", 1800, 3000, 10, 30, 2],
    ["Mineral water", "Beverages", "case", 1200, 2200, 12, 40, 2],
  ];
  const pids = items.map((p, i) => {
    const [name, cat, unit, cost, price, reorder, target, sup] = p;
    const id = Number(
      run(
        "INSERT INTO products(tenant,sku,barcode,name,category,unit,cost,price,reorder,target,supplier) VALUES(?,?,?,?,?,?,?,?,?,?,?)",
        tenant,
        `SR-${String(1001 + i)}`,
        `20000000${String(1001 + i)}`,
        name,
        cat,
        unit,
        cost,
        price,
        reorder,
        target,
        supplierIds[sup],
      ).lastInsertRowid,
    );
    const qty =
      i % 7 === 0 ? Math.max(2, reorder - 5) : target + 8 + ((i * 13) % 29);
    for (let j = 0; j < 2; j++) {
      const loc = locIds[j === 1 ? 3 : sup === 0 ? 0 : sup === 1 ? 2 : 1];
      const expiry = dateOffset(
        cat === "Produce" || cat === "Dairy" || cat === "Bakery"
          ? i % 8 === 0
            ? 2
            : 9 + (i % 13)
          : 90 + i,
      );
      const q = j === 0 ? Math.ceil(qty * 0.7) : Math.floor(qty * 0.3);
      const lot = Number(
        run(
          "INSERT INTO lots(tenant,product,location,code,expiry,quantity) VALUES(?,?,?,?,?,?)",
          tenant,
          id,
          loc,
          `LOT-${1001 + i}-${j + 1}`,
          expiry,
          q,
        ).lastInsertRowid,
      );
      run(
        "INSERT INTO movements(tenant,product,location,lot,type,quantity,reference,note,actor,created) VALUES(?,?,?,?,?,?,?,?,?,?)",
        tenant,
        id,
        loc,
        lot,
        "opening",
        q,
        "DEMO-OPEN",
        "Opening balance · fictional demonstration",
        "Demo setup",
        `${dateOffset(-1)} 08:00:00`,
      );
    }
    for (let d = 1; d <= 28; d++) {
      const q = 1 + ((i + d * 7) % 5) + (d % 7 === 0 ? 2 : 0);
      run(
        "INSERT INTO sales(tenant,external_id,product,location,quantity,revenue,sold_at,status) VALUES(?,?,?,?,?,?,?,?)",
        tenant,
        `HISTORY-${i}-${d}`,
        id,
        locIds[0],
        q,
        price * q,
        `${dateOffset(-d)}T12:00:00.000Z`,
        "historical",
      );
    }
    return id;
  });
  for (let s = 0; s < 3; s++) {
    const order = Number(
      run(
        "INSERT INTO orders(tenant,supplier,status,expected,note) VALUES(?,?,?,?,?)",
        tenant,
        supplierIds[s],
        "ordered",
        dateOffset(s + 1),
        "Sample replenishment order",
      ).lastInsertRowid,
    );
    items.forEach((p, i) => {
      if (p[7] === s && i % 2 === 0)
        run(
          "INSERT INTO order_items(tenant,order_id,product,quantity,cost) VALUES(?,?,?,?,?)",
          tenant,
          order,
          pids[i],
          p[6],
          p[3],
        );
    });
  }
  const lot = one(
    "SELECT * FROM lots WHERE tenant=? ORDER BY id LIMIT 1",
    tenant,
  );
  run(
    "INSERT INTO counts(tenant,lot,expected,counted,version,status,note,actor) VALUES(?,?,?,?,?,?,?,?)",
    tenant,
    lot.id,
    lot.quantity,
    Math.max(0, lot.quantity - 1),
    lot.version,
    "pending",
    "Sample count discrepancy for manager review",
    "Jordan · demo operator",
  );
  audit(
    { tenant, name: "Demo setup" },
    "Workspace seeded",
    "36 products, 5 locations, 4 suppliers, 28 days of fictional sales. Historical sales precede opening balances.",
  );
}
