import { db } from '@server/db/drizzle';
import { sql, type SQL } from 'drizzle-orm';
import { initDatabase } from '@server/db/init';
import { NotFoundError } from '@server/errors';
import { bindValue } from '@server/db/bind';
import { resolveApiKeyByEnvVar } from '@server/services/provider-keys.service';
import type {
  CreateLlmProviderInput,
  UpdateLlmProviderInput,
  CreateVoiceProviderInput,
  UpdateVoiceProviderInput,
} from '@server/validators/ai-providers.validator';

export interface LlmProvider {
  id: string;
  name: string;
  display_name: string;
  api_base_url: string;
  auth_header_format: string;
  api_key_env_var: string | null;
  is_active: boolean;
  display_order: number;
}

export interface LlmModel {
  id: string;
  provider_id: string;
  model_id: string;
  display_name: string;
  context_length: number;
  supports_functions: boolean;
  is_active: boolean;
  display_order: number;
}

export interface VoiceProvider {
  id: string;
  name: string;
  display_name: string;
  api_base_url: string;
  api_key_env_var: string | null;
  is_active: boolean;
  display_order: number;
}

export interface VoiceModel {
  id: string;
  voice_provider_id: string;
  voice_id: string;
  display_name: string;
  language_code: string;
  gender: string;
  is_active: boolean;
  display_order: number;
}

export async function listLlmProviders(activeOnly = true): Promise<LlmProvider[]> {
  await initDatabase();
  /* raw: SELECT * FROM llm_providers [WHERE is_active = true] ORDER BY display_order, display_name */
  if (activeOnly) {
    const { rows } = await db.execute(sql`SELECT * FROM llm_providers WHERE is_active = true ORDER BY display_order, display_name`);
    return rows as unknown as LlmProvider[];
  }
  const { rows } = await db.execute(sql`SELECT * FROM llm_providers ORDER BY display_order, display_name`);
  return rows as unknown as LlmProvider[];
}

export async function listLlmModels(providerId?: string, activeOnly = true): Promise<LlmModel[]> {
  await initDatabase();
  const conditions: SQL[] = [];
  if (activeOnly) { conditions.push(sql`m.is_active = true`); conditions.push(sql`p.is_active = true`); }
  if (providerId) conditions.push(sql`m.provider_id = ${providerId}`);
  const where = conditions.length ? sql`WHERE ${sql.join(conditions, sql` AND `)}` : sql``;

  /* raw: SELECT m.* FROM llm_models m JOIN llm_providers p ON p.id = m.provider_id [WHERE ...] ORDER BY m.display_order, m.display_name */
  const { rows } = await db.execute(sql`SELECT m.* FROM llm_models m JOIN llm_providers p ON p.id = m.provider_id ${where} ORDER BY m.display_order, m.display_name`);
  return rows as unknown as LlmModel[];
}

export async function listVoiceProviders(activeOnly = true): Promise<VoiceProvider[]> {
  await initDatabase();
  /* raw: SELECT * FROM voice_providers [WHERE is_active = true] ORDER BY display_order, display_name */
  if (activeOnly) {
    const { rows } = await db.execute(sql`SELECT * FROM voice_providers WHERE is_active = true ORDER BY display_order, display_name`);
    return rows as unknown as VoiceProvider[];
  }
  const { rows } = await db.execute(sql`SELECT * FROM voice_providers ORDER BY display_order, display_name`);
  return rows as unknown as VoiceProvider[];
}

export async function listVoiceModels(providerId?: string, activeOnly = true): Promise<VoiceModel[]> {
  await initDatabase();
  const conditions: SQL[] = [];
  if (activeOnly) { conditions.push(sql`m.is_active = true`); conditions.push(sql`p.is_active = true`); }
  if (providerId) conditions.push(sql`m.voice_provider_id = ${providerId}`);
  const where = conditions.length ? sql`WHERE ${sql.join(conditions, sql` AND `)}` : sql``;

  /* raw: SELECT m.* FROM voice_models m JOIN voice_providers p ON p.id = m.voice_provider_id [WHERE ...] ORDER BY m.display_order, m.display_name */
  const { rows } = await db.execute(sql`SELECT m.* FROM voice_models m JOIN voice_providers p ON p.id = m.voice_provider_id ${where} ORDER BY m.display_order, m.display_name`);
  return rows as unknown as VoiceModel[];
}

