518 lines
16 KiB
TypeScript
518 lines
16 KiB
TypeScript
/**
|
|
* Small fixture set for the dev database: players, teams, real ranked maps,
|
|
* a few finished matches, one open tournament, and one player waiting in the 1v1 queue.
|
|
*
|
|
* Refuses to run unless the database name is vash_dev.
|
|
* bun scripts/seed-dev.ts
|
|
*/
|
|
import { SQL } from 'bun';
|
|
|
|
const databaseUrl = process.env.DATABASE_URL || process.env.DB_URL;
|
|
if (!databaseUrl) throw new Error('DATABASE_URL or DB_URL is not set');
|
|
|
|
const sql = new SQL(databaseUrl, { max: 1 });
|
|
|
|
const PLAYERS = [
|
|
{
|
|
id: 'dev-player-01',
|
|
name: 'saltine',
|
|
country: 'US',
|
|
osuId: 9100001,
|
|
elo: 2100,
|
|
wins: 40,
|
|
losses: 12,
|
|
rank: 800
|
|
},
|
|
{
|
|
id: 'dev-player-02',
|
|
name: 'ricebowl',
|
|
country: 'KR',
|
|
osuId: 9100002,
|
|
elo: 1950,
|
|
wins: 32,
|
|
losses: 14,
|
|
rank: 2500
|
|
},
|
|
{
|
|
id: 'dev-player-03',
|
|
name: 'pebble',
|
|
country: 'NL',
|
|
osuId: 9100003,
|
|
elo: 1800,
|
|
wins: 28,
|
|
losses: 16,
|
|
rank: 6000
|
|
},
|
|
{
|
|
id: 'dev-player-04',
|
|
name: 'nightbus',
|
|
country: 'GB',
|
|
osuId: 9100004,
|
|
elo: 1700,
|
|
wins: 22,
|
|
losses: 18,
|
|
rank: 12000
|
|
},
|
|
{
|
|
id: 'dev-player-05',
|
|
name: 'teacup',
|
|
country: 'DE',
|
|
osuId: 9100005,
|
|
elo: 1600,
|
|
wins: 18,
|
|
losses: 15,
|
|
rank: 20000
|
|
},
|
|
{
|
|
id: 'dev-player-06',
|
|
name: 'lantern',
|
|
country: 'JP',
|
|
osuId: 9100006,
|
|
elo: 1500,
|
|
wins: 16,
|
|
losses: 16,
|
|
rank: 35000
|
|
},
|
|
{
|
|
id: 'dev-player-07',
|
|
name: 'moss',
|
|
country: 'CA',
|
|
osuId: 9100007,
|
|
elo: 1400,
|
|
wins: 14,
|
|
losses: 17,
|
|
rank: 50000
|
|
},
|
|
{
|
|
id: 'dev-player-08',
|
|
name: 'keypad',
|
|
country: 'FR',
|
|
osuId: 9100008,
|
|
elo: 1300,
|
|
wins: 11,
|
|
losses: 14,
|
|
rank: 80000
|
|
},
|
|
{
|
|
id: 'dev-player-09',
|
|
name: 'river',
|
|
country: 'AU',
|
|
osuId: 9100009,
|
|
elo: 1200,
|
|
wins: 9,
|
|
losses: 12,
|
|
rank: 110000
|
|
},
|
|
{
|
|
id: 'dev-player-10',
|
|
name: 'comet',
|
|
country: 'PL',
|
|
osuId: 9100010,
|
|
elo: 1100,
|
|
wins: 7,
|
|
losses: 11,
|
|
rank: 150000
|
|
},
|
|
{
|
|
id: 'dev-player-11',
|
|
name: 'soda',
|
|
country: 'BR',
|
|
osuId: 9100011,
|
|
elo: 1050,
|
|
wins: 5,
|
|
losses: 9,
|
|
rank: 200000
|
|
},
|
|
{
|
|
id: 'dev-player-12',
|
|
name: 'wool',
|
|
country: 'SE',
|
|
osuId: 9100012,
|
|
elo: 1000,
|
|
wins: 3,
|
|
losses: 6,
|
|
rank: 280000
|
|
}
|
|
] as const;
|
|
|
|
type Beatmap = {
|
|
id: string;
|
|
title: string;
|
|
artist: string;
|
|
version: string;
|
|
sr: number;
|
|
bpm: number;
|
|
length: number;
|
|
cover: string | null;
|
|
list: string | null;
|
|
};
|
|
|
|
const FALLBACK: Beatmap[] = [
|
|
['75', 'DISCO PRINCE', 'Kenji Ninuma', 'Normal', 2.4],
|
|
['163', 'Crazy Noisy Bizarre Town', 'The DU', 'Normal', 3.1],
|
|
['250', 'Sakura Kagetsu', 'Mutsuhiko Izumi', 'Normal', 3.6],
|
|
['306', 'Airman ga Taosenai', 'Team.NEKOKAN', 'Normal', 4.4],
|
|
['390', 'Kira Kira Days', 'MOSAIC.WAV', 'Normal', 4.2],
|
|
['455', 'FREEDOM DiVE', 'xi', 'FOUR DIMENSIONS', 6.2],
|
|
['500', 'Blue Zenith', 'xi', 'FOUR DIMENSIONS', 6.6],
|
|
['1001', 'Pretender', 'Official HIGE DANdism', 'Insane', 5.1],
|
|
['1002', 'Highscore', 'Panda Eyes', 'Extra', 5.4],
|
|
['1003', 'Everything will freeze', 'UNDEAD CORPORATION', 'Extra', 5.8],
|
|
['1004', 'Sidetracked Day', 'namirin', 'Insane', 4.8],
|
|
['1005', 'Hitorigoto', 'ClariS', 'Insane', 4.6]
|
|
].map(([id, title, artist, version, sr]) => ({
|
|
id,
|
|
title,
|
|
artist,
|
|
version,
|
|
sr: Number(sr),
|
|
bpm: 180,
|
|
length: 180,
|
|
cover: null,
|
|
list: null
|
|
}));
|
|
|
|
const DEV_LINKS = [
|
|
{ label: 'Discord', url: 'https://discord.gg/n3mZgWk' },
|
|
{ label: 'Main sheet', url: 'https://docs.google.com/spreadsheets/' },
|
|
{ label: 'Livestream', url: 'https://twitch.tv/' }
|
|
];
|
|
const DEV_PRIZES = [
|
|
{ place: 'Gold crown', prize: 'Profile banner, $130 (or 5 years of osu!supporter)' },
|
|
{ place: 'Silver crown', prize: 'Profile banner, $60' },
|
|
{ place: 'Bronze crown', prize: 'Profile banner, $30' }
|
|
];
|
|
|
|
const SLOT_PLAN = [
|
|
['NM', 1],
|
|
['NM', 2],
|
|
['NM', 3],
|
|
['NM', 4],
|
|
['HD', 1],
|
|
['HD', 2],
|
|
['HR', 1],
|
|
['HR', 2],
|
|
['DT', 1],
|
|
['DT', 2],
|
|
['FM', 1],
|
|
['TB', 1]
|
|
] as const;
|
|
|
|
async function main() {
|
|
const [{ name }] = await sql`SELECT current_database() AS name`;
|
|
// SEED_TEST_DATABASE lets a throwaway database be seeded too, for testing this script.
|
|
if (name !== 'vash_dev' && name !== process.env.SEED_TEST_DATABASE) {
|
|
throw new Error(`Refusing to seed ${name}. This script only runs against vash_dev.`);
|
|
}
|
|
|
|
await sql.begin(async (tx) => {
|
|
// Matches anyone played with the seeded players or pools go too; they would
|
|
// block deleting what they reference. Deleting a match cascades to its rows.
|
|
await tx`UPDATE tournament_match SET match_id = NULL WHERE match_id IN (SELECT id FROM match WHERE mappool_id IN (SELECT id FROM mappool WHERE created_by = 'dev-seed') OR id IN (SELECT match_id FROM match_participant WHERE team_id IN (SELECT id FROM team WHERE owner_id LIKE 'dev-player-%')))`;
|
|
await tx`DELETE FROM match WHERE mappool_id IN (SELECT id FROM mappool WHERE created_by = 'dev-seed') OR id IN (SELECT match_id FROM match_participant WHERE team_id IN (SELECT id FROM team WHERE owner_id LIKE 'dev-player-%'))`;
|
|
await tx`DELETE FROM match_game_score WHERE match_game_id IN (SELECT id FROM match_game WHERE match_id IN (SELECT id FROM match WHERE created_by = 'dev-seed'))`;
|
|
await tx`DELETE FROM match_game WHERE match_id IN (SELECT id FROM match WHERE created_by = 'dev-seed')`;
|
|
await tx`DELETE FROM match_participant_player WHERE participant_id IN (SELECT id FROM match_participant WHERE match_id IN (SELECT id FROM match WHERE created_by = 'dev-seed'))`;
|
|
await tx`DELETE FROM match_participant WHERE match_id IN (SELECT id FROM match WHERE created_by = 'dev-seed')`;
|
|
await tx`DELETE FROM match_event WHERE match_id IN (SELECT id FROM match WHERE created_by = 'dev-seed')`;
|
|
await tx`DELETE FROM match_invite WHERE created_by = 'dev-seed' OR created_by LIKE 'dev-player-%'`;
|
|
await tx`DELETE FROM match WHERE created_by = 'dev-seed'`;
|
|
await tx`DELETE FROM tournament_registration WHERE tournament_id IN (SELECT id FROM tournament WHERE created_by = 'dev-seed')`;
|
|
await tx`DELETE FROM tournament_staff WHERE tournament_id IN (SELECT id FROM tournament WHERE created_by = 'dev-seed')`;
|
|
await tx`DELETE FROM tournament_round WHERE tournament_id IN (SELECT id FROM tournament WHERE created_by = 'dev-seed')`;
|
|
await tx`DELETE FROM tournament WHERE created_by = 'dev-seed'`;
|
|
await tx`DELETE FROM mappool_slot WHERE mappool_id IN (SELECT id FROM mappool WHERE created_by = 'dev-seed')`;
|
|
await tx`DELETE FROM mappool WHERE created_by = 'dev-seed'`;
|
|
await tx`DELETE FROM match_queue WHERE user_id LIKE 'dev-player-%'`;
|
|
await tx`DELETE FROM notification WHERE user_id LIKE 'dev-player-%'`;
|
|
await tx`DELETE FROM player_rating WHERE user_id LIKE 'dev-player-%'`;
|
|
await tx`DELETE FROM team_member WHERE user_id LIKE 'dev-player-%' OR team_id IN (SELECT id FROM team WHERE owner_id LIKE 'dev-player-%')`;
|
|
await tx`DELETE FROM team WHERE owner_id LIKE 'dev-player-%'`;
|
|
await tx`DELETE FROM session WHERE user_id LIKE 'dev-player-%'`;
|
|
await tx`DELETE FROM account WHERE user_id LIKE 'dev-player-%'`;
|
|
await tx`DELETE FROM "user" WHERE id LIKE 'dev-player-%'`;
|
|
});
|
|
|
|
for (const player of PLAYERS) {
|
|
await sql`
|
|
INSERT INTO "user" (id, name, email, email_verified, role, country_code, osu_id, created_at, updated_at)
|
|
VALUES (
|
|
${player.id},
|
|
${player.name},
|
|
${`${player.osuId}@osu.local`},
|
|
true,
|
|
'player',
|
|
${player.country},
|
|
${player.osuId},
|
|
now(),
|
|
now()
|
|
)
|
|
`;
|
|
await sql`
|
|
INSERT INTO account (id, account_id, provider_id, user_id, created_at, updated_at)
|
|
VALUES (${`dev-account-${player.osuId}`}, ${String(player.osuId)}, 'osu', ${player.id}, now(), now())
|
|
`;
|
|
await sql`
|
|
INSERT INTO player_rating (user_id, elo, wins, losses, initial_elo, osu_rank_at_seed, provisional, updated_at)
|
|
VALUES (${player.id}, ${player.elo}, ${player.wins}, ${player.losses}, ${player.elo}, ${player.rank}, false, now())
|
|
`;
|
|
const [personal] = await sql`
|
|
INSERT INTO team (name, is_personal, owner_id)
|
|
VALUES (${player.name}, true, ${player.id})
|
|
RETURNING id
|
|
`;
|
|
await sql`
|
|
INSERT INTO team_member (team_id, user_id, role)
|
|
VALUES (${personal.id}, ${player.id}, 'owner')
|
|
`;
|
|
}
|
|
|
|
const teams = [
|
|
{
|
|
name: 'Night Shift',
|
|
members: ['dev-player-01', 'dev-player-02', 'dev-player-03', 'dev-player-04']
|
|
},
|
|
{ name: 'Late Entry', members: ['dev-player-05', 'dev-player-06'] }
|
|
];
|
|
for (const group of teams) {
|
|
const [team] = await sql`
|
|
INSERT INTO team (name, is_personal, owner_id)
|
|
VALUES (${group.name}, false, ${group.members[0]})
|
|
RETURNING id
|
|
`;
|
|
for (const [index, member] of group.members.entries()) {
|
|
await sql`
|
|
INSERT INTO team_member (team_id, user_id, role)
|
|
VALUES (${team.id}, ${member}, ${index === 0 ? 'owner' : 'member'})
|
|
`;
|
|
}
|
|
}
|
|
|
|
const maps = await loadBeatmaps();
|
|
const pools = [
|
|
{ name: 'Dev Cup', min: 4.2, max: 5.2 },
|
|
{ name: 'Dev Finals', min: 5.4, max: 6.6 },
|
|
{ name: 'Dev Qualifiers', min: 3.2, max: 4.4 }
|
|
];
|
|
const poolIds: string[] = [];
|
|
for (const pool of pools) {
|
|
const chosen = pickBand(maps, pool.min, pool.max);
|
|
const [row] = await sql`
|
|
INSERT INTO mappool (name, created_by, verified_at, origin, rating)
|
|
VALUES (${pool.name}, 'dev-seed', now(), 'Vash dev fixtures', ${(pool.min + pool.max) / 2})
|
|
RETURNING id
|
|
`;
|
|
poolIds.push(row.id);
|
|
for (let i = 0; i < SLOT_PLAN.length; i++) {
|
|
const [category, order] = SLOT_PLAN[i];
|
|
const map = chosen[i % chosen.length];
|
|
const mods =
|
|
category === 'NM' || category === 'TB' ? [] : [category === 'FM' ? 'FM' : category];
|
|
await sql`
|
|
INSERT INTO mappool_slot (
|
|
mappool_id, category, order_in_category, beatmap_id, star_rating, bpm, total_length,
|
|
mods, title, artist, version, cover_url, list_cover_url
|
|
) VALUES (
|
|
${row.id}, ${category}, ${order}, ${map.id}, ${map.sr}, ${map.bpm}, ${map.length},
|
|
${sql.array(mods, 'TEXT')}, ${map.title}, ${map.artist}, ${map.version}, ${map.cover}, ${map.list}
|
|
)
|
|
`;
|
|
}
|
|
}
|
|
|
|
const personalTeams = await sql`
|
|
SELECT id, owner_id, name FROM team WHERE owner_id LIKE 'dev-player-%' AND is_personal
|
|
ORDER BY owner_id
|
|
`;
|
|
for (let i = 0; i < personalTeams.length - 1; i += 2) {
|
|
const left = personalTeams[i];
|
|
const right = personalTeams[i + 1];
|
|
const leftPlayer = PLAYERS.find((player) => player.id === left.owner_id)!;
|
|
const rightPlayer = PLAYERS.find((player) => player.id === right.owner_id)!;
|
|
const leftWins = leftPlayer.elo >= rightPlayer.elo;
|
|
const [created] = await sql`
|
|
INSERT INTO match (name, state, config, mappool_id, winner_id, created_by, finished_at, kind, game, elo_applied)
|
|
VALUES (
|
|
${`${left.name} vs ${right.name}`},
|
|
'FINISHED',
|
|
${{ bestOf: 7, teamSize: 1, scoringType: 'score_v2', bans: 1, kind: 'quick' }},
|
|
${poolIds[i % poolIds.length]},
|
|
${leftWins ? left.id : right.id},
|
|
'dev-seed',
|
|
now() - make_interval(hours => ${i}),
|
|
'quick',
|
|
'osu',
|
|
true
|
|
)
|
|
RETURNING id
|
|
`;
|
|
await addSide(created.id, left.id, left.owner_id, 0, leftWins ? 4 : 2);
|
|
await addSide(created.id, right.id, right.owner_id, 1, leftWins ? 2 : 4);
|
|
}
|
|
|
|
const [cup] =
|
|
await sql`SELECT id FROM mappool WHERE name = 'Dev Cup' AND created_by = 'dev-seed'`;
|
|
const [tournament] = await sql`
|
|
INSERT INTO tournament (
|
|
name, description, format, state, config, max_slots, created_by, registration_open_at, registration_close_at,
|
|
tagline, accent_color, banner_url, forum_post, links, prizes
|
|
) VALUES (
|
|
'Dev Weekly',
|
|
'A small open 1v1 you can sign up for while trying the site.',
|
|
'single_elim',
|
|
'REGISTRATION',
|
|
${{ teamSize: 1, scoringType: 'score_v2', bans: 1, bestOf: 7 }},
|
|
16,
|
|
'dev-seed',
|
|
now(),
|
|
now() + interval '7 days',
|
|
'The weekly 1v1 for trying out Vash Esports',
|
|
'#ff66aa',
|
|
'https://assets.ppy.sh/beatmaps/2566536/covers/cover@2x.jpg',
|
|
${await Bun.file(new URL('./fixtures/dev-forum-post.bbcode', import.meta.url)).text()},
|
|
${DEV_LINKS},
|
|
${DEV_PRIZES}
|
|
)
|
|
RETURNING id
|
|
`;
|
|
await sql`
|
|
INSERT INTO tournament_round (tournament_id, name, abbreviation, round_order, best_of, mappool_id, bracket_type)
|
|
VALUES (${tournament.id}, 'Round of 16', 'RO16', 1, 7, ${cup.id}, 'winners')
|
|
`;
|
|
const nightShift =
|
|
await sql`SELECT id FROM team WHERE name = 'Night Shift' AND owner_id = 'dev-player-01'`;
|
|
for (const player of PLAYERS.slice(0, 6)) {
|
|
const [team] = await sql`
|
|
SELECT id FROM team WHERE owner_id = ${player.id} AND is_personal
|
|
`;
|
|
await sql`
|
|
INSERT INTO tournament_registration (tournament_id, team_id, user_id, status, seed)
|
|
VALUES (${tournament.id}, ${team.id}, ${player.id}, 'registered', ${PLAYERS.indexOf(player) + 1})
|
|
`;
|
|
}
|
|
void nightShift;
|
|
|
|
const [waitingTeam] = await sql`
|
|
SELECT id FROM team WHERE owner_id = 'dev-player-06' AND is_personal
|
|
`;
|
|
await sql`
|
|
INSERT INTO match_queue (user_id, team_id, elo, format)
|
|
VALUES ('dev-player-06', ${waitingTeam.id}, 1500, 1)
|
|
`;
|
|
|
|
const counts = await sql`
|
|
SELECT
|
|
(SELECT count(*) FROM "user" WHERE id LIKE 'dev-player-%') AS players,
|
|
(SELECT count(*) FROM team WHERE owner_id LIKE 'dev-player-%') AS teams,
|
|
(SELECT count(*) FROM mappool WHERE created_by = 'dev-seed') AS pools,
|
|
(SELECT count(*) FROM match WHERE created_by = 'dev-seed') AS matches
|
|
`;
|
|
console.log(counts[0]);
|
|
await sql.end();
|
|
}
|
|
|
|
async function addSide(
|
|
matchId: string,
|
|
teamId: string,
|
|
userId: string,
|
|
slot: number,
|
|
score: number
|
|
) {
|
|
const [participant] = await sql`
|
|
INSERT INTO match_participant (match_id, team_id, slot, score)
|
|
VALUES (${matchId}, ${teamId}, ${slot}, ${score})
|
|
RETURNING id
|
|
`;
|
|
await sql`
|
|
INSERT INTO match_participant_player (participant_id, user_id)
|
|
VALUES (${participant.id}, ${userId})
|
|
`;
|
|
}
|
|
|
|
async function loadBeatmaps(): Promise<Beatmap[]> {
|
|
const clientId = process.env.OSU_CLIENT_ID;
|
|
const clientSecret = process.env.OSU_CLIENT_SECRET;
|
|
if (!clientId || !clientSecret) return expand(FALLBACK);
|
|
const tokenResponse = await fetch('https://osu.ppy.sh/oauth/token', {
|
|
method: 'POST',
|
|
headers: { 'Content-Type': 'application/json', Accept: 'application/json' },
|
|
body: JSON.stringify({
|
|
client_id: Number(clientId),
|
|
client_secret: clientSecret,
|
|
grant_type: 'client_credentials',
|
|
scope: 'public'
|
|
})
|
|
});
|
|
if (!tokenResponse.ok) return expand(FALLBACK);
|
|
const token = (await tokenResponse.json()) as { access_token?: string };
|
|
if (!token.access_token) return expand(FALLBACK);
|
|
const maps: Beatmap[] = [];
|
|
for (let page = 1; page <= 2 && maps.length < 80; page++) {
|
|
const response = await fetch(
|
|
`https://osu.ppy.sh/api/v2/beatmapsets/search?m=0&s=ranked&page=${page}`,
|
|
{ headers: { Authorization: `Bearer ${token.access_token}`, Accept: 'application/json' } }
|
|
);
|
|
if (!response.ok) break;
|
|
const body = (await response.json()) as {
|
|
beatmapsets?: {
|
|
title: string;
|
|
artist: string;
|
|
covers?: { cover?: string; list?: string };
|
|
beatmaps?: {
|
|
id: number;
|
|
mode: string;
|
|
version: string;
|
|
difficulty_rating: number;
|
|
bpm: number;
|
|
total_length: number;
|
|
}[];
|
|
}[];
|
|
};
|
|
for (const set of body.beatmapsets ?? []) {
|
|
for (const beatmap of set.beatmaps ?? []) {
|
|
if (beatmap.mode !== 'osu') continue;
|
|
maps.push({
|
|
id: String(beatmap.id),
|
|
title: set.title,
|
|
artist: set.artist,
|
|
version: beatmap.version,
|
|
sr: beatmap.difficulty_rating,
|
|
bpm: beatmap.bpm,
|
|
length: beatmap.total_length,
|
|
cover: set.covers?.cover ?? null,
|
|
list: set.covers?.list ?? null
|
|
});
|
|
}
|
|
}
|
|
}
|
|
return maps.length >= 12 ? maps : expand(FALLBACK);
|
|
}
|
|
|
|
function expand(maps: Beatmap[]): Beatmap[] {
|
|
const copy = [...maps];
|
|
while (copy.length < 36)
|
|
copy.push({
|
|
...maps[copy.length % maps.length],
|
|
sr: maps[copy.length % maps.length].sr + 0.15
|
|
});
|
|
return copy;
|
|
}
|
|
|
|
function pickBand(maps: Beatmap[], min: number, max: number): Beatmap[] {
|
|
const inBand = maps.filter((map) => map.sr >= min && map.sr <= max);
|
|
const source = inBand.length >= 8 ? inBand : [...maps].sort((a, b) => a.sr - b.sr);
|
|
const picked: Beatmap[] = [];
|
|
for (let i = 0; i < SLOT_PLAN.length; i++) {
|
|
picked.push(
|
|
source[Math.min(source.length - 1, Math.floor((i / SLOT_PLAN.length) * source.length))]
|
|
);
|
|
}
|
|
return picked;
|
|
}
|
|
|
|
main().catch(async (err) => {
|
|
console.error(err);
|
|
await sql.end();
|
|
process.exit(1);
|
|
});
|