/**
 * Queries de Telemetria usando Prisma
 * Migrado de lib/db/queries/telemetry.ts
 */

import { Prisma } from "@prisma/client";
import { prisma } from "../app";

export type TelemetryEventType =
  | "ad_view"
  | "ad_click"
  | "ad_exit"
  | "search"
  | "filter"
  | "favorite"
  | "unfavorite"
  | "purchase"
  | "purchase_intention_created"
  | "purchase_intention_viewed"
  | "bid_placed"
  | "bid_viewed"
  | "seller_profile_viewed"
  | "inquiry"
  | "page_view"
  | "price_change";

export interface TelemetryEvent {
  event_id: number;
  event_type: TelemetryEventType;
  ad_id: number | null;
  user_id: number | null;
  session_id: string | null;
  metadata: Prisma.JsonValue;
  timestamp: Date;
}

export interface TrackEventData {
  event_type: TelemetryEventType;
  ad_id?: number;
  user_id?: number;
  session_id?: string;
  metadata?: Record<string, any>;
}

export interface AdAnalytics {
  ad_id: number;
  impressions: number;
  clicks: number;
  ctr: number;
  avg_view_duration: number;
  bounce_rate: number;
  inquiries: number;
  favorites: number;
  conversion_rate: number;
  time_to_sell: number | null;
  last_updated: Date;
}

export interface UserBehavior {
  user_id: number;
  search_queries: string[];
  filters_applied: Record<string, any>;
  items_viewed: Array<{
    item_id: number;
    duration: number;
    timestamp: Date;
  }>;
  items_favorited: number[];
  purchases: Array<{
    ad_id: number;
    price: number;
    timestamp: Date;
  }>;
  active_hours: Array<{
    hour: number;
    activity_count: number;
  }>;
}

export interface MarketplaceStats {
  total_transactions: number;
  avg_price_by_category: Record<string, number>;
  conversion_rate: number;
  most_searched_items: Array<{
    item_id: number;
    item_name: string;
    search_count: number;
  }>;
  most_sold_items: Array<{
    item_id: number;
    item_name: string;
    sales_count: number;
  }>;
  peak_hours: Array<{
    hour: number;
    activity_count: number;
  }>;
  avg_time_to_sell: number;
}

/**
 * Registra um evento de telemetria
 */
export async function trackEvent(
  data: TrackEventData,
): Promise<TelemetryEvent> {
  const event = await prisma.telemetryEvent.create({
    data: {
      event_type: data.event_type as TelemetryEventType,
      ad_id: data.ad_id || null,
      user_id: data.user_id || null,
      session_id: data.session_id || null,
      metadata: data.metadata || {},
    },
  });
  return {
    ...event,
    event_type: event.event_type as TelemetryEventType,
  };
}

/**
 * Registra múltiplos eventos em batch
 */
export async function trackEventsBatch(
  events: TrackEventData[],
): Promise<TelemetryEvent[]> {
  if (events.length === 0) return [];

  const eventsResult = await prisma.$transaction(async (tx) => {
    return await Promise.all(
      events.map((event) =>
        tx.telemetryEvent.create({
          data: {
            event_type: event.event_type as TelemetryEventType,
            ad_id: event.ad_id || null,
            user_id: event.user_id || null,
            session_id: event.session_id || null,
            metadata: event.metadata || {},
          },
        }),
      ),
    );
  });
  return eventsResult.map((event) => ({
    ...event,
    event_type: event.event_type as TelemetryEventType,
  }));
}

/**
 * Busca analytics de um anúncio específico
 */
export async function getAdAnalytics(
  adId: number,
): Promise<AdAnalytics | null> {
  const analytics = await prisma.adAnalytics.findUnique({
    where: { ad_id: adId },
  });
  if (!analytics) return null;
  return {
    ...analytics,
    ctr: Number(analytics.ctr),
    avg_view_duration: Number(analytics.avg_view_duration),
    bounce_rate: Number(analytics.bounce_rate),
    conversion_rate: Number(analytics.conversion_rate),
  };
}

/**
 * Busca comportamento do usuário (comprador)
 */
