import crypto from 'crypto'
import { Prisma } from '@prisma/client'
import { prisma } from '@/lib/prisma'

export const STANDARD_MODEL_WHITE_BACKGROUND_BARCODE_OPERATION = {
  UPLOAD: 'standard-model-white-background.by-barcode.upload',
  CLEANUP: 'standard-model-white-background.by-barcode.cleanup',
  UPLOAD_BY_SPU: 'standard-model-white-background.by-spu.upload',
} as const

export type BarcodeOperationType =
  typeof STANDARD_MODEL_WHITE_BACKGROUND_BARCODE_OPERATION[keyof typeof STANDARD_MODEL_WHITE_BACKGROUND_BARCODE_OPERATION]

export class ExternalWhiteBackgroundBarcodeError extends Error {
  status: number
  code: string

  constructor(status: number, code: string, message: string) {
    super(message)
    this.status = status
    this.code = code
  }
}

export type ResolvedStandardModelSkuByBarcode = {
  barcode: string
  materialId: string
  skuId: string
  skuName: string
  resolvedSpu: string
  resolvedSkuName: string
}

let supportTablesInitialized = false

async function executeRaw(sql: string) {
  try {
    await prisma.$executeRawUnsafe(sql)
  } catch (error) {
    console.error('[external-white-background-by-barcode] 执行 SQL 失败:', error)
  }
}

export async function ensureStandardModelWhiteBackgroundBarcodeSupportTables() {
  if (supportTablesInitialized) {
    return
  }

  await executeRaw(`
    CREATE TABLE IF NOT EXISTS "material_sku_barcodes" (
      "id" TEXT PRIMARY KEY,
      "sku_id" TEXT NOT NULL,
      "barcode" TEXT NOT NULL,
      "deleted_at" TIMESTAMP(3),
      "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
      "updated_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP
    )
  `)

  await executeRaw(`
    DO $$
    BEGIN
      IF NOT EXISTS (
        SELECT 1
        FROM pg_constraint
        WHERE conname = 'material_sku_barcodes_sku_id_fkey'
      ) THEN
        ALTER TABLE "material_sku_barcodes"
        ADD CONSTRAINT "material_sku_barcodes_sku_id_fkey"
        FOREIGN KEY ("sku_id") REFERENCES "material_skus"("id")
        ON DELETE CASCADE ON UPDATE CASCADE;
      END IF;
    END
    $$;
  `)

  await executeRaw(`
    CREATE UNIQUE INDEX IF NOT EXISTS "material_sku_barcodes_barcode_active_key"
    ON "material_sku_barcodes" ("barcode")
    WHERE "deleted_at" IS NULL
  `)
  await executeRaw(`CREATE INDEX IF NOT EXISTS "material_sku_barcodes_barcode_idx" ON "material_sku_barcodes" ("barcode")`)
  await executeRaw(`CREATE INDEX IF NOT EXISTS "material_sku_barcodes_sku_id_idx" ON "material_sku_barcodes" ("sku_id")`)
  await executeRaw(`CREATE INDEX IF NOT EXISTS "material_sku_barcodes_deleted_at_idx" ON "material_sku_barcodes" ("deleted_at")`)

  await executeRaw(`
    CREATE TABLE IF NOT EXISTS "external_api_idempotency_records" (
      "id" TEXT PRIMARY KEY,
      "api_key_id" TEXT NOT NULL,
      "operation_type" TEXT NOT NULL,
      "client_request_id" TEXT NOT NULL,
      "request_hash" TEXT NOT NULL,
      "status" TEXT NOT NULL,
      "response_status" INTEGER,
      "response_body" JSONB,
      "completed_at" TIMESTAMP(3),
      "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
      "updated_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP
    )
  `)

  await executeRaw(`
    DO $$
    BEGIN
      IF NOT EXISTS (
        SELECT 1
        FROM pg_constraint
        WHERE conname = 'external_api_idempotency_records_api_key_id_fkey'
      ) THEN
        ALTER TABLE "external_api_idempotency_records"
        ADD CONSTRAINT "external_api_idempotency_records_api_key_id_fkey"
        FOREIGN KEY ("api_key_id") REFERENCES "api_keys"("id")
        ON DELETE CASCADE ON UPDATE CASCADE;
      END IF;
    END
    $$;
  `)

  await executeRaw(`
    CREATE UNIQUE INDEX IF NOT EXISTS "external_api_idempotency_records_api_key_operation_client_key"
    ON "external_api_idempotency_records" ("api_key_id", "operation_type", "client_request_id")
  `)
  await executeRaw(`CREATE INDEX IF NOT EXISTS "external_api_idempotency_records_api_key_id_idx" ON "external_api_idempotency_records" ("api_key_id")`)
  await executeRaw(`CREATE INDEX IF NOT EXISTS "external_api_idempotency_records_operation_type_idx" ON "external_api_idempotency_records" ("operation_type")`)
  await executeRaw(`CREATE INDEX IF NOT EXISTS "external_api_idempotency_records_client_request_id_idx" ON "external_api_idempotency_records" ("client_request_id")`)
  await executeRaw(`CREATE INDEX IF NOT EXISTS "external_api_idempotency_records_status_idx" ON "external_api_idempotency_records" ("status")`)
  await executeRaw(`CREATE INDEX IF NOT EXISTS "external_api_idempotency_records_created_at_idx" ON "external_api_idempotency_records" ("created_at")`)

  supportTablesInitialized = true
}

