import { sql } from "kysely";
import { db, pool } from "./db.js";

async function upsertLocation(name: string, kind: "warehouse" | "truck" | "other", priority: number) {
  return db
    .insertInto("locations")
    .values({ name, kind, priority })
    .onConflict((oc) => oc.column("name").doUpdateSet({ kind, priority, active: true }))
    .returningAll()
    .executeTakeFirstOrThrow();
}

async function upsertPart(name: string, sku: string, type: "base" | "partial" | "full", averageCost: number) {
  const part = await db
    .insertInto("parts")
    .values({ name, sku, type, average_cost: String(averageCost) })
    .onConflict((oc) => oc.column("sku").doUpdateSet({ name, type, average_cost: String(averageCost), active: true }))
    .returningAll()
    .executeTakeFirstOrThrow();
  await db
    .insertInto("part_aliases")
    .values({ part_id: part.id, code: sku, label: "Generated" })
    .onConflict((oc) => oc.column("code").doNothing())
    .execute();
  return part;
}

async function upsertFileMakerPart(
  fileMakerId: string,
  name: string,
  type: "base" | "partial" | "full",
  averageCost: number
) {
  const bySku = await db.selectFrom("parts").selectAll().where("sku", "=", fileMakerId).executeTakeFirst();
  const byName = bySku ?? (await db.selectFrom("parts").selectAll().where("name", "=", name).executeTakeFirst());

  if (byName) {
    if (byName.sku && byName.sku !== fileMakerId) {
      await db
        .insertInto("part_aliases")
        .values({ part_id: byName.id, code: byName.sku, label: "Previous internal SKU" })
        .onConflict((oc) => oc.column("code").doNothing())
        .execute();
    }

    const part = await db
      .updateTable("parts")
      .set({
        name,
        sku: fileMakerId,
        type,
        average_cost: String(averageCost),
        active: true
      })
      .where("id", "=", byName.id)
      .returningAll()
      .executeTakeFirstOrThrow();

    await db
      .insertInto("part_aliases")
      .values({ part_id: part.id, code: fileMakerId, label: "FileMaker ID" })
      .onConflict((oc) => oc.column("code").doNothing())
      .execute();

    return part;
  }

  const part = await upsertPart(name, fileMakerId, type, averageCost);
  await db
    .insertInto("part_aliases")
    .values({ part_id: part.id, code: fileMakerId, label: "FileMaker ID" })
    .onConflict((oc) => oc.column("code").doNothing())
    .execute();
  return part;
}

async function upsertThreshold(partId: string, locationId: string, minQuantity: number, reorderQuantity: number) {
  await db
    .insertInto("stock_thresholds")
    .values({
      part_id: partId,
      location_id: locationId,
      min_quantity: String(minQuantity),
      reorder_quantity: String(reorderQuantity)
    })
    .onConflict((oc) =>
      oc.columns(["part_id", "location_id"]).doUpdateSet({
        min_quantity: String(minQuantity),
        reorder_quantity: String(reorderQuantity)
      })
    )
    .execute();
}

async function seedReceiveIfMissing(userId: string | null, partId: string, locationId: string, quantity: number, unitCost: number, reference: string) {
  const existing = await db
    .selectFrom("inventory_operations")
    .select("id")
    .where("idempotency_key", "=", reference)
    .executeTakeFirst();
  if (existing) return;

  await db.transaction().execute(async (tx) => {
    const operation = await tx
      .insertInto("inventory_operations")
      .values({
        idempotency_key: reference,
        type: "receive",
        status: "committed",
        user_id: userId,
        note: "Seed data",
        metadata: sql`${JSON.stringify({ reference, seed: true })}::jsonb`
      })
      .returning(["id"])
      .executeTakeFirstOrThrow();

    await tx
      .insertInto("inventory_lots")
      .values({
        part_id: partId,
        location_id: locationId,
        received_quantity: String(quantity),
        remaining_quantity: String(quantity),
        unit_cost: String(unitCost),
        vendor_name: "Seed data",
        reference
      })
      .execute();

    await tx
      .insertInto("inventory_balances")
      .values({
        part_id: partId,
        location_id: locationId,
        quantity: String(quantity)
      })
      .onConflict((oc) =>
        oc.columns(["part_id", "location_id"]).doUpdateSet({
          quantity: sql`inventory_balances.quantity + excluded.quantity`,
          updated_at: sql`now()`
        })
      )
      .execute();

    await tx
      .insertInto("inventory_ledger")
      .values({
        operation_id: operation.id,
        part_id: partId,
        location_id: locationId,
        quantity_delta: String(quantity),
        unit_cost_snapshot: String(unitCost),
        action: "receive"
      })
      .execute();
  });
}

