import ExcelJS from "exceljs";
import { VegetableRepository } from "../repositories/VegetableRepository.js";
import { SYSTEM_PROTECTED_VEGETABLE_IDS } from "../constants/boxTracking.js";

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

export class VegetableService {
  constructor(private readonly vegetableRepository = new VegetableRepository()) { }

  async createVegetable(data: { name: string; tamilName?: string; status?: boolean; hindiName?: string; hsnCode: string; isFlexible?: boolean; createdBy?: bigint }) {
    if (!data.name || !data.name.trim()) {
      throw new Error("Vegetable name is required");
    }
    if (!data.hsnCode || !data.hsnCode.trim()) {
      throw new Error("HSN Code is required");
    }
    const existingName = await this.vegetableRepository.findByName(data.name);
    if (existingName) {
      throw new Error("Vegetable name already exists");
    }
    if (data.tamilName && data.tamilName.trim()) {
      const existingTamil = await this.vegetableRepository.findByTamilName(data.tamilName);
      if (existingTamil) {
        throw new Error("Vegetable Tamil name already exists");
      }
    }
    if (data.hindiName && data.hindiName.trim()) {
      const existingHindi = await this.vegetableRepository.findByHindiName(data.hindiName);
      if (existingHindi) {
        throw new Error("Vegetable Hindi name already exists");
      }
    }
    const vegData = {
      ...data,
      name: data.name.trim(),
      tamilName: data.tamilName?.trim() || null,
      hindiName: data.hindiName?.trim() || null,
      isFlexible: data.isFlexible ?? false,
    };
    return this.vegetableRepository.create(vegData as any);
  }

  async getVegetables(page: number = 1, limit: number = 10, search?: string, status?: boolean) {
    const skip = (page - 1) * limit;
    const { items, total } = await this.vegetableRepository.findAll(skip, limit, search, status);
    const totalPages = Math.ceil(total / limit);

    return {
      data: items,
      meta: {
        total,
        page,
        limit,
        totalPages,
      },
    };
  }

  async getVegetableById(id: bigint) {
    const vegetable = await this.vegetableRepository.findById(id);
    if (!vegetable) {
      throw new Error("Vegetable not found");
    }
    return vegetable;
  }

  async updateVegetable(id: bigint, data: { name?: string; tamilName?: string; status?: boolean; hindiName?: string; hsnCode?: string; isFlexible?: boolean }) {
    const targetId = BigInt(id);
    if (SYSTEM_PROTECTED_VEGETABLE_IDS.some((protectedId) => protectedId === targetId)) {
      throw new Error("Tomato (ID: 1) is a system default vegetable item and cannot be edited.");
    }
    const existing = await this.vegetableRepository.findById(id);
    if (!existing) {
      throw new Error("Vegetable not found");
    }
    if (data.name && data.name.trim().toLowerCase() !== existing.name.toLowerCase()) {
      const existingName = await this.vegetableRepository.findByName(data.name, targetId);
      if (existingName) {
        throw new Error("Vegetable name already exists");
      }
    }
    if (data.tamilName !== undefined && data.tamilName && data.tamilName.trim()) {
      const existingTamilStr = existing.tamilName ? existing.tamilName.trim().toLowerCase() : "";
      if (data.tamilName.trim().toLowerCase() !== existingTamilStr) {
        const existingTamil = await this.vegetableRepository.findByTamilName(data.tamilName, targetId);
        if (existingTamil) {
          throw new Error("Vegetable Tamil name already exists");
        }
      }
    }
    if (data.hindiName !== undefined && data.hindiName && data.hindiName.trim()) {
      const existingHindiStr = existing.hindiName ? existing.hindiName.trim().toLowerCase() : "";
      if (data.hindiName.trim().toLowerCase() !== existingHindiStr) {
        const existingHindi = await this.vegetableRepository.findByHindiName(data.hindiName, targetId);
        if (existingHindi) {
          throw new Error("Vegetable Hindi name already exists");
        }
      }
    }
    if (data.hsnCode !== undefined && (!data.hsnCode || !data.hsnCode.trim())) {
      throw new Error("HSN Code cannot be empty");
    }

    const updateData = {
      ...data,
      ...(data.name ? { name: data.name.trim() } : {}),
      ...(data.tamilName !== undefined ? { tamilName: data.tamilName?.trim() || null } : {}),
      ...(data.hindiName !== undefined ? { hindiName: data.hindiName?.trim() || null } : {}),
    };

    return this.vegetableRepository.update(id, updateData as any);
  }

