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 { dispatchWebhook } from './webhooks.service';

const BOOKING_SORT_COLS: Record<string, string> = {
  booking_date: 'b.booking_date',
  date: 'b.booking_date',
  status: 'b.status',
  guest_name: 'b.guest_name',
  created_at: 'b.created_at',
};

export interface BookingFilters {
  restaurantId: string;
  branchId?: string;
  customerId?: string;
  status?: string;
  date?: string;
  page?: number;
  limit?: number;
  search?: string;
  sortField?: string;
  sortDir?: string;
}

export async function listBookings(filters: BookingFilters) {
  await initDatabase();
  const { restaurantId, branchId, customerId, status, date, search, page = 1, limit = 20, sortField, sortDir } = filters;
  const offset = (page - 1) * limit;
  const conditions: SQL[] = [sql`b.restaurant_id = ${restaurantId}`];

  if (branchId) conditions.push(sql`b.branch_id = ${branchId}`);
  if (customerId) conditions.push(sql`b.customer_id = ${customerId}`);
  if (status) conditions.push(sql`b.status = ${status}`);
  if (date) conditions.push(sql`b.booking_date = ${date}`);
  if (search) {
    const pattern = `%${search}%`;
    conditions.push(sql`(b.guest_name ILIKE ${pattern} OR b.guest_phone ILIKE ${pattern})`);
  }

  const where = sql.join(conditions, sql` AND `);

  /* raw: SELECT COUNT(*) FROM bookings b WHERE ... */
  const { rows: countRows } = await db.execute(sql`SELECT COUNT(*) FROM bookings b WHERE ${where}`);
  const total = parseInt((countRows[0] as { count: string })?.count ?? '0', 10);

  const orderCol = BOOKING_SORT_COLS[sortField ?? ''] ?? 'b.booking_date';
  const orderDir = sortDir === 'asc' ? 'ASC' : 'DESC';

  /* raw: SELECT b.* FROM bookings b WHERE ... ORDER BY ... LIMIT ... OFFSET ... */
  const { rows } = await db.execute(sql`
    SELECT b.* FROM bookings b
    WHERE ${where}
    ORDER BY ${sql.raw(orderCol)} ${sql.raw(orderDir)}, b.booking_time ${sql.raw(orderDir)}
    LIMIT ${limit} OFFSET ${offset}
  `);

  return { data: rows, total, page, limit, pages: Math.ceil(total / limit) };
}

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

export async function createBooking(restaurantId: string, data: Record<string, unknown>) {
  await initDatabase();
  /* raw: INSERT INTO bookings (...) VALUES (...) RETURNING * */
  const { rows } = await db.execute(sql`
    INSERT INTO bookings (restaurant_id, branch_id, customer_id, guest_name, guest_phone, guest_email,
       table_no, party_size, booking_date, booking_time, zone, status, channel, occasion,
       dietary_needs, special_requests, ai_notes, is_vip, notes)
    VALUES (${restaurantId}, ${(data.branch_id as string) ?? null}, ${(data.customer_id as string) ?? null},
            ${data.guest_name as string}, ${(data.guest_phone as string) ?? null}, ${(data.guest_email as string) ?? null},
            ${(data.table_no as string) ?? null}, ${(data.party_size as number) ?? 1},
            ${(data.booking_date as string) ?? null}, ${(data.booking_time as string) ?? null}, ${(data.zone as string) ?? null},
            COALESCE(${(data.status as string) ?? null},'pending')::booking_status, ${(data.channel as string) ?? 'chat'}::channel_type,
            ${(data.occasion as string) ?? null},
            ${JSON.stringify(data.dietary_needs ?? [])}::jsonb, ${(data.special_requests as string) ?? null},
            ${(data.ai_notes as string) ?? null}, ${data.is_vip ?? false}, ${(data.notes as string) ?? null})
    RETURNING *
  `);
  const row = rows[0];
  if (row) dispatchWebhook(restaurantId, 'booking.created', row as Record<string, unknown>);
  return row;
}

export async function updateBookingStatus(id: string, restaurantId: string, status: string, notes?: string) {
  await initDatabase();
  /* raw: UPDATE bookings SET status = $1, notes = COALESCE($4, notes), updated_at = NOW() WHERE id = $2 AND restaurant_id = $3 RETURNING * */
  const { rows } = await db.execute(sql`
    UPDATE bookings SET status = ${status}, notes = COALESCE(${notes ?? null}, notes), updated_at = NOW()
    WHERE id = ${id} AND restaurant_id = ${restaurantId} RETURNING *
  `);
  if (!rows[0]) throw new NotFoundError('Booking');
  const event = status === 'cancelled' ? 'booking.cancelled' : 'booking.updated';
  dispatchWebhook(restaurantId, event, rows[0] as Record<string, unknown>);
  return rows[0];
}

export async function updateBooking(id: string, restaurantId: string, data: Record<string, unknown>) {
  await initDatabase();
  const allowed = ['guest_name','guest_phone','guest_email','table_no','party_size','booking_date',
    'booking_time','zone','status','occasion','special_requests','notes','is_vip','reminder_sent'];
  const sets: SQL[] = [];
  for (const [k, v] of Object.entries(data)) {
    if (!allowed.includes(k)) continue;
    sets.push(sql`${sql.raw(k)} = ${v 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 bookings SET ... WHERE id = $N AND restaurant_id = $N RETURNING * */
  const { rows } = await db.execute(sql`UPDATE bookings SET ${sql.join(sets, sql`, `)} WHERE id = ${id} AND restaurant_id = ${restaurantId} RETURNING *`);
  if (!rows[0]) throw new NotFoundError('Booking');
  dispatchWebhook(restaurantId, 'booking.updated', rows[0] as Record<string, unknown>);
  return rows[0];
}

export async function cancelBooking(id: string, restaurantId: string) {
  return updateBookingStatus(id, restaurantId, 'cancelled');
}

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