games_app/backend/dist-test-faq/repositories/analyticsMaterializedQueries.js

407 lines
18 KiB
JavaScript

"use strict";
/**
* Dashboard chart queries backed by materialized views (daily/hourly rollups).
*/
Object.defineProperty(exports, "__esModule", { value: true });
exports.areDashboardMaterializedViewsReady = areDashboardMaterializedViewsReady;
exports.areChartMaterializedViewsReady = areChartMaterializedViewsReady;
exports.invalidateChartMaterializedViewsCache = invalidateChartMaterializedViewsCache;
exports.mvDailyDepositAmounts = mvDailyDepositAmounts;
exports.mvDailyWithdrawalAmounts = mvDailyWithdrawalAmounts;
exports.mvDailyBonusAmounts = mvDailyBonusAmounts;
exports.mvDailyAdjustmentAmounts = mvDailyAdjustmentAmounts;
exports.mvDailyNetPnl = mvDailyNetPnl;
exports.mvDailyPredictionCounts = mvDailyPredictionCounts;
exports.mvShortVsLong = mvShortVsLong;
exports.mvBetHours = mvBetHours;
exports.mvTransactionKpis = mvTransactionKpis;
exports.mvBonusKpis = mvBonusKpis;
exports.mvAdjustmentKpis = mvAdjustmentKpis;
exports.mvGameHousePnl = mvGameHousePnl;
exports.mvBetSegmentStats = mvBetSegmentStats;
exports.mvActiveUsersInPeriod = mvActiveUsersInPeriod;
exports.mvGeographyStats = mvGeographyStats;
exports.mvDemographics = mvDemographics;
exports.mvTransactionSpike = mvTransactionSpike;
exports.mvDailyBonusAmountsWithReferrals = mvDailyBonusAmountsWithReferrals;
const connection_1 = require("../db/connection");
const REQUIRED_MATVIEWS = [
'analytics_daily_transactions',
'analytics_daily_bets',
'analytics_daily_adjustments',
'analytics_hourly_bets',
'analytics_daily_tx_by_user',
'analytics_daily_bets_by_user',
'analytics_daily_tx_by_country',
'analytics_daily_adjustments_by_user',
'analytics_daily_referrals_by_user',
'analytics_daily_country_user_logins',
'analytics_daily_active_users',
];
function housePnlAccountFilter(scope, column = 'stake_account_kind') {
return scope === 'real'
? `AND ${column} IN ('main', 'bonus')`
: `AND ${column} = 'test'`;
}
let mvReady = null;
function num(v) {
if (v == null)
return 0;
return typeof v === 'number' ? v : parseInt(String(v), 10) || 0;
}
function toDailyPoints(rows) {
return rows.map((r) => ({ date: r.day, value: num(r.value) }));
}
/** Cached check — dashboard + chart MVs exist in public schema. */
async function areDashboardMaterializedViewsReady() {
if (mvReady !== null)
return mvReady;
try {
const rows = await (0, connection_1.query)(`SELECT COUNT(*)::text AS n
FROM pg_matviews
WHERE schemaname = 'public'
AND matviewname = ANY($1::text[])`, [REQUIRED_MATVIEWS]);
mvReady = num(rows[0]?.n) === REQUIRED_MATVIEWS.length;
}
catch {
mvReady = false;
}
return mvReady;
}
async function areChartMaterializedViewsReady() {
return areDashboardMaterializedViewsReady();
}
function invalidateChartMaterializedViewsCache() {
mvReady = null;
}
function dayRangeParams(range) {
return [range.from, range.to];
}
async function mvDailyDepositAmounts(range) {
const rows = await (0, connection_1.query)(`SELECT to_char(day, 'YYYY-MM-DD') AS day, COALESCE(SUM(amount_raw), 0)::text AS value
FROM analytics_daily_transactions
WHERE type = 'deposit' AND status = 'completed'
AND day >= ($1::timestamptz AT TIME ZONE 'UTC')::date
AND day <= ($2::timestamptz AT TIME ZONE 'UTC')::date
GROUP BY day ORDER BY day`, dayRangeParams(range));
return toDailyPoints(rows);
}
async function mvDailyWithdrawalAmounts(range) {
const rows = await (0, connection_1.query)(`SELECT to_char(day, 'YYYY-MM-DD') AS day, COALESCE(SUM(amount_raw), 0)::text AS value
FROM analytics_daily_transactions
WHERE type = 'withdrawal' AND status = 'completed'
AND day >= ($1::timestamptz AT TIME ZONE 'UTC')::date
AND day <= ($2::timestamptz AT TIME ZONE 'UTC')::date
GROUP BY day ORDER BY day`, dayRangeParams(range));
return toDailyPoints(rows);
}
async function mvDailyBonusAmounts(range) {
const rows = await (0, connection_1.query)(`SELECT to_char(day, 'YYYY-MM-DD') AS day, COALESCE(SUM(amount_raw), 0)::text AS value
FROM analytics_daily_adjustments
WHERE adjustment_type = 'bonus'
AND day >= ($1::timestamptz AT TIME ZONE 'UTC')::date
AND day <= ($2::timestamptz AT TIME ZONE 'UTC')::date
GROUP BY day ORDER BY day`, dayRangeParams(range));
return toDailyPoints(rows);
}
async function mvDailyAdjustmentAmounts(range) {
const rows = await (0, connection_1.query)(`SELECT to_char(day, 'YYYY-MM-DD') AS day, COALESCE(SUM(amount_raw), 0)::text AS value
FROM analytics_daily_adjustments
WHERE adjustment_type = 'admin_adjustment'
AND day >= ($1::timestamptz AT TIME ZONE 'UTC')::date
AND day <= ($2::timestamptz AT TIME ZONE 'UTC')::date
GROUP BY day ORDER BY day`, dayRangeParams(range));
return toDailyPoints(rows);
}
async function mvDailyNetPnl(range, scope = 'real') {
const rows = await (0, connection_1.query)(`SELECT to_char(day, 'YYYY-MM-DD') AS day,
(COALESCE(SUM(stake_raw), 0) - COALESCE(SUM(payout_raw), 0))::text AS value
FROM analytics_daily_bets
WHERE status IN ('won', 'lost', 'refunded')
${housePnlAccountFilter(scope)}
AND day >= ($1::timestamptz AT TIME ZONE 'UTC')::date
AND day <= ($2::timestamptz AT TIME ZONE 'UTC')::date
GROUP BY day ORDER BY day`, dayRangeParams(range));
return toDailyPoints(rows);
}
async function mvDailyPredictionCounts(range, isAuto) {
const rows = await (0, connection_1.query)(`SELECT to_char(day, 'YYYY-MM-DD') AS day, COALESCE(SUM(bet_count), 0)::text AS value
FROM analytics_daily_bets
WHERE is_auto = $3
AND day >= ($1::timestamptz AT TIME ZONE 'UTC')::date
AND day <= ($2::timestamptz AT TIME ZONE 'UTC')::date
GROUP BY day ORDER BY day`, [range.from, range.to, isAuto]);
return toDailyPoints(rows);
}
async function mvShortVsLong(range) {
const rows = await (0, connection_1.query)(`SELECT direction,
COALESCE(SUM(bet_count), 0)::text AS count,
COALESCE(SUM(stake_raw), 0)::text AS stake
FROM analytics_daily_bets
WHERE day >= ($1::timestamptz AT TIME ZONE 'UTC')::date
AND day <= ($2::timestamptz AT TIME ZONE 'UTC')::date
GROUP BY direction`, dayRangeParams(range));
return rows.map((r) => ({
direction: r.direction,
count: num(r.count),
stakeRaw: num(r.stake),
}));
}
async function mvBetHours(range) {
const rows = await (0, connection_1.query)(`SELECT EXTRACT(HOUR FROM hour_ts AT TIME ZONE 'UTC')::int::text AS hour,
COALESCE(SUM(bet_count), 0)::text AS count
FROM analytics_hourly_bets
WHERE hour_ts >= date_trunc('hour', $1::timestamptz)
AND hour_ts <= date_trunc('hour', $2::timestamptz)
GROUP BY 1 ORDER BY 1`, dayRangeParams(range));
return rows.map((r) => ({ hour: num(r.hour), count: num(r.count) }));
}
function dayFilterSql(alias = 'day') {
return `${alias} >= ($1::timestamptz AT TIME ZONE 'UTC')::date AND ${alias} <= ($2::timestamptz AT TIME ZONE 'UTC')::date`;
}
async function mvTransactionKpis(range, type) {
const rows = await (0, connection_1.query)(`SELECT
COALESCE(SUM(t.amount_raw), 0)::text AS total_amount,
COALESCE(SUM(t.tx_count), 0)::text AS total_count,
(SELECT COUNT(DISTINCT u.user_id)::text FROM analytics_daily_tx_by_user u
WHERE u.type = $3 AND u.status = 'completed' AND ${dayFilterSql('u.day')}) AS unique_users,
COALESCE(MAX(t.max_amount_raw), 0)::text AS max_amount
FROM analytics_daily_transactions t
WHERE t.type = $3 AND t.status = 'completed' AND ${dayFilterSql('t.day')}`, [range.from, range.to, type]);
const r = rows[0];
const totalAmount = num(r?.total_amount);
const uniqueUsers = num(r?.unique_users);
return {
totalAmountRaw: totalAmount,
totalCount: num(r?.total_count),
uniqueUsers,
averagePerUserRaw: uniqueUsers > 0 ? Math.round(totalAmount / uniqueUsers) : 0,
maxAmountRaw: num(r?.max_amount),
};
}
async function mvBonusKpis(range) {
const rows = await (0, connection_1.query)(`SELECT
(SELECT COALESCE(SUM(amount_raw), 0)::text FROM analytics_daily_adjustments
WHERE adjustment_type = 'bonus' AND ${dayFilterSql('day')}) AS bonus_total,
(SELECT COUNT(DISTINCT user_id)::text FROM analytics_daily_adjustments_by_user
WHERE adjustment_type = 'bonus' AND ${dayFilterSql('day')}) AS bonus_users,
(SELECT COALESCE(SUM(amount_raw), 0)::text FROM analytics_daily_referrals_by_user
WHERE ${dayFilterSql('day')}) AS referral_total,
(SELECT COALESCE(SUM(amount_raw), 0)::text FROM analytics_daily_adjustments
WHERE adjustment_type = 'bonus' AND ${dayFilterSql('day')}) AS manual_bonus`, dayRangeParams(range));
const r = rows[0];
const bonusTotal = num(r?.bonus_total) + num(r?.referral_total);
const bonusUsers = num(r?.bonus_users);
return {
totalAmountRaw: bonusTotal,
uniqueUsers: bonusUsers,
averagePerUserRaw: bonusUsers > 0 ? Math.round(bonusTotal / bonusUsers) : 0,
byType: {
manualBonusRaw: num(r?.manual_bonus),
referralCommissionRaw: num(r?.referral_total),
},
};
}
async function mvAdjustmentKpis(range) {
const rows = await (0, connection_1.query)(`SELECT
COALESCE(SUM(a.amount_raw), 0)::text AS total_amount,
(SELECT COUNT(DISTINCT user_id)::text FROM analytics_daily_adjustments_by_user
WHERE adjustment_type = 'admin_adjustment' AND ${dayFilterSql('day')}) AS unique_users,
COALESCE(SUM(a.adj_count), 0)::text AS total_count
FROM analytics_daily_adjustments a
WHERE a.adjustment_type = 'admin_adjustment' AND ${dayFilterSql('a.day')}`, dayRangeParams(range));
const r = rows[0];
const totalAmount = num(r?.total_amount);
const uniqueUsers = num(r?.unique_users);
const totalCount = num(r?.total_count);
return {
uniqueUsers,
totalAmountRaw: totalAmount,
averageRaw: totalCount > 0 ? Math.round(totalAmount / totalCount) : 0,
};
}
async function mvGameHousePnl(range, scope = 'real') {
const rows = await (0, connection_1.query)(`SELECT
COALESCE(SUM(stake_raw), 0)::text AS stake,
COALESCE(SUM(payout_raw), 0)::text AS payout
FROM analytics_daily_bets
WHERE status IN ('won', 'lost', 'refunded') ${housePnlAccountFilter(scope)} AND ${dayFilterSql('day')}`, dayRangeParams(range));
const stake = num(rows[0]?.stake);
const payout = num(rows[0]?.payout);
return { houseNetPnlRaw: stake - payout };
}
async function mvBetSegmentStats(range, filters) {
const values = [range.from, range.to];
const clauses = [dayFilterSql('day')];
if (filters.isAuto !== undefined) {
values.push(filters.isAuto);
clauses.push(`is_auto = $${values.length}`);
}
if (filters.stakeAccountKinds?.length) {
values.push(filters.stakeAccountKinds);
clauses.push(`stake_account_kind = ANY($${values.length}::text[])`);
}
const where = clauses.join(' AND ');
const rows = await (0, connection_1.query)(`SELECT
COUNT(DISTINCT user_id)::text AS users,
COALESCE(SUM(bet_count), 0)::text AS predictions,
COALESCE(SUM(stake_raw), 0)::text AS stake,
COALESCE(SUM(payout_raw), 0)::text AS payout,
COALESCE(SUM(won_count), 0)::text AS won,
COALESCE(SUM(settled_count), 0)::text AS settled
FROM analytics_daily_bets_by_user
WHERE ${where}`, values);
const r = rows[0];
const stake = num(r?.stake);
const payout = num(r?.payout);
const settled = num(r?.settled);
const won = num(r?.won);
return {
users: num(r?.users),
predictions: num(r?.predictions),
stakeRaw: stake,
netPnlRaw: payout - stake,
winRate: settled > 0 ? Math.round((won / settled) * 10000) / 100 : 0,
};
}
async function mvActiveUsersInPeriod(range) {
const rows = await (0, connection_1.query)(`SELECT COUNT(DISTINCT user_id)::text AS c
FROM analytics_daily_active_users
WHERE ${dayFilterSql('day')}`, dayRangeParams(range));
return num(rows[0]?.c);
}
async function mvGeographyStats(range) {
const rows = await (0, connection_1.query)(`WITH dep AS (
SELECT country, SUM(amount_raw)::bigint AS total
FROM analytics_daily_tx_by_country
WHERE type = 'deposit' AND ${dayFilterSql('day')}
GROUP BY country
),
wdr AS (
SELECT country, SUM(amount_raw)::bigint AS total
FROM analytics_daily_tx_by_country
WHERE type = 'withdrawal' AND ${dayFilterSql('day')}
GROUP BY country
),
act AS (
SELECT country, COUNT(DISTINCT user_id)::int AS active_users
FROM analytics_daily_country_user_logins
WHERE ${dayFilterSql('day')}
GROUP BY country
)
SELECT
COALESCE(u.country_of_residence, 'XX') AS country,
COUNT(DISTINCT u.id)::text AS users,
COALESCE(MAX(dep.total), 0)::text AS deposits_raw,
COALESCE(MAX(wdr.total), 0)::text AS withdrawals_raw,
COALESCE(SUM(s.net_pnl_raw), 0)::text AS net_pnl_raw,
COALESCE(MAX(act.active_users), 0)::text AS active_users
FROM users u
LEFT JOIN user_admin_stats s ON s.user_id = u.id
LEFT JOIN dep ON dep.country = COALESCE(u.country_of_residence, 'XX')
LEFT JOIN wdr ON wdr.country = COALESCE(u.country_of_residence, 'XX')
LEFT JOIN act ON act.country = COALESCE(u.country_of_residence, 'XX')
GROUP BY COALESCE(u.country_of_residence, 'XX')
ORDER BY COUNT(DISTINCT u.id) DESC
LIMIT 50`, dayRangeParams(range));
return rows.map((r) => ({
country: r.country,
users: num(r.users),
depositsRaw: num(r.deposits_raw),
withdrawalsRaw: num(r.withdrawals_raw),
netPnlRaw: num(r.net_pnl_raw),
activeUsers: num(r.active_users),
}));
}
async function mvDemographics(range) {
const [byGender, byAge] = await Promise.all([
(0, connection_1.query)(`SELECT
COALESCE(u.gender, 'unknown') AS gender,
COUNT(DISTINCT u.id)::text AS users,
COALESCE(SUM(s.total_stake_raw), 0)::text AS stake_raw,
COALESCE(SUM(s.net_pnl_raw), 0)::text AS net_pnl_raw,
COALESCE(SUM(dep.amount_raw), 0)::text AS deposits_raw
FROM users u
LEFT JOIN user_admin_stats s ON s.user_id = u.id
LEFT JOIN (
SELECT user_id, SUM(amount_raw)::bigint AS amount_raw
FROM analytics_daily_tx_by_user
WHERE type = 'deposit' AND status = 'completed' AND ${dayFilterSql('day')}
GROUP BY user_id
) dep ON dep.user_id = u.id
GROUP BY COALESCE(u.gender, 'unknown')`, dayRangeParams(range)),
(0, connection_1.query)(`SELECT
CASE
WHEN u.date_of_birth IS NULL THEN 'unknown'
WHEN EXTRACT(YEAR FROM age(u.date_of_birth)) < 18 THEN 'under_18'
WHEN EXTRACT(YEAR FROM age(u.date_of_birth)) BETWEEN 18 AND 24 THEN '18_24'
WHEN EXTRACT(YEAR FROM age(u.date_of_birth)) BETWEEN 25 AND 34 THEN '25_34'
WHEN EXTRACT(YEAR FROM age(u.date_of_birth)) BETWEEN 35 AND 44 THEN '35_44'
WHEN EXTRACT(YEAR FROM age(u.date_of_birth)) BETWEEN 45 AND 54 THEN '45_54'
ELSE '55_plus'
END AS age_bucket,
COUNT(DISTINCT u.id)::text AS users,
COALESCE(SUM(s.total_stake_raw), 0)::text AS stake_raw,
COALESCE(SUM(s.net_pnl_raw), 0)::text AS net_pnl_raw,
COALESCE(SUM(dep.amount_raw), 0)::text AS deposits_raw
FROM users u
LEFT JOIN user_admin_stats s ON s.user_id = u.id
LEFT JOIN (
SELECT user_id, SUM(amount_raw)::bigint AS amount_raw
FROM analytics_daily_tx_by_user
WHERE type = 'deposit' AND status = 'completed' AND ${dayFilterSql('day')}
GROUP BY user_id
) dep ON dep.user_id = u.id
GROUP BY 1
ORDER BY 1`, dayRangeParams(range)),
]);
return {
byGender: byGender.map((r) => ({
gender: r.gender,
users: num(r.users),
stakeRaw: num(r.stake_raw),
netPnlRaw: num(r.net_pnl_raw),
depositsRaw: num(r.deposits_raw),
})),
byAge: byAge.map((r) => ({
ageBucket: r.age_bucket,
users: num(r.users),
stakeRaw: num(r.stake_raw),
netPnlRaw: num(r.net_pnl_raw),
depositsRaw: num(r.deposits_raw),
})),
};
}
async function mvTransactionSpike(txType, range) {
const durationMs = range.to.getTime() - range.from.getTime();
const prevFrom = new Date(range.from.getTime() - durationMs);
const rows = await (0, connection_1.query)(`SELECT 'current' AS period, COALESCE(SUM(amount_raw), 0)::text AS v
FROM analytics_daily_transactions
WHERE type = $5 AND status = 'completed'
AND day >= ($1::timestamptz AT TIME ZONE 'UTC')::date
AND day <= ($2::timestamptz AT TIME ZONE 'UTC')::date
UNION ALL
SELECT 'previous', COALESCE(SUM(amount_raw), 0)::text
FROM analytics_daily_transactions
WHERE type = $5 AND status = 'completed'
AND day >= ($3::timestamptz AT TIME ZONE 'UTC')::date
AND day < ($4::timestamptz AT TIME ZONE 'UTC')::date`, [range.from, range.to, prevFrom, range.from, txType]);
return {
currentRaw: num(rows.find((r) => r.period === 'current')?.v),
previousRaw: num(rows.find((r) => r.period === 'previous')?.v),
};
}
async function mvDailyBonusAmountsWithReferrals(range) {
const rows = await (0, connection_1.query)(`WITH days AS (
SELECT to_char(day, 'YYYY-MM-DD') AS day, amount_raw FROM analytics_daily_adjustments
WHERE adjustment_type = 'bonus' AND ${dayFilterSql('day')}
UNION ALL
SELECT to_char(day, 'YYYY-MM-DD'), amount_raw FROM analytics_daily_referrals_by_user
WHERE ${dayFilterSql('day')}
)
SELECT day, COALESCE(SUM(amount_raw), 0)::text AS value
FROM days
GROUP BY day ORDER BY day`, dayRangeParams(range));
return toDailyPoints(rows);
}
//# sourceMappingURL=analyticsMaterializedQueries.js.map