  async deleteVegetable(id: bigint) {
    const targetId = BigInt(id);
    if (SYSTEM_PROTECTED_VEGETABLE_IDS.some((protectedId) => protectedId === targetId)) {
      throw new Error("Tomato (ID: 1) is a system default vegetable item and cannot be deleted.");
    }
    const existing = await this.vegetableRepository.findById(id);
    if (!existing) {
      throw new Error("Vegetable not found");
    }
    return this.vegetableRepository.delete(id);
  }

  async findByNameForImport(name: string) {
    return this.vegetableRepository.findByName(name);
  }

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

    // ── Sheet 1: Data entry sheet ──
    const sheet = workbook.addWorksheet("Vegetables");
    sheet.columns = [
      { header: "Name *", key: "name", width: 28 },
      { header: "Tamil Name *", key: "tamilName", width: 22 },
      { header: "Hindi Name *", key: "hindiName", width: 22 },
      { header: "HSN Code *", key: "hsnCode", width: 16, style: { numFmt: "@" } },
    ];

    // Pre-set HSN Code column as "Text" format so typing digits retains leading zeros
    const hsnCol = sheet.getColumn("hsnCode");
    hsnCol.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 (placed at row 2 and row 3)
    sheet.addRow({ name: "Tomato", tamilName: "தக்காளி", hindiName: "टमाटर", hsnCode: "07020000" });
    sheet.addRow({ name: "Onion", tamilName: "வெங்காயம்", hindiName: "प्याज", hsnCode: "07031000" });

    sheet.getCell("D2").numFmt = "@";
    sheet.getCell("D3").numFmt = "@";

    // ── Sheet 2: Instructions ──
    const infoSheet = workbook.addWorksheet("Instructions");
    const instructions = [
      "VEGETABLE BULK IMPORT — INSTRUCTIONS",
      "",
      "Column Rules:",
      "  Name *       — Required. Max 150 characters. Must be unique (no duplicates in system).",
      "  Tamil Name * — Required. Max 150 characters.",
      "  Hindi Name * — Required. Max 150 characters.",
      "  HSN Code *   — Required. Numbers only, max 15 digits. Pre-set as Text format to retain leading zeros.",
      "  Status       — Always set to Active automatically (no column needed).",
      "",
      "General Rules:",
      "  - Upload only Excel (.xlsx) files.",
      "  - Maximum file size: 5 MB.",
      "  - You can only import new vegetables — names that already exist will be skipped.",
      "  - Duplicate names within the same file are also skipped.",
      "  - All column headers must be present in the sheet.",
      "  - HSN Code column is formatted as Text to preserve leading zeros (e.g., 07020000).",
    ];
    instructions.forEach((line) => infoSheet.addRow([line]));
    infoSheet.getColumn(1).width = 80;
    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("Vegetables") ?? 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 required = ["name", "tamil name", "hindi name", "hsn code"];
    for (const col of required) {
      if (!colIndex[col]) {
        throw new Error(`Missing required column "${col}" in the sheet.`);
      }
    }

    const results: VegetableImportRowResult[] = [];
    const seenNames = new Set<string>();
    const seenTamilNames = new Set<string>();
    const seenHindiNames = new Set<string>();
    let importedCount = 0;
    let skippedCount = 0;

