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

export interface AiAgent {
  id: string;
  restaurant_id: string;
  branch_id: string | null;
  name: string;
  description: string | null;
  role: string;
  channels: string[];
  llm_model_id: string | null;
  voice_model_id: string | null;
  voice_language_code: string | null;
  realtime_model: string | null;
  system_prompt: string | null;
  greeting_script: string | null;
  closing_script: string | null;
  fallback_rules: unknown[];
  capabilities: Record<string, unknown>;
  is_active: boolean;
  is_default: boolean;
  vad_threshold: number | null;
  vad_prefix_padding_ms: number | null;
  vad_silence_duration_ms: number | null;
  menu_category_ids: string[] | null;
  knowledge_base_ids: string[];
  created_at: string;
  updated_at: string;
  llm_model_name?: string;
  llm_provider_name?: string;
  voice_model_name?: string;
  voice_provider_name?: string;
}

const AGENT_SELECT = sql.raw(`
  a.*,
  lm.display_name AS llm_model_name,
  lp.display_name AS llm_provider_name,
  vm.display_name AS voice_model_name,
  vp.display_name AS voice_provider_name,
  COALESCE(
    (SELECT array_agg(akb.kb_id::text) FROM agent_knowledge_bases akb WHERE akb.agent_id = a.id),
    ARRAY[]::text[]
  ) AS knowledge_base_ids
FROM ai_agents a
LEFT JOIN llm_models lm ON lm.id = a.llm_model_id
LEFT JOIN llm_providers lp ON lp.id = lm.provider_id
LEFT JOIN voice_models vm ON vm.id = a.voice_model_id
LEFT JOIN voice_providers vp ON vp.id = vm.voice_provider_id
`);

async function syncAgentKbIds(agentId: string, restaurantId: string, kbIds: string[] | null | undefined): Promise<void> {
  if (!Array.isArray(kbIds)) return;
  // Cross-tenant guard: only accept KB ids that belong to the same
  // restaurant as the agent. Silently dropping foreign ids prevents
  // operators (or a malicious payload) from linking another tenant's
  // KBs even though the read-side query already filters by
  // restaurant_id.
  let validIds: string[] = [];
  if (kbIds.length > 0) {
    const { rows } = await db.execute(
      sql`SELECT id::text AS id FROM knowledge_base WHERE restaurant_id = ${restaurantId} AND id = ANY(${kbIds}::uuid[])`
    );
    validIds = (rows as Array<{ id: string }>).map(r => r.id);
  }
  /* raw: DELETE FROM agent_knowledge_bases WHERE agent_id = $1 */
  await db.execute(sql`DELETE FROM agent_knowledge_bases WHERE agent_id = ${agentId}`);
  if (validIds.length === 0) return;
  for (const kbId of validIds) {
    /* raw: INSERT INTO agent_knowledge_bases (agent_id, kb_id) VALUES ($1, $2) ON CONFLICT DO NOTHING */
    await db.execute(sql`INSERT INTO agent_knowledge_bases (agent_id, kb_id) VALUES (${agentId}, ${kbId}) ON CONFLICT DO NOTHING`);
  }
}

export async function listAgents(restaurantId: string, branchId?: string | null): Promise<AiAgent[]> {
  await initDatabase();
  if (branchId) {
    /* raw: SELECT ... FROM ai_agents a LEFT JOIN ... WHERE a.restaurant_id = $1 AND (a.branch_id IS NULL OR a.branch_id = $2) ORDER BY ... */
    const { rows } = await db.execute(sql`SELECT ${AGENT_SELECT} WHERE a.restaurant_id = ${restaurantId} AND (a.branch_id IS NULL OR a.branch_id = ${branchId}) ORDER BY a.is_default DESC, a.created_at`);
    return rows as unknown as AiAgent[];
  }
  /* raw: SELECT ... FROM ai_agents a LEFT JOIN ... WHERE a.restaurant_id = $1 ORDER BY ... */
  const { rows } = await db.execute(sql`SELECT ${AGENT_SELECT} WHERE a.restaurant_id = ${restaurantId} ORDER BY a.is_default DESC, a.created_at`);
  return rows as unknown as AiAgent[];
}

export async function getAgent(id: string, restaurantId: string, branchId?: string | null): Promise<AiAgent> {
  await initDatabase();
  let result;

  if (branchId) {
    /* raw: SELECT ... WHERE a.id = $1 AND a.restaurant_id = $2 AND (a.branch_id IS NULL OR a.branch_id = $3) */
    result = await db.execute(sql`SELECT ${AGENT_SELECT} WHERE a.id = ${id} AND a.restaurant_id = ${restaurantId} AND (a.branch_id IS NULL OR a.branch_id = ${branchId})`);
  } else {
    /* raw: SELECT ... WHERE a.id = $1 AND a.restaurant_id = $2 */
    result = await db.execute(sql`SELECT ${AGENT_SELECT} WHERE a.id = ${id} AND a.restaurant_id = ${restaurantId}`);
  }

  if (!result.rows[0]) throw new NotFoundError('AI agent');
  return result.rows[0] as unknown as AiAgent;
}

