import type { PoolConnection } from 'mysql2/promise';
import { queryWithConn, executeWithConn } from '../../config/database.js';
import type { Client } from '../types/index.js';

function rowToClient(row: Record<string, unknown>): Client {
  return {
    id: row.id as string,
    name: row.name as string,
    mobile: row.mobile as string,
    alternate_mobile: (row.alternate_mobile as string) ?? undefined,
    email: (row.email as string) ?? undefined,
    send_sms: Boolean(row.send_sms),
  };
}

export async function findAll(
  search?: string,
  page = 1,
  limit = 20,
  conn?: PoolConnection,
): Promise<{ rows: Client[]; total: number }> {
  const offset = (Number(page) - 1) * Number(limit);
  const searchClause = search ? 'WHERE c.deleted_at IS NULL AND (c.name LIKE ? OR c.mobile LIKE ?)' : 'WHERE c.deleted_at IS NULL';
  const params: unknown[] = [];
  if (search) {
    params.push(`%${search}%`, `%${search}%`);
  }

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

  const dataSql = `
    SELECT c.id as id, c.name, c.mobile, c.alternate_mobile, c.email, c.send_sms
    FROM clients c ${searchClause}
    ORDER BY c.name ASC
    LIMIT ? OFFSET ?
  `;
  const dataParams = [...params, Number(limit), Number(offset)];
  const dataRows = await queryWithConn(dataSql, dataParams, conn) as unknown as Record<string, unknown>[];

  return { rows: dataRows.map(rowToClient), total };
}

export async function findById(id: string, conn?: PoolConnection): Promise<Client | null> {
  const sql = `
    SELECT c.id as id, c.name, c.mobile, c.alternate_mobile, c.email, c.send_sms
    FROM clients c
    WHERE c.id = ? AND c.deleted_at IS NULL
  `;
  const rows = await queryWithConn(sql, [id], conn) as unknown as Record<string, unknown>[];
  return rows.length ? rowToClient(rows[0]!) : null;
}

// Batch lookup for callers (e.g. reports) that need many clients by id at
// once — one query instead of one per row, which matters once there are
// thousands of rows to resolve.
export async function findByIds(ids: string[], conn?: PoolConnection): Promise<Client[]> {
  if (ids.length === 0) return [];
  const unique = [...new Set(ids)];
  const sql = `
    SELECT c.id as id, c.name, c.mobile, c.alternate_mobile, c.email, c.send_sms
    FROM clients c
    WHERE c.id IN (${unique.map(() => '?').join(',')}) AND c.deleted_at IS NULL
  `;
  const rows = await queryWithConn(sql, unique, conn) as unknown as Record<string, unknown>[];
  return rows.map(rowToClient);
}

export async function insert(data: Partial<Client>, id: string, createdBy: string, conn?: PoolConnection): Promise<void> {
  const sql = `
    INSERT INTO clients (id, name, mobile, alternate_mobile, email, send_sms, created_by)
    VALUES (?, ?, ?, ?, ?, ?, ?)
  `;
  await executeWithConn(sql, [
    id, data.name, data.mobile,
    data.alternate_mobile ?? null, data.email ?? null,
    data.send_sms ?? false, createdBy,
  ], conn);
}

export async function update(id: string, data: Partial<Client>, 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.alternate_mobile !== undefined) { sets.push('alternate_mobile = ?'); params.push(data.alternate_mobile || null); }
  if (data.email !== undefined) { sets.push('email = ?'); params.push(data.email || null); }
  if (data.send_sms !== undefined) { sets.push('send_sms = ?'); params.push(data.send_sms); }

  if (sets.length === 0) return;

  sets.push('updated_by = ?');
  params.push(updatedBy);

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

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