export function normalizeExternalBarcode(value: unknown) {
  return String(value || '').trim()
}

export function stableHashPayload(payload: unknown) {
  return crypto
    .createHash('sha256')
    .update(JSON.stringify(sortPayload(payload)))
    .digest('hex')
}

function sortPayload(payload: unknown): unknown {
  if (Array.isArray(payload)) {
    return payload.map(sortPayload)
  }

  if (payload && typeof payload === 'object') {
    return Object.keys(payload as Record<string, unknown>)
      .sort()
      .reduce((acc, key) => {
        acc[key] = sortPayload((payload as Record<string, unknown>)[key])
        return acc
      }, {} as Record<string, unknown>)
  }

  return payload
}

export async function startExternalApiIdempotencyRecord(input: {
  apiKeyId: string
  operationType: BarcodeOperationType
  clientRequestId: string
  requestHash: string
}) {
  await ensureStandardModelWhiteBackgroundBarcodeSupportTables()

  if (!input.clientRequestId.trim()) {
    throw new ExternalWhiteBackgroundBarcodeError(400, 'INVALID_CLIENT_REQUEST_ID', 'clientRequestId 不能为空')
  }

  try {
    const result = await prisma.$transaction(
      async (tx: any) => {
        const existing = await tx.externalApiIdempotencyRecord.findUnique({
          where: {
            apiKeyId_operationType_clientRequestId: {
              apiKeyId: input.apiKeyId,
              operationType: input.operationType,
              clientRequestId: input.clientRequestId,
            },
          },
        })

        if (existing) {
          if (existing.requestHash !== input.requestHash) {
            throw new ExternalWhiteBackgroundBarcodeError(
              409,
              'IDEMPOTENCY_CONFLICT',
              '同一个 clientRequestId 已用于不同请求，请更换 clientRequestId',
            )
          }

          if (existing.status === 'succeeded' && existing.responseBody && existing.responseStatus) {
            return {
              mode: 'replay' as const,
              record: existing,
            }
          }

          if (existing.status === 'failed') {
            const record = await tx.externalApiIdempotencyRecord.update({
              where: { id: existing.id },
              data: {
                status: 'processing',
                responseStatus: null,
                responseBody: Prisma.JsonNull,
                completedAt: null,
              },
            })

            return {
              mode: 'created' as const,
              record,
            }
          }

          throw new ExternalWhiteBackgroundBarcodeError(
            409,
            'IDEMPOTENCY_CONFLICT',
            '同一个 clientRequestId 的请求仍在处理中，请稍后重试',
          )
        }

        const record = await tx.externalApiIdempotencyRecord.create({
          data: {
            apiKeyId: input.apiKeyId,
            operationType: input.operationType,
            clientRequestId: input.clientRequestId,
            requestHash: input.requestHash,
            status: 'processing',
          },
        })

        return {
          mode: 'created' as const,
          record,
        }
      },
      {
        isolationLevel: Prisma.TransactionIsolationLevel.Serializable,
      },
    )

    return result
  } catch (error: any) {
    if (error instanceof ExternalWhiteBackgroundBarcodeError) {
      throw error
    }

    if (error?.code === 'P2002') {
      const existing = await (prisma as any).externalApiIdempotencyRecord.findUnique({
        where: {
          apiKeyId_operationType_clientRequestId: {
            apiKeyId: input.apiKeyId,
            operationType: input.operationType,
            clientRequestId: input.clientRequestId,
          },
        },
      })

      if (existing?.requestHash !== input.requestHash) {
        throw new ExternalWhiteBackgroundBarcodeError(
          409,
          'IDEMPOTENCY_CONFLICT',
          '同一个 clientRequestId 已用于不同请求，请更换 clientRequestId',
        )
      }

      if (existing?.status === 'succeeded' && existing.responseBody && existing.responseStatus) {
        return {
          mode: 'replay' as const,
          record: existing,
        }
      }

      if (existing?.status === 'failed') {
        const record = await (prisma as any).externalApiIdempotencyRecord.update({
          where: { id: existing.id },
          data: {
            status: 'processing',
            responseStatus: null,
            responseBody: Prisma.JsonNull,
            completedAt: null,
          },
        })

        return {
          mode: 'created' as const,
          record,
        }
      }

      throw new ExternalWhiteBackgroundBarcodeError(
        409,
        'IDEMPOTENCY_CONFLICT',
        '同一个 clientRequestId 的请求仍在处理中，请稍后重试',
      )
    }

    throw error
  }
}

