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';

const SAFE_COLS = `id, restaurant_id, name, address, phone, hours, timezone, is_active,
  smtp_host, smtp_port, smtp_user, smtp_from,
  CASE WHEN smtp_pass IS NOT NULL THEN 'configured' ELSE NULL END AS smtp_pass_status,
  created_at, updated_at`;

export async function listBranches(restaurantId: string, activeOnly = false) {
  await initDatabase();
  /* raw: SELECT ${SAFE_COLS} FROM branches WHERE restaurant_id = $1 [AND is_active = true] ORDER BY name ASC */
  const conditions: SQL[] = [sql`restaurant_id = ${restaurantId}`];
  if (activeOnly) conditions.push(sql`is_active = true`);
  const { rows } = await db.execute(sql`SELECT ${sql.raw(SAFE_COLS)} FROM branches WHERE ${sql.join(conditions, sql` AND `)} ORDER BY name ASC`);
  return rows;
}

export async function getBranch(id: string, restaurantId: string) {
  await initDatabase();
  /* raw: SELECT ${SAFE_COLS} FROM branches WHERE id = $1 AND restaurant_id = $2 */
  const { rows } = await db.execute(sql`SELECT ${sql.raw(SAFE_COLS)} FROM branches WHERE id = ${id} AND restaurant_id = ${restaurantId}`);
  if (!rows[0]) throw new NotFoundError('Branch');
  return rows[0];
}

export async function createBranch(restaurantId: string, data: Record<string, unknown>) {
  await initDatabase();
  /* raw: INSERT INTO branches (...) VALUES (...) RETURNING * */
  const { rows } = await db.execute(sql`
    INSERT INTO branches (restaurant_id, name, address, phone, hours, timezone, is_active)
    VALUES (${restaurantId}, ${data.name as string}, ${(data.address as string) ?? null}, ${(data.phone as string) ?? null},
            ${JSON.stringify(data.hours ?? {})}::jsonb, ${(data.timezone as string) ?? 'UTC'}, ${data.is_active ?? true})
    RETURNING *
  `);
  return rows[0];
}

export async function updateBranch(id: string, restaurantId: string, data: Record<string, unknown>) {
  await initDatabase();
  const allowed = ['name','address','phone','hours','timezone','is_active','smtp_host','smtp_port','smtp_user','smtp_pass','smtp_from'];
  const sets: SQL[] = [];
  for (const [k, v] of Object.entries(data)) {
    if (!allowed.includes(k)) continue;
    const val = k === 'hours' ? JSON.stringify(v) : v;
    sets.push(sql`${sql.raw(k)} = ${val as string | number | boolean | null}`);
  }
  if (sets.length === 0) throw new ValidationError('No valid fields to update');
  sets.push(sql`updated_at = NOW()`);

  /* raw: UPDATE branches SET ... WHERE id = $N AND restaurant_id = $N RETURNING id */
  const { rows } = await db.execute(sql`UPDATE branches SET ${sql.join(sets, sql`, `)} WHERE id = ${id} AND restaurant_id = ${restaurantId} RETURNING id`);
  if (!rows[0]) throw new NotFoundError('Branch');

  /* raw: SELECT ${SAFE_COLS} FROM branches WHERE id = $1 AND restaurant_id = $2 */
  const { rows: resultRows } = await db.execute(sql`SELECT ${sql.raw(SAFE_COLS)} FROM branches WHERE id = ${id} AND restaurant_id = ${restaurantId}`);
  if (!resultRows[0]) throw new NotFoundError('Branch');
  return resultRows[0];
}

export async function deleteBranch(id: string, restaurantId: string) {
  await initDatabase();
  /* raw: DELETE FROM branches WHERE id = $1 AND restaurant_id = $2 */
  const result = await db.execute(sql`DELETE FROM branches WHERE id = ${id} AND restaurant_id = ${restaurantId}`);
  if ((result.rowCount ?? 0) === 0) throw new NotFoundError('Branch');
}
