138 lines
4.8 KiB
JavaScript
138 lines
4.8 KiB
JavaScript
import { usersPool } from "./pools.js";
|
|
|
|
// ── Schema ────────────────────────────────────────────────────────────────────
|
|
|
|
export async function initUsersSchema() {
|
|
await usersPool.query(`
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id SERIAL PRIMARY KEY,
|
|
username TEXT NOT NULL UNIQUE,
|
|
password_hash TEXT NOT NULL,
|
|
team TEXT NOT NULL CHECK (team IN ('blue', 'red')),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
`);
|
|
await initUserActionQuotaSchema();
|
|
}
|
|
|
|
export async function initUserActionQuotaSchema() {
|
|
await usersPool.query(`
|
|
CREATE TABLE IF NOT EXISTS user_action_quota (
|
|
user_id INTEGER PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
|
|
actions_remaining INTEGER NOT NULL DEFAULT 0,
|
|
quota_reset_at TIMESTAMPTZ NOT NULL DEFAULT '1970-01-01 00:00:00+00'
|
|
);
|
|
`);
|
|
}
|
|
|
|
// ── Helpers ───────────────────────────────────────────────────────────────────
|
|
|
|
/** Returns the next noon (12:00:00) UTC after the current moment. */
|
|
export function nextNoonUtc() {
|
|
const now = new Date();
|
|
const noon = new Date(Date.UTC(
|
|
now.getUTCFullYear(), now.getUTCMonth(), now.getUTCDate(), 12, 0, 0, 0
|
|
));
|
|
if (noon <= now) noon.setUTCDate(noon.getUTCDate() + 1);
|
|
return noon;
|
|
}
|
|
|
|
// ── Queries ───────────────────────────────────────────────────────────────────
|
|
|
|
export async function createUser(username, passwordHash, team) {
|
|
const { rows } = await usersPool.query(
|
|
`INSERT INTO users (username, password_hash, team)
|
|
VALUES ($1, $2, $3)
|
|
RETURNING id, username, team`,
|
|
[username, passwordHash, team]
|
|
);
|
|
return rows[0];
|
|
}
|
|
|
|
export async function getUserByUsername(username) {
|
|
const { rows } = await usersPool.query(
|
|
`SELECT id, username, team, password_hash FROM users WHERE username = $1`,
|
|
[username]
|
|
);
|
|
return rows[0] ?? null;
|
|
}
|
|
|
|
export async function getUserById(id) {
|
|
const { rows } = await usersPool.query(
|
|
`SELECT id, username, team FROM users WHERE id = $1`,
|
|
[id]
|
|
);
|
|
return rows[0] ?? null;
|
|
}
|
|
|
|
export async function getTeamPlayerCounts() {
|
|
const { rows } = await usersPool.query(
|
|
`SELECT team, COUNT(*)::int AS count FROM users GROUP BY team`
|
|
);
|
|
const result = { blue: 0, red: 0 };
|
|
for (const row of rows) result[row.team] = row.count;
|
|
return result;
|
|
}
|
|
|
|
/** Returns username and team for each of the given user IDs. */
|
|
export async function getUsersByIds(ids) {
|
|
if (!ids.length) return [];
|
|
const { rows } = await usersPool.query(
|
|
`SELECT id, username, team FROM users WHERE id = ANY($1::int[])`,
|
|
[ids]
|
|
);
|
|
return rows;
|
|
}
|
|
|
|
// ── User action quota ─────────────────────────────────────────────────────────
|
|
|
|
/** Returns the current quota row for a user, or null if it doesn't exist. */
|
|
export async function getUserActionsRow(userId) {
|
|
const { rows } = await usersPool.query(
|
|
`SELECT actions_remaining, quota_reset_at FROM user_action_quota WHERE user_id = $1`,
|
|
[userId]
|
|
);
|
|
return rows[0] ?? null;
|
|
}
|
|
|
|
/** Insert or overwrite the quota row for a user. */
|
|
export async function resetUserActions(userId, actionsRemaining, quotaResetAt) {
|
|
await usersPool.query(
|
|
`INSERT INTO user_action_quota (user_id, actions_remaining, quota_reset_at)
|
|
VALUES ($1, $2, $3)
|
|
ON CONFLICT (user_id) DO UPDATE
|
|
SET actions_remaining = $2,
|
|
quota_reset_at = $3`,
|
|
[userId, actionsRemaining, quotaResetAt]
|
|
);
|
|
}
|
|
|
|
/**
|
|
* Atomically decrements actions_remaining by 1 if > 0.
|
|
* Returns the updated row (with new actions_remaining), or null if already 0.
|
|
*/
|
|
export async function decrementUserActions(userId) {
|
|
const { rows } = await usersPool.query(
|
|
`UPDATE user_action_quota
|
|
SET actions_remaining = actions_remaining - 1
|
|
WHERE user_id = $1 AND actions_remaining > 0
|
|
RETURNING actions_remaining`,
|
|
[userId]
|
|
);
|
|
return rows[0] ?? null;
|
|
}
|
|
|
|
/**
|
|
* Resets ALL users' quota to a given value (used on world-seed wipeout).
|
|
* Users who have no row yet get one inserted.
|
|
*/
|
|
export async function resetAllUserActions(actionsRemaining, quotaResetAt) {
|
|
await usersPool.query(
|
|
`INSERT INTO user_action_quota (user_id, actions_remaining, quota_reset_at)
|
|
SELECT id, $1, $2 FROM users
|
|
ON CONFLICT (user_id) DO UPDATE
|
|
SET actions_remaining = $1,
|
|
quota_reset_at = $2`,
|
|
[actionsRemaining, quotaResetAt]
|
|
);
|
|
} |