ALTER TABLE "subscription" RENAME TO "subscription_old";--> statement-breakpoint
ALTER TABLE subscription_old DROP CONSTRAINT subscription_stripe_subscription_id_unique;--> statement-breakpoint
CREATE TABLE "subscription"
(
    "id"                     uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
    "user_id"                uuid,
    "stripe_subscription_id" varchar(255),
    "stripe_customer_id"     varchar(255)                               NOT NULL,
    "plan"                   varchar(45)                                NOT NULL,
    "status"                 varchar(45)                                NOT NULL,
    "reference_id"           uuid,
    "metadata"               jsonb,
    "seats"                  integer          DEFAULT 1,
    "limits"                 jsonb,
    "cancel_at_period_end"   boolean          DEFAULT false,
    "period_start"           timestamp,
    "period_end"             timestamp,
    "trial_start"            timestamp,
    "trial_end"              timestamp,
    "canceled_at"            timestamp,
    "created_at"             timestamp        DEFAULT now()             NOT NULL,
    "updated_at"             timestamp        DEFAULT now()             NOT NULL,
    CONSTRAINT "subscription_stripe_subscription_id_unique" UNIQUE ("stripe_subscription_id")
);
--> statement-breakpoint

INSERT INTO subscription (
    user_id,
    stripe_subscription_id,
    stripe_customer_id,
    plan,
    status,
    reference_id,
    metadata,
    seats,
    limits,
    cancel_at_period_end,
    period_start,
    period_end,
    trial_start,
    trial_end,
    canceled_at,
    created_at,
    updated_at
)
SELECT
    user_id::uuid,
    stripe_subscription_id,
    stripe_customer_id,
    plan,
    status,
    reference_id::uuid,
    metadata,
    seats,
    limits,
    cancel_at_period_end,
    period_start,
    period_end,
    trial_start,
    trial_end,
    canceled_at,
    created_at,
    updated_at
FROM subscription_old;--> statement-breakpoint

DROP TABLE "subscription_old" CASCADE;--> statement-breakpoint

ALTER TABLE "subscription" ADD CONSTRAINT "subscription_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users" ("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
CREATE INDEX "subscription_user_id_idx" ON "subscription" USING btree ("user_id");--> statement-breakpoint
CREATE INDEX "subscription_stripe_customer_id_idx" ON "subscription" USING btree ("stripe_customer_id");--> statement-breakpoint
CREATE UNIQUE INDEX "subscription_stripe_subscription_id_idx" ON "subscription" USING btree ("stripe_subscription_id");--> statement-breakpoint
CREATE INDEX "subscription_reference_id_idx" ON "subscription" USING btree ("reference_id");