export async function createAgent(restaurantId: string, data: Record<string, unknown>, branchId?: string | null): Promise<AiAgent> {
  await initDatabase();
  const jsonFields = new Set(['fallback_rules', 'capabilities']);
  const insertData: Record<string, unknown> = { restaurant_id: restaurantId };

  for (const [k, v] of Object.entries(data)) {
    if (k === 'restaurant_id') continue;
    if (!AGENT_ALLOWED_COLS.has(k)) continue;
    insertData[k] = jsonFields.has(k) ? JSON.stringify(v) : v;
  }

  if (branchId) {
    insertData.branch_id = branchId;
  }

  if (insertData.is_default === true) {
    /* raw: UPDATE ai_agents SET is_default = false WHERE restaurant_id = $1 AND is_default = true */
    await db.execute(sql`UPDATE ai_agents SET is_default = false WHERE restaurant_id = ${restaurantId} AND is_default = true`);
  }

  const keys = Object.keys(insertData);
  const vals = Object.values(insertData);
  const colsSql = sql.raw(keys.join(', '));
  const valsSql = sql.join(vals.map(bindValue), sql`, `);

  /* raw: INSERT INTO ai_agents (...) VALUES (...) RETURNING * */
  const { rows } = await db.execute(sql`INSERT INTO ai_agents (${colsSql}) VALUES (${valsSql}) RETURNING *`);
  const created = rows[0] as unknown as AiAgent;
  await syncAgentKbIds(created.id, restaurantId, data.knowledge_base_ids as string[] | null | undefined);
  return getAgent(created.id, restaurantId);
}

const AGENT_ALLOWED_COLS = new Set([
  'name','description','role','channels','llm_model_id','voice_model_id','voice_language_code',
  'realtime_model','system_prompt','greeting_script','closing_script','fallback_rules',
  'capabilities','is_active','is_default','branch_id',
  'vad_threshold','vad_prefix_padding_ms','vad_silence_duration_ms',
  'menu_category_ids',
]);

export async function updateAgent(id: string, restaurantId: string, data: Record<string, unknown>, branchId?: string | null): Promise<AiAgent> {
  await initDatabase();
  const jsonFields = new Set(['fallback_rules', 'capabilities']);
  const sets: SQL[] = [];

  for (const [k, v] of Object.entries(data)) {
    if (k === 'id' || k === 'restaurant_id' || k === 'created_at' || k === 'updated_at') continue;
    if (!AGENT_ALLOWED_COLS.has(k)) continue;
    const val = jsonFields.has(k) ? JSON.stringify(v) : v;
    sets.push(sql`${sql.raw(k)} = ${bindValue(val)}`);
  }

  if (sets.length === 0) throw new ValidationError('No valid fields to update');

  if (data.is_default === true) {
    /* raw: UPDATE ai_agents SET is_default = false WHERE restaurant_id = $1 AND is_default = true AND id != $2 */
    await db.execute(sql`UPDATE ai_agents SET is_default = false WHERE restaurant_id = ${restaurantId} AND is_default = true AND id != ${id}`);
  }

  sets.push(sql`updated_at = NOW()`);

  const conditions: SQL[] = [sql`id = ${id}`, sql`restaurant_id = ${restaurantId}`];
  if (branchId) conditions.push(sql`branch_id = ${branchId}`);
  const where = sql.join(conditions, sql` AND `);

  /* raw: UPDATE ai_agents SET ... WHERE id = $N AND restaurant_id = $N [AND branch_id = $N] RETURNING * */
  const { rows } = await db.execute(sql`UPDATE ai_agents SET ${sql.join(sets, sql`, `)} WHERE ${where} RETURNING *`);
  if (!rows[0]) throw new NotFoundError('AI agent');
  if ('knowledge_base_ids' in data) {
    await syncAgentKbIds(id, restaurantId, data.knowledge_base_ids as string[] | null | undefined);
  }
  return getAgent(id, restaurantId);
}

export async function deleteAgent(id: string, restaurantId: string, branchId?: string | null): Promise<void> {
  await initDatabase();
  const conditions: SQL[] = [sql`id = ${id}`, sql`restaurant_id = ${restaurantId}`];
  if (branchId) conditions.push(sql`branch_id = ${branchId}`);
  const where = sql.join(conditions, sql` AND `);

  /* raw: DELETE FROM ai_agents WHERE id = $1 AND restaurant_id = $2 [AND branch_id = $3] */
  const result = await db.execute(sql`DELETE FROM ai_agents WHERE ${where}`);
  if ((result.rowCount ?? 0) === 0) throw new NotFoundError('AI agent');
}