import { eq, desc, inArray, sql, and, or, like } from "drizzle-orm";
import { drizzle } from "drizzle-orm/mysql2";
import type { ResultSetHeader } from "mysql2";
import { inspectPointcloudMetadata } from "./pointcloudMetadata.js";
import {
  users, InsertUser,
  vehicleSeries, InsertVehicleSeries, VehicleSeries,
  vehicleModels, InsertVehicleModel, VehicleModel,
  inspectionObjects, InsertInspectionObject, InspectionObject,
  modelCadFiles, InsertModelCadFile, ModelCadFile,
  modelCadInspectionObjects,
  modelPointcloudFiles, InsertModelPointcloudFile, ModelPointcloudFile,
  taskCadFiles, InsertTaskCadFile, TaskCadFile,
  inspectionTasks, InsertInspectionTask, InspectionTask,
  taskPointcloudFiles, InsertTaskPointcloudFile, TaskPointcloudFile,
  taskAnalysisResults, InsertTaskAnalysisResult, TaskAnalysisResult,
} from "../drizzle/schema";

let _db: ReturnType<typeof drizzle> | null = null;

const sleep = (ms: number) => new Promise(r => setTimeout(r, ms));

/** drizzle mysql2 insert resolves to mysql2 ResultSetHeader with insertId (not result[0].insertId). */
function getMysqlInsertId(result: unknown): number {
  const r = result as ResultSetHeader & { 0?: ResultSetHeader };
  if (typeof r.insertId === "number" && r.insertId > 0) return r.insertId;
  if (r[0] && typeof r[0].insertId === "number") return r[0].insertId;
  throw new Error("insertId missing from insert result");
}

/**
 * Clears the cached pool so the next getDb() creates a new connection.
 * Call after ECONNREFUSED / PROTOCOL_CONNECTION_LOST so retries can recover.
 */
export function invalidateDb() {
  _db = null;
}

/**
 * Returns the drizzle DB instance, creating it if needed.
 * Retries the initial TCP probe while MySQL is still starting (common in Docker).
 */
export async function getDb() {
  if (_db) return _db;
  if (!process.env.DATABASE_URL) return null;

  const maxAttempts = 40;
  const delayMs = 500;
  // One drizzle instance / pool; retry execute only (avoids leaking a pool per attempt).
  const instance = drizzle(process.env.DATABASE_URL);

  for (let attempt = 1; attempt <= maxAttempts; attempt++) {
    try {
      await instance.execute(sql`SELECT 1`);
      _db = instance;
      if (attempt > 1) {
        console.log(`[Database] Connected after ${attempt} attempt(s).`);
      }
      return _db;
    } catch (error: any) {
      const msg = error?.message ?? String(error);
      const retriable =
        error?.code === "ECONNREFUSED" ||
        error?.code === "ETIMEDOUT" ||
        error?.code === "ENOTFOUND" ||
        msg.includes("connect");

      if (attempt < maxAttempts && retriable) {
        if (attempt === 1 || attempt % 5 === 0) {
          console.warn(`[Database] Waiting for MySQL... (${attempt}/${maxAttempts}) ${msg}`);
        }
        await sleep(delayMs);
        continue;
      }
      console.error("[Database] Giving up:", msg);
      _db = null;
      return null;
    }
  }
  return null;
}

// ─── 用户 ─────────────────────────────────────────────────────────────────────

export async function getUserById(id: number) {
  const db = await getDb();
  if (!db) return undefined;
  const result = await db.select().from(users).where(eq(users.id, id)).limit(1);
  return result[0];
}

export async function getUserByUsername(username: string) {
  const db = await getDb();
  if (!db) return undefined;
  const result = await db.select().from(users).where(eq(users.username, username)).limit(1);
  return result[0];
}

export async function listUsers(search?: string) {
  const db = await getDb();
  if (!db) return [];
  const term = search?.trim();
  if (!term) return db.select().from(users).orderBy(desc(users.createdAt));
  const pattern = `%${term.replace(/[\\%_]/g, "\\$&")}%`;
  return db.select().from(users)
    .where(or(like(users.username, pattern), like(users.name, pattern), like(users.email, pattern)))
    .orderBy(desc(users.createdAt));
}

export async function createUser(data: InsertUser) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  const result = await db.insert(users).values(data);
  return getUserById(getMysqlInsertId(result));
}

export async function updateUser(id: number, data: Partial<InsertUser>) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  await db.update(users).set(data).where(eq(users.id, id));
  return getUserById(id);
}

export async function incrementUserSessionVersion(id: number) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  await db.update(users)
    .set({ sessionVersion: sql`${users.sessionVersion} + 1` })
    .where(eq(users.id, id));
}

export async function deleteUser(id: number) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  await db.delete(users).where(eq(users.id, id));
}

export async function countActiveSuperAdmins() {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  const [row] = await db.select({ count: sql<number>`count(*)` }).from(users)
    .where(and(eq(users.role, "super_admin"), eq(users.isActive, 1)));
  return Number(row?.count ?? 0);
}

// ─── 车系 ─────────────────────────────────────────────────────────────────────

export async function createVehicleSeries(data: InsertVehicleSeries): Promise<VehicleSeries> {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  const result = await db.insert(vehicleSeries).values(data);
  const [row] = await db.select().from(vehicleSeries).where(eq(vehicleSeries.id, getMysqlInsertId(result)));
  return row;
}

export async function listVehicleSeries(): Promise<VehicleSeries[]> {
  const db = await getDb();
  if (!db) return [];
  return db.select().from(vehicleSeries).orderBy(desc(vehicleSeries.createdAt));
}

