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

const ORDER_SORT_COLS: Record<string, string> = {
  placed_at: 'o.created_at',
  created_at: 'o.created_at',
  total: 'o.total',
  status: 'o.status',
  customer_name: 'o.customer_name',
};

export interface OrderFilters {
  restaurantId: string;
  branchId?: string;
  customerId?: string;
  status?: string;
  channel?: string;
  page?: number;
  limit?: number;
  search?: string;
  startDate?: string;
  endDate?: string;
  sortField?: string;
  sortDir?: string;
}

export async function listOrders(filters: OrderFilters) {
  await initDatabase();
  const { restaurantId, branchId, customerId, status, channel, search, page = 1, limit = 20, startDate, endDate, sortField, sortDir } = filters;
  const offset = (page - 1) * limit;
  const conditions: SQL[] = [sql`o.restaurant_id = ${restaurantId}`];

  if (branchId) conditions.push(sql`o.branch_id = ${branchId}`);
  if (customerId) conditions.push(sql`o.customer_id = ${customerId}`);
  if (status) conditions.push(sql`o.status = ${status}`);
  if (channel) conditions.push(sql`o.channel = ${channel}`);
  if (startDate) conditions.push(sql`o.created_at >= ${startDate}`);
  if (endDate) conditions.push(sql`o.created_at <= ${endDate}`);
  if (search) {
    const pattern = `%${search}%`;
    conditions.push(sql`(o.customer_name ILIKE ${pattern} OR o.id::text ILIKE ${pattern})`);
  }

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

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

  const orderCol = ORDER_SORT_COLS[sortField ?? ''] ?? 'o.created_at';
  const orderDir = sortDir === 'asc' ? 'ASC' : 'DESC';

  /* raw: SELECT o.*, c.name as customer_full_name FROM orders o LEFT JOIN customers c ... WHERE ... ORDER BY ... LIMIT ... OFFSET ... */
  const { rows } = await db.execute(sql`
    SELECT o.*, c.name as customer_full_name
    FROM orders o
    LEFT JOIN customers c ON o.customer_id = c.id
    WHERE ${where}
    ORDER BY ${sql.raw(orderCol)} ${sql.raw(orderDir)}
    LIMIT ${limit} OFFSET ${offset}
  `);

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

export async function getOrder(id: string, restaurantId: string) {
  await initDatabase();
  /* raw: SELECT o.*, c.name as customer_full_name, c.email as customer_email FROM orders o LEFT JOIN customers c ... WHERE o.id = $1 AND o.restaurant_id = $2 */
  const { rows } = await db.execute(sql`
    SELECT o.*, c.name as customer_full_name, c.email as customer_email
    FROM orders o
    LEFT JOIN customers c ON o.customer_id = c.id
    WHERE o.id = ${id} AND o.restaurant_id = ${restaurantId}
  `);
  if (!rows[0]) throw new NotFoundError('Order');
  return rows[0];
}

export async function createOrder(restaurantId: string, data: Record<string, unknown>) {
  await initDatabase();
  /* raw: BEGIN; pg_advisory_xact_lock; SELECT MAX(order_number)+1; INSERT INTO orders ... RETURNING *; COMMIT */
  const row = await db.transaction(async (tx) => {
    await tx.execute(sql`SELECT pg_advisory_xact_lock(hashtext(${restaurantId})::bigint)`);
    const { rows: [numRow] } = await tx.execute(sql`SELECT COALESCE(MAX(order_number), 0) + 1 AS next_num FROM orders WHERE restaurant_id = ${restaurantId}`);
    const nextOrderNumber = parseInt((numRow as { next_num: string })?.next_num ?? '1', 10);
    const { rows: [inserted] } = await tx.execute(sql`
      INSERT INTO orders (restaurant_id, branch_id, customer_id, customer_name, customer_phone,
         customer_email, items, status, channel, delivery_type, table_number, subtotal, tax, total,
         special_instructions, agent_id, conversation_id, ai_confidence, delivery_address, order_number)
      VALUES (${restaurantId}, ${(data.branch_id as string) ?? null}, ${(data.customer_id as string) ?? null},
              ${(data.customer_name as string) ?? null}, ${(data.customer_phone as string) ?? null},
              ${(data.customer_email as string) ?? null},
              ${JSON.stringify(data.items ?? [])}::jsonb, COALESCE(${(data.status as string) ?? null}::order_status,'pending'::order_status),
              ${(data.channel as string) ?? 'chat'}::channel_type, COALESCE(${(data.delivery_type as string) ?? null},'dine-in'),
              ${(data.table_number as string) ?? null},
              ${(data.subtotal as number) ?? 0}, ${(data.tax as number) ?? 0}, ${(data.total as number) ?? 0},
              ${(data.special_instructions as string) ?? null}, ${(data.agent_id as string) ?? null},
              ${(data.conversation_id as string) ?? null}, ${(data.ai_confidence as number) ?? null},
              ${(data.delivery_address as string) ?? null}, ${nextOrderNumber})
      RETURNING *
    `);
    return inserted ?? null;
  });
  if (row) dispatchWebhook(restaurantId, 'order.created', row as Record<string, unknown>);
  return row;
}

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

export async function updateOrderDetails(id: string, restaurantId: string, data: Record<string, unknown>) {
  await initDatabase();
  const sets: SQL[] = [sql`updated_at = NOW()`, sql`is_modified = true`];

  if (data.items !== undefined) {
    const items = data.items as Array<{ price?: unknown; quantity?: unknown }>;
    const subtotal = items.reduce((sum, i) => sum + Number(i.price ?? 0) * Number(i.quantity ?? 1), 0);
    const tax = Math.round(subtotal * 0.08 * 100) / 100;
    const total = Math.round((subtotal + tax) * 100) / 100;
    sets.push(sql`items = ${JSON.stringify(items)}::jsonb`);
    sets.push(sql`subtotal = ${subtotal}`);
    sets.push(sql`tax = ${tax}`);
    sets.push(sql`total = ${total}`);
  }
  if (data.customer_name !== undefined) sets.push(sql`customer_name = ${(data.customer_name as string) ?? null}`);
  if (data.customer_phone !== undefined) sets.push(sql`customer_phone = ${(data.customer_phone as string) ?? null}`);
  if (data.delivery_type !== undefined) sets.push(sql`delivery_type = ${(data.delivery_type as string) ?? null}`);
  if (data.delivery_address !== undefined) sets.push(sql`delivery_address = ${(data.delivery_address as string) ?? null}`);
  if (data.table_number !== undefined) sets.push(sql`table_number = ${(data.table_number as string) ?? null}`);
  if (data.special_instructions !== undefined) sets.push(sql`special_instructions = ${(data.special_instructions as string) ?? null}`);
  if (data.modification_note !== undefined) sets.push(sql`modification_note = ${(data.modification_note as string) ?? null}`);
  if (data.status !== undefined) sets.push(sql`status = ${data.status as string}::order_status`);

  /* raw: UPDATE orders SET ... WHERE id = $1 AND restaurant_id = $2 RETURNING * */
  const { rows } = await db.execute(sql`UPDATE orders SET ${sql.join(sets, sql`, `)} WHERE id = ${id} AND restaurant_id = ${restaurantId} RETURNING *`);
  if (!rows[0]) throw new NotFoundError('Order');
  dispatchWebhook(restaurantId, 'order.updated', rows[0] as Record<string, unknown>);
  return rows[0];
}

export async function cancelOrder(id: string, restaurantId: string, reason?: string) {
  return updateOrderStatus(id, restaurantId, 'cancelled', reason);
}

export async function getOrderStats(restaurantId: string, branchId?: string) {
  await initDatabase();
  const conditions: SQL[] = [sql`restaurant_id = ${restaurantId}`];
  if (branchId) conditions.push(sql`branch_id = ${branchId}`);
  const where = sql.join(conditions, sql` AND `);

  /* raw: SELECT COUNT(*) FILTER (...) as pending, ... FROM orders WHERE ... */
  const { rows } = await db.execute(sql`
    SELECT
      COUNT(*) FILTER (WHERE status = 'pending') as pending,
      COUNT(*) FILTER (WHERE status IN ('confirmed','kitchen','ready')) as active,
      COUNT(*) FILTER (WHERE status = 'completed') as completed,
      COUNT(*) FILTER (WHERE status = 'cancelled') as cancelled,
      COUNT(*) as total,
      COALESCE(SUM(total) FILTER (WHERE status = 'completed'), 0) as revenue
    FROM orders WHERE ${where}
  `);
  return rows[0];
}

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