export async function getProvidersForUI() {
  const [llmProviders, llmModels, voiceProviders, voiceModels] = await Promise.all([
    listLlmProviders(true),
    listLlmModels(undefined, true),
    listVoiceProviders(true),
    listVoiceModels(undefined, true),
  ]);

  const llm = llmProviders.map(({ api_key_env_var, auth_header_format, ...p }) => ({
    ...p,
    models: llmModels.filter(m => m.provider_id === p.id),
  }));

  // Only expose OpenAI voice providers in the agent voice selector. ElevenLabs,
  // Deepgram, and Sarvam AI are seeded in the DB and work for the TTS preview
  // button, but live phone calls route exclusively through the OpenAI Realtime
  // API — passing a non-OpenAI voice ID to the Realtime session causes it to
  // fail. Hidden until a proper multi-provider voice bridge is implemented
  // (see task #496).
  const SUPPORTED_VOICE_PROVIDERS = new Set(['openai_realtime']);
  const voice = voiceProviders
    .filter(p => SUPPORTED_VOICE_PROVIDERS.has(p.name))
    .map(({ api_key_env_var, ...p }) => ({
      ...p,
      models: voiceModels.filter(m => m.voice_provider_id === p.id),
    }));

  return { llm, voice };
}

export async function getLlmProvider(id: string): Promise<LlmProvider> {
  await initDatabase();
  /* raw: SELECT * FROM llm_providers WHERE id = $1 */
  const { rows } = await db.execute(sql`SELECT * FROM llm_providers WHERE id = ${id}`);
  if (!rows[0]) throw new NotFoundError('LLM provider');
  return rows[0] as unknown as LlmProvider;
}

export async function createLlmProvider(data: CreateLlmProviderInput): Promise<LlmProvider> {
  await initDatabase();
  /* raw: INSERT INTO llm_providers (...) VALUES (...) RETURNING * */
  const { rows } = await db.execute(sql`
    INSERT INTO llm_providers (name, display_name, api_base_url, auth_header_format, api_key_env_var, is_active, display_order)
    VALUES (${data.name}, ${data.display_name}, ${data.api_base_url}, ${data.auth_header_format}, ${data.api_key_env_var}, ${data.is_active}, ${data.display_order})
    RETURNING *
  `);
  return rows[0] as unknown as LlmProvider;
}

const LLM_PROVIDER_ALLOWED_COLS = new Set(['name','display_name','api_base_url','auth_header_format','api_key_env_var','is_active','display_order']);
const LLM_MODEL_ALLOWED_COLS = new Set(['provider_id','model_id','display_name','context_length','supports_functions','is_active','display_order']);
const VOICE_PROVIDER_ALLOWED_COLS = new Set(['name','display_name','api_base_url','api_key_env_var','is_active','display_order']);
const VOICE_MODEL_ALLOWED_COLS = new Set(['voice_provider_id','voice_id','display_name','language_code','gender','is_active','display_order']);

export async function updateLlmProvider(id: string, data: UpdateLlmProviderInput): Promise<LlmProvider> {
  await initDatabase();
  const fields = data as Record<string, unknown>;
  const sets: SQL[] = [];
  for (const [k, v] of Object.entries(fields)) {
    if (!LLM_PROVIDER_ALLOWED_COLS.has(k)) continue;
    sets.push(sql`${sql.raw(k)} = ${bindValue(v)}`);
  }
  if (sets.length === 0) throw new NotFoundError('LLM provider');
  sets.push(sql`updated_at = NOW()`);

  /* raw: UPDATE llm_providers SET ... WHERE id = $N RETURNING * */
  const { rows } = await db.execute(sql`UPDATE llm_providers SET ${sql.join(sets, sql`, `)} WHERE id = ${id} RETURNING *`);
  if (!rows[0]) throw new NotFoundError('LLM provider');
  return rows[0] as unknown as LlmProvider;
}

export async function deleteLlmProvider(id: string): Promise<void> {
  await initDatabase();
  /* raw: DELETE FROM llm_providers WHERE id = $1 */
  const result = await db.execute(sql`DELETE FROM llm_providers WHERE id = ${id}`);
  if ((result.rowCount ?? 0) === 0) throw new NotFoundError('LLM provider');
}

