import ExcelJS from "exceljs";
import { EmployeeRepository } from "../repositories/EmployeeRepository.js";
import { Prisma } from "../generated/prisma/client.js";

export interface EmployeeImportRowResult {
  row: number;
  name: string;
  status: "imported" | "skipped";
  reason?: string;
}

export class EmployeeService {
  constructor(private readonly employeeRepository = new EmployeeRepository()) {}

  async create(data: Prisma.EmployeeUncheckedCreateInput) {
    if (data.name) {
      const existingNameAndMobile = await this.employeeRepository.findByNameAndMobile(data.name, data.mobile);
      if (existingNameAndMobile) {
        throw new Error("Employee with this name and mobile number already exists");
      }
    }

    if (!data.employeeCode || data.employeeCode.trim() === "") {
      data.employeeCode = await this.employeeRepository.generateEmployeeCode();
    } else {
      const existing = await this.employeeRepository.findByCode(data.employeeCode);
      if (existing) {
        throw new Error(`Employee with code ${data.employeeCode} already exists`);
      }
    }
    return this.employeeRepository.create(data);
  }

  async findAll(skip?: number, take?: number, search?: string, status?: boolean) {
    return this.employeeRepository.findAll(skip, take, search, status);
  }

  async findById(id: bigint) {
    return this.employeeRepository.findById(id);
  }

  async update(id: bigint, data: Prisma.EmployeeUncheckedUpdateInput) {
    const existing = await this.employeeRepository.findById(id);
    if (!existing) {
      throw new Error("Employee not found");
    }

    const nameToCheck = data.name !== undefined ? (typeof data.name === "string" ? data.name : (data.name as any)?.set) : existing.name;
    const mobileToCheck = data.mobile !== undefined ? (typeof data.mobile === "string" ? data.mobile : (data.mobile as any)?.set) : existing.mobile;

    if (nameToCheck) {
      const duplicate = await this.employeeRepository.findByNameAndMobile(nameToCheck, mobileToCheck, id);
      if (duplicate) {
        throw new Error("Employee with this name and mobile number already exists");
      }
    }

    return this.employeeRepository.update(id, data);
  }

  async delete(id: bigint) {
    return this.employeeRepository.delete(id);
  }

  async generateImportTemplate(): Promise<Buffer> {
    const workbook = new ExcelJS.Workbook();
    workbook.creator = "AVS System";

    // ── Sheet 1: Data entry sheet ──
    const sheet = workbook.addWorksheet("Employees");
    sheet.columns = [
      { header: "Employee Name *", key: "name", width: 25 },
      { header: "Mobile No", key: "mobile", width: 18, style: { numFmt: "@" } },
      { header: "Designation *", key: "designation", width: 25 },
    ];

    // Format Mobile No column as text to retain leading zeros/formatting
    sheet.getColumn("mobile").numFmt = "@";

    // Style header row
    const headerRow = sheet.getRow(1);
    headerRow.font = { bold: true, color: { argb: "FFFFFFFF" }, size: 11 };
    headerRow.fill = { type: "pattern", pattern: "solid", fgColor: { argb: "FF0F5132" } };
    headerRow.alignment = { horizontal: "center", vertical: "middle" };
    headerRow.height = 22;

    // Example rows
    sheet.addRow({
      name: "Rahul Kumar",
      mobile: "9876543210",
      designation: "Sales Executive",
    });
    sheet.addRow({
      name: "Suresh Mani",
      mobile: "9845123456",
      designation: "Store Manager",
    });

    sheet.getCell("B2").numFmt = "@";
    sheet.getCell("B3").numFmt = "@";

    // ── Sheet 2: Instructions ──
    const infoSheet = workbook.addWorksheet("Instructions");
    const instructions = [
      "EMPLOYEE BULK IMPORT — INSTRUCTIONS",
      "",
      "Column Rules:",
      "  Employee Name *  — Required. Max 255 characters.",
      "  Mobile No        — Optional. 10-digit mobile number starting with 6-9 if provided.",
      "  Designation *    — Required. Max 100 characters (e.g. Sales Executive, Store Manager).",
      "  Employee Code    — Automatically generated by the system (e.g. EMP001, EMP002, ...).",
      "  Status           — Always set to Active automatically (no column needed).",
      "",
      "Uniqueness Rules:",
      "  - Duplicate check is performed on the combination of Employee Name and Mobile No.",
      "  - Same name with different mobile number is ALLOWED.",
      "  - Same mobile number with different name is ALLOWED.",
      "  - Only if BOTH Employee Name and Mobile No match an existing employee, it will be skipped.",
      "",
      "General Rules:",
      "  - Upload only Excel (.xlsx) files.",
      "  - Maximum file size: 5 MB.",
      "  - All column headers must be present in the sheet.",
    ];
    instructions.forEach((line) => infoSheet.addRow([line]));
    infoSheet.getColumn(1).width = 85;
    const titleCell = infoSheet.getCell("A1");
    titleCell.font = { bold: true, size: 13 };

    const arrayBuffer = await workbook.xlsx.writeBuffer();
    return Buffer.from(arrayBuffer);
  }

