CREATE TABLE IF NOT EXISTS "chat_threads" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "title" text,
  "kind" text DEFAULT 'dm' NOT NULL,
  "created_by" uuid NOT NULL,
  "created_at" timestamp with time zone DEFAULT now() NOT NULL,
  "last_message_at" timestamp with time zone DEFAULT now() NOT NULL,
  "last_message_preview" varchar(200),
  CONSTRAINT "chat_threads_created_by_users_id_fk"
    FOREIGN KEY ("created_by") REFERENCES "public"."users"("id") ON DELETE cascade
);
CREATE INDEX IF NOT EXISTS "chat_threads_last_message_idx" ON "chat_threads" ("last_message_at");

CREATE TABLE IF NOT EXISTS "chat_thread_participants" (
  "thread_id" uuid NOT NULL,
  "user_id" uuid NOT NULL,
  "joined_at" timestamp with time zone DEFAULT now() NOT NULL,
  "last_read_at" timestamp with time zone,
  CONSTRAINT "chat_thread_participants_thread_id_user_id_pk" PRIMARY KEY ("thread_id","user_id"),
  CONSTRAINT "chat_thread_participants_thread_id_chat_threads_id_fk"
    FOREIGN KEY ("thread_id") REFERENCES "public"."chat_threads"("id") ON DELETE cascade,
  CONSTRAINT "chat_thread_participants_user_id_users_id_fk"
    FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade
);
CREATE INDEX IF NOT EXISTS "chat_thread_participants_user_idx" ON "chat_thread_participants" ("user_id");

CREATE TABLE IF NOT EXISTS "chat_messages" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "thread_id" uuid NOT NULL,
  "sender_user_id" uuid NOT NULL,
  "body" text NOT NULL,
  "created_at" timestamp with time zone DEFAULT now() NOT NULL,
  CONSTRAINT "chat_messages_thread_id_chat_threads_id_fk"
    FOREIGN KEY ("thread_id") REFERENCES "public"."chat_threads"("id") ON DELETE cascade,
  CONSTRAINT "chat_messages_sender_user_id_users_id_fk"
    FOREIGN KEY ("sender_user_id") REFERENCES "public"."users"("id") ON DELETE cascade
);
CREATE INDEX IF NOT EXISTS "chat_messages_thread_created_idx" ON "chat_messages" ("thread_id","created_at");
ALTER TABLE "chat_threads" ADD COLUMN IF NOT EXISTS "last_message_preview" varchar(200);
