import { db } from '@server/db/drizzle';
import { sql, type SQL } from 'drizzle-orm';
import { initDatabase } from '@server/db/init';
import { NotFoundError, ConflictError, ValidationError } from '@server/errors';
import { dispatchWebhook } from './webhooks.service';

export interface CustomerFilters {
  restaurantId: string;
  search?: string;
  page?: number;
  limit?: number;
}

export async function listCustomers(filters: CustomerFilters) {
  await initDatabase();
  const { restaurantId, search, page = 1, limit = 20 } = filters;
  const offset = (page - 1) * limit;
  const conditions: SQL[] = [sql`c.restaurant_id = ${restaurantId}`];

  if (search) {
    const pattern = `%${search}%`;
    conditions.push(sql`(c.name ILIKE ${pattern} OR c.email ILIKE ${pattern} OR c.phone ILIKE ${pattern})`);
  }

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

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

  /* raw: SELECT c.*, COALESCE(o.order_count, 0) AS total_orders, ... FROM customers c LEFT JOIN ... */
  const { rows } = await db.execute(sql`
    SELECT c.*,
      COALESCE(o.order_count, 0) AS total_orders,
      COALESCE(o.order_total, 0) AS computed_spend,
      COALESCE(b.booking_count, 0) AS total_reservations,
      GREATEST(
        COALESCE(o.last_order_at, c.created_at),
        COALESCE(b.last_booking_at, c.created_at),
        c.created_at
      ) AS last_visit
     FROM customers c
     LEFT JOIN (
       SELECT customer_id, COUNT(*) AS order_count, SUM(total) AS order_total, MAX(created_at) AS last_order_at
       FROM orders WHERE restaurant_id = ${restaurantId} GROUP BY customer_id
     ) o ON o.customer_id = c.id
     LEFT JOIN (
       SELECT customer_id, COUNT(*) AS booking_count, MAX(created_at) AS last_booking_at
       FROM bookings WHERE restaurant_id = ${restaurantId} GROUP BY customer_id
     ) b ON b.customer_id = c.id
     WHERE ${where}
     ORDER BY COALESCE(o.order_total, 0) DESC, c.created_at DESC
     LIMIT ${limit} OFFSET ${offset}
  `);
  return { data: rows, total, page, limit, pages: Math.ceil(total / limit) };
}

export async function getCustomer(id: string, restaurantId: string) {
  await initDatabase();
  /* raw: SELECT c.*, ... FROM customers c LEFT JOIN ... WHERE c.id = $1 AND c.restaurant_id = $2 */
  const { rows } = await db.execute(sql`
    SELECT c.*,
      COALESCE(o.order_count, 0) AS total_orders,
      COALESCE(o.order_total, 0) AS computed_spend,
      COALESCE(b.booking_count, 0) AS total_reservations,
      GREATEST(
        COALESCE(o.last_order_at, c.created_at),
        COALESCE(b.last_booking_at, c.created_at),
        c.created_at
      ) AS last_visit
     FROM customers c
     LEFT JOIN (
       SELECT customer_id, COUNT(*) AS order_count, SUM(total) AS order_total, MAX(created_at) AS last_order_at
       FROM orders WHERE restaurant_id = ${restaurantId} GROUP BY customer_id
     ) o ON o.customer_id = c.id
     LEFT JOIN (
       SELECT customer_id, COUNT(*) AS booking_count, MAX(created_at) AS last_booking_at
       FROM bookings WHERE restaurant_id = ${restaurantId} GROUP BY customer_id
     ) b ON b.customer_id = c.id
     WHERE c.id = ${id} AND c.restaurant_id = ${restaurantId}
  `);
  if (!rows[0]) throw new NotFoundError('Customer');
  return rows[0];
}

export async function createCustomer(restaurantId: string, data: Record<string, unknown>) {
  await initDatabase();
  if (data.email) {
    /* raw: SELECT id FROM customers WHERE restaurant_id = $1 AND email = $2 */
    const { rows: existing } = await db.execute(sql`SELECT id FROM customers WHERE restaurant_id = ${restaurantId} AND email = ${data.email as string}`);
    if (existing[0]) throw new ConflictError('A customer with this email already exists');
  }
  /* raw: INSERT INTO customers (...) VALUES (...) RETURNING * */
  const { rows } = await db.execute(sql`
    INSERT INTO customers (restaurant_id, name, email, phone, preferences, tags, notes)
    VALUES (${restaurantId}, ${data.name as string}, ${(data.email as string) ?? null}, ${(data.phone as string) ?? null},
            ${JSON.stringify(data.preferences ?? [])}::jsonb, ${JSON.stringify(data.tags ?? [])}::jsonb,
            ${(data.notes as string) ?? null})
    RETURNING *
  `);
  const row = rows[0];
  if (row) dispatchWebhook(restaurantId, 'customer.created', row as Record<string, unknown>);
  return row;
}

export async function updateCustomer(id: string, restaurantId: string, data: Record<string, unknown>) {
  await initDatabase();
  const allowed = ['name','email','phone','preferences','tags','notes','total_visits','total_spend'];
  const sets: SQL[] = [];
  for (const [k, v] of Object.entries(data)) {
    if (!allowed.includes(k)) continue;
    const val = (k === 'preferences' || k === 'tags') ? 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 customers SET ... WHERE id = $N AND restaurant_id = $N RETURNING * */
  const { rows } = await db.execute(sql`UPDATE customers SET ${sql.join(sets, sql`, `)} WHERE id = ${id} AND restaurant_id = ${restaurantId} RETURNING *`);
  if (!rows[0]) throw new NotFoundError('Customer');
  dispatchWebhook(restaurantId, 'customer.updated', rows[0] as Record<string, unknown>);
  return rows[0];
}

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