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 listPhoneNumbers(restaurantId: string, branchId?: string) {
  await initDatabase();
  const conditions: SQL[] = [sql`restaurant_id = ${restaurantId}`];
  if (branchId) conditions.push(sql`(branch_id = ${branchId} OR branch_id IS NULL)`);
  const where = sql.join(conditions, sql` AND `);

  /* raw: SELECT * FROM phone_numbers WHERE restaurant_id = $1 [AND (branch_id = $2 OR branch_id IS NULL)] ORDER BY created_at DESC */
  const { rows } = await db.execute(sql`SELECT * FROM phone_numbers WHERE ${where} ORDER BY created_at DESC`);
  return rows;
}

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

export async function createPhoneNumber(restaurantId: string, data: Record<string, unknown>) {
  await initDatabase();
  /* raw: INSERT INTO phone_numbers (...) VALUES (...) RETURNING * */
  const { rows } = await db.execute(sql`
    INSERT INTO phone_numbers (restaurant_id, branch_id, number, type, sip_config, is_active)
    VALUES (${restaurantId}, ${(data.branch_id as string) ?? null}, ${data.number as string},
            ${(data.type as string) ?? 'purchased'}, ${JSON.stringify(data.sip_config ?? {})}::jsonb, ${data.is_active ?? true})
    RETURNING *
  `);
  return rows[0];
}

export async function updatePhoneNumber(id: string, restaurantId: string, data: Record<string, unknown>) {
  await initDatabase();
  const allowed = ['number','type','sip_config','is_active'];
  const sets: SQL[] = [];
  for (const [k, v] of Object.entries(data)) {
    if (!allowed.includes(k)) continue;
    const val = k === 'sip_config' ? JSON.stringify(v) : v;
    sets.push(sql`${sql.raw(k)} = ${bindValue(val)}`);
  }
  if (sets.length === 0) throw new ValidationError('No valid fields to update');

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

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