WFDASH-WEBAPP/scripts/setup-pocketbase.ts

import PocketBase from "pocketbase";

const PB_URL = "http://127.0.0.1:8090";
const ADMIN_EMAIL = "david.els1@outlook.com";
const ADMIN_PASSWORD = "1234567890";

async function setup() {
  const pb = new PocketBase(PB_URL);

  console.log("Authenticating as admin...");
  const authData = await pb.collection("_superusers").authWithPassword(ADMIN_EMAIL, ADMIN_PASSWORD);

  function withSystemFields(fields: Record<string, unknown>[]) {
    const existing = fields.filter(f => f.name !== "created" && f.name !== "updated");
    const idIdx = existing.findIndex(f => f.name === "id");
    existing.splice(idIdx + 1, 0, ...SYSTEM_FIELDS);
    return existing;
  }

  async function createCollection(data: Record<string, unknown>) {
    const body = {
      ...data,
      fields: withSystemFields(data.fields as Record<string, unknown>[]),
    };
    const response = await fetch(`${PB_URL}/api/collections`, {
      method: "POST",
      headers: {
        "Content-Type": "application/json",
        "Authorization": authData.token,
      },
      body: JSON.stringify(body),
    });
    if (!response.ok) {
      const err = await response.json();
      console.error("API Error:", JSON.stringify(err, null, 2));
      throw new Error(err.message || "Failed to create collection");
    }
    return response.json();
  }

  const authRule = "@request.auth.id != ''";

  const SYSTEM_FIELDS = [
    { id: "autodate2990389176", name: "created", type: "autodate", system: true, hidden: false, onCreate: true, onUpdate: false, presentable: false },
    { id: "autodate3332085495", name: "updated", type: "autodate", system: true, hidden: false, onCreate: true, onUpdate: true, presentable: false },
  ];

  // Delete all existing collections (including stale ones from other projects)
  // Using raw API because SDK delete is unreliable for this
  const allCollections = ["order_items", "quote_items", "purchase_orders", "orders", "quotes", "suppliers", "company_details", "companies", "customers", "stock_items", "book_out_records", "logs"];
  for (const colName of allCollections) {
    try {
      const existing = await pb.collections.getList(1, 1, { filter: `name = "${colName}"` });
      if (existing.items.length > 0) {
        console.log(`Deleting "${colName}" (${existing.items[0].id})...`);
        const resp = await fetch(`${PB_URL}/api/collections/${existing.items[0].id}`, {
          method: "DELETE",
          headers: { "Authorization": authData.token },
        });
        if (!resp.ok) console.error(`  Failed to delete ${colName}: ${resp.status}`);
      }
    } catch {}
  }

  // Phase 1: Base collections
  console.log("\n--- Creating base collections ---");

  await createCollection({
    name: "companies",
    type: "base",
    fields: [
      { name: "company", type: "text", required: false, options: { max: 255 } },
      { name: "address", type: "text", required: false, options: { max: 500 } },
      { name: "vendor", type: "text", required: false, options: { max: 100 } },
      // Native PB geoPoint for future routing / delivery features.
      // Default is {lon:0, lat:0} (Null Island) — the UI treats that
      // as empty. Diverges from customers/suppliers which use combined
      // text — see 2026-06-28-add-companies-coordinates migration.
      { name: "coordinates", type: "geoPoint", required: false },
    ],
    listRule: authRule,
    viewRule: authRule,
    createRule: authRule,
    updateRule: authRule,
    deleteRule: authRule,
  });
  console.log("✓ companies");

  await createCollection({
    name: "company_details",
    type: "base",
    fields: [
      { name: "company_name", type: "text", required: true, options: { max: 100 } },
      { name: "address", type: "text", required: false, options: { max: 500 } },
      { name: "number", type: "text", required: false, options: { max: 20 } },
      { name: "email", type: "email", required: false },
      { name: "vat_number", type: "text", required: false, options: { max: 20 } },
      { name: "registration_number", type: "text", required: false, options: { max: 20 } },
    ],
    listRule: authRule,
    viewRule: authRule,
    createRule: authRule,
    updateRule: authRule,
    deleteRule: authRule,
  });
  console.log("✓ company_details");

  await createCollection({
    name: "suppliers",
    type: "base",
    fields: [
      { name: "suppliername", type: "text", required: false, options: { max: 255 } },
      { name: "email", type: "email", required: false },
      { name: "suppliernumber", type: "text", required: false, options: { max: 255 } },
      { name: "supplieraddress", type: "text", required: false, options: { max: 500 } },
      { name: "supply_tags", type: "text", required: false, options: { max: 500 } },
      { name: "coordinates", type: "text", required: false, options: { max: 255 } },
      { name: "closingtime", type: "text", required: false, options: { max: 255 } },
    ],
    listRule: authRule,
    viewRule: authRule,
    createRule: authRule,
    updateRule: authRule,
    deleteRule: authRule,
  });
  console.log("✓ suppliers");

  // Get IDs
  const companiesId = (await pb.collections.getList(1, 1, { filter: 'name = "companies"' })).items[0].id;
  const suppliersId = (await pb.collections.getList(1, 1, { filter: 'name = "suppliers"' })).items[0].id;
  // `users` is PB's built-in auth collection, auto-created on first
  // server boot — same shape as a base collection but the auth gate is
  // managed by PB itself. We reference its id for the customer.rep
  // relation below.
  const usersId = (await pb.collections.getList(1, 1, { filter: 'name = "users"' })).items[0].id;

  await createCollection({
    name: "customers",
    type: "base",
    fields: [
      { name: "customer", type: "text", required: false, options: { max: 255 } },
      { name: "email", type: "text", required: false, options: { max: 255 } },
      { name: "alternative_email", type: "text", required: false, options: { max: 255 } },
      { name: "buyer_email", type: "text", required: false, options: { max: 255 } },
      { name: "number", type: "text", required: false, options: { max: 255 } },
      // PB relation → companies. A customer works for a company but
      // may deliver to a different address; delivery coordinates
      // belong on the customer (below), not the company.
      { name: "company", type: "relation", required: false, collectionId: companiesId, maxSelect: 1 },
      { name: "dateadded", type: "date", required: false },
      { name: "lastvisiteddate", type: "date", required: false },
      // PB relation → users. Sales rep handling this customer.
      { name: "rep", type: "relation", required: false, collectionId: usersId, maxSelect: 1 },
      // Native PB geoPoint. Default is {lon:0, lat:0} (Null Island) —
      // the UI treats that as empty.
      { name: "coordinates", type: "geoPoint", required: false },
      { name: "description", type: "text", required: false, options: { max: 5000 } },
    ],
    listRule: authRule,
    viewRule: authRule,
    createRule: authRule,
    updateRule: authRule,
    deleteRule: authRule,
  });
  console.log("✓ customers");

  const customersId = (await pb.collections.getList(1, 1, { filter: 'name = "customers"' })).items[0].id;
  console.log(`\nIDs: customers=${customersId}, companies=${companiesId}, suppliers=${suppliersId}, users=${usersId}`);

  // Phase 2: Quotes
  console.log("\n--- Creating quotes ---");
  await createCollection({
    name: "quotes",
    type: "base",
    fields: [
      { name: "quote_number", type: "text", required: true, options: { max: 50 } },
      { name: "quote_reference", type: "text", required: false, options: { max: 50 } },
      { name: "external_number", type: "text", required: false, options: { max: 50 } },
      { name: "rfq_number", type: "text", required: false, options: { max: 50 } },
      { name: "markup", type: "number", required: false },
      { name: "customer", type: "relation", required: true, collectionId: customersId, maxSelect: 1 },
      { name: "company", type: "relation", required: false, collectionId: companiesId, maxSelect: 1 },
      { name: "claimed_by", type: "text", required: false, options: { max: 100 } },
      { name: "description", type: "text", required: false, options: { max: 5000 } },
      { name: "status", type: "select", required: false, values: ["new", "claimed", "processed", "emailed", "approval_pending", "approved", "rejected", "problem", "complete", "cancelled", "follow_up", "email_failed", "unmatched_bounce"] },
      { name: "approved_by", type: "text", required: false, options: { max: 100 } },
      { name: "notes", type: "text", required: false, options: { max: 5000 } },
      { name: "email_sender", type: "email", required: false },
      { name: "email_subject", type: "text", required: false, options: { max: 255 } },
      { name: "email_body", type: "text", required: false, options: { max: 10000 } },
      { name: "has_attachments", type: "bool", required: false },
      { name: "company_letterhead", type: "select", required: false, values: ["CNL", "ISHERWOOD", "JEI"] },
      { name: "email_delivered", type: "bool", required: false },
      { name: "email_delivered_at", type: "date", required: false },
      { name: "email_received_at", type: "date", required: false },
      { name: "email_opened", type: "bool", required: false },
      { name: "email_opened_at", type: "date", required: false },
      { name: "email_tracking_id", type: "text", required: false, options: { max: 64 } },
      { name: "has_new_reply", type: "bool", required: false },
      { name: "last_reply_at", type: "date", required: false },
      { name: "email_failed_at", type: "date", required: false },
      { name: "pdf_generated_at", type: "date", required: false },
    ],
    listRule: authRule,
    viewRule: authRule,
    createRule: authRule,
    updateRule: authRule,
    deleteRule: authRule,
  });
  console.log("✓ quotes");

  const quotesId = (await pb.collections.getList(1, 1, { filter: 'name = "quotes"' })).items[0].id;

  // Phase 3: Orders
  console.log("\n--- Creating orders ---");
  await createCollection({
    name: "orders",
    type: "base",
    fields: [
      { name: "order_number", type: "text", required: true, options: { max: 50 } },
      { name: "company", type: "relation", required: true, collectionId: companiesId, maxSelect: 1 },
      { name: "customer", type: "relation", required: false, collectionId: customersId, maxSelect: 1 },
      { name: "quote", type: "relation", required: false, collectionId: quotesId, maxSelect: 1 },
      { name: "notes", type: "text", required: false, options: { max: 5000 } },
      { name: "quote_matching_attempted", type: "bool", required: false },
      { name: "quote_match_confidence", type: "number", required: false },
      { name: "potential_quote", type: "relation", required: false, collectionId: quotesId, maxSelect: 1 },
      { name: "potential_quote_confidence", type: "number", required: false },
      { name: "is_repair_order", type: "bool", required: false },
      // True once an invoice has been generated for this order.
      // Independent of item lifecycle — an order can be marked
      // invoiced before, during, or after the items have all been
      // delivered. Set by the invoicing action; never derived.
      { name: "invoiced", type: "bool", required: false },
    ],
    listRule: authRule,
    viewRule: authRule,
    createRule: authRule,
    updateRule: authRule,
    deleteRule: authRule,
  });
  console.log("✓ orders");

  const ordersId = (await pb.collections.getList(1, 1, { filter: 'name = "orders"' })).items[0].id;

  // Phase 4: PO, Quote Items, Order Items
  console.log("\n--- Creating dependent collections ---");

  await createCollection({
    name: "purchase_orders",
    type: "base",
    fields: [
      { name: "po_number", type: "text", required: true, options: { max: 50 } },
      { name: "order", type: "relation", required: true, collectionId: ordersId, maxSelect: 1 },
      { name: "supplier", type: "relation", required: true, collectionId: suppliersId, maxSelect: 1 },
      { name: "status", type: "select", required: false, values: ["draft", "sent", "received"] },
      { name: "total_amount", type: "number", required: false, options: { min: 0 } },
    ],
    listRule: authRule,
    viewRule: authRule,
    createRule: authRule,
    updateRule: authRule,
    deleteRule: authRule,
  });
  console.log("✓ purchase_orders");

  await createCollection({
    name: "quote_items",
    type: "base",
    fields: [
      { name: "quote", type: "relation", required: true, collectionId: quotesId, maxSelect: 1 },
      { name: "description", type: "text", required: false, options: { max: 5000 } },
      { name: "quote_number", type: "text", required: false, options: { max: 100 } },
      { name: "quote_reference", type: "text", required: false, options: { max: 100 } },
      { name: "quantity", type: "number", required: false, options: { min: 0 } },
      { name: "supplier", type: "relation", required: false, collectionId: suppliersId, maxSelect: 1 },
      { name: "cost_price", type: "number", required: false, options: { min: 0 } },
      { name: "selling_price", type: "number", required: false, options: { min: 0 } },
      { name: "notes", type: "text", required: false, options: { max: 5000 } },
      { name: "is_processed", type: "bool", required: false },
      { name: "markup", type: "number", required: false },
      { name: "included_in_quote", type: "bool", required: false },
      { name: "quote_pdf_id", type: "text", required: false, options: { max: 50 } },
      // Flipped to true the first time the user manually saves the
      // item (process page "Save Item", or quote-form item save).
      // Gates the AI rewrite feature — we never let the LLM rewrite
      // an item the user hasn't put their own touch on.
      { name: "user_edited", type: "bool", required: false },
    ],
    listRule: authRule,
    viewRule: authRule,
    createRule: authRule,
    updateRule: authRule,
    deleteRule: authRule,
  });
  console.log("✓ quote_items");

  const poId = (await pb.collections.getList(1, 1, { filter: 'name = "purchase_orders"' })).items[0].id;

  await createCollection({
    name: "order_items",
    type: "base",
    fields: [
      { name: "order", type: "relation", required: true, collectionId: ordersId, maxSelect: 1 },
      { name: "description", type: "text", required: true, options: { max: 255 } },
      { name: "po_description", type: "text", required: false, options: { max: 5000 } },
      { name: "quantity", type: "number", required: false, options: { min: 0 } },
      { name: "order_qty", type: "number", required: false },
      { name: "selling_price", type: "number", required: true, options: { min: 0 } },
      { name: "supplier", type: "relation", required: false, collectionId: suppliersId, maxSelect: 1 },
      { name: "cost_price", type: "number", required: false, options: { min: 0 } },
      { name: "markup", type: "number", required: false },
      { name: "purchase_order", type: "relation", required: false, collectionId: poId, maxSelect: 1 },
      { name: "item_status", type: "select", required: false, values: ["pending", "processed", "po_generated", "driver_pool", "assigned", "collected", "stock_verified", "picking", "ready_delivery", "delivered", "cancelled"] },
      { name: "notes", type: "text", required: false, options: { max: 5000 } },
    ],
    listRule: authRule,
    viewRule: authRule,
    createRule: authRule,
    updateRule: authRule,
    deleteRule: authRule,
  });
  console.log("✓ order_items");

  console.log("\n✅ Setup complete!");
}

setup().catch(console.error);