import { EntityManager, In } from "typeorm";
import { SavePosMasterHistory } from "./../interfaces/OrgMapping";
import { AppDataSource } from "../database/data-source";
import { EmployeePosMaster } from "../entities/EmployeePosMaster";
import { EmployeeTempPosMaster } from "../entities/EmployeeTempPosMaster";
import { OrgRevision } from "../entities/OrgRevision";
import { PosMaster } from "../entities/PosMaster";
import { PosMasterEmployeeHistory } from "../entities/PosMasterEmployeeHistory";
import { PosMasterEmployeeTempHistory } from "../entities/PosMasterEmployeeTempHistory";
import { PosMasterHistory } from "../entities/PosMasterHistory";
import { Position } from "../entities/Position";
import { ProfileEducation } from "../entities/ProfileEducation";
import { RequestWithUser } from "../middlewares/user";
import { Profile } from "../entities/Profile";

/**
 * function สำหรับดึงตำแหน่งที่รักษาการแทน
 * ใช้กรณี positionSign ว่าง
 * - ถ้า posType = "อำนวยการ" หรือ "บริหาร" ใช้ posExecutiveName
 * - ถ้า posType อื่นๆ ใช้ positionName + posLevel
 */
export async function getPosMasterPositions(posMasterIds: string[]): Promise<Map<string, string>> {
  if (posMasterIds.length === 0) {
    return new Map();
  }

  const positionRepo = AppDataSource.getRepository(Position);

  // Query รอบที่ 1: หา position ที่มีคนครอง
  const positionsWithHolder = await positionRepo.find({
    where: {
      posMasterId: In(posMasterIds),
      positionIsSelected: true,
    },
    relations: ["posType", "posLevel", "posExecutive"],
  });

  // หา posMasterId ที่ยังไม่ได้ผลลัพธ์
  const foundMasterIds = new Set(positionsWithHolder.map((p) => p.posMasterId));
  const missingMasterIds = posMasterIds.filter((id) => !foundMasterIds.has(id));

  // Query รอบที่ 2: เฉพาะที่ขาด (กรณีไม่มีคนครอง)
  let positionsWithoutHolder: Position[] = [];
  if (missingMasterIds.length > 0) {
    positionsWithoutHolder = await positionRepo.find({
      where: {
        posMasterId: In(missingMasterIds),
      },
      order: { createdAt: "ASC" },
      relations: ["posType", "posLevel", "posExecutive"],
    });
  }

  // รวม positions และสร้าง Map
  const allPositions = [...positionsWithHolder, ...positionsWithoutHolder];
  const positionMap = new Map<string, string>();

  for (const pos of allPositions) {
    const posTypeName = pos.posType?.posTypeName || "";
    let positionText = "";

    if (posTypeName === "อำนวยการ" || posTypeName === "บริหาร") {
      positionText =
        pos.posExecutive?.posExecutiveName ||
        `${pos.positionName || ""}ระดับ${pos.posLevel?.posLevelName || ""}`.trim();
    } else {
      positionText = `${pos.positionName || ""}${pos.posLevel?.posLevelName || ""}`.trim();
    }

    positionMap.set(pos.posMasterId, positionText);
  }

  return positionMap;
}

