import type { Embed, TopLevelComponent } from "discord.js";

import { and, desc, eq, inArray } from "drizzle-orm";
import { randomUUID } from "node:crypto";

import db, {
	type Ticket,
	actors,
	ticketAttachments,
	ticketComments,
	ticketMessages,
	ticketParticipants,
	tickets,
} from "..";

export const ticketWithRelations = {
	guild: true,
	opener: {
		with: {
			user: true,
		},
	},
	subjectActor: {
		with: {
			user: true,
		},
	},
	claimedByActor: {
		with: {
			user: true,
		},
	},
	closedByActor: {
		with: {
			user: true,
		},
	},
	participants: {
		with: {
			actor: {
				with: {
					user: true,
				},
			},
		},
	},
	messages: {
		orderBy: {
			createdAt: "asc",
		},
		with: {
			actor: {
				with: {
					user: true,
				},
			},
			attachments: true,
		},
	},
	attachments: {
		with: {
			actor: {
				with: {
					user: true,
				},
			},
		},
	},
	comments: {
		with: {
			actor: {
				with: {
					user: true,
				},
			},
		},
	},
} as const;

export async function getTicketById(id: string) {
	return await db.query.tickets.findFirst({
		with: ticketWithRelations,
		where: {
			id: id,
		},
	});
}

export type TicketWithRelations = NonNullable<Awaited<ReturnType<typeof getTicketById>>>;

export async function getTicket(ticketId: string): Promise<TicketWithRelations | undefined>;
export async function getTicket(
	guildId: number,
	ticketId: number,
): Promise<TicketWithRelations | undefined>;

export async function getTicket(
	idOrGuildId: string | number,
	ticketId?: number,
): Promise<TicketWithRelations | undefined> {
	return typeof idOrGuildId === "string"
		? getTicketById(idOrGuildId)
		: getTicketByGuild(idOrGuildId, ticketId);
}

export async function getTicketByGuild(
	guildId: number,
	localId?: number,
): Promise<TicketWithRelations | undefined> {
	if (localId === undefined) return undefined;
	return await db.query.tickets.findFirst({
		where: {
			guildId: guildId,
			localId: localId,
		},
		with: ticketWithRelations,
	});
}

export async function getTicketByUser(
	userId: number,
	ticketNumber: number,
): Promise<TicketWithRelations | undefined> {
	return await db.query.tickets.findFirst({
		where: {
			openedBy: userId,
			localId: ticketNumber,
		},
		with: ticketWithRelations,
	});
}

export async function getTicketByChannelId(
	channelId: string,
): Promise<TicketWithRelations | undefined> {
	return await db.query.tickets.findFirst({
		where: {
			channelId: channelId,
		},
		with: ticketWithRelations,
	});
}

export async function editTicket(
	ticketId: string,
	data: Partial<Ticket>,
): Promise<TicketWithRelations | undefined> {
	const [result] = await db.update(tickets).set(data).where(eq(tickets.id, ticketId)).returning();
	if (!result) return undefined;
	return await getTicketById(ticketId);
}

export async function getTicketsByUser(userId: number): Promise<TicketWithRelations[]> {
	return await db.query.tickets.findMany({
		where: {
			openedBy: userId,
		},
		with: ticketWithRelations,
	});
}

