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

interface UserRow extends RowDataPacket {
  id: number;
  name: string | null;
  designation: string | null;
  mobile_number: string | null;
  email: string;
  photo_path: string | null;
  role_id: number | null;
  role_name: string | null;
  is_active: number;
}

interface UnitLinkRow extends RowDataPacket {
  user_id: number;
  id: number;
  name: string;
}

interface DivisionLinkRow extends RowDataPacket {
  user_id: number;
  id: number;
  name: string;
  unit_id: number;
}

const SELECT_USERS = `
  SELECT u.id, u.name, u.designation, u.mobile_number, u.email, u.photo_path, u.is_active,
         u.role_id, r.name AS role_name
  FROM users u
  LEFT JOIN roles r ON r.id = u.role_id
`;

// The seeded developer login — never surfaced in the Users UI or reachable through it.
const HIDDEN_EMAIL = 'dev@mail.com';

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

function toIntOrNull(value: unknown): number | null {
  if (value === undefined || value === null || value === '') return null;
  const n = Number(value);
  return Number.isFinite(n) ? n : null;
}

/** Multipart repeated fields arrive as a string, a string[], or are absent. */
function toIntArray(value: unknown): number[] {
  const arr = Array.isArray(value) ? value : value === undefined || value === null || value === '' ? [] : [value];
  return arr.map((v) => Number(v)).filter((n) => Number.isFinite(n));
}

async function attachUnitsAndDivisions(users: UserRow[]) {
  if (users.length === 0) return [];
  const ids = users.map((u) => u.id);

  const [unitRows] = await pool.query<UnitLinkRow[]>(
    `SELECT uu.user_id, un.id, un.name FROM user_units uu JOIN units un ON un.id = uu.unit_id WHERE uu.user_id IN (?)`,
    [ids],
  );
  const [divisionRows] = await pool.query<DivisionLinkRow[]>(
    `SELECT ud.user_id, d.id, d.name, d.unit_id FROM user_divisions ud JOIN divisions d ON d.id = ud.division_id WHERE ud.user_id IN (?)`,
    [ids],
  );

  const unitsByUser = new Map<number, { id: number; name: string }[]>();
  for (const r of unitRows) {
    if (!unitsByUser.has(r.user_id)) unitsByUser.set(r.user_id, []);
    unitsByUser.get(r.user_id)!.push({ id: r.id, name: r.name });
  }
  const divisionsByUser = new Map<number, { id: number; name: string; unitId: number }[]>();
  for (const r of divisionRows) {
    if (!divisionsByUser.has(r.user_id)) divisionsByUser.set(r.user_id, []);
    divisionsByUser.get(r.user_id)!.push({ id: r.id, name: r.name, unitId: r.unit_id });
  }

  return users.map((u) => ({
    id: u.id,
    fullName: u.name,
    designation: u.designation,
    mobileNumber: u.mobile_number,
    email: u.email,
    photoUrl: u.photo_path,
    roleId: u.role_id,
    roleName: u.role_name,
    units: unitsByUser.get(u.id) ?? [],
    divisions: divisionsByUser.get(u.id) ?? [],
    isActive: !!u.is_active,
  }));
}

async function replaceUnitsAndDivisions(userId: number, unitIds: number[], divisionIds: number[]) {
  await pool.query('DELETE FROM user_units WHERE user_id = ?', [userId]);
  await pool.query('DELETE FROM user_divisions WHERE user_id = ?', [userId]);
  for (const unitId of unitIds) {
    await pool.query('INSERT INTO user_units (user_id, unit_id) VALUES (?, ?)', [userId, unitId]);
  }
  for (const divisionId of divisionIds) {
    await pool.query('INSERT INTO user_divisions (user_id, division_id) VALUES (?, ?)', [userId, divisionId]);
  }
}

export async function list(_req: Request, res: Response, next: NextFunction) {
  try {
    const [rows] = await pool.query<UserRow[]>(`${SELECT_USERS} WHERE u.email <> ? ORDER BY u.id DESC`, [
      HIDDEN_EMAIL,
    ]);
    res.json(await attachUnitsAndDivisions(rows));
  } 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<UserRow[]>(`${SELECT_USERS} WHERE u.id = ? AND u.email <> ? LIMIT 1`, [
      id,
      HIDDEN_EMAIL,
    ]);
    if (!rows[0]) return res.status(404).json({ message: 'User not found' });
    const [dto] = await attachUnitsAndDivisions(rows);
    res.json(dto);
  } catch (err) {
    next(err);
  }
}

