CREATE TABLE `guild` (
	`guild_id` text PRIMARY KEY NOT NULL,
	`suggestion_forum_id` text,
	`bug_channel_id` text,
	`highlights_channel_id` text,
	`commands_channel_id` text,
	`ticket_channel_id` text,
	`ticket_category_id` text,
	`ticket_message` text
);
--> statement-breakpoint
CREATE TABLE `manager` (
	`role_id` text PRIMARY KEY NOT NULL,
	`guild_id` text NOT NULL
);
--> statement-breakpoint
CREATE TABLE `ticket_attachments` (
	`id` text PRIMARY KEY NOT NULL,
	`ticket_id` integer NOT NULL,
	`message_id` integer,
	`file_name` text NOT NULL,
	`content_type` text NOT NULL,
	`size` integer NOT NULL,
	`bucket` text NOT NULL,
	`key` text NOT NULL,
	`url` text NOT NULL,
	`uploaded_at` integer DEFAULT (unixepoch()) NOT NULL,
	FOREIGN KEY (`ticket_id`) REFERENCES `tickets`(`id`) ON UPDATE no action ON DELETE cascade,
	FOREIGN KEY (`message_id`) REFERENCES `ticket_messages`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE TABLE `ticket_messages` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`ticket_id` integer NOT NULL,
	`author_id` text NOT NULL,
	`author_type` text DEFAULT 'user' NOT NULL,
	`content` text NOT NULL,
	`created_at` integer DEFAULT (unixepoch()) NOT NULL,
	FOREIGN KEY (`ticket_id`) REFERENCES `tickets`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE INDEX `ticket_msgs_ticket_idx` ON `ticket_messages` (`ticket_id`);--> statement-breakpoint
CREATE TABLE `tickets` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`guild_id` text NOT NULL,
	`channel_id` text,
	`author_id` text NOT NULL,
	`anonymous_id` text NOT NULL,
	`type` text NOT NULL,
	`status` text DEFAULT 'open' NOT NULL,
	`topic` text,
	`created_at` integer DEFAULT (unixepoch()) NOT NULL,
	`closed_at` integer
);
--> statement-breakpoint
CREATE INDEX `tickets_guild_idx` ON `tickets` (`guild_id`);--> statement-breakpoint
CREATE INDEX `tickets_author_idx` ON `tickets` (`author_id`);--> statement-breakpoint
CREATE INDEX `tickets_status_idx` ON `tickets` (`status`);