export async function getTicketsByUserGDPR(userId: number) {
	const userActors = await db
		.select({ id: actors.id })
		.from(actors)
		.where(eq(actors.userId, userId));

	const actorIds = userActors.map((a) => a.id);

	if (actorIds.length === 0) {
		return [];
	}

	const openedTickets = await db
		.select({ id: tickets.id })
		.from(tickets)
		.where(inArray(tickets.openedBy, actorIds));

	const subjectTickets = await db
		.select({ id: tickets.id })
		.from(tickets)
		.where(inArray(tickets.subject, actorIds));

	const participantTickets = await db
		.select({ ticketId: ticketParticipants.ticketId })
		.from(ticketParticipants)
		.where(inArray(ticketParticipants.actorId, actorIds));

	const allTargetTicketIds = Array.from(
		new Set([
			...openedTickets.map((t) => t.id),
			...subjectTickets.map((t) => t.id),
			...participantTickets.map((t) => t.ticketId),
		]),
	);

	if (allTargetTicketIds.length === 0) {
		return [];
	}

	const userTickets = await db.query.tickets.findMany({
		where: {
			id: {
				in: allTargetTicketIds,
			},
		},
		with: ticketWithRelations,
	});

	const sanitizeActor = (actor: TicketWithRelations["opener"]) => {
		if (!actor) return null;
		const isSelf = actor.userId === userId;

		return {
			id: actor.id,
			guildId: actor.guildId,
			userId: actor.userId,
			displayName: actor.displayName,
			username: actor.username,
			avatar: actor.avatar,
			moderator: actor.moderator,
			createdAt: actor.createdAt,
			discordId: isSelf ? actor.discordId : undefined,
			user: actor.user
				? {
						id: actor.user.id,
						displayName: actor.user.displayName,
						username: actor.user.username,
						avatar: actor.user.avatar,

						discordId: isSelf ? actor.user.discordId : undefined,
						acceptedPrivacyPolicy: isSelf ? actor.user.acceptedPrivacyPolicy : undefined,
						acceptedTermsOfService: isSelf ? actor.user.acceptedTermsOfService : undefined,
						createdAt: isSelf ? actor.user.createdAt : undefined,
						updatedAt: isSelf ? actor.user.updatedAt : undefined,
					}
				: null,
		};
	};

	const sanitizeGuild = (guild: TicketWithRelations["guild"]) => {
		if (!guild) return null;
		return {
			id: guild.id,
			name: guild.name,
			icon: guild.icon,
		};
	};

	return userTickets.map((ticket) => {
		const isModeratorOnTicket =
			ticket.participants.some((p) => actorIds.includes(p.actorId) && p.role === "moderator") ||
			ticket.participants.some((p) => actorIds.includes(p.actorId) && p.actor?.moderator === true);

		const baseTicket = {
			id: ticket.id,
			guildId: ticket.guildId,
			localId: ticket.localId,
			status: ticket.status,
			openedAt: ticket.openedAt,
			closedAt: ticket.closedAt,
			closeReason: ticket.closeReason,
			openReason: ticket.openReason,
			claimedAt: ticket.claimedAt,
			guild: sanitizeGuild(ticket.guild),
			opener: sanitizeActor(ticket.opener),
			subjectActor: sanitizeActor(ticket.subjectActor),
			claimedByActor: sanitizeActor(ticket.claimedByActor),
			closedByActor: sanitizeActor(ticket.closedByActor),
		};

		if (isModeratorOnTicket) {
			return {
				...baseTicket,
				privateReason: ticket.privateReason,
				participants: ticket.participants.map((p) => ({
					id: p.id,
					role: p.role,
					actorId: p.actorId,
					createdAt: p.createdAt,
					reason: p.reason,
					actor: sanitizeActor(p.actor),
				})),
				messages: ticket.messages.map((msg) => ({
					id: msg.id,
					messageId: msg.messageId,
					actorId: msg.actorId,
					actorRole: msg.actorRole,
					content: msg.content,
					embeds: msg.embeds,
					components: msg.components,
					createdAt: msg.createdAt,
					editedAt: msg.editedAt,
					isPrivate: msg.isPrivate,
					deletedAt: msg.deletedAt,
					actor: sanitizeActor(msg.actor),
				})),
				attachments: ticket.attachments.map((att) => ({
					id: att.id,
					messageId: att.messageId,
					fileName: att.fileName,
					fileType: att.fileType,
					fileSize: att.fileSize,
					actorId: att.actorId,
					actor: sanitizeActor(att.actor),
				})),
				comments: ticket.comments.map((c) => ({
					id: c.id,
					content: c.content,
					createdAt: c.createdAt,
					updatedAt: c.updatedAt,
					actor: sanitizeActor(c.actor),
				})),
			};
		}

		const sanitizedMessages = ticket.messages
			.filter((msg) => !msg.isPrivate && !msg.deletedAt)
			.map((msg) => ({
				id: msg.id,
				messageId: msg.messageId,
				actorId: msg.actorId,
				actorRole: msg.actorRole,
				content: msg.content,
				embeds: msg.embeds,
				components: msg.components,
				createdAt: msg.createdAt,
				editedAt: msg.editedAt,
				actor: sanitizeActor(msg.actor),
			}));

		return {
			...baseTicket,
			privateReason: null,
			comments: [],
			participants: ticket.participants.map((p) => ({
				id: p.id,
				role: p.role,
				actorId: p.actorId,
				createdAt: p.createdAt,
				reason: actorIds.includes(p.actorId) ? p.reason : null,
				actor: sanitizeActor(p.actor),
			})),
			messages: sanitizedMessages,
			attachments: ticket.attachments
				.filter((att) => {
					if (!att.messageId) return true;
					return sanitizedMessages.some((msg) => msg.id === att.messageId);
				})
				.map((att) => ({
					id: att.id,
					messageId: att.messageId,
					fileName: att.fileName,
					fileType: att.fileType,
					fileSize: att.fileSize,
					actorId: att.actorId,
					actor: sanitizeActor(att.actor),
				})),
		};
	});
}