export async function createLlmModel(data: Record<string, unknown>): Promise<LlmModel> {
  await initDatabase();
  /* raw: INSERT INTO llm_models (...) VALUES (...) RETURNING * */
  const { rows } = await db.execute(sql`
    INSERT INTO llm_models (provider_id, model_id, display_name, context_length, supports_functions, is_active, display_order)
    VALUES (${data.provider_id as string}, ${data.model_id as string}, ${data.display_name as string},
            ${(data.context_length as number) ?? 0}, ${data.supports_functions ?? false}, ${data.is_active ?? true}, ${(data.display_order as number) ?? 0})
    RETURNING *
  `);
  return rows[0] as unknown as LlmModel;
}

export async function updateLlmModel(id: string, data: Record<string, unknown>): Promise<LlmModel> {
  await initDatabase();
  const sets: SQL[] = [];
  for (const [k, v] of Object.entries(data)) {
    if (!LLM_MODEL_ALLOWED_COLS.has(k)) continue;
    sets.push(sql`${sql.raw(k)} = ${bindValue(v)}`);
  }
  if (sets.length === 0) throw new NotFoundError('LLM model');
  sets.push(sql`updated_at = NOW()`);

  /* raw: UPDATE llm_models SET ... WHERE id = $N RETURNING * */
  const { rows } = await db.execute(sql`UPDATE llm_models SET ${sql.join(sets, sql`, `)} WHERE id = ${id} RETURNING *`);
  if (!rows[0]) throw new NotFoundError('LLM model');
  return rows[0] as unknown as LlmModel;
}

export async function deleteLlmModel(id: string): Promise<void> {
  await initDatabase();
  /* raw: DELETE FROM llm_models WHERE id = $1 */
  const result = await db.execute(sql`DELETE FROM llm_models WHERE id = ${id}`);
  if ((result.rowCount ?? 0) === 0) throw new NotFoundError('LLM model');
}

export async function getVoiceProvider(id: string): Promise<VoiceProvider> {
  await initDatabase();
  /* raw: SELECT * FROM voice_providers WHERE id = $1 */
  const { rows } = await db.execute(sql`SELECT * FROM voice_providers WHERE id = ${id}`);
  if (!rows[0]) throw new NotFoundError('Voice provider');
  return rows[0] as unknown as VoiceProvider;
}

export async function createVoiceProvider(data: CreateVoiceProviderInput): Promise<VoiceProvider> {
  await initDatabase();
  /* raw: INSERT INTO voice_providers (...) VALUES (...) RETURNING * */
  const { rows } = await db.execute(sql`
    INSERT INTO voice_providers (name, display_name, api_base_url, api_key_env_var, is_active, display_order)
    VALUES (${data.name}, ${data.display_name}, ${data.api_base_url}, ${data.api_key_env_var}, ${data.is_active}, ${data.display_order})
    RETURNING *
  `);
  return rows[0] as unknown as VoiceProvider;
}

export async function updateVoiceProvider(id: string, data: UpdateVoiceProviderInput): Promise<VoiceProvider> {
  await initDatabase();
  const fields = data as Record<string, unknown>;
  const sets: SQL[] = [];
  for (const [k, v] of Object.entries(fields)) {
    if (!VOICE_PROVIDER_ALLOWED_COLS.has(k)) continue;
    sets.push(sql`${sql.raw(k)} = ${bindValue(v)}`);
  }
  if (sets.length === 0) throw new NotFoundError('Voice provider');
  sets.push(sql`updated_at = NOW()`);

  /* raw: UPDATE voice_providers SET ... WHERE id = $N RETURNING * */
  const { rows } = await db.execute(sql`UPDATE voice_providers SET ${sql.join(sets, sql`, `)} WHERE id = ${id} RETURNING *`);
  if (!rows[0]) throw new NotFoundError('Voice provider');
  return rows[0] as unknown as VoiceProvider;
}

export async function deleteVoiceProvider(id: string): Promise<void> {
  await initDatabase();
  /* raw: DELETE FROM voice_providers WHERE id = $1 */
  const result = await db.execute(sql`DELETE FROM voice_providers WHERE id = ${id}`);
  if ((result.rowCount ?? 0) === 0) throw new NotFoundError('Voice provider');
}

export async function createVoiceModel(data: Record<string, unknown>): Promise<VoiceModel> {
  await initDatabase();
  /* raw: INSERT INTO voice_models (...) VALUES (...) RETURNING * */
  const { rows } = await db.execute(sql`
    INSERT INTO voice_models (voice_provider_id, voice_id, display_name, language_code, gender, is_active, display_order)
    VALUES (${data.voice_provider_id as string}, ${data.voice_id as string}, ${data.display_name as string},
            ${(data.language_code as string) ?? 'en-US'}, ${(data.gender as string) ?? 'neutral'}, ${data.is_active ?? true}, ${(data.display_order as number) ?? 0})
    RETURNING *
  `);
  return rows[0] as unknown as VoiceModel;
}

