import ExcelJS from "exceljs";
import prisma from "../config/prisma.js";
import { DistrictRepository } from "../repositories/DistrictRepository.js";

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

export class DistrictService {
  constructor(private readonly districtRepository = new DistrictRepository()) {}

  async createDistrict(data: { name: string; tamilName?: string; status?: boolean; stateId: bigint; createdBy?: bigint }) {
    if (!data.name) {
      throw new Error("District name is required");
    }
    if (!data.stateId) {
      throw new Error("State ID is required");
    }
    const existingDistrict = await this.districtRepository.findByName(data.name);
    if (existingDistrict) {
      throw new Error("District name already exists");
    }
    if (data.tamilName && data.tamilName.trim()) {
      const existingTamilDistrict = await this.districtRepository.findByTamilName(data.tamilName);
      if (existingTamilDistrict) {
        throw new Error("District Tamil name already exists");
      }
    }
    const districtData = {
      ...data,
      name: data.name.trim(),
      tamilName: data.tamilName?.trim() || null,
    };
    return this.districtRepository.create(districtData as any);
  }

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

  async getDistrictById(id: bigint) {
    const district = await this.districtRepository.findById(id);
    if (!district) {
      throw new Error("District not found");
    }
    return district;
  }

  async updateDistrict(id: bigint, data: { name?: string; tamilName?: string; status?: boolean; stateId?: bigint }) {
    const existing = await this.districtRepository.findById(id);
    if (!existing) {
      throw new Error("District not found");
    }

    if (data.name && data.name.trim().toLowerCase() !== existing.name.toLowerCase()) {
      const duplicateDistrict = await this.districtRepository.findByName(data.name, id);
      if (duplicateDistrict) {
        throw new Error("District 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 duplicateTamilDistrict = await this.districtRepository.findByTamilName(data.tamilName, id);
        if (duplicateTamilDistrict) {
          throw new Error("District Tamil name already exists");
        }
      }
    }

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

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

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

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

    // ── Sheet 1: Data entry sheet ──
    const sheet = workbook.addWorksheet("Districts");
    sheet.columns = [
      { header: "State Name *", key: "stateName", width: 28 },
      { header: "District 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({ stateName: "Tamil Nadu", name: "Chennai", tamilName: "சென்னை" });
    sheet.addRow({ stateName: "Tamil Nadu", name: "Coimbatore", tamilName: "கோயம்புத்தூர்" });
    sheet.addRow({ stateName: "Tamil Nadu", name: "Madurai", tamilName: "மதுரை" });
    sheet.addRow({ stateName: "Karnataka", name: "Bangalore Urban", tamilName: "பெங்களூர் நகர்ப்புறம்" });

    // ── Sheet 2: Instructions ──
    const infoSheet = workbook.addWorksheet("Instructions");
    const instructions = [
      "DISTRICT BULK IMPORT — INSTRUCTIONS",
      "",
      "Column Rules:",
      "  State Name *    — Required. Must match an existing State in the system (e.g. Tamil Nadu).",
      "  District 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 districts — names that already exist will be skipped.",
      "  - Duplicate district names and Tamil names within the same file are also skipped.",
      "  - The State must already exist in the system prior to importing districts.",
      "  - 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("Districts") ?? 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 stateColIdx = colIndex["state name"] || colIndex["state"];
    const nameColIdx = colIndex["district name"] || colIndex["name"] || colIndex["district"];
    const tamilNameColIdx = colIndex["tamil name"] || colIndex["district in tamil"] || colIndex["district tamil name"] || colIndex["tamil"];

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

    // Fetch all states for quick lookup
    const allStates = await prisma.state.findMany({
      select: { id: true, name: true, tamilName: true },
    });
    const stateMap = new Map<string, bigint>();
    allStates.forEach((st) => {
      stateMap.set(st.name.trim().toLowerCase(), st.id);
      if (st.tamilName) {
        stateMap.set(st.tamilName.trim().toLowerCase(), st.id);
      }
    });

    const results: DistrictImportRowResult[] = [];
    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 stateName = cellStr(row, stateColIdx);
      const name = cellStr(row, nameColIdx);
      const tamilName = cellStr(row, tamilNameColIdx);

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

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

      // ── Match State ──
      const stateId = stateMap.get(stateName.toLowerCase().trim());
      if (!stateId) {
        results.push({ row: rowNum, name, status: "skipped", reason: `State "${stateName}" does not exist in the system.` });
        skippedCount++;
        continue;
      }

      // ── In-File Duplicate Checks ──
      const nameLower = name.toLowerCase();
      if (seenNames.has(nameLower)) {
        results.push({ row: rowNum, name, status: "skipped", reason: "Duplicate district 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.districtRepository.findByName(name);
      if (existing) {
        results.push({ row: rowNum, name, status: "skipped", reason: "District name already exists in the system." });
        skippedCount++;
        continue;
      }

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

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

      // ── Insert District ──
      await this.createDistrict({
        name,
        tamilName: tamilName || undefined,
        stateId,
        status: true,
        createdBy,
      } as any);

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

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