import { db } from './db';

const migrations: Array<{ id: number; sql: string }> = [
	{
		id: 1,
		sql: `
			CREATE TABLE giveaways (
				at_uri     TEXT PRIMARY KEY,
				did        TEXT NOT NULL,
				rkey       TEXT NOT NULL,
				status     TEXT NOT NULL DEFAULT 'open',
				prizes     TEXT NOT NULL,
				hmac_secret TEXT NOT NULL,
				ends_at    TEXT,
				created_at TEXT NOT NULL
			);

			CREATE TABLE entries (
				at_uri          TEXT PRIMARY KEY,
				giveaway_at_uri TEXT NOT NULL REFERENCES giveaways(at_uri),
				author_did      TEXT NOT NULL,
				token           TEXT NOT NULL,
				eligible        INTEGER NOT NULL DEFAULT 1,
				created_at      TEXT NOT NULL
			);
			CREATE INDEX entries_giveaway ON entries(giveaway_at_uri);
			CREATE UNIQUE INDEX entries_author_giveaway ON entries(giveaway_at_uri, author_did);

			CREATE TABLE draws (
				at_uri          TEXT PRIMARY KEY,
				giveaway_at_uri TEXT NOT NULL REFERENCES giveaways(at_uri),
				prize_id        TEXT NOT NULL,
				entry_at_uri    TEXT NOT NULL REFERENCES entries(at_uri),
				status          TEXT NOT NULL DEFAULT 'pending',
				drawn_at        TEXT NOT NULL
			);
			CREATE INDEX draws_giveaway ON draws(giveaway_at_uri);

			CREATE TABLE oauth_sessions (
				key        TEXT PRIMARY KEY,
				state      TEXT NOT NULL,
				updated_at TEXT NOT NULL DEFAULT (datetime('now'))
			);
		`,
	},
	{
		id: 2,
		sql: `ALTER TABLE giveaways ADD COLUMN event_uri TEXT NOT NULL DEFAULT '';`,
	},
	{
		id: 3,
		sql: `
			CREATE TABLE device_sessions (
				token_hash  TEXT PRIMARY KEY,
				did         TEXT NOT NULL,
				session_type TEXT NOT NULL
			);
		`,
	},
];

function migrate() {
	db.exec(`
		CREATE TABLE IF NOT EXISTS _migrations (
			id    INTEGER PRIMARY KEY,
			ran_at TEXT NOT NULL DEFAULT (datetime('now'))
		)
	`);

	const completed = new Set<number>(
		db.prepare('SELECT id FROM _migrations').all().map((r: unknown) => (r as { id: number }).id)
	);

	const insert = db.prepare('INSERT INTO _migrations (id) VALUES (?)');

	const run = db.transaction(() => {
		for (const { id, sql } of migrations) {
			if (completed.has(id)) continue;
			db.exec(sql);
			insert.run(id);
		}
	});

	run();
}

export { migrate, migrations };