export async function updateVehicleSeries(id: number, data: Partial<InsertVehicleSeries>): Promise<void> {
  const db = await getDb();
  if (!db) return;
  await db.update(vehicleSeries).set(data).where(eq(vehicleSeries.id, id));
}

export async function deleteVehicleSeries(id: number): Promise<void> {
  const db = await getDb();
  if (!db) return;
  await db.delete(vehicleSeries).where(eq(vehicleSeries.id, id));
}

// ─── 车型 ─────────────────────────────────────────────────────────────────────

export async function createVehicleModel(data: InsertVehicleModel): Promise<VehicleModel> {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  const result = await db.insert(vehicleModels).values(data);
  const [row] = await db.select().from(vehicleModels).where(eq(vehicleModels.id, getMysqlInsertId(result)));
  return row;
}

export async function listVehicleModelsBySeries(seriesId: number): Promise<VehicleModel[]> {
  const db = await getDb();
  if (!db) return [];
  return db.select().from(vehicleModels)
    .where(eq(vehicleModels.seriesId, seriesId))
    .orderBy(desc(vehicleModels.createdAt));
}

export async function getVehicleModelById(id: number): Promise<VehicleModel | undefined> {
  const db = await getDb();
  if (!db) return undefined;
  const [row] = await db.select().from(vehicleModels).where(eq(vehicleModels.id, id));
  return row;
}

export async function updateVehicleModel(id: number, data: Partial<InsertVehicleModel>): Promise<void> {
  const db = await getDb();
  if (!db) return;
  await db.update(vehicleModels).set(data).where(eq(vehicleModels.id, id));
}

export async function deleteVehicleModel(id: number): Promise<void> {
  const db = await getDb();
  if (!db) return;
  await db.delete(vehicleModels).where(eq(vehicleModels.id, id));
}

// ─── 检测对象与默认输入方案 ──────────────────────────────────────────────────

export type InspectionObjectWithConfiguration = InspectionObject & {
  defaultPointcloudName: string | null;
  defaultCadName: string | null;
  configurationState: "missing_pointcloud" | "missing_cad" | "ready_draft" | "active";
  taskReferenceCount: number;
  pointcloudReferenceCount: number;
  cadReferenceCount: number;
  canDelete: boolean;
};

export type InspectionObjectReferenceCounts = {
  taskReferenceCount: number;
  pointcloudReferenceCount: number;
  cadReferenceCount: number;
};

export class InspectionObjectReferencedError extends Error {
  constructor(public readonly references: InspectionObjectReferenceCounts) {
    const parts = [
      references.taskReferenceCount > 0 ? `任务 ${references.taskReferenceCount} 个` : null,
      references.pointcloudReferenceCount > 0 ? `点云 ${references.pointcloudReferenceCount} 个` : null,
      references.cadReferenceCount > 0 ? `CAD ${references.cadReferenceCount} 个` : null,
    ].filter(Boolean);
    super(`检测对象仍被以下数据引用：${parts.join("、")}。请先处理关联数据后再删除。`);
    this.name = "InspectionObjectReferencedError";
  }
}

export function assertInspectionObjectDeletable(references: InspectionObjectReferenceCounts): void {
  if (references.taskReferenceCount + references.pointcloudReferenceCount + references.cadReferenceCount > 0) {
    throw new InspectionObjectReferencedError(references);
  }
}

export async function listInspectionObjects(modelId: number, activeOnly = false): Promise<InspectionObjectWithConfiguration[]> {
  const db = await getDb();
  if (!db) return [];
  const conditions = [eq(inspectionObjects.modelId, modelId)];
  if (activeOnly) conditions.push(eq(inspectionObjects.status, "active"));
  const rows = await db.select().from(inspectionObjects).where(and(...conditions)).orderBy(inspectionObjects.createdAt);
  if (rows.length === 0) return [];
  const objectIds = rows.map(row => row.id);
  const pointclouds = await db.select().from(modelPointcloudFiles).where(eq(modelPointcloudFiles.modelId, modelId));
  const cadFiles = await db.select().from(modelCadFiles).where(eq(modelCadFiles.modelId, modelId));
  const taskReferences = await db.select({
    inspectionObjectId: inspectionTasks.inspectionObjectId,
    count: sql<number>`count(*)`,
  }).from(inspectionTasks).where(inArray(inspectionTasks.inspectionObjectId, objectIds)).groupBy(inspectionTasks.inspectionObjectId);
  const cadReferences = await db.select({
    inspectionObjectId: modelCadInspectionObjects.inspectionObjectId,
    count: sql<number>`count(*)`,
  }).from(modelCadInspectionObjects).where(inArray(modelCadInspectionObjects.inspectionObjectId, objectIds)).groupBy(modelCadInspectionObjects.inspectionObjectId);
  const pcById = new Map(pointclouds.map(row => [row.id, row]));
  const cadById = new Map(cadFiles.map(row => [row.id, row]));
  const taskCountByObjectId = new Map(taskReferences.map(row => [row.inspectionObjectId, Number(row.count)]));
  const cadCountByObjectId = new Map(cadReferences.map(row => [row.inspectionObjectId, Number(row.count)]));
  const pointcloudCountByObjectId = new Map<number, number>();
  for (const pointcloud of pointclouds) {
    if (pointcloud.inspectionObjectId != null) {
      pointcloudCountByObjectId.set(pointcloud.inspectionObjectId, (pointcloudCountByObjectId.get(pointcloud.inspectionObjectId) ?? 0) + 1);
    }
  }
  return rows.map(row => {
    const pc = row.defaultModelPointcloudId ? pcById.get(row.defaultModelPointcloudId) : undefined;
    const cad = row.defaultModelCadFileId ? cadById.get(row.defaultModelCadFileId) : undefined;
    const hasPc = pc?.inspectionObjectId === row.id && pc.status === "available";
    const hasCad = cad?.status === "available";
    const taskReferenceCount = taskCountByObjectId.get(row.id) ?? 0;
    const pointcloudReferenceCount = pointcloudCountByObjectId.get(row.id) ?? 0;
    const cadReferenceCount = cadCountByObjectId.get(row.id) ?? 0;
    return {
      ...row,
      defaultPointcloudName: pc?.name ?? null,
      defaultCadName: cad?.name ?? null,
      configurationState: !hasPc ? "missing_pointcloud" : !hasCad ? "missing_cad" : row.status === "active" ? "active" : "ready_draft",
      taskReferenceCount,
      pointcloudReferenceCount,
      cadReferenceCount,
      canDelete: taskReferenceCount + pointcloudReferenceCount + cadReferenceCount === 0,
    };
  });
}

