CREATE TYPE "ProfileType" AS ENUM ('PERSONAL', 'PROFESSIONAL');
CREATE TYPE "SocialProfileVisibility" AS ENUM ('PUBLIC', 'PRIVATE');
CREATE TYPE "SocialFollowStatus" AS ENUM ('PENDING', 'ACCEPTED');
CREATE TYPE "SocialPostVisibility" AS ENUM ('PUBLIC', 'FOLLOWERS');
CREATE TYPE "SocialPostStatus" AS ENUM ('PUBLISHED', 'ARCHIVED', 'DELETED');
CREATE TYPE "ConversationType" AS ENUM ('DIRECT', 'GROUP');
CREATE TYPE "MessageType" AS ENUM ('TEXT', 'SYSTEM');

ALTER TABLE "identity_users"
  ADD COLUMN "username" VARCHAR(30),
  ADD COLUMN "profile_type" "ProfileType" NOT NULL DEFAULT 'PERSONAL';

CREATE UNIQUE INDEX "identity_users_username_key" ON "identity_users"("username");
ALTER TABLE "identity_users"
  ADD CONSTRAINT "identity_users_username_format"
  CHECK ("username" IS NULL OR "username" ~ '^[a-z][a-z0-9_]{2,29}$');

CREATE TABLE "social_profiles" (
  "user_id" UUID NOT NULL,
  "bio" VARCHAR(300),
  "website" VARCHAR(500),
  "visibility" "SocialProfileVisibility" NOT NULL DEFAULT 'PUBLIC',
  "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updated_at" TIMESTAMPTZ(3) NOT NULL,
  CONSTRAINT "social_profiles_pkey" PRIMARY KEY ("user_id")
);

CREATE TABLE "social_follows" (
  "id" UUID NOT NULL,
  "follower_id" UUID NOT NULL,
  "following_id" UUID NOT NULL,
  "status" "SocialFollowStatus" NOT NULL DEFAULT 'ACCEPTED',
  "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updated_at" TIMESTAMPTZ(3) NOT NULL,
  CONSTRAINT "social_follows_pkey" PRIMARY KEY ("id"),
  CONSTRAINT "social_follows_no_self" CHECK ("follower_id" <> "following_id")
);

CREATE TABLE "social_posts" (
  "id" UUID NOT NULL,
  "author_id" UUID NOT NULL,
  "caption" VARCHAR(2200),
  "visibility" "SocialPostVisibility" NOT NULL DEFAULT 'PUBLIC',
  "status" "SocialPostStatus" NOT NULL DEFAULT 'PUBLISHED',
  "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updated_at" TIMESTAMPTZ(3) NOT NULL,
  CONSTRAINT "social_posts_pkey" PRIMARY KEY ("id"),
  CONSTRAINT "social_posts_content_required" CHECK (length(trim(coalesce("caption", ''))) > 0)
);

CREATE TABLE "social_comments" (
  "id" UUID NOT NULL,
  "post_id" UUID NOT NULL,
  "author_id" UUID NOT NULL,
  "parent_id" UUID,
  "body" VARCHAR(1000) NOT NULL,
  "deleted_at" TIMESTAMPTZ(3),
  "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updated_at" TIMESTAMPTZ(3) NOT NULL,
  CONSTRAINT "social_comments_pkey" PRIMARY KEY ("id"),
  CONSTRAINT "social_comments_body_required" CHECK (length(trim("body")) > 0)
);

CREATE TABLE "social_post_likes" (
  "post_id" UUID NOT NULL,
  "user_id" UUID NOT NULL,
  "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT "social_post_likes_pkey" PRIMARY KEY ("post_id", "user_id")
);

CREATE TABLE "messaging_conversations" (
  "id" UUID NOT NULL,
  "type" "ConversationType" NOT NULL DEFAULT 'DIRECT',
  "direct_key" VARCHAR(73),
  "title" VARCHAR(120),
  "last_message_at" TIMESTAMPTZ(3),
  "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updated_at" TIMESTAMPTZ(3) NOT NULL,
  CONSTRAINT "messaging_conversations_pkey" PRIMARY KEY ("id")
);

CREATE TABLE "messaging_conversation_participants" (
  "conversation_id" UUID NOT NULL,
  "user_id" UUID NOT NULL,
  "joined_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "last_read_at" TIMESTAMPTZ(3),
  "muted_at" TIMESTAMPTZ(3),
  "archived_at" TIMESTAMPTZ(3),
  CONSTRAINT "messaging_conversation_participants_pkey" PRIMARY KEY ("conversation_id", "user_id")
);

