import type { Request, Response } from "express";
import multer from "multer";
import * as XLSX from "xlsx";
import { prisma } from "../../config/prisma.js";
import { ApiError, asyncHandler } from "../../utils/asyncHandler.js";
import { audit } from "../../utils/audit.js";
import { buildEmployeeWhere } from "./employees.controller.js";
import { createEmployeeSchema } from "./core.schemas.js";

const MIME_XLSX = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

export const excelUpload = multer({
  storage: multer.memoryStorage(),
  limits: { fileSize: 10 * 1024 * 1024, files: 1 },
  fileFilter: (_req, file, cb) => {
    const ok =
      /\.(xlsx|xls)$/i.test(file.originalname) ||
      file.mimetype === MIME_XLSX ||
      file.mimetype === "application/vnd.ms-excel" ||
      file.mimetype === "application/octet-stream";
    if (ok) return cb(null, true);
    cb(new ApiError(400, "الملف يجب أن يكون Excel (.xlsx أو .xls)"));
  },
});

const EXPORT_COLUMNS: { key: string; label: string }[] = [
  { key: "fullName", label: "اسم الموظف" },
  { key: "companyName", label: "اسم الشركة" },
  { key: "nationality", label: "الجنسية" },
  { key: "profession", label: "المهنة" },
  { key: "department", label: "القسم" },
  { key: "city", label: "المدينة" },
  { key: "status", label: "الحالة" },
  { key: "iqamaNo", label: "رقم الإقامة" },
  { key: "iqamaExpiry", label: "انتهاء الإقامة" },
  { key: "iqamaIssueDate", label: "إصدار الإقامة" },
  { key: "passportNo", label: "رقم الجواز" },
  { key: "passportExpiry", label: "انتهاء الجواز" },
  { key: "insuranceNo", label: "رقم التأمين الطبي" },
  { key: "insuranceExpiry", label: "انتهاء التأمين الطبي" },
  { key: "basicSalary", label: "الراتب الأساسي" },
  { key: "hireDate", label: "تاريخ التعيين" },
  { key: "email", label: "البريد الإلكتروني" },
  { key: "phone", label: "الهاتف" },
  { key: "notes", label: "ملاحظات" },
];

const STATUS_ARABIC: Record<string, string> = {
  active: "على رأس العمل",
  resigned: "استقال",
  on_leave: "إجازة",
};

const STATUS_TO_ENUM: Record<string, string> = {
  active: "active",
  resigned: "resigned",
  on_leave: "on_leave",
  نشط: "active",
  "على رأس العمل": "active",
  عامل: "active",
  استقال: "resigned",
  مفصول: "resigned",
  منتهي: "resigned",
  إجازة: "on_leave",
  اجازة: "on_leave",
  "في إجازة": "on_leave",
};

function setExcelHeaders(res: Response, filename: string) {
  res.setHeader("Content-Type", MIME_XLSX);
  res.setHeader("Content-Disposition", `attachment; filename="${filename}"`);
}

function toDateString(v: any): string | null {
  if (v === undefined || v === null) return null;
  if (v instanceof Date && !isNaN(v.getTime())) {
    const y = v.getFullYear();
    const m = String(v.getMonth() + 1).padStart(2, "0");
    const d = String(v.getDate()).padStart(2, "0");
    return `${y}-${m}-${d}`;
  }
  if (typeof v === "number" && isFinite(v)) {
    const parsed = XLSX.SSF?.parse_date_code(v);
    if (parsed) return `${parsed.y}-${String(parsed.m).padStart(2, "0")}-${String(parsed.d).padStart(2, "0")}`;
  }
  const s = String(v).trim();
  return s === "" ? null : s;
}

function cleanStr(v: any): string | null {
  if (v === undefined || v === null) return null;
  const s = String(v).trim();
  return s === "" ? null : s;
}

function cleanSalary(v: any): number | null {
  if (v === undefined || v === null) return null;
  if (typeof v === "number") return v;
  const s = String(v).replace(/[,\s]/g, "");
  if (s === "") return null;
  const n = Number(s);
  return isNaN(n) ? null : n;
}

