/**
 * Queries de Telemetria
 * Sistema completo de tracking e analytics
 */

import { query } from "../index";

export type TelemetryEventType =
  | "ad_view"
  | "ad_click"
  | "ad_exit"
  | "search"
  | "filter"
  | "favorite"
  | "unfavorite"
  | "purchase"
  | "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: Record<string, any>;
  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 result = await query<TelemetryEvent>(
    `
    INSERT INTO telemetry_events (
      event_type, ad_id, user_id, session_id, metadata
    )
    VALUES ($1, $2, $3, $4, $5)
    RETURNING *
    `,
    [
      data.event_type,
      data.ad_id || null,
      data.user_id || null,
      data.session_id || null,
      JSON.stringify(data.metadata || {}),
    ],
  );

  return result.rows[0];
}

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

  const values: any[] = [];
  const placeholders: string[] = [];
  let paramIndex = 1;

  for (const event of events) {
    placeholders.push(
      `($${paramIndex}, $${paramIndex + 1}, $${paramIndex + 2}, $${paramIndex + 3}, $${paramIndex + 4})`,
    );
    values.push(
      event.event_type,
      event.ad_id || null,
      event.user_id || null,
      event.session_id || null,
      JSON.stringify(event.metadata || {}),
    );
    paramIndex += 5;
  }

  const result = await query<TelemetryEvent>(
    `
    INSERT INTO telemetry_events (
      event_type, ad_id, user_id, session_id, metadata
    )
    VALUES ${placeholders.join(", ")}
    RETURNING *
    `,
    values,
  );

  return result.rows;
}

/**
 * Busca analytics de um anúncio específico
 */
export async function getAdAnalytics(
  adId: number,
): Promise<AdAnalytics | null> {
  const result = await query<AdAnalytics>(
    `
    SELECT * FROM ad_analytics
    WHERE ad_id = $1
    `,
    [adId],
  );

  return result.rows[0] || null;
}

/**
 * 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 userCheck = await query<{ user_id: number }>(
    "SELECT user_id FROM users WHERE user_id = $1",
    [userId],
  );

  if (userCheck.rows.length === 0) {
    return null;
  }

  const dateFilter =
    startDate && endDate ? "AND timestamp BETWEEN $2 AND $3" : "";

  const params: any[] = [userId];
  if (startDate && endDate) {
    params.push(startDate.getTime(), endDate.getTime());
  }

  // Buscar queries de busca
  const searchQueries = await query<{ metadata: { query?: string } }>(
    `
    SELECT DISTINCT metadata->>'query' as query
    FROM telemetry_events
    WHERE user_id = $1 AND event_type = 'search' ${dateFilter}
    `,
    params,
  );

  // Buscar filtros aplicados
  const filters = await query<{ metadata: Record<string, any> }>(
    `
    SELECT DISTINCT metadata
    FROM telemetry_events
    WHERE user_id = $1 AND event_type = 'filter' ${dateFilter}
    `,
    params,
  );

  // Buscar itens visualizados
  const itemsViewed = await query<{
    ad_id: number;
    metadata: { duration?: number };
    timestamp: Date;
  }>(
    `
    SELECT ad_id, metadata, timestamp
    FROM telemetry_events
    WHERE user_id = $1 AND event_type = 'ad_view' AND ad_id IS NOT NULL ${dateFilter}
    ORDER BY timestamp DESC
    `,
    params,
  );

  // Buscar favoritos
  const favorites = await query<{ ad_id: number }>(
    `
    SELECT DISTINCT ad_id
    FROM telemetry_events
    WHERE user_id = $1 AND event_type = 'favorite' AND ad_id IS NOT NULL ${dateFilter}
    `,
    params,
  );

  // Buscar compras
  const purchases = await query<{
    ad_id: number;
    metadata: { price?: number };
    timestamp: Date;
  }>(
    `
    SELECT ad_id, metadata, timestamp
    FROM telemetry_events
    WHERE user_id = $1 AND event_type = 'purchase' AND ad_id IS NOT NULL ${dateFilter}
    ORDER BY timestamp DESC
    `,
    params,
  );

  // Buscar horas ativas
  const activeHours = await query<{
    hour: number;
    count: string;
  }>(
    `
    SELECT EXTRACT(HOUR FROM timestamp)::INTEGER as hour, COUNT(*)::TEXT as count
    FROM telemetry_events
    WHERE user_id = $1 ${dateFilter}
    GROUP BY hour
    ORDER BY hour
    `,
    params,
  );

  // Buscar item_id dos anúncios visualizados
  const _itemIds = await query<{ 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 = $1 AND te.event_type = 'ad_view' AND te.ad_id IS NOT NULL ${dateFilter}
    `,
    params,
  );

  return {
    user_id: userId,
    search_queries: searchQueries.rows
      .map((r) => r.metadata?.query)
      .filter((q): q is string => !!q),
    filters_applied: filters.rows.reduce((acc, r) => {
      return { ...acc, ...r.metadata };
    }, {}),
    items_viewed: itemsViewed.rows.map((r) => ({
      item_id: r.ad_id,
      duration: r.metadata?.duration || 0,
      timestamp: r.timestamp,
    })),
    items_favorited: favorites.rows.map((r) => r.ad_id),
    purchases: purchases.rows.map((r) => ({
      ad_id: r.ad_id,
      price: r.metadata?.price || 0,
      timestamp: r.timestamp,
    })),
    active_hours: activeHours.rows.map((r) => ({
      hour: r.hour,
      activity_count: parseInt(r.count, 10),
    })),
  };
}

/**
 * Busca métricas agregadas do marketplace
 */