export async function updateVoiceModel(id: string, data: Record<string, unknown>): Promise<VoiceModel> {
  await initDatabase();
  const sets: SQL[] = [];
  for (const [k, v] of Object.entries(data)) {
    if (!VOICE_MODEL_ALLOWED_COLS.has(k)) continue;
    sets.push(sql`${sql.raw(k)} = ${bindValue(v)}`);
  }
  if (sets.length === 0) throw new NotFoundError('Voice model');
  sets.push(sql`updated_at = NOW()`);

  /* raw: UPDATE voice_models SET ... WHERE id = $N RETURNING * */
  const { rows } = await db.execute(sql`UPDATE voice_models SET ${sql.join(sets, sql`, `)} WHERE id = ${id} RETURNING *`);
  if (!rows[0]) throw new NotFoundError('Voice model');
  return rows[0] as unknown as VoiceModel;
}

export async function deleteVoiceModel(id: string): Promise<void> {
  await initDatabase();
  /* raw: DELETE FROM voice_models WHERE id = $1 */
  const result = await db.execute(sql`DELETE FROM voice_models WHERE id = ${id}`);
  if ((result.rowCount ?? 0) === 0) throw new NotFoundError('Voice model');
}

export interface ProviderClientConfig {
  apiBaseUrl: string;
  apiKey: string | undefined;
  authHeaderName: string;
  authHeaderValue: string | undefined;
  modelId: string;
}

export async function getLlmClientConfig(llmModelId: string, restaurantId?: string | null): Promise<ProviderClientConfig> {
  await initDatabase();
  /* raw: SELECT lp.api_base_url, lp.auth_header_format, lp.api_key_env_var, lm.model_id FROM llm_models lm JOIN llm_providers lp ... WHERE lm.id = $1 */
  const { rows } = await db.execute(sql`
    SELECT lp.api_base_url, lp.auth_header_format, lp.api_key_env_var, lm.model_id
    FROM llm_models lm JOIN llm_providers lp ON lp.id = lm.provider_id
    WHERE lm.id = ${llmModelId}
  `);
  const row = rows[0] as { api_base_url: string; auth_header_format: string; api_key_env_var: string | null; model_id: string } | undefined;
  if (!row) throw new NotFoundError('LLM model');

  const apiKey = row.api_key_env_var
    ? await resolveApiKeyByEnvVar(restaurantId || null, row.api_key_env_var) || undefined
    : undefined;
  const isApiKeyHeader = row.auth_header_format.toLowerCase() === 'x-api-key';

  return {
    apiBaseUrl: row.api_base_url,
    apiKey,
    authHeaderName: isApiKeyHeader ? 'x-api-key' : 'Authorization',
    authHeaderValue: apiKey ? (isApiKeyHeader ? apiKey : `Bearer ${apiKey}`) : undefined,
    modelId: row.model_id,
  };
}

export async function getVoiceClientConfig(voiceModelId: string, restaurantId?: string | null): Promise<{
  apiBaseUrl: string;
  apiKey: string | undefined;
  voiceId: string;
  languageCode: string;
  providerName: string;
}> {
  await initDatabase();
  /* raw: SELECT vp.api_base_url, vp.api_key_env_var, vm.voice_id, vm.language_code, vp.name AS provider_name FROM voice_models vm JOIN voice_providers vp ... WHERE vm.id = $1 */
  const { rows } = await db.execute(sql`
    SELECT vp.api_base_url, vp.api_key_env_var, vm.voice_id, vm.language_code, vp.name AS provider_name
    FROM voice_models vm JOIN voice_providers vp ON vp.id = vm.voice_provider_id
    WHERE vm.id = ${voiceModelId}
  `);
  const row = rows[0] as { api_base_url: string; api_key_env_var: string | null; voice_id: string; language_code: string; provider_name: string } | undefined;
  if (!row) throw new NotFoundError('Voice model');

  const apiKey = row.api_key_env_var
    ? await resolveApiKeyByEnvVar(restaurantId || null, row.api_key_env_var) || undefined
    : undefined;

  return {
    apiBaseUrl: row.api_base_url,
    apiKey,
    voiceId: row.voice_id,
    languageCode: row.language_code,
    providerName: row.provider_name,
  };
}