root
95 lines
3.6 KiB
Plaintext
95 lines
3.6 KiB
Plaintext
import { pool } from "../db.js";
|
|
|
|
export async function findUserByEmail(email) {
|
|
const { rows } = await pool.query(`
|
|
SELECT id, email, password_hash, display_name, role, status, balance, last_login_at, created_at, updated_at
|
|
FROM uno_bff.users WHERE lower(email) = lower($1) LIMIT 1
|
|
`, [email]);
|
|
return rows[0] || null;
|
|
}
|
|
|
|
export async function findUserByEmailAndPassword(email, password) {
|
|
const { rows } = await pool.query(`
|
|
SELECT id, email, password_hash, display_name, role, status, balance, last_login_at, created_at, updated_at
|
|
FROM uno_bff.users
|
|
WHERE lower(email) = lower($1) AND status = 'active'
|
|
AND password_hash::text = sites.crypt($2::text, password_hash::text)
|
|
LIMIT 1
|
|
`, [email, password]);
|
|
return rows[0] || null;
|
|
}
|
|
|
|
export async function findUserById(userId) {
|
|
const { rows } = await pool.query(`
|
|
SELECT id, email, display_name, role, status, balance, created_at
|
|
FROM uno_bff.users WHERE id = $1 AND status = 'active'
|
|
`, [userId]);
|
|
return rows[0] || null;
|
|
}
|
|
|
|
export async function updateUserBalance(userId, delta) {
|
|
const { rows } = await pool.query(`
|
|
UPDATE uno_bff.users
|
|
SET balance = balance + $2, updated_at = NOW()
|
|
WHERE id = $1
|
|
RETURNING balance
|
|
`, [userId, delta]);
|
|
return rows[0]?.balance ?? null;
|
|
}
|
|
|
|
export async function touchLastLogin(userId) {
|
|
await pool.query(`UPDATE uno_bff.users SET last_login_at = now(), updated_at = now() WHERE id = $1`, [userId]);
|
|
}
|
|
|
|
export async function createUser({ email, password, displayName }) {
|
|
const { rows } = await pool.query(`
|
|
INSERT INTO uno_bff.users (email, password_hash, display_name, role, status)
|
|
VALUES (lower($1), sites.crypt($2::text, sites.gen_salt('bf', 10)), $3, 'user', 'active')
|
|
RETURNING id, email, display_name, role, status, balance, created_at
|
|
`, [email, password, displayName || null]);
|
|
return rows[0];
|
|
}
|
|
|
|
export async function userExistsByEmail(email) {
|
|
const { rows } = await pool.query(`SELECT 1 FROM uno_bff.users WHERE lower(email) = lower($1) LIMIT 1`, [email]);
|
|
return rows.length > 0;
|
|
}
|
|
|
|
export async function findUserByTelegramId(telegramId) {
|
|
const { rows } = await pool.query(`SELECT * FROM uno_bff.users WHERE telegram_id = $1 AND status = 'active' LIMIT 1`, [telegramId]);
|
|
return rows[0] || null;
|
|
}
|
|
|
|
export async function linkTelegramToUser(userId, telegramId) {
|
|
const { rows } = await pool.query(`UPDATE uno_bff.users SET telegram_id = $2 WHERE id = $1 RETURNING *`, [userId, telegramId]);
|
|
return rows[0] || null;
|
|
}
|
|
|
|
export async function createUserFromTelegram({ telegramId, displayName, email }) {
|
|
const { rows } = await pool.query(
|
|
`INSERT INTO uno_bff.users (email, password_hash, display_name, telegram_id, role, status)
|
|
VALUES (lower($1), '!tg!' || sites.gen_salt('bf', 10), $2, $3, 'user', 'active')
|
|
RETURNING id, email, display_name, role, status, balance, telegram_id, created_at`,
|
|
[email, displayName || null, telegramId]
|
|
);
|
|
return rows[0];
|
|
}
|
|
|
|
export async function countTelegramSignupsByIpLast24h(ipAddress) {
|
|
if (!ipAddress) return 0;
|
|
const { rows } = await pool.query(
|
|
`SELECT COUNT(*)::int AS c FROM uno_bff.signup_attempts
|
|
WHERE ip_address = $1 AND provider = 'telegram' AND created_at > now() - interval '24 hours'`,
|
|
[ipAddress]
|
|
);
|
|
return rows[0]?.c ?? 0;
|
|
}
|
|
|
|
export async function recordSignupAttempt({ ipAddress, provider, telegramId, userAgent }) {
|
|
await pool.query(
|
|
`INSERT INTO uno_bff.signup_attempts (ip_address, provider, telegram_id, user_agent)
|
|
VALUES ($1, $2, $3, $4)`,
|
|
[ipAddress || null, provider, telegramId || null, userAgent || null]
|
|
);
|
|
}
|