import { sql, type Transaction } from "kysely";
import type { Database } from "./db.js";
import { db, emitSyncEvent, toNumber } from "./db.js";
import type {
  AdjustmentInput,
  AssembleInput,
  DisassembleInput,
  ReceiveInput,
  TransferInput
} from "@inventory/shared";

type Tx = Transaction<Database>;
type OperationType = Database["inventory_operations"]["type"];

class InventoryError extends Error {
  statusCode: number;
  details?: unknown;

  constructor(message: string, statusCode = 400, details?: unknown) {
    super(message);
    this.statusCode = statusCode;
    this.details = details;
  }
}

export { InventoryError };

async function findExistingOperation(tx: Tx, idempotencyKey: string) {
  return tx
    .selectFrom("inventory_operations")
    .selectAll()
    .where("idempotency_key", "=", idempotencyKey)
    .executeTakeFirst();
}

async function createOperation(
  tx: Tx,
  input: {
    idempotencyKey: string;
    type: OperationType;
    userId: string;
    metadata?: unknown;
    note?: string | null;
  }
) {
  const existing = await findExistingOperation(tx, input.idempotencyKey);
  if (existing) return { operation: existing, created: false };

  const operation = await tx
    .insertInto("inventory_operations")
    .values({
      idempotency_key: input.idempotencyKey,
      type: input.type,
      user_id: input.userId,
      status: "committed",
      note: input.note ?? null,
      metadata: sql`${JSON.stringify(input.metadata ?? {})}::jsonb`
    })
    .returningAll()
    .executeTakeFirstOrThrow();
  return { operation, created: true };
}

async function getBalance(tx: Tx, partId: string, locationId: string) {
  const row = await tx
    .selectFrom("inventory_balances")
    .select(["quantity"])
    .where("part_id", "=", partId)
    .where("location_id", "=", locationId)
    .forUpdate()
    .executeTakeFirst();
  return toNumber(row?.quantity);
}

async function applyBalance(tx: Tx, partId: string, locationId: string, quantityDelta: number) {
  if (quantityDelta < 0) {
    const current = await getBalance(tx, partId, locationId);
    if (current + quantityDelta < -0.0001) {
      const part = await tx.selectFrom("parts").select(["name"]).where("id", "=", partId).executeTakeFirst();
      const location = await tx.selectFrom("locations").select(["name"]).where("id", "=", locationId).executeTakeFirst();
      throw new InventoryError("Insufficient stock", 409, {
        partId,
        partName: part?.name,
        locationId,
        locationName: location?.name,
        available: current,
        required: Math.abs(quantityDelta)
      });
    }
  }

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

  if (quantityDelta < 0) {
    await tx
      .updateTable("inventory_balances")
      .set({
        quantity: sql`quantity + ${quantityDelta}`,
        updated_at: sql`now()`
      })
      .where("part_id", "=", partId)
      .where("location_id", "=", locationId)
      .execute();
  }
}

async function addLedger(
  tx: Tx,
  operationId: string,
  action: OperationType,
  partId: string,
  locationId: string,
  quantityDelta: number,
  unitCostSnapshot: number
) {
  await tx
    .insertInto("inventory_ledger")
    .values({
      operation_id: operationId,
      action,
      part_id: partId,
      location_id: locationId,
      quantity_delta: String(quantityDelta),
      unit_cost_snapshot: String(unitCostSnapshot)
    })
    .execute();
}

async function currentTotalQuantity(tx: Tx, partId: string) {
  const row = await tx
    .selectFrom("inventory_balances")
    .select((eb) => eb.fn.coalesce(eb.fn.sum<string>("quantity"), sql<string>`0`).as("quantity"))
    .where("part_id", "=", partId)
    .executeTakeFirst();
  return toNumber(row?.quantity);
}

async function updateWeightedAverageCost(tx: Tx, partId: string, incomingQuantity: number, incomingUnitCost: number) {
  const part = await tx.selectFrom("parts").select(["average_cost"]).where("id", "=", partId).forUpdate().executeTakeFirstOrThrow();
  const currentQuantity = await currentTotalQuantity(tx, partId);
  const currentCost = toNumber(part.average_cost);
  const newAverage =
    currentQuantity + incomingQuantity <= 0
      ? incomingUnitCost
      : (currentQuantity * currentCost + incomingQuantity * incomingUnitCost) / (currentQuantity + incomingQuantity);

  await tx.updateTable("parts").set({ average_cost: String(newAverage) }).where("id", "=", partId).execute();
  return newAverage;
}

