import { db } from '@server/db/drizzle';
import { sql, type SQL } from 'drizzle-orm';
import { initDatabase } from '@server/db/init';
import { ValidationError } from '@server/errors';
import { encrypt, decrypt } from '@server/utils/crypto';
import { bindValue } from '@server/db/bind';

import { childLogger } from '@server/logger';
const log = childLogger('svc.telephone.twilio-phone');

export interface TelephoneSettings {
  id: string;
  restaurant_id: string;
  twilio_connected: boolean;
  twilio_account_sid_hint: string | null;
  twilio_account_sid: string | null;
  twilio_auth_token: string | null;
  created_at: string;
  updated_at: string;
}

export interface TwilioNumber {
  sid: string;
  phone_number: string;
  friendly_name: string;
  capabilities: { voice: boolean; sms: boolean };
}

export async function getTelephoneSettings(restaurantId: string): Promise<TelephoneSettings> {
  await initDatabase();
  /* raw: SELECT * FROM telephone_settings WHERE restaurant_id = $1 */
  const { rows } = await db.execute(sql`SELECT * FROM telephone_settings WHERE restaurant_id = ${restaurantId}`);
  if (rows[0]) return rows[0] as unknown as TelephoneSettings;

  /* raw: INSERT INTO telephone_settings (restaurant_id) VALUES ($1) ON CONFLICT ... DO UPDATE SET updated_at = NOW() RETURNING * */
  const { rows: insertRows } = await db.execute(sql`
    INSERT INTO telephone_settings (restaurant_id) VALUES (${restaurantId})
    ON CONFLICT (restaurant_id) DO UPDATE SET updated_at = NOW()
    RETURNING *
  `);
  return insertRows[0] as unknown as TelephoneSettings;
}

export function sanitizeSettings(settings: TelephoneSettings): Omit<TelephoneSettings, 'twilio_account_sid' | 'twilio_auth_token'> {
  const { twilio_account_sid, twilio_auth_token, ...safe } = settings;
  return safe;
}

async function decryptCredentials(settings: TelephoneSettings): Promise<{ accountSid: string; authToken: string } | null> {
  if (!settings.twilio_connected || !settings.twilio_account_sid || !settings.twilio_auth_token) {
    return null;
  }
  try {
    return {
      accountSid: decrypt(settings.twilio_account_sid),
      authToken: decrypt(settings.twilio_auth_token),
    };
  } catch {
    return null;
  }
}

export async function getValidatedSettings(restaurantId: string): Promise<{ settings: Omit<TelephoneSettings, 'twilio_account_sid' | 'twilio_auth_token'>; valid: boolean }> {
  const settings = await getTelephoneSettings(restaurantId);
  const safe = sanitizeSettings(settings);

  if (!settings.twilio_connected) {
    return { settings: safe, valid: false };
  }

  const creds = await decryptCredentials(settings);
  if (!creds) {
    /* raw: UPDATE telephone_settings SET twilio_connected = false, ... WHERE restaurant_id = $1 */
    await db.execute(sql`UPDATE telephone_settings SET twilio_connected = false, twilio_account_sid_hint = NULL, twilio_account_sid = NULL, twilio_auth_token = NULL, updated_at = NOW() WHERE restaurant_id = ${restaurantId}`);
    return { settings: { ...safe, twilio_connected: false, twilio_account_sid_hint: null }, valid: false };
  }

  const check = await verifyTwilioCredentials(creds.accountSid, creds.authToken);
  if (!check.ok) {
    /* raw: UPDATE telephone_settings SET twilio_connected = false, updated_at = NOW() WHERE restaurant_id = $1 */
    await db.execute(sql`UPDATE telephone_settings SET twilio_connected = false, updated_at = NOW() WHERE restaurant_id = ${restaurantId}`);
    return { settings: { ...safe, twilio_connected: false }, valid: false };
  }

  return { settings: safe, valid: true };
}