export async function CreatePosMasterHistoryOfficer(
  posMasterId: string,
  request: RequestWithUser | null,
  type?: string | null,
  positionData?: { positionId?: string } | null,
  manager?: EntityManager,
): Promise<boolean> {
  const execute = async (transactionManager: EntityManager) => {
    const repoPosmaster = transactionManager.getRepository(PosMaster);
    const repoHistory = transactionManager.getRepository(PosMasterHistory);
    const repoOrgRevision = transactionManager.getRepository(OrgRevision);
    const repoPosition = transactionManager.getRepository(Position);
    const repoProfile = transactionManager.getRepository(Profile);

    const pm = await repoPosmaster.findOne({
      where: { id: posMasterId },
      relations: [
        "positions",
        "positions.posLevel",
        "positions.posType",
        "positions.posExecutive",
        "orgRoot",
        "orgChild1",
        "orgChild2",
        "orgChild3",
        "orgChild4",
        "current_holder",
        "next_holder",
      ],
    });

    if (!pm || !pm.ancestorDNA) {
      return;
    }

    const checkCurrentRevision = await repoOrgRevision.findOne({
      where: {
        id: pm.orgRevisionId,
        orgRevisionIsCurrent: true,
        orgRevisionIsDraft: false,
      },
    });
    const _null: any = null;
    const h = new PosMasterHistory();

    // query position โดยตรงจาก positionRepository
    let selectedPosition: Position | null = null;
    if (positionData?.positionId) {
      selectedPosition = await repoPosition.findOne({
        where: { id: positionData.positionId },
        relations: { posLevel: true, posType: true, posExecutive: true },
      });
    } else {
      // ใช้ logic เดิม หาจาก pm.positions ที่ positionIsSelected = true
      selectedPosition =
        pm.positions.length > 0
          ? pm.positions.find((p) => p.positionIsSelected === true) ?? null
          : null;
    }

    let position = selectedPosition?.positionName ?? _null;
    let posTypeName = selectedPosition?.posType?.posTypeName ?? _null;
    let posLevelName = selectedPosition?.posLevel?.posLevelName ?? _null;
    let posExecutiveName = selectedPosition?.posExecutive?.posExecutiveName ?? _null;
    if (pm.isSit && pm.current_holderId) {
      const profile = await repoProfile.findOne({
        where: { id: pm.current_holderId },
        relations: ["posType", "posLevel"],
      });
      position = profile?.position ?? _null;
      posTypeName = profile?.posType?.posTypeName ?? _null;
      posLevelName = profile?.posLevel?.posLevelName ?? _null;
      posExecutiveName = profile?.posExecutive ?? _null;
    }

    h.ancestorDNA = pm.ancestorDNA ? pm.ancestorDNA : _null;
    if (!type || type != "DELETE") {
      if (checkCurrentRevision) {
        h.prefix = pm.current_holder?.prefix || _null;
        h.firstName = pm.current_holder?.firstName || _null;
        h.lastName = pm.current_holder?.lastName || _null;
        h.profileId = pm.current_holder?.id || _null;
      } else {
        h.prefix = pm.next_holder?.prefix || _null;
        h.firstName = pm.next_holder?.firstName || _null;
        h.lastName = pm.next_holder?.lastName || _null;
      }
      h.position = position;
      h.posType = posTypeName;
      h.posLevel = posLevelName;
    }
    h.rootDnaId = pm.orgRoot?.ancestorDNA || _null;
    h.child1DnaId = pm.orgChild1?.ancestorDNA || _null;
    h.child2DnaId = pm.orgChild2?.ancestorDNA || _null;
    h.child3DnaId = pm.orgChild3?.ancestorDNA || _null;
    h.child4DnaId = pm.orgChild4?.ancestorDNA || _null;
    h.posMasterNoPrefix = pm.posMasterNoPrefix ?? _null;
    h.posMasterNo = pm.posMasterNo ?? _null;
    h.posMasterNoSuffix = pm.posMasterNoSuffix ?? _null;
    h.posExecutive = posExecutiveName;
    h.shortName =
      [
        pm.orgChild4?.orgChild4ShortName,
        pm.orgChild3?.orgChild3ShortName,
        pm.orgChild2?.orgChild2ShortName,
        pm.orgChild1?.orgChild1ShortName,
        pm.orgRoot?.orgRootShortName,
      ].find((s) => typeof s === "string" && s.trim().length > 0) ?? _null;
    const userId = request?.user?.sub ?? "";
    const userName = request?.user?.name ?? "system";
    h.createdUserId = userId;
    h.createdFullName = userName;
    h.lastUpdateUserId = userId;
    h.lastUpdateFullName = userName;
    h.createdAt = new Date();
    h.lastUpdatedAt = new Date();
    await repoHistory.save(h);
  };

  try {
    if (manager) {
      await execute(manager);
      return true;
    }

    await AppDataSource.transaction(async (transactionManager) => {
      await execute(transactionManager);
    });
    return true;
  } catch (err) {
    if (manager) {
      console.error("CreatePosMasterHistoryOfficer error (external transaction):", err);
      throw err;
    }
    console.error("CreatePosMasterHistoryOfficer transaction error:", err);
    return false;
  }
}

