import ExcelJS from "exceljs";
import { StateRepository } from "../repositories/StateRepository.js";

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

export class StateService {
  constructor(private readonly stateRepository = new StateRepository()) {}

  async createState(data: { name: string; tamilName?: string; status?: boolean; createdBy?: bigint }) {
    if (!data.name || !data.name.trim()) {
      throw new Error("State name is required");
    }
    const existingName = await this.stateRepository.findByName(data.name.trim());
    if (existingName) {
      throw new Error("State name already exists");
    }
    if (data.tamilName && data.tamilName.trim()) {
      const existingTamilName = await this.stateRepository.findByTamilName(data.tamilName.trim());
      if (existingTamilName) {
        throw new Error("State Tamil name already exists");
      }
    }
    const stateData = {
      ...data,
      name: data.name.trim(),
      tamilName: data.tamilName?.trim() || null,
    };
    return this.stateRepository.create(stateData as any);
  }

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

  async getActiveStates() {
    return this.stateRepository.getActiveList();
  }

  async getStateById(id: bigint) {
    const state = await this.stateRepository.findById(id);
    if (!state) {
      throw new Error("State not found");
    }
    return state;
  }

  async updateState(id: bigint, data: { name?: string; tamilName?: string; status?: boolean }) {
    const existing = await this.stateRepository.findById(id);
    if (!existing) {
      throw new Error("State not found");
    }
    if (data.name && data.name.trim().toLowerCase() !== existing.name.toLowerCase()) {
      const duplicateName = await this.stateRepository.findByName(data.name.trim(), id);
      if (duplicateName) {
        throw new Error("State name already exists");
      }
    }
    if (data.tamilName !== undefined && data.tamilName && data.tamilName.trim()) {
      const existingTamil = existing.tamilName ? existing.tamilName.trim().toLowerCase() : "";
      if (data.tamilName.trim().toLowerCase() !== existingTamil) {
        const duplicateTamilName = await this.stateRepository.findByTamilName(data.tamilName.trim(), id);
        if (duplicateTamilName) {
          throw new Error("State Tamil name already exists");
        }
      }
    }
    const updateData = {
      ...data,
      ...(data.name ? { name: data.name.trim() } : {}),
      ...(data.tamilName !== undefined ? { tamilName: data.tamilName?.trim() || null } : {}),
    };
    return this.stateRepository.update(id, updateData as any);
  }

  async deleteState(id: bigint) {
    const existing = await this.stateRepository.findById(id);
    if (!existing) {
      throw new Error("State not found");
    }
    return this.stateRepository.delete(id);
  }

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

    // ── Sheet 1: Data entry sheet ──
    const sheet = workbook.addWorksheet("States");
    sheet.columns = [
      { header: "Name *", key: "name", width: 28 },
      { header: "Tamil Name *", key: "tamilName", width: 28 },
    ];

    // 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: "Tamil Nadu", tamilName: "தமிழ்நாடு" });
    sheet.addRow({ name: "Kerala", tamilName: "கேரளா" });
    sheet.addRow({ name: "Karnataka", tamilName: "கர்நாடகா" });
    sheet.addRow({ name: "Andhra Pradesh", tamilName: "ஆந்திரப் பிரதேசம்" });

    // ── Sheet 2: Instructions ──
    const infoSheet = workbook.addWorksheet("Instructions");
    const instructions = [
      "STATE BULK IMPORT — INSTRUCTIONS",
      "",
      "Column Rules:",
      "  Name *       — Required. Max 150 characters. Must be unique (no duplicates in system).",
      "  Tamil Name * — Required. Max 150 characters. Must be unique (no duplicates in system).",
      "  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 states — names that already exist will be skipped.",
      "  - Duplicate names and Tamil names within the same file are also skipped.",
      "  - All column headers must be present in the sheet.",
    ];
    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("States") ?? 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["name"] || colIndex["state name"] || colIndex["state"];
    const tamilNameColIdx = colIndex["tamil name"] || colIndex["state tamil name"];

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

    const results: StateImportRowResult[] = [];
    const seenNames = new Set<string>();
    const seenTamilNames = 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);
      const tamilName = cellStr(row, tamilNameColIdx);

      // Skip row if completely blank
      if (!name && !tamilName) continue;

      // ── Validate Required Fields ──
      if (!name) {
        results.push({ row: rowNum, name: "(empty)", status: "skipped", reason: "State Name is required." });
        skippedCount++;
        continue;
      }
      if (!tamilName) {
        results.push({ row: rowNum, name, status: "skipped", reason: "Tamil Name is required." });
        skippedCount++;
        continue;
      }
      if (name.length > 150) {
        results.push({ row: rowNum, name, status: "skipped", reason: "State 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;
      }

      // ── In-File Duplicate Checks ──
      const nameLower = name.toLowerCase();
      if (seenNames.has(nameLower)) {
        results.push({ row: rowNum, name, status: "skipped", reason: "Duplicate state 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;
      }

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

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

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

      // ── Insert State ──
      await this.createState({
        name,
        tamilName: tamilName || 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,
    };
  }
}