/**
 * robot-manager Master seed — 真实 v5 主数据（172 台机器人 + L3 业务交易）
 *
 * 数据源：backend/prisma/seeds/fixtures/robot-master-v5.json
 *   （由 prisma/seeds/fixtures/build-robot-master-fixture.mjs 从 Master_Metadata_v5.xlsx 派生）
 *
 * 产出（按数据分层）：
 *   L1 平台主数据（platform_master）
 *     - Customer 41 / Supplier 3 / Location 4
 *   L2 模块主数据（robot_manager）
 *     - RobotModel 9 / RobotSku 14
 *   L2 核心实体
 *     - RobotUnit 172（仅不变属性）+ RobotUnitSnapshot 1:1（当前状态）+ 起点 LifecycleEvent
 *   L3 业务交易（按 Excel 聚合派生）
 *     - PurchaseOrder 4（+ Lines 11）按 PO 字符串聚合
 *     - SalesOrder 32（+ Lines 92）按 SO 字符串聚合
 *     - DeliveryRequest 24（+ Fulfillments 38）按 SO + deliveryDate 聚合
 *
 * 与 robot-manager-v3-seed.ts 关系：
 *   - v3 demo seed：合成 30 台分布在各 stage（保留，stage 全覆盖 UI 验证）
 *   - master seed：真实 172 台，stage 分布偏 In Transit / Reserved / Delivered（真实数据验证）
 *
 * 幂等性：以 code/ffsn/poNo/soNo/deliveryNo 为唯一键 upsert。重跑安全。
 *
 * 运行（不进默认 db:seed 链）：
 *   cd backend
 *   npm run db:seed:robot-master
 */
import {
  PrismaClient,
  RobotLifecycleStage,
  RobotLifecycleEventType,
  RobotUsageType,
  CustomerType,
  SupplierType,
  LocationType,
  PurchaseOrderStatus,
  SalesOrderStatus,
  SalesContractStatus,
  SalesLineType,
  DeliveryRequestType,
  DeliveryRequestStatus,
} from '@prisma/client';
import { randomUUID } from 'node:crypto';
import * as fs from 'node:fs';
import * as path from 'node:path';

const prisma = new PrismaClient();

type Fixture = {
  meta: any;
  models: Array<{ code: string; name: string; brand?: string }>;
  skus: Array<{ code: string; name: string; modelCode: string; variant: string | null }>;
  customers: Array<{ code: string; name: string; type: string; countryCode: string; currencyCode: string }>;
  suppliers: Array<{ code: string; name: string; type: string; countryCode: string; currencyCode: string }>;
  locations: Array<{ code: string; name: string; type: string; countryCode: string }>;
  purchaseOrders: Array<{
    poNo: string; supplierCode: string; currencyCode: string;
    totalAmount: number; status: string; orderedAt: string | null;
    lines: Array<{ lineNo: number; skuCode: string; quantity: number; unitPrice: number; totalPrice: number }>;
  }>;
  salesOrders: Array<{
    soNo: string; customerCode: string; currencyCode: string;
    totalAmount: number; contractStatus: string; status: string;
    lines: Array<{ lineNo: number; ffsn: string; lineType: string; unitPrice: number; netAmount: number }>;
  }>;
  deliveryRequests: Array<{
    deliveryNo: string; soNo: string; customerCode: string;
    requestType: string; expectedDate: string; status: string;
    fulfillments: Array<{ ffsn: string; deliveredAt: string }>;
  }>;
  robots: Array<{
    ffsn: string; supplierSn: string | null; placeholderSnOrig: string | null;
    modelCode: string; skuCode: string; usageType: string;
    customerCode: string | null; supplierCode: string | null; locationCode: string | null;
    stage: string; lifecycleStatusRaw: string | null;
    purchaseDate: string | null; arrivalDate: string | null; deliveryDate: string | null;
    purchasePrice: number | null; salesPriceHw: number | null; salesPriceSw: number | null;
    poRaw: string | null; salesOrderIdRaw: string | null; deliveryNumber: string | null;
    specialist: string | null; issue: string | null; notes: string | null; recordStatus: string | null;
  }>;
};