    const cellStr = (row: ExcelJS.Row, key: string): string => {
      if (!colIndex[key]) return "";
      const cell = row.getCell(colIndex[key]);
      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, "name");
      const tamilName = cellStr(row, "tamil name");
      const hindiName = cellStr(row, "hindi name");
      const hsnRaw = cellStr(row, "hsn code");

      // ── Validate ──
      if (!name) {
        results.push({ row: rowNum, name: "(empty)", status: "skipped", reason: "Name is required." });
        skippedCount++;
        continue;
      }
      if (!tamilName) {
        results.push({ row: rowNum, name: name || "(empty)", status: "skipped", reason: "Tamil Name is required." });
        skippedCount++;
        continue;
      }
      if (!hindiName) {
        results.push({ row: rowNum, name: name || "(empty)", status: "skipped", reason: "Hindi Name is required." });
        skippedCount++;
        continue;
      }
      if (!hsnRaw) {
        results.push({ row: rowNum, name, status: "skipped", reason: "HSN Code is required." });
        skippedCount++;
        continue;
      }
      if (name.length > 150) {
        results.push({ row: rowNum, name, status: "skipped", reason: "Name exceeds 150 characters." });
        skippedCount++;
        continue;
      }
      if (tamilName.length > 150) {
        results.push({ row: rowNum, name, status: "skipped", reason: "Tamil Name exceeds 150 characters." });
        skippedCount++;
        continue;
      }
      if (hindiName.length > 150) {
        results.push({ row: rowNum, name, status: "skipped", reason: "Hindi Name exceeds 150 characters." });
        skippedCount++;
        continue;
      }
      if (hsnRaw && (!/^\d+$/.test(hsnRaw) || /^0+$/.test(hsnRaw) || hsnRaw.length > 15)) {
        results.push({ row: rowNum, name, status: "skipped", reason: "HSN Code must be positive digits only and max 15 digits." });
        skippedCount++;
        continue;
      }

      // Duplicate within the file
      const nameLower = name.toLowerCase();
      if (seenNames.has(nameLower)) {
        results.push({ row: rowNum, name, status: "skipped", reason: "Duplicate name in uploaded file." });
        skippedCount++;
        continue;
      }

      const tamilLower = tamilName.toLowerCase();
      if (seenTamilNames.has(tamilLower)) {
        results.push({ row: rowNum, name, status: "skipped", reason: "Duplicate Tamil name in uploaded file." });
        skippedCount++;
        continue;
      }

      const hindiLower = hindiName.toLowerCase();
      if (seenHindiNames.has(hindiLower)) {
        results.push({ row: rowNum, name, status: "skipped", reason: "Duplicate Hindi name in uploaded file." });
        skippedCount++;
        continue;
      }

      // Duplicate in database
      const existing = await this.vegetableRepository.findByName(name);
      if (existing) {
        results.push({ row: rowNum, name, status: "skipped", reason: "Vegetable name already exists in the system." });
        skippedCount++;
        continue;
      }

      const existingTamil = await this.vegetableRepository.findByTamilName(tamilName);
      if (existingTamil) {
        results.push({ row: rowNum, name, status: "skipped", reason: "Vegetable Tamil name already exists in the system." });
        skippedCount++;
        continue;
      }

      const existingHindi = await this.vegetableRepository.findByHindiName(hindiName);
      if (existingHindi) {
        results.push({ row: rowNum, name, status: "skipped", reason: "Vegetable Hindi name already exists in the system." });
        skippedCount++;
        continue;
      }

      seenNames.add(nameLower);
      seenTamilNames.add(tamilLower);
      seenHindiNames.add(hindiLower);

      // ── Insert ──
      await this.createVegetable({
        name,
        tamilName: tamilName || undefined,
        hindiName: hindiName || undefined,
        hsnCode: hsnRaw || undefined,
        status: true,
        createdBy,
      } as any);

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

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