function normalizeHeader(s: any): string {
  return String(s ?? "")
    .trim()
    .replace(/\*+$/, "")
    .trim()
    .toLowerCase();
}

function normalizeCompanyName(s: string): string {
  return String(s || "")
    .trim()
    .toLowerCase()
    .replace(/\s+/g, " ");
}

export const downloadEmployeeTemplate = asyncHandler(async (_req: Request, res: Response) => {
  const headers = EXPORT_COLUMNS.map((c) => (c.key === "fullName" || c.key === "companyName" ? `${c.label} *` : c.label));
  const importWs = XLSX.utils.aoa_to_sheet([headers]);

  const instructionsWs = XLSX.utils.aoa_to_sheet([
    ["إرشادات استيراد العمالة"],
    [],
    ["1. العمودان المطلوبان: اسم الموظف واسم الشركة (يجب أن يكون اسم الشركة موجوداً مسبقاً في النظام)."],
    ["2. الحقول الأخرى اختيارية."],
    ["3. الحالة: على رأس العمل / استقال / إجازة (أو active / resigned / on_leave)."],
    ["4. التواريخ بصيغة YYYY-MM-DD (مثال: 2026-08-17)."],
    ["5. الراتب الأساسي رقم فقط."],
    ["6. لا تحذف صف العناوين — ابدأ البيانات من الصف الثاني."],
    ["7. الملف يجب أن يكون .xlsx أو .xls."],
  ]);

  const wb = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(wb, importWs, "استيراد");
  XLSX.utils.book_append_sheet(wb, instructionsWs, "إرشادات");
  const buf = XLSX.write(wb, { type: "buffer", bookType: "xlsx" });
  setExcelHeaders(res, "employees-template.xlsx");
  res.send(buf);
});

export const exportEmployees = asyncHandler(async (req: Request, res: Response) => {
  const where = buildEmployeeWhere(req.query);
  const employees = await prisma.employee.findMany({
    where,
    include: { company: { select: { name: true } } },
    orderBy: { createdAt: "desc" },
  });

  const header = EXPORT_COLUMNS.map((c) => c.label);
  const data = employees.map((e) => [
    e.fullName,
    e.company?.name ?? "",
    e.nationality ?? "",
    e.profession ?? "",
    e.department ?? "",
    e.city ?? "",
    STATUS_ARABIC[e.status] ?? e.status,
    e.iqamaNo ?? "",
    toDateString(e.iqamaExpiry) ?? "",
    toDateString(e.iqamaIssueDate) ?? "",
    e.passportNo ?? "",
    toDateString(e.passportExpiry) ?? "",
    e.insuranceNo ?? "",
    toDateString(e.insuranceExpiry) ?? "",
    e.basicSalary === null ? "" : Number(e.basicSalary),
    toDateString(e.hireDate) ?? "",
    e.email ?? "",
    e.phone ?? "",
    e.notes ?? "",
  ]);
  const ws = XLSX.utils.aoa_to_sheet([header, ...data]);
  const wb = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(wb, ws, "العمالة");
  const buf = XLSX.write(wb, { type: "buffer", bookType: "xlsx", cellDates: true });
  setExcelHeaders(res, "employees-export.xlsx");
  res.send(buf);
});