const FIXTURE_PATH = path.join(__dirname, 'fixtures', 'robot-master-v5.json');

async function main() {
  const fixture: Fixture = JSON.parse(fs.readFileSync(FIXTURE_PATH, 'utf-8'));
  console.log(`[master-seed] fixture: ${fixture.robots.length} robots, ${fixture.purchaseOrders.length} POs, ${fixture.salesOrders.length} SOs, ${fixture.deliveryRequests.length} deliveries`);

  const itadmin = await prisma.user.findFirst({ where: { username: 'itadmin' } });
  if (!itadmin) throw new Error('itadmin 不存在，先跑 npm run init:itadmin');
  const userId = itadmin.id;

  const org = await prisma.organization.upsert({
    where: { code: 'TEST_ORG' },
    create: { code: 'TEST_ORG', name: 'FF Test Organization' },
    update: {},
  });
  const orgId = org.id;
  console.log(`[master-seed] organization: ${org.code}`);

  // ───── L1 主数据 ─────
  for (const c of fixture.customers) {
    await prisma.customer.upsert({
      where: { code: c.code },
      create: {
        code: c.code, name: c.name, type: c.type as CustomerType,
        countryCode: c.countryCode, currencyCode: c.currencyCode,
        organizationId: orgId, createdById: userId,
      },
      update: {},
    });
  }
  for (const s of fixture.suppliers) {
    await prisma.supplier.upsert({
      where: { code: s.code },
      create: {
        code: s.code, name: s.name, type: s.type as SupplierType,
        countryCode: s.countryCode, currencyCode: s.currencyCode,
        organizationId: orgId, createdById: userId,
      },
      update: {},
    });
  }
  for (const l of fixture.locations) {
    await prisma.location.upsert({
      where: { code: l.code },
      create: {
        code: l.code, name: l.name, type: l.type as LocationType,
        countryCode: l.countryCode,
        organizationId: orgId, createdById: userId,
      },
      update: {},
    });
  }
  console.log(`[master-seed] L1: customers ${fixture.customers.length}, suppliers ${fixture.suppliers.length}, locations ${fixture.locations.length}`);

  // ───── L2 模块主数据 ─────
  const modelByCode = new Map<string, string>();
  for (const m of fixture.models) {
    const row = await prisma.robotModel.upsert({
      where: { code: m.code },
      create: { code: m.code, name: m.name, brand: m.brand ?? 'EAI', organizationId: orgId, createdById: userId },
      update: {},
    });
    modelByCode.set(m.code, row.id);
  }
  const skuByCode = new Map<string, string>();
  for (const s of fixture.skus) {
    const modelId = modelByCode.get(s.modelCode);
    if (!modelId) throw new Error(`sku ${s.code}: model ${s.modelCode} not found`);
    const row = await prisma.robotSku.upsert({
      where: { code: s.code },
      create: {
        code: s.code, name: s.name, modelId,
        variant: s.variant ?? undefined, currencyCode: 'USD',
        organizationId: orgId, createdById: userId,
      },
      update: {},
    });
    skuByCode.set(s.code, row.id);
  }
  console.log(`[master-seed] L2 master: models ${fixture.models.length}, skus ${fixture.skus.length}`);

  // ───── lookup tables ─────
  const customerByCode = new Map<string, string>(
    (await prisma.customer.findMany({ where: { code: { in: fixture.customers.map((c) => c.code) } } }))
      .map((c) => [c.code, c.id]),
  );
  const supplierByCode = new Map<string, string>(
    (await prisma.supplier.findMany({ where: { code: { in: fixture.suppliers.map((s) => s.code) } } }))
      .map((s) => [s.code, s.id]),
  );
  const locationByCode = new Map<string, string>(
    (await prisma.location.findMany({ where: { code: { in: fixture.locations.map((l) => l.code) } } }))
      .map((l) => [l.code, l.id]),
  );

  // ───── L3 PurchaseOrders ─────
  // poNo → { id, lineByKey: Map(skuCode → lineId) }
  const poByNo = new Map<string, { id: string; lineBySkuCode: Map<string, string> }>();
  for (const po of fixture.purchaseOrders) {
    const supplierId = supplierByCode.get(po.supplierCode);
    if (!supplierId) {
      console.warn(`[master-seed] PO ${po.poNo}: supplier ${po.supplierCode} not found, skip`);
      continue;
    }
    const orderedAt = po.orderedAt ? new Date(po.orderedAt) : new Date();
    const row = await prisma.purchaseOrder.upsert({
      where: { poNo: po.poNo },
      create: {
        poNo: po.poNo,
        supplierId,
        currencyCode: po.currencyCode,
        totalAmount: po.totalAmount,
        status: po.status as PurchaseOrderStatus,
        orderedAt,
        organizationId: orgId,
        createdById: userId,
      },
      update: {},
      include: { lines: true },
    });

    const lineBySkuCode = new Map<string, string>();
    const existingLineSkus = new Set(row.lines.map((l) => l.skuId));
    for (const line of po.lines) {
      const skuId = skuByCode.get(line.skuCode);
      if (!skuId) continue;
      if (existingLineSkus.has(skuId)) {
        const existing = row.lines.find((l) => l.skuId === skuId)!;
        lineBySkuCode.set(line.skuCode, existing.id);
      } else {
        const created = await prisma.purchaseOrderLine.create({
          data: {
            purchaseOrderId: row.id,
            lineNo: line.lineNo,
            skuId,
            quantity: line.quantity,
            unitPrice: line.unitPrice,
            totalPrice: line.totalPrice,
            currencyCode: po.currencyCode,
            defaultUsageType: RobotUsageType.SALES,
          },
        });
        lineBySkuCode.set(line.skuCode, created.id);
      }
    }
    poByNo.set(po.poNo, { id: row.id, lineBySkuCode });
  }
  console.log(`[master-seed] L3: purchaseOrders ${fixture.purchaseOrders.length} upserted`);

  // ───── L3 SalesOrders（lines 在 robot 创建后回填 robotUnitId） ─────
  const soByNo = new Map<string, string>();
  for (const so of fixture.salesOrders) {
    const customerId = customerByCode.get(so.customerCode);
    if (!customerId) continue;
    const row = await prisma.salesOrder.upsert({
      where: { soNo: so.soNo },
      create: {
        soNo: so.soNo,
        customerId,
        currencyCode: so.currencyCode,
        totalAmount: so.totalAmount,
        contractStatus: so.contractStatus as SalesContractStatus,
        status: so.status as SalesOrderStatus,
        organizationId: orgId,
        createdById: userId,
      },
      update: {},
    });
    soByNo.set(so.soNo, row.id);
  }
  console.log(`[master-seed] L3: salesOrders ${fixture.salesOrders.length} upserted`);

  // ───── L2 RobotUnit + Snapshot + LifecycleEvent ─────
  // 拿 ffsn 集合，已存在的不重建只更新 L3 FKs
  const existingByFfsn = new Map<string, string>(
    (await prisma.robotUnit.findMany({
      where: { organizationId: orgId, ffsn: { in: fixture.robots.map((r) => r.ffsn) } },
      select: { id: true, ffsn: true },
    })).map((r) => [r.ffsn, r.id]),
  );

  const now = new Date();
  const newUnits: any[] = [];
  const newEvents: any[] = [];
  const newSnapshots: any[] = [];
  const updates: Array<{ unitId: string; data: { purchaseOrderId: string | null; purchaseOrderLineId: string | null } }> = [];
  const ffsnToUnitId = new Map<string, string>(existingByFfsn);

  for (const r of fixture.robots) {
    const modelId = modelByCode.get(r.modelCode);
    const skuId = skuByCode.get(r.skuCode);
    if (!modelId || !skuId) {
      console.warn(`[master-seed] skip ${r.ffsn}: missing model/sku`);
      continue;
    }
    const supplierId = r.supplierCode ? supplierByCode.get(r.supplierCode) ?? null : null;

    // 解析 L3 PO refs
    let purchaseOrderId: string | null = null;
    let purchaseOrderLineId: string | null = null;
    if (r.poRaw && poByNo.has(r.poRaw)) {
      const po = poByNo.get(r.poRaw)!;
      purchaseOrderId = po.id;
      purchaseOrderLineId = po.lineBySkuCode.get(r.skuCode) ?? null;
    }

    if (existingByFfsn.has(r.ffsn)) {
      const unitId = existingByFfsn.get(r.ffsn)!;
      updates.push({ unitId, data: { purchaseOrderId, purchaseOrderLineId } });
      continue;
    }

    const unitId = randomUUID();
    const eventId = randomUUID();
    ffsnToUnitId.set(r.ffsn, unitId);
    newUnits.push({
      id: unitId,
      organizationId: orgId,
      ffsn: r.ffsn,
      ffsnDisplay: r.ffsn,
      supplierSn: r.supplierSn,
      placeholderSnOrig: r.placeholderSnOrig,
      modelId,
      skuId,
      usageType: r.usageType as RobotUsageType,
      originalSupplierId: supplierId,
      purchaseOrderId,
      purchaseOrderLineId,
      manufactureDate: r.purchaseDate ? new Date(r.purchaseDate) : null,
      metadata: {
        importedFrom: 'Master_Metadata_v5',
        recordStatus: r.recordStatus,
        lifecycleStatusRaw: r.lifecycleStatusRaw,
        specialist: r.specialist,
        issue: r.issue,
        notes: r.notes,
      },
      createdById: userId,
    });
    newEvents.push({
      id: eventId,
      robotUnitId: unitId,
      eventType: RobotLifecycleEventType.imported_from_v5,
      fromStage: null,
      toStage: r.stage as RobotLifecycleStage,
      actorUserId: userId,
      notes: `imported from Master_Metadata_v5 (${r.lifecycleStatusRaw ?? 'unknown'})`,
      payload: {},
      occurredAt: now,
      organizationId: orgId,
      createdById: userId,
    });
    newSnapshots.push({
      robotUnitId: unitId,
      currentStage: r.stage as RobotLifecycleStage,
      currentLocationId: r.locationCode ? locationByCode.get(r.locationCode) ?? null : null,
      currentCustomerId: r.customerCode ? customerByCode.get(r.customerCode) ?? null : null,
      currentSalesOrderId: r.salesOrderIdRaw ? soByNo.get(r.salesOrderIdRaw) ?? null : null,
      lastEventId: eventId,
      lastEventAt: now,
      version: 1,
    });
  }

  if (newUnits.length) {
    await prisma.$transaction([
      prisma.robotUnit.createMany({ data: newUnits }),
      prisma.robotLifecycleEvent.createMany({ data: newEvents }),
      prisma.robotUnitSnapshot.createMany({ data: newSnapshots }),
    ]);
  }
  // 已存在 robot：补 L3 FKs + currentSalesOrderId（幂等）
  for (const u of updates) {
    await prisma.robotUnit.update({ where: { id: u.unitId }, data: u.data });
  }
  for (const r of fixture.robots) {
    if (!existingByFfsn.has(r.ffsn)) continue;
    const unitId = existingByFfsn.get(r.ffsn)!;
    await prisma.robotUnitSnapshot.update({
      where: { robotUnitId: unitId },
      data: {
        currentSalesOrderId: r.salesOrderIdRaw ? soByNo.get(r.salesOrderIdRaw) ?? null : null,
      },
    });
  }
  console.log(`[master-seed] L2: created ${newUnits.length} new units, updated L3 FKs on ${updates.length} existing units`);

  // ───── L3 SalesOrderLines（需 robotUnitId，所以在 robot 创建后） ─────
  let solCreated = 0, solExisting = 0;
  for (const so of fixture.salesOrders) {
    const salesOrderId = soByNo.get(so.soNo);
    if (!salesOrderId) continue;
    const existingLines = await prisma.salesOrderLine.findMany({
      where: { salesOrderId },
      select: { lineNo: true },
    });
    const existingLineNos = new Set(existingLines.map((l) => l.lineNo));
    for (const line of so.lines) {
      if (existingLineNos.has(line.lineNo)) {
        solExisting++;
        continue;
      }
      const robotUnitId = ffsnToUnitId.get(line.ffsn) ?? null;
      await prisma.salesOrderLine.create({
        data: {
          salesOrderId,
          lineNo: line.lineNo,
          robotUnitId,
          lineType: line.lineType as SalesLineType,
          unitPrice: line.unitPrice,
          netAmount: line.netAmount,
          currencyCode: so.currencyCode,
        },
      });
      solCreated++;
    }
  }
  console.log(`[master-seed] L3 SO lines: ${solCreated} created / ${solExisting} existing`);

  // ───── L3 DeliveryRequests + Fulfillments ─────
  // 先 upsert request；fulfillment 按 (deliveryRequestId, robotUnitId) 去重
  let drCreated = 0, drExisting = 0, dfCreated = 0, dfExisting = 0;
  for (const dr of fixture.deliveryRequests) {
    const salesOrderId = soByNo.get(dr.soNo);
    const customerId = customerByCode.get(dr.customerCode);
    if (!salesOrderId || !customerId) continue;
    const existing = await prisma.deliveryRequest.findUnique({ where: { deliveryNo: dr.deliveryNo } });
    let drId: string;
    if (existing) {
      drId = existing.id;
      drExisting++;
    } else {
      const row = await prisma.deliveryRequest.create({
        data: {
          deliveryNo: dr.deliveryNo,
          salesOrderId,
          customerId,
          requestType: dr.requestType as DeliveryRequestType,
          expectedDate: new Date(dr.expectedDate),
          status: dr.status as DeliveryRequestStatus,
          organizationId: orgId,
          createdById: userId,
        },
      });
      drId = row.id;
      drCreated++;
    }
    for (const f of dr.fulfillments) {
      const robotUnitId = ffsnToUnitId.get(f.ffsn);
      if (!robotUnitId) continue;
      const exists = await prisma.deliveryFulfillment.findFirst({
        where: { deliveryRequestId: drId, robotUnitId },
        select: { id: true },
      });
      if (exists) {
        dfExisting++;
        continue;
      }
      await prisma.deliveryFulfillment.create({
        data: {
          deliveryRequestId: drId,
          robotUnitId,
          deliveredAt: new Date(f.deliveredAt),
          organizationId: orgId,
          createdById: userId,
        },
      });
      dfCreated++;
      // 同步更新 snapshot
      await prisma.robotUnitSnapshot.update({
        where: { robotUnitId },
        data: { currentDeliveryRequestId: drId },
      });
    }
  }
  console.log(`[master-seed] L3 deliveries: requests ${drCreated} created / ${drExisting} existing; fulfillments ${dfCreated} created / ${dfExisting} existing`);

  // ───── 验证汇总 ─────
  const stageCount = await prisma.robotUnitSnapshot.groupBy({
    by: ['currentStage'],
    _count: true,
    where: {
      robotUnit: {
        organizationId: orgId,
        metadata: { path: ['importedFrom'], equals: 'Master_Metadata_v5' },
      },
    },
  });
  console.log('[master-seed] stage distribution (master-imported only):');
  for (const s of stageCount) console.log(`  ${s.currentStage}: ${s._count}`);

  console.log('✅ master seed done');
}

main()
  .catch((e) => {
    console.error(e);
    process.exit(1);
  })
  .finally(() => prisma.$disconnect());