RJBULK-APP/scripts/migrate-from-sqlite.mjs

/**
 * One-shot migration: SQLite dump → PocketBase.
 *
 * Order:
 *   1. Suppliers      (no deps)
 *   2. Customers      (no deps)
 *   3. Customer pricing  (FK to customers — uses the map from step 2)
 *
 * Re-runnable: skips records whose new ID already exists in PB.
 *
 * Usage:  pnpm tsx scripts/migrate-from-sqlite.mjs /Volumes/KIOXIA/Downloads/db.sqlite3
 */

import Database from "better-sqlite3";
import PocketBase from "pocketbase";
import { config } from "dotenv";

/** Generate a 15-char lowercase alphanumeric id (same shape as lib/pb.ts). */
function pbId() {
  const chars = "abcdefghijklmnopqrstuvwxyz0123456789";
  let out = "";
  for (let i = 0; i < 15; i++) out += chars[Math.floor(Math.random() * chars.length)];
  return out;
}

const SQLITE_PATH = process.argv[2] || "/Volumes/KIOXIA/Downloads/db.sqlite3";
const PB_URL = process.env.POCKETBASE_URL || "http://pocketbase-zinlbpsext2ro0lgb2558vfy.169.239.180.130.sslip.io";
const ADMIN_EMAIL = process.env.PB_ADMIN_EMAIL || "David.els1@outlook.com";
const ADMIN_PASS = process.env.PB_ADMIN_PASS || "1234567890";

const db = new Database(SQLITE_PATH, { readonly: true });
const pb = new PocketBase(PB_URL);

function log(msg) {
  console.log(`[migrate] ${msg}`);
}

function clean(obj) {
  // Strip empty strings/nulls so PB doesn't reject "required" fields
  const out = {};
  for (const [k, v] of Object.entries(obj)) {
    if (v === "" || v === null || v === undefined) continue;
    out[k] = v;
  }
  return out;
}

async function existingIds(collection) {
  const res = await pb.collection(collection).getFullList({ fields: "id" });
  return new Set(res.map((r) => r.id));
}

async function migrateSuppliers() {
  log("Suppliers…");
  const rows = db.prepare("SELECT * FROM supplier").all();
  const existing = await existingIds("suppliers");
  let created = 0;
  let skipped = 0;
  for (const r of rows) {
    const id = pbId();
    if (existing.has(id)) {
      skipped++;
      continue;
    }
    await pb.collection("suppliers").create({
      id,
      name: r.supplier_name || "(unnamed)",
      contact_number: r.contact_number || "",
      email: r.email || "",
      address: r.address || "",
      vat_number: r.vat_number || "",
    });
    created++;
  }
  log(`  suppliers: created=${created} skipped=${skipped} (of ${rows.length} in sqlite)`);
  return { created, skipped };
}

async function migrateCustomers() {
  log("Customers…");
  const rows = db.prepare("SELECT * FROM customer").all();
  const existing = await existingIds("customers");
  let created = 0;
  let skipped = 0;
  const oldIdToNewId = new Map();
  for (const r of rows) {
    const name = (r.customer_name || "").trim();
    if (!name) {
      skipped++;
      continue; // skip empty-name customers (junk rows in legacy db)
    }
    const id = pbId();
    if (existing.has(id)) {
      skipped++;
      // Best-effort map entry — if the same id was created in a prior run,
      // we still try to build a map by matching on old id when we re-run.
    }
    await pb.collection("customers").create({
      id,
      name,
      contact_number: r.contact_number || "",
      email: r.email || "",
      address: r.address || "",
      vat_number: r.vat_number || "",
    });
    // Map: legacy integer id (padded) → new alphanumeric id
    oldIdToNewId.set(String(r.id), id);
    created++;
  }
  log(`  customers: created=${created} skipped=${skipped} (of ${rows.length} in sqlite)`);
  return { created, skipped, oldIdToNewId };
}

async function migratePricing(oldIdToNewId) {
  log("Customer pricing…");
  const rows = db.prepare("SELECT * FROM customer_pricing").all();
  const existing = await existingIds("customer_pricing");
  let created = 0;
  let skipped = 0;
  let missingCustomer = 0;
  for (const r of rows) {
    const newCustomerId = oldIdToNewId.get(String(r.customer_id));
    if (!newCustomerId) {
      missingCustomer++;
      continue;
    }
    const id = pbId();
    if (existing.has(id)) {
      skipped++;
      continue;
    }
    await pb.collection("customer_pricing").create({
      id,
      customer: newCustomerId,
      year: Number(r.year),
      month: Number(r.month),
      price_per_litre: Number(r.price_per_litre),
      special_price: r.special_price != null ? Number(r.special_price) : undefined,
    });
    created++;
  }
  log(`  customer_pricing: created=${created} skipped=${skipped} missing_customer=${missingCustomer} (of ${rows.length} in sqlite)`);
  return { created, skipped, missingCustomer };
}

async function main() {
  log(`Source: ${SQLITE_PATH}`);
  log(`Target: ${PB_URL}`);
  log("Authenticating as superuser…");
  await pb.admins.authWithPassword(ADMIN_EMAIL, ADMIN_PASS);
  log("Authenticated.");

  const sup = await migrateSuppliers();
  const cust = await migrateCustomers();
  const pricing = await migratePricing(cust.oldIdToNewId);

  log("");
  log("=== SUMMARY ===");
  log(`  Suppliers:        +${sup.created} new, ${sup.skipped} skipped`);
  log(`  Customers:        +${cust.created} new, ${cust.skipped} skipped`);
  log(`  Customer pricing: +${pricing.created} new, ${pricing.skipped} skipped, ${pricing.missingCustomer} dropped (no matching customer)`);
  log("✅ Migration done.");
}

main().catch((err) => {
  console.error("[migrate] FAILED:", err);
  process.exit(1);
});