import {
	type Guild,
	type Ticket,
	db,
	guild,
	manager,
	tickets,
} from "@/database";
import type { Snowflake } from "discord.js";
import { and, eq, sql } from "drizzle-orm";

type QueryResult<T, WillCreate extends boolean = false> =
	| { exists: true; data: T }
	| (WillCreate extends true
			? { exists: false; data: T }
			: { exists: false; data: T | null });

export async function getGuild(
	guildId: Snowflake,
): Promise<QueryResult<Guild, true>> {
	const result = await db
		.select()
		.from(guild)
		.where(eq(guild.guildId, guildId))
		.execute();

	if (!result || result.length === 0) {
		const newGuild = await db
			.insert(guild)
			.values({ guildId })
			.returning()
			.execute();
		return {
			exists: false,
			data: newGuild[0],
		};
	}

	return {
		exists: true,
		data: result[0],
	};
}

export async function getTicketMessage(
	guildId: Snowflake,
): Promise<QueryResult<string, false>> {
	const guild = await getGuild(guildId);
	if (!guild || !guild.data.ticket_message) {
		return {
			exists: false,
			data: null,
		};
	}

	return {
		exists: true,
		data: guild.data.ticket_message,
	};
}

export async function getTicket(
	guildId: Snowflake,
	channelId: Snowflake,
): Promise<QueryResult<Ticket, false>> {
	const guild = await getGuild(guildId);
	if (!guild || !guild.data.ticket_message) {
		return {
			exists: false,
			data: null,
		};
	}

	const ticket = await db
		.selectDistinct()
		.from(tickets)
		.where(and(eq(tickets.guildId, guildId), eq(tickets.channelId, channelId)))
		.execute();

	if (!ticket || ticket.length === 0) {
		return {
			exists: false,
			data: null,
		};
	}

	return {
		exists: true,
		data: ticket[0],
	};
}

export async function getTicketById(
	guildId: Snowflake | null,
	id: number,
): Promise<QueryResult<Ticket, false>> {
	// const guild = await getGuild(guildId);
	// if (!guild || !guild.data.ticket_message) {
	// 	return {
	// 		exists: false,
	// 		data: null,
	// 	};
	// }

	const ticket = await db
		.select()
		.from(tickets)
		// .where(and(eq(tickets.guildId, guildId), eq(tickets.id, id)))
		.where(eq(tickets.id, id))
		.execute();

	if (!ticket || ticket.length === 0) {
		return {
			exists: false,
			data: null,
		};
	}

	return {
		exists: true,
		data: ticket[0],
	};
}

export async function getTicketByNumericId(
	guildId: Snowflake,
	numericId: number,
): Promise<QueryResult<Ticket, false>> {
	const guild = await getGuild(guildId);
	if (!guild || !guild.data.ticket_message) {
		return {
			exists: false,
			data: null,
		};
	}

	const ticket = await db
		.select()
		.from(tickets)
		.where(
			and(eq(tickets.guildId, guildId), eq(tickets.ticketNumber, numericId)),
		)
		.execute();

	if (!ticket || ticket.length === 0) {
		return {
			exists: false,
			data: null,
		};
	}

	return {
		exists: true,
		data: ticket[0],
	};
}

export async function getManagerRoleIds(
	guildId: Snowflake,
): Promise<Snowflake[]> {
	const rows = await db
		.select({ roleId: manager.roleId })
		.from(manager)
		.where(eq(manager.guildId, guildId))
		.execute();

	return rows.map((r) => r.roleId);
}

export async function countTickets(guildId: Snowflake) {
	const result = await db
		.select({ count: sql<number>`COUNT(*)` })
		.from(tickets)
		.where(eq(tickets.guildId, guildId))
		.execute();

	return result[0].count;
}