export async function create(req: Request, res: Response, next: NextFunction) {
  const body = req.body as Record<string, unknown>;
  const email = (body.email as string)?.trim();
  const unitIds = toIntArray(body.unitIds);
  const divisionIds = toIntArray(body.divisionIds);
  const password = (body.password as string) ?? '';
  const confirmPassword = (body.confirmPassword as string) ?? '';

  if (!email || unitIds.length === 0 || divisionIds.length === 0 || !password || !confirmPassword) {
    return res.status(400).json({ message: 'Email, unit, division, password and confirm password are required' });
  }
  if (password !== confirmPassword) {
    return res.status(400).json({ message: 'Password and confirm password do not match' });
  }
  if (password.length < 6) {
    return res.status(400).json({ message: 'Password must be at least 6 characters' });
  }

  const photoPath = req.file ? `/uploads/users/${req.file.filename}` : null;
  const passwordHash = await bcrypt.hash(password, 10);

  try {
    const [result] = await pool.query<ResultSetHeader>(
      `INSERT INTO users (name, designation, mobile_number, email, password_hash, photo_path, role_id)
       VALUES (?, ?, ?, ?, ?, ?, ?)`,
      [
        (body.fullName as string)?.trim() || null,
        (body.designation as string)?.trim() || null,
        (body.mobileNumber as string)?.trim() || null,
        email,
        passwordHash,
        photoPath,
        toIntOrNull(body.roleId),
      ],
    );
    await replaceUnitsAndDivisions(result.insertId, unitIds, divisionIds);

    const [rows] = await pool.query<UserRow[]>(`${SELECT_USERS} WHERE u.id = ?`, [result.insertId]);
    const [dto] = await attachUnitsAndDivisions(rows);
    res.status(201).json(dto);
  } catch (err: unknown) {
    if (mysqlErrorCode(err) === 'ER_DUP_ENTRY') {
      return res.status(409).json({ message: 'A user with this email already exists' });
    }
    next(err);
  }
}

export async function update(req: Request, res: Response, next: NextFunction) {
  const id = Number(req.params.id);
  const body = req.body as Record<string, unknown>;
  const email = (body.email as string)?.trim();
  const unitIds = toIntArray(body.unitIds);
  const divisionIds = toIntArray(body.divisionIds);
  const password = (body.password as string) ?? '';
  const confirmPassword = (body.confirmPassword as string) ?? '';

  if (!email || unitIds.length === 0 || divisionIds.length === 0) {
    return res.status(400).json({ message: 'Email, unit and division are required' });
  }
  if (password || confirmPassword) {
    if (password !== confirmPassword) {
      return res.status(400).json({ message: 'Password and confirm password do not match' });
    }
    if (password.length < 6) {
      return res.status(400).json({ message: 'Password must be at least 6 characters' });
    }
  }

  try {
    const sets = ['name = ?', 'designation = ?', 'mobile_number = ?', 'email = ?', 'role_id = ?'];
    const params: unknown[] = [
      (body.fullName as string)?.trim() || null,
      (body.designation as string)?.trim() || null,
      (body.mobileNumber as string)?.trim() || null,
      email,
      toIntOrNull(body.roleId),
    ];

    if (req.file) {
      sets.push('photo_path = ?');
      params.push(`/uploads/users/${req.file.filename}`);
    }
    if (password) {
      sets.push('password_hash = ?');
      params.push(await bcrypt.hash(password, 10));
    }

    params.push(id, HIDDEN_EMAIL);
    const [result] = await pool.query<ResultSetHeader>(
      `UPDATE users SET ${sets.join(', ')} WHERE id = ? AND email <> ?`,
      params,
    );
    if (result.affectedRows === 0) {
      return res.status(404).json({ message: 'User not found' });
    }
    await replaceUnitsAndDivisions(id, unitIds, divisionIds);

    const [rows] = await pool.query<UserRow[]>(`${SELECT_USERS} WHERE u.id = ?`, [id]);
    const [dto] = await attachUnitsAndDivisions(rows);
    res.json(dto);
  } catch (err: unknown) {
    if (mysqlErrorCode(err) === 'ER_DUP_ENTRY') {
      return res.status(409).json({ message: 'A user with this email 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 users WHERE id = ? AND email <> ?', [
      id,
      HIDDEN_EMAIL,
    ]);
    if (result.affectedRows === 0) {
      return res.status(404).json({ message: 'User 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 user is linked to other records and cannot be deleted.' });
    }
    next(err);
  }
}