import type { PoolConnection } from 'mysql2/promise';
import { queryWithConn, executeWithConn } from '../../config/database.js';
import { findByEmployeeId as findPermissionOverrides } from './employeePermissionOverride.repo.js';
import { findByEmployeeId as findStageTransitions } from './employeeStageTransition.repo.js';
import { PERMISSIONS } from '../constants/permissions.js';
import type { Employee } from '../types/index.js';

function rowToEmployee(row: Record<string, unknown>): Employee {
  return {
    id: row.id as string,
    code: row.code as string,
    name: row.name as string,
    mobile: row.mobile as string,
    email: (row.email as string) ?? undefined,
    role_id: row.role_id as string,
    auth_enabled: Boolean(row.auth_enabled),
    user_id: (row.user_id as string) ?? undefined,
    pending_delivery_stage_id: (row.pending_delivery_stage_id as string) ?? null,
  };
}

export interface AuthContext {
  id: string;
  name: string;
  role_id: string;
  role_name: string;
  permissions: string[] | '*';
  pending_delivery_stage_id: string | null;
}

// Resolves an employee's *current* identity, role, and permissions in one
// query — used on every authenticated request instead of trusting the JWT's
// embedded claims, so a revoked role, changed permissions, or a
// deleted/disabled account takes effect immediately rather than only after
// the token expires. Returns null if the employee or their role is gone or
// the account has been disabled — the caller should treat that as "no
// longer authenticated".
export async function findAuthContext(employeeId: string, conn?: PoolConnection): Promise<AuthContext | null> {
  const sql = `
    SELECT e.id as id, e.name as name, e.role_id as role_id,
           e.pending_delivery_stage_id as pending_delivery_stage_id,
           r.name as role_name, r.permissions as permissions
    FROM employees e
    JOIN roles r ON r.id = e.role_id AND r.deleted_at IS NULL
    WHERE e.id = ? AND e.deleted_at IS NULL AND e.auth_enabled = TRUE
  `;
  const rows = await queryWithConn(sql, [employeeId], conn) as unknown as Record<string, unknown>[];
  if (!rows.length) return null;
  const row = rows[0]!;

  let permissions: string[] | '*' = [];
  if (typeof row.permissions === 'string') {
    permissions = row.permissions === '*' ? '*' : JSON.parse(row.permissions);
  } else {
    permissions = (row.permissions as string[]) ?? [];
  }
  // Legacy seed data stored the wildcard as ['*'] instead of the bare
  // string '*', and a since-fixed bug could carry that stray '*' entry into
  // an otherwise-normal permissions array on save — either way, an array
  // containing the literal '*' string means "everything", so normalize to
  // the canonical '*' regardless of what else is in the array (matches
  // role.repo.ts).
  if (Array.isArray(permissions) && permissions.includes('*')) {
    permissions = '*';
  }

  // Per-employee grant/deny overrides layered on top of the role's own
  // permissions — cheap fast path (no override rows, the overwhelming
  // common case) leaves the role's permissions completely untouched.
  const overrides = await findPermissionOverrides(row.id as string, conn);
  if (overrides.length > 0) {
    const grants = overrides.filter((o) => o.effect === 'grant').map((o) => o.permission);
    const denies = new Set(overrides.filter((o) => o.effect === 'deny').map((o) => o.permission));

    if (permissions === '*') {
      // '*' + denies: there's nothing to "add" (already has everything), but
      // a deny has to actually remove access, so '*' can't be kept literally.
      permissions = denies.size > 0
        ? Object.values(PERMISSIONS).filter((p) => !denies.has(p))
        : '*';
    } else {
      const merged = new Set(permissions);
      for (const g of grants) merged.add(g);
      for (const d of denies) merged.delete(d);
      permissions = [...merged];
    }
  }

  return {
    id: row.id as string,
    name: row.name as string,
    role_id: row.role_id as string,
    role_name: row.role_name as string,
    permissions,
    pending_delivery_stage_id: (row.pending_delivery_stage_id as string) ?? null,
  };
}

// Used to block deleting a role that's still assigned to an employee — an
// employee left pointing at a deleted role fails to resolve permissions and
// can no longer log in.
export async function hasEmployeesWithRole(roleId: string, conn?: PoolConnection): Promise<boolean> {
  const rows = await queryWithConn(
    'SELECT 1 FROM employees WHERE role_id = ? AND deleted_at IS NULL LIMIT 1',
    [roleId], conn,
  ) as unknown as unknown[];
  return rows.length > 0;
}