export async function saveTwilioCredentials(restaurantId: string, accountSid: string, authToken: string): Promise<{ connected: boolean; error?: string }> {
  await initDatabase();
  if (!accountSid?.trim() || !authToken?.trim()) {
    throw new ValidationError('Account SID and Auth Token are required');
  }

  const valid = await verifyTwilioCredentials(accountSid.trim(), authToken.trim());
  if (!valid.ok) {
    return { connected: false, error: valid.error || 'Invalid Twilio credentials' };
  }

  const hint = accountSid.trim().slice(0, 6) + '...' + accountSid.trim().slice(-4);
  const encSid = encrypt(accountSid.trim());
  const encToken = encrypt(authToken.trim());

  /* raw: INSERT INTO telephone_settings (...) VALUES (...) ON CONFLICT (...) DO UPDATE SET ... */
  await db.execute(sql`
    INSERT INTO telephone_settings (restaurant_id, twilio_connected, twilio_account_sid_hint, twilio_account_sid, twilio_auth_token)
    VALUES (${restaurantId}, true, ${hint}, ${encSid}, ${encToken})
    ON CONFLICT (restaurant_id) DO UPDATE SET twilio_connected = true, twilio_account_sid_hint = ${hint}, twilio_account_sid = ${encSid}, twilio_auth_token = ${encToken}, updated_at = NOW()
  `);

  /* raw: SELECT id FROM phone_numbers WHERE restaurant_id = $1 AND is_active = true AND assigned_agent_id IS NOT NULL AND twilio_number_sid IS NOT NULL */
  const { rows: eligibleNumbers } = await db.execute(sql`
    SELECT id FROM phone_numbers WHERE restaurant_id = ${restaurantId} AND is_active = true AND assigned_agent_id IS NOT NULL AND twilio_number_sid IS NOT NULL
  `);
  for (const num of eligibleNumbers as { id: string }[]) {
    try { await provisionWebhookForNumber(num.id, restaurantId); } catch { /* best-effort */ }
  }

  return { connected: true };
}

export async function disconnectTwilio(restaurantId: string): Promise<void> {
  await initDatabase();
  /* raw: UPDATE telephone_settings SET twilio_connected = false, ... WHERE restaurant_id = $1 */
  await db.execute(sql`
    UPDATE telephone_settings SET twilio_connected = false, twilio_account_sid_hint = NULL, twilio_account_sid = NULL, twilio_auth_token = NULL, updated_at = NOW()
    WHERE restaurant_id = ${restaurantId}
  `);
  /* raw: UPDATE phone_numbers SET is_active = false, assigned_agent_id = NULL WHERE restaurant_id = $1 AND type = 'purchased' */
  await db.execute(sql`
    UPDATE phone_numbers SET is_active = false, assigned_agent_id = NULL, updated_at = NOW()
    WHERE restaurant_id = ${restaurantId} AND type = 'purchased'
  `);
}

export async function deletePhoneNumber(restaurantId: string, numberId: string): Promise<void> {
  await initDatabase();
  /* raw: DELETE FROM phone_numbers WHERE id = $1 AND restaurant_id = $2 AND type = 'purchased' */
  const { rowCount } = await db.execute(sql`
    DELETE FROM phone_numbers WHERE id = ${numberId} AND restaurant_id = ${restaurantId} AND type = 'purchased'
  `);
  if (!rowCount) throw new ValidationError('Phone number not found or cannot be removed');
}

export async function verifyTwilioCredentials(accountSid: string, authToken: string): Promise<{ ok: boolean; error?: string }> {
  try {
    const credentials = Buffer.from(`${accountSid}:${authToken}`).toString('base64');
    const res = await fetch(`https://api.twilio.com/2010-04-01/Accounts/${accountSid}.json`, {
      headers: { Authorization: `Basic ${credentials}` },
    });
    if (res.ok) return { ok: true };
    const data = await res.json().catch(() => ({}));
    return { ok: false, error: data.message || `HTTP ${res.status}` };
  } catch (err: unknown) {
    return { ok: false, error: err instanceof Error ? err.message : 'Network error' };
  }
}

