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

export const tickets = s.sqliteTable("tickets", {
	id: s.integer("id").primaryKey({ autoIncrement: true }),
	anonymousId: s.text("anonymous_id").notNull(),

	status: s.text("status").notNull(),
	channelId: s.text("channel_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" }),

	/** 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" }),
});

export const ticketParticipants = s.sqliteTable(
	"ticket_participants",
	{
		id: s.integer("id").primaryKey({ autoIncrement: true }),
		ticketId: s
			.integer("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
			.integer("ticket_id")
			.notNull()
			.references(() => tickets.id, { onDelete: "cascade" }),
		actorId: s
			.integer("actor_id")
			.notNull()
			.references(() => actors.id, { onDelete: "cascade" }),

		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
			.integer("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),
	],
);