import { NextFunction, Request, Response } from 'express';
import ExcelJS from 'exceljs';
import { ResultSetHeader, RowDataPacket } from 'mysql2/promise';
import { pool } from '../config/db.js';
import { validateClientInput, type ClientInput } from './client.controller.js';

const NAVY = 'FF1F3864';
const GOLD = 'FFFFE699';

// Same options as the client form (frontend/src/features/setup/constants.ts).
const CLIENT_TYPES = [
  'Government', 'PSU', 'EPC', 'A-Grade Contractor', 'Private', 'Public Limited', 'Private Limited', 'Partnership',
  'Proprietorship', 'LLP', 'NGO / Trust', 'Cooperative Society', 'Joint Venture', 'Foreign / MNC', 'Others',
];

/** Template columns, in order. `*` marks the fields the client form requires. */
const COLUMNS: { key: Exclude<keyof ClientInput, 'isActive'> | 'active'; header: string; width: number }[] = [
  { key: 'name', header: 'Client / Company Name *', width: 30 },
  { key: 'clientType', header: 'Client Type *', width: 20 },
  { key: 'industrySector', header: 'Industry Sector', width: 18 },
  { key: 'gstNo', header: 'GST No. *', width: 20 },
  { key: 'panNo', header: 'PAN No. *', width: 14 },
  { key: 'contactPerson', header: 'Contact Person *', width: 20 },
  { key: 'designation', header: 'Designation', width: 16 },
  { key: 'phone', header: 'Phone *', width: 15 },
  { key: 'alternatePhone', header: 'Alternate Phone', width: 15 },
  { key: 'email', header: 'Email *', width: 26 },
  { key: 'address', header: 'Address', width: 30 },
  { key: 'state', header: 'State', width: 16 },
  { key: 'city', header: 'City', width: 14 },
  { key: 'pincode', header: 'Pincode', width: 10 },
  { key: 'country', header: 'Country', width: 12 },
  { key: 'active', header: 'Active (Yes/No)', width: 14 },
];

const HEADER_ROW = 2;
const FIRST_DATA_ROW = 3;
const TEMPLATE_ROWS = 200;

export async function downloadClientTemplate(_req: Request, res: Response, next: NextFunction) {
  try {
    const workbook = new ExcelJS.Workbook();
    const sheet = workbook.addWorksheet('Clients');
    sheet.columns = COLUMNS.map((c) => ({ width: c.width }));

    const note = sheet.getRow(1);
    note.getCell(1).value =
      'CLIENT IMPORT TEMPLATE — one client per row from row 3. Fields marked * are required. GST e.g. 22AAAAA0000A1Z5, PAN e.g. AAAAA0000A. Active defaults to Yes.';
    sheet.mergeCells(1, 1, 1, COLUMNS.length);
    note.getCell(1).font = { bold: true, color: { argb: 'FFFFFFFF' } };
    note.getCell(1).fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: NAVY } };
    note.getCell(1).alignment = { wrapText: true, vertical: 'middle' };
    note.height = 32;

    const header = sheet.getRow(HEADER_ROW);
    COLUMNS.forEach((c, i) => {
      const cell = header.getCell(i + 1);
      cell.value = c.header;
      cell.font = { bold: true };
      cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: GOLD } };
    });
    header.commit();
    sheet.views = [{ state: 'frozen', ySplit: HEADER_ROW }];

    const typeCol = COLUMNS.findIndex((c) => c.key === 'clientType') + 1;
    const activeCol = COLUMNS.findIndex((c) => c.key === 'active') + 1;
    for (let r = FIRST_DATA_ROW; r < FIRST_DATA_ROW + TEMPLATE_ROWS; r++) {
      sheet.getCell(r, typeCol).dataValidation = { type: 'list', allowBlank: true, formulae: [`"${CLIENT_TYPES.join(',')}"`] };
      sheet.getCell(r, activeCol).dataValidation = { type: 'list', allowBlank: true, formulae: ['"Yes,No"'] };
    }

    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    res.setHeader('Content-Disposition', 'attachment; filename="client_import_template.xlsx"');
    await workbook.xlsx.write(res);
    res.end();
  } catch (err) {
    next(err);
  }
}

function cellText(sheet: ExcelJS.Worksheet, row: number, col: number): string {
  const value = sheet.getCell(row, col).value;
  if (value === null || value === undefined) return '';
  if (typeof value === 'object' && 'text' in value) return String((value as { text: unknown }).text ?? '').trim();
  if (typeof value === 'object' && 'result' in value) return String((value as { result: unknown }).result ?? '').trim();
  return String(value).trim();
}