CREATE TABLE "messaging_messages" (
  "id" UUID NOT NULL,
  "conversation_id" UUID NOT NULL,
  "sender_id" UUID NOT NULL,
  "client_message_id" VARCHAR(160) NOT NULL,
  "type" "MessageType" NOT NULL DEFAULT 'TEXT',
  "body" VARCHAR(4000),
  "reply_to_id" UUID,
  "edited_at" TIMESTAMPTZ(3),
  "deleted_at" TIMESTAMPTZ(3),
  "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updated_at" TIMESTAMPTZ(3) NOT NULL,
  CONSTRAINT "messaging_messages_pkey" PRIMARY KEY ("id"),
  CONSTRAINT "messaging_messages_text_body_required"
    CHECK ("deleted_at" IS NOT NULL OR "type" <> 'TEXT' OR length(trim(coalesce("body", ''))) > 0)
);

CREATE UNIQUE INDEX "social_follows_follower_id_following_id_key" ON "social_follows"("follower_id", "following_id");
CREATE INDEX "social_follows_following_id_status_created_at_idx" ON "social_follows"("following_id", "status", "created_at");
CREATE INDEX "social_follows_follower_id_status_created_at_idx" ON "social_follows"("follower_id", "status", "created_at");
CREATE INDEX "social_posts_author_id_status_created_at_idx" ON "social_posts"("author_id", "status", "created_at");
CREATE INDEX "social_posts_status_visibility_created_at_idx" ON "social_posts"("status", "visibility", "created_at");
CREATE INDEX "social_comments_post_id_deleted_at_created_at_idx" ON "social_comments"("post_id", "deleted_at", "created_at");
CREATE INDEX "social_comments_author_id_created_at_idx" ON "social_comments"("author_id", "created_at");
CREATE INDEX "social_post_likes_user_id_created_at_idx" ON "social_post_likes"("user_id", "created_at");
CREATE UNIQUE INDEX "messaging_conversations_direct_key_key" ON "messaging_conversations"("direct_key");
CREATE INDEX "messaging_conversations_last_message_at_idx" ON "messaging_conversations"("last_message_at");
CREATE INDEX "messaging_conversation_participants_user_id_archived_at_idx" ON "messaging_conversation_participants"("user_id", "archived_at");
CREATE UNIQUE INDEX "messaging_messages_sender_id_client_message_id_key" ON "messaging_messages"("sender_id", "client_message_id");
CREATE INDEX "messaging_messages_conversation_id_created_at_idx" ON "messaging_messages"("conversation_id", "created_at");
CREATE INDEX "messaging_messages_sender_id_created_at_idx" ON "messaging_messages"("sender_id", "created_at");

ALTER TABLE "social_profiles" ADD CONSTRAINT "social_profiles_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "identity_users"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "social_follows" ADD CONSTRAINT "social_follows_follower_id_fkey" FOREIGN KEY ("follower_id") REFERENCES "identity_users"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "social_follows" ADD CONSTRAINT "social_follows_following_id_fkey" FOREIGN KEY ("following_id") REFERENCES "identity_users"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "social_posts" ADD CONSTRAINT "social_posts_author_id_fkey" FOREIGN KEY ("author_id") REFERENCES "identity_users"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "social_comments" ADD CONSTRAINT "social_comments_post_id_fkey" FOREIGN KEY ("post_id") REFERENCES "social_posts"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "social_comments" ADD CONSTRAINT "social_comments_author_id_fkey" FOREIGN KEY ("author_id") REFERENCES "identity_users"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "social_comments" ADD CONSTRAINT "social_comments_parent_id_fkey" FOREIGN KEY ("parent_id") REFERENCES "social_comments"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "social_post_likes" ADD CONSTRAINT "social_post_likes_post_id_fkey" FOREIGN KEY ("post_id") REFERENCES "social_posts"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "social_post_likes" ADD CONSTRAINT "social_post_likes_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "identity_users"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "messaging_conversation_participants" ADD CONSTRAINT "messaging_conversation_participants_conversation_id_fkey" FOREIGN KEY ("conversation_id") REFERENCES "messaging_conversations"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "messaging_conversation_participants" ADD CONSTRAINT "messaging_conversation_participants_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "identity_users"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "messaging_messages" ADD CONSTRAINT "messaging_messages_conversation_id_fkey" FOREIGN KEY ("conversation_id") REFERENCES "messaging_conversations"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "messaging_messages" ADD CONSTRAINT "messaging_messages_sender_id_fkey" FOREIGN KEY ("sender_id") REFERENCES "identity_users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE "messaging_messages" ADD CONSTRAINT "messaging_messages_reply_to_id_fkey" FOREIGN KEY ("reply_to_id") REFERENCES "messaging_messages"("id") ON DELETE SET NULL ON UPDATE CASCADE;