export async function fetchAndSyncTwilioNumbers(restaurantId: string): Promise<TwilioNumber[]> {
  const settings = await getTelephoneSettings(restaurantId);
  const creds = await decryptCredentials(settings);
  if (!creds) return [];

  let remoteNumbers: TwilioNumber[];
  try {
    const credentials = Buffer.from(`${creds.accountSid}:${creds.authToken}`).toString('base64');
    const res = await fetch(
      `https://api.twilio.com/2010-04-01/Accounts/${creds.accountSid}/IncomingPhoneNumbers.json?PageSize=50`,
      { headers: { Authorization: `Basic ${credentials}` } }
    );
    if (!res.ok) return [];
    const data = await res.json();
    remoteNumbers = (data.incoming_phone_numbers || []).map((n: Record<string, unknown>) => ({
      sid: n.sid as string,
      phone_number: n.phone_number as string,
      friendly_name: n.friendly_name as string,
      capabilities: n.capabilities as { voice: boolean; sms: boolean },
    }));
  } catch {
    return [];
  }

  const numbersToProvision: string[] = [];

  for (const tn of remoteNumbers) {
    /* raw: SELECT id, twilio_number_sid, is_active, assigned_agent_id FROM phone_numbers WHERE restaurant_id = $1 AND number = $2 */
    const { rows: existingRows } = await db.execute(sql`
      SELECT id, twilio_number_sid, is_active, assigned_agent_id FROM phone_numbers WHERE restaurant_id = ${restaurantId} AND number = ${tn.phone_number}
    `);
    const existing = existingRows[0] as { id: string; twilio_number_sid: string | null; is_active: boolean; assigned_agent_id: string | null } | undefined;

    if (!existing) {
      /* raw: INSERT INTO phone_numbers (...) VALUES (...) RETURNING * */
      const { rows: insertedRows } = await db.execute(sql`
        INSERT INTO phone_numbers (restaurant_id, number, display_name, type, is_active, twilio_number_sid)
        VALUES (${restaurantId}, ${tn.phone_number}, ${tn.friendly_name}, 'purchased', true, ${tn.sid})
        RETURNING *
      `);
      const inserted = insertedRows[0] as { id: string } | undefined;
      if (inserted) numbersToProvision.push(inserted.id);
    } else {
      if (!existing.twilio_number_sid) {
        /* raw: UPDATE phone_numbers SET twilio_number_sid = $1, updated_at = NOW() WHERE id = $2 */
        await db.execute(sql`UPDATE phone_numbers SET twilio_number_sid = ${tn.sid}, updated_at = NOW() WHERE id = ${existing.id}`);
      }
      if (existing.is_active && existing.assigned_agent_id && (existing.twilio_number_sid || tn.sid)) {
        numbersToProvision.push(existing.id);
      }
    }
  }

  for (const numId of numbersToProvision) {
    await provisionWebhookForNumber(numId, restaurantId);
  }

  return remoteNumbers;
}

export async function listPhoneNumbers(restaurantId: string) {
  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 = 'purchased' ORDER BY p.created_at DESC */
  const { rows } = await db.execute(sql`
    SELECT p.*, a.name AS assigned_agent_name
    FROM phone_numbers p
    LEFT JOIN ai_agents a ON a.id = p.assigned_agent_id
    WHERE p.restaurant_id = ${restaurantId} AND p.type = 'purchased'
    ORDER BY p.created_at DESC
  `);
  return rows;
}