interface ImportError {
  row: number;
  message: string;
}

/**
 * Imports every filled row of the template. All-or-nothing: if any row fails validation
 * (including GST/PAN already used in the file or by an existing client), nothing is saved.
 */
export async function importClients(req: Request, res: Response, next: NextFunction) {
  const file = req.file;
  if (!file) return res.status(400).json({ message: 'A .xlsx file is required.' });

  try {
    const workbook = new ExcelJS.Workbook();
    // exceljs bundles an older Buffer typing that doesn't match @types/node — not a runtime concern.
    // eslint-disable-next-line @typescript-eslint/no-explicit-any
    await workbook.xlsx.load(file.buffer as any);
    const sheet = workbook.worksheets[0];
    if (!sheet) return res.status(400).json({ message: 'The uploaded file has no worksheet.' });

    const [existing] = await pool.query<RowDataPacket[]>('SELECT gst_no, pan_no FROM clients');
    const usedGst = new Set(existing.map((r) => String(r.gst_no ?? '').toUpperCase()).filter(Boolean));
    const usedPan = new Set(existing.map((r) => String(r.pan_no ?? '').toUpperCase()).filter(Boolean));

    const errors: ImportError[] = [];
    const inputs: { row: number; input: ClientInput }[] = [];

    for (let r = FIRST_DATA_ROW; r <= sheet.rowCount; r++) {
      const values = COLUMNS.map((_, i) => cellText(sheet, r, i + 1));
      if (values.every((v) => v === '')) continue; // blank row

      const input: ClientInput = {};
      COLUMNS.forEach((c, i) => {
        if (c.key === 'active') input.isActive = !/^(no|n|false|0|inactive)$/i.test(values[i]);
        else if (values[i]) input[c.key] = values[i];
      });

      const problem = validateClientInput(input);
      if (problem) {
        errors.push({ row: r, message: problem });
        continue;
      }
      if (input.clientType && !CLIENT_TYPES.includes(input.clientType)) {
        errors.push({ row: r, message: `Client type "${input.clientType}" is not one of the allowed options.` });
        continue;
      }
      const gst = input.gstNo!.trim().toUpperCase();
      const pan = input.panNo!.trim().toUpperCase();
      if (usedGst.has(gst)) {
        errors.push({ row: r, message: `GST No. ${gst} is already used by another client.` });
        continue;
      }
      if (usedPan.has(pan)) {
        errors.push({ row: r, message: `PAN No. ${pan} is already used by another client.` });
        continue;
      }
      usedGst.add(gst);
      usedPan.add(pan);
      inputs.push({ row: r, input });
    }

    if (errors.length > 0) {
      return res.status(400).json({ message: 'Import failed — fix the listed rows and try again. No clients were added.', errors });
    }
    if (inputs.length === 0) {
      return res.status(400).json({ message: 'The file has no client rows. Fill the template from row 3.' });
    }

    const conn = await pool.getConnection();
    try {
      await conn.beginTransaction();
      for (const { input } of inputs) {
        const [result] = await conn.query<ResultSetHeader>(
          `INSERT INTO clients
            (name, client_type, industry_sector, gst_no, pan_no, contact_person, designation, phone, alternate_phone, email, address, state, city, pincode, country, is_active)
           VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
          [
            input.name!.trim(),
            input.clientType ?? null,
            input.industrySector ?? null,
            input.gstNo!.trim().toUpperCase(),
            input.panNo!.trim().toUpperCase(),
            input.contactPerson ?? null,
            input.designation ?? null,
            input.phone!.trim(),
            input.alternatePhone ?? null,
            input.email ?? null,
            input.address ?? null,
            input.state ?? null,
            input.city ?? null,
            input.pincode ?? null,
            input.country?.trim() || 'India',
            input.isActive === false ? 0 : 1,
          ],
        );
        const clientCode = `CLI${String(result.insertId).padStart(4, '0')}`;
        await conn.query('UPDATE clients SET client_code = ? WHERE id = ?', [clientCode, result.insertId]);
      }
      await conn.commit();
      res.status(201).json({ ok: true, created: inputs.length });
    } catch (err) {
      await conn.rollback();
      throw err;
    } finally {
      conn.release();
    }
  } catch (err) {
    next(err);
  }
}