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

interface CategoryRow extends RowDataPacket {
  id: number;
  item_code: string;
  description: string;
}

function toDto(row: CategoryRow) {
  return { id: row.id, itemCode: row.item_code, description: row.description };
}

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<CategoryRow[]>('SELECT id, item_code, description FROM categories ORDER BY id');
    res.json(rows.map(toDto));
  } catch (err) {
    next(err);
  }
}

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

  try {
    const [result] = await pool.query<ResultSetHeader>(
      'INSERT INTO categories (item_code, description) VALUES (?, ?)',
      [itemCode.trim(), description.trim()],
    );
    res.status(201).json({ id: result.insertId, itemCode: itemCode.trim(), description: description.trim() });
  } catch (err: unknown) {
    if (mysqlErrorCode(err) === 'ER_DUP_ENTRY') {
      return res.status(409).json({ message: 'Item code already exists' });
    }
    next(err);
  }
}

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

  try {
    const [result] = await pool.query<ResultSetHeader>(
      'UPDATE categories SET item_code = ?, description = ? WHERE id = ?',
      [itemCode.trim(), description.trim(), id],
    );
    if (result.affectedRows === 0) {
      return res.status(404).json({ message: 'Category not found' });
    }
    res.json({ id, itemCode: itemCode.trim(), description: description.trim() });
  } catch (err: unknown) {
    if (mysqlErrorCode(err) === 'ER_DUP_ENTRY') {
      return res.status(409).json({ message: 'Item code already exists' });
    }
    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 categories WHERE id = ?', [id]);
    if (result.affectedRows === 0) {
      return res.status(404).json({ message: 'Category 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 category is linked to other records and cannot be deleted.' });
    }
    next(err);
  }
}