export async function updatePhoneNumberSettings(
  numberId: string,
  restaurantId: string,
  data: {
    call_recording?: boolean;
    call_transcription?: boolean;
    greeting_override?: string | null;
    fallback_number?: string | null;
    branch_id?: string | null;
    branch_routing_mode?: 'assigned' | 'ask_caller' | 'geo_detect';
  }
): Promise<void> {
  await initDatabase();

  if (data.branch_id) {
    /* raw: SELECT id FROM branches WHERE id = $1 AND restaurant_id = $2 */
    const { rows: branchRows } = await db.execute(sql`SELECT id FROM branches WHERE id = ${data.branch_id} AND restaurant_id = ${restaurantId}`);
    if (!branchRows[0]) throw new ValidationError('Branch not found or does not belong to this restaurant');
  }

  if (data.branch_routing_mode !== undefined) {
    const validModes = ['assigned', 'ask_caller', 'geo_detect'];
    if (!validModes.includes(data.branch_routing_mode)) {
      throw new ValidationError('Invalid branch_routing_mode value');
    }
  }

  const sets: SQL[] = [];
  if (data.call_recording !== undefined) sets.push(sql`call_recording = ${bindValue(data.call_recording)}`);
  if (data.call_transcription !== undefined) sets.push(sql`call_transcription = ${bindValue(data.call_transcription)}`);
  if (data.greeting_override !== undefined) sets.push(sql`greeting_override = ${bindValue(data.greeting_override)}`);
  if (data.fallback_number !== undefined) sets.push(sql`fallback_number = ${bindValue(data.fallback_number)}`);
  if (data.branch_id !== undefined) sets.push(sql`branch_id = ${bindValue(data.branch_id)}`);
  if (data.branch_routing_mode !== undefined) sets.push(sql`branch_routing_mode = ${bindValue(data.branch_routing_mode)}`);

  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 */
  const result = await db.execute(sql`UPDATE phone_numbers SET ${sql.join(sets, sql`, `)} WHERE id = ${numberId} AND restaurant_id = ${restaurantId}`);
  if ((result.rowCount ?? 0) === 0) throw new ValidationError('Phone number not found');
}

export async function assignAgentToNumber(numberId: string, restaurantId: string, agentId: string | null): Promise<void> {
  await initDatabase();
  if (agentId) {
    /* raw: SELECT id FROM ai_agents WHERE id = $1 AND restaurant_id = $2 */
    const { rows: agentRows } = await db.execute(sql`SELECT id FROM ai_agents WHERE id = ${agentId} AND restaurant_id = ${restaurantId}`);
    if (!agentRows[0]) throw new ValidationError('Agent not found or does not belong to this restaurant');
  }
  /* raw: UPDATE phone_numbers SET assigned_agent_id = $1, updated_at = NOW() WHERE id = $2 AND restaurant_id = $3 */
  const result = await db.execute(sql`UPDATE phone_numbers SET assigned_agent_id = ${agentId}, updated_at = NOW() WHERE id = ${numberId} AND restaurant_id = ${restaurantId}`);
  if ((result.rowCount ?? 0) === 0) throw new ValidationError('Phone number not found');

  await provisionWebhookForNumber(numberId, restaurantId);
}

function buildTwilioAuth(accountSid: string, authToken: string): string {
  return Buffer.from(`${accountSid}:${authToken}`).toString('base64');
}

function getWebhookBaseUrl(): string {
  const base = process.env.WEBHOOK_BASE_URL;
  if (!base) throw new Error('WEBHOOK_BASE_URL is not configured. Set this environment variable to your public HTTPS app URL so Twilio can reach your voice webhooks.');
  return base;
}

