import ExcelJS from "exceljs";
import prisma from "../config/prisma.js";
import { CustomerRepository } from "../repositories/CustomerRepository.js";
import { Prisma } from "../generated/prisma/client.js";

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

export class CustomerService {
  constructor(private readonly customerRepository = new CustomerRepository()) { }

  async createCustomer(data: any) {
    if (data.name && data.mobileNo) {
      const existing = await this.customerRepository.findByNameAndMobileNo(data.name, data.mobileNo);
      if (existing) {
        throw new Error("Customer with this name and mobile number already exists");
      }
    }
    if (data.openingBalance !== undefined) {
      data.outstandingBalance = data.openingBalance;
    }
    return this.customerRepository.create(data);
  }

  async getCustomers(page: number = 1, limit: number = 10, search?: string, status?: boolean) {
    const skip = (page - 1) * limit;
    const { items, total } = await this.customerRepository.findAll(skip, limit, search, status);

    return {
      data: items,
      meta: {
        total,
        page,
        limit,
        totalPages: Math.ceil(total / limit),
      },
    };
  }

  async getCustomerById(id: bigint) {
    const customer = await this.customerRepository.findById(id);
    if (!customer) {
      throw new Error("Customer not found");
    }
    return customer;
  }

  async updateCustomer(id: bigint, data: any) {
    const existing = await this.customerRepository.findById(id);
    if (!existing) {
      throw new Error("Customer not found");
    }

    const nameToCheck = data.name !== undefined ? data.name : existing.name;
    const mobileToCheck = data.mobileNo !== undefined ? data.mobileNo : existing.mobileNo;

    if (nameToCheck && mobileToCheck) {
      const duplicate = await this.customerRepository.findByNameAndMobileNo(nameToCheck, mobileToCheck, id);
      if (duplicate) {
        throw new Error("Customer with this name and mobile number already exists");
      }
    }

    // If openingBalance is being updated, recalculate outstandingBalance
    // to preserve the net activity (totalSales - totalReceipts) with the new base.
    // Formula: newOutstanding = newOpeningBalance + (oldOutstanding - oldOpeningBalance)
    if (data.openingBalance !== undefined) {
      const newOpeningBalance = new Prisma.Decimal(data.openingBalance);
      const oldOpeningBalance = existing.openingBalance;
      const oldOutstanding = existing.outstandingBalance;
      const netActivity = oldOutstanding.sub(oldOpeningBalance); // totalSales - totalReceipts
      const recalculated = newOpeningBalance.add(netActivity).toDecimalPlaces(2);
      // Clamp to 0: outstandingBalance can never be negative (a negative value would
      // imply the customer has a credit they were never actually given).
      data.outstandingBalance = recalculated.lt(0) ? new Prisma.Decimal(0) : recalculated;
    }

    return this.customerRepository.update(id, data);
  }

  async deleteCustomer(id: bigint) {
    const existing = await this.customerRepository.findById(id);
    if (!existing) {
      throw new Error("Customer not found");
    }
    return this.customerRepository.softDelete(id);
  }

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

    // ── Sheet 1: Data entry sheet ──
    const sheet = workbook.addWorksheet("Customers");
    sheet.columns = [
      { header: "Customer Name *", key: "name", width: 25 },
      { header: "Tamil Name", key: "tamilName", width: 22 },
      { header: "Mobile No *", key: "mobileNo", width: 18, style: { numFmt: "@" } },
      { header: "State Name *", key: "stateName", width: 22 },
      { header: "District Name *", key: "districtName", width: 22 },
      { header: "Address", key: "address", width: 30 },
      { header: "Opening Balance", key: "openingBalance", width: 18 },
    ];

    // Format Mobile No column as text to retain leading zeros/formatting
    sheet.getColumn("mobileNo").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
    sheet.addRow({
      name: "Raja Traders",
      tamilName: "ராஜா டிரேடர்ஸ்",
      mobileNo: "9876543210",
      stateName: "Tamil Nadu",
      districtName: "Madurai",
      address: "123 Main Bazaar",
      openingBalance: 0,
    });
    sheet.addRow({
      name: "Kaveri Retail",
      tamilName: "காவேரி ரீடெய்ல்",
      mobileNo: "9845123456",
      stateName: "Karnataka",
      districtName: "Bangalore Urban",
      address: "45 Market Street",
      openingBalance: 5000,
    });

    sheet.getCell("C2").numFmt = "@";
    sheet.getCell("C3").numFmt = "@";

    // ── Sheet 2: Instructions ──
    const infoSheet = workbook.addWorksheet("Instructions");
    const instructions = [
      "CUSTOMER BULK IMPORT — INSTRUCTIONS",
      "",
      "Column Rules:",
      "  Customer Name *  — Required. Max 255 characters.",
      "  Tamil Name       — Optional. Max 255 characters.",
      "  Mobile No *      — Required. 10-digit mobile number starting with 6-9.",
      "  State Name *     — Required. Must match an existing State in the system (e.g. Tamil Nadu).",
      "  District Name *  — Required. Must match an existing District under the specified State.",
      "  Address          — Optional text.",
      "  Opening Balance  — Optional number (defaults to 0 if not provided).",
      "  Status           — Always set to Active automatically (no column needed).",
      "",
      "Uniqueness Rules:",
      "  - Duplicate check is performed on the combination of Customer Name and Mobile No.",
      "  - Same name with different mobile number is ALLOWED.",
      "  - Same mobile number with different name is ALLOWED.",
      "  - Only if BOTH Customer Name and Mobile No match an existing customer, it will be skipped.",
      "",
      "General Rules:",
      "  - Upload only Excel (.xlsx) files.",
      "  - Maximum file size: 5 MB.",
      "  - All column headers must be present in the sheet.",
    ];
    instructions.forEach((line) => infoSheet.addRow([line]));
    infoSheet.getColumn(1).width = 85;
    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("Customers") ?? 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["customer name"] || colIndex["name"] || colIndex["customer"];
    const tamilNameColIdx = colIndex["tamil name"] || colIndex["customer tamil name"] || colIndex["tamil"];
    const mobileColIdx = colIndex["mobile no"] || colIndex["mobile number"] || colIndex["mobile"] || colIndex["phone"];
    const stateColIdx = colIndex["state name"] || colIndex["state"];
    const districtColIdx = colIndex["district name"] || colIndex["district"];
    const addressColIdx = colIndex["address"];
    const openingBalColIdx = colIndex["opening balance"] || colIndex["opening balance (rs)"] || colIndex["balance"];

