CREATE TABLE "profile" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"user_id" uuid,
	"avatar_id" uuid,
	"is_main" boolean DEFAULT false NOT NULL,
	"slug" varchar(45) NOT NULL,
	"name" varchar(255) NOT NULL,
	"job_info" text,
	"created_at" timestamp DEFAULT now() NOT NULL,
	"updated_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
ALTER TABLE "media" DROP CONSTRAINT "media_user_id_users_id_fk";
--> statement-breakpoint
ALTER TABLE "media" ALTER COLUMN "user_id" DROP NOT NULL;--> statement-breakpoint
ALTER TABLE "posts" ADD COLUMN "author_id" uuid;--> statement-breakpoint
ALTER TABLE "profile" ADD CONSTRAINT "profile_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "profile" ADD CONSTRAINT "profile_avatar_id_media_id_fk" FOREIGN KEY ("avatar_id") REFERENCES "public"."media"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
CREATE UNIQUE INDEX "profile_slug_index" ON "profile" USING btree ("slug");--> statement-breakpoint
CREATE INDEX "profile_is_main_index" ON "profile" USING btree ("is_main");--> statement-breakpoint
ALTER TABLE "media" ADD CONSTRAINT "media_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "posts" ADD CONSTRAINT "posts_author_id_profile_id_fk" FOREIGN KEY ("author_id") REFERENCES "public"."profile"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
CREATE INDEX "post_author_id_index" ON "posts" USING btree ("author_id");--> statement-breakpoint
INSERT INTO profile (
    user_id,
    slug,
    name,
    is_main
)
SELECT
    id,
    id,
    name,
    true
FROM users;--> statement-breakpoint
UPDATE posts
SET
    author_id = profile.id
FROM profile
     INNER JOIN users on profile.user_id = users.id
WHERE posts.user_id = users.id;