import { db, isEmpty, quit } from "./db.js";

/*  ___     _   _  __
 *   | \_/ |_) |_ (_
 *   |  |  |   |_ __)
 */
export type BlogCats = {
	blog_cat_id: number;
	name: string;
	url: string;
}[];

export type BlogTags = {
	tag_id: number;
	name: string;
	url: string;
	count: number;
}[];

export type BlogMeta = {
	main: BlogCats;
	base: BlogCats;
	cats: BlogCats;
	tags: BlogTags;
};

export type BlogPost = {
	post_id: number;
	url: string;
	title: string;
	subtitle: string;
	excerpt: string;
	content: string;
	date: number;
	live: number;
	meta: BlogMeta;
}[];

/*   _           _ ___    _        __
 *  |_ | | |\ | /   |  | / \ |\ | (_
 *  |  |_| | \| \_  |  | \_/ | \| __)
 */
const getPostsRange = (limit: number, offset: number): BlogPost => {
	const results: any = db
		.query(
			`
SELECT * FROM blog_posts
WHERE live = 1
ORDER BY date DESC
LIMIT $offset, $limit;
    `,
		)
		.all({ $limit: limit, $offset: offset });
	return results;
};

export const getPost = (post_url: string): BlogPost => {
	const results: any = db
		.query(
			`
SELECT post_id, url, title, subtitle, excerpt, content, date, live
FROM blog_posts
WHERE url = $post_url
LIMIT 1;
    `,
		)
		.all({ $post_url: post_url });
	return results;
};

export const getPostCats = (post_id: number): BlogCats => {
	const results: any = db
		.query(
			`
SELECT c.blog_cat_id, c.name, c.url
FROM blog_meta m INNER JOIN blog_categories c
ON m.meta_val = c.blog_cat_id
WHERE m.blog_id = $post_id AND m.meta_key = "category";
`,
		)
		.all({ $post_id: post_id });
	if (isEmpty(results)) {
		return [
			{
				blog_cat_id: 0,
				name: "uncategorized",
				url: "uncategorized",
			},
		];
	}
	return results;
};

export const getMainCat = (cat_id: number, parent:boolean): BlogCats => {
	const results: any = db
		.query(
			`
SELECT blog_cat_id, name, url
FROM blog_categories
WHERE blog_cat_id = $cat_id LIMIT 0,1;
`,
		)
		.all({ $cat_id: parent ? Math.floor(cat_id) : cat_id });
	if (isEmpty(results)) {
		return [
			{
				blog_cat_id: 0,
				name: "uncategorized",
				url: "uncategorized",
			},
		];
	}
	return results;
};

export const getPostTags = (post_id: number): BlogTags => {
	const results: any = db
		.query(
			`
SELECT t.tag_id, t.name, t.url, t.tag_count as count
FROM blog_meta m INNER JOIN blog_tags t
ON m.meta_val = t.tag_id
WHERE m.blog_id = $post_id AND m.meta_key = 'tag';
`,
		)
		.all({ $post_id: post_id });
	if (isEmpty(results)) {
		return [
			{
				tag_id: 0,
				name: "",
				url: "",
				count: 0,
			},
		];
	}
	return results;
};

export const getCategories = (): BlogCats => {
	const results: any = db
		.query(
			`
SELECT blog_cat_id, name, url
FROM blog_categories
ORDER BY blog_cat_id ASC;
`,
		)
		.all({});
	if (isEmpty(results)) {
		return [
			{
				blog_cat_id: 0,
				name: "uncategorized",
				url: "uncategorized",
			},
		];
	}
	return results;
};

export const getCategoryByID = (cat_id: number): BlogCats => {
	const results: any = db
		.query(
			`
SELECT blog_cat_id, name, url
FROM blog_categories
WHERE blog_cat_id = $cat_id
LIMIT 1;
`,
		)
		.all({ $cat_id: cat_id });
	return results;
};

export const getTags = (): BlogTags => {
	const results: any = db
		.query(
			`
SELECT tag_id, name, url, tag_count as count
FROM blog_tags
ORDER BY url ASC;
`,
		)
		.all({});
	if (isEmpty(results)) {
		return [
			{
				tag_id: 0,
				name: "untagged",
				url: "untagged",
				count: 0,
			},
		];
	}
	return results;
};
export const getTotalPostCount = (): number => {
	const results = db
		.query(
			`
SELECT post_id AS total
FROM blog_posts
WHERE live = 1;
`,
		)
		.all({});
	return results.length;
};

export const getTotalPostCountByCat = (cat_id: number): number => {
	const results = db
		.query(
			`
SELECT DISTINCT(blog_id)
FROM blog_meta
WHERE meta_key = 'category'
AND meta_val >= $greater AND meta_val < $less;
`,
		)
		.all({ $greater: cat_id, $less: cat_id + 1 });
	return results.length;
};
export const getTotalPostCountBySubCat = (subcat_id: number): number => {
	const results = db
		.query(
			`
SELECT DISTINCT(blog_id)
FROM blog_meta
WHERE meta_key = 'category'
AND meta_val = $subcat_id;
`,
		)
		.all({ $subcat_id: subcat_id });
	return results.length;
};