export async function completeExternalApiIdempotencyRecord(input: {
  recordId: string
  responseStatus: number
  responseBody: unknown
}) {
  await (prisma as any).externalApiIdempotencyRecord.update({
    where: { id: input.recordId },
    data: {
      status: 'succeeded',
      responseStatus: input.responseStatus,
      responseBody: input.responseBody,
      completedAt: new Date(),
    },
  })
}

export async function failExternalApiIdempotencyRecord(recordId: string) {
  try {
    await (prisma as any).externalApiIdempotencyRecord.update({
      where: { id: recordId },
      data: {
        status: 'failed',
      },
    })
  } catch (error) {
    console.error('[external-white-background-by-barcode] 更新幂等失败状态失败:', error)
  }
}

export async function resolveStandardModelSkuByBarcode(
  barcode: string,
): Promise<ResolvedStandardModelSkuByBarcode> {
  await ensureStandardModelWhiteBackgroundBarcodeSupportTables()

  const normalizedBarcode = normalizeExternalBarcode(barcode)
  if (!normalizedBarcode) {
    throw new ExternalWhiteBackgroundBarcodeError(400, 'INVALID_BARCODE', 'barcode 不能为空')
  }

  const matches = await (prisma as any).materialSkuBarcode.findMany({
    where: {
      barcode: normalizedBarcode,
      deletedAt: null,
    },
    select: {
      id: true,
      barcode: true,
      sku: {
        select: {
          id: true,
          skuName: true,
          deletedAt: true,
          materialId: true,
          material: {
            select: {
              id: true,
              productModel: true,
              materialType: true,
              deletedAt: true,
            },
          },
        },
      },
    },
    take: 3,
  })

  if (matches.length === 0) {
    throw new ExternalWhiteBackgroundBarcodeError(404, 'BARCODE_NOT_FOUND', '未找到对应的标准模型 SKU 条码绑定')
  }

  const validTargets = matches.filter((match: any) => match.sku)
  const activeSkuIds = Array.from(new Set(validTargets.map((match: any) => String(match.sku.id))))
  if (activeSkuIds.length > 1 || matches.length > 1) {
    throw new ExternalWhiteBackgroundBarcodeError(409, 'BARCODE_AMBIGUOUS', '该 barcode 绑定了多个 SKU，无法确定唯一标准模型 SKU')
  }

  const match = validTargets[0]
  const sku = match?.sku
  const material = sku?.material

  if (!sku) {
    throw new ExternalWhiteBackgroundBarcodeError(404, 'BARCODE_NOT_FOUND', '未找到对应的标准模型 SKU 条码绑定')
  }

  if (sku.deletedAt) {
    throw new ExternalWhiteBackgroundBarcodeError(410, 'SKU_DELETED', '该 barcode 绑定的 SKU 已被删除')
  }

  if (!material) {
    throw new ExternalWhiteBackgroundBarcodeError(
      404,
      'BARCODE_NOT_BOUND_TO_STANDARD_MODEL_SKU',
      '该 barcode 未绑定到有效标准模型素材',
    )
  }

  if (material.deletedAt) {
    throw new ExternalWhiteBackgroundBarcodeError(410, 'MATERIAL_DELETED', '该 barcode 绑定的素材已被删除')
  }

  if (material.materialType !== '标准模型') {
    throw new ExternalWhiteBackgroundBarcodeError(
      400,
      'BARCODE_NOT_BOUND_TO_STANDARD_MODEL_SKU',
      '该 barcode 绑定的不是标准模型 SKU',
    )
  }

  return {
    barcode: normalizedBarcode,
    materialId: sku.materialId,
    skuId: sku.id,
    skuName: sku.skuName,
    resolvedSpu: material.productModel,
    resolvedSkuName: sku.skuName,
  }
}