import { readFile } from 'node:fs/promises';
import postgres from 'postgres';

if (!process.env.DB_URL) throw new Error('DB_URL is required');
const catalog = JSON.parse(
	await readFile(new URL('../data/pokemon-research.json', import.meta.url), 'utf8')
);
const client = postgres(process.env.DB_URL, { max: 1 });
try {
	await client.begin(async (tx) => {
		const species = new Map(
			(await tx`select id, number from pokemon`).map((r) => [r.number, r.id])
		);
		const games = new Map(
			(await tx`select id, source_id from games`).map((r) => [r.source_id, r.id])
		);
		const speciesId = (n: number) => {
			const id = species.get(n);
			if (!id) throw new Error(`Seed species ${n} first`);
			return id;
		};
		const gameId = (n: number) => {
			const id = games.get(n);
			if (!id) throw new Error(`Seed game ${n} first`);
			return id;
		};
		for (const row of catalog.facts)
			await tx`insert into pokemon_research (pokemon_id,facts) values (${speciesId(row.number)},${tx.json(row.facts)}) on conflict (pokemon_id) do update set facts=excluded.facts`;
		for (const row of catalog.games) {
			if (!games.has(row.sourceId)) continue;
			await tx`insert into pokemon_game_research (game_id,version_group,regions) values (${gameId(row.sourceId)},${row.versionGroup},${row.regions}) on conflict (game_id) do update set version_group=excluded.version_group,regions=excluded.regions`;
		}
		for (let offset = 0; offset < catalog.dexes.length; offset += 1000) {
			const batch = catalog.dexes
				.slice(offset, offset + 1000)
				.filter((r: number[]) => games.has(r[1]))
				.map(([n, v, d, name, number]: [number, number, string, string, number]) => ({
					pokemon_id: speciesId(n),
					game_id: gameId(v),
					dex: d,
					dex_name: name,
					number
				}));
			if (batch.length)
				await tx`insert into pokemon_dex_numbers ${tx(batch)} on conflict (pokemon_id,game_id,dex) do update set number=excluded.number,dex_name=excluded.dex_name`;
		}
		for (const row of catalog.moves)
			await tx`insert into pokemon_moves ${tx(row)} on conflict (id) do update set name=excluded.name,type=excluded.type,category=excluded.category,power=excluded.power,accuracy=excluded.accuracy,pp=excluded.pp,priority=excluded.priority,target=excluded.target`;
		for (let offset = 0; offset < catalog.learnsets.length; offset += 500) {
			const batch = catalog.learnsets
				.slice(offset, offset + 500)
				.map(([n, version_group, moves]: [number, number, never]) => ({
					pokemon_id: speciesId(n),
					version_group,
					moves: tx.json(moves)
				}));
			await tx`insert into pokemon_learnsets ${tx(batch)} on conflict (pokemon_id,version_group) do update set moves=excluded.moves`;
		}
		console.log(
			`Seeded ${catalog.facts.length} profiles, ${catalog.dexes.length} dex entries, ${catalog.moves.length} moves and ${catalog.learnsets.length} versioned learnsets`
		);
	});
} finally {
	await client.end();
}