    if (!nameColIdx) {
      throw new Error('Missing required column "Customer Name" in the sheet.');
    }
    if (!mobileColIdx) {
      throw new Error('Missing required column "Mobile No" in the sheet.');
    }
    if (!stateColIdx) {
      throw new Error('Missing required column "State Name" in the sheet.');
    }
    if (!districtColIdx) {
      throw new Error('Missing required column "District Name" in the sheet.');
    }

    // Fetch all states with their districts for fast lookup
    const allStates = await prisma.state.findMany({
      select: {
        id: true,
        name: true,
        tamilName: true,
        districts: {
          select: {
            id: true,
            name: true,
            tamilName: true,
          },
        },
      },
    });

    const stateMap = new Map<string, typeof allStates[0]>();
    allStates.forEach((st) => {
      stateMap.set(st.name.trim().toLowerCase(), st);
      if (st.tamilName) {
        stateMap.set(st.tamilName.trim().toLowerCase(), st);
      }
    });

    const results: CustomerImportRowResult[] = [];
    const seenCompoundKeys = 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);
      let mobileNo = cellStr(row, mobileColIdx);
      const stateName = cellStr(row, stateColIdx);
      const districtName = cellStr(row, districtColIdx);
      const address = cellStr(row, addressColIdx);
      const openingBalStr = cellStr(row, openingBalColIdx);

      // Skip row if blank
      if (!name && !mobileNo && !stateName && !districtName) continue;

      // ── Validate Required Fields ──
      if (!name) {
        results.push({ row: rowNum, name: "(empty)", status: "skipped", reason: "Customer Name is required." });
        skippedCount++;
        continue;
      }
      if (name.length > 255) {
        results.push({ row: rowNum, name, status: "skipped", reason: "Customer Name exceeds 255 characters." });
        skippedCount++;
        continue;
      }

      // Clean mobile number
      mobileNo = mobileNo.replace(/\D/g, "");
      if (!mobileNo) {
        results.push({ row: rowNum, name, status: "skipped", reason: "Mobile No is required." });
        skippedCount++;
        continue;
      }
      if (!/^[6-9]\d{9}$/.test(mobileNo)) {
        results.push({ row: rowNum, name, status: "skipped", reason: "Invalid mobile number format (must be 10 digits starting with 6-9)." });
        skippedCount++;
        continue;
      }

      if (!stateName) {
        results.push({ row: rowNum, name, status: "skipped", reason: "State Name is required." });
        skippedCount++;
        continue;
      }
      if (!districtName) {
        results.push({ row: rowNum, name, status: "skipped", reason: "District Name is required." });
        skippedCount++;
        continue;
      }

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

      // ── Match District ──
      const districtLower = districtName.toLowerCase().trim();
      const matchedDistrict = matchedState.districts.find(
        (d) => d.name.toLowerCase().trim() === districtLower || (d.tamilName && d.tamilName.toLowerCase().trim() === districtLower)
      );
      if (!matchedDistrict) {
        results.push({
          row: rowNum,
          name,
          status: "skipped",
          reason: `District "${districtName}" does not exist under state "${matchedState.name}".`,
        });
        skippedCount++;
        continue;
      }

      // ── Compound Uniqueness Check (Name + Mobile No) ──
      const compoundKey = `${name.toLowerCase().trim()}:::${mobileNo}`;
      if (seenCompoundKeys.has(compoundKey)) {
        results.push({
          row: rowNum,
          name,
          status: "skipped",
          reason: "Duplicate customer with same name and mobile number in uploaded file.",
        });
        skippedCount++;
        continue;
      }

      const existingCustomer = await this.customerRepository.findByNameAndMobileNo(name, mobileNo);
      if (existingCustomer) {
        results.push({
          row: rowNum,
          name,
          status: "skipped",
          reason: "Customer with this name and mobile number already exists in the system.",
        });
        skippedCount++;
        continue;
      }

      seenCompoundKeys.add(compoundKey);

      // Parse Opening Balance
      let openingBalance = 0;
      if (openingBalStr) {
        const parsed = parseFloat(openingBalStr);
        if (!isNaN(parsed) && parsed >= 0) {
          openingBalance = parsed;
        }
      }

      // ── Insert Customer ──
      await this.createCustomer({
        name: name.trim(),
        tamilName: tamilName?.trim() || null,
        mobileNo,
        address: address?.trim() || null,
        stateId: matchedState.id,
        districtId: matchedDistrict.id,
        openingBalance,
        status: true,
        createdBy,
      });

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

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