import { NextFunction, Request, Response } from 'express';
import { ResultSetHeader, RowDataPacket } from 'mysql2';
import { pool } from '../config/db.js';

interface DivisionRow extends RowDataPacket {
  id: number;
  unit_id: number;
  name: string;
  unit_name: string;
}

const SELECT_DIVISIONS = `
  SELECT d.id, d.unit_id, d.name, u.name AS unit_name
  FROM divisions d
  JOIN units u ON u.id = d.unit_id
`;

function toDto(row: DivisionRow) {
  return { id: row.id, unitId: row.unit_id, name: row.name, unitName: row.unit_name };
}

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

export async function list(_req: Request, res: Response, next: NextFunction) {
  try {
    const [rows] = await pool.query<DivisionRow[]>(`${SELECT_DIVISIONS} ORDER BY u.name, d.name`);
    res.json(rows.map(toDto));
  } catch (err) {
    next(err);
  }
}

export async function create(req: Request, res: Response, next: NextFunction) {
  const { unitId, name } = req.body as { unitId?: number; name?: string };
  if (!unitId || !name?.trim()) {
    return res.status(400).json({ message: 'Unit and division name are required' });
  }

  try {
    const [result] = await pool.query<ResultSetHeader>(
      'INSERT INTO divisions (unit_id, name) VALUES (?, ?)',
      [unitId, name.trim()],
    );
    const [rows] = await pool.query<DivisionRow[]>(`${SELECT_DIVISIONS} WHERE d.id = ?`, [result.insertId]);
    res.status(201).json(toDto(rows[0]));
  } catch (err: unknown) {
    const code = mysqlErrorCode(err);
    if (code === 'ER_DUP_ENTRY') {
      return res.status(409).json({ message: 'This division name already exists for the selected unit' });
    }
    if (code === 'ER_NO_REFERENCED_ROW_2' || code === 'ER_NO_REFERENCED_ROW') {
      return res.status(400).json({ message: 'Selected unit does not exist' });
    }
    next(err);
  }
}

export async function update(req: Request, res: Response, next: NextFunction) {
  const id = Number(req.params.id);
  const { unitId, name } = req.body as { unitId?: number; name?: string };
  if (!unitId || !name?.trim()) {
    return res.status(400).json({ message: 'Unit and division name are required' });
  }

  try {
    const [result] = await pool.query<ResultSetHeader>(
      'UPDATE divisions SET unit_id = ?, name = ? WHERE id = ?',
      [unitId, name.trim(), id],
    );
    if (result.affectedRows === 0) {
      return res.status(404).json({ message: 'Division not found' });
    }
    const [rows] = await pool.query<DivisionRow[]>(`${SELECT_DIVISIONS} WHERE d.id = ?`, [id]);
    res.json(toDto(rows[0]));
  } catch (err: unknown) {
    const code = mysqlErrorCode(err);
    if (code === 'ER_DUP_ENTRY') {
      return res.status(409).json({ message: 'This division name already exists for the selected unit' });
    }
    if (code === 'ER_NO_REFERENCED_ROW_2' || code === 'ER_NO_REFERENCED_ROW') {
      return res.status(400).json({ message: 'Selected unit does not exist' });
    }
    next(err);
  }
}

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 divisions WHERE id = ?', [id]);
    if (result.affectedRows === 0) {
      return res.status(404).json({ message: 'Division 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 division is linked to other records and cannot be deleted.' });
    }
    next(err);
  }
}