import { Database } from "bun:sqlite";
import { BlogCats, BlogPost, BlogTags, getPostByURL, getPostCats, getPostTags } from "./blog.js";

const db = new Database("src/db.sqlite");

/*   _           _ ___    _        __
 *  |_ | | |\ | /   |  | / \ |\ | (_
 *  |  |_| | \| \_  |  | \_/ | \| __)
 */
export const getAllPosts = (): any => {
	const results: any = db
		.query(`
SELECT * FROM blog_posts
ORDER BY date DESC;
    `).all({});
	return results;
};

export const checkCat = (cat: string): any => {
	const results: any = db
		.query(`
SELECT * FROM blog_categories
WHERE url = $cat or name = $cat
LIMIT 1;
    `).all({ $cat: cat });
	return results;
};

export const checkTag = (tag: string): any => {
	const results: any = db
		.query(`
SELECT * FROM blog_tags
WHERE url = $tag or name = $tag or tag_id = $tag
LIMIT 1;
    `).all({ $tag: tag });
	return results;
};

export const addCat = (cat: string, url: string, parent: number): any => {
	let clean = url == "" ? cat : url;
	clean = clean
		.trim()
		.toLowerCase()
		.replace(/ /g, "_")
		.replace(/[^0-9a-z_]/g, "");
	db.query(`
INSERT INTO blog_categories
(cat_id, blog_cat_id, name, url)
VALUES (NULL, $parent, $name, $url);
`).all({
		$parent: parent,
		$name: cat,
		$url: clean,
	});
	const catId = checkCat(cat);
	if (parent == 0) {
		db.query(`
UPDATE blog_categories
SET blog_cat_id = $id
WHERE cat_id = $id;
`).all({ $id: catId[0].cat_id });
	} else {
		let count: any = db.query(`
SELECT COUNT(cat_id) as count
FROM blog_categories
WHERE blog_cat_id > $parent
AND blog_cat_id < $next;
`).all({
			$parent: parent,
			$next: parent + 1,
		});

		count = count[0]["count"];
		const newId = parent + ((count + 1) * 0.001);
		db.query(`
UPDATE blog_categories
SET blog_cat_id = $catID
WHERE cat_id = $id;
`).all({
			$catID: newId,
			$id: catId[0].cat_id,
		});
	}
	return checkCat(cat);
};

export const addTag = (tag: string, url: string): any => {
	let clean = url == "" ? tag : url;
	clean = clean
		.trim()
		.toLowerCase()
		.replace(/ /g, "_")
		.replace(/[^0-9a-z_]/g, "");
	db.query(`
INSERT INTO blog_tags
(tag_id, name, url, tag_count)
VALUES (NULL, $name, $url, 0);
`).all({
		$name: tag,
		$url: clean,
	});
	return checkTag(tag);
};

export const rmMeta = (post: number, type: string, val: number): void => {
	db.query(`
DELETE FROM blog_meta
WHERE blog_id = $post
AND meta_key = $type
and meta_val = $val;
`).all({
		$post: post,
		$type: type,
		$val: val,
	});

	if (type == "tag") {
		let tagCount: any = db
			.query(`
SELECT tag_count
FROM blog_tags
WHERE tag_id = $tag_id
LIMIT 1;
`).all({ $tag_id: val });
		let count: number = parseInt(tagCount[0].tag_count);
		if (count > 0) {
			count--;
		}
		db.query(`
UPDATE blog_tags
SET tag_count = $count
WHERE tag_id = $tag_id;
`).all({
			$count: count,
			$tag_id: val,
		});
	}
};

export const addMeta = (post: number, type: string, val: number): void => {
	db.query(`
INSERT INTO blog_meta
(meta_id, blog_id, meta_key, meta_val)
VALUES (NULL , $post, $type, $val);
`).all({
		$post: post,
		$type: type,
		$val: val,
	});

	if (type == "tag") {
		let tagCount: any = db
			.query(`
SELECT tag_count
FROM blog_tags
WHERE tag_id = $tag_id
LIMIT 1;
`).all({ $tag_id: val });
		let count: number = parseInt(tagCount[0].tag_count);
		count++;
		db.query(`
UPDATE blog_tags
SET tag_count = $count
WHERE tag_id = $tag_id;
`).all({
			$count: count,
			$tag_id: val,
		});
	}
};