export const getTotalPostCountByTag = (tag_id: number): number => {
	const results = db
		.query(
			`
SELECT blog_id as total
FROM blog_meta
WHERE meta_key = 'tag'
AND meta_val = $tag_id;
`,
		)
		.all({ $tag_id: tag_id });
	return results.length;
};
export const getCatByName = (cat_name: string) => {
	const results: any = db
		.query(
			`
SELECT blog_cat_id
FROM blog_categories
WHERE url = $cat_name
LIMIT 1;
`,
		)
		.all({ $cat_name: cat_name });
	return parseFloat(results[0].blog_cat_id);
};
export const getTagByName = (tag_name: string) => {
	const results: any = db
		.query(
			`
SELECT tag_id
FROM blog_tags
WHERE url = $tag_name
LIMIT 1;",
`,
		)
		.all({ $tag_name: tag_name });
	return parseInt(results[0].tag_id);
};
const getPostsByCat = (cat_id: number, limit: number, offset: number) => {
	const results = db
		.query(
			`
SELECT p.post_id, p.url, p.title, p.subtitle, p.excerpt, p.content, p.date
FROM blog_meta as m
INNER JOIN blog_posts as p
ON m.blog_id = p.post_id
WHERE m.meta_key = 'category'
AND m.meta_val >= $greater AND m.meta_val < $less AND p.live = 1
GROUP BY p.post_id
ORDER BY p.date DESC
LIMIT $offset, $limit;
`,
		)
		.all({
			$greater: cat_id,
			$less: cat_id + 1,
			$limit: limit,
			$offset: offset,
		});
	return results;
};

const getPostsBySubCat = (subcat_id: number, limit: number, offset: number) => {
	const results = db
		.query(
			`
SELECT p.post_id, p.url, p.title, p.subtitle, p.excerpt, p.content, p.date
FROM blog_meta as m
INNER JOIN blog_posts as p
ON m.blog_id = p.post_id
WHERE m.meta_key = 'category'
AND m.meta_val = $subcat_id AND p.live = 1
GROUP BY p.post_id
ORDER BY p.date DESC
LIMIT $offset, $limit;
`,
		)
		.all({
			$subcat_id: subcat_id,
			$limit: limit,
			$offset: offset,
		});
	return results;
};
const getPostsByTag = (tag_id: number, limit: number, offset: number) => {
	const results = db
		.query(
			`
SELECT p.post_id, p.url, p.title, p.subtitle, p.excerpt, p.content, p.date
FROM blog_meta as m
INNER JOIN blog_posts as p
ON m.blog_id = p.post_id
WHERE m.meta_key = 'tag' AND m.meta_val = $tag_id AND p.live = 1
GROUP BY p.post_id
ORDER BY p.date DESC
LIMIT $offset, $limit;
		`,
		)
		.all({
			$tag_id: tag_id,
			$limit: limit,
			$offset: offset,
		});
	return results;
};

/*   _     _   _   _ ___ __
 *  |_ \/ |_) / \ |_) | (_
 *  |_ /\ |   \_/ | \ | __)
 */
export const getPostByURL = (url: string): BlogPost => {
	let post = getPost(url);
	post.forEach((post) => {
		const cats = getPostCats(post.post_id);
		const tags = getPostTags(post.post_id);
		const main = getMainCat(cats[0].blog_cat_id, true);
		const base = getMainCat(cats[0].blog_cat_id, false);
		post.meta = { cats, tags, main, base };
	});
	return post;
};

export const getPosts = (limit: number, offset: number): BlogPost => {
	let posts = getPostsRange(limit, offset);
	posts.forEach((post) => {
		const cats = getPostCats(post.post_id);
		const tags = getPostTags(post.post_id);
		const main = getMainCat(cats[0].blog_cat_id, true);
		const base = getMainCat(cats[0].blog_cat_id, false);
		post.meta = { cats, tags, main, base };
	});
	return posts;
};

export const getPostsByCatID = (
	cat_id: number,
	limit: number,
	offset: number,
) => {
	let posts = getPostsByCat(cat_id, limit, offset);
	posts.forEach((post: any) => {
		const cats = getPostCats(post.post_id);
		const tags = getPostTags(post.post_id);
		const main = getMainCat(cats[0].blog_cat_id, true);
		const base = getMainCat(cats[0].blog_cat_id, false);
		post.meta = { cats, tags, main, base };
	});
	return posts;
};

export const getPostsBySubCatID = (
	subcat_id: number,
	limit: number,
	offset: number,
) => {
	let posts = getPostsBySubCat(subcat_id, limit, offset);
	posts.forEach((post: any) => {
		const cats = getPostCats(post.post_id);
		const tags = getPostTags(post.post_id);
		const main = getMainCat(cats[0].blog_cat_id, true);
		const base = getMainCat(cats[0].blog_cat_id, false);
		post.meta = { cats, tags, main, base };
	});
	return posts;
};

export const getPostsByTagID = (
	tag_id: number,
	limit: number,
	offset: number,
) => {
	let posts = getPostsByTag(tag_id, limit, offset);
	posts.forEach((post: any) => {
		const cats = getPostCats(post.post_id);
		const tags = getPostTags(post.post_id);
		const main = getMainCat(cats[0].blog_cat_id, true);
		const base = getMainCat(cats[0].blog_cat_id, false);
		post.meta = { cats, tags, main, base };
	});
	return posts;
};

export { quit };