0000_shallow_ghost_rider.sql RawHistoryFolder

1CREATE TABLE `chat_rooms` (
2	`id` text PRIMARY KEY NOT NULL,
3	`name` text,
4	`type` text DEFAULT 'direct' NOT NULL,
5	`created_at` integer NOT NULL
6);
7--> statement-breakpoint
8CREATE TABLE `messages` (
9	`id` text PRIMARY KEY NOT NULL,
10	`chat_room_id` text NOT NULL,
11	`sender_id` text NOT NULL,
12	`content` text NOT NULL,
13	`type` text DEFAULT 'chat' NOT NULL,
14	`timestamp` integer NOT NULL,
15	FOREIGN KEY (`chat_room_id`) REFERENCES `chat_rooms`(`id`) ON UPDATE no action ON DELETE no action,
16	FOREIGN KEY (`sender_id`) REFERENCES `users`(`id`) ON UPDATE no action ON DELETE no action
17);
18--> statement-breakpoint
19CREATE TABLE `users` (
20	`id` text PRIMARY KEY NOT NULL,
21	`email` text NOT NULL,
22	`password` text NOT NULL,
23	`nickname` text NOT NULL,
24	`status` text DEFAULT 'offline',
25	`avatar_url` text,
26	`created_at` integer NOT NULL,
27	`last_seen` integer
28);
29--> statement-breakpoint
30CREATE UNIQUE INDEX `users_email_unique` ON `users` (`email`);--> statement-breakpoint
31CREATE VIEW `chat_room_view` AS select "id", "name", "type", "created_at" from "chat_rooms";--> statement-breakpoint
32CREATE VIEW `message_view` AS select "messages"."id", "messages"."content", "messages"."type", "messages"."timestamp", "users"."nickname", "chat_rooms"."name" from "messages" left join "users" on "messages"."sender_id" = "users"."id" left join "chat_rooms" on "messages"."chat_room_id" = "chat_rooms"."id";--> statement-breakpoint
33CREATE VIEW `user_view` AS select "id", "nickname", "status", "avatar_url", "last_seen" from "users";