CREATE TABLE `sessions` (
	`id` text PRIMARY KEY,
	`user_id` integer NOT NULL,
	`access_token` text NOT NULL,
	`refresh_token` text NOT NULL,
	`scope` text,
	`token_type` text,
	`expires_at` integer NOT NULL,
	`created_at` integer NOT NULL,
	CONSTRAINT `fk_sessions_user_id_users_id_fk` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
);
--> statement-breakpoint
CREATE TABLE `users` (
	`id` integer PRIMARY KEY AUTOINCREMENT,
	`discord_id` text UNIQUE,
	`display_name` text,
	`username` text,
	`avatar` text,
	`created_at` integer NOT NULL,
	`updated_at` integer NOT NULL
);
--> statement-breakpoint
CREATE TABLE `actors` (
	`id` integer PRIMARY KEY AUTOINCREMENT,
	`guild_id` integer NOT NULL,
	`user_id` integer NOT NULL,
	`discord_id` text,
	`display_name` text,
	`username` text,
	`avatar` text,
	`moderator` integer,
	`created_at` integer NOT NULL,
	`updated_at` integer NOT NULL,
	`deleted_at` integer,
	CONSTRAINT `fk_actors_guild_id_guilds_id_fk` FOREIGN KEY (`guild_id`) REFERENCES `guilds`(`id`) ON DELETE CASCADE,
	CONSTRAINT `fk_actors_user_id_users_id_fk` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
);
--> statement-breakpoint
CREATE TABLE `guilds` (
	`id` integer PRIMARY KEY AUTOINCREMENT,
	`discord_id` text NOT NULL UNIQUE,
	`name` text NOT NULL,
	`icon` text,
	`ticket_category` text,
	`ticket_log_channel` text,
	`ticket_greeting` text,
	`ticket_prompt_channel` text,
	`ticket_prompt` text,
	`created_at` integer NOT NULL,
	`updated_at` integer NOT NULL
);
--> statement-breakpoint
CREATE TABLE `ticket_attachments` (
	`id` integer PRIMARY KEY AUTOINCREMENT,
	`public_id` text NOT NULL,
	`message_id` integer,
	`ticket_id` text NOT NULL,
	`actor_id` integer NOT NULL,
	`file_name` text NOT NULL,
	`file_type` text NOT NULL,
	`file_size` integer NOT NULL,
	CONSTRAINT `fk_ticket_attachments_message_id_ticket_messages_id_fk` FOREIGN KEY (`message_id`) REFERENCES `ticket_messages`(`id`) ON DELETE CASCADE,
	CONSTRAINT `fk_ticket_attachments_ticket_id_tickets_id_fk` FOREIGN KEY (`ticket_id`) REFERENCES `tickets`(`id`) ON DELETE CASCADE,
	CONSTRAINT `fk_ticket_attachments_actor_id_actors_id_fk` FOREIGN KEY (`actor_id`) REFERENCES `actors`(`id`) ON DELETE CASCADE
);
--> statement-breakpoint
CREATE TABLE `ticket_messages` (
	`id` integer PRIMARY KEY AUTOINCREMENT,
	`public_id` text NOT NULL,
	`message_id` text,
	`ticket_id` text NOT NULL,
	`actor_id` integer NOT NULL,
	`content` text NOT NULL,
	`actor_role` text NOT NULL,
	`created_at` integer NOT NULL,
	`edited_at` integer,
	`deleted_at` integer,
	CONSTRAINT `fk_ticket_messages_ticket_id_tickets_id_fk` FOREIGN KEY (`ticket_id`) REFERENCES `tickets`(`id`) ON DELETE CASCADE,
	CONSTRAINT `fk_ticket_messages_actor_id_actors_id_fk` FOREIGN KEY (`actor_id`) REFERENCES `actors`(`id`) ON DELETE CASCADE
);
--> statement-breakpoint
CREATE TABLE `ticket_participants` (
	`id` integer PRIMARY KEY AUTOINCREMENT,
	`ticket_id` text NOT NULL,
	`actor_id` integer NOT NULL,
	`role` text NOT NULL,
	`reason` text,
	`created_at` integer NOT NULL,
	CONSTRAINT `fk_ticket_participants_ticket_id_tickets_id_fk` FOREIGN KEY (`ticket_id`) REFERENCES `tickets`(`id`) ON DELETE CASCADE,
	CONSTRAINT `fk_ticket_participants_actor_id_actors_id_fk` FOREIGN KEY (`actor_id`) REFERENCES `actors`(`id`) ON DELETE CASCADE
);
--> statement-breakpoint
CREATE TABLE `tickets` (
	`id` text PRIMARY KEY,
	`guild_id` integer NOT NULL,
	`local_id` integer NOT NULL,
	`status` text NOT NULL,
	`channel_id` text,
	`close_reason` text,
	`private_reason` text,
	`opened_at` integer NOT NULL,
	`closed_at` integer,
	`opened_by` integer NOT NULL,
	`open_reason` text,
	`subject` integer NOT NULL,
	`claimed_by` integer,
	`claimed_at` integer,
	`thanked_at` integer,
	`warned_at` integer,
	`last_message_at` integer,
	`has_moderator_message` integer,
	`has_author_message` integer,
	CONSTRAINT `fk_tickets_guild_id_guilds_id_fk` FOREIGN KEY (`guild_id`) REFERENCES `guilds`(`id`) ON DELETE CASCADE,
	CONSTRAINT `fk_tickets_opened_by_actors_id_fk` FOREIGN KEY (`opened_by`) REFERENCES `actors`(`id`),
	CONSTRAINT `fk_tickets_subject_actors_id_fk` FOREIGN KEY (`subject`) REFERENCES `actors`(`id`),
	CONSTRAINT `fk_tickets_claimed_by_actors_id_fk` FOREIGN KEY (`claimed_by`) REFERENCES `actors`(`id`)
);
--> statement-breakpoint
CREATE UNIQUE INDEX `actors_guild_user_idx` ON `actors` (`guild_id`,`user_id`);--> statement-breakpoint
CREATE INDEX `ticket_attachments_ticket_idx` ON `ticket_attachments` (`ticket_id`);--> statement-breakpoint
CREATE INDEX `ticket_attachments_actor_idx` ON `ticket_attachments` (`actor_id`);--> statement-breakpoint
CREATE INDEX `ticket_attachments_public_id_idx` ON `ticket_attachments` (`public_id`);--> statement-breakpoint
CREATE INDEX `ticket_messages_ticket_idx` ON `ticket_messages` (`ticket_id`);--> statement-breakpoint
CREATE INDEX `ticket_messages_actor_idx` ON `ticket_messages` (`actor_id`);--> statement-breakpoint
CREATE INDEX `ticket_messages_public_id_idx` ON `ticket_messages` (`public_id`);--> statement-breakpoint
CREATE INDEX `ticket_participants_ticket_idx` ON `ticket_participants` (`ticket_id`);--> statement-breakpoint
CREATE INDEX `ticket_participants_actor_idx` ON `ticket_participants` (`actor_id`);--> statement-breakpoint
CREATE INDEX `ticket_participants_role_idx` ON `ticket_participants` (`role`);--> statement-breakpoint
CREATE UNIQUE INDEX `tickets_guild_local_idx` ON `tickets` (`guild_id`,`local_id`);