import { databaseTimeToDate } from '$lib/Utility/time';
import { Database } from 'bun:sqlite';

export interface Badge {
	post?: string;
	image?: string;
	description?: string;
	id?: number;
	time?: string;
	category?: string;
}

const database = new Database('./data/due_moe.sqlite3', {
	create: true
});

database
	.query(
		`CREATE TABLE IF NOT EXISTS user_badges (
	id INTEGER PRIMARY KEY, 
	user_id INTEGER NOT NULL, 
	post TEXT(1000) NOT NULL,
	image TEXT(1000) NOT NULL,
	description TEXT(1000) DEFAULT NULL,
	category TEXT(1000) DEFAULT NULL,
	time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)`
	)
	.run();

export const getUserBadges = (userId: number): Badge[] => {
	return database
		.query('SELECT * FROM user_badges WHERE user_id = $userId')
		.all({ $userId: String(userId) })
		.sort((a, b) =>
			databaseTimeToDate((a as Badge).time ?? '').getTime() >
			databaseTimeToDate((b as Badge).time ?? '').getTime()
				? -1
				: 1
		) as Badge[];
};

export const addUserBadge = (userId: number, badge: Badge) => {
	const { post, image, description, time, category } = badge;

	if (post === undefined || image === undefined) return;

	if (time) {
		database
			.query(
				`INSERT INTO user_badges (user_id, post, image, description, time, category) VALUES (?1, ?2, ?3, ?4, ?5, ?6);`
			)
			.run(userId.toString(), post, image, description || null, time, category || null);
	} else {
		database
			.query(
				`INSERT INTO user_badges (user_id, post, image, description, category) VALUES (?1, ?2, ?3, ?4, ?5);`
			)
			.run(userId.toString(), post, image, description || null, category || null);
	}
};

export const removeUserBadge = (userId: number, id: number) => {
	if (!isNaN(id))
		database.query('DELETE FROM user_badges WHERE user_id = $userId AND id = $id').run({
			$userId: userId,
			$id: id
		});
};

export const updateUserBadge = (userId: number, id: number, badge: Badge) => {
	if (badge.post === undefined || badge.image === undefined || badge.description === undefined)
		return;

	database
		.query(
			'UPDATE user_badges SET post = ?1, image = ?2, description = ?3, category = ?6 WHERE id = ?4 AND user_id = ?5'
		)
		.run(
			badge.post || null,
			badge.image || null,
			badge.description || null,
			id,
			userId,
			badge.category || null
		);
};