import { NextFunction, Request, Response } from 'express';
import { PoolConnection, ResultSetHeader, 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';

interface LineItemInput {
  description?: string;
  unit?: string;
  qty?: number;
  aceRate?: number;
  saleRate?: number;
}

interface OverheadInput {
  categoryId?: number;
  subCategory?: string;
  ctcPerMonth?: number;
  nos?: number;
  month?: number;
}

interface ProjectInput {
  name?: string;
  clientId?: number;
  state?: string;
  location?: string;
  startDate?: string;
  finishDate?: string;
  unitId?: number;
  divisionId?: number;
  projectManagerId?: number;
  scopeDescription?: string;
  customerPoNo?: string;
  poDate?: string;
  supply?: LineItemInput[];
  civil?: LineItemInput[];
  installation?: LineItemInput[];
  tc?: LineItemInput[];
  overheads?: OverheadInput[];
}

interface ProjectRow extends RowDataPacket {
  id: number;
  job_code: string | null;
  name: string;
  client_id: number;
  client_name: string | null;
  state: string | null;
  location: string | null;
  start_date: string;
  finish_date: string;
  unit_id: number | null;
  unit_name: string | null;
  division_id: number | null;
  division_name: string | null;
  project_manager_id: number | null;
  pm_name: string | null;
  scope_description: string | null;
  customer_po_no: string | null;
  po_date: string | null;
}

interface ProjectListRow extends RowDataPacket {
  id: number;
  job_code: string | null;
  name: string;
  start_date: string;
  finish_date: string;
  client_name: string | null;
  pm_name: string | null;
  ace_total: string;
  sale_total: string;
}

interface LineItemRow extends RowDataPacket {
  id: number;
  section: LineItemSection;
  sno: number;
  description: string;
  unit: string;
  qty: string;
  ace_rate: string;
  sale_rate: string;
}

interface OverheadRow extends RowDataPacket {
  id: number;
  sno: number;
  category_id: number;
  category_code: string | null;
  category_description: string | null;
  sub_category: string | null;
  ctc_per_month: string;
  nos: number;
  month: string;
}

interface JobCodeRow extends RowDataPacket {
  job_code: string;
}

function mysqlErrorCode(err: unknown): string | undefined {
  return err && typeof err === 'object' && 'code' in err ? (err as { code?: string }).code : undefined;
}

function financialYearLabel(date: Date): string {
  const year = date.getFullYear();
  const month = date.getMonth() + 1; // 1-12
  return month >= 4 ? `${year}-${year + 1}` : `${year - 1}-${year}`;
}

/**
 * Next sequence number continues from the highest job code ever issued this
 * financial year — not a row count — so a deleted project never frees up its
 * number for reuse and can't collide with one still on the books.
 */
async function nextJobCode(conn: PoolConnection, fy: string): Promise<string> {
  const [rows] = await conn.query<JobCodeRow[]>(
    'SELECT job_code FROM projects WHERE job_code LIKE ? ORDER BY job_code DESC LIMIT 1',
    [`VPMT_${fy}_%`],
  );
  const lastSeq = rows[0] ? parseInt(rows[0].job_code.split('_').pop()!, 10) || 0 : 0;
  return `VPMT_${fy}_${String(lastSeq + 1).padStart(3, '0')}`;
}

function validateProject(input: ProjectInput): string | null {
  if (!input.name?.trim()) return 'Project name is required';
  if (!input.clientId) return 'Client is required';
  if (!input.startDate) return 'Start date is required';
  if (!input.finishDate) return 'Finish date is required';
  if (new Date(input.finishDate) < new Date(input.startDate)) {
    return 'Finish date cannot be before the start date';
  }

  const overheads = input.overheads ?? [];
  if (overheads.length === 0) {
    return 'Overheads is mandatory — add at least one entry';
  }

  const sectionCounts = LINE_ITEM_SECTIONS.map((key) => (input[key] ?? []).length);
  if (sectionCounts.every((n) => n === 0)) {
    return 'Add at least one entry to Supply, Civil Works, Installation or T & C';
  }

  for (const key of LINE_ITEM_SECTIONS) {
    const rows = input[key] ?? [];
    for (const [i, row] of rows.entries()) {
      const label = `${SECTION_LABELS[key]} row ${i + 1}`;
      if (!row.description?.trim()) return `${label}: description is required`;
      if (!row.unit || !LINE_ITEM_UNITS.includes(row.unit)) return `${label}: a valid unit is required`;
      if (!(Number(row.qty) > 0)) return `${label}: quantity must be greater than 0`;
      if (!(Number(row.aceRate) > 0)) return `${label}: ACE rate must be greater than 0`;
      if (!(Number(row.saleRate) > Number(row.aceRate))) return `${label}: sale rate must be higher than the ACE rate`;
    }
  }

  for (const [i, row] of overheads.entries()) {
    const label = `Overheads row ${i + 1}`;
    if (!row.categoryId) return `${label}: category is required`;
    if (!(Number(row.ctcPerMonth) > 0)) return `${label}: CTC/M must be greater than 0`;
    if (!(Number(row.nos) > 0)) return `${label}: Nos must be greater than 0`;
    if (!(Number(row.month) > 0)) return `${label}: Month must be greater than 0`;
  }

  return null;
}

async function insertLineItemsAndOverheads(conn: PoolConnection, projectId: number, input: ProjectInput) {
  for (const key of LINE_ITEM_SECTIONS) {
    const rows = input[key] ?? [];
    for (const [i, row] of rows.entries()) {
      await conn.query(
        `INSERT INTO project_line_items (project_id, section, sno, description, unit, qty, ace_rate, sale_rate)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
        [projectId, key, i + 1, row.description!.trim(), row.unit, row.qty, row.aceRate, row.saleRate],
      );
    }
  }
  const overheads = input.overheads ?? [];
  for (const [i, row] of overheads.entries()) {
    await conn.query(
      `INSERT INTO project_overheads (project_id, sno, category_id, sub_category, ctc_per_month, nos, month)
       VALUES (?, ?, ?, ?, ?, ?, ?)`,
      [projectId, i + 1, row.categoryId, row.subCategory?.trim() || null, row.ctcPerMonth, row.nos, row.month],
    );
  }
}

function toLineItemDto(row: LineItemRow) {
  return {
    id: row.id,
    description: row.description,
    unit: row.unit,
    qty: Number(row.qty),
    aceRate: Number(row.ace_rate),
    saleRate: Number(row.sale_rate),
  };
}

function toOverheadDto(row: OverheadRow) {
  return {
    id: row.id,
    categoryId: row.category_id,
    categoryCode: row.category_code,
    categoryDescription: row.category_description,
    subCategory: row.sub_category,
    ctcPerMonth: Number(row.ctc_per_month),
    nos: Number(row.nos),
    month: Number(row.month),
  };
}

async function fetchProjectDto(id: number) {
  const [projectRows] = await pool.query<ProjectRow[]>(
    `SELECT p.*, c.name AS client_name, un.name AS unit_name, d.name AS division_name, pm.name AS pm_name
     FROM projects p
     LEFT JOIN clients c ON c.id = p.client_id
     LEFT JOIN units un ON un.id = p.unit_id
     LEFT JOIN divisions d ON d.id = p.division_id
     LEFT JOIN users pm ON pm.id = p.project_manager_id
     WHERE p.id = ?`,
    [id],
  );
  const project = projectRows[0];
  if (!project) return null;

  const [lineItemRows] = await pool.query<LineItemRow[]>(
    'SELECT * FROM project_line_items WHERE project_id = ? ORDER BY section, sno',
    [id],
  );
  const [overheadRows] = await pool.query<OverheadRow[]>(
    `SELECT o.*, cat.item_code AS category_code, cat.description AS category_description
     FROM project_overheads o
     LEFT JOIN categories cat ON cat.id = o.category_id
     WHERE o.project_id = ?
     ORDER BY o.sno`,
    [id],
  );

  const bySection = (key: LineItemSection) => lineItemRows.filter((r) => r.section === key).map(toLineItemDto);

  return {
    id: project.id,
    jobCode: project.job_code,
    name: project.name,
    clientId: project.client_id,
    clientName: project.client_name,
    state: project.state,
    location: project.location,
    startDate: project.start_date,
    finishDate: project.finish_date,
    unitId: project.unit_id,
    unitName: project.unit_name,
    divisionId: project.division_id,
    divisionName: project.division_name,
    projectManagerId: project.project_manager_id,
    projectManagerName: project.pm_name,
    scopeDescription: project.scope_description,
    customerPoNo: project.customer_po_no,
    poDate: project.po_date,
    supply: bySection('supply'),
    civil: bySection('civil'),
    installation: bySection('installation'),
    tc: bySection('tc'),
    overheads: overheadRows.map(toOverheadDto),
  };
}

export async function list(_req: Request, res: Response, next: NextFunction) {
  try {
    const [rows] = await pool.query<ProjectListRow[]>(
      `SELECT p.id, p.job_code, p.name, p.start_date, p.finish_date,
              c.name AS client_name, pm.name AS pm_name,
              COALESCE((SELECT SUM(qty * ace_rate) FROM project_line_items WHERE project_id = p.id), 0) AS ace_total,
              COALESCE((SELECT SUM(qty * sale_rate) FROM project_line_items WHERE project_id = p.id), 0) AS sale_total
       FROM projects p
       LEFT JOIN clients c ON c.id = p.client_id
       LEFT JOIN users pm ON pm.id = p.project_manager_id
       ORDER BY p.id DESC`,
    );
    res.json(
      rows.map((row) => ({
        id: row.id,
        jobCode: row.job_code,
        name: row.name,
        startDate: row.start_date,
        finishDate: row.finish_date,
        clientName: row.client_name,
        projectManagerName: row.pm_name,
        aceTotal: Number(row.ace_total),
        saleTotal: Number(row.sale_total),
      })),
    );
  } catch (err) {
    next(err);
  }
}

export async function getOne(req: Request, res: Response, next: NextFunction) {
  const id = Number(req.params.id);
  try {
    const dto = await fetchProjectDto(id);
    if (!dto) return res.status(404).json({ message: 'Project not found' });
    res.json(dto);
  } catch (err) {
    next(err);
  }
}

export async function create(req: Request, res: Response, next: NextFunction) {
  const input = req.body as ProjectInput;
  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<ResultSetHeader>(
      `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.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(await fetchProjectDto(projectId));
  } 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' });
    }
    next(err);
  } finally {
    conn.release();
  }
}

export async function update(req: Request, res: Response, next: NextFunction) {
  const id = Number(req.params.id);
  const input = req.body as ProjectInput;
  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<ResultSetHeader>(
      `UPDATE projects SET
        name = ?, client_id = ?, state = ?, location = ?, start_date = ?, finish_date = ?,
        unit_id = ?, division_id = ?, project_manager_id = ?, scope_description = ?, customer_po_no = ?, po_date = ?
       WHERE id = ?`,
      [
        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,
        id,
      ],
    );
    if (result.affectedRows === 0) {
      await conn.rollback();
      return res.status(404).json({ message: 'Project not found' });
    }

    await conn.query('DELETE FROM project_line_items WHERE project_id = ?', [id]);
    await conn.query('DELETE FROM project_overheads WHERE project_id = ?', [id]);
    await insertLineItemsAndOverheads(conn, id, input);

    await conn.commit();
    res.json(await fetchProjectDto(id));
  } 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' });
    }
    next(err);
  } finally {
    conn.release();
  }
}

export async function remove(req: Request, res: Response, next: NextFunction) {
  const id = Number(req.params.id);
  try {
    const [result] = await pool.query<ResultSetHeader>('DELETE FROM projects WHERE id = ?', [id]);
    if (result.affectedRows === 0) {
      return res.status(404).json({ message: 'Project not found' });
    }
    res.status(204).send();
  } catch (err: unknown) {
    const code = mysqlErrorCode(err);
    if (code === 'ER_ROW_IS_REFERENCED_2' || code === 'ER_ROW_IS_REFERENCED') {
      return res.status(409).json({ message: 'This project is linked to other records and cannot be deleted.' });
    }
    next(err);
  }
}