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

export async function getNotificationPreferences(restaurantId: string, userId: string) {
  await initDatabase();
  /* raw: SELECT * FROM notification_preferences WHERE restaurant_id = $1 AND user_id = $2 */
  const { rows } = await db.execute(sql`SELECT * FROM notification_preferences WHERE restaurant_id = ${restaurantId} AND user_id = ${userId}`);
  return rows[0] ?? null;
}

export async function upsertNotificationPreferences(
  restaurantId: string,
  userId: string,
  preferences: unknown[]
) {
  await initDatabase();
  /* raw: INSERT INTO notification_preferences (...) VALUES (...) ON CONFLICT ... DO NOTHING RETURNING * */
  const { rows } = await db.execute(sql`
    INSERT INTO notification_preferences (restaurant_id, user_id, preferences)
    VALUES (${restaurantId}, ${userId}, ${JSON.stringify(preferences)}::jsonb)
    ON CONFLICT ON CONSTRAINT notification_preferences_pkey DO NOTHING
    RETURNING *
  `);
  return rows[0] ?? null;
}

export async function saveNotificationPreferences(
  restaurantId: string,
  userId: string,
  preferences: unknown[]
) {
  await initDatabase();
  /* raw: SELECT id FROM notification_preferences WHERE restaurant_id = $1 AND user_id = $2 */
  const { rows: existing } = await db.execute(sql`SELECT id FROM notification_preferences WHERE restaurant_id = ${restaurantId} AND user_id = ${userId}`);
  if (existing[0]) {
    /* raw: UPDATE notification_preferences SET preferences = $1, updated_at = NOW() WHERE restaurant_id = $2 AND user_id = $3 RETURNING * */
    const { rows } = await db.execute(sql`
      UPDATE notification_preferences
      SET preferences = ${JSON.stringify(preferences)}::jsonb, updated_at = NOW()
      WHERE restaurant_id = ${restaurantId} AND user_id = ${userId}
      RETURNING *
    `);
    return rows[0] ?? null;
  }
  /* raw: INSERT INTO notification_preferences (...) VALUES (...) RETURNING * */
  const { rows } = await db.execute(sql`
    INSERT INTO notification_preferences (restaurant_id, user_id, preferences)
    VALUES (${restaurantId}, ${userId}, ${JSON.stringify(preferences)}::jsonb)
    RETURNING *
  `);
  return rows[0] ?? null;
}