export async function getInspectionObjectById(id: number): Promise<InspectionObject | undefined> {
  const db = await getDb();
  if (!db) return undefined;
  const [row] = await db.select().from(inspectionObjects).where(eq(inspectionObjects.id, id));
  return row;
}

export async function createInspectionObject(data: InsertInspectionObject): Promise<InspectionObject> {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  const result = await db.insert(inspectionObjects).values(data);
  return (await getInspectionObjectById(getMysqlInsertId(result)))!;
}

export async function updateInspectionObject(id: number, data: Partial<InsertInspectionObject>): Promise<InspectionObject> {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  await db.update(inspectionObjects).set(data).where(eq(inspectionObjects.id, id));
  return (await getInspectionObjectById(id))!;
}

export async function deleteInspectionObject(id: number): Promise<void> {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  await db.transaction(async tx => {
    const [object] = await tx.select({ id: inspectionObjects.id }).from(inspectionObjects)
      .where(eq(inspectionObjects.id, id)).limit(1).for("update");
    if (!object) throw new Error("检测对象不存在");

    const taskRows = await tx.select({ id: inspectionTasks.id }).from(inspectionTasks)
      .where(eq(inspectionTasks.inspectionObjectId, id)).for("update");
    const pointcloudRows = await tx.select({ id: modelPointcloudFiles.id }).from(modelPointcloudFiles)
      .where(eq(modelPointcloudFiles.inspectionObjectId, id)).for("update");
    const cadRows = await tx.select({ cadFileId: modelCadInspectionObjects.cadFileId }).from(modelCadInspectionObjects)
      .where(eq(modelCadInspectionObjects.inspectionObjectId, id)).for("update");
    assertInspectionObjectDeletable({
      taskReferenceCount: taskRows.length,
      pointcloudReferenceCount: pointcloudRows.length,
      cadReferenceCount: cadRows.length,
    });
    await tx.delete(inspectionObjects).where(eq(inspectionObjects.id, id));
  });
}

export async function cadAppliesToInspectionObject(cadFileId: number, inspectionObjectId: number): Promise<boolean> {
  const db = await getDb();
  if (!db) return false;
  const [row] = await db.select().from(modelCadInspectionObjects).where(and(
    eq(modelCadInspectionObjects.cadFileId, cadFileId),
    eq(modelCadInspectionObjects.inspectionObjectId, inspectionObjectId),
  )).limit(1);
  return Boolean(row);
}

export async function setCadInspectionObjects(cadFileId: number, inspectionObjectIds: number[]): Promise<void> {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  await db.transaction(async tx => {
    const lockedObjects = await tx.select({ id: inspectionObjects.id }).from(inspectionObjects)
      .where(inArray(inspectionObjects.id, inspectionObjectIds)).for("share");
    if (lockedObjects.length !== new Set(inspectionObjectIds).size) throw new Error("Inspection object not found");
    await tx.delete(modelCadInspectionObjects).where(eq(modelCadInspectionObjects.cadFileId, cadFileId));
    if (inspectionObjectIds.length > 0) {
      await tx.insert(modelCadInspectionObjects).values(inspectionObjectIds.map(inspectionObjectId => ({ cadFileId, inspectionObjectId })));
    }
  });
}

export async function listCadInspectionObjectIds(cadFileIds: number[]): Promise<Record<number, number[]>> {
  const db = await getDb();
  if (!db || cadFileIds.length === 0) return {};
  const rows = await db.select().from(modelCadInspectionObjects).where(inArray(modelCadInspectionObjects.cadFileId, cadFileIds));
  return rows.reduce<Record<number, number[]>>((result, row) => {
    (result[row.cadFileId] ??= []).push(row.inspectionObjectId);
    return result;
  }, {});
}

export async function configureInspectionObject(input: {
  id: number;
  defaultModelPointcloudId: number;
  defaultModelCadFileId: number;
  active: boolean;
}): Promise<InspectionObject> {
  const object = await getInspectionObjectById(input.id);
  const pc = await getModelPointcloudFileById(input.defaultModelPointcloudId);
  const cad = await getModelCadFileById(input.defaultModelCadFileId);
  if (!object || !pc || !cad) throw new Error("Configuration resource not found");
  if (pc.modelId !== object.modelId || pc.inspectionObjectId !== object.id || pc.status !== "available") {
    throw new Error("Pointcloud is incompatible with inspection object");
  }
  if (cad.modelId !== object.modelId || cad.status !== "available" || !await cadAppliesToInspectionObject(cad.id, object.id)) {
    throw new Error("CAD is incompatible with inspection object");
  }
  return updateInspectionObject(object.id, {
    defaultModelPointcloudId: pc.id,
    defaultModelCadFileId: cad.id,
    status: input.active ? "active" : "draft",
  });
}

