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

interface ClientRow extends RowDataPacket {
  id: number;
  client_code: string | null;
  name: string;
  client_type: string | null;
  industry_sector: string | null;
  gst_no: string | null;
  pan_no: string | null;
  contact_person: string | null;
  designation: string | null;
  phone: string;
  alternate_phone: string | null;
  email: string | null;
  address: string | null;
  state: string | null;
  city: string | null;
  pincode: string | null;
  country: string;
  is_active: number;
}

export interface ClientInput {
  name?: string;
  clientType?: string;
  industrySector?: string;
  gstNo?: string;
  panNo?: string;
  contactPerson?: string;
  designation?: string;
  phone?: string;
  alternatePhone?: string;
  email?: string;
  address?: string;
  state?: string;
  city?: string;
  pincode?: string;
  country?: string;
  isActive?: boolean;
}

const COLUMNS =
  'id, client_code, name, client_type, industry_sector, gst_no, pan_no, contact_person, designation, phone, alternate_phone, email, address, state, city, pincode, country, is_active';

function toDto(row: ClientRow) {
  return {
    id: row.id,
    clientCode: row.client_code,
    name: row.name,
    clientType: row.client_type,
    industrySector: row.industry_sector,
    gstNo: row.gst_no,
    panNo: row.pan_no,
    contactPerson: row.contact_person,
    designation: row.designation,
    phone: row.phone,
    alternatePhone: row.alternate_phone,
    email: row.email,
    address: row.address,
    state: row.state,
    city: row.city,
    pincode: row.pincode,
    country: row.country,
    isActive: !!row.is_active,
  };
}

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

function duplicateFieldError(err: unknown): { field: string; message: string } | null {
  if (mysqlErrorCode(err) !== 'ER_DUP_ENTRY') return null;
  const text = String((err as { sqlMessage?: string; message?: string }).sqlMessage ?? (err as Error).message ?? '');
  if (text.includes('uq_clients_gst_no')) {
    return { field: 'gstNo', message: 'This GST No. is already used by another client' };
  }
  if (text.includes('uq_clients_pan_no')) {
    return { field: 'panNo', message: 'This PAN No. is already used by another client' };
  }
  return { field: 'unknown', message: 'A client with these details already exists' };
}

type StringClientField = Exclude<keyof ClientInput, 'isActive'>;

const REQUIRED_FIELDS: { key: StringClientField; label: string }[] = [
  { key: 'name', label: 'Client / Company name' },
  { key: 'clientType', label: 'Client type' },
  { key: 'gstNo', label: 'GST No.' },
  { key: 'panNo', label: 'PAN No.' },
  { key: 'contactPerson', label: 'Contact person' },
  { key: 'phone', label: 'Phone' },
  { key: 'email', label: 'Email' },
];

const GST_REGEX = /^[0-9]{2}[A-Z]{5}[0-9]{4}[A-Z]{1}[1-9A-Z]{1}Z[0-9A-Z]{1}$/;
const PAN_REGEX = /^[A-Z]{5}[0-9]{4}[A-Z]{1}$/;

export function validateClientInput(input: ClientInput): string | null {
  const missing = REQUIRED_FIELDS.filter((f) => !input[f.key]?.trim());
  if (missing.length > 0) {
    return `${missing.map((f) => f.label).join(', ')} ${missing.length > 1 ? 'are' : 'is'} required`;
  }
  if (!GST_REGEX.test(input.gstNo!.trim().toUpperCase())) {
    return 'GST No. format is invalid (e.g. 22AAAAA0000A1Z5)';
  }
  if (!PAN_REGEX.test(input.panNo!.trim().toUpperCase())) {
    return 'PAN No. format is invalid (e.g. AAAAA0000A)';
  }
  return null;
}

export async function list(_req: Request, res: Response, next: NextFunction) {
  try {
    const [rows] = await pool.query<ClientRow[]>(`SELECT ${COLUMNS} FROM clients ORDER BY id DESC`);
    res.json(rows.map(toDto));
  } catch (err) {
    next(err);
  }
}

export async function getOne(req: Request, res: Response, next: NextFunction) {
  const id = Number(req.params.id);
  try {
    const [rows] = await pool.query<ClientRow[]>(`SELECT ${COLUMNS} FROM clients WHERE id = ? LIMIT 1`, [id]);
    if (!rows[0]) return res.status(404).json({ message: 'Client not found' });
    res.json(toDto(rows[0]));
  } catch (err) {
    next(err);
  }
}

export async function create(req: Request, res: Response, next: NextFunction) {
  const input = req.body as ClientInput;
  const validationError = validateClientInput(input);
  if (validationError) {
    return res.status(400).json({ message: validationError });
  }

  try {
    const [result] = await pool.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 pool.query('UPDATE clients SET client_code = ? WHERE id = ?', [clientCode, result.insertId]);

    const [rows] = await pool.query<ClientRow[]>(`SELECT ${COLUMNS} FROM clients WHERE id = ?`, [result.insertId]);
    res.status(201).json(toDto(rows[0]));
  } catch (err) {
    const dup = duplicateFieldError(err);
    if (dup) {
      return res.status(409).json({ message: dup.message, field: dup.field });
    }
    next(err);
  }
}

export async function update(req: Request, res: Response, next: NextFunction) {
  const id = Number(req.params.id);
  const input = req.body as ClientInput;
  const validationError = validateClientInput(input);
  if (validationError) {
    return res.status(400).json({ message: validationError });
  }

  try {
    const [result] = await pool.query<ResultSetHeader>(
      `UPDATE clients SET
        name = ?, client_type = ?, industry_sector = ?, gst_no = ?, pan_no = ?, contact_person = ?,
        designation = ?, phone = ?, alternate_phone = ?, email = ?, address = ?, state = ?, city = ?,
        pincode = ?, country = ?, is_active = ?
       WHERE id = ?`,
      [
        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,
        id,
      ],
    );
    if (result.affectedRows === 0) {
      return res.status(404).json({ message: 'Client not found' });
    }

    const [rows] = await pool.query<ClientRow[]>(`SELECT ${COLUMNS} FROM clients WHERE id = ?`, [id]);
    res.json(toDto(rows[0]));
  } catch (err) {
    const dup = duplicateFieldError(err);
    if (dup) {
      return res.status(409).json({ message: dup.message, field: dup.field });
    }
    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 clients WHERE id = ?', [id]);
    if (result.affectedRows === 0) {
      return res.status(404).json({ message: 'Client 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 client is linked to other records and cannot be deleted.' });
    }
    next(err);
  }
}