export async function findAll(
  search?: string,
  roleId?: string,
  page = 1,
  limit = 20,
  conn?: PoolConnection,
): Promise<{ rows: Employee[]; total: number }> {
  const offset = (Number(page) - 1) * Number(limit);
  const conditions = ['e.deleted_at IS NULL'];
  const params: unknown[] = [];

  if (search) {
    conditions.push('(e.name LIKE ? OR e.code LIKE ? OR e.mobile LIKE ?)');
    params.push(`%${search}%`, `%${search}%`, `%${search}%`);
  }
  if (roleId) {
    conditions.push('e.role_id = ?');
    params.push(roleId);
  }

  const where = `WHERE ${conditions.join(' AND ')}`;

  const countRows = await queryWithConn(`SELECT COUNT(*) as total FROM employees e ${where}`, params.length ? params : undefined, conn) as unknown as { total: number }[];
  const total = countRows[0]?.total ?? 0;

  const dataSql = `
    SELECT e.id as id, e.code, e.name, e.mobile, e.email,
           e.role_id as role_id, e.auth_enabled, e.user_id, e.pending_delivery_stage_id
    FROM employees e ${where}
    ORDER BY e.code ASC
    LIMIT ? OFFSET ?
  `;
  const dataRows = await queryWithConn(dataSql, [...params, Number(limit), Number(offset)], conn) as unknown as Record<string, unknown>[];
  const rows = dataRows.map(rowToEmployee);

  // One batched query for the whole page instead of one per row — the list
  // still needs each employee's overrides so re-opening the edit modal
  // (which is seeded from this list, not a fresh per-id fetch) reflects
  // what's actually saved rather than always looking empty.
  if (rows.length > 0) {
    const ids = rows.map((r) => r.id);
    const overrideRows = await queryWithConn(
      `SELECT employee_id, permission, effect FROM employee_permission_overrides
       WHERE employee_id IN (${ids.map(() => '?').join(',')}) AND deleted_at IS NULL`,
      ids, conn,
    ) as unknown as { employee_id: string; permission: string; effect: 'grant' | 'deny' }[];

    if (overrideRows.length > 0) {
      const byEmployee = new Map<string, { permission: string; effect: 'grant' | 'deny' }[]>();
      for (const o of overrideRows) {
        const list = byEmployee.get(o.employee_id) ?? [];
        list.push({ permission: o.permission, effect: o.effect });
        byEmployee.set(o.employee_id, list);
      }
      for (const row of rows) {
        const overrides = byEmployee.get(row.id);
        if (overrides) row.permission_overrides = overrides;
      }
    }

    const transitionRows = await queryWithConn(
      `SELECT employee_id, from_stage_id, to_stage_id FROM employee_stage_transitions
       WHERE employee_id IN (${ids.map(() => '?').join(',')}) AND deleted_at IS NULL`,
      ids, conn,
    ) as unknown as { employee_id: string; from_stage_id: string; to_stage_id: string }[];

    if (transitionRows.length > 0) {
      const byEmployee = new Map<string, { fromStageId: string; toStageId: string }[]>();
      for (const t of transitionRows) {
        const list = byEmployee.get(t.employee_id) ?? [];
        list.push({ fromStageId: t.from_stage_id, toStageId: t.to_stage_id });
        byEmployee.set(t.employee_id, list);
      }
      for (const row of rows) {
        const transitions = byEmployee.get(row.id);
        if (transitions) row.stage_transitions = transitions;
      }
    }
  }

  return { rows, total };
}

export async function findById(id: string, conn?: PoolConnection): Promise<Employee | null> {
  const sql = `
    SELECT e.id as id, e.code, e.name, e.mobile, e.email,
           e.role_id as role_id, e.auth_enabled, e.user_id, e.pending_delivery_stage_id
    FROM employees e
    WHERE e.id = ? AND e.deleted_at IS NULL
  `;
  const rows = await queryWithConn(sql, [id], conn) as unknown as Record<string, unknown>[];
  if (!rows.length) return null;
  const employee = rowToEmployee(rows[0]!);
  // Overrides are only needed for the single-employee fetch (edit form,
  // create/update response) — the list endpoint intentionally skips this to
  // avoid an extra query per row.
  const overrides = await findPermissionOverrides(id, conn);
  if (overrides.length > 0) {
    employee.permission_overrides = overrides.map((o) => ({ permission: o.permission, effect: o.effect }));
  }
  const transitions = await findStageTransitions(id, conn);
  if (transitions.length > 0) {
    employee.stage_transitions = transitions;
  }
  return employee;
}

// Batch lookup for callers (e.g. reports) that need many employees by id at
// once — one query instead of one per row.
export async function findByIds(ids: string[], conn?: PoolConnection): Promise<Employee[]> {
  if (ids.length === 0) return [];
  const unique = [...new Set(ids)];
  const sql = `
    SELECT e.id as id, e.code, e.name, e.mobile, e.email,
           e.role_id as role_id, e.auth_enabled, e.user_id, e.pending_delivery_stage_id
    FROM employees e
    WHERE e.id IN (${unique.map(() => '?').join(',')}) AND e.deleted_at IS NULL
  `;
  const rows = await queryWithConn(sql, unique, conn) as unknown as Record<string, unknown>[];
  return rows.map(rowToEmployee);
}

