import { NextFunction, Request, Response } from 'express';
import ExcelJS from 'exceljs';
import { RowDataPacket } from 'mysql2/promise';
import { pool } from '../config/db.js';
import { LINE_ITEM_SECTIONS, LINE_ITEM_UNITS, SECTION_LABELS, type LineItemSection } from '../constants/lineItems.js';
import {
  financialYearLabel,
  insertLineItemsAndOverheads,
  mysqlErrorCode,
  nextJobCode,
  validateProject,
  type LineItemInput,
  type OverheadInput,
  type ProjectInput,
} from './project.controller.js';

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

const BASIC_FIELD_ROWS = {
  name: 4,
  client: 5,
  state: 6,
  location: 7,
  startDate: 8,
  finishDate: 9,
  unit: 10,
  division: 11,
  projectManager: 12,
  scopeDescription: 13,
  customerPoNo: 14,
  poDate: 15,
} as const;

interface SectionLayout {
  key: LineItemSection;
  label: string;
  bannerRow: number;
  headerRow: number;
  dataStartRow: number;
  dataEndRow: number;
}

const ROWS_PER_SECTION = 15;
const SECTION_LAYOUT: SectionLayout[] = (() => {
  let row = 17;
  return LINE_ITEM_SECTIONS.map((key) => {
    const bannerRow = row;
    const headerRow = row + 1;
    const dataStartRow = row + 2;
    const dataEndRow = dataStartRow + ROWS_PER_SECTION - 1;
    row = dataEndRow + 2;
    return { key, label: SECTION_LABELS[key], bannerRow, headerRow, dataStartRow, dataEndRow };
  });
})();

const OVERHEADS_LAYOUT = (() => {
  const bannerRow = SECTION_LAYOUT[SECTION_LAYOUT.length - 1].dataEndRow + 2;
  const headerRow = bannerRow + 1;
  const dataStartRow = bannerRow + 2;
  const dataEndRow = dataStartRow + ROWS_PER_SECTION - 1;
  return { bannerRow, headerRow, dataStartRow, dataEndRow };
})();

function bannerRow(sheet: ExcelJS.Worksheet, rowNum: number, label: string, cols: number) {
  const row = sheet.getRow(rowNum);
  row.getCell(1).value = label;
  sheet.mergeCells(rowNum, 1, rowNum, cols);
  row.eachCell({ includeEmpty: true }, (cell) => {
    cell.font = { bold: true, color: { argb: 'FFFFFFFF' } };
    cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: NAVY } };
  });
  row.commit();
}

function headerRow(sheet: ExcelJS.Worksheet, rowNum: number, headers: string[]) {
  const row = sheet.getRow(rowNum);
  headers.forEach((h, i) => {
    row.getCell(i + 1).value = h;
  });
  row.eachCell({ includeEmpty: true }, (cell) => {
    cell.font = { bold: true };
    cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: GOLD } };
  });
  row.commit();
}

/** Separator between a category's item code and description in the template dropdown. */
const CATEGORY_SEPARATOR = ' — ';

const DATE_FORMAT = 'dd-mm-yyyy';

/**
 * Writes each list into its own column of the hidden "Lists" sheet and returns, per list, the
 * absolute range to use as a dropdown source (or null when the list is empty).
 */
function addListsSheet(workbook: ExcelJS.Workbook, lists: { title: string; values: string[] }[]) {
  const sheet = workbook.addWorksheet('Lists', { state: 'hidden' });
  return lists.map(({ title, values: all }, i) => {
    const values = [...new Set(all)];
    const col = i + 1;
    const letter = sheet.getColumn(col).letter;
    sheet.getCell(1, col).value = title;
    values.forEach((v, j) => (sheet.getCell(j + 2, col).value = v));
    sheet.getColumn(col).width = 30;
    return values.length > 0 ? `Lists!$${letter}$2:$${letter}$${values.length + 1}` : null;
  });
}

function listValidation(cell: ExcelJS.Cell, range: string | null, title: string) {
  if (!range) return;
  cell.dataValidation = {
    type: 'list',
    allowBlank: true,
    formulae: [range],
    showErrorMessage: true,
    errorStyle: 'error',
    errorTitle: title,
    error: `Choose a ${title.toLowerCase()} from the dropdown list.`,
  };
}

