import * as newDb from "@stealth-developers/db";

import { GUILD_ID, SYSTEM_ACTOR_ID } from "./constants";
import * as oldDb from "./old-db";

const [guild] = await newDb.db
	.select()
	.from(newDb.guilds)
	.where(newDb.eq(newDb.guilds.id, GUILD_ID))
	.limit(1);

async function migrate() {
	if (!guild) throw new Error("guild not found");

	console.log("fetching old data...");
	const allTickets = await oldDb.db.select().from(oldDb.tickets);
	const allMessages = await oldDb.db.select().from(oldDb.ticketMessages);
	const allAttachments = await oldDb.db.select().from(oldDb.attachments);

	console.log(
		`got ${allTickets.length} tickets, ${allMessages.length} messages, and ${allAttachments.length} attachments.`,
	);

	const discordIds = new Set<string>();
	const addId = (id: string | null | undefined) => {
		if (!id) return;
		if (id === "system" || id === "0" || id === "reporter") return;
		discordIds.add(id);
	};

	for (const t of allTickets) {
		addId(t.authorId);
		addId(t.openedBy);
		addId(t.closedBy);
		addId(t.claimedBy);
	}

	for (const m of allMessages) {
		addId(m.authorId);
	}

	for (const a of allAttachments) {
		addId(a.authorId);
	}

	const actorMap = new Map<string, number>();
	const discordIdArray = Array.from(discordIds);

	if (discordIdArray.length > 0) {
		console.log("resolving existing actors...");
		const existingActors = await newDb.db
			.select()
			.from(newDb.actors)
			.where(newDb.inArray(newDb.actors.discordId, discordIdArray));

		for (const actor of existingActors) {
			actorMap.set(actor.discordId!, actor.id);
		}
	}

	const resolveActorId = (
		discordId: string | null | undefined,
		fallbackOpenedByActorId?: number,
	): number => {
		if (!discordId) return SYSTEM_ACTOR_ID;
		if (discordId === "system" || discordId === "0") {
			return SYSTEM_ACTOR_ID;
		}
		if (discordId === "reporter") {
			return fallbackOpenedByActorId ?? SYSTEM_ACTOR_ID;
		}
		const id = actorMap.get(discordId);
		return id ?? SYSTEM_ACTOR_ID;
	};

	const resolveNullableActorId = (
		discordId: string | null | undefined,
		fallbackOpenedByActorId?: number,
	): number | null => {
		if (!discordId) return null;
		if (discordId === "system" || discordId === "0") {
			return SYSTEM_ACTOR_ID;
		}
		if (discordId === "reporter") {
			return fallbackOpenedByActorId ?? SYSTEM_ACTOR_ID;
		}
		const id = actorMap.get(discordId);
		return id ?? SYSTEM_ACTOR_ID;
	};

	const cleanDate = (d: any): Date | null => {
		if (!d) return null;
		const date = d instanceof Date ? d : new Date(d);
		if (isNaN(date.getTime())) {
			return null;
		}
		return date;
	};

	const cleanRequiredDate = (d: any, fallback = new Date()): Date => {
		return cleanDate(d) ?? fallback;
	};

	const isUniqueConstraintError = (err: any): boolean => {
		const msg = (err.message || "").toLowerCase();
		return (
			msg.includes("unique constraint failed") ||
			msg.includes("sqlite_constraint") ||
			msg.includes("unique index") ||
			err.code === "SQLITE_CONSTRAINT"
		);
	};

	// precalc statistics from messages to fill metadata in tickets
	const ticketStatsMap = new Map<
		number,
		{
			lastMessageAt: Date | null;
			hasModeratorMessage: boolean;
			hasAuthorMessage: boolean;
		}
	>();

	for (const t of allTickets) {
		ticketStatsMap.set(t.id, {
			lastMessageAt: null,
			hasModeratorMessage: false,
			hasAuthorMessage: false,
		});
	}

	for (const m of allMessages) {
		const mDeletedAt = cleanDate(m.deletedAt);
		if (mDeletedAt) continue;

		const stats = ticketStatsMap.get(m.ticketId);
		if (!stats) continue;

		const ticket = allTickets.find((t) => t.id === m.ticketId);
		if (!ticket) continue;

		const subjectActorId = resolveActorId(ticket.authorId);
		const actorId = resolveActorId(m.authorId);

		const mCreatedAt = cleanDate(m.createdAt);
		if (mCreatedAt) {
			if (!stats.lastMessageAt || mCreatedAt > stats.lastMessageAt) {
				stats.lastMessageAt = mCreatedAt;
			}
		}

		if (m.authorType === "staff") {
			stats.hasModeratorMessage = true;
		} else if (m.authorType === "user" && actorId === subjectActorId) {
			stats.hasAuthorMessage = true;
		}
	}

	// map & insert tickets and participants
	const oldTicketIdToPublicId = new Map<number, string>();
	const oldTicketIdToOpenedByActorId = new Map<number, number>();
	const oldTicketIdToSubjectActorId = new Map<number, number>();

	const ticketsToInsert: newDb.NewTicket[] = [];
	const participantsToInsert: newDb.NewTicketParticipant[] = [];

	for (const t of allTickets) {
		const openedByActorId = resolveActorId(t.openedBy);
		const subjectActorId = resolveActorId(t.authorId);
		const closedByActorId = resolveNullableActorId(t.closedBy, openedByActorId);
		const claimedByActorId = resolveNullableActorId(t.claimedBy);

		oldTicketIdToPublicId.set(t.id, t.publicId);
		oldTicketIdToOpenedByActorId.set(t.id, openedByActorId);
		oldTicketIdToSubjectActorId.set(t.id, subjectActorId);

		const stats = ticketStatsMap.get(t.id) || {
			lastMessageAt: null,
			hasModeratorMessage: false,
			hasAuthorMessage: false,
		};

		const numbers = {
			openedByActorId,
			subjectActorId,
			closedByActorId,
			claimedByActorId,

			id: t.id,
			guildId: guild.id,
			localId: t.id,
		};

		for (const [key, value] of Object.entries(numbers)) {
			if (value && (isNaN(value) || value === Infinity)) {
				throw new Error(`Invalid actor ID for ticket ${t.id}: ${key}`);
			}
		}

		ticketsToInsert.push({
			id: t.publicId,
			guildId: guild.id,
			localId: t.id,

			status: t.status,
			channelId: t.channelId,
			threadId: null,

			closeReason: t.closeReason,
			privateReason: t.privateReason,

			openedAt: cleanRequiredDate(t.createdAt),
			closedAt: cleanDate(t.closedAt),
			closedBy: closedByActorId,

			openedBy: openedByActorId,
			openReason: undefined,

			subject: subjectActorId,

			claimedBy: claimedByActorId,
			claimedAt: cleanDate(t.claimedAt),

			thankedAt: cleanDate(t.thankedAt),
			warnedAt: cleanDate(t.warnedAt),
			lastMessageAt: cleanDate(stats.lastMessageAt),
			hasModeratorMessage: stats.hasModeratorMessage,
			hasAuthorMessage: stats.hasAuthorMessage,
		});

		participantsToInsert.push({
			ticketId: t.publicId,
			actorId: subjectActorId,
			role: "subject",
			reason: "Ticket Subject",
			createdAt: cleanRequiredDate(t.createdAt),
		});

		if (openedByActorId !== subjectActorId) {
			participantsToInsert.push({
				ticketId: t.publicId,
				actorId: openedByActorId,
				role: "participant",
				reason: "Ticket Opener",
				createdAt: cleanRequiredDate(t.createdAt),
			});
		}

		if (
			claimedByActorId &&
			claimedByActorId !== subjectActorId &&
			claimedByActorId !== openedByActorId
		) {
			participantsToInsert.push({
				ticketId: t.publicId,
				actorId: claimedByActorId,
				role: "moderator",
				reason: "Claimed Ticket",
				createdAt: cleanRequiredDate(t.claimedAt, cleanRequiredDate(t.createdAt)),
			});
		}
	}

	console.log(`Inserting ${ticketsToInsert.length} tickets...`);
	const processedTicketIds = new Set<string>();
	let insertedTickets = 0;
	let skippedTickets = 0;

	for (const ticket of ticketsToInsert) {
		if (processedTicketIds.has(ticket.id)) {
			console.warn(`[DEDUPE] skipping duplicate ticket ID in memory: ${ticket.id}`);
			skippedTickets++;
			continue;
		}
		processedTicketIds.add(ticket.id);

		try {
			await newDb.db.insert(newDb.tickets).values(ticket);
			insertedTickets++;
		} catch (err: any) {
			if (isUniqueConstraintError(err)) {
				console.warn(`[WARNING] skipping ticket ID conflict in DB: ${ticket.id}`);
				skippedTickets++;
				continue;
			}
			console.error(
				`\n--- ERROR INSERTING TICKET (ID: ${ticket.id}, localId: ${ticket.localId}) ---`,
			);
			console.error(err.message || err);
			console.log("ticket payload:", JSON.stringify(ticket, null, 2));
			throw err;
		}
	}
	console.log(
		`tickets status: migrated ${insertedTickets}, skipped ${skippedTickets} duplicate/conflict rows.`,
	);

	console.log(`inserting ${participantsToInsert.length} ticket participants...`);
	let insertedParticipants = 0;
	let skippedParticipants = 0;

	for (const participant of participantsToInsert) {
		try {
			await newDb.db.insert(newDb.ticketParticipants).values(participant);
			insertedParticipants++;
		} catch (err: any) {
			if (isUniqueConstraintError(err)) {
				skippedParticipants++;
				continue;
			}
			console.error(`\n--- ERROR INSERTING PARTICIPANT (ticket ID: ${participant.ticketId}) ---`);
			console.error(err.message || err);
			console.log("participant payload:", JSON.stringify(participant, null, 2));
			throw err;
		}
	}
	console.log(
		`participants status: migrated ${insertedParticipants}, skipped ${skippedParticipants} duplicates.`,
	);

	// map & insert tickets
	const oldMessageIdToPublicId = new Map<number, string>();
	const oldMessageIdToTicketId = new Map<number, number>();
	const messagesToInsert: newDb.NewTicketMessage[] = [];

	console.log("Processing messages...");
	for (const m of allMessages) {
		const ticketPublicId = oldTicketIdToPublicId.get(m.ticketId);
		if (!ticketPublicId) continue;

		const subjectActorId = oldTicketIdToSubjectActorId.get(m.ticketId);
		const actorId = resolveActorId(m.authorId);

		oldMessageIdToPublicId.set(m.id, m.publicId);
		oldMessageIdToTicketId.set(m.id, m.ticketId);

		let actorRole: "participant" | "moderator" | "subject" | "bot" = "participant";
		if (m.authorType === "system") {
			actorRole = "bot";
		} else if (m.authorType === "staff") {
			actorRole = "moderator";
		} else if (m.authorType === "user") {
			actorRole = actorId === subjectActorId ? "subject" : "participant";
		}

		messagesToInsert.push({
			id: m.publicId,
			messageId: m.messageId,
			ticketId: ticketPublicId,
			actorId: actorId,
			actorRole: actorRole,
			isPrivate: false,
			content: m.content,
			embeds: null,
			components: null,
			createdAt: cleanRequiredDate(m.createdAt),
			editedAt: cleanDate(m.editedAt),
			deletedAt: cleanDate(m.deletedAt),
		});
	}

	console.log(`inserting ${messagesToInsert.length} messages...`);
	const processedMessageIds = new Set<string>();
	let insertedMessages = 0;
	let skippedMessages = 0;

	for (const message of messagesToInsert) {
		if (processedMessageIds.has(message.id)) {
			skippedMessages++;
			continue;
		}
		processedMessageIds.add(message.id);

		try {
			await newDb.db.insert(newDb.ticketMessages).values(message);
			insertedMessages++;
		} catch (err: any) {
			if (isUniqueConstraintError(err)) {
				skippedMessages++;
				continue;
			}
			console.error(
				`\n--- ERROR INSERTING MESSAGE (ID: ${message.id}, MessageID: ${message.messageId}) ---`,
			);
			console.error(err.message || err);
			console.log("message payload:", JSON.stringify(message, null, 2));
			throw err;
		}
	}
	console.log(
		`messages status: migrated ${insertedMessages}, skipped ${skippedMessages} duplicates.`,
	);

	const attachmentsToInsert: newDb.NewTicketAttachment[] = [];

	console.log("Processing attachments...");
	for (const a of allAttachments) {
		if (a.ownerType === "bug") continue;

		let ticketId: string | undefined;
		let messageId: string | null = null;

		if (a.ownerType === "ticket_message") {
			const mPublicId = oldMessageIdToPublicId.get(a.ownerId);
			const oldTicketId = oldMessageIdToTicketId.get(a.ownerId);
			if (mPublicId && oldTicketId) {
				messageId = mPublicId;
				ticketId = oldTicketIdToPublicId.get(oldTicketId);
			}
		} else if (a.ownerType === "ticket") {
			ticketId = oldTicketIdToPublicId.get(a.ownerId);
		}

		if (!ticketId) continue;
		const actorId = resolveActorId(a.authorId);

		attachmentsToInsert.push({
			id: a.id,
			messageId,
			ticketId,
			actorId,
			fileName: a.fileName,
			fileType: a.contentType,
			fileSize: a.size,
		});
	}

	console.log(`inserting ${attachmentsToInsert.length} attachments...`);
	const processedAttachmentIds = new Set<string>();
	let insertedAttachments = 0;
	let skippedAttachments = 0;

	for (const attachment of attachmentsToInsert) {
		if (processedAttachmentIds.has(attachment.id)) {
			skippedAttachments++;
			continue;
		}
		processedAttachmentIds.add(attachment.id);

		try {
			await newDb.db.insert(newDb.ticketAttachments).values(attachment);
			insertedAttachments++;
		} catch (err: any) {
			if (isUniqueConstraintError(err)) {
				skippedAttachments++;
				continue;
			}
			console.error(`\n--- ERROR INSERTING ATTACHMENT (ID: ${attachment.id}) ---`);
			console.error(err.message || err);
			console.log("attachment payload:", JSON.stringify(attachment, null, 2));
			throw err;
		}
	}
	console.log(
		`attachments status: migrated ${insertedAttachments}, skipped ${skippedAttachments} duplicates.`,
	);

	console.log("migration completed successfully.");
}

migrate().catch(console.error);