// ─── 车型 CAD 文件 ─────────────────────────────────────────────────────────────

export async function createModelCadFile(data: InsertModelCadFile): Promise<ModelCadFile> {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  const result = await db.insert(modelCadFiles).values(data);
  const [row] = await db.select().from(modelCadFiles).where(eq(modelCadFiles.id, getMysqlInsertId(result)));
  return row;
}

export async function listModelCadFiles(modelId: number): Promise<ModelCadFile[]> {
  const db = await getDb();
  if (!db) return [];
  return db.select().from(modelCadFiles)
    .where(eq(modelCadFiles.modelId, modelId))
    .orderBy(desc(modelCadFiles.createdAt));
}

export async function listModelCadFilesForInspectionObject(modelId: number, inspectionObjectId: number): Promise<ModelCadFile[]> {
  const db = await getDb();
  if (!db) return [];
  return db.select({
    id: modelCadFiles.id, modelId: modelCadFiles.modelId, name: modelCadFiles.name,
    format: modelCadFiles.format, fileSize: modelCadFiles.fileSize, filePath: modelCadFiles.filePath,
    contentScope: modelCadFiles.contentScope, status: modelCadFiles.status, createdAt: modelCadFiles.createdAt,
  }).from(modelCadFiles).innerJoin(modelCadInspectionObjects, eq(modelCadInspectionObjects.cadFileId, modelCadFiles.id))
    .where(and(eq(modelCadFiles.modelId, modelId), eq(modelCadFiles.status, "available"), eq(modelCadInspectionObjects.inspectionObjectId, inspectionObjectId)))
    .orderBy(desc(modelCadFiles.createdAt));
}

export async function getModelCadFileById(id: number): Promise<ModelCadFile | undefined> {
  const db = await getDb();
  if (!db) return undefined;
  const [row] = await db.select().from(modelCadFiles).where(eq(modelCadFiles.id, id));
  return row;
}

export async function deleteModelCadFile(id: number): Promise<void> {
  const db = await getDb();
  if (!db) return;
  const [defaultUse] = await db.select({ id: inspectionObjects.id }).from(inspectionObjects)
    .where(eq(inspectionObjects.defaultModelCadFileId, id)).limit(1);
  if (defaultUse) throw new Error("Resource is used as default input");
  const [historicalUse] = await db.select({ id: taskCadFiles.id }).from(taskCadFiles)
    .where(eq(taskCadFiles.sourceModelCadFileId, id)).limit(1);
  if (historicalUse) {
    await db.update(modelCadFiles).set({ status: "disabled" }).where(eq(modelCadFiles.id, id));
    return;
  }
  await db.delete(modelCadInspectionObjects).where(eq(modelCadInspectionObjects.cadFileId, id));
  await db.delete(modelCadFiles).where(eq(modelCadFiles.id, id));
}

// ─── 车型点云文件 ─────────────────────────────────────────────────────────────

export async function createModelPointcloudFile(data: InsertModelPointcloudFile): Promise<ModelPointcloudFile> {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  return db.transaction(async tx => {
    if (data.inspectionObjectId != null) {
      const [object] = await tx.select({ id: inspectionObjects.id }).from(inspectionObjects)
        .where(eq(inspectionObjects.id, data.inspectionObjectId)).limit(1).for("share");
      if (!object) throw new Error("Inspection object not found");
    }
    const result = await tx.insert(modelPointcloudFiles).values(data);
    const [row] = await tx.select().from(modelPointcloudFiles).where(eq(modelPointcloudFiles.id, getMysqlInsertId(result)));
    return row;
  });
}

const modelMetadataBackfills = new Map<number, Promise<ModelPointcloudFile>>();

async function backfillModelPointcloudMetadata(row: ModelPointcloudFile): Promise<ModelPointcloudFile> {
  if (row.metadataStatus !== "pending") return row;
  const existing = modelMetadataBackfills.get(row.id);
  if (existing) return existing;

  const backfill = (async () => {
    const db = await getDb();
    if (!db) return row;
    const metadata = inspectPointcloudMetadata(row.filePath, row.name);
    await db.update(modelPointcloudFiles)
      .set(metadata)
      .where(and(
        eq(modelPointcloudFiles.id, row.id),
        eq(modelPointcloudFiles.metadataStatus, "pending"),
      ));
    return { ...row, ...metadata };
  })();
  modelMetadataBackfills.set(row.id, backfill);
  try {
    return await backfill;
  } finally {
    modelMetadataBackfills.delete(row.id);
  }
}

export async function listModelPointcloudFiles(modelId: number): Promise<ModelPointcloudFile[]> {
  const db = await getDb();
  if (!db) return [];
  const rows = await db.select().from(modelPointcloudFiles)
    .where(eq(modelPointcloudFiles.modelId, modelId))
    .orderBy(desc(modelPointcloudFiles.createdAt));
  return Promise.all(rows.map(backfillModelPointcloudMetadata));
}

export async function listModelPointcloudFilesForInspectionObject(modelId: number, inspectionObjectId: number): Promise<ModelPointcloudFile[]> {
  const rows = await listModelPointcloudFiles(modelId);
  return rows.filter(row => row.inspectionObjectId === inspectionObjectId && row.status === "available");
}