export async function CreatePosMasterHistoryEmployee(
  posMasterId: string,
  request: RequestWithUser | null,
  type?: string | null,
): Promise<boolean> {
  try {
    await AppDataSource.transaction(async (manager) => {
      const repoPosmaster = manager.getRepository(EmployeePosMaster);
      const repoHistory = manager.getRepository(PosMasterEmployeeHistory);

      const pm = await repoPosmaster.findOne({
        where: { id: posMasterId },
        relations: [
          "positions",
          "positions.posLevel",
          "positions.posType",
          // "positions.posExecutive",
          "orgRoot",
          "orgChild1",
          "orgChild2",
          "orgChild3",
          "orgChild4",
          "current_holder",
        ],
      });
      if (!pm) return false;
      if (!pm.ancestorDNA) return false;
      const _null: any = null;
      const h = new PosMasterEmployeeHistory();
      const selectedPosition =
        pm.positions.length > 0
          ? pm.positions.find((p) => p.positionIsSelected === true) ?? null
          : null;
      h.ancestorDNA = pm.ancestorDNA;
      if (!type || type != "DELETE") {
        h.profileEmployeeId = pm.current_holder?.id || _null;
        h.prefix = pm.current_holder?.prefix || _null;
        h.firstName = pm.current_holder?.firstName || _null;
        h.lastName = pm.current_holder?.lastName || _null;
        h.position = selectedPosition?.positionName ?? _null;
        h.posType = selectedPosition?.posType?.posTypeName ?? _null;
        h.posLevel = selectedPosition?.posLevel?.posLevelName ?? _null;
      }
      h.rootDnaId = pm.orgRoot?.ancestorDNA || _null;
      h.child1DnaId = pm.orgChild1?.ancestorDNA || _null;
      h.child2DnaId = pm.orgChild2?.ancestorDNA || _null;
      h.child3DnaId = pm.orgChild3?.ancestorDNA || _null;
      h.child4DnaId = pm.orgChild4?.ancestorDNA || _null;
      h.posMasterNoPrefix = pm.posMasterNoPrefix ?? _null;
      h.posMasterNo = pm.posMasterNo ?? _null;
      h.posMasterNoSuffix = pm.posMasterNoSuffix ?? _null;
      h.shortName =
        [
          pm.orgChild4?.orgChild4ShortName,
          pm.orgChild3?.orgChild3ShortName,
          pm.orgChild2?.orgChild2ShortName,
          pm.orgChild1?.orgChild1ShortName,
          pm.orgRoot?.orgRootShortName,
        ].find((s) => typeof s === "string" && s.trim().length > 0) ?? _null;
      const userId = request?.user?.sub ?? "";
      const userName = request?.user?.name ?? "system";
      h.createdUserId = userId;
      h.createdFullName = userName;
      h.lastUpdateUserId = userId;
      h.lastUpdateFullName = userName;
      h.createdAt = new Date();
      h.lastUpdatedAt = new Date();
      await repoHistory.save(h);
    });

    return true;
  } catch (err) {
    console.error("CreatePosMasterHistoryEmployee transaction error:", err);
    return false;
  }
}

