1 files changed, 61 insertions, 0 deletions
diff --git a/api/src/db/migrations/0000_huge_luke_cage.sql b/api/src/db/migrations/0000_huge_luke_cage.sql
new file mode 100644
index 0000000..1abb4d2
--- /dev/null
+++ b/api/src/db/migrations/0000_huge_luke_cage.sql
@@ -0,0 +1,61 @@
+CREATE TABLE `categories` (
+	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
+	`name` text NOT NULL,
+	`slug` text NOT NULL,
+	`description` text,
+	`created_at` text DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')) NOT NULL,
+	`updated_at` text DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')) NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE `comments` (
+	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
+	`content` text NOT NULL,
+	`is_internal` integer DEFAULT false NOT NULL,
+	`ticket_id` integer NOT NULL,
+	`author_id` integer NOT NULL,
+	`created_at` text DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')) NOT NULL,
+	`updated_at` text DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')) NOT NULL,
+	FOREIGN KEY (`ticket_id`) REFERENCES `tickets`(`id`) ON UPDATE no action ON DELETE cascade,
+	FOREIGN KEY (`author_id`) REFERENCES `users`(`id`) ON UPDATE no action ON DELETE restrict
+);
+--> statement-breakpoint
+CREATE TABLE `tickets` (
+	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
+	`title` text NOT NULL,
+	`description` text NOT NULL,
+	`status` text DEFAULT 'open' NOT NULL,
+	`priority` text DEFAULT 'medium' NOT NULL,
+	`creator_id` integer NOT NULL,
+	`assignee_id` integer,
+	`category_id` integer,
+	`due_at` text,
+	`resolved_at` text,
+	`created_at` text DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')) NOT NULL,
+	`updated_at` text DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')) NOT NULL,
+	FOREIGN KEY (`creator_id`) REFERENCES `users`(`id`) ON UPDATE no action ON DELETE restrict,
+	FOREIGN KEY (`assignee_id`) REFERENCES `users`(`id`) ON UPDATE no action ON DELETE set null,
+	FOREIGN KEY (`category_id`) REFERENCES `categories`(`id`) ON UPDATE no action ON DELETE set null
+);
+--> statement-breakpoint
+CREATE TABLE `users` (
+	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
+	`name` text NOT NULL,
+	`email` text NOT NULL,
+	`password` text NOT NULL,
+	`role` text DEFAULT 'requester' NOT NULL,
+	`is_active` integer DEFAULT true NOT NULL,
+	`created_at` text DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')) NOT NULL,
+	`updated_at` text DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')) NOT NULL
+);
+--> statement-breakpoint
+CREATE UNIQUE INDEX `categories_slug_unique_idx` ON `categories` (`slug`);--> statement-breakpoint
+CREATE INDEX `comments_ticket_id_idx` ON `comments` (`ticket_id`);--> statement-breakpoint
+CREATE INDEX `comments_author_id_idx` ON `comments` (`author_id`);--> statement-breakpoint
+CREATE INDEX `tickets_status_idx` ON `tickets` (`status`);--> statement-breakpoint
+CREATE INDEX `tickets_priority_idx` ON `tickets` (`priority`);--> statement-breakpoint
+CREATE INDEX `tickets_assignee_id_idx` ON `tickets` (`assignee_id`);--> statement-breakpoint
+CREATE INDEX `tickets_creator_id_idx` ON `tickets` (`creator_id`);--> statement-breakpoint
+CREATE INDEX `tickets_category_id_idx` ON `tickets` (`category_id`);--> statement-breakpoint
+CREATE INDEX `tickets_status_assignee_idx` ON `tickets` (`status`,`assignee_id`);--> statement-breakpoint
+CREATE UNIQUE INDEX `users_email_unique_idx` ON `users` (`email`);--> statement-breakpoint
+CREATE INDEX `users_role_idx` ON `users` (`role`);
+\ No newline at end of file