export const importEmployees = asyncHandler(async (req: Request, res: Response) => {
  const file = (req as any).file;
  if (!file) throw new ApiError(400, "الملف مطلوب");

  let workbook: XLSX.WorkBook;
  try {
    workbook = XLSX.read(file.buffer, { type: "buffer", cellDates: true });
  } catch {
    throw new ApiError(400, "تعذر قراءة الملف. تأكد أنه ملف Excel صالح");
  }

  const sheetName = workbook.SheetNames[0];
  if (!sheetName) throw new ApiError(400, "الملف لا يحتوي على أوراق عمل");
  const sheet = workbook.Sheets[sheetName];
  const rawRows: Record<string, any>[] = XLSX.utils.sheet_to_json(sheet, { defval: "" });

  if (rawRows.length === 0) throw new ApiError(400, "الملف لا يحتوي على بيانات");

  // مطابقة العناوين بالأعمدة
  const headerKeys = new Map<number, string>(); // colIndex -> field key
  const labelToKey = new Map<string, string>();
  for (const c of EXPORT_COLUMNS) {
    labelToKey.set(normalizeHeader(c.label), c.key);
    labelToKey.set(c.key.toLowerCase(), c.key);
  }
  const rowValues = (rawRows as any[])[0];
  const keys = Object.keys(rowValues);
  keys.forEach((k, idx) => {
    const key = labelToKey.get(normalizeHeader(k));
    if (key) headerKeys.set(idx, key);
  });

  const companies = await prisma.company.findMany({ select: { id: true, name: true } });
  const companyByName = new Map<string, number>();
  for (const c of companies) companyByName.set(normalizeCompanyName(c.name), c.id);

  const mapRow = (raw: Record<string, any>): Record<string, any> => {
    const values = Object.values(raw);
    const row: Record<string, any> = {};
    for (const [idx, key] of headerKeys) row[key] = values[idx];
    return row;
  };

  const imported: number[] = [];
  const errors: { row: number; message: string }[] = [];

  for (let i = 0; i < rawRows.length; i++) {
    const rowNumber = i + 2; // الصف الأول هو العناوين
    const raw = mapRow(rawRows[i]);
    try {
      const body: any = { fullName: cleanStr(raw.fullName) ?? "" };

      if (raw.companyId !== undefined && raw.companyId !== "") {
        body.companyId = Number(raw.companyId);
      } else {
        const companyName = cleanStr(raw.companyName);
        if (!companyName) throw new ApiError(400, "اسم الشركة مطلوب");
        const companyId = companyByName.get(normalizeCompanyName(companyName));
        if (!companyId) throw new ApiError(400, `الشركة غير موجودة: ${companyName}`);
        body.companyId = companyId;
      }

      if (raw.nationality !== undefined) body.nationality = cleanStr(raw.nationality);
      if (raw.profession !== undefined) body.profession = cleanStr(raw.profession);
      if (raw.department !== undefined) body.department = cleanStr(raw.department);
      if (raw.city !== undefined) body.city = cleanStr(raw.city);
      if (raw.iqamaNo !== undefined) body.iqamaNo = cleanStr(raw.iqamaNo);
      if (raw.passportNo !== undefined) body.passportNo = cleanStr(raw.passportNo);
      if (raw.insuranceNo !== undefined) body.insuranceNo = cleanStr(raw.insuranceNo);
      if (raw.email !== undefined) body.email = cleanStr(raw.email);
      if (raw.phone !== undefined) body.phone = cleanStr(raw.phone);
      if (raw.notes !== undefined) body.notes = cleanStr(raw.notes);
      if (raw.basicSalary !== undefined) body.basicSalary = cleanSalary(raw.basicSalary);
      if (raw.iqamaExpiry !== undefined) body.iqamaExpiry = toDateString(raw.iqamaExpiry);
      if (raw.iqamaIssueDate !== undefined) body.iqamaIssueDate = toDateString(raw.iqamaIssueDate);
      if (raw.passportExpiry !== undefined) body.passportExpiry = toDateString(raw.passportExpiry);
      if (raw.insuranceExpiry !== undefined) body.insuranceExpiry = toDateString(raw.insuranceExpiry);
      if (raw.hireDate !== undefined) body.hireDate = toDateString(raw.hireDate);

      if (raw.status !== undefined && cleanStr(raw.status)) {
        const status = cleanStr(raw.status)!;
        body.status = STATUS_TO_ENUM[status.toLowerCase()] ?? status;
      }

      const parsed = createEmployeeSchema.safeParse({ body });
      if (!parsed.success) {
        const msg = parsed.error.issues.map((iss) => `${String(iss.path[0])}: ${iss.message}`).join("؛ ");
        errors.push({ row: rowNumber, message: msg });
        continue;
      }

      const employee = await prisma.employee.create({ data: parsed.data.body });
      imported.push(employee.id);
      await audit(req, "create", "employee", employee.id, null, parsed.data.body);
    } catch (err: any) {
      errors.push({ row: rowNumber, message: err?.message || "خطأ غير متوقع" });
    }
  }

  res.json({ success: true, total: rawRows.length, imported: imported.length, failed: errors.length, errors });
});