async function getPartCost(tx: Tx, partId: string) {
  const part = await tx.selectFrom("parts").select(["average_cost"]).where("id", "=", partId).executeTakeFirstOrThrow();
  return toNumber(part.average_cost);
}

export async function receiveStock(input: ReceiveInput, userId: string) {
  const result = await db.transaction().execute(async (tx) => {
    const { operation, created } = await createOperation(tx, {
      idempotencyKey: input.idempotencyKey,
      type: "receive",
      userId,
      metadata: input
    });

    if (created) {
      await updateWeightedAverageCost(tx, input.partId, input.quantity, input.unitCost);
      await tx
        .insertInto("inventory_lots")
        .values({
          part_id: input.partId,
          location_id: input.locationId,
          received_quantity: String(input.quantity),
          remaining_quantity: String(input.quantity),
          unit_cost: String(input.unitCost),
          vendor_name: input.vendorName ?? null,
          reference: input.reference ?? null
        })
        .execute();
      await applyBalance(tx, input.partId, input.locationId, input.quantity);
      await addLedger(tx, operation.id, "receive", input.partId, input.locationId, input.quantity, input.unitCost);
    }

    return operation;
  });
  await emitSyncEvent("inventory.updated", { operationId: result.id, type: "receive" });
  return result;
}

export async function transferStock(input: TransferInput, userId: string) {
  if (input.fromLocationId === input.toLocationId) {
    throw new InventoryError("Source and destination must be different");
  }

  const result = await db.transaction().execute(async (tx) => {
    const { operation, created } = await createOperation(tx, {
      idempotencyKey: input.idempotencyKey,
      type: "transfer",
      userId,
      metadata: input
    });
    if (created) {
      const cost = await getPartCost(tx, input.partId);
      await applyBalance(tx, input.partId, input.fromLocationId, -input.quantity);
      await applyBalance(tx, input.partId, input.toLocationId, input.quantity);
      await addLedger(tx, operation.id, "transfer", input.partId, input.fromLocationId, -input.quantity, cost);
      await addLedger(tx, operation.id, "transfer", input.partId, input.toLocationId, input.quantity, cost);
    }
    return operation;
  });
  await emitSyncEvent("inventory.updated", { operationId: result.id, type: "transfer" });
  return result;
}

export async function adjustStock(input: AdjustmentInput, userId: string) {
  if (Math.abs(input.quantityDelta) < 0.0001) {
    throw new InventoryError("Quantity change must not be zero");
  }

  const result = await db.transaction().execute(async (tx) => {
    const { operation, created } = await createOperation(tx, {
      idempotencyKey: input.idempotencyKey,
      type: "adjust",
      userId,
      metadata: input,
      note: input.reason
    });
    if (created) {
      const cost = await getPartCost(tx, input.partId);
      await applyBalance(tx, input.partId, input.locationId, input.quantityDelta);
      await addLedger(tx, operation.id, "adjust", input.partId, input.locationId, input.quantityDelta, cost);
    }
    return operation;
  });
  await emitSyncEvent("inventory.updated", { operationId: result.id, type: "adjust" });
  return result;
}

async function activeBom(tx: Tx, assemblyPartId: string, bomVersionId?: string) {
  const version = bomVersionId
    ? await tx.selectFrom("bom_versions").selectAll().where("id", "=", bomVersionId).executeTakeFirst()
    : await tx
        .selectFrom("bom_versions")
        .selectAll()
        .where("assembly_part_id", "=", assemblyPartId)
        .where("active", "=", true)
        .orderBy("version", "desc")
        .executeTakeFirst();
  if (!version) throw new InventoryError("No active BOM version found", 404);

  const lines = await tx
    .selectFrom("bom_lines")
    .innerJoin("parts", "parts.id", "bom_lines.component_part_id")
    .select([
      "bom_lines.component_part_id as componentPartId",
      "bom_lines.quantity_required as quantityRequired",
      "parts.average_cost as averageCost"
    ])
    .where("bom_version_id", "=", version.id)
    .execute();
  return { version, lines };
}