export async function provisionWebhookForNumber(numberId: string, restaurantId: string): Promise<void> {
  /* raw: SELECT twilio_number_sid, is_active, assigned_agent_id FROM phone_numbers WHERE id = $1 AND restaurant_id = $2 */
  const { rows: phoneRows } = await db.execute(sql`SELECT twilio_number_sid, is_active, assigned_agent_id FROM phone_numbers WHERE id = ${numberId} AND restaurant_id = ${restaurantId}`);
  const phoneRow = phoneRows[0] as { twilio_number_sid: string | null; is_active: boolean; assigned_agent_id: string | null } | undefined;
  if (!phoneRow?.twilio_number_sid) return;

  const settings = await getTelephoneSettings(restaurantId);
  const creds = await decryptCredentials(settings);
  if (!creds) return;

  const baseUrl = getWebhookBaseUrl();
  const shouldActivate = phoneRow.is_active && phoneRow.assigned_agent_id;

  const body = new URLSearchParams();
  if (shouldActivate) {
    body.set('VoiceUrl', `${baseUrl}/api/webhooks/twilio/voice`);
    body.set('VoiceMethod', 'POST');
    body.set('StatusCallback', `${baseUrl}/api/webhooks/twilio/status`);
    body.set('StatusCallbackMethod', 'POST');
  } else {
    body.set('VoiceUrl', '');
    body.set('VoiceMethod', 'POST');
    body.set('StatusCallback', '');
    body.set('StatusCallbackMethod', 'POST');
  }

  try {
    const res = await fetch(
      `https://api.twilio.com/2010-04-01/Accounts/${creds.accountSid}/IncomingPhoneNumbers/${phoneRow.twilio_number_sid}.json`,
      {
        method: 'POST',
        headers: {
          Authorization: `Basic ${buildTwilioAuth(creds.accountSid, creds.authToken)}`,
          'Content-Type': 'application/x-www-form-urlencoded',
        },
        body,
      }
    );
    if (!res.ok) {
      const data = await res.json().catch(() => ({}));
      log.warn({ numberSid: phoneRow.twilio_number_sid, message: (data as Record<string, string>).message, status: res.status }, 'failed to update webhook');
    }
  } catch (err: unknown) {
    log.warn({ err }, 'network error updating webhook');
  }
}

export async function searchAvailableNumbers(
  accountSid: string, authToken: string, country: string, params: { areaCode?: string; type?: string; limit?: number }
): Promise<{ numbers: Array<{ phone_number: string; friendly_name: string; locality: string; region: string; capabilities: Record<string, boolean> }> }> {
  const numberType = params.type === 'toll-free' ? 'TollFree' : params.type === 'mobile' ? 'Mobile' : 'Local';
  const url = new URL(`https://api.twilio.com/2010-04-01/Accounts/${accountSid}/AvailablePhoneNumbers/${country}/${numberType}.json`);
  if (params.areaCode) url.searchParams.set('AreaCode', params.areaCode);
  url.searchParams.set('PageSize', String(params.limit || 20));

  const res = await fetch(url.toString(), {
    headers: { Authorization: `Basic ${buildTwilioAuth(accountSid, authToken)}` },
  });
  if (!res.ok) {
    const data = await res.json().catch(() => ({}));
    throw new ValidationError((data as Record<string, string>).message || `Twilio API error: ${res.status}`);
  }
  const data = await res.json();
  const key = `available_phone_numbers`;
  return {
    numbers: (data[key] || []).map((n: Record<string, unknown>) => ({
      phone_number: n.phone_number,
      friendly_name: n.friendly_name,
      locality: n.locality || '',
      region: n.region || '',
      capabilities: n.capabilities || {},
    })),
  };
}

