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

142 lines
4.9 KiB
JavaScript

"use strict";
Object.defineProperty(exports, "__esModule", { value: true });
exports.refreshUserStats = refreshUserStats;
exports.backfillStatsBatch = backfillStatsBatch;
exports.refreshUserStatsForIds = refreshUserStatsForIds;
exports.countUsers = countUsers;
exports.countUsersMissingStats = countUsersMissingStats;
const connection_1 = require("../db/connection");
const userGameBetStatsRepository_1 = require("./userGameBetStatsRepository");
/** Shared SELECT body that aggregates live data for one or many users. */
const STATS_SELECT_BODY = `
SELECT
u.id AS user_id,
COALESCE(lm.available_balance, 0)::bigint AS main_balance_raw,
COALESCE(lb.available_balance, 0)::bigint AS bonus_balance_raw,
COALESCE(bonus_adj.total, 0)::bigint AS total_bonus_received_raw,
COALESCE(bet_stats.prediction_count, 0)::int AS prediction_count,
COALESCE(bet_stats.total_stake_raw, 0)::bigint AS total_stake_raw,
COALESCE(bet_stats.total_payout_raw, 0)::bigint AS total_payout_raw,
(COALESCE(bet_stats.total_payout_raw, 0) - COALESCE(bet_stats.total_stake_raw, 0))::bigint AS net_pnl_raw,
login.last_login_at,
bet_stats.last_bet_at,
GREATEST(
COALESCE(login.last_login_at, u.created_at),
COALESCE(bet_stats.last_bet_at, u.created_at),
u.updated_at
) AS last_activity_at
FROM users u
CROSS JOIN (SELECT id FROM wallet_types WHERE code = 'USDT_TRC20' LIMIT 1) wt
LEFT JOIN ledger_accounts lm
ON lm.user_id = u.id AND lm.wallet_type_id = wt.id AND lm.account_kind = 'main'
LEFT JOIN ledger_accounts lb
ON lb.user_id = u.id AND lb.wallet_type_id = wt.id AND lb.account_kind = 'bonus'
LEFT JOIN LATERAL (
SELECT COALESCE(SUM(amount), 0)::bigint AS total
FROM ledger_adjustments
WHERE user_id = u.id AND adjustment_type = 'bonus'
) bonus_adj ON TRUE
LEFT JOIN LATERAL (
${userGameBetStatsRepository_1.USER_BET_STATS_LATERAL_BODY}
) bet_stats ON TRUE
LEFT JOIN LATERAL (
SELECT MAX(created_at) AS last_login_at
FROM audit_logs
WHERE user_id = u.id AND action = 'login'
) login ON TRUE`;
const UPSERT_FROM_STATS = `
INSERT INTO user_admin_stats (
user_id,
main_balance_raw,
bonus_balance_raw,
total_bonus_received_raw,
prediction_count,
total_stake_raw,
total_payout_raw,
net_pnl_raw,
last_login_at,
last_bet_at,
last_activity_at,
updated_at
)
SELECT
user_id,
main_balance_raw,
bonus_balance_raw,
total_bonus_received_raw,
prediction_count,
total_stake_raw,
total_payout_raw,
net_pnl_raw,
last_login_at,
last_bet_at,
last_activity_at,
NOW()
FROM stats_src
ON CONFLICT (user_id) DO UPDATE SET
main_balance_raw = EXCLUDED.main_balance_raw,
bonus_balance_raw = EXCLUDED.bonus_balance_raw,
total_bonus_received_raw = EXCLUDED.total_bonus_received_raw,
prediction_count = EXCLUDED.prediction_count,
total_stake_raw = EXCLUDED.total_stake_raw,
total_payout_raw = EXCLUDED.total_payout_raw,
net_pnl_raw = EXCLUDED.net_pnl_raw,
last_login_at = EXCLUDED.last_login_at,
last_bet_at = EXCLUDED.last_bet_at,
last_activity_at = EXCLUDED.last_activity_at,
updated_at = NOW()`;
/**
* Recomputes and upserts admin stats for a single user.
*/
async function refreshUserStats(userId) {
await (0, connection_1.query)(`WITH stats_src AS (
${STATS_SELECT_BODY}
WHERE u.id = $1
)
${UPSERT_FROM_STATS}`, [userId]);
}
/**
* Backfills or refreshes admin stats for a batch of users (ordered by created_at, id).
* @returns Number of rows upserted.
*/
async function backfillStatsBatch(limit, offset) {
const rows = await (0, connection_1.query)(`WITH batch AS (
SELECT id FROM users ORDER BY created_at, id LIMIT $1 OFFSET $2
),
stats_src AS (
${STATS_SELECT_BODY}
WHERE u.id IN (SELECT id FROM batch)
)
${UPSERT_FROM_STATS}
RETURNING user_id`, [limit, offset]);
return rows.length;
}
/** Recomputes stats for an explicit set of users (batch upsert). */
async function refreshUserStatsForIds(userIds) {
if (userIds.length === 0)
return 0;
const rows = await (0, connection_1.query)(`WITH batch AS (
SELECT unnest($1::uuid[]) AS id
),
stats_src AS (
${STATS_SELECT_BODY}
WHERE u.id IN (SELECT id FROM batch)
)
${UPSERT_FROM_STATS}
RETURNING user_id`, [userIds]);
return rows.length;
}
/** Total users (for backfill progress). */
async function countUsers() {
const rows = await (0, connection_1.query)('SELECT COUNT(*)::text AS count FROM users');
return parseInt(rows[0]?.count ?? '0', 10);
}
/** Users missing a stats row (needs initial backfill). */
async function countUsersMissingStats() {
const rows = await (0, connection_1.query)(`SELECT COUNT(*)::text AS count
FROM users u
LEFT JOIN user_admin_stats s ON s.user_id = u.id
WHERE s.user_id IS NULL`);
return parseInt(rows[0]?.count ?? '0', 10);
}
//# sourceMappingURL=userAdminStatsRepository.js.map