export async function assignModelPointcloudInspectionObject(id: number, inspectionObjectId: number): Promise<ModelPointcloudFile> {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  return db.transaction(async tx => {
    const [object] = await tx.select().from(inspectionObjects)
      .where(eq(inspectionObjects.id, inspectionObjectId)).limit(1).for("share");
    const [file] = await tx.select().from(modelPointcloudFiles)
      .where(eq(modelPointcloudFiles.id, id)).limit(1).for("update");
    if (!file || !object || file.modelId !== object.modelId) throw new Error("Inspection object does not belong to file model");
    await tx.update(modelPointcloudFiles).set({ inspectionObjectId }).where(eq(modelPointcloudFiles.id, id));
    const [updated] = await tx.select().from(modelPointcloudFiles).where(eq(modelPointcloudFiles.id, id));
    return updated;
  });
}

export async function getModelPointcloudFileById(id: number): Promise<ModelPointcloudFile | undefined> {
  const db = await getDb();
  if (!db) return undefined;
  const [row] = await db.select().from(modelPointcloudFiles).where(eq(modelPointcloudFiles.id, id));
  return row;
}

export async function findModelPointcloudFileByModelAndName(
  modelId: number,
  name: string,
  excludeId?: number,
): Promise<ModelPointcloudFile | undefined> {
  const db = await getDb();
  if (!db) return undefined;
  const conditions = [
    eq(modelPointcloudFiles.modelId, modelId),
    sql`lower(${modelPointcloudFiles.name}) = lower(${name})`,
  ];
  if (excludeId !== undefined) {
    conditions.push(sql`${modelPointcloudFiles.id} <> ${excludeId}`);
  }
  const [row] = await db.select().from(modelPointcloudFiles).where(and(...conditions)).limit(1);
  return row;
}

export async function updateModelPointcloudFileName(
  id: number,
  name: string,
): Promise<ModelPointcloudFile> {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  await db.update(modelPointcloudFiles).set({ name }).where(eq(modelPointcloudFiles.id, id));
  const row = await getModelPointcloudFileById(id);
  if (!row) throw new Error("Model pointcloud file disappeared after update");
  return row;
}

export async function deleteModelPointcloudFile(id: number): Promise<void> {
  const db = await getDb();
  if (!db) return;
  const [defaultUse] = await db.select({ id: inspectionObjects.id }).from(inspectionObjects)
    .where(eq(inspectionObjects.defaultModelPointcloudId, id)).limit(1);
  if (defaultUse) throw new Error("Resource is used as default input");
  const [historicalUse] = await db.select({ id: taskPointcloudFiles.id }).from(taskPointcloudFiles)
    .where(eq(taskPointcloudFiles.sourceModelPointcloudId, id)).limit(1);
  if (historicalUse) {
    await db.update(modelPointcloudFiles).set({ status: "disabled" }).where(eq(modelPointcloudFiles.id, id));
    return;
  }
  await db.delete(modelPointcloudFiles).where(eq(modelPointcloudFiles.id, id));
}

// ─── 任务 CAD 文件 ─────────────────────────────────────────────────────────────

export async function createTaskCadFile(data: InsertTaskCadFile): Promise<TaskCadFile> {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  const result = await db.insert(taskCadFiles).values(data);
  const [row] = await db.select().from(taskCadFiles).where(eq(taskCadFiles.id, getMysqlInsertId(result)));
  return row;
}

export async function listTaskCadFiles(taskId: number): Promise<TaskCadFile[]> {
  const db = await getDb();
  if (!db) return [];
  return db.select().from(taskCadFiles)
    .where(eq(taskCadFiles.taskId, taskId))
    .orderBy(taskCadFiles.createdAt);
}

export async function getTaskCadFileById(id: number): Promise<TaskCadFile | undefined> {
  const db = await getDb();
  if (!db) return undefined;
  const [row] = await db.select().from(taskCadFiles).where(eq(taskCadFiles.id, id));
  return row;
}

export async function deleteTaskCadFile(id: number): Promise<void> {
  const db = await getDb();
  if (!db) return;
  await db.delete(taskCadFiles).where(eq(taskCadFiles.id, id));
}

export async function linkTaskCadFilesFromModel(taskId: number, modelCadFileIds: number[]): Promise<TaskCadFile[]> {
  const db = await getDb();
  if (!db) throw new Error("Database not available");

  const task = await getInspectionTaskById(taskId);
  if (!task) throw new Error("Task not found");

  const existing = await listTaskCadFiles(taskId);
  const existingSourceIds = new Set(
    existing
      .map((row) => row.sourceModelCadFileId)
      .filter((id): id is number => typeof id === "number"),
  );

  const inserted: TaskCadFile[] = [];
  for (const id of modelCadFileIds) {
    if (existingSourceIds.has(id)) continue;

    const source = await getModelCadFileById(id);
    if (!source) throw new Error("Model CAD file not found");
    if (source.modelId !== task.modelId) {
      throw new Error("Cannot link CAD file from another model");
    }
    if (!task.inspectionObjectId || source.status !== "available" || !await cadAppliesToInspectionObject(source.id, task.inspectionObjectId)) {
      throw new Error("Cannot link CAD file from another inspection object");
    }

    const record = await createTaskCadFile({
      taskId,
      name: source.name,
      format: source.format,
      fileSize: source.fileSize,
      filePath: source.filePath,
      source: "model_library",
      sourceModelCadFileId: source.id,
    } as InsertTaskCadFile);
    inserted.push(record);
    existingSourceIds.add(id);
  }

  return inserted;
}

