import * as s from "drizzle-orm/sqlite-core";
import { actors } from "./actors";
import { guilds } from "./guilds";

export const tickets = s.sqliteTable(
	"tickets",
	{
		id: s.text("id").primaryKey(),
		guildId: s
			.integer("guild_id")
			.notNull()
			.references(() => guilds.id, { onDelete: "cascade" }),
		localId: s.integer("local_id").notNull(),

		status: s.text("status").notNull(),
		channelId: s.text("channel_id"),
		threadId: s.text("thread_id"),

		closeReason: s.text("close_reason"),
		privateReason: s.text("private_reason"),

		openedAt: s.integer("opened_at", { mode: "timestamp" }).notNull(),
		closedAt: s.integer("closed_at", { mode: "timestamp" }),
		closedBy: s.integer("closed_by").references(() => actors.id),

		/** the actor that opened the ticket, may be the same as the subject */
		openedBy: s
			.integer("opened_by")
			.notNull()
			.references(() => actors.id),
		openReason: s.text("open_reason"),

		/** the actor who is the subject of the ticket */
		subject: s
			.integer("subject")
			.notNull()
			.references(() => actors.id),

		claimedBy: s.integer("claimed_by").references(() => actors.id),
		claimedAt: s.integer("claimed_at", { mode: "timestamp" }),

		thankedAt: s.integer("thanked_at", { mode: "timestamp" }),
		warnedAt: s.integer("warned_at", { mode: "timestamp" }),
		lastMessageAt: s.integer("last_message_at", { mode: "timestamp" }),

		hasModeratorMessage: s.integer("has_moderator_message", { mode: "boolean" }),
		hasAuthorMessage: s.integer("has_author_message", { mode: "boolean" }),
	},
	(table) => [s.uniqueIndex("tickets_guild_local_idx").on(table.guildId, table.localId)],
);

export const ticketParticipants = s.sqliteTable(
	"ticket_participants",
	{
		id: s.integer("id").primaryKey({ autoIncrement: true }),
		ticketId: s
			.text("ticket_id")
			.notNull()
			.references(() => tickets.id, { onDelete: "cascade" }),
		actorId: s
			.integer("actor_id")
			.notNull()
			.references(() => actors.id, { onDelete: "cascade" }),

		role: s.text("role", { enum: ["participant", "moderator", "subject"] }).notNull(),
		reason: s.text("reason"),

		createdAt: s.integer("created_at", { mode: "timestamp" }).notNull(),
	},
	(table) => [
		s.index("ticket_participants_ticket_idx").on(table.ticketId),
		s.index("ticket_participants_actor_idx").on(table.actorId),
		s.index("ticket_participants_role_idx").on(table.role),
	],
);

export const ticketMessages = s.sqliteTable(
	"ticket_messages",
	{
		id: s.integer("id").primaryKey({ autoIncrement: true }),
		publicId: s.text("public_id").notNull(),
		messageId: s.text("message_id"),

		ticketId: s
			.text("ticket_id")
			.notNull()
			.references(() => tickets.id, { onDelete: "cascade" }),
		actorId: s
			.integer("actor_id")
			.notNull()
			.references(() => actors.id, { onDelete: "cascade" }),

		isPrivate: s.integer("is_private", { mode: "boolean" }).notNull().default(false),
		content: s.text("content").notNull(),
		actorRole: s.text("actor_role", { enum: ["participant", "moderator", "subject"] }).notNull(),

		createdAt: s.integer("created_at", { mode: "timestamp" }).notNull(),
		editedAt: s.integer("edited_at", { mode: "timestamp" }),
		deletedAt: s.integer("deleted_at", { mode: "timestamp" }),
	},
	(table) => [
		s.index("ticket_messages_ticket_idx").on(table.ticketId),
		s.index("ticket_messages_actor_idx").on(table.actorId),
		s.index("ticket_messages_public_id_idx").on(table.publicId),
	],
);

export const ticketAttachments = s.sqliteTable(
	"ticket_attachments",
	{
		id: s.integer("id").primaryKey({ autoIncrement: true }),
		publicId: s.text("public_id").notNull(),
		messageId: s.integer("message_id").references(() => ticketMessages.id, { onDelete: "cascade" }),
		ticketId: s
			.text("ticket_id")
			.notNull()
			.references(() => tickets.id, { onDelete: "cascade" }),
		actorId: s
			.integer("actor_id")
			.notNull()
			.references(() => actors.id, { onDelete: "cascade" }),

		fileName: s.text("file_name").notNull(),
		fileType: s.text("file_type").notNull(),
		fileSize: s.integer("file_size").notNull(),
	},
	(table) => [
		s.index("ticket_attachments_ticket_idx").on(table.ticketId),
		s.index("ticket_attachments_actor_idx").on(table.actorId),
		s.index("ticket_attachments_public_id_idx").on(table.publicId),
	],
);

export const ticketComments = s.sqliteTable(
	"ticket_comments",
	{
		id: s.text("id").primaryKey(),
		ticketId: s
			.text("ticket_id")
			.notNull()
			.references(() => tickets.id, { onDelete: "cascade" }),
		actorId: s
			.integer("actor_id")
			.notNull()
			.references(() => actors.id, { onDelete: "cascade" }),
		content: s.text("content").notNull(),
		createdAt: s.integer("created_at", { mode: "timestamp" }).notNull(),
		updatedAt: s.integer("updated_at", { mode: "timestamp" }).notNull(),
	},
	(table) => [
		s.index("ticket_comments_ticket_idx").on(table.ticketId),
		s.index("ticket_comments_actor_idx").on(table.actorId),
	],
);

export type Ticket = typeof tickets.$inferSelect;
export type NewTicket = typeof tickets.$inferInsert;

export type TicketParticipant = typeof ticketParticipants.$inferSelect;
export type NewTicketParticipant = typeof ticketParticipants.$inferInsert;

export type TicketMessage = typeof ticketMessages.$inferSelect;
export type NewTicketMessage = typeof ticketMessages.$inferInsert;

export type TicketAttachment = typeof ticketAttachments.$inferSelect;
export type NewTicketAttachment = typeof ticketAttachments.$inferInsert;

export type TicketComment = typeof ticketComments.$inferSelect;
export type NewTicketComment = typeof ticketComments.$inferInsert;