import * as XLSX from "xlsx";
import { downloadFile } from "./downloadHelper";
import { formatFileName } from "../utils";

/**
 * Generate and download a sample Excel file for employee bulk upload
 */
export function generateSampleExcel(): void {
  // Define the headers
  const headers = [
    "first_name",
    "last_name",
    "email",
    "contact_no",
    "alternate_mobile_number",
    "employee_type",
    "CTC",
    "stipend",
    "joining_date",
    "role_name",
    "department_name",
    "regime_type",

    // Employee Information fields
    "address",
    "gender",
    "date_of_birth",
    "marital_status",
    "parents_name",
    "parents_contact_number",
    "blood_group",
    "native_place",
    "aadhar_card",
    "pan_card_number",
    "education_qualification",
    "total_work_experience_years",
  ];

  // Create sample data rows
  const sampleData = [
    {
      first_name: "John",
      last_name: "Doe",
      email: "john.doe11@example.com",
      contact_no: "9876543210",
      alternate_mobile_number: "9876543200",
      employee_type: "employee",
      CTC: 50000,
      stipend: 0,
      joining_date: "2024-01-15",
      role_name: "Manager",
      department_name: "Management",
      regime_type: "new",

      address: "Ahmedabad, Gujarat",
      gender: "Male",
      date_of_birth: "1995-06-10",
      marital_status: "Single",
      parents_name: "Robert Doe",
      parents_contact_number: "9876500001",
      blood_group: "B+",
      native_place: "Rajkot",
      aadhar_card: "1234-5678-9012",
      pan_card_number: "ABCDE1234F",
      education_qualification: "MBA",
      total_work_experience_years: 5,
    },
    {
      first_name: "Jane",
      last_name: "Smith",
      email: "jane.smith11@example.com",
      contact_no: "9876543211",
      alternate_mobile_number: "9876543201",
      employee_type: "trainee",
      CTC: 0,
      stipend: 15000,
      joining_date: "2024-02-01",
      role_name: "HR",
      department_name: "Human Resources",
      regime_type: "old",

      address: "Surat, Gujarat",
      gender: "Female",
      date_of_birth: "1998-03-22",
      marital_status: "Unmarried",
      parents_name: "David Smith",
      parents_contact_number: "9876500002",
      blood_group: "O+",
      native_place: "Vadodara",
      aadhar_card: "2345-6789-0123",
      pan_card_number: "FGHIJ5678K",
      education_qualification: "BBA",
      total_work_experience_years: 2,
    },
  ];

  // Create workbook and worksheet
  const workbook = XLSX.utils.book_new();

  // Create the main worksheet with headers and sample data
  const worksheetData = [
    headers,
    ...sampleData.map((row) =>
      headers.map((header) => row[header as keyof typeof row])
    ),
  ];
  const worksheet = XLSX.utils.aoa_to_sheet(worksheetData);

  // Create the NOTE row content
  const noteText = `NOTE:
- role_name and department_name must EXACTLY match names already created in the system.
- employee_type allowed values: trainee, employee
- joining_date format: YYYY-MM-DD
- email must be unique per company
- status allowed values: active, inactive
- For employees: set CTC > 0 and stipend = 0
- For trainees: set CTC = 0 and stipend > 0`;

  // Insert the NOTE row at the top
  XLSX.utils.sheet_add_aoa(worksheet, [[noteText]], { origin: "A1" });

  // Merge cells for the NOTE row (A1 to K1 - covering all columns)
  if (!worksheet["!merges"]) worksheet["!merges"] = [];
  worksheet["!merges"].push({
    s: { r: 0, c: 0 }, // Start: A1
    e: { r: 0, c: headers.length - 1 }, // End: K1 (last column)
  });

  // Shift the headers and data down by 1 row
  XLSX.utils.sheet_add_aoa(worksheet, [headers], { origin: "A2" });
  XLSX.utils.sheet_add_aoa(
    worksheet,
    sampleData.map((row) =>
      headers.map((header) => row[header as keyof typeof row])
    ),
    { origin: "A3" }
  );

  // Set column widths for better readability
  const columnWidths = [
    { wch: 15 }, // first_name
    { wch: 15 }, // last_name
    { wch: 25 }, // email
    { wch: 15 }, // contact_no
    { wch: 15 }, // employee_type
    { wch: 12 }, // gross_salary
    { wch: 10 }, // stipend
    { wch: 15 }, // joining_date
    { wch: 20 }, // role_name
    { wch: 20 }, // department_name
    { wch: 10 }, // status
    { wch: 10 }, // regime_type
  ];
  worksheet["!cols"] = columnWidths;

  // Style the NOTE row
  const noteCell = worksheet["A1"];
  if (noteCell) {
    noteCell.s = {
      fill: {
        fgColor: { rgb: "F3F4F6" }, // Light gray background
      },
      font: {
        bold: true,
        sz: 10,
      },
      alignment: {
        wrapText: true,
        vertical: "top",
      },
    };
  }

  // Style the header row
  headers.forEach((_, index) => {
    const cellAddress = XLSX.utils.encode_cell({ r: 1, c: index });
    const cell = worksheet[cellAddress];
    if (cell) {
      cell.s = {
        fill: {
          fgColor: { rgb: "E5E7EB" }, // Slightly darker gray for headers
        },
        font: {
          bold: true,
          sz: 11,
        },
        alignment: {
          horizontal: "center",
        },
      };
    }
  });

  // Set row heights
  worksheet["!rows"] = [
    { hpt: 80 }, // NOTE row - taller for wrapped text
    { hpt: 20 }, // Header row
    { hpt: 18 }, // Data rows
    { hpt: 18 },
  ];

  // Add worksheet to workbook
  XLSX.utils.book_append_sheet(workbook, worksheet, "Employee Template");

  // Generate Excel file buffer
  const excelBuffer = XLSX.write(workbook, {
    bookType: "xlsx",
    type: "array",
  });

  // Create blob and download
  const blob = new Blob([excelBuffer], {
    type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
  });

  const filename = formatFileName("employee_bulk_upload_template");

  // Download the file
  downloadFile(
    blob,
    filename,
    "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
  );
}