export async function getUserBehavior(
  userId: number,
  startDate?: Date,
  endDate?: Date,
): Promise<UserBehavior | null> {
  // Verificar se usuário existe
  const user = await prisma.user.findUnique({
    where: { user_id: userId },
    select: { user_id: true },
  });

  if (!user) {
    return null;
  }

  const dateFilter: Prisma.TelemetryEventWhereInput = {};
  if (startDate && endDate) {
    dateFilter.timestamp = {
      gte: startDate,
      lte: endDate,
    };
  }

  // Buscar queries de busca
  const searchEvents = await prisma.telemetryEvent.findMany({
    where: {
      user_id: userId,
      event_type: "search",
      ...dateFilter,
    },
    select: {
      metadata: true,
    },
  });

  // Buscar filtros aplicados
  const filterEvents = await prisma.telemetryEvent.findMany({
    where: {
      user_id: userId,
      event_type: "filter",
      ...dateFilter,
    },
    select: {
      metadata: true,
    },
  });

  // Buscar itens visualizados
  const viewEvents = await prisma.telemetryEvent.findMany({
    where: {
      user_id: userId,
      event_type: "ad_view",
      ad_id: { not: null },
      ...dateFilter,
    },
    select: {
      ad_id: true,
      metadata: true,
      timestamp: true,
    },
    orderBy: { timestamp: "desc" },
  });

  // Buscar favoritos
  const favoriteEvents = await prisma.telemetryEvent.findMany({
    where: {
      user_id: userId,
      event_type: "favorite",
      ad_id: { not: null },
      ...dateFilter,
    },
    select: {
      ad_id: true,
    },
    distinct: ["ad_id"],
  });

  // Buscar compras
  const purchaseEvents = await prisma.telemetryEvent.findMany({
    where: {
      user_id: userId,
      event_type: "purchase",
      ad_id: { not: null },
      ...dateFilter,
    },
    select: {
      ad_id: true,
      metadata: true,
      timestamp: true,
    },
    orderBy: { timestamp: "desc" },
  });

  // Buscar horas ativas
  const activeHoursRaw = await prisma.$queryRaw<
    Array<{ hour: number; count: bigint }>
  >`
    SELECT 
      EXTRACT(HOUR FROM timestamp)::INTEGER as hour,
      COUNT(*) as count
    FROM telemetry_events
    WHERE user_id = ${userId}
    ${startDate && endDate ? Prisma.sql`AND timestamp BETWEEN ${startDate} AND ${endDate}` : Prisma.empty}
    GROUP BY hour
    ORDER BY hour
  `;

  // Buscar item_id dos anúncios visualizados
  const _itemIds = await prisma.$queryRaw<Array<{ item_id: number }>>`
    SELECT DISTINCT l.item_id
    FROM telemetry_events te
    JOIN listings l ON l.listing_id = te.ad_id
    WHERE te.user_id = ${userId}
      AND te.event_type = 'ad_view'
      AND te.ad_id IS NOT NULL
    ${startDate && endDate ? Prisma.sql`AND te.timestamp BETWEEN ${startDate} AND ${endDate}` : Prisma.empty}
  `;

  return {
    user_id: userId,
    search_queries: searchEvents
      .map((r) => (r.metadata as any)?.query)
      .filter((q): q is string => !!q),
    filters_applied: filterEvents.reduce(
      (acc, r) => ({ ...acc, ...(r.metadata as any) }),
      {} as Record<string, any>,
    ),
    items_viewed: viewEvents.map((r) => ({
      item_id: r.ad_id!,
      duration: ((r.metadata as any)?.duration || 0) as number,
      timestamp: r.timestamp,
    })),
    items_favorited: favoriteEvents.map((r) => r.ad_id!),
    purchases: purchaseEvents.map((r) => ({
      ad_id: r.ad_id!,
      price: ((r.metadata as any)?.price || 0) as number,
      timestamp: r.timestamp,
    })),
    active_hours: activeHoursRaw.map((r) => ({
      hour: r.hour,
      activity_count: Number(r.count),
    })),
  };
}

/**
 * Busca métricas agregadas do marketplace
 */