export async function CreatePosMasterHistoryEmployeeTemp(
  posMasterId: string,
  request: RequestWithUser | null,
): Promise<boolean> {
  try {
    await AppDataSource.transaction(async (manager) => {
      const repoPosmaster = manager.getRepository(EmployeeTempPosMaster);
      const repoHistory = manager.getRepository(PosMasterEmployeeTempHistory);

      const pm = await repoPosmaster.findOne({
        where: { id: posMasterId },
        relations: [
          "positions",
          "positions.posLevel",
          "positions.posType",
          // "positions.posExecutive",
          "orgRoot",
          "orgChild1",
          "orgChild2",
          "orgChild3",
          "orgChild4",
          "current_holder",
        ],
      });

      if (!pm) return false;
      if (!pm.ancestorDNA) return false;
      const _null: any = null;
      const h = new PosMasterEmployeeTempHistory();
      const selectedPosition =
        pm.positions.length > 0
          ? pm.positions.find((p) => p.positionIsSelected === true) ?? null
          : null;
      h.ancestorDNA = pm.ancestorDNA;
      h.prefix = pm.current_holder?.prefix || _null;
      h.firstName = pm.current_holder?.firstName || _null;
      h.lastName = pm.current_holder?.lastName || _null;
      h.posMasterNoPrefix = pm.posMasterNoPrefix ?? _null;
      h.posMasterNo = pm.posMasterNo ?? _null;
      h.posMasterNoSuffix = pm.posMasterNoSuffix ?? _null;
      h.position = selectedPosition?.positionName ?? _null;
      h.posType = selectedPosition?.posType?.posTypeName ?? _null;
      h.posLevel = selectedPosition?.posLevel?.posLevelName ?? _null;
      h.shortName =
        [
          pm.orgChild4?.orgChild4ShortName,
          pm.orgChild3?.orgChild3ShortName,
          pm.orgChild2?.orgChild2ShortName,
          pm.orgChild1?.orgChild1ShortName,
          pm.orgRoot?.orgRootShortName,
        ].find((s) => typeof s === "string" && s.trim().length > 0) ?? _null;
      const userId = request?.user?.sub ?? "";
      const userName = request?.user?.name ?? "system";
      h.createdUserId = userId;
      h.createdFullName = userName;
      h.lastUpdateUserId = userId;
      h.lastUpdateFullName = userName;
      h.createdAt = new Date();
      h.lastUpdatedAt = new Date();
      await repoHistory.save(h);
    });

    return true;
  } catch (err) {
    console.error("CreatePosMasterHistoryEmployeeTemp transaction error:", err);
    return false;
  }
}

export async function getTopDegrees(educations: ProfileEducation[]): Promise<string> {
  // filter เฉพาะ isUse==true หรือ isEducation==true
  const filtered = educations.filter((e) => e.isUse === true || e.isEducation === true);
  // sort: isEducation==true ก่อน, ถ้าเท่ากัน sort ด้วย level น้อยสุด
  const sorted = filtered.sort((a, b) => {
    const aEdu = !!a.isEducation ? 0 : 1;
    const bEdu = !!b.isEducation ? 0 : 1;
    if (aEdu !== bEdu) return aEdu - bEdu;
    const aLevel = typeof a.level === "number" ? a.level : Number.MAX_SAFE_INTEGER;
    const bLevel = typeof b.level === "number" ? b.level : Number.MAX_SAFE_INTEGER;
    return aLevel - bLevel;
  });

  return sorted
    .map((e) => [e.degree, e.field].filter(Boolean).join(" "))
    .filter(Boolean)
    .join("\n");
}

