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

export async function listTemplates(restaurantId: string) {
  await initDatabase();
  /* raw: SELECT * FROM message_templates WHERE restaurant_id = $1 ORDER BY template_key, channel */
  const { rows } = await db.execute(sql`SELECT * FROM message_templates WHERE restaurant_id = ${restaurantId} ORDER BY template_key, channel`);
  return rows;
}

export async function upsertTemplate(
  restaurantId: string,
  templateKey: string,
  channel: string,
  content: string
) {
  await initDatabase();
  /* raw: INSERT INTO message_templates (...) VALUES (...) ON CONFLICT (...) DO UPDATE SET ... RETURNING * */
  const { rows } = await db.execute(sql`
    INSERT INTO message_templates (restaurant_id, template_key, channel, content)
    VALUES (${restaurantId}, ${templateKey}, ${channel}, ${content})
    ON CONFLICT (restaurant_id, template_key, channel)
    DO UPDATE SET content = EXCLUDED.content, updated_at = NOW()
    RETURNING *
  `);
  return rows[0] ?? null;
}

export async function bulkUpsertTemplates(
  restaurantId: string,
  templates: { templateKey: string; channel: string; content: string }[]
) {
  await initDatabase();
  const results = [];
  for (const t of templates) {
    const row = await upsertTemplate(restaurantId, t.templateKey, t.channel, t.content);
    if (row) results.push(row);
  }
  return results;
}