620 lines
16 KiB
TypeScript
620 lines
16 KiB
TypeScript
/**
|
|
* 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'
|
|
`;
|
|
}
|