export async function purchaseNumber(
  restaurantId: string, phoneNumber: string
): Promise<{ sid: string; phone_number: string; friendly_name: string }> {
  const settings = await getTelephoneSettings(restaurantId);
  const creds = await decryptCredentials(settings);
  if (!creds) throw new ValidationError('Twilio credentials not configured');

  const baseUrl = getWebhookBaseUrl();
  const body = new URLSearchParams({
    PhoneNumber: phoneNumber,
    VoiceUrl: `${baseUrl}/api/webhooks/twilio/voice`,
    VoiceMethod: 'POST',
    StatusCallback: `${baseUrl}/api/webhooks/twilio/status`,
    StatusCallbackMethod: 'POST',
  });

  const res = await fetch(
    `https://api.twilio.com/2010-04-01/Accounts/${creds.accountSid}/IncomingPhoneNumbers.json`,
    {
      method: 'POST',
      headers: {
        Authorization: `Basic ${buildTwilioAuth(creds.accountSid, creds.authToken)}`,
        'Content-Type': 'application/x-www-form-urlencoded',
      },
      body,
    }
  );

  const data = await res.json();
  if (!res.ok) throw new ValidationError(data.message || 'Failed to purchase number');

  /* raw: INSERT INTO phone_numbers (...) VALUES (...) RETURNING * */
  await db.execute(sql`
    INSERT INTO phone_numbers (restaurant_id, number, display_name, type, is_active, twilio_number_sid)
    VALUES (${restaurantId}, ${data.phone_number as string}, ${data.friendly_name as string}, 'purchased', true, ${data.sid as string})
  `);

  return { sid: data.sid, phone_number: data.phone_number, friendly_name: data.friendly_name };
}

export async function initiateOutboundCall(
  restaurantId: string, to: string, fromNumberId: string
): Promise<{ callSid: string; sessionId: string }> {
  const settings = await getTelephoneSettings(restaurantId);
  const creds = await decryptCredentials(settings);
  if (!creds) throw new ValidationError('Twilio credentials not configured');

  /* raw: SELECT id, number, assigned_agent_id FROM phone_numbers WHERE id = $1 AND restaurant_id = $2 AND is_active = true */
  const { rows: phoneRows } = await db.execute(sql`SELECT id, number, assigned_agent_id FROM phone_numbers WHERE id = ${fromNumberId} AND restaurant_id = ${restaurantId} AND is_active = true`);
  const phoneRow = phoneRows[0] as { id: string; number: string; assigned_agent_id: string | null } | undefined;
  if (!phoneRow) throw new ValidationError('Phone number not found or inactive');

  /* raw: INSERT INTO sip_call_sessions (...) VALUES (...) RETURNING * */
  const { rows: sessionRows } = await db.execute(sql`
    INSERT INTO sip_call_sessions (restaurant_id, phone_number_id, agent_id, caller_number, callee_number, direction, status, transcript)
    VALUES (${restaurantId}, ${phoneRow.id}, ${phoneRow.assigned_agent_id || null}, ${phoneRow.number}, ${to}, 'outbound', 'ringing', '[]'::jsonb)
    RETURNING *
  `);
  const session = sessionRows[0] as { id: string };

  const baseUrl = getWebhookBaseUrl();
  const callBody = new URLSearchParams({
    To: to,
    From: phoneRow.number,
    Url: `${baseUrl}/api/webhooks/twilio/voice?sessionId=${session.id}`,
    StatusCallback: `${baseUrl}/api/webhooks/twilio/status`,
    StatusCallbackMethod: 'POST',
    'StatusCallbackEvent[]': 'initiated',
  });
  callBody.append('StatusCallbackEvent[]', 'ringing');
  callBody.append('StatusCallbackEvent[]', 'answered');
  callBody.append('StatusCallbackEvent[]', 'completed');

  const res = await fetch(
    `https://api.twilio.com/2010-04-01/Accounts/${creds.accountSid}/Calls.json`,
    {
      method: 'POST',
      headers: {
        Authorization: `Basic ${buildTwilioAuth(creds.accountSid, creds.authToken)}`,
        'Content-Type': 'application/x-www-form-urlencoded',
      },
      body: callBody,
    }
  );

  const callData = await res.json();
  if (!res.ok) {
    /* raw: DELETE FROM sip_call_sessions WHERE id = $1 */
    await db.execute(sql`DELETE FROM sip_call_sessions WHERE id = ${session.id}`);
    throw new ValidationError(callData.message || 'Failed to initiate call');
  }

  /* raw: UPDATE sip_call_sessions SET sip_call_id = $1, status = $2 WHERE id = $3 */
  await db.execute(sql`UPDATE sip_call_sessions SET sip_call_id = ${callData.sid as string}, status = 'active' WHERE id = ${session.id}`);

  return { callSid: callData.sid, sessionId: session.id };
}