function dateValidation(cell: ExcelJS.Cell, title: string) {
  cell.numFmt = DATE_FORMAT;
  cell.dataValidation = {
    type: 'date',
    operator: 'greaterThan',
    allowBlank: true,
    formulae: [new Date(Date.UTC(2000, 0, 1))],
    showInputMessage: true,
    promptTitle: title,
    prompt: 'Enter the date as dd-mm-yyyy (e.g. 20-08-2026).',
    showErrorMessage: true,
    errorStyle: 'error',
    errorTitle: title,
    error: 'Enter a valid date as dd-mm-yyyy.',
  };
}

export async function downloadTemplate(_req: Request, res: Response, next: NextFunction) {
  try {
    const [clients, units, divisions, users, categories] = await Promise.all([
      pool.query<RowDataPacket[]>('SELECT name FROM clients WHERE is_active = 1 ORDER BY name'),
      pool.query<RowDataPacket[]>('SELECT name FROM units ORDER BY name'),
      pool.query<RowDataPacket[]>('SELECT name FROM divisions ORDER BY name'),
      pool.query<RowDataPacket[]>('SELECT name FROM users WHERE is_active = 1 ORDER BY name'),
      pool.query<RowDataPacket[]>('SELECT item_code, description FROM categories ORDER BY item_code'),
    ]).then((results) => results.map((r) => r[0]));

    const workbook = new ExcelJS.Workbook();
    const sheet = workbook.addWorksheet('Project');
    const [clientList, unitList, divisionList, pmList, categoryList, lineUnitList, originList] = addListsSheet(workbook, [
      { title: 'Clients', values: clients.map((r) => String(r.name)) },
      { title: 'Units', values: units.map((r) => String(r.name)) },
      { title: 'Divisions', values: divisions.map((r) => String(r.name)) },
      { title: 'Project Managers', values: users.map((r) => String(r.name)) },
      {
        title: 'Categories',
        values: categories.map((r) => `${r.item_code}${r.description ? `${CATEGORY_SEPARATOR}${r.description}` : ''}`),
      },
      { title: 'Line Units', values: [...LINE_ITEM_UNITS] },
      { title: 'Origin', values: ['foreign', 'local'] },
    ]);
    sheet.columns = [{ width: 30 }, { width: 22 }, { width: 14 }, { width: 14 }, { width: 14 }, { width: 12 }];

    const titleRow = sheet.getRow(1);
    titleRow.getCell(1).value = 'PROJECT IMPORT TEMPLATE — one project per file. Pick Client, Unit, Division, Project Manager, Unit of measure, Origin and Category from the dropdowns; enter dates as dd-mm-yyyy.';
    sheet.mergeCells(1, 1, 1, 6);
    titleRow.getCell(1).font = { bold: true };
    titleRow.getCell(1).alignment = { wrapText: true };
    titleRow.height = 30;

    bannerRow(sheet, 3, 'BASIC DETAILS', 6);
    const basicLabels: Record<keyof typeof BASIC_FIELD_ROWS, string> = {
      name: 'Project Name',
      client: 'Client',
      state: 'State',
      location: 'Location',
      startDate: 'Start Date (dd-mm-yyyy)',
      finishDate: 'Finish Date (dd-mm-yyyy)',
      unit: 'Unit',
      division: 'Division',
      projectManager: 'Project Manager',
      scopeDescription: 'Scope / Description',
      customerPoNo: 'Customer PO No.',
      poDate: 'PO Date (dd-mm-yyyy)',
    };
    (Object.keys(BASIC_FIELD_ROWS) as (keyof typeof BASIC_FIELD_ROWS)[]).forEach((key) => {
      const rowNum = BASIC_FIELD_ROWS[key];
      const row = sheet.getRow(rowNum);
      row.getCell(1).value = basicLabels[key];
      row.getCell(1).font = { bold: true };
      row.commit();
    });

    // Dropdowns from the database, and date cells with date validation
    listValidation(sheet.getCell(BASIC_FIELD_ROWS.client, 2), clientList, 'Client');
    listValidation(sheet.getCell(BASIC_FIELD_ROWS.unit, 2), unitList, 'Unit');
    listValidation(sheet.getCell(BASIC_FIELD_ROWS.division, 2), divisionList, 'Division');
    listValidation(sheet.getCell(BASIC_FIELD_ROWS.projectManager, 2), pmList, 'Project Manager');
    dateValidation(sheet.getCell(BASIC_FIELD_ROWS.startDate, 2), 'Start Date');
    dateValidation(sheet.getCell(BASIC_FIELD_ROWS.finishDate, 2), 'Finish Date');
    dateValidation(sheet.getCell(BASIC_FIELD_ROWS.poDate, 2), 'PO Date');

    for (const section of SECTION_LAYOUT) {
      bannerRow(sheet, section.bannerRow, section.label, 6);
      headerRow(sheet, section.headerRow, ['Description', 'Unit', 'Qty', 'ACE Rate', 'Sale Rate', 'Origin (foreign/local)']);
      for (let r = section.dataStartRow; r <= section.dataEndRow; r++) {
        sheet.getRow(r).commit();
        listValidation(sheet.getCell(r, 2), lineUnitList, 'Unit');
        listValidation(sheet.getCell(r, 6), originList, 'Origin');
      }
    }

    bannerRow(sheet, OVERHEADS_LAYOUT.bannerRow, 'OVERHEADS', 6);
    headerRow(sheet, OVERHEADS_LAYOUT.headerRow, ['Category (item code)', 'Sub Category', 'CTC per Month', 'Nos', 'Month']);
    for (let r = OVERHEADS_LAYOUT.dataStartRow; r <= OVERHEADS_LAYOUT.dataEndRow; r++) {
      sheet.getRow(r).commit();
      listValidation(sheet.getCell(r, 1), categoryList, 'Category');
    }

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

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

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

/** A date cell as YYYY-MM-DD: accepts real Excel dates, dd-mm-yyyy / dd/mm/yyyy text and YYYY-MM-DD. */
function cellDate(sheet: ExcelJS.Worksheet, row: number, col: number): string {
  const text = cellText(sheet, row, col);
  const dmy = /^(\d{1,2})[-/.](\d{1,2})[-/.](\d{4})$/.exec(text);
  if (dmy) return `${dmy[3]}-${dmy[2].padStart(2, '0')}-${dmy[1].padStart(2, '0')}`;
  return text;
}

function cellNumber(sheet: ExcelJS.Worksheet, row: number, col: number): number | undefined {
  const text = cellText(sheet, row, col);
  if (!text) return undefined;
  const n = Number(text);
  return Number.isFinite(n) ? n : undefined;
}

export async function importProject(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 its own (older, non-generic) Buffer typing that doesn't
    // structurally match the current @types/node Buffer<ArrayBufferLike> —
    // a known cross-package type mismatch, not a real 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 errors: ImportError[] = [];

    const basicText = (key: keyof typeof BASIC_FIELD_ROWS) => cellText(sheet, BASIC_FIELD_ROWS[key], 2);

    const [clients, units, divisions, users, categories] = await Promise.all([
      pool.query<RowDataPacket[]>('SELECT id, name FROM clients'),
      pool.query<RowDataPacket[]>('SELECT id, name FROM units'),
      pool.query<RowDataPacket[]>('SELECT id, name, unit_id FROM divisions'),
      pool.query<RowDataPacket[]>('SELECT id, name FROM users'),
      pool.query<RowDataPacket[]>('SELECT id, item_code FROM categories'),
    ]).then((results) => results.map((r) => r[0]));

    const byNameLower = (rows: RowDataPacket[], field: string) =>
      new Map(rows.map((r) => [String(r[field]).toLowerCase(), r]));

    const clientByName = byNameLower(clients, 'name');
    const unitByName = byNameLower(units, 'name');
    const divisionByName = byNameLower(divisions, 'name');
    const userByName = byNameLower(users, 'name');
    const categoryByCode = byNameLower(categories, 'item_code');

    const clientName = basicText('client');
    const client = clientName ? clientByName.get(clientName.toLowerCase()) : undefined;
    if (clientName && !client) errors.push({ row: BASIC_FIELD_ROWS.client, message: `Client "${clientName}" not found.` });

    const unitName = basicText('unit');
    const unit = unitName ? unitByName.get(unitName.toLowerCase()) : undefined;
    if (unitName && !unit) errors.push({ row: BASIC_FIELD_ROWS.unit, message: `Unit "${unitName}" not found.` });

    const divisionName = basicText('division');
    const division = divisionName ? divisionByName.get(divisionName.toLowerCase()) : undefined;
    if (divisionName && !division) {
      errors.push({ row: BASIC_FIELD_ROWS.division, message: `Division "${divisionName}" not found.` });
    }

    const pmName = basicText('projectManager');
    const pm = pmName ? userByName.get(pmName.toLowerCase()) : undefined;
    if (pmName && !pm) errors.push({ row: BASIC_FIELD_ROWS.projectManager, message: `Project Manager "${pmName}" not found.` });

    const input: ProjectInput = {
      name: basicText('name'),
      clientId: client?.id as number | undefined,
      state: basicText('state'),
      location: basicText('location'),
      startDate: cellDate(sheet, BASIC_FIELD_ROWS.startDate, 2),
      finishDate: cellDate(sheet, BASIC_FIELD_ROWS.finishDate, 2),
      unitId: unit?.id as number | undefined,
      divisionId: division?.id as number | undefined,
      projectManagerId: pm?.id as number | undefined,
      scopeDescription: basicText('scopeDescription'),
      customerPoNo: basicText('customerPoNo'),
      poDate: cellDate(sheet, BASIC_FIELD_ROWS.poDate, 2),
      supply: [],
      civil: [],
      installation: [],
      tc: [],
      overheads: [],
    };

    for (const section of SECTION_LAYOUT) {
      const rows: LineItemInput[] = [];
      for (let r = section.dataStartRow; r <= section.dataEndRow; r++) {
        const description = cellText(sheet, r, 1);
        if (!description) continue;
        const unitCell = cellText(sheet, r, 2);
        const origin = cellText(sheet, r, 6).toLowerCase();
        rows.push({
          description,
          unit: unitCell,
          qty: cellNumber(sheet, r, 3),
          aceRate: cellNumber(sheet, r, 4),
          saleRate: cellNumber(sheet, r, 5),
          origin: origin === 'foreign' || origin === 'local' ? (origin as 'foreign' | 'local') : null,
        });
      }
      input[section.key] = rows;
    }

    const overheadRows: OverheadInput[] = [];
    for (let r = OVERHEADS_LAYOUT.dataStartRow; r <= OVERHEADS_LAYOUT.dataEndRow; r++) {
      // The dropdown shows "CODE — Description"; only the code identifies the category
      const code = cellText(sheet, r, 1).split(CATEGORY_SEPARATOR)[0].trim();
      if (!code) continue;
      const category = categoryByCode.get(code.toLowerCase());
      if (!category) {
        errors.push({ row: r, message: `Category "${code}" not found.` });
        continue;
      }
      overheadRows.push({
        categoryId: category.id as number,
        subCategory: cellText(sheet, r, 2),
        ctcPerMonth: cellNumber(sheet, r, 3),
        nos: cellNumber(sheet, r, 4),
        month: cellNumber(sheet, r, 5),
      });
    }
    input.overheads = overheadRows;

    if (errors.length > 0) {
      return res.status(400).json({ message: 'Import failed — fix the highlighted rows and try again.', errors });
    }

    const validationError = validateProject(input);
    if (validationError) {
      return res.status(400).json({ message: validationError });
    }

    const conn = await pool.getConnection();
    try {
      await conn.beginTransaction();

      const [result] = await conn.query(
        `INSERT INTO projects
          (name, client_id, state, location, start_date, finish_date, unit_id, division_id, project_manager_id, scope_description, customer_po_no, po_date)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [
          input.name!.trim(),
          input.clientId,
          input.state || null,
          input.location || null,
          input.startDate,
          input.finishDate,
          input.unitId || null,
          input.divisionId || null,
          input.projectManagerId || null,
          input.scopeDescription || null,
          input.customerPoNo || null,
          input.poDate || null,
        ],
      );
      const projectId = (result as { insertId: number }).insertId;

      const jobCode = await nextJobCode(conn, financialYearLabel(new Date()));
      await conn.query('UPDATE projects SET job_code = ? WHERE id = ?', [jobCode, projectId]);

      await insertLineItemsAndOverheads(conn, projectId, input);

      await conn.commit();
      res.status(201).json({ ok: true, projectId, jobCode });
    } catch (err) {
      await conn.rollback();
      if (mysqlErrorCode(err) === 'ER_NO_REFERENCED_ROW_2') {
        return res.status(400).json({ message: 'Selected client, unit, division or project manager does not exist' });
      }
      throw err;
    } finally {
      conn.release();
    }
  } catch (err) {
    next(err);
  }
}