// standard ops
export async function createTicket(data: {
	guildId: number;
	openedBy: number;
	subject: number;
	status: string;
	channelId?: string | null;
	openReason?: string | null;
}): Promise<TicketWithRelations> {
	const id = randomUUID();

	return await db.transaction(async (tx) => {
		const [lastTicket] = await tx
			.select({ localId: tickets.localId })
			.from(tickets)
			.where(eq(tickets.guildId, data.guildId))
			.orderBy(desc(tickets.localId))
			.limit(1);

		const nextLocalId = lastTicket ? lastTicket.localId + 1 : 1;

		const [newTicket] = await tx
			.insert(tickets)
			.values({
				id: id,
				guildId: data.guildId,
				localId: nextLocalId,
				status: data.status,
				channelId: data.channelId,
				openedBy: data.openedBy,
				subject: data.subject,
				openReason: data.openReason,
				openedAt: new Date(),
			})
			.returning();

		if (!newTicket) throw new Error("Failed to create ticket");

		await tx.insert(ticketParticipants).values({
			ticketId: newTicket.id,
			actorId: data.openedBy,
			role: data.openedBy === data.subject ? "subject" : "participant",
			createdAt: new Date(),
		});

		const populatedTicket = await tx.query.tickets.findFirst({
			where: {
				id: newTicket.id,
			},
			with: ticketWithRelations,
		});

		if (!populatedTicket) throw new Error("Failed to retrieve created ticket");
		return populatedTicket as TicketWithRelations;
	});
}

export async function closeTicket(data: {
	ticketId: string;
	closedBy: number;
	reason?: string | null;
	status?: string;
}): Promise<TicketWithRelations | undefined> {
	const [closedTicket] = await db
		.update(tickets)
		.set({
			status: data.status ?? "closed",
			closedAt: new Date(),
			closedBy: data.closedBy,
			closeReason: data.reason,
		})
		.where(eq(tickets.id, data.ticketId))
		.returning();

	if (!closedTicket) return undefined;
	return await getTicketById(data.ticketId);
}

export async function claimTicket(
	ticketId: string,
	actorId: number,
): Promise<TicketWithRelations | undefined> {
	const [ticket] = await db
		.update(tickets)
		.set({
			claimedBy: actorId,
			claimedAt: new Date(),
		})
		.where(eq(tickets.id, ticketId))
		.returning();

	if (!ticket) return undefined;
	return await getTicketById(ticketId);
}

export async function unclaimTicket(ticketId: string): Promise<TicketWithRelations | undefined> {
	const [ticket] = await db
		.update(tickets)
		.set({
			claimedBy: null,
			claimedAt: null,
		})
		.where(eq(tickets.id, ticketId))
		.returning();

	if (!ticket) return undefined;
	return await getTicketById(ticketId);
}