// ─── 检测任务 ─────────────────────────────────────────────────────────────────

export async function createInspectionTask(data: InsertInspectionTask): Promise<InspectionTask> {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  const result = await db.insert(inspectionTasks).values(data);
  const [row] = await db.select().from(inspectionTasks).where(eq(inspectionTasks.id, getMysqlInsertId(result)));
  return row;
}

export async function createInspectionTaskWithDefaults(data: InsertInspectionTask & { inspectionObjectId: number }): Promise<{
  task: InspectionTask;
  pointcloudFile: TaskPointcloudFile;
  cadFile: TaskCadFile;
}> {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  return db.transaction(async tx => {
    const [object] = await tx.select().from(inspectionObjects)
      .where(eq(inspectionObjects.id, data.inspectionObjectId)).limit(1).for("share");
    if (!object || object.modelId !== data.modelId || object.status !== "active") throw new Error("Inspection object is not active for model");
    if (!object.defaultModelPointcloudId || !object.defaultModelCadFileId) throw new Error("Default input scheme is incomplete");
    const [pointcloud] = await tx.select().from(modelPointcloudFiles)
      .where(eq(modelPointcloudFiles.id, object.defaultModelPointcloudId)).limit(1);
    const [cad] = await tx.select().from(modelCadFiles)
      .where(eq(modelCadFiles.id, object.defaultModelCadFileId)).limit(1);
    const [cadApplication] = await tx.select({ cadFileId: modelCadInspectionObjects.cadFileId }).from(modelCadInspectionObjects).where(and(
      eq(modelCadInspectionObjects.cadFileId, object.defaultModelCadFileId),
      eq(modelCadInspectionObjects.inspectionObjectId, object.id),
    )).limit(1);
    if (!pointcloud || pointcloud.inspectionObjectId !== object.id || pointcloud.status !== "available") throw new Error("Default pointcloud is invalid");
    if (!cad || cad.modelId !== object.modelId || cad.status !== "available" || !cadApplication) throw new Error("Default CAD is invalid");

    const taskInsert = await tx.insert(inspectionTasks).values(data);
    const taskId = getMysqlInsertId(taskInsert);
    const pointcloudInsert = await tx.insert(taskPointcloudFiles).values({
      taskId, name: pointcloud.name, fileSize: pointcloud.fileSize, filePath: pointcloud.filePath,
      source: "model_library", sourceModelPointcloudId: pointcloud.id,
      metadataStatus: pointcloud.metadataStatus, pointCount: pointcloud.pointCount,
      pointAttributes: pointcloud.pointAttributes, pointDataFormat: pointcloud.pointDataFormat,
      bytesPerPoint: pointcloud.bytesPerPoint, metadataError: pointcloud.metadataError,
    });
    const cadInsert = await tx.insert(taskCadFiles).values({
      taskId, name: cad.name, format: cad.format, fileSize: cad.fileSize, filePath: cad.filePath,
      source: "model_library", sourceModelCadFileId: cad.id,
    });
    const [task] = await tx.select().from(inspectionTasks).where(eq(inspectionTasks.id, taskId));
    const [pointcloudFile] = await tx.select().from(taskPointcloudFiles).where(eq(taskPointcloudFiles.id, getMysqlInsertId(pointcloudInsert)));
    const [cadFile] = await tx.select().from(taskCadFiles).where(eq(taskCadFiles.id, getMysqlInsertId(cadInsert)));
    return { task, pointcloudFile, cadFile };
  });
}

export async function findInspectionTaskByModelAndName(modelId: number, name: string): Promise<InspectionTask | undefined> {
  const db = await getDb();
  if (!db) return undefined;
  const [row] = await db.select().from(inspectionTasks)
    .where(and(eq(inspectionTasks.modelId, modelId), eq(inspectionTasks.name, name)))
    .limit(1);
  return row;
}

export async function listInspectionTasksByModel(modelId: number): Promise<InspectionTask[]> {
  const db = await getDb();
  if (!db) return [];
  const tasks = await db.select().from(inspectionTasks)
    .where(eq(inspectionTasks.modelId, modelId))
    .orderBy(desc(inspectionTasks.createdAt));
  return withComputedInspectionTaskStatuses(tasks);
}

export async function getInspectionTaskById(id: number): Promise<InspectionTask | undefined> {
  const db = await getDb();
  if (!db) return undefined;
  const [row] = await db.select().from(inspectionTasks).where(eq(inspectionTasks.id, id));
  if (!row) return undefined;
  const [task] = await withComputedInspectionTaskStatuses([row]);
  return task;
}

type InspectionTaskStatus = InspectionTask["status"];
type AnalysisStatus = TaskAnalysisResult["status"];

function summarizeInspectionTaskStatus(
  fallback: InspectionTaskStatus,
  analysisStatuses: AnalysisStatus[],
): InspectionTaskStatus {
  if (analysisStatuses.length === 0) return fallback;
  if (analysisStatuses.some(status => status === "processing" || status === "pending")) {
    return "processing";
  }
  if (analysisStatuses.some(status => status === "error")) return "error";
  if (analysisStatuses.some(status => status === "completed")) return "completed";
  return fallback;
}

