import { db } from '@server/db/drizzle';
import { sql, type SQL } from 'drizzle-orm';
import { initDatabase } from '@server/db/init';
import { NotFoundError, ValidationError } from '@server/errors';

function stripSipPassword(line: SipLine): SipLine {
  const config = typeof line.sip_config === 'string' ? JSON.parse(line.sip_config) : { ...line.sip_config };
  const { sip_password, ...safeConfig } = config;
  return { ...line, sip_config: safeConfig };
}

async function validateAgentOwnership(restaurantId: string, agentId: string | null | undefined): Promise<void> {
  if (!agentId) return;
  /* raw: SELECT id FROM ai_agents WHERE id = $1 AND restaurant_id = $2 */
  const { rows } = await db.execute(sql`SELECT id FROM ai_agents WHERE id = ${agentId} AND restaurant_id = ${restaurantId}`);
  if (!rows[0]) throw new ValidationError('Agent not found or does not belong to this restaurant');
}

export interface SipLine {
  id: string;
  restaurant_id: string;
  branch_id: string | null;
  number: string;
  display_name: string | null;
  type: string;
  sip_config: {
    sip_server?: string;
    sip_username?: string;
    sip_password?: string;
    outbound_proxy?: string;
    registration_status?: 'registered' | 'unregistered' | 'unknown';
  };
  assigned_agent_id: string | null;
  assigned_agent_name?: string;
  is_active: boolean;
  call_recording: boolean;
  call_transcription: boolean;
  greeting_override: string | null;
  created_at: string;
  updated_at: string;
}

const SIP_SELECT = sql.raw(`
  p.*,
  a.name AS assigned_agent_name
FROM phone_numbers p
LEFT JOIN ai_agents a ON a.id = p.assigned_agent_id
`);

export async function listSipLines(restaurantId: string): Promise<SipLine[]> {
  await initDatabase();
  /* raw: SELECT p.*, a.name AS assigned_agent_name FROM phone_numbers p LEFT JOIN ai_agents a ... WHERE p.restaurant_id = $1 AND p.type = 'sip' ORDER BY p.created_at DESC */
  const { rows } = await db.execute(sql`SELECT ${SIP_SELECT} WHERE p.restaurant_id = ${restaurantId} AND p.type = 'sip' ORDER BY p.created_at DESC`);
  return (rows as unknown as SipLine[]).map(stripSipPassword);
}

export async function getSipLine(id: string, restaurantId: string): Promise<SipLine> {
  await initDatabase();
  /* raw: SELECT p.*, a.name AS assigned_agent_name FROM phone_numbers p LEFT JOIN ai_agents a ... WHERE p.id = $1 AND p.restaurant_id = $2 AND p.type = 'sip' */
  const { rows } = await db.execute(sql`SELECT ${SIP_SELECT} WHERE p.id = ${id} AND p.restaurant_id = ${restaurantId} AND p.type = 'sip'`);
  if (!rows[0]) throw new NotFoundError('SIP line');
  return stripSipPassword(rows[0] as unknown as SipLine);
}

export async function createSipLine(restaurantId: string, data: {
  display_name: string;
  sip_server: string;
  sip_username: string;
  sip_password: string;
  outbound_proxy?: string;
  assigned_agent_id?: string;
  branch_id?: string;
}): Promise<SipLine> {
  await initDatabase();
  if (!data.display_name?.trim()) throw new ValidationError('Display name is required');
  if (!data.sip_server?.trim()) throw new ValidationError('SIP server is required');
  if (!data.sip_username?.trim()) throw new ValidationError('SIP username is required');

  await validateAgentOwnership(restaurantId, data.assigned_agent_id);

  const sipConfig = {
    sip_server: data.sip_server.trim(),
    sip_username: data.sip_username.trim(),
    sip_password: data.sip_password || '',
    outbound_proxy: data.outbound_proxy?.trim() || '',
    registration_status: 'unknown' as const,
  };

  /* raw: INSERT INTO phone_numbers (...) VALUES (...) RETURNING * */
  const { rows } = await db.execute(sql`
    INSERT INTO phone_numbers (restaurant_id, branch_id, number, display_name, type, sip_config, assigned_agent_id, is_active)
    VALUES (${restaurantId}, ${data.branch_id || null}, ${`sip:${data.sip_username}@${data.sip_server}`},
            ${data.display_name.trim()}, 'sip', ${JSON.stringify(sipConfig)}::jsonb, ${data.assigned_agent_id || null}, true)
    RETURNING *
  `);
  const created = rows[0] as unknown as { id: string };
  return getSipLine(created.id, restaurantId);
}