export async function getMarketplaceStats(
  startDate?: Date,
  endDate?: Date,
): Promise<MarketplaceStats> {
  const dateFilter =
    startDate && endDate
      ? Prisma.sql`WHERE timestamp BETWEEN ${startDate} AND ${endDate}`
      : Prisma.empty;

  // Total de transações
  const transactionsResult = await prisma.$queryRaw<Array<{ count: bigint }>>`
    SELECT COUNT(*)::TEXT as count
    FROM telemetry_events
    WHERE event_type = 'purchase'
    ${dateFilter}
  `;
  const total_transactions = Number(transactionsResult[0]?.count || 0);

  // Preço médio por categoria
  const avgPriceResult = await prisma.$queryRaw<
    Array<{ item_name: string; avg_price: string }>
  >`
    SELECT 
      l.item_name,
      AVG(COALESCE(l.price_pix, l.price_tibia_coins, l.price_gold_coins))::TEXT as avg_price
    FROM listings l
    JOIN telemetry_events te ON te.ad_id = l.listing_id
    WHERE te.event_type = 'purchase'
    ${startDate && endDate ? Prisma.sql`AND te.timestamp BETWEEN ${startDate} AND ${endDate}` : Prisma.empty}
    GROUP BY l.item_name
    LIMIT 10
  `;

  // Taxa de conversão
  const viewsResult = await prisma.$queryRaw<Array<{ count: bigint }>>`
    SELECT COUNT(*) as count
    FROM telemetry_events
    WHERE event_type = 'ad_view'
    ${dateFilter}
  `;
  const purchasesResult = await prisma.$queryRaw<Array<{ count: bigint }>>`
    SELECT COUNT(*) as count
    FROM telemetry_events
    WHERE event_type = 'purchase'
    ${dateFilter}
  `;

  const viewsCount = Number(viewsResult[0]?.count || 0);
  const purchasesCount = Number(purchasesResult[0]?.count || 0);
  const conversion_rate =
    viewsCount > 0 ? (purchasesCount / viewsCount) * 100 : 0;

  // Itens mais procurados
  const mostSearchedResult = await prisma.$queryRaw<
    Array<{ item_name: string; search_count: bigint }>
  >`
    SELECT 
      (metadata->>'query') as item_name,
      COUNT(*) as search_count
    FROM telemetry_events
    WHERE event_type = 'search' AND (metadata->>'query') IS NOT NULL
    ${dateFilter}
    GROUP BY (metadata->>'query')
    ORDER BY COUNT(*) DESC
    LIMIT 10
  `;

  // Itens mais vendidos
  const mostSoldResult = await prisma.$queryRaw<
    Array<{ item_id: number; item_name: string; sales_count: bigint }>
  >`
    SELECT 
      l.item_id,
      l.item_name,
      COUNT(*) as sales_count
    FROM telemetry_events te
    JOIN listings l ON l.listing_id = te.ad_id
    WHERE te.event_type = 'purchase'
    ${startDate && endDate ? Prisma.sql`AND te.timestamp BETWEEN ${startDate} AND ${endDate}` : Prisma.empty}
    GROUP BY l.item_id, l.item_name
    ORDER BY COUNT(*) DESC
    LIMIT 10
  `;

  // Horários de pico
  const peakHoursResult = await prisma.$queryRaw<
    Array<{ hour: number; activity_count: bigint }>
  >`
    SELECT 
      EXTRACT(HOUR FROM timestamp)::INTEGER as hour,
      COUNT(*) as activity_count
    FROM telemetry_events
    ${dateFilter}
    GROUP BY hour
    ORDER BY COUNT(*) DESC
    LIMIT 10
  `;

  // Tempo médio para venda
  const avgTimeResult = await prisma.$queryRaw<Array<{ avg_hours: string }>>`
    SELECT AVG(aa.time_to_sell)::TEXT as avg_hours
    FROM ad_analytics aa
    WHERE aa.time_to_sell IS NOT NULL
  `;

  return {
    total_transactions,
    avg_price_by_category: avgPriceResult.reduce(
      (acc, r) => {
        acc[r.item_name] = parseFloat(r.avg_price);
        return acc;
      },
      {} as Record<string, number>,
    ),
    conversion_rate,
    most_searched_items: mostSearchedResult.map((r) => ({
      item_id: 0,
      item_name: r.item_name || "",
      search_count: Number(r.search_count),
    })),
    most_sold_items: mostSoldResult.map((r) => ({
      item_id: r.item_id,
      item_name: r.item_name,
      sales_count: Number(r.sales_count),
    })),
    peak_hours: peakHoursResult.map((r) => ({
      hour: r.hour,
      activity_count: Number(r.activity_count),
    })),
    avg_time_to_sell: parseFloat(avgTimeResult[0]?.avg_hours || "0"),
  };
}

/**
 * Agrega eventos de telemetria em métricas de analytics
 * Executar periodicamente (ex: a cada hora)
 */
