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';

export interface ConversationFilters {
  restaurantId: string;
  branchId?: string;
  status?: string;
  channel?: string;
  search?: string;
  page?: number;
  limit?: number;
  startDate?: string;
  endDate?: string;
}

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

  if (branchId) conditions.push(sql`c.branch_id = ${branchId}`);
  if (status) conditions.push(sql`c.status = ${status}`);
  if (channel) conditions.push(sql`c.channel = ${channel}`);
  if (startDate) conditions.push(sql`c.created_at >= ${startDate}`);
  if (endDate) conditions.push(sql`c.created_at < (${endDate}::date + interval '1 day')`);
  if (search) {
    const pattern = `%${search}%`;
    conditions.push(sql`(c.customer_name ILIKE ${pattern} OR c.topic ILIKE ${pattern} OR cust.phone ILIKE ${pattern})`);
  }

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

  /* raw: SELECT COUNT(*) FROM public.conversations c LEFT JOIN public.customers cust ... 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 c.*, cust.phone AS customer_phone, cust.email AS customer_email FROM ... WHERE ... ORDER BY ... LIMIT ... OFFSET ... */
  const { rows } = await db.execute(sql`
    SELECT c.*, cust.phone AS customer_phone, cust.email AS customer_email
    ${joinFrag} WHERE ${where} ORDER BY c.updated_at DESC LIMIT ${limit} OFFSET ${offset}
  `);
  return { data: rows, total, page, limit, pages: Math.ceil(total / limit) };
}

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

export async function updateConversationStatus(id: string, restaurantId: string, status: string) {
  await initDatabase();
  /* raw: UPDATE conversations SET status = $1, updated_at = NOW() WHERE id = $2 AND restaurant_id = $3 RETURNING * */
  const { rows } = await db.execute(sql`
    UPDATE conversations SET status = ${status}, updated_at = NOW() WHERE id = ${id} AND restaurant_id = ${restaurantId} RETURNING *
  `);
  if (!rows[0]) throw new NotFoundError('Conversation');
  if (status === 'resolved') {
    dispatchWebhook(restaurantId, 'ai.conversation.ended', rows[0] as Record<string, unknown>);
  } else if (status === 'human') {
    dispatchWebhook(restaurantId, 'ai.escalation', rows[0] as Record<string, unknown>);
  }
  return rows[0];
}

export async function appendMessage(
  id: string,
  restaurantId: string,
  message: { role: string; content: string; timestamp?: string }
) {
  await initDatabase();
  const now = message.timestamp ?? new Date().toISOString();
  const newMsg = { ...message, timestamp: now };
  /* raw: UPDATE conversations SET messages = messages || $1::jsonb, unread_count = unread_count + 1, updated_at = NOW() WHERE id = $2 AND restaurant_id = $3 RETURNING * */
  const { rows } = await db.execute(sql`
    UPDATE conversations
    SET messages = messages || ${JSON.stringify([newMsg])}::jsonb, unread_count = unread_count + 1, updated_at = NOW()
    WHERE id = ${id} AND restaurant_id = ${restaurantId} RETURNING *
  `);
  if (!rows[0]) throw new NotFoundError('Conversation');
  return rows[0];
}

export async function markConversationRead(id: string, restaurantId: string) {
  await initDatabase();
  /* raw: UPDATE conversations SET unread_count = 0, updated_at = NOW() WHERE id = $1 AND restaurant_id = $2 RETURNING * */
  const { rows } = await db.execute(sql`
    UPDATE conversations SET unread_count = 0, updated_at = NOW()
    WHERE id = ${id} AND restaurant_id = ${restaurantId} RETURNING *
  `);
  if (!rows[0]) throw new NotFoundError('Conversation');
  return rows[0];
}

export async function assignConversation(id: string, restaurantId: string, assignedAgent: string | null) {
  await initDatabase();
  /* raw: UPDATE conversations SET assigned_agent = $1, updated_at = NOW() WHERE id = $2 AND restaurant_id = $3 RETURNING * */
  const { rows } = await db.execute(sql`
    UPDATE conversations SET assigned_agent = ${assignedAgent}, updated_at = NOW()
    WHERE id = ${id} AND restaurant_id = ${restaurantId} RETURNING *
  `);
  if (!rows[0]) throw new NotFoundError('Conversation');
  return rows[0];
}

export async function takeOverConversation(id: string, restaurantId: string, agentName: string) {
  await initDatabase();
  /* raw: UPDATE conversations SET status = 'human', assigned_agent = $1, updated_at = NOW() WHERE id = $2 AND restaurant_id = $3 RETURNING * */
  const { rows } = await db.execute(sql`
    UPDATE conversations SET status = 'human', assigned_agent = ${agentName}, updated_at = NOW()
    WHERE id = ${id} AND restaurant_id = ${restaurantId} RETURNING *
  `);
  if (!rows[0]) throw new NotFoundError('Conversation');
  dispatchWebhook(restaurantId, 'ai.escalation', rows[0] as Record<string, unknown>);
  return rows[0];
}

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