// messages
export async function createTicketMessageWithAttachments(data: {
	id: string;
	ticketId: string;
	actorId: number;
	messageId?: string | null;
	content: string;
	actorRole: "participant" | "moderator" | "subject" | "bot";
	isPrivate?: boolean;
	embeds?: Embed[];
	components?: TopLevelComponent[];
	attachments?: {
		id: string;
		fileName: string;
		fileType: string;
		fileSize: number;
	}[];
}) {
	return await db.transaction(async (tx) => {
		const [message] = await tx
			.insert(ticketMessages)
			.values({
				id: data.id,
				messageId: data.messageId,
				ticketId: data.ticketId,
				actorId: data.actorId,
				isPrivate: data.isPrivate,
				content: data.content,
				actorRole: data.actorRole,
				embeds: data.embeds,
				components: data.components,
				createdAt: new Date(),
			})
			.returning();

		if (data.attachments && data.attachments.length > 0) {
			await tx.insert(ticketAttachments).values(
				data.attachments.map((att) => ({
					id: att.id,
					messageId: message?.id,
					ticketId: data.ticketId,
					actorId: data.actorId,
					fileName: att.fileName,
					fileType: att.fileType,
					fileSize: att.fileSize,
				})),
			);
		}

		const ticketUpdate: Partial<Ticket> = {
			lastMessageAt: new Date(),
		};

		if (data.actorRole === "moderator") ticketUpdate.hasModeratorMessage = true;
		else if (data.actorRole === "subject") ticketUpdate.hasAuthorMessage = true;

		await tx.update(tickets).set(ticketUpdate).where(eq(tickets.id, data.ticketId));

		return message;
	});
}

export async function getTicketTranscript(ticketId: string) {
	return await db.query.tickets.findFirst({
		where: { id: ticketId },
		with: {
			opener: true,
			subjectActor: true,
			claimedByActor: true,

			participants: {
				with: {
					actor: true,
				},
			},
			messages: {
				orderBy: (messages, { asc }) => [asc(messages.createdAt)],
				with: {
					actor: true,
					attachments: true,
				},
			},
		},
	});
}

export const ticketMessageWithRelations = {
	actor: {
		with: {
			user: true,
		},
	},
	attachments: true,
} as const;

export async function getTicketMessage(id: string) {
	return await db.query.ticketMessages.findFirst({
		where: {
			id: id,
		},
		with: ticketMessageWithRelations,
	});
}

export type TicketMessageWithRelations = NonNullable<Awaited<ReturnType<typeof getTicketMessage>>>;

export async function getTicketMessageByMessageId(
	messageId: string,
): Promise<TicketMessageWithRelations | undefined> {
	return await db.query.ticketMessages.findFirst({
		where: {
			messageId: messageId,
		},
		with: ticketMessageWithRelations,
	});
}

export async function getTicketMessages(
	ticketId: string,
	options?: { includeDeleted?: boolean },
): Promise<TicketMessageWithRelations[]> {
	return await db.query.ticketMessages.findMany({
		where: options?.includeDeleted
			? { ticketId: ticketId }
			: {
					ticketId: ticketId,
					deletedAt: { isNull: true },
				},
		orderBy: {
			createdAt: "asc",
		},
		with: ticketMessageWithRelations,
	});
}

export async function editTicketMessage(
	id: string,
	data: { content?: string; embeds?: Embed[]; components?: TopLevelComponent[] },
): Promise<TicketMessageWithRelations | undefined> {
	const [updated] = await db
		.update(ticketMessages)
		.set({
			editedAt: new Date(),
			...data,
		})
		.where(eq(ticketMessages.id, id))
		.returning();

	if (!updated) return undefined;
	return await getTicketMessage(id);
}