async function upsertBom(assemblyPartId: string, lines: { componentPartId: string; quantityRequired: number }[]) {
  const existingBom = await db.selectFrom("bom_versions").select("id").where("assembly_part_id", "=", assemblyPartId).executeTakeFirst();
  if (existingBom) return;

  const version = await db
    .insertInto("bom_versions")
    .values({
      assembly_part_id: assemblyPartId,
      name: "Seed recipe",
      version: 1,
      active: true
    })
    .returningAll()
    .executeTakeFirstOrThrow();

  await db
    .insertInto("bom_lines")
    .values(
      lines.map((line) => ({
        bom_version_id: version.id,
        component_part_id: line.componentPartId,
        quantity_required: String(line.quantityRequired)
      }))
    )
    .execute();
}

async function main() {
  const existing = await db.selectFrom("users").select(["id"]).executeTakeFirst();
  const ownerId = existing?.id ?? null;

  const warehouse = await upsertLocation("Warehouse", "warehouse", 10);
  const truck01 = await upsertLocation("Truck01", "truck", 20);
  const truck02 = await upsertLocation("Truck02", "truck", 30);

  const fileMakerParts = [
    { id: "B001", name: "SlatePlate with no base", type: "base" as const, cost: 3.74, low: 500 },
    { id: "B002", name: "Aluminium base w/notch", type: "base" as const, cost: 0.2, low: 100 },
    { id: "B003", name: "Plastic base w/notch", type: "base" as const, cost: 0.68, low: 300 },
    { id: "B004", name: "5\" x 5/16\" stainless lag bolt", type: "base" as const, cost: 0.68, low: 300 },
    { id: "B005", name: "5.5\" x 5/16\" stainless lag bolt", type: "base" as const, cost: 0.68, low: 100 },
    { id: "B006", name: "6\" x 5/16\" stainless lag bolt", type: "base" as const, cost: 0.68, low: 100 },
    { id: "B007", name: "5/16\" x 3/4\" stainless/butyl washer", type: "base" as const, cost: 0.68, low: 100 },
    { id: "B008", name: "Adjuster", type: "base" as const, cost: 0.68, low: 300 },
    { id: "B009", name: "Spacer", type: "base" as const, cost: 0.68, low: 300 },
    { id: "B010", name: "Eco \"L\" w/grommet", type: "base" as const, cost: 0.68, low: 100 },
    { id: "P001", name: "SlatePlate with Aluminium base", type: "partial" as const, cost: 0, low: 0 },
    { id: "P002", name: "SlatePlate with Plastic base", type: "partial" as const, cost: 0, low: 0 },
    { id: "P003", name: "5\" x 5/16\" stainless lag bolt w/stainless/butyl washer", type: "partial" as const, cost: 0, low: 0 },
    { id: "P004", name: "5.5\" x 5/16\" stainless lag bolt w/stainless/butyl washer", type: "partial" as const, cost: 0, low: 0 },
    { id: "P005", name: "6\" x 5/16\" stainless lag bolt w/stainless/butyl washer", type: "partial" as const, cost: 0, low: 0 },
    { id: "P006", name: "6\" x 5/16\" stainless lag bolt w/stainless/butyl washer and Eco \"L\" w/grommet", type: "partial" as const, cost: 0, low: 0 },
    { id: "F001", name: "Fully Assembled Product", type: "full" as const, cost: 0, low: 0 }
  ];

  const partBySku = new Map<string, Awaited<ReturnType<typeof upsertPart>>>();
  for (const item of fileMakerParts) {
    const part = await upsertFileMakerPart(item.id, item.name, item.type, item.cost);
    partBySku.set(item.id, part);
    if (item.type === "base") {
      await upsertThreshold(part.id, warehouse.id, item.low, item.low);
      await upsertThreshold(part.id, truck01.id, Math.max(5, Math.ceil(item.low * 0.15)), Math.max(10, Math.ceil(item.low * 0.3)));
      await upsertThreshold(part.id, truck02.id, Math.max(5, Math.ceil(item.low * 0.15)), Math.max(10, Math.ceil(item.low * 0.3)));
      await seedReceiveIfMissing(ownerId, part.id, warehouse.id, item.low * 2, item.cost, `seed-receive-${item.id}-warehouse`);
      await seedReceiveIfMissing(ownerId, part.id, truck01.id, Math.max(10, Math.ceil(item.low * 0.25)), item.cost, `seed-receive-${item.id}-truck01`);
      await seedReceiveIfMissing(ownerId, part.id, truck02.id, Math.max(8, Math.ceil(item.low * 0.18)), item.cost, `seed-receive-${item.id}-truck02`);
    }
  }

  await upsertBom(partBySku.get("P001")!.id, [
    { componentPartId: partBySku.get("B001")!.id, quantityRequired: 1 },
    { componentPartId: partBySku.get("B002")!.id, quantityRequired: 1 },
    { componentPartId: partBySku.get("B008")!.id, quantityRequired: 1 },
    { componentPartId: partBySku.get("B009")!.id, quantityRequired: 1 }
  ]);

  await upsertBom(partBySku.get("P002")!.id, [
    { componentPartId: partBySku.get("B001")!.id, quantityRequired: 1 },
    { componentPartId: partBySku.get("B003")!.id, quantityRequired: 1 },
    { componentPartId: partBySku.get("B008")!.id, quantityRequired: 1 },
    { componentPartId: partBySku.get("B009")!.id, quantityRequired: 1 }
  ]);

  await upsertBom(partBySku.get("P003")!.id, [
    { componentPartId: partBySku.get("B004")!.id, quantityRequired: 1 },
    { componentPartId: partBySku.get("B007")!.id, quantityRequired: 1 }
  ]);

  await upsertBom(partBySku.get("P004")!.id, [
    { componentPartId: partBySku.get("B005")!.id, quantityRequired: 1 },
    { componentPartId: partBySku.get("B007")!.id, quantityRequired: 1 }
  ]);

  await upsertBom(partBySku.get("P005")!.id, [
    { componentPartId: partBySku.get("B006")!.id, quantityRequired: 1 },
    { componentPartId: partBySku.get("B007")!.id, quantityRequired: 1 }
  ]);

  await upsertBom(partBySku.get("P006")!.id, [
    { componentPartId: partBySku.get("B006")!.id, quantityRequired: 1 },
    { componentPartId: partBySku.get("B007")!.id, quantityRequired: 1 },
    { componentPartId: partBySku.get("B010")!.id, quantityRequired: 1 }
  ]);

  await upsertBom(partBySku.get("F001")!.id, [
    { componentPartId: partBySku.get("P001")!.id, quantityRequired: 1 },
    { componentPartId: partBySku.get("P003")!.id, quantityRequired: 4 },
    { componentPartId: partBySku.get("P006")!.id, quantityRequired: 2 }
  ]);

  const screw = await upsertPart("Stainless Screw", "INV-SCREW-001", "base", 0.1);
  const hinge = await upsertPart("Cabinet Hinge", "INV-HINGE-001", "base", 2.75);
  const kit = await upsertPart("Install Kit", "INV-KIT-001", "partial", 0);
  const product = await upsertPart("Finished Door Pack", "INV-DOORPACK-001", "full", 0);

  await db
    .insertInto("inventory_balances")
    .values([
      { part_id: screw.id, location_id: warehouse.id, quantity: "500" },
      { part_id: hinge.id, location_id: warehouse.id, quantity: "100" },
      { part_id: screw.id, location_id: truck01.id, quantity: "80" },
      { part_id: hinge.id, location_id: truck01.id, quantity: "20" }
    ])
    .onConflict((oc) => oc.columns(["part_id", "location_id"]).doNothing())
    .execute();

  const existingBom = await db.selectFrom("bom_versions").select("id").where("assembly_part_id", "=", kit.id).executeTakeFirst();
  if (!existingBom) {
    const version = await db.insertInto("bom_versions").values({
      assembly_part_id: kit.id,
      name: "Default",
      version: 1,
      active: true
    }).returningAll().executeTakeFirstOrThrow();
    await db.insertInto("bom_lines").values([
      { bom_version_id: version.id, component_part_id: screw.id, quantity_required: "4" },
      { bom_version_id: version.id, component_part_id: hinge.id, quantity_required: "2" }
    ]).execute();
  }

  const existingFullBom = await db.selectFrom("bom_versions").select("id").where("assembly_part_id", "=", product.id).executeTakeFirst();
  if (!existingFullBom) {
    const version = await db.insertInto("bom_versions").values({
      assembly_part_id: product.id,
      name: "Default",
      version: 1,
      active: true
    }).returningAll().executeTakeFirstOrThrow();
    await db.insertInto("bom_lines").values([
      { bom_version_id: version.id, component_part_id: kit.id, quantity_required: "1" },
      { bom_version_id: version.id, component_part_id: hinge.id, quantity_required: "1" }
    ]).execute();
  }
}

main()
  .catch((error) => {
    console.error(error);
    process.exitCode = 1;
  })
  .finally(async () => {
    await pool.end();
  });