-- Destructive rebuild: integer identity PKs replace text ULIDs.
-- Drops app tables only (keeps drizzle's migration history table).

DROP TABLE IF EXISTS "pins" CASCADE;
--> statement-breakpoint
DROP TABLE IF EXISTS "message_mentions" CASCADE;
--> statement-breakpoint
DROP TABLE IF EXISTS "reactions" CASCADE;
--> statement-breakpoint
DROP TABLE IF EXISTS "thread_subscriptions" CASCADE;
--> statement-breakpoint
DROP TABLE IF EXISTS "messages" CASCADE;
--> statement-breakpoint
DROP TABLE IF EXISTS "channel_members" CASCADE;
--> statement-breakpoint
DROP TABLE IF EXISTS "channel_seq" CASCADE;
--> statement-breakpoint
DROP TABLE IF EXISTS "channels" CASCADE;
--> statement-breakpoint
DROP TABLE IF EXISTS "auth_tokens" CASCADE;
--> statement-breakpoint
DROP TABLE IF EXISTS "notifications" CASCADE;
--> statement-breakpoint
DROP TABLE IF EXISTS "invites" CASCADE;
--> statement-breakpoint
DROP TABLE IF EXISTS "sessions" CASCADE;
--> statement-breakpoint
DROP TABLE IF EXISTS "workspace_members" CASCADE;
--> statement-breakpoint
DROP TABLE IF EXISTS "workspaces" CASCADE;
--> statement-breakpoint
DROP TABLE IF EXISTS "users" CASCADE;
--> statement-breakpoint
DROP TABLE IF EXISTS "organizations" CASCADE;
--> statement-breakpoint
DROP TYPE IF EXISTS "public"."auth_token_purpose";
--> statement-breakpoint
DROP TYPE IF EXISTS "public"."channel_type";
--> statement-breakpoint
DROP TYPE IF EXISTS "public"."role";
--> statement-breakpoint
CREATE TYPE "public"."channel_type" AS ENUM('public', 'private', 'dm', 'group_dm');
--> statement-breakpoint
CREATE TYPE "public"."role" AS ENUM('owner', 'admin', 'member', 'multi_channel_guest', 'single_channel_guest', 'bot');
--> statement-breakpoint
CREATE TYPE "public"."auth_token_purpose" AS ENUM('email_verify', 'password_reset', 'magic_link');
--> statement-breakpoint
CREATE TABLE "organizations" (
	"id" integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
	"name" text NOT NULL,
	"plan" text DEFAULT 'free' NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "users" (
	"id" integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
	"email" text NOT NULL,
	"email_verified_at" timestamp with time zone,
	"password_hash" text NOT NULL,
	"name" text NOT NULL,
	"username" text NOT NULL,
	"avatar_url" text,
	"tz" text DEFAULT 'UTC' NOT NULL,
	"mfa_secret" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "workspaces" (
	"id" integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
	"org_id" integer NOT NULL,
	"slug" text NOT NULL,
	"name" text NOT NULL,
	"icon_url" text,
	"settings" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"retention_days" integer,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "workspace_members" (
	"workspace_id" integer NOT NULL,
	"user_id" integer NOT NULL,
	"role" "role" DEFAULT 'member' NOT NULL,
	"display_name" text,
	"title" text,
	"status_text" text,
	"status_emoji" text,
	"status_expires_at" timestamp with time zone,
	"joined_at" timestamp with time zone DEFAULT now() NOT NULL,
	"deactivated_at" timestamp with time zone
);
--> statement-breakpoint
CREATE TABLE "sessions" (
	"id" integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
	"user_id" integer NOT NULL,
	"refresh_token_hash" text NOT NULL,
	"device_label" text,
	"ip" text,
	"user_agent" text,
	"rotated_from" integer,
	"revoked_at" timestamp with time zone,
	"expires_at" timestamp with time zone NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "invites" (
	"id" integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
	"workspace_id" integer NOT NULL,
	"email" text,
	"invited_by_user_id" integer,
	"token_hash" text NOT NULL,
	"role" "role" DEFAULT 'member' NOT NULL,
	"expires_at" timestamp with time zone NOT NULL,
	"used_at" timestamp with time zone,
	"max_uses" integer DEFAULT 1 NOT NULL,
	"use_count" integer DEFAULT 0 NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "notifications" (
	"id" integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
	"user_id" integer NOT NULL,
	"type" text NOT NULL,
	"title" text NOT NULL,
	"body" text NOT NULL,
	"payload" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"read_at" timestamp with time zone,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "auth_tokens" (
	"id" integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
	"user_id" integer NOT NULL,
	"purpose" "auth_token_purpose" NOT NULL,
	"token_hash" text NOT NULL,
	"expires_at" timestamp with time zone NOT NULL,
	"used_at" timestamp with time zone,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "channels" (
	"id" integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
	"workspace_id" integer NOT NULL,
	"type" "channel_type" NOT NULL,
	"name" text,
	"topic" text,
	"purpose" text,
	"created_by" integer NOT NULL,
	"is_archived" boolean DEFAULT false NOT NULL,
	"last_message_at" timestamp with time zone,
	"member_count" integer DEFAULT 0 NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "channel_seq" (
	"channel_id" integer PRIMARY KEY NOT NULL,
	"last_seq" bigint DEFAULT 0 NOT NULL
);
--> statement-breakpoint
CREATE TABLE "channel_members" (
	"channel_id" integer NOT NULL,
	"user_id" integer NOT NULL,
	"role" text DEFAULT 'member' NOT NULL,
	"joined_at" timestamp with time zone DEFAULT now() NOT NULL,
	"last_read_seq" bigint DEFAULT 0 NOT NULL,
	"last_read_at" timestamp with time zone,
	"mention_count" integer DEFAULT 0 NOT NULL,
	"notif_pref" text DEFAULT 'all' NOT NULL,
	"is_muted" boolean DEFAULT false NOT NULL,
	"is_starred" boolean DEFAULT false NOT NULL,
	"section_id" text
);
--> statement-breakpoint
CREATE TABLE "messages" (
	"id" integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
	"workspace_id" integer NOT NULL,
	"channel_id" integer NOT NULL,
	"seq" bigint NOT NULL,
	"client_msg_id" text NOT NULL,
	"author_id" integer NOT NULL,
	"type" text DEFAULT 'text' NOT NULL,
	"text" text NOT NULL,
	"blocks" jsonb NOT NULL,
	"revision" integer DEFAULT 0 NOT NULL,
	"parent_id" integer,
	"is_broadcast" boolean DEFAULT false NOT NULL,
	"thread_reply_count" integer DEFAULT 0 NOT NULL,
	"thread_last_reply_at" timestamp with time zone,
	"edited_at" timestamp with time zone,
	"deleted_at" timestamp with time zone,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "thread_subscriptions" (
	"user_id" integer NOT NULL,
	"root_message_id" integer NOT NULL,
	"channel_id" integer NOT NULL,
	"workspace_id" integer NOT NULL,
	"last_read_reply_seq" bigint DEFAULT 0 NOT NULL,
	"reason" text NOT NULL,
	"is_muted" boolean DEFAULT false NOT NULL,
	"subscribed_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "reactions" (
	"message_id" integer NOT NULL,
	"user_id" integer NOT NULL,
	"emoji" text NOT NULL
);
--> statement-breakpoint
CREATE TABLE "message_mentions" (
	"message_id" integer NOT NULL,
	"target_type" text NOT NULL,
	"target_id" integer
);
--> statement-breakpoint
CREATE TABLE "pins" (
	"channel_id" integer NOT NULL,
	"message_id" integer NOT NULL,
	"pinned_by" integer NOT NULL,
	"pinned_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE UNIQUE INDEX "workspaces_slug_idx" ON "workspaces" USING btree ("slug");
--> statement-breakpoint
CREATE UNIQUE INDEX "users_email_idx" ON "users" USING btree ("email");
--> statement-breakpoint
CREATE UNIQUE INDEX "users_username_idx" ON "users" USING btree ("username");
--> statement-breakpoint
CREATE UNIQUE INDEX "workspace_members_pk" ON "workspace_members" USING btree ("workspace_id","user_id");
--> statement-breakpoint
CREATE UNIQUE INDEX "channel_members_pk" ON "channel_members" USING btree ("channel_id","user_id");
--> statement-breakpoint
CREATE UNIQUE INDEX "messages_channel_seq_idx" ON "messages" USING btree ("channel_id","seq");
--> statement-breakpoint
CREATE UNIQUE INDEX "messages_channel_client_msg_idx" ON "messages" USING btree ("channel_id","client_msg_id");
--> statement-breakpoint
CREATE UNIQUE INDEX "thread_subscriptions_pk" ON "thread_subscriptions" USING btree ("user_id","root_message_id");
--> statement-breakpoint
CREATE UNIQUE INDEX "reactions_pk" ON "reactions" USING btree ("message_id","user_id","emoji");
--> statement-breakpoint
CREATE UNIQUE INDEX "pins_pk" ON "pins" USING btree ("channel_id","message_id");
--> statement-breakpoint
CREATE INDEX "notifications_user_id_created_at_idx" ON "notifications" USING btree ("user_id","created_at");
--> statement-breakpoint
CREATE INDEX "invites_email_idx" ON "invites" USING btree ("email");
--> statement-breakpoint
ALTER TABLE "workspaces" ADD CONSTRAINT "workspaces_org_id_organizations_id_fk" FOREIGN KEY ("org_id") REFERENCES "public"."organizations"("id");
--> statement-breakpoint
ALTER TABLE "workspace_members" ADD CONSTRAINT "workspace_members_workspace_id_workspaces_id_fk" FOREIGN KEY ("workspace_id") REFERENCES "public"."workspaces"("id");
--> statement-breakpoint
ALTER TABLE "workspace_members" ADD CONSTRAINT "workspace_members_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id");
--> statement-breakpoint
ALTER TABLE "sessions" ADD CONSTRAINT "sessions_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id");
--> statement-breakpoint
ALTER TABLE "invites" ADD CONSTRAINT "invites_workspace_id_workspaces_id_fk" FOREIGN KEY ("workspace_id") REFERENCES "public"."workspaces"("id");
--> statement-breakpoint
ALTER TABLE "invites" ADD CONSTRAINT "invites_invited_by_user_id_users_id_fk" FOREIGN KEY ("invited_by_user_id") REFERENCES "public"."users"("id") ON DELETE set null;
--> statement-breakpoint
ALTER TABLE "notifications" ADD CONSTRAINT "notifications_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade;
--> statement-breakpoint
ALTER TABLE "auth_tokens" ADD CONSTRAINT "auth_tokens_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id");
--> statement-breakpoint
ALTER TABLE "channels" ADD CONSTRAINT "channels_workspace_id_workspaces_id_fk" FOREIGN KEY ("workspace_id") REFERENCES "public"."workspaces"("id");
--> statement-breakpoint
ALTER TABLE "channels" ADD CONSTRAINT "channels_created_by_users_id_fk" FOREIGN KEY ("created_by") REFERENCES "public"."users"("id");
--> statement-breakpoint
ALTER TABLE "channel_seq" ADD CONSTRAINT "channel_seq_channel_id_channels_id_fk" FOREIGN KEY ("channel_id") REFERENCES "public"."channels"("id");
--> statement-breakpoint
ALTER TABLE "channel_members" ADD CONSTRAINT "channel_members_channel_id_channels_id_fk" FOREIGN KEY ("channel_id") REFERENCES "public"."channels"("id");
--> statement-breakpoint
ALTER TABLE "channel_members" ADD CONSTRAINT "channel_members_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id");
--> statement-breakpoint
ALTER TABLE "messages" ADD CONSTRAINT "messages_workspace_id_workspaces_id_fk" FOREIGN KEY ("workspace_id") REFERENCES "public"."workspaces"("id");
--> statement-breakpoint
ALTER TABLE "messages" ADD CONSTRAINT "messages_channel_id_channels_id_fk" FOREIGN KEY ("channel_id") REFERENCES "public"."channels"("id");
--> statement-breakpoint
ALTER TABLE "messages" ADD CONSTRAINT "messages_author_id_users_id_fk" FOREIGN KEY ("author_id") REFERENCES "public"."users"("id");
--> statement-breakpoint
ALTER TABLE "thread_subscriptions" ADD CONSTRAINT "thread_subscriptions_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id");
--> statement-breakpoint
ALTER TABLE "thread_subscriptions" ADD CONSTRAINT "thread_subscriptions_root_message_id_messages_id_fk" FOREIGN KEY ("root_message_id") REFERENCES "public"."messages"("id");
--> statement-breakpoint
ALTER TABLE "thread_subscriptions" ADD CONSTRAINT "thread_subscriptions_channel_id_channels_id_fk" FOREIGN KEY ("channel_id") REFERENCES "public"."channels"("id");
--> statement-breakpoint
ALTER TABLE "thread_subscriptions" ADD CONSTRAINT "thread_subscriptions_workspace_id_workspaces_id_fk" FOREIGN KEY ("workspace_id") REFERENCES "public"."workspaces"("id");
--> statement-breakpoint
ALTER TABLE "reactions" ADD CONSTRAINT "reactions_message_id_messages_id_fk" FOREIGN KEY ("message_id") REFERENCES "public"."messages"("id");
--> statement-breakpoint
ALTER TABLE "reactions" ADD CONSTRAINT "reactions_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id");
--> statement-breakpoint
ALTER TABLE "message_mentions" ADD CONSTRAINT "message_mentions_message_id_messages_id_fk" FOREIGN KEY ("message_id") REFERENCES "public"."messages"("id");
--> statement-breakpoint
ALTER TABLE "pins" ADD CONSTRAINT "pins_channel_id_channels_id_fk" FOREIGN KEY ("channel_id") REFERENCES "public"."channels"("id");
--> statement-breakpoint
ALTER TABLE "pins" ADD CONSTRAINT "pins_message_id_messages_id_fk" FOREIGN KEY ("message_id") REFERENCES "public"."messages"("id");
--> statement-breakpoint
ALTER TABLE "pins" ADD CONSTRAINT "pins_pinned_by_users_id_fk" FOREIGN KEY ("pinned_by") REFERENCES "public"."users"("id");