export async function deleteTicketMessage(
	id: string,
): Promise<TicketMessageWithRelations | undefined> {
	const [deleted] = await db
		.update(ticketMessages)
		.set({
			deletedAt: new Date(),
		})
		.where(eq(ticketMessages.id, id))
		.returning();

	if (!deleted) return undefined;
	return await getTicketMessage(id);
}

export async function restoreTicketMessage(
	id: string,
): Promise<TicketMessageWithRelations | undefined> {
	const [restored] = await db
		.update(ticketMessages)
		.set({
			deletedAt: null,
		})
		.where(eq(ticketMessages.id, id))
		.returning();

	if (!restored) return undefined;
	return await getTicketMessage(id);
}

export async function setTicketMessagePrivacy(
	id: string,
	isPrivate: boolean,
): Promise<TicketMessageWithRelations | undefined> {
	const [updated] = await db
		.update(ticketMessages)
		.set({
			isPrivate,
		})
		.where(eq(ticketMessages.id, id))
		.returning();

	if (!updated) return undefined;
	return await getTicketMessage(id);
}

export async function setTicketMessageExternalId(
	id: string,
	messageId: string,
): Promise<TicketMessageWithRelations | undefined> {
	const [updated] = await db
		.update(ticketMessages)
		.set({
			messageId,
		})
		.where(eq(ticketMessages.id, id))
		.returning();

	if (!updated) return undefined;
	return await getTicketMessage(id);
}

// participants
export async function addTicketParticipant(data: {
	ticketId: string;
	actorId: number;
	role: "participant" | "moderator" | "subject";
	reason?: string | null;
}) {
	const participant = await db.insert(ticketParticipants).values({
		ticketId: data.ticketId,
		actorId: data.actorId,
		role: data.role,
		reason: data.reason,
		createdAt: new Date(),
	});

	return participant;
}

export async function addTicketParticipants(
	data: {
		ticketId: string;
		actorId: number;
		role: "participant" | "moderator" | "subject";
		reason?: string | null;
	}[],
) {
	const participants = data.map((item) => ({
		ticketId: item.ticketId,
		actorId: item.actorId,
		role: item.role,
		reason: item.reason,
		createdAt: new Date(),
	}));
	const result = await db.insert(ticketParticipants).values(participants).returning();
	return result;
}

export async function removeTicketParticipant(ticketId: string, actorId: number) {
	const [removed] = await db
		.delete(ticketParticipants)
		.where(and(eq(ticketParticipants.ticketId, ticketId), eq(ticketParticipants.actorId, actorId)))
		.returning();

	return removed;
}

export async function getTicketParticipants(ticketId: string) {
	return await db.query.ticketParticipants.findMany({
		where: {
			ticketId: ticketId,
		},
		with: {
			actor: true,
		},
	});
}

// comments
export async function getTicketComments(ticketId: string) {
	return await db.query.ticketComments.findMany({
		where: {
			ticketId: ticketId,
		},
		with: {
			actor: {
				with: {
					user: true,
				},
			},
		},
	});
}

export async function addTicketComment(data: {
	ticketId: string;
	actorId: number;
	content: string;
}) {
	const [comment] = await db
		.insert(ticketComments)
		.values({
			id: randomUUID(),
			ticketId: data.ticketId,
			actorId: data.actorId,
			content: data.content,
			createdAt: new Date(),
			updatedAt: new Date(),
		})
		.returning();

	return comment;
}

export async function removeTicketComment(commentId: string) {
	const [removed] = await db
		.delete(ticketComments)
		.where(eq(ticketComments.id, commentId))
		.returning();

	return removed;
}

export async function editTicketComment(commentId: string, content: string) {
	const [updated] = await db
		.update(ticketComments)
		.set({
			content: content,
			updatedAt: new Date(),
		})
		.where(eq(ticketComments.id, commentId))
		.returning();

	return updated;
}

// attachments
export async function getTicketAttachments(ticketId: string) {
	return await db.query.ticketAttachments.findMany({
		where: {
			ticketId: ticketId,
		},
		with: {
			actor: {
				with: {
					user: true,
				},
			},
		},
	});
}