export async function aggregateHourlyMetrics(): Promise<void> {
  // Usar $queryRaw para queries complexas de agregação
  // Por enquanto, manter a lógica original mas adaptada para Prisma

  // Agregar impressões
  await prisma.$executeRaw`
    INSERT INTO ad_analytics (ad_id, impressions, last_updated)
    SELECT 
      ad_id,
      COUNT(*) as impressions,
      CURRENT_TIMESTAMP
    FROM telemetry_events
    WHERE event_type = 'ad_view' 
      AND ad_id IS NOT NULL
      AND timestamp >= NOW() - INTERVAL '1 hour'
    GROUP BY ad_id
    ON CONFLICT (ad_id) DO UPDATE SET
      impressions = ad_analytics.impressions + EXCLUDED.impressions,
      last_updated = CURRENT_TIMESTAMP
  `;

  // Agregar cliques
  await prisma.$executeRaw`
    UPDATE ad_analytics aa
    SET clicks = clicks + sub.count,
        last_updated = CURRENT_TIMESTAMP
    FROM (
      SELECT ad_id, COUNT(*) as count
      FROM telemetry_events
      WHERE event_type = 'ad_click'
        AND ad_id IS NOT NULL
        AND timestamp >= NOW() - INTERVAL '1 hour'
      GROUP BY ad_id
    ) sub
    WHERE aa.ad_id = sub.ad_id
  `;

  // Atualizar CTR
  await prisma.$executeRaw`
    UPDATE ad_analytics
    SET ctr = CASE 
      WHEN impressions > 0 THEN (clicks::DECIMAL / impressions::DECIMAL) * 100
      ELSE 0
    END,
    last_updated = CURRENT_TIMESTAMP
    WHERE impressions > 0
  `;

  // Agregar duração média de visualização
  await prisma.$executeRaw`
    UPDATE ad_analytics aa
    SET 
      avg_view_duration = COALESCE(
        (
          SELECT AVG((metadata->>'duration')::DECIMAL)
          FROM telemetry_events
          WHERE event_type = 'ad_exit'
            AND ad_id = aa.ad_id
            AND timestamp >= NOW() - INTERVAL '1 hour'
            AND metadata->>'duration' IS NOT NULL
        ),
        avg_view_duration
      ),
      last_updated = CURRENT_TIMESTAMP
    WHERE EXISTS (
      SELECT 1 FROM telemetry_events
      WHERE event_type = 'ad_exit'
        AND ad_id = aa.ad_id
        AND timestamp >= NOW() - INTERVAL '1 hour'
    )
  `;

  // Calcular bounce rate
  await prisma.$executeRaw`
    UPDATE ad_analytics aa
    SET 
      bounce_rate = CASE
        WHEN aa.impressions > 0 THEN
          (
            SELECT (COUNT(*)::DECIMAL / aa.impressions::DECIMAL) * 100
            FROM telemetry_events
            WHERE event_type = 'ad_exit'
              AND ad_id = aa.ad_id
              AND timestamp >= NOW() - INTERVAL '1 hour'
              AND (metadata->>'duration')::DECIMAL < 3
          )
        ELSE 0
      END,
      last_updated = CURRENT_TIMESTAMP
    WHERE aa.impressions > 0
  `;

  // Agregar inquiries
  await prisma.$executeRaw`
    UPDATE ad_analytics aa
    SET inquiries = inquiries + sub.count,
        last_updated = CURRENT_TIMESTAMP
    FROM (
      SELECT ad_id, COUNT(*) as count
      FROM telemetry_events
      WHERE event_type = 'inquiry'
        AND ad_id IS NOT NULL
        AND timestamp >= NOW() - INTERVAL '1 hour'
      GROUP BY ad_id
    ) sub
    WHERE aa.ad_id = sub.ad_id
  `;

  // Agregar favoritos
  await prisma.$executeRaw`
    UPDATE ad_analytics aa
    SET favorites = favorites + sub.count,
        last_updated = CURRENT_TIMESTAMP
    FROM (
      SELECT ad_id, COUNT(*) as count
      FROM telemetry_events
      WHERE event_type = 'favorite'
        AND ad_id IS NOT NULL
        AND timestamp >= NOW() - INTERVAL '1 hour'
      GROUP BY ad_id
    ) sub
    WHERE aa.ad_id = sub.ad_id
  `;

  // Calcular taxa de conversão
  await prisma.$executeRaw`
    UPDATE ad_analytics aa
    SET 
      conversion_rate = CASE
        WHEN aa.impressions > 0 THEN
          (
            SELECT (COUNT(*)::DECIMAL / aa.impressions::DECIMAL) * 100
            FROM telemetry_events
            WHERE event_type = 'purchase'
              AND ad_id = aa.ad_id
              AND timestamp >= NOW() - INTERVAL '1 hour'
          )
        ELSE 0
      END,
      last_updated = CURRENT_TIMESTAMP
    WHERE EXISTS (
      SELECT 1 FROM telemetry_events
      WHERE event_type = 'purchase'
        AND ad_id = aa.ad_id
        AND timestamp >= NOW() - INTERVAL '1 hour'
    )
  `;

  // Calcular time_to_sell
  await prisma.$executeRaw`
    UPDATE ad_analytics aa
    SET 
      time_to_sell = EXTRACT(EPOCH FROM (l.sold_at - l.created_at)) / 3600,
      last_updated = CURRENT_TIMESTAMP
    FROM listings l
    WHERE aa.ad_id = l.listing_id
      AND l.listing_status = 'sold'
      AND l.sold_at IS NOT NULL
      AND aa.time_to_sell IS NULL
      AND l.sold_at >= NOW() - INTERVAL '1 hour'
  `;
}