async function withComputedInspectionTaskStatuses(tasks: InspectionTask[]): Promise<InspectionTask[]> {
  if (tasks.length === 0) return tasks;
  const db = await getDb();
  if (!db) return tasks;

  const rows = await db
    .select({
      taskId: taskAnalysisResults.taskId,
      status: taskAnalysisResults.status,
    })
    .from(taskAnalysisResults)
    .where(inArray(taskAnalysisResults.taskId, tasks.map(task => task.id)));

  const statusesByTask = new Map<number, AnalysisStatus[]>();
  for (const row of rows) {
    const statuses = statusesByTask.get(row.taskId) ?? [];
    statuses.push(row.status);
    statusesByTask.set(row.taskId, statuses);
  }

  return tasks.map(task => ({
    ...task,
    status: summarizeInspectionTaskStatus(task.status, statusesByTask.get(task.id) ?? []),
  }));
}

export async function updateInspectionTask(id: number, data: Partial<InsertInspectionTask>): Promise<void> {
  const db = await getDb();
  if (!db) return;
  await db.update(inspectionTasks).set(data).where(eq(inspectionTasks.id, id));
}

export async function classifyInspectionTask(id: number, inspectionObjectId: number): Promise<void> {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  await db.transaction(async tx => {
    const [object] = await tx.select({ id: inspectionObjects.id }).from(inspectionObjects)
      .where(eq(inspectionObjects.id, inspectionObjectId)).limit(1).for("share");
    if (!object) throw new Error("Inspection object not found");
    await tx.update(inspectionTasks).set({ inspectionObjectId }).where(eq(inspectionTasks.id, id));
  });
}

export async function deleteInspectionTask(id: number): Promise<void> {
  const db = await getDb();
  if (!db) return;
  await db.delete(taskAnalysisResults).where(eq(taskAnalysisResults.taskId, id));
  await db.delete(taskPointcloudFiles).where(eq(taskPointcloudFiles.taskId, id));
  await db.delete(taskCadFiles).where(eq(taskCadFiles.taskId, id));
  await db.delete(inspectionTasks).where(eq(inspectionTasks.id, id));
}

// ─── 任务点云文件 ──────────────────────────────────────────────────────────────

export async function createTaskPointcloudFile(data: InsertTaskPointcloudFile): Promise<TaskPointcloudFile> {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  const result = await db.insert(taskPointcloudFiles).values(data);
  const [row] = await db.select().from(taskPointcloudFiles).where(eq(taskPointcloudFiles.id, getMysqlInsertId(result)));
  return row;
}

/** 把配准算法产出的点云注册为任务点云（source='registered'）。
 *  filePath 必须是绝对路径，文件必须已存在于磁盘。
 */
export async function createRegisteredPointcloudFile(
  taskId: number,
  name: string,
  filePath: string,
  sourceResultId: number,
  sourcePointcloudFileId?: number | null,
): Promise<TaskPointcloudFile> {
  const fs = await import('node:fs');
  const fileSize = fs.existsSync(filePath) ? fs.statSync(filePath).size : 0;
  const metadata = inspectPointcloudMetadata(filePath, name);
  return createTaskPointcloudFile({
    taskId,
    name,
    filePath,
    fileSize,
    source: 'registered',
    sourceResultId,
    sourcePointcloudFileId: sourcePointcloudFileId ?? null,
    ...metadata,
  } as InsertTaskPointcloudFile);
}

const taskMetadataBackfills = new Map<number, Promise<TaskPointcloudFile>>();

async function backfillTaskPointcloudMetadata(row: TaskPointcloudFile): Promise<TaskPointcloudFile> {
  if (row.metadataStatus !== "pending") return row;
  const existing = taskMetadataBackfills.get(row.id);
  if (existing) return existing;

  const backfill = (async () => {
    const db = await getDb();
    if (!db) return row;
    const metadata = inspectPointcloudMetadata(row.filePath, row.name);
    await db.update(taskPointcloudFiles)
      .set(metadata)
      .where(and(
        eq(taskPointcloudFiles.id, row.id),
        eq(taskPointcloudFiles.metadataStatus, "pending"),
      ));
    return { ...row, ...metadata };
  })();
  taskMetadataBackfills.set(row.id, backfill);
  try {
    return await backfill;
  } finally {
    taskMetadataBackfills.delete(row.id);
  }
}

export async function listTaskPointcloudFiles(taskId: number): Promise<TaskPointcloudFile[]> {
  const db = await getDb();
  if (!db) return [];
  const rows = await db.select().from(taskPointcloudFiles)
    .where(eq(taskPointcloudFiles.taskId, taskId))
    .orderBy(taskPointcloudFiles.createdAt);
  return Promise.all(rows.map(backfillTaskPointcloudMetadata));
}

export async function getTaskPointcloudFileById(id: number): Promise<TaskPointcloudFile | undefined> {
  const db = await getDb();
  if (!db) return undefined;
  const [row] = await db.select().from(taskPointcloudFiles).where(eq(taskPointcloudFiles.id, id));
  return row;
}

export async function findTaskPointcloudFileByTaskAndName(
  taskId: number,
  name: string,
  excludeId?: number,
): Promise<TaskPointcloudFile | undefined> {
  const db = await getDb();
  if (!db) return undefined;
  const conditions = [
    eq(taskPointcloudFiles.taskId, taskId),
    sql`lower(${taskPointcloudFiles.name}) = lower(${name})`,
  ];
  if (excludeId !== undefined) {
    conditions.push(sql`${taskPointcloudFiles.id} <> ${excludeId}`);
  }
  const [row] = await db.select().from(taskPointcloudFiles).where(and(...conditions)).limit(1);
  return row;
}