export async function getMarketplaceStats(
  startDate?: Date,
  endDate?: Date,
): Promise<MarketplaceStats> {
  const dateFilter =
    startDate && endDate ? "WHERE timestamp BETWEEN $1 AND $2" : "";
  const params = startDate && endDate ? [startDate, endDate] : [];

  // Total de transações
  const transactions = await query<{ count: string }>(
    `
    SELECT COUNT(*)::TEXT as count
    FROM telemetry_events
    WHERE event_type = 'purchase' ${dateFilter.replace("timestamp", "timestamp")}
    `,
    params,
  );

  // Preço médio por categoria (requer join com listings)
  const avgPriceByCategory = await query<{
    category: 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' ${dateFilter.replace("timestamp", "te.timestamp")}
    GROUP BY l.item_name
    LIMIT 10
    `,
    params,
  );

  // Taxa de conversão
  const views = await query<{ count: string }>(
    `
    SELECT COUNT(*)::TEXT as count
    FROM telemetry_events
    WHERE event_type = 'ad_view' ${dateFilter}
    `,
    params,
  );

  const purchases = await query<{ count: string }>(
    `
    SELECT COUNT(*)::TEXT as count
    FROM telemetry_events
    WHERE event_type = 'purchase' ${dateFilter}
    `,
    params,
  );

  const viewsCount = parseInt(views.rows[0]?.count || "0", 10);
  const purchasesCount = parseInt(purchases.rows[0]?.count || "0", 10);
  const conversionRate =
    viewsCount > 0 ? (purchasesCount / viewsCount) * 100 : 0;

  // Itens mais procurados
  const mostSearched = await query<{
    item_name: string;
    search_count: string;
  }>(
    `
    SELECT 
      metadata->>'query' as item_name,
      COUNT(*)::TEXT 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
    `,
    params,
  );

  // Itens mais vendidos
  const mostSold = await query<{
    item_id: number;
    item_name: string;
    sales_count: string;
  }>(
    `
    SELECT 
      l.item_id,
      l.item_name,
      COUNT(*)::TEXT as sales_count
    FROM telemetry_events te
    JOIN listings l ON l.listing_id = te.ad_id
    WHERE te.event_type = 'purchase' ${dateFilter.replace("timestamp", "te.timestamp")}
    GROUP BY l.item_id, l.item_name
    ORDER BY COUNT(*) DESC
    LIMIT 10
    `,
    params,
  );

  // Horários de pico
  const peakHours = await query<{
    hour: number;
    activity_count: string;
  }>(
    `
    SELECT 
      EXTRACT(HOUR FROM timestamp)::INTEGER as hour,
      COUNT(*)::TEXT as activity_count
    FROM telemetry_events
    ${dateFilter}
    GROUP BY hour
    ORDER BY COUNT(*) DESC
    LIMIT 10
    `,
    params,
  );

  // Tempo médio para venda
  const avgTimeToSell = await query<{
    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: parseInt(transactions.rows[0]?.count || "0", 10),
    avg_price_by_category: avgPriceByCategory.rows.reduce(
      (acc, r) => {
        acc[r.category] = parseFloat(r.avg_price);
        return acc;
      },
      {} as Record<string, number>,
    ),
    conversion_rate: conversionRate,
    most_searched_items: mostSearched.rows.map((r) => ({
      item_id: 0, // Não temos item_id em searches, apenas query string
      item_name: r.item_name || "",
      search_count: parseInt(r.search_count, 10),
    })),
    most_sold_items: mostSold.rows.map((r) => ({
      item_id: r.item_id,
      item_name: r.item_name,
      sales_count: parseInt(r.sales_count, 10),
    })),
    peak_hours: peakHours.rows.map((r) => ({
      hour: r.hour,
      activity_count: parseInt(r.activity_count, 10),
    })),
    avg_time_to_sell: parseFloat(avgTimeToSell.rows[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> {
  // Agregar impressões (ad_view events)
  await query(
    `
    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 query(
    `
    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 (Click-Through Rate)
  await query(
    `
    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 query(
    `
    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 (views com duração < 3 segundos)
  await query(
    `
    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 query(
    `
    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 query(
    `
    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 query(
    `
    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 para anúncios vendidos
  await query(
    `
    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'
    `,
  );
}