export async function SavePosMasterHistoryOfficer(
  queryRunner: any,
  posMasterDnaId: string,
  profileId: string | null,
  pm: SavePosMasterHistory | null,
): Promise<boolean> {
  try {
    // Type workaround: entity columns are nullable but types don't reflect it
    const _null: any = null;
    const repoPosMasterHistory = queryRunner.manager.getRepository(PosMasterHistory);
    const pmh = await repoPosMasterHistory.findOne({
      where: {
        ancestorDNA: posMasterDnaId,
      },
      order: { createdAt: "DESC" },
    });

    // Check if we need to insert a new history record
    const shouldInsert = !pmh && profileId && pm;
    const profileChanged = pmh && pmh.profileId !== profileId;

    if (shouldInsert || profileChanged) {
      // insert new record
      const newPmh = new PosMasterHistory();
      newPmh.ancestorDNA = posMasterDnaId;
      newPmh.prefix = pm?.prefix ?? _null;
      newPmh.firstName = pm?.firstName ?? _null;
      newPmh.lastName = pm?.lastName ?? _null;
      newPmh.position = pm?.position ?? _null;
      newPmh.posType = pm?.posType ?? _null;
      newPmh.posLevel = pm?.posLevel ?? _null;
      newPmh.posExecutive = pm?.posExecutive ?? _null;
      newPmh.profileId = profileId ?? _null;
      newPmh.rootDnaId = pm?.rootDnaId ?? _null;
      newPmh.child1DnaId = pm?.child1DnaId ?? _null;
      newPmh.child2DnaId = pm?.child2DnaId ?? _null;
      newPmh.child3DnaId = pm?.child3DnaId ?? _null;
      newPmh.child4DnaId = pm?.child4DnaId ?? _null;
      newPmh.shortName = pm?.shortName ?? _null;
      newPmh.posMasterNoPrefix = pm?.posMasterNoPrefix ?? _null;
      newPmh.posMasterNo = pm?.posMasterNo ?? _null;
      newPmh.posMasterNoSuffix = pm?.posMasterNoSuffix ?? _null;
      // Add audit fields for data integrity
      newPmh.createdUserId = "system";
      newPmh.createdFullName = "system";
      newPmh.lastUpdateUserId = "system";
      newPmh.lastUpdateFullName = "system";
      newPmh.createdAt = new Date();
      newPmh.lastUpdatedAt = new Date();
      await queryRunner.manager.save(PosMasterHistory, newPmh);
      return true;
    }
    return true;
  } catch (err) {
    console.error("SavePosMasterHistoryOfficer error:", err);
    return false;
  }
}

export interface BatchPosMasterHistoryOperation {
  posMasterDnaId: string;
  profileId: string | null;
  pm: SavePosMasterHistory | null;
}

