import { defineRelations, sql } from "drizzle-orm";
import { index, integer, sqliteTable, text } from "drizzle-orm/sqlite-core";

export const tickets = sqliteTable(
	"tickets",
	{
		id: integer("id").primaryKey({ autoIncrement: true }),
		guildId: text("guild_id").notNull(),
		ticketNumber: integer("ticket_number"),

		channelId: text("channel_id"),

		authorId: text("author_id").notNull(),
		anonymousId: text("anonymous_id").notNull(),
		claimedBy: text("claimed_by"),
		claimedAt: integer("claimed_at", { mode: "timestamp" }),

		addedUsers: text("added_users", { mode: "json" }).$type<string[]>().default([]).notNull(),

		type: text("type").notNull(),
		status: text("status", { enum: ["open", "closed", "archived"] })
			.default("open")
			.notNull(),

		topic: text("topic"),
		openedBy: text("opened_by"),

		createdAt: integer("created_at", { mode: "timestamp" })
			.default(sql`(unixepoch())`)
			.notNull(),
		closedAt: integer("closed_at", { mode: "timestamp" }),
		closedBy: text("closed_by"),

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

		thankedAt: integer("thanked_at", {
			mode: "timestamp",
		}).$type<Date | null>(),
		warnedAt: integer("warned_at", { mode: "timestamp" }).$type<Date | null>(),
	},
	(t) => [
		index("tickets_guild_idx").on(t.guildId),
		index("tickets_author_idx").on(t.authorId),
		index("tickets_status_idx").on(t.status),
	],
);

export const ticketMessages = sqliteTable(
	"ticket_messages",
	{
		id: integer("id").primaryKey({ autoIncrement: true }),
		ticketId: integer("ticket_id")
			.references(() => tickets.id, { onDelete: "cascade" })
			.notNull(),

		messageId: text("message_id"),
		authorId: text("author_id").notNull(),

		authorType: text("author_type", { enum: ["user", "staff", "system"] })
			.default("user")
			.notNull(),

		content: text("content").notNull(),

		createdAt: integer("created_at", { mode: "timestamp" })
			.default(sql`(unixepoch())`)
			.notNull(),
		editedAt: integer("edited_at", { mode: "timestamp" }).$type<Date | null>(),
		deletedAt: integer("deleted_at", {
			mode: "timestamp",
		}).$type<Date | null>(),
	},
	(t) => [index("ticket_msgs_ticket_idx").on(t.ticketId)],
);

export const ticketAccessLogs = sqliteTable(
	"ticket_access_logs",
	{
		id: integer("id").primaryKey({ autoIncrement: true }),
		ticketId: integer("ticket_id")
			.references(() => tickets.id, { onDelete: "cascade" })
			.notNull(),

		userId: text("user_id").notNull(),
		action: text("action", { enum: ["view_transcript"] }).notNull(),

		createdAt: integer("created_at", { mode: "timestamp" })
			.default(sql`(unixepoch())`)
			.notNull(),
	},
	(t) => [index("ticket_access_logs_ticket_idx").on(t.ticketId)],
);

export const TicketRelations = defineRelations(
	{ tickets, ticketMessages, ticketAccessLogs },
	(r) => ({
		tickets: {
			messages: r.many.ticketMessages({
				from: r.tickets.id,
				to: r.ticketMessages.ticketId,
			}),
			accessLogs: r.many.ticketAccessLogs({
				from: r.tickets.id,
				to: r.ticketAccessLogs.ticketId,
			}),
		},
		ticketMessages: {
			ticket: r.one.tickets({
				from: r.ticketMessages.ticketId,
				to: r.tickets.id,
			}),
		},
		ticketAccessLogs: {
			ticket: r.one.tickets({
				from: r.ticketAccessLogs.ticketId,
				to: r.tickets.id,
			}),
		},
	}),
);

export type Ticket = typeof tickets.$inferSelect;
export type TicketMessage = typeof ticketMessages.$inferSelect;