export async function findByUserId(userId: string, conn?: PoolConnection): Promise<Employee | null> {
  const sql = `
    SELECT e.id as id, e.code, e.name, e.mobile, e.email,
           e.role_id as role_id, e.auth_enabled, e.user_id, e.pending_delivery_stage_id
    FROM employees e
    WHERE e.user_id = ? AND e.deleted_at IS NULL
  `;
  const rows = await queryWithConn(sql, [userId], conn) as unknown as Record<string, unknown>[];
  return rows.length ? rowToEmployee(rows[0]!) : null;
}

export async function findByUserIdWithPassword(userId: string, conn?: PoolConnection): Promise<(Employee & { password_hash?: string }) | null> {
  const sql = `
    SELECT e.id as id, e.code, e.name, e.mobile, e.email,
           e.role_id as role_id, e.auth_enabled, e.user_id, e.password_hash
    FROM employees e
    WHERE e.user_id = ? AND e.deleted_at IS NULL
  `;
  const rows = await queryWithConn(sql, [userId], conn) as unknown as Record<string, unknown>[];
  if (!rows.length) return null;
  const r = rows[0]!;
  return {
    ...rowToEmployee(r),
    password_hash: (r.password_hash as string) ?? undefined,
  };
}

// Sorted by the numeric suffix, not the code string itself — a plain
// `ORDER BY code DESC` compares text character-by-character, so "KCE-9"
// sorts *above* "KCE-10" (same reason "apple" sorts before "banana"), which
// would make this silently return a stale "highest" code once double-digit
// codes exist. Matches the same numeric-suffix trick utils/id.ts's nextId()
// already uses for every other generated id in this app.
export async function findMaxCode(conn?: PoolConnection): Promise<string | null> {
  const rows = await queryWithConn(
    "SELECT code FROM employees WHERE deleted_at IS NULL ORDER BY CAST(SUBSTRING_INDEX(code, '-', -1) AS UNSIGNED) DESC LIMIT 1",
    undefined, conn,
  ) as unknown as { code: string }[];
  return rows[0]?.code ?? null;
}

export async function insert(data: Partial<Employee> & { password_hash?: string }, id: string, createdBy: string, conn?: PoolConnection): Promise<void> {
  const sql = `
    INSERT INTO employees (id, code, name, mobile, email, role_id, auth_enabled, user_id, password_hash, pending_delivery_stage_id, created_by)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  `;
  await executeWithConn(sql, [
    id, data.code, data.name, data.mobile,
    data.email ?? null, data.role_id,
    data.auth_enabled ?? false, data.user_id ?? null,
    (data as { password_hash?: string }).password_hash ?? null,
    data.pending_delivery_stage_id ?? null,
    createdBy,
  ], conn);
}

export async function update(id: string, data: Partial<Employee> & { password_hash?: string }, updatedBy: string, conn?: PoolConnection): Promise<void> {
  const sets: string[] = [];
  const params: unknown[] = [];

  if (data.name !== undefined) { sets.push('name = ?'); params.push(data.name); }
  if (data.mobile !== undefined) { sets.push('mobile = ?'); params.push(data.mobile); }
  if (data.email !== undefined) { sets.push('email = ?'); params.push(data.email || null); }
  if (data.role_id !== undefined) { sets.push('role_id = ?'); params.push(data.role_id); }
  if (data.auth_enabled !== undefined) { sets.push('auth_enabled = ?'); params.push(data.auth_enabled); }
  if (data.user_id !== undefined) { sets.push('user_id = ?'); params.push(data.user_id || null); }
  if (data.pending_delivery_stage_id !== undefined) { sets.push('pending_delivery_stage_id = ?'); params.push(data.pending_delivery_stage_id || null); }
  if ((data as { password_hash?: string }).password_hash !== undefined) {
    sets.push('password_hash = ?');
    params.push((data as { password_hash?: string }).password_hash);
  }

  if (sets.length === 0) return;
  sets.push('updated_by = ?');
  params.push(updatedBy);
  params.push(id);

  await executeWithConn(`UPDATE employees SET ${sets.join(', ')} WHERE id = ? AND deleted_at IS NULL`, params, conn);
}

export async function softDelete(id: string, deletedBy: string, conn?: PoolConnection): Promise<void> {
  await executeWithConn('UPDATE employees SET deleted_at = NOW(), deleted_by = ? WHERE id = ? AND deleted_at IS NULL', [deletedBy, id], conn);
}