export async function updateTaskPointcloudFileName(
  id: number,
  name: string,
): Promise<TaskPointcloudFile> {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  await db.update(taskPointcloudFiles).set({ name }).where(eq(taskPointcloudFiles.id, id));
  const row = await getTaskPointcloudFileById(id);
  if (!row) throw new Error("Task pointcloud file disappeared after update");
  return row;
}

export async function deleteTaskPointcloudFile(id: number): Promise<void> {
  const db = await getDb();
  if (!db) return;
  await db.delete(taskPointcloudFiles).where(eq(taskPointcloudFiles.id, id));
}

export async function linkTaskPointcloudFilesFromModel(
  taskId: number,
  modelPointcloudFileIds: number[],
): Promise<TaskPointcloudFile[]> {
  const db = await getDb();
  if (!db) throw new Error("Database not available");

  const task = await getInspectionTaskById(taskId);
  if (!task) throw new Error("Task not found");

  const existing = await listTaskPointcloudFiles(taskId);
  const existingSourceIds = new Set(
    existing
      .map((row) => row.sourceModelPointcloudId)
      .filter((id): id is number => typeof id === "number"),
  );

  const inserted: TaskPointcloudFile[] = [];
  for (const id of modelPointcloudFileIds) {
    if (existingSourceIds.has(id)) continue;

    const source = await getModelPointcloudFileById(id);
    if (!source) throw new Error("Model pointcloud file not found");
    if (source.modelId !== task.modelId) {
      throw new Error("Cannot link pointcloud file from another model");
    }
    if (!task.inspectionObjectId || source.inspectionObjectId !== task.inspectionObjectId || source.status !== "available") {
      throw new Error("Cannot link pointcloud file from another inspection object");
    }

    const record = await createTaskPointcloudFile({
      taskId,
      name: source.name,
      fileSize: source.fileSize,
      filePath: source.filePath,
      source: "model_library",
      sourceModelPointcloudId: source.id,
      metadataStatus: source.metadataStatus,
      pointCount: source.pointCount,
      pointAttributes: source.pointAttributes,
      pointDataFormat: source.pointDataFormat,
      bytesPerPoint: source.bytesPerPoint,
      metadataError: source.metadataError,
    } as InsertTaskPointcloudFile);
    inserted.push(record);
    existingSourceIds.add(id);
  }

  return inserted;
}

// ─── 任务分析结果 ──────────────────────────────────────────────────────────────

export async function createTaskAnalysisResult(data: InsertTaskAnalysisResult): Promise<TaskAnalysisResult> {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  const result = await db.insert(taskAnalysisResults).values(data);
  const [row] = await db.select().from(taskAnalysisResults).where(eq(taskAnalysisResults.id, getMysqlInsertId(result)));
  return row;
}

export async function listTaskAnalysisResults(taskId: number): Promise<TaskAnalysisResult[]> {
  const db = await getDb();
  if (!db) return [];
  return db.select().from(taskAnalysisResults)
    .where(eq(taskAnalysisResults.taskId, taskId))
    .orderBy(desc(taskAnalysisResults.createdAt));
}

export async function getTaskAnalysisResultById(id: number): Promise<TaskAnalysisResult | undefined> {
  const db = await getDb();
  if (!db) return undefined;
  const [row] = await db.select().from(taskAnalysisResults).where(eq(taskAnalysisResults.id, id));
  return row;
}

export async function updateTaskAnalysisResult(id: number, data: Partial<InsertTaskAnalysisResult>): Promise<void> {
  const db = await getDb();
  if (!db) return;
  await db.update(taskAnalysisResults).set(data).where(eq(taskAnalysisResults.id, id));
}

/**
 * 非破坏性局部更新 task_analysis_results.resultData。
 * 与 updateTaskAnalysisResult(resultData) 不同——后者会把整个 JSON 列覆写，
 * 适合「最终结果」；本函数把 patch 合并到现有 JSON 上，适合
 * 子进程频繁上报进度的场景（例如算法插件 progress / stage）。
 *
 * 实现策略：先读取一次再 set 写回；并发更新时按「最后写入者获胜」处理，
 * 对进度上报来说足够安全（覆盖丢失最多 1~2 个心跳）。
 */
export async function patchTaskAnalysisResultData(
  id: number,
  patch: Record<string, unknown>,
): Promise<void> {
  const db = await getDb();
  if (!db) return;
  const existing = await getTaskAnalysisResultById(id);
  if (!existing) return;
  const prev = (existing.resultData as Record<string, unknown> | null) ?? {};
  const merged: Record<string, unknown> = { ...prev };
  for (const [k, v] of Object.entries(patch)) {
    if (v === undefined) continue;
    merged[k] = v;
  }
  await db
    .update(taskAnalysisResults)
    .set({ resultData: merged })
    .where(eq(taskAnalysisResults.id, id));
}

export async function deleteTaskAnalysisResult(id: number): Promise<void> {
  const db = await getDb();
  if (!db) return;
  await db.delete(taskAnalysisResults).where(eq(taskAnalysisResults.id, id));
}

/**
 * 删除分析结果及其派生的已配准点云记录。
 * 文件产物由调用方先清理；这里只负责在同一个事务中提交数据库变化。
 */
export async function deleteTaskAnalysisResultWithDerivedPointclouds(id: number): Promise<void> {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  await db.transaction(async (tx) => {
    await tx.delete(taskPointcloudFiles).where(eq(taskPointcloudFiles.sourceResultId, id));
    await tx.delete(taskAnalysisResults).where(eq(taskAnalysisResults.id, id));
  });
}