export async function BatchSavePosMasterHistoryOfficer(
  queryRunner: any,
  operations: BatchPosMasterHistoryOperation[],
): Promise<boolean> {
  if (operations.length === 0) return true;

  try {
    const repoPosMasterHistory = queryRunner.manager.getRepository(PosMasterHistory);
    const dnaIds = operations.map((op) => op.posMasterDnaId);

    // Fetch all existing history records in ONE query
    const existingHistory = await repoPosMasterHistory.find({
      where: { ancestorDNA: In(dnaIds) },
      order: { createdAt: "DESC" },
    });

    // Build lookup map
    const historyByDna = new Map<string, PosMasterHistory[]>();
    for (const h of existingHistory) {
      if (!historyByDna.has(h.ancestorDNA)) {
        historyByDna.set(h.ancestorDNA, []);
      }
      historyByDna.get(h.ancestorDNA)!.push(h);
    }

    // Process operations and collect new records
    const newRecords: PosMasterHistory[] = [];
    const _null: any = null;

    for (const op of operations) {
      const existing = historyByDna.get(op.posMasterDnaId)?.[0];
      const shouldInsert = !existing && op.profileId && op.pm;
      const profileChanged = existing && existing.profileId !== op.profileId;
      const positionChanged =
        existing &&
        (existing.position !== op.pm?.position ||
          existing.posType !== op.pm?.posType ||
          existing.posLevel !== op.pm?.posLevel ||
          existing.posExecutive !== op.pm?.posExecutive);

      // ถ้าไม่มี record เดิม หรือ profile เปลี่ยน หรือ position เปลี่ยน ให้สร้าง record ใหม่
      if (shouldInsert || profileChanged || positionChanged) {
        const newPmh = new PosMasterHistory();
        newPmh.ancestorDNA = op.posMasterDnaId;
        newPmh.prefix = op.pm?.prefix ?? _null;
        newPmh.firstName = op.pm?.firstName ?? _null;
        newPmh.lastName = op.pm?.lastName ?? _null;
        newPmh.position = op.pm?.position ?? _null;
        newPmh.posType = op.pm?.posType ?? _null;
        newPmh.posLevel = op.pm?.posLevel ?? _null;
        newPmh.posExecutive = op.pm?.posExecutive ?? _null;
        newPmh.profileId = op.profileId ?? _null;
        newPmh.rootDnaId = op.pm?.rootDnaId ?? _null;
        newPmh.child1DnaId = op.pm?.child1DnaId ?? _null;
        newPmh.child2DnaId = op.pm?.child2DnaId ?? _null;
        newPmh.child3DnaId = op.pm?.child3DnaId ?? _null;
        newPmh.child4DnaId = op.pm?.child4DnaId ?? _null;
        newPmh.shortName = op.pm?.shortName ?? _null;
        newPmh.posMasterNoPrefix = op.pm?.posMasterNoPrefix ?? _null;
        newPmh.posMasterNo = op.pm?.posMasterNo ?? _null;
        newPmh.posMasterNoSuffix = op.pm?.posMasterNoSuffix ?? _null;
        newPmh.createdUserId = "system";
        newPmh.createdFullName = "system";
        newPmh.lastUpdateUserId = "system";
        newPmh.lastUpdateFullName = "system";
        newPmh.createdAt = new Date();
        newPmh.lastUpdatedAt = new Date();
        newRecords.push(newPmh);
      }
    }

    // Batch insert all new records
    if (newRecords.length > 0) {
      await queryRunner.manager.save(PosMasterHistory, newRecords);
    }

    return true;
  } catch (err) {
    console.error("BatchSavePosMasterHistoryOfficer error:", err);
    return false;
  }
}

/**
 * อัพเดทประวัติคนครองตำแหน่งเมื่อมีการเปลี่ยนแปลงข้อมูล profile
 * เช่น เปลี่ยนชื่อ - นามสกุล
 * ใช้สำหรับบันทึกประวัติเมื่อ profile ที่ครองตำแหน่งมีการเปลี่ยนแปลง
 *
 * @param profileId ID ของ profile ที่ต้องการตรวจสอบ
 * @param request RequestWithUser สำหรับบันทึกข้อมูลผู้ดำเนินการ
 * @param type "OFFICER" สำหรับข้าราชการ | "EMPLOYEE" สำหรับลูกจ้างประจำ (default: "OFFICER")
 */
export async function updateHolderProfileHistory(
  profileId: string,
  request: RequestWithUser,
  type: "OFFICER" | "EMPLOYEE" = "OFFICER",
): Promise<void> {
  try {
    if (type === "OFFICER") {
      const posMasterRepo = AppDataSource.getRepository(PosMaster);
      const posMaster = await posMasterRepo.findOne({
        where: {
          current_holderId: profileId,
          orgRevision: {
            orgRevisionIsCurrent: true,
            orgRevisionIsDraft: false,
          },
        },
        relations: {
          orgRevision: true,
        },
      });

      if (posMaster) {
        await CreatePosMasterHistoryOfficer(posMaster.id, request);
      }
    } else if (type === "EMPLOYEE") {
      const empPosMasterRepo = AppDataSource.getRepository(EmployeePosMaster);
      const employeePosMaster = await empPosMasterRepo.findOne({
        where: {
          current_holderId: profileId,
          orgRevision: {
            orgRevisionIsCurrent: true,
            orgRevisionIsDraft: false,
          },
        },
        relations: {
          orgRevision: true,
        },
      });

      if (employeePosMaster) {
        await CreatePosMasterHistoryEmployee(employeePosMaster.id, request);
      }
    }
  } catch (error) {
    console.error("updateHolderProfileHistory error:", error);
    throw error;
  }
}