export const addPost = (post: any): void => {
	const live = post.live == "on" ? 1 : 0;
	// @todo: update db date format to mili epoch
	const date = new Date(post.date).valueOf() / 1000;
	db.query(`
INSERT INTO blog_posts
(post_id, url, title, subtitle, excerpt, content, comments, date, live)
VALUES (NULL, $url, $title, $subtitle, $excerpt, $content, 0, $date, $live);
    `).all({
		$live: live,
		$date: date,
		$title: post.title,
		$subtitle: post.subtitle,
		$excerpt: post.excerpt,
		$content: post.content,
		$url: post.url,
	});

	const id: BlogPost = getPostByURL(post.url);
	post.tags.forEach((tag: any) => {
		let tagId: BlogTags = checkTag(tag);
		if (tagId[0] == undefined) {
			tagId = addTag(tag, "");
		}
		addMeta(id[0].post_id, "tag", tagId[0].tag_id);
	});

	post.cats.forEach((cat: any) => {
		let catId: BlogCats = checkCat(cat);
		if (catId[0] == undefined) {
			catId = addCat(cat, "", 0);
		}
		addMeta(id[0].post_id, "category", catId[0].blog_cat_id);
	});
};

export const modPost = (post: any): void => {
	const live = post.live == "on" ? 1 : 0;
	// @todo: update db date format to mili epoch
	const date = new Date(post.date).valueOf() / 1000;
	db.query(`
UPDATE blog_posts
SET date = $date,
    title = $title,
    subtitle = $subtitle,
    excerpt = $excerpt,
    content = $content,
		url = $url,
    live = $live
WHERE post_id = $id;
    `).all({
		$id: post.id,
		$live: live,
		$date: date,
		$title: post.title,
		$subtitle: post.subtitle,
		$excerpt: post.excerpt,
		$content: post.content,
		$url: post.url,
	});

	const currentTags: BlogTags = getPostTags(post.id);
	post.tags.forEach((tag: any) => {
		if (currentTags.filter((t) => t.url == tag).toString().length == 0) {
			let tagId: BlogTags = checkTag(tag);
			if (tagId[0] == undefined) {
				tagId = addTag(tag, "");
			}
			addMeta(post.id, "tag", tagId[0].tag_id);
		}
	});
	currentTags.forEach((tag) => {
		if (!post.tags.includes(tag.url)) {
			rmMeta(post.id, "tag", tag.tag_id);
		}
	});

	const currentCats: BlogCats = getPostCats(post.id);
	post.cats.forEach((cat: any) => {
		if (currentCats.filter((c) => c.url == cat).toString().length == 0) {
			let catId: BlogCats = checkCat(cat);
			if (catId[0] == undefined) {
				catId = addCat(cat, "", 0);
			}
			addMeta(post.id, "category", catId[0].blog_cat_id);
		}
	});
	currentCats.forEach((cat) => {
		if (!post.cats.includes(cat.url)) {
			rmMeta(post.id, "category", cat.blog_cat_id);
		}
	});
};

export const modTag = (tag_id: number, tag_name: string, tag_url: string) => {
	db.query(`
UPDATE blog_tags
SET name = $name, url = $url
WHERE tag_id = $id;
	`).all({
		$name: tag_name,
		$url: tag_url,
		$id: tag_id,
	});
}

export const modCat = (cat_id: number, blog_cat_id: number, parent_cat_id: number, cat_name: string, cat_url: string) => {
	if (parent_cat_id != 0) {
		if (Math.floor(blog_cat_id) == Math.floor(parent_cat_id)) {
			db.query(`
UPDATE blog_categories
SET name = $name, url = $url
WHERE blog_cat_id = $id;
		`).all({
				$name: cat_name,
				$url: cat_url,
				$id: blog_cat_id,
			});
		} else {
			let count: any = db.query(`
SELECT COUNT(cat_id) as count
FROM blog_categories
WHERE blog_cat_id > $parent
AND blog_cat_id < $next;
`).all({
				$parent: parent_cat_id,
				$next: Math.floor(parent_cat_id + 1),
			});

			count = count[0]["count"];
			const newId: number = Math.floor(parent_cat_id) + ((count + 1) * 0.001);
			db.query(`
UPDATE blog_categories
SET blog_cat_id = $catID
WHERE cat_id = $id;
`).all({
				$catID: newId,
				$id: cat_id,
			});
		}
	} else {
		db.query(`
UPDATE blog_categories
SET name = $name, url = $url, blog_cat_id = $id
WHERE cat_id = $id;
		`).all({
			$name: cat_name,
			$url: cat_url,
			$id: cat_id,
		});
	}
};

export const rmPost = (post_id: number): void => {
	db.query(`
DELETE FROM blog_posts
WHERE post_id = $id;
    `).all({ $id: post_id });

	const currentTags: BlogTags = getPostTags(post_id);
	currentTags.forEach((tag) => {
		rmMeta(post_id, "tag", tag.tag_id);
	});

	const currentCats: BlogCats = getPostCats(post_id);
	currentCats.forEach((cat) => {
		rmMeta(post_id, "category", cat.blog_cat_id);
	});
};