export async function updateSipLine(id: string, restaurantId: string, data: Record<string, unknown>): Promise<SipLine> {
  await initDatabase();
  const existing = await getSipLine(id, restaurantId);

  if (data.assigned_agent_id !== undefined) {
    await validateAgentOwnership(restaurantId, data.assigned_agent_id as string | null);
  }

  const sets: SQL[] = [];

  if (data.display_name !== undefined) sets.push(sql`display_name = ${((data.display_name as string)?.trim() || existing.display_name) as string}`);
  if (data.assigned_agent_id !== undefined) sets.push(sql`assigned_agent_id = ${(data.assigned_agent_id as string) || null}`);
  if (data.is_active !== undefined) sets.push(sql`is_active = ${data.is_active as boolean}`);
  if (data.call_recording !== undefined) sets.push(sql`call_recording = ${data.call_recording as boolean}`);
  if (data.call_transcription !== undefined) sets.push(sql`call_transcription = ${data.call_transcription as boolean}`);
  if (data.greeting_override !== undefined) sets.push(sql`greeting_override = ${((data.greeting_override as string)?.trim() || null) as string | null}`);

  const sipUpdates: Record<string, unknown> = {};
  if (data.sip_server !== undefined) sipUpdates.sip_server = (data.sip_server as string)?.trim();
  if (data.sip_username !== undefined) sipUpdates.sip_username = (data.sip_username as string)?.trim();
  if (data.sip_password !== undefined) sipUpdates.sip_password = data.sip_password;
  if (data.outbound_proxy !== undefined) sipUpdates.outbound_proxy = (data.outbound_proxy as string)?.trim() || '';

  if (Object.keys(sipUpdates).length > 0) {
    const currentConfig = typeof existing.sip_config === 'string'
      ? JSON.parse(existing.sip_config)
      : existing.sip_config || {};
    sets.push(sql`sip_config = ${JSON.stringify({ ...currentConfig, ...sipUpdates })}::jsonb`);

    if (sipUpdates.sip_server || sipUpdates.sip_username) {
      const server = (sipUpdates.sip_server as string) || currentConfig.sip_server || '';
      const username = (sipUpdates.sip_username as string) || currentConfig.sip_username || '';
      sets.push(sql`number = ${`sip:${username}@${server}`}`);
    }
  }

  if (sets.length === 0) throw new ValidationError('No valid fields to update');
  sets.push(sql`updated_at = NOW()`);

  /* raw: UPDATE phone_numbers SET ... WHERE id = $N AND restaurant_id = $N */
  await db.execute(sql`UPDATE phone_numbers SET ${sql.join(sets, sql`, `)} WHERE id = ${id} AND restaurant_id = ${restaurantId}`);

  return getSipLine(id, restaurantId);
}

export async function deleteSipLine(id: string, restaurantId: string): Promise<void> {
  await initDatabase();
  /* raw: DELETE FROM phone_numbers WHERE id = $1 AND restaurant_id = $2 AND type = 'sip' */
  const result = await db.execute(sql`DELETE FROM phone_numbers WHERE id = ${id} AND restaurant_id = ${restaurantId} AND type = 'sip'`);
  if ((result.rowCount ?? 0) === 0) throw new NotFoundError('SIP line');
}
