import { ok, err, type CommandContext } from "@tailor-platform/erp-kit/core";
import { productionOrderLifecycle } from "../db/productionOrder.lifecycle.generated";
import type { Transaction, Selectable } from "../generated/kysely-tailordb";
import {
  ProductionOrderNotFoundError,
  ProductionOrderNotReleasableError,
  BomNotResolvedError,
  RoutingNotResolvedError,
  CrossCompanyMasterReferenceError,
  CrossSiteMasterReferenceError,
  PlannedMaterialCostUnavailableError,
} from "../lib/errors.generated";
import type { ItemManagementQueries } from "../module";

export interface ReleaseProductionOrderInput {
  id: string;
}

interface ReleaseMaterialValuationSnapshot {
  plannedUnitCost: number;
  currencyCode: string;
  valuationMethod: string;
  sourceValuationReference: string | null;
}

function getOptionalNumber(record: Record<string, unknown>, key: string): number | null {
  const value = record[key];
  return typeof value === "number" && Number.isFinite(value) ? value : null;
}

function getOptionalString(record: Record<string, unknown>, key: string): string | null {
  const value = record[key];
  return typeof value === "string" && value.length > 0 ? value : null;
}

function resolveReleaseMaterialValuation(
  itemRecord: Record<string, unknown> | undefined,
): ReleaseMaterialValuationSnapshot | null {
  if (!itemRecord) {
    return null;
  }

  const valuationMethod =
    getOptionalString(itemRecord, "valuationMethod") ??
    getOptionalString(itemRecord, "costingMethod") ??
    (getOptionalNumber(itemRecord, "standardCostRate") != null ? "STANDARD" : null) ??
    (getOptionalNumber(itemRecord, "costPerUnit") != null ? "AVCO" : null);

  if (!valuationMethod) {
    return null;
  }

  const plannedUnitCost =
    valuationMethod === "STANDARD"
      ? (getOptionalNumber(itemRecord, "standardCostRate") ??
        getOptionalNumber(itemRecord, "costPerUnit"))
      : (getOptionalNumber(itemRecord, "costPerUnit") ??
        getOptionalNumber(itemRecord, "standardCostRate"));

  if (plannedUnitCost == null) {
    return null;
  }

  return {
    plannedUnitCost,
    currencyCode:
      getOptionalString(itemRecord, "currencyCode") ??
      getOptionalString(itemRecord, "valuationCurrency") ??
      "USD",
    valuationMethod,
    sourceValuationReference:
      getOptionalString(itemRecord, "valuationReference") ??
      getOptionalString(itemRecord, "valuationPolicyId") ??
      getOptionalString(itemRecord, "itemValuationId"),
  };
}

/**
 * Function: releaseProductionOrder
 *
 * Turns a draft plan into executable work by resolving active BOM and routing
 * content, freezing snapshots, creating work orders and material requirements,
 * and opening manufacturing cost collection.
 */
