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

export interface CallSession {
  id: string;
  restaurant_id: string;
  phone_number_id: string | null;
  agent_id: string | null;
  sip_call_id: string | null;
  caller_number: string | null;
  callee_number: string | null;
  direction: string;
  status: string;
  started_at: string;
  ended_at: string | null;
  duration_seconds: number;
  transcript: unknown[];
  escalated_to_human: boolean;
  metadata: Record<string, unknown>;
  created_at: string;
  agent_name?: string;
  phone_display_name?: string;
}

export interface CallSessionFilters {
  status?: string;
  direction?: string;
  agentId?: string;
  dateFrom?: string;
  dateTo?: string;
  page?: number;
  limit?: number;
}

const CALL_SELECT = sql.raw(`
  cs.*,
  a.name AS agent_name,
  pn.display_name AS phone_display_name
FROM sip_call_sessions cs
LEFT JOIN ai_agents a ON a.id = cs.agent_id
LEFT JOIN phone_numbers pn ON pn.id = cs.phone_number_id
`);

export async function listCallSessions(
  restaurantId: string,
  filters: CallSessionFilters = {}
): Promise<{ sessions: CallSession[]; total: number }> {
  await initDatabase();

  const conditions: SQL[] = [sql`cs.restaurant_id = ${restaurantId}`];

  if (filters.status) conditions.push(sql`cs.status = ${filters.status}`);
  if (filters.direction) conditions.push(sql`cs.direction = ${filters.direction}`);
  if (filters.agentId) conditions.push(sql`cs.agent_id = ${filters.agentId}`);
  if (filters.dateFrom) conditions.push(sql`cs.started_at >= ${filters.dateFrom}`);
  if (filters.dateTo) conditions.push(sql`cs.started_at <= ${filters.dateTo}`);

  const where = sql.join(conditions, sql` AND `);
  const page = Math.max(1, filters.page || 1);
  const limit = Math.min(100, Math.max(1, filters.limit || 20));
  const offset = (page - 1) * limit;

  /* raw: SELECT cs.*, a.name AS agent_name, pn.display_name ... FROM sip_call_sessions cs LEFT JOIN ... WHERE ... ORDER BY ... */
  /* raw: SELECT COUNT(*) AS total FROM sip_call_sessions cs WHERE ... */
  const [sessionsResult, countResult] = await Promise.all([
    db.execute(sql`SELECT ${CALL_SELECT} WHERE ${where} ORDER BY cs.started_at DESC LIMIT ${limit} OFFSET ${offset}`),
    db.execute(sql`SELECT COUNT(*) AS total FROM sip_call_sessions cs WHERE ${where}`),
  ]);

  return {
    sessions: sessionsResult.rows as unknown as CallSession[],
    total: parseInt((countResult.rows[0] as { total: string })?.total || '0', 10),
  };
}

export async function getCallSession(
  id: string,
  restaurantId: string
): Promise<CallSession | null> {
  await initDatabase();
  /* raw: SELECT cs.*, ... FROM sip_call_sessions cs LEFT JOIN ... WHERE cs.id = $1 AND cs.restaurant_id = $2 */
  const { rows } = await db.execute(sql`SELECT ${CALL_SELECT} WHERE cs.id = ${id} AND cs.restaurant_id = ${restaurantId}`);
  return (rows[0] as unknown as CallSession) ?? null;
}