/**
 * Validate Excel file structure before upload
 * @param file - The Excel file to validate
 * @returns Promise<{valid: boolean, errors: string[]}>
 */
export async function validateExcelFile(
  file: File
): Promise<{ valid: boolean; errors: string[] }> {
  return new Promise((resolve) => {
    const reader = new FileReader();

    reader.onload = (e) => {
      try {
        const data = new Uint8Array(e.target?.result as ArrayBuffer);
        const workbook = XLSX.read(data, { type: "array" });

        // Get first worksheet
        const firstSheetName = workbook.SheetNames[0];
        const worksheet = workbook.Sheets[firstSheetName];

        // Convert to JSON to check structure
        const jsonData = XLSX.utils.sheet_to_json(worksheet, { header: 1 });

        const errors: string[] = [];

        // Check if file has data
        if (jsonData.length < 2) {
          errors.push(
            "File must contain at least a header row and one data row"
          );
        }

        // Expected headers (skip NOTE row, check actual headers)
        const expectedHeaders = [
          "first_name",
          "last_name",
          "email",
          "contact_no",
          "employee_type",
          "CTC",
          "stipend",
          "joining_date",
          "role_name",
          "department_name",
          "status",
          "regime_type",
        ];

        // Find header row (skip NOTE row if present)
        let headerRowIndex = 0;
        for (let i = 0; i < Math.min(3, jsonData.length); i++) {
          const row = jsonData[i] as string[];
          if (row && row.some((cell) => expectedHeaders.includes(cell))) {
            headerRowIndex = i;
            break;
          }
        }

        const headers = jsonData[headerRowIndex] as string[];

        // Check required headers
        const missingHeaders = expectedHeaders.filter(
          (header) => !headers.includes(header)
        );

        if (missingHeaders.length > 0) {
          errors.push(`Missing required headers: ${missingHeaders.join(", ")}`);
        }

        resolve({
          valid: errors.length === 0,
          errors,
        });
      } catch (error) {
        resolve({
          valid: false,
          errors: ["Invalid Excel file format"],
        });
      }
    };

    reader.onerror = () => {
      resolve({
        valid: false,
        errors: ["Failed to read file"],
      });
    };

    reader.readAsArrayBuffer(file);
  });
}

/**
 * Generate and download a sample Excel file for timesheet bulk upload
 */
