CREATE TABLE `backfill_errors` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`backfill_id` integer NOT NULL,
	`did` text NOT NULL,
	`collection` text NOT NULL,
	`error_message` text NOT NULL,
	`created_at` integer NOT NULL,
	FOREIGN KEY (`backfill_id`) REFERENCES `backfill_progress`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE INDEX `backfill_errors_backfill_id_idx` ON `backfill_errors` (`backfill_id`);--> statement-breakpoint
CREATE TABLE `backfill_progress` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`status` text NOT NULL,
	`backfill_type` text NOT NULL,
	`last_processed_did` text,
	`dids_total` integer DEFAULT 0 NOT NULL,
	`dids_processed` integer DEFAULT 0 NOT NULL,
	`records_indexed` integer DEFAULT 0 NOT NULL,
	`started_at` integer NOT NULL,
	`completed_at` integer,
	`error_message` text
);
--> statement-breakpoint
CREATE TABLE `boards` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`did` text NOT NULL,
	`rkey` text NOT NULL,
	`cid` text NOT NULL,
	`name` text NOT NULL,
	`description` text,
	`slug` text,
	`sort_order` integer,
	`category_id` integer,
	`category_uri` text NOT NULL,
	`created_at` integer NOT NULL,
	`indexed_at` integer NOT NULL,
	FOREIGN KEY (`category_id`) REFERENCES `categories`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE UNIQUE INDEX `boards_did_rkey_idx` ON `boards` (`did`,`rkey`);--> statement-breakpoint
CREATE INDEX `boards_category_id_idx` ON `boards` (`category_id`);--> statement-breakpoint
CREATE TABLE `categories` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`did` text NOT NULL,
	`rkey` text NOT NULL,
	`cid` text NOT NULL,
	`name` text NOT NULL,
	`description` text,
	`slug` text,
	`sort_order` integer,
	`forum_id` integer,
	`created_at` integer NOT NULL,
	`indexed_at` integer NOT NULL,
	FOREIGN KEY (`forum_id`) REFERENCES `forums`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE UNIQUE INDEX `categories_did_rkey_idx` ON `categories` (`did`,`rkey`);--> statement-breakpoint
CREATE TABLE `firehose_cursor` (
	`service` text PRIMARY KEY DEFAULT 'jetstream' NOT NULL,
	`cursor` integer NOT NULL,
	`updated_at` integer NOT NULL
);
--> statement-breakpoint
CREATE TABLE `forums` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`did` text NOT NULL,
	`rkey` text NOT NULL,
	`cid` text NOT NULL,
	`name` text NOT NULL,
	`description` text,
	`indexed_at` integer NOT NULL
);
--> statement-breakpoint
CREATE UNIQUE INDEX `forums_did_rkey_idx` ON `forums` (`did`,`rkey`);--> statement-breakpoint
CREATE TABLE `memberships` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`did` text NOT NULL,
	`rkey` text NOT NULL,
	`cid` text NOT NULL,
	`forum_id` integer,
	`forum_uri` text NOT NULL,
	`role` text,
	`role_uri` text,
	`joined_at` integer,
	`created_at` integer NOT NULL,
	`indexed_at` integer NOT NULL,
	FOREIGN KEY (`did`) REFERENCES `users`(`did`) ON UPDATE no action ON DELETE no action,
	FOREIGN KEY (`forum_id`) REFERENCES `forums`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE UNIQUE INDEX `memberships_did_rkey_idx` ON `memberships` (`did`,`rkey`);--> statement-breakpoint
CREATE INDEX `memberships_did_idx` ON `memberships` (`did`);--> statement-breakpoint
CREATE TABLE `mod_actions` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`did` text NOT NULL,
	`rkey` text NOT NULL,
	`cid` text NOT NULL,
	`action` text NOT NULL,
	`subject_did` text,
	`subject_post_uri` text,
	`forum_id` integer,
	`reason` text,
	`created_by` text NOT NULL,
	`expires_at` integer,
	`created_at` integer NOT NULL,
	`indexed_at` integer NOT NULL,
	FOREIGN KEY (`forum_id`) REFERENCES `forums`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE UNIQUE INDEX `mod_actions_did_rkey_idx` ON `mod_actions` (`did`,`rkey`);--> statement-breakpoint
CREATE INDEX `mod_actions_subject_did_idx` ON `mod_actions` (`subject_did`);--> statement-breakpoint
CREATE INDEX `mod_actions_subject_post_uri_idx` ON `mod_actions` (`subject_post_uri`);--> statement-breakpoint
CREATE TABLE `posts` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`did` text NOT NULL,
	`rkey` text NOT NULL,
	`cid` text NOT NULL,
	`title` text,
	`text` text NOT NULL,
	`forum_uri` text,
	`board_uri` text,
	`board_id` integer,
	`root_post_id` integer,
	`parent_post_id` integer,
	`root_uri` text,
	`parent_uri` text,
	`created_at` integer NOT NULL,
	`indexed_at` integer NOT NULL,
	`banned_by_mod` integer DEFAULT false NOT NULL,
	`deleted_by_user` integer DEFAULT false NOT NULL,
	FOREIGN KEY (`did`) REFERENCES `users`(`did`) ON UPDATE no action ON DELETE no action,
	FOREIGN KEY (`board_id`) REFERENCES `boards`(`id`) ON UPDATE no action ON DELETE no action,
	FOREIGN KEY (`root_post_id`) REFERENCES `posts`(`id`) ON UPDATE no action ON DELETE no action,
	FOREIGN KEY (`parent_post_id`) REFERENCES `posts`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE UNIQUE INDEX `posts_did_rkey_idx` ON `posts` (`did`,`rkey`);--> statement-breakpoint
CREATE INDEX `posts_forum_uri_idx` ON `posts` (`forum_uri`);--> statement-breakpoint
CREATE INDEX `posts_board_id_idx` ON `posts` (`board_id`);--> statement-breakpoint
CREATE INDEX `posts_board_uri_idx` ON `posts` (`board_uri`);--> statement-breakpoint
CREATE INDEX `posts_root_post_id_idx` ON `posts` (`root_post_id`);--> statement-breakpoint
CREATE TABLE `role_permissions` (
	`role_id` integer NOT NULL,
	`permission` text NOT NULL,
	PRIMARY KEY(`role_id`, `permission`),
	FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE TABLE `roles` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`did` text NOT NULL,
	`rkey` text NOT NULL,
	`cid` text NOT NULL,
	`name` text NOT NULL,
	`description` text,
	`priority` integer NOT NULL,
	`created_at` integer NOT NULL,
	`indexed_at` integer NOT NULL
);
--> statement-breakpoint
CREATE UNIQUE INDEX `roles_did_rkey_idx` ON `roles` (`did`,`rkey`);--> statement-breakpoint
CREATE INDEX `roles_did_idx` ON `roles` (`did`);--> statement-breakpoint
CREATE INDEX `roles_did_name_idx` ON `roles` (`did`,`name`);--> statement-breakpoint
CREATE TABLE `users` (
	`did` text PRIMARY KEY NOT NULL,
	`handle` text,
	`indexed_at` integer NOT NULL
);