export async function transferCall(
  sessionId: string, restaurantId: string, targetNumber: string
): Promise<void> {
  /* raw: SELECT id, sip_call_id, status FROM sip_call_sessions WHERE id = $1 AND restaurant_id = $2 */
  const { rows: sessionRows } = await db.execute(sql`SELECT id, sip_call_id, status FROM sip_call_sessions WHERE id = ${sessionId} AND restaurant_id = ${restaurantId}`);
  const session = sessionRows[0] as { id: string; sip_call_id: string | null; status: string } | undefined;
  if (!session) throw new ValidationError('Call session not found');
  if (session.status !== 'active') throw new ValidationError('Call is not active');
  if (!session.sip_call_id) throw new ValidationError('No Twilio call SID for this session');

  const settings = await getTelephoneSettings(restaurantId);
  const creds = await decryptCredentials(settings);
  if (!creds) throw new ValidationError('Twilio credentials not configured');

  const baseUrl = getWebhookBaseUrl();

  /* raw: UPDATE sip_call_sessions SET metadata = metadata || '{"pending_transfer":{"type":"dial","target":$1}}' WHERE id = $2 */
  await db.execute(sql`
    UPDATE sip_call_sessions
       SET metadata = COALESCE(metadata, '{}'::jsonb) || jsonb_build_object('pending_transfer', jsonb_build_object('type', 'dial', 'target', ${targetNumber}))
     WHERE id = ${sessionId}
  `);

  const twimlUrl = `${baseUrl}/api/webhooks/twilio/voice?transfer_session_id=${encodeURIComponent(sessionId)}`;

  const body = new URLSearchParams({
    Url: twimlUrl,
    Method: 'POST',
  });

  const res = await fetch(
    `https://api.twilio.com/2010-04-01/Accounts/${creds.accountSid}/Calls/${session.sip_call_id}.json`,
    {
      method: 'POST',
      headers: {
        Authorization: `Basic ${buildTwilioAuth(creds.accountSid, creds.authToken)}`,
        'Content-Type': 'application/x-www-form-urlencoded',
      },
      body,
    }
  );

  if (!res.ok) {
    const data = await res.json().catch(() => ({}));
    throw new ValidationError((data as Record<string, string>).message || 'Failed to transfer call');
  }

  /* raw: UPDATE sip_call_sessions SET escalated_to_human = true WHERE id = $1 */
  await db.execute(sql`UPDATE sip_call_sessions SET escalated_to_human = true WHERE id = ${sessionId}`);
}