  async importBulk(fileBuffer: Buffer, createdBy?: bigint) {
    const workbook = new ExcelJS.Workbook();
    await workbook.xlsx.load(fileBuffer as any);

    const sheet = workbook.getWorksheet("Employees") ?? workbook.worksheets[0];
    if (!sheet) {
      throw new Error("No worksheet found in the uploaded file.");
    }

    // Map header names -> column indices (1-based)
    const headerRow = sheet.getRow(1);
    const colIndex: Record<string, number> = {};
    headerRow.eachCell((cell, colNum) => {
      const key = String(cell.value ?? "").replace(/\s*\*\s*$/, "").trim().toLowerCase();
      colIndex[key] = colNum;
    });

    const nameColIdx = colIndex["employee name"] || colIndex["name"] || colIndex["employee"];
    const mobileColIdx = colIndex["mobile no"] || colIndex["mobile number"] || colIndex["mobile"] || colIndex["phone"];
    const designationColIdx = colIndex["designation"] || colIndex["role"] || colIndex["position"];

    if (!nameColIdx) {
      throw new Error('Missing required column "Employee Name" in the sheet.');
    }
    if (!designationColIdx) {
      throw new Error('Missing required column "Designation" in the sheet.');
    }

    const results: EmployeeImportRowResult[] = [];
    const seenCompoundKeys = new Set<string>();
    let importedCount = 0;
    let skippedCount = 0;

    const cellStr = (row: ExcelJS.Row, colNum?: number): string => {
      if (!colNum) return "";
      const cell = row.getCell(colNum);
      const val = cell.value;
      if (val === null || val === undefined) return "";
      if (typeof val === "object") {
        if ("text" in val && (val as any).text !== undefined) return String((val as any).text).trim();
        if ("result" in val && (val as any).result !== undefined) return String((val as any).result).trim();
        if ("richText" in val && Array.isArray((val as any).richText)) {
          return (val as any).richText.map((rt: any) => rt.text).join("").trim();
        }
      }
      if (typeof val === "number" && cell.text) {
        return String(cell.text).trim();
      }
      return String(val).trim();
    };

    for (let rowNum = 2; rowNum <= sheet.rowCount; rowNum++) {
      const row = sheet.getRow(rowNum);
      // Skip completely empty rows
      if (row.actualCellCount === 0) continue;

      const name = cellStr(row, nameColIdx);
      let mobile = cellStr(row, mobileColIdx);
      const designation = cellStr(row, designationColIdx);

      // Skip row if blank
      if (!name && !mobile && !designation) continue;

      // ── Validate Required Fields ──
      if (!name) {
        results.push({ row: rowNum, name: "(empty)", status: "skipped", reason: "Employee Name is required." });
        skippedCount++;
        continue;
      }
      if (name.length > 255) {
        results.push({ row: rowNum, name, status: "skipped", reason: "Employee Name exceeds 255 characters." });
        skippedCount++;
        continue;
      }

      if (!designation) {
        results.push({ row: rowNum, name, status: "skipped", reason: "Designation is required." });
        skippedCount++;
        continue;
      }
      if (designation.length > 100) {
        results.push({ row: rowNum, name, status: "skipped", reason: "Designation exceeds 100 characters." });
        skippedCount++;
        continue;
      }

      // Clean mobile number (optional)
      if (mobile) {
        mobile = mobile.replace(/\D/g, "");
        if (mobile && !/^[6-9]\d{9}$/.test(mobile)) {
          results.push({ row: rowNum, name, status: "skipped", reason: "Invalid mobile number format (must be 10 digits starting with 6-9)." });
          skippedCount++;
          continue;
        }
      } else {
        mobile = "";
      }

      // ── Compound Uniqueness Check (Name + Mobile) ──
      const compoundKey = `${name.toLowerCase().trim()}:::${mobile}`;
      if (seenCompoundKeys.has(compoundKey)) {
        results.push({
          row: rowNum,
          name,
          status: "skipped",
          reason: "Duplicate employee with same name and mobile number in uploaded file.",
        });
        skippedCount++;
        continue;
      }

      const existingEmp = await this.employeeRepository.findByNameAndMobile(name, mobile || null);
      if (existingEmp) {
        results.push({
          row: rowNum,
          name,
          status: "skipped",
          reason: "Employee with this name and mobile number already exists in the system.",
        });
        skippedCount++;
        continue;
      }

      seenCompoundKeys.add(compoundKey);

      // ── Generate Auto Employee Code ──
      const employeeCode = await this.employeeRepository.generateEmployeeCode();

      // ── Insert Employee ──
      await this.employeeRepository.create({
        employeeCode,
        name: name.trim(),
        mobile: mobile || null,
        designation: designation.trim(),
        status: true,
        createdBy: createdBy ?? null,
      });

      results.push({ row: rowNum, name: `${name} (${employeeCode})`, status: "imported" });
      importedCount++;
    }

    return {
      message: `Import complete. ${importedCount} imported, ${skippedCount} skipped.`,
      importedCount,
      skippedCount,
      results,
    };
  }
}