export async function run(
  db: Transaction,
  input: ReleaseProductionOrderInput,
  ctx: CommandContext,
  itemManagementQueries: Pick<ItemManagementQueries, "getItem">,
) {
  const { id } = input;

  // 1. Fetch production order with lock
  const order = await db
    .selectFrom("ProductionOrder")
    .selectAll()
    .where("id", "=", id)
    .forUpdate()
    .executeTakeFirst();

  if (!order) {
    return err(new ProductionOrderNotFoundError(id));
  }

  // 2. Validate status is releasable
  const nextStatus = productionOrderLifecycle.tryTransition(order.status, "release");
  if (!nextStatus) {
    return err(new ProductionOrderNotReleasableError(id));
  }

  // 3. Resolve active BOM
  let bom: Selectable<"BillOfMaterial"> | undefined;
  if (order.selectedBomVersionId) {
    bom = await db
      .selectFrom("BillOfMaterial")
      .selectAll()
      .where("id", "=", order.selectedBomVersionId)
      .where("status", "=", "ACTIVE")
      .executeTakeFirst();
  } else {
    bom = await db
      .selectFrom("BillOfMaterial")
      .selectAll()
      .where("parentItemId", "=", order.orderedItemId)
      .where("companyId", "=", order.companyId)
      .where("status", "=", "ACTIVE")
      .executeTakeFirst();
  }

  if (!bom) {
    return err(new BomNotResolvedError(id));
  }

  // 4. Validate BOM company scope
  if (bom.companyId !== order.companyId) {
    return err(new CrossCompanyMasterReferenceError(id));
  }

  // 5. Validate BOM site scope
  if (bom.siteId != null && bom.siteId !== order.siteId) {
    return err(new CrossSiteMasterReferenceError(id));
  }

  // 6. Resolve active routing
  let routing: Selectable<"Routing"> | undefined;
  if (order.selectedRoutingRevisionId) {
    routing = await db
      .selectFrom("Routing")
      .selectAll()
      .where("id", "=", order.selectedRoutingRevisionId)
      .where("status", "=", "ACTIVE")
      .executeTakeFirst();
  } else {
    routing = await db
      .selectFrom("Routing")
      .selectAll()
      .where("parentItemId", "=", order.orderedItemId)
      .where("companyId", "=", order.companyId)
      .where("status", "=", "ACTIVE")
      .executeTakeFirst();
  }

  if (!routing) {
    return err(new RoutingNotResolvedError(id));
  }

  // 7. Validate routing company scope
  if (routing.companyId !== order.companyId) {
    return err(new CrossCompanyMasterReferenceError(id));
  }

  // 8. Validate routing site scope
  if (routing.siteId != null && routing.siteId !== order.siteId) {
    return err(new CrossSiteMasterReferenceError(id));
  }

  // 9. Fetch BOM lines for material requirements
  const bomLines = await db
    .selectFrom("BillOfMaterialLine")
    .selectAll()
    .where("billOfMaterialId", "=", bom.id)
    .execute();

  // 10. Resolve planned material cost for each component
  let totalPlannedMaterialCost = 0;
  let baselineCurrencyCode = "USD";
  const materialRequirements: {
    itemId: string;
    requiredQuantity: number;
    unitOfMeasure: string | null;
    plannedUnitCost: number;
    valuationMethod: string;
    valuationCurrencyCode: string;
    sourceValuationReference: string | null;
  }[] = [];

  for (const line of bomLines) {
    // Resolve release-time valuation from inventory-owned item valuation context.
    const { item } = (await itemManagementQueries.getItem(db, { id: line.itemId }, ctx)).value;
    const itemValuationRecord = (item ?? undefined) as Record<string, unknown> | undefined;
    const valuation = resolveReleaseMaterialValuation(itemValuationRecord);

    if (!valuation) {
      return err(new PlannedMaterialCostUnavailableError(line.itemId));
    }

    const requiredQuantity = line.requiredQuantity * order.plannedQuantity;
    totalPlannedMaterialCost += valuation.plannedUnitCost * requiredQuantity;
    baselineCurrencyCode = valuation.currencyCode;

    materialRequirements.push({
      itemId: line.itemId,
      requiredQuantity,
      unitOfMeasure: line.unitOfMeasure ?? null,
      plannedUnitCost: valuation.plannedUnitCost,
      valuationMethod: valuation.valuationMethod,
      valuationCurrencyCode: valuation.currencyCode,
      sourceValuationReference: valuation.sourceValuationReference,
    });
  }

  // 11. Fetch routing operations for work orders
  const operations = await db
    .selectFrom("RoutingOperation")
    .selectAll()
    .where("routingId", "=", routing.id)
    .execute();

  // 12. Calculate planned labor and machine costs from operations and work centers
  let totalPlannedLaborCost = 0;
  let totalPlannedMachineCost = 0;
  let totalPlannedOverheadCost = 0;
  const workCenterSnapshots = new Map<
    string,
    {
      workCenterId: string;
      code: string | null;
      laborRate: number | null;
      machineRate: number | null;
      overheadAbsorptionMethod: string | null;
      overheadAbsorptionRate: number | null;
      overheadAbsorptionCurrency: string | null;
    }
  >();

  for (const op of operations) {
    const wc = await db
      .selectFrom("WorkCenter")
      .selectAll()
      .where("id", "=", op.workCenterId)
      .executeTakeFirst();

    if (wc) {
      const duration = op.standardSetupTime + op.standardRunTime * order.plannedQuantity;
      const laborCost = (wc.laborRate ?? 0) * duration;
      const machineCost = (wc.machineRate ?? 0) * duration;
      totalPlannedLaborCost += laborCost;
      totalPlannedMachineCost += machineCost;

      const wcRecord = wc as Record<string, unknown>;
      const overheadRate = getOptionalNumber(wcRecord, "overheadAbsorptionRate");
      if (wc.overheadAbsorptionMethod === "PERCENT_OF_LABOR_COST" && overheadRate != null) {
        totalPlannedOverheadCost += laborCost * (overheadRate / 100);
      } else if (
        wc.overheadAbsorptionMethod === "PERCENT_OF_MACHINE_COST" &&
        overheadRate != null
      ) {
        totalPlannedOverheadCost += machineCost * (overheadRate / 100);
      } else if (
        wc.overheadAbsorptionMethod === "FIXED_AMOUNT_PER_GOOD_UNIT" &&
        overheadRate != null
      ) {
        totalPlannedOverheadCost += order.plannedQuantity * overheadRate;
      }

      workCenterSnapshots.set(op.workCenterId, {
        workCenterId: wc.id,
        code: wc.code,
        laborRate: wc.laborRate ?? null,
        machineRate: wc.machineRate ?? null,
        overheadAbsorptionMethod: wc.overheadAbsorptionMethod ?? null,
        overheadAbsorptionRate: overheadRate,
        overheadAbsorptionCurrency: wc.overheadAbsorptionCurrency ?? null,
      });
    }
  }

  // 13. Create BOM snapshot
  await db
    .insertInto("ProductionOrderBomSnapshot")
    .values({
      productionOrderId: order.id,
      parentItemId: bom.parentItemId,
      bomType: bom.bomType,
      snapshotData: JSON.stringify({
        bomId: bom.id,
        revisionNumber: bom.revisionNumber,
        companyId: bom.companyId,
        siteId: bom.siteId,
        effectivityStartDate: bom.effectivityStartDate,
        effectivityEndDate: bom.effectivityEndDate,
        defaultSelection: bom.defaultSelection,
        lines: bomLines.map((line) => {
          const requirement = materialRequirements.find((req) => req.itemId === line.itemId);
          return {
            itemId: line.itemId,
            requiredQuantity: line.requiredQuantity,
            unitOfMeasure: line.unitOfMeasure,
            scrapAssumption: line.scrapAssumption,
            isSubassembly: line.isSubassembly,
            plannedUnitCost: requirement?.plannedUnitCost ?? null,
            currencyCode: requirement?.valuationCurrencyCode ?? null,
            valuationMethod: requirement?.valuationMethod ?? null,
            sourceValuationReference: requirement?.sourceValuationReference ?? null,
          };
        }),
      }),
    })
    .returningAll()
    .executeTakeFirst();

  // 14. Create routing snapshot
  await db
    .insertInto("ProductionOrderRoutingSnapshot")
    .values({
      productionOrderId: order.id,
      routingId: routing.id,
      snapshotData: JSON.stringify({
        routingId: routing.id,
        revisionNumber: routing.revisionNumber,
        companyId: routing.companyId,
        siteId: routing.siteId,
        operations: operations.map((op) => ({
          id: op.id,
          sequenceNumber: op.sequenceNumber,
          operationDescription: op.operationDescription,
          workCenterId: op.workCenterId,
          standardSetupTime: op.standardSetupTime,
          standardRunTime: op.standardRunTime,
          operatorInstructions: op.operatorInstructions,
          workCenterSnapshot: workCenterSnapshots.get(op.workCenterId) ?? null,
        })),
      }),
    })
    .returningAll()
    .executeTakeFirst();

  // 15. Create cost baseline
  await db
    .insertInto("ProductionOrderCostBaseline")
    .values({
      productionOrderId: order.id,
      plannedMaterialCost: totalPlannedMaterialCost,
      plannedLaborCost: totalPlannedLaborCost,
      plannedMachineCost: totalPlannedMachineCost,
      plannedOverheadCost: totalPlannedOverheadCost,
      currencyCode: baselineCurrencyCode,
    })
    .returningAll()
    .executeTakeFirst();

  // 16. Create material requirements (bulk insert)
  if (materialRequirements.length > 0) {
    await db
      .insertInto("ProductionOrderMaterialRequirement")
      .values(
        materialRequirements.map((matReq) => ({
          productionOrderId: order.id,
          itemId: matReq.itemId,
          requiredQuantity: matReq.requiredQuantity,
          unitOfMeasure: matReq.unitOfMeasure,
          plannedUnitCost: matReq.plannedUnitCost,
        })),
      )
      .execute();
  }

  // 17. Create work orders from routing operations (bulk insert)
  if (operations.length > 0) {
    await db
      .insertInto("WorkOrder")
      .values(
        operations.map((op) => ({
          productionOrderId: order.id,
          routingOperationSequenceNumber: op.sequenceNumber,
          workCenterId: op.workCenterId,
          plannedQuantity: order.plannedQuantity,
          completedQuantity: 0,
          scrapQuantity: 0,
          actualSetupTime: 0,
          actualRunTime: 0,
          actualStartDate: null,
          pauseReason: null,
          executionNotes: null,
          status: "PENDING" as const,
        })),
      )
      .execute();
  }

  // 18. Create manufacturing cost summary in COLLECTING status
  await db
    .insertInto("ManufacturingCostSummary")
    .values({
      productionOrderId: order.id,
      plannedMaterialCost: totalPlannedMaterialCost,
      plannedLaborCost: totalPlannedLaborCost,
      plannedMachineCost: totalPlannedMachineCost,
      plannedOverheadCost: totalPlannedOverheadCost,
      actualMaterialCost: 0,
      actualLaborCost: 0,
      actualMachineCost: 0,
      actualOverheadCost: 0,
      currencyCode: baselineCurrencyCode,
      reviewedDate: null,
      reviewerNotes: null,
      status: "COLLECTING",
    })
    .returningAll()
    .executeTakeFirst();

  // 19. Update order status to RELEASED with selected BOM/routing IDs
  const releasedOrder = await db
    .updateTable("ProductionOrder")
    .set({
      selectedBomVersionId: bom.id,
      selectedRoutingRevisionId: routing.id,
      status: nextStatus,
    })
    .where("id", "=", id)
    .returningAll()
    .executeTakeFirstOrThrow();

  return ok({ productionOrder: releasedOrder });
}