import { db } from '@server/db/drizzle';
import { sql } from 'drizzle-orm';
import { initDatabase } from '@server/db/init';

export interface AlertSettingsRow {
  id: string;
  restaurantId: string;
  soundEnabled: boolean;
  volume: number;
  autoDismiss: number;
  showOrders: boolean;
  showBookings: boolean;
  showAiEscalations: boolean;
  browserNotifications: boolean;
}

const DEFAULTS: Omit<AlertSettingsRow, 'id' | 'restaurantId'> = {
  soundEnabled: true,
  volume: 70,
  autoDismiss: 30,
  showOrders: true,
  showBookings: true,
  showAiEscalations: true,
  browserNotifications: false,
};

function normalize(row: Record<string, unknown>): AlertSettingsRow {
  return {
    id: row.id as string,
    restaurantId: (row.restaurant_id ?? row.restaurantId) as string,
    soundEnabled: Boolean(row.sound_enabled ?? row.soundEnabled ?? DEFAULTS.soundEnabled),
    volume: Number(row.volume ?? DEFAULTS.volume),
    autoDismiss: Number(row.auto_dismiss ?? row.autoDismiss ?? DEFAULTS.autoDismiss),
    showOrders: Boolean(row.show_orders ?? row.showOrders ?? DEFAULTS.showOrders),
    showBookings: Boolean(row.show_bookings ?? row.showBookings ?? DEFAULTS.showBookings),
    showAiEscalations: Boolean(row.show_ai_escalations ?? row.showAiEscalations ?? DEFAULTS.showAiEscalations),
    browserNotifications: Boolean(row.browser_notifications ?? row.browserNotifications ?? DEFAULTS.browserNotifications),
  };
}

export async function getAlertSettings(restaurantId: string): Promise<AlertSettingsRow> {
  await initDatabase();
  /* raw: SELECT * FROM alert_settings WHERE restaurant_id = $1 */
  const { rows } = await db.execute(sql`SELECT * FROM alert_settings WHERE restaurant_id = ${restaurantId}`);
  if (!rows[0]) {
    return { id: '', restaurantId, ...DEFAULTS };
  }
  return normalize(rows[0] as Record<string, unknown>);
}

export async function upsertAlertSettings(
  restaurantId: string,
  data: Partial<Omit<AlertSettingsRow, 'id' | 'restaurantId'>>
): Promise<AlertSettingsRow> {
  await initDatabase();
  /* raw: INSERT INTO alert_settings (...) VALUES (...) ON CONFLICT (restaurant_id) DO UPDATE SET ... RETURNING * */
  const { rows } = await db.execute(sql`
    INSERT INTO alert_settings
      (restaurant_id, sound_enabled, volume, auto_dismiss, show_orders, show_bookings, show_ai_escalations, browser_notifications)
    VALUES (${restaurantId}, ${data.soundEnabled ?? DEFAULTS.soundEnabled}, ${data.volume ?? DEFAULTS.volume},
            ${data.autoDismiss ?? DEFAULTS.autoDismiss}, ${data.showOrders ?? DEFAULTS.showOrders},
            ${data.showBookings ?? DEFAULTS.showBookings}, ${data.showAiEscalations ?? DEFAULTS.showAiEscalations},
            ${data.browserNotifications ?? DEFAULTS.browserNotifications})
    ON CONFLICT (restaurant_id) DO UPDATE SET
      sound_enabled = EXCLUDED.sound_enabled,
      volume = EXCLUDED.volume,
      auto_dismiss = EXCLUDED.auto_dismiss,
      show_orders = EXCLUDED.show_orders,
      show_bookings = EXCLUDED.show_bookings,
      show_ai_escalations = EXCLUDED.show_ai_escalations,
      browser_notifications = EXCLUDED.browser_notifications,
      updated_at = now()
    RETURNING *
  `);
  return normalize(rows[0] as Record<string, unknown>);
}