export async function createConference(
  sessionId: string, restaurantId: string, staffNumber?: string
): Promise<{ conferenceName: string; staffCallSid?: string; staffNumberDialed?: string; staffJoinStatus: string }> {
  /* raw: SELECT id, sip_call_id, status, phone_number_id FROM sip_call_sessions WHERE id = $1 AND restaurant_id = $2 */
  const { rows: sessionRows } = await db.execute(sql`SELECT id, sip_call_id, status, phone_number_id FROM sip_call_sessions WHERE id = ${sessionId} AND restaurant_id = ${restaurantId}`);
  const session = sessionRows[0] as { id: string; sip_call_id: string | null; status: string; phone_number_id: string | null } | undefined;
  if (!session) throw new ValidationError('Call session not found');
  if (session.status !== 'active') throw new ValidationError('Call is not active');
  if (!session.sip_call_id) throw new ValidationError('No Twilio call SID for this session');

  const settings = await getTelephoneSettings(restaurantId);
  const creds = await decryptCredentials(settings);
  if (!creds) throw new ValidationError('Twilio credentials not configured');

  const conferenceName = `restro-conf-${sessionId.slice(0, 8)}-${Date.now()}`;
  const baseUrl = getWebhookBaseUrl();

  /* raw: UPDATE sip_call_sessions SET metadata = metadata || '{"pending_transfer":{"type":"conference","name":$1}}' WHERE id = $2 */
  await db.execute(sql`
    UPDATE sip_call_sessions
       SET metadata = COALESCE(metadata, '{}'::jsonb) || jsonb_build_object('pending_transfer', jsonb_build_object('type', 'conference', 'name', ${conferenceName}))
     WHERE id = ${sessionId}
  `);

  const callerTwimlUrl = `${baseUrl}/api/webhooks/twilio/voice?transfer_session_id=${encodeURIComponent(sessionId)}&transfer_role=caller`;

  const updateBody = new URLSearchParams({ Url: callerTwimlUrl, Method: 'POST' });
  const updateRes = await fetch(
    `https://api.twilio.com/2010-04-01/Accounts/${creds.accountSid}/Calls/${session.sip_call_id}.json`,
    {
      method: 'POST',
      headers: {
        Authorization: `Basic ${buildTwilioAuth(creds.accountSid, creds.authToken)}`,
        'Content-Type': 'application/x-www-form-urlencoded',
      },
      body: updateBody,
    }
  );

  if (!updateRes.ok) {
    const data = await updateRes.json().catch(() => ({}));
    throw new ValidationError((data as Record<string, string>).message || 'Failed to create conference');
  }

  let staffCallSid: string | undefined;
  let staffNumberDialed: string | undefined;
  let staffJoinStatus = 'no_staff_number';

  const dialTarget = staffNumber?.trim() || await resolveStaffNumber(restaurantId, session.phone_number_id);
  if (dialTarget) {
    staffNumberDialed = dialTarget;
    const staffTwimlUrl = `${baseUrl}/api/webhooks/twilio/voice?transfer_session_id=${encodeURIComponent(sessionId)}&transfer_role=staff`;

    let fromNumber: string | undefined;
    if (session.phone_number_id) {
      /* raw: SELECT number FROM phone_numbers WHERE id = $1 */
      const { rows: pnRows } = await db.execute(sql`SELECT number FROM phone_numbers WHERE id = ${session.phone_number_id}`);
      fromNumber = (pnRows[0] as { number: string } | undefined)?.number;
    }

    const callBody = new URLSearchParams({
      To: dialTarget,
      From: fromNumber || dialTarget,
      Url: staffTwimlUrl,
      Method: 'POST',
    });

    const callRes = await fetch(
      `https://api.twilio.com/2010-04-01/Accounts/${creds.accountSid}/Calls.json`,
      {
        method: 'POST',
        headers: {
          Authorization: `Basic ${buildTwilioAuth(creds.accountSid, creds.authToken)}`,
          'Content-Type': 'application/x-www-form-urlencoded',
        },
        body: callBody,
      }
    );

    if (callRes.ok) {
      const callData = await callRes.json() as { sid?: string };
      staffCallSid = callData.sid;
      staffJoinStatus = 'calling_staff';
    } else {
      staffJoinStatus = 'staff_call_failed';
    }
  }

  return { conferenceName, staffCallSid, staffNumberDialed, staffJoinStatus };
}

async function resolveStaffNumber(restaurantId: string, phoneNumberId: string | null): Promise<string | null> {
  if (phoneNumberId) {
    /* raw: SELECT fallback_number FROM phone_numbers WHERE id = $1 */
    const { rows } = await db.execute(sql`SELECT fallback_number FROM phone_numbers WHERE id = ${phoneNumberId}`);
    const pn = rows[0] as { fallback_number: string | null } | undefined;
    if (pn?.fallback_number) return pn.fallback_number;
  }
  return null;
}