async function autoPickSources(tx: Tx, componentPartId: string, requiredQuantity: number) {
  const balances = await tx
    .selectFrom("inventory_balances")
    .innerJoin("locations", "locations.id", "inventory_balances.location_id")
    .select(["inventory_balances.location_id as locationId", "inventory_balances.quantity as quantity"])
    .where("inventory_balances.part_id", "=", componentPartId)
    .where("inventory_balances.quantity", ">", "0")
    .orderBy("locations.priority", "asc")
    .execute();

  let remaining = requiredQuantity;
  const sources: { componentPartId: string; locationId: string; quantity: number }[] = [];
  for (const balance of balances) {
    if (remaining <= 0) break;
    const take = Math.min(remaining, toNumber(balance.quantity));
    if (take > 0) {
      sources.push({ componentPartId, locationId: balance.locationId, quantity: take });
      remaining -= take;
    }
  }
  if (remaining > 0.0001) {
    throw new InventoryError("Insufficient stock for auto-pick", 409, { componentPartId, requiredQuantity, shortage: remaining });
  }
  return sources;
}

export async function assembleProduct(input: AssembleInput, userId: string) {
  const result = await db.transaction().execute(async (tx) => {
    const { version, lines } = await activeBom(tx, input.assemblyPartId, input.bomVersionId);
    const { operation, created } = await createOperation(tx, {
      idempotencyKey: input.idempotencyKey,
      type: "assemble",
      userId,
      metadata: { ...input, bomVersionId: version.id }
    });

    if (!created) return operation;

    const allSources: { componentPartId: string; locationId: string; quantity: number }[] = [];
    for (const line of lines) {
      const required = toNumber(line.quantityRequired) * input.quantity;
      if (input.sourcingMode === "single_location") {
        if (!input.sourceLocationId) throw new InventoryError("Source location is required");
        allSources.push({ componentPartId: line.componentPartId, locationId: input.sourceLocationId, quantity: required });
      } else if (input.sourcingMode === "manual") {
        const manual = input.sources?.filter((source) => source.componentPartId === line.componentPartId) ?? [];
        const total = manual.reduce((sum, source) => sum + source.quantity, 0);
        if (Math.abs(total - required) > 0.0001) {
          throw new InventoryError("Manual sources do not match BOM requirement", 400, { componentPartId: line.componentPartId, required, provided: total });
        }
        allSources.push(...manual);
      } else {
        allSources.push(...(await autoPickSources(tx, line.componentPartId, required)));
      }
    }

    for (const source of allSources) {
      const cost = await getPartCost(tx, source.componentPartId);
      await applyBalance(tx, source.componentPartId, source.locationId, -source.quantity);
      await addLedger(tx, operation.id, "assemble", source.componentPartId, source.locationId, -source.quantity, cost);
    }

    const assemblyUnitCost = lines.reduce((sum, line) => sum + toNumber(line.quantityRequired) * toNumber(line.averageCost), 0);
    await updateWeightedAverageCost(tx, input.assemblyPartId, input.quantity, assemblyUnitCost);
    await applyBalance(tx, input.assemblyPartId, input.outputLocationId, input.quantity);
    await addLedger(tx, operation.id, "assemble", input.assemblyPartId, input.outputLocationId, input.quantity, assemblyUnitCost);
    return operation;
  });
  await emitSyncEvent("inventory.updated", { operationId: result.id, type: "assemble" });
  return result;
}

export async function disassembleProduct(input: DisassembleInput, userId: string) {
  const returnLocationId = input.returnLocationId ?? input.inputLocationId;
  const result = await db.transaction().execute(async (tx) => {
    const { version, lines } = await activeBom(tx, input.assemblyPartId, input.bomVersionId);
    const { operation, created } = await createOperation(tx, {
      idempotencyKey: input.idempotencyKey,
      type: "disassemble",
      userId,
      metadata: { ...input, bomVersionId: version.id }
    });
    if (!created) return operation;

    const assemblyCost = await getPartCost(tx, input.assemblyPartId);
    await applyBalance(tx, input.assemblyPartId, input.inputLocationId, -input.quantity);
    await addLedger(tx, operation.id, "disassemble", input.assemblyPartId, input.inputLocationId, -input.quantity, assemblyCost);

    for (const line of lines) {
      const quantity = toNumber(line.quantityRequired) * input.quantity;
      const cost = await getPartCost(tx, line.componentPartId);
      await applyBalance(tx, line.componentPartId, returnLocationId, quantity);
      await addLedger(tx, operation.id, "disassemble", line.componentPartId, returnLocationId, quantity, cost);
    }
    return operation;
  });
  await emitSyncEvent("inventory.updated", { operationId: result.id, type: "disassemble" });
  return result;
}