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';
import { bindValue } from '@server/db/bind';

export async function getAiConfig(restaurantId: string, branchId?: string) {
  await initDatabase();
  const conditions: SQL[] = [sql`restaurant_id = ${restaurantId}`];
  if (branchId) conditions.push(sql`branch_id = ${branchId}`);
  else conditions.push(sql`branch_id IS NULL`);
  const where = sql.join(conditions, sql` AND `);

  /* raw: SELECT * FROM ai_agent_configs WHERE restaurant_id = $1 [AND branch_id = $2 | AND branch_id IS NULL] LIMIT 1 */
  const { rows } = await db.execute(sql`SELECT * FROM ai_agent_configs WHERE ${where} LIMIT 1`);
  if (!rows[0]) throw new NotFoundError('AI config');
  return rows[0];
}

export async function updateAiConfig(restaurantId: string, data: Record<string, unknown>, branchId?: string) {
  await initDatabase();

  const allowed = [
    'voice_enabled','chat_enabled','model','voice_model','response_style',
    'system_prompt','greeting_script','closing_script','fallback_rules','capabilities',
    'twilio_phone_number','inbound_routing','outbound_enabled','call_greeting',
    'call_recording_enabled','call_transcription_enabled',
  ];

  const jsonFields = new Set(['fallback_rules', 'capabilities']);
  const sets: SQL[] = [];
  for (const [k, v] of Object.entries(data)) {
    if (!allowed.includes(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');
  sets.push(sql`updated_at = NOW()`);

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

  /* raw: UPDATE ai_agent_configs SET ... WHERE restaurant_id = $N [AND branch_id = $N | AND branch_id IS NULL] RETURNING * */
  const { rows } = await db.execute(sql`UPDATE ai_agent_configs SET ${sql.join(sets, sql`, `)} WHERE ${where} RETURNING *`);
  if (!rows[0]) throw new NotFoundError('AI config');
  return rows[0];
}

export async function ensureAiConfig(restaurantId: string, branchId?: string) {
  await initDatabase();
  const conditions: SQL[] = [sql`restaurant_id = ${restaurantId}`];
  if (branchId) conditions.push(sql`branch_id = ${branchId}`);
  else conditions.push(sql`branch_id IS NULL`);
  const where = sql.join(conditions, sql` AND `);

  /* raw: SELECT * FROM ai_agent_configs WHERE restaurant_id = $1 [AND branch_id = $2 | AND branch_id IS NULL] LIMIT 1 */
  const { rows } = await db.execute(sql`SELECT * FROM ai_agent_configs WHERE ${where} LIMIT 1`);
  if (rows[0]) return rows[0];

  /* raw: INSERT INTO ai_agent_configs (...) VALUES (...) RETURNING * */
  const { rows: insertRows } = await db.execute(sql`
    INSERT INTO ai_agent_configs (restaurant_id, branch_id, voice_enabled, chat_enabled, model)
    VALUES (${restaurantId}, ${branchId ?? null}, true, true, 'gpt-4o') RETURNING *
  `);
  return insertRows[0];
}