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

export interface CallLogFilters {
  restaurantId: string;
  direction?: string;
  status?: string;
  search?: string;
  dateFrom?: string;
  dateTo?: string;
  page?: number;
  limit?: number;
}

export async function listCallLogs(filters: CallLogFilters) {
  await initDatabase();
  const { restaurantId, direction, status, search, dateFrom, dateTo, page = 1, limit = 20 } = filters;
  const offset = (page - 1) * limit;
  const conditions: SQL[] = [sql`cl.restaurant_id = ${restaurantId}`];

  if (direction) conditions.push(sql`cl.direction = ${direction}`);
  if (status) conditions.push(sql`cl.status = ${status}`);
  if (dateFrom) conditions.push(sql`cl.created_at >= ${dateFrom}`);
  if (dateTo) conditions.push(sql`cl.created_at < (${dateTo}::date + interval '1 day')`);
  if (search) {
    const pattern = `%${search}%`;
    conditions.push(sql`(cust.name ILIKE ${pattern} OR cl.from_number ILIKE ${pattern} OR cl.to_number ILIKE ${pattern})`);
  }

  const where = sql.join(conditions, sql` AND `);
  const joinFrag = sql.raw(`
    FROM public.call_logs cl
    LEFT JOIN public.conversations cv ON cv.id = cl.conversation_id
    LEFT JOIN public.customers cust ON cust.id = cv.customer_id`);

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

  /* raw: SELECT cl.*, cust.id AS customer_id, cust.name AS customer_name, ... FROM ... WHERE ... ORDER BY cl.created_at DESC LIMIT ... OFFSET ... */
  const { rows } = await db.execute(sql`
    SELECT cl.*,
       cust.id AS customer_id,
       cust.name AS customer_name,
       cust.phone AS customer_phone,
       cust.email AS customer_email,
       cv.topic AS conversation_topic
     ${joinFrag}
     WHERE ${where}
     ORDER BY cl.created_at DESC
     LIMIT ${limit} OFFSET ${offset}
  `);

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

export async function getCallLog(id: string, restaurantId: string) {
  await initDatabase();
  /* raw: SELECT cl.*, cust.id AS customer_id, cust.name AS customer_name, ... FROM public.call_logs cl LEFT JOIN ... WHERE cl.id = $1 AND cl.restaurant_id = $2 */
  const { rows } = await db.execute(sql`
    SELECT cl.*,
       cust.id AS customer_id,
       cust.name AS customer_name,
       cust.phone AS customer_phone,
       cust.email AS customer_email,
       cv.topic AS conversation_topic,
       cv.messages AS conversation_messages
     FROM public.call_logs cl
     LEFT JOIN public.conversations cv ON cv.id = cl.conversation_id
     LEFT JOIN public.customers cust ON cust.id = cv.customer_id
     WHERE cl.id = ${id} AND cl.restaurant_id = ${restaurantId}
  `);
  if (!rows[0]) throw new NotFoundError('Call log');
  return rows[0];
}