export function generateTimesheetSampleExcel(): void {
  // Define the headers
  const headers = [
    "project_name",
    "employee_email",
    "start_time",
    "end_time",
    "activity_type",
    "description",
  ];

  // Create sample data rows
  const sampleData = [
    {
      project_name: "Timesheet Management",
      employee_email: "rahul.verma@mailinator.com",
      start_time: "2026-01-07 09:00",
      end_time: "2026-01-07 12:00",
      activity_type: "3D architectural",
      description: "Frontend work on homepage",
    },
    {
      project_name: "HRMS Portal",
      employee_email: "amit.sharma@mailinator.com",
      start_time: "2026-01-07 13:00",
      end_time: "2026-01-07 17:00",
      activity_type: "New type",
      description: "Bug testing and fixes",
    },
  ];

  // Create workbook and worksheet
  const workbook = XLSX.utils.book_new();

  // Create the main worksheet with headers and sample data
  const worksheetData = [
    headers,
    ...sampleData.map((row) =>
      headers.map((header) => row[header as keyof typeof row])
    ),
  ];
  const worksheet = XLSX.utils.aoa_to_sheet(worksheetData);

  // Create the NOTE row content
  const noteText = `NOTE:
- project_name, employee_email, and activity_type must EXACTLY match existing records in the system
- Time format: YYYY-MM-DD HH:MM
- End time must be after start time
- Employee email must exist in the system
- Project name must exist in the system
- Activity type must exist in the system`;

  // Insert the NOTE row at the top
  XLSX.utils.sheet_add_aoa(worksheet, [[noteText]], { origin: "A1" });

  // Merge cells for the NOTE row (A1 to F1 - covering all columns)
  if (!worksheet["!merges"]) worksheet["!merges"] = [];
  worksheet["!merges"].push({
    s: { r: 0, c: 0 }, // Start: A1
    e: { r: 0, c: headers.length - 1 }, // End: F1 (last column)
  });

  // Shift the headers and data down by 1 row
  XLSX.utils.sheet_add_aoa(worksheet, [headers], { origin: "A2" });
  XLSX.utils.sheet_add_aoa(
    worksheet,
    sampleData.map((row) =>
      headers.map((header) => row[header as keyof typeof row])
    ),
    { origin: "A3" }
  );

  // Set column widths for better readability
  const columnWidths = [
    { wch: 20 }, // project_name
    { wch: 25 }, // employee_email
    { wch: 18 }, // start_time
    { wch: 18 }, // end_time
    { wch: 15 }, // activity_type
    { wch: 30 }, // description
  ];
  worksheet["!cols"] = columnWidths;

  // Style the NOTE row
  const noteCell = worksheet["A1"];
  if (noteCell) {
    noteCell.s = {
      fill: {
        fgColor: { rgb: "F3F4F6" }, // Light gray background
      },
      font: {
        bold: true,
        sz: 10,
      },
      alignment: {
        wrapText: true,
        vertical: "top",
      },
    };
  }

  // Style the header row
  headers.forEach((_, index) => {
    const cellAddress = XLSX.utils.encode_cell({ r: 1, c: index });
    const cell = worksheet[cellAddress];
    if (cell) {
      cell.s = {
        fill: {
          fgColor: { rgb: "E5E7EB" }, // Slightly darker gray for headers
        },
        font: {
          bold: true,
          sz: 11,
        },
        alignment: {
          horizontal: "center",
        },
      };
    }
  });

  // Set row heights
  worksheet["!rows"] = [
    { hpt: 80 }, // NOTE row - taller for wrapped text
    { hpt: 20 }, // Header row
    { hpt: 18 }, // Data rows
    { hpt: 18 },
  ];

  // Add worksheet to workbook
  XLSX.utils.book_append_sheet(workbook, worksheet, "Timesheet Template");

  // Generate Excel file buffer
  const excelBuffer = XLSX.write(workbook, {
    bookType: "xlsx",
    type: "array",
  });

  // Create blob and download
  const blob = new Blob([excelBuffer], {
    type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
  });

  const filename = formatFileName("timesheet_bulk_upload_template");

  // Download the file
  downloadFile(
    blob,
    filename,
    "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
  );
}

/**
 * Validate timesheet Excel file structure before upload
 * @param file - The Excel file to validate
 * @returns Promise<{valid: boolean, errors: string[]}>
 */
export async function validateTimesheetExcelFile(
  file: File
): Promise<{ valid: boolean; errors: string[] }> {
  return new Promise((resolve) => {
    const reader = new FileReader();

    reader.onload = (e) => {
      try {
        const data = new Uint8Array(e.target?.result as ArrayBuffer);
        const workbook = XLSX.read(data, { type: "array" });

        // Get first worksheet
        const firstSheetName = workbook.SheetNames[0];
        const worksheet = workbook.Sheets[firstSheetName];

        // Convert to JSON to check structure
        const jsonData = XLSX.utils.sheet_to_json(worksheet, { header: 1 });

        const errors: string[] = [];

        // Check if file has data
        if (jsonData.length < 2) {
          errors.push(
            "File must contain at least a header row and one data row"
          );
        }

        // Expected headers for timesheet
        const expectedHeaders = [
          "project_name",
          "employee_email",
          "start_time",
          "end_time",
          "activity_type",
          "description",
        ];

        // Find header row (skip NOTE row if present)
        let headerRowIndex = 0;
        for (let i = 0; i < Math.min(3, jsonData.length); i++) {
          const row = jsonData[i] as string[];
          if (row && row.some((cell) => expectedHeaders.includes(cell))) {
            headerRowIndex = i;
            break;
          }
        }

        const headers = jsonData[headerRowIndex] as string[];

        // Check required headers
        const missingHeaders = expectedHeaders.filter(
          (header) => !headers.includes(header)
        );

        if (missingHeaders.length > 0) {
          errors.push(`Missing required headers: ${missingHeaders.join(", ")}`);
        }

        resolve({
          valid: errors.length === 0,
          errors,
        });
      } catch (error) {
        resolve({
          valid: false,
          errors: ["Invalid Excel file format"],
        });
      }
    };

    reader.onerror = () => {
      resolve({
        valid: false,
        errors: ["Error reading file"],
      });
    };

    reader.readAsArrayBuffer(file);
  });
}