ab19c57d09
- Fix ZodError: EZY API returns params=null, use .nullish() instead of .optional() - Add manual SQL migration for new collections (locations, reviews, birthday_packages) and schema changes (header ctaLabel/ctaHref, navLinks autoChildrenFrom/children, homepage sectionTitles/whyParents/ gallery/video/birthdayIntro groups) - Note: push:true doesn't run CLI drizzle-kit in standalone build; run migrations/0001_new_collections.sql manually on new environments Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
333 lines
18 KiB
SQL
333 lines
18 KiB
SQL
-- Shumiland DB migration: new collections + HomePage/Header schema changes
|
|
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
|
-- 1. Enums
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
|
DO $$ BEGIN
|
|
CREATE TYPE "enum_reviews_source" AS ENUM ('google', 'facebook', 'instagram', 'manual');
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
CREATE TYPE "enum_header_nav_links_auto_children_from" AS ENUM ('none', 'locations');
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
|
-- 2. Header: add cta_label, cta_href
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
|
ALTER TABLE "header" ADD COLUMN IF NOT EXISTS "cta_label" varchar;
|
|
ALTER TABLE "header" ADD COLUMN IF NOT EXISTS "cta_href" varchar;
|
|
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
|
-- 3. header_nav_links: add auto_children_from
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
|
ALTER TABLE "header_nav_links"
|
|
ADD COLUMN IF NOT EXISTS "auto_children_from" "enum_header_nav_links_auto_children_from" DEFAULT 'none';
|
|
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
|
-- 4. header_nav_links_children table
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
|
CREATE TABLE IF NOT EXISTS "header_nav_links_children" (
|
|
"_order" integer NOT NULL,
|
|
"_parent_id" varchar NOT NULL,
|
|
"id" varchar PRIMARY KEY NOT NULL,
|
|
"label" varchar,
|
|
"href" varchar
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS "header_nav_links_children_order_idx"
|
|
ON "header_nav_links_children" ("_order");
|
|
CREATE INDEX IF NOT EXISTS "header_nav_links_children_parent_id_idx"
|
|
ON "header_nav_links_children" ("_parent_id");
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "header_nav_links_children"
|
|
ADD CONSTRAINT "header_nav_links_children_parent_id_fk"
|
|
FOREIGN KEY ("_parent_id") REFERENCES "header_nav_links"("id") ON DELETE CASCADE;
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
|
-- 5. home_page: add new group columns
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
|
ALTER TABLE "home_page"
|
|
ADD COLUMN IF NOT EXISTS "section_titles_locations" varchar,
|
|
ADD COLUMN IF NOT EXISTS "section_titles_why_parents" varchar,
|
|
ADD COLUMN IF NOT EXISTS "section_titles_birthday" varchar,
|
|
ADD COLUMN IF NOT EXISTS "section_titles_gallery" varchar,
|
|
ADD COLUMN IF NOT EXISTS "section_titles_reviews" varchar,
|
|
ADD COLUMN IF NOT EXISTS "section_titles_news" varchar,
|
|
ADD COLUMN IF NOT EXISTS "video_poster_id" integer,
|
|
ADD COLUMN IF NOT EXISTS "video_src" varchar,
|
|
ADD COLUMN IF NOT EXISTS "birthday_intro_text" varchar;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "home_page"
|
|
ADD CONSTRAINT "home_page_video_poster_id_media_id_fk"
|
|
FOREIGN KEY ("video_poster_id") REFERENCES "media"("id") ON DELETE SET NULL;
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
CREATE INDEX IF NOT EXISTS "home_page_video_poster_idx" ON "home_page" ("video_poster_id");
|
|
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
|
-- 6. home_page_why_parents_items array table
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
|
CREATE TABLE IF NOT EXISTS "home_page_why_parents_items" (
|
|
"_order" integer NOT NULL,
|
|
"_parent_id" integer NOT NULL,
|
|
"id" varchar PRIMARY KEY NOT NULL,
|
|
"title" varchar,
|
|
"description" varchar
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS "home_page_why_parents_items_order_idx"
|
|
ON "home_page_why_parents_items" ("_order");
|
|
CREATE INDEX IF NOT EXISTS "home_page_why_parents_items_parent_id_idx"
|
|
ON "home_page_why_parents_items" ("_parent_id");
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "home_page_why_parents_items"
|
|
ADD CONSTRAINT "home_page_why_parents_items_parent_id_fk"
|
|
FOREIGN KEY ("_parent_id") REFERENCES "home_page"("id") ON DELETE CASCADE;
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
|
-- 7. home_page_why_parents_side_gallery array table
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
|
CREATE TABLE IF NOT EXISTS "home_page_why_parents_side_gallery" (
|
|
"_order" integer NOT NULL,
|
|
"_parent_id" integer NOT NULL,
|
|
"id" varchar PRIMARY KEY NOT NULL,
|
|
"image_id" integer
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS "home_page_why_parents_side_gallery_order_idx"
|
|
ON "home_page_why_parents_side_gallery" ("_order");
|
|
CREATE INDEX IF NOT EXISTS "home_page_why_parents_side_gallery_parent_id_idx"
|
|
ON "home_page_why_parents_side_gallery" ("_parent_id");
|
|
CREATE INDEX IF NOT EXISTS "home_page_why_parents_side_gallery_image_idx"
|
|
ON "home_page_why_parents_side_gallery" ("image_id");
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "home_page_why_parents_side_gallery"
|
|
ADD CONSTRAINT "home_page_why_parents_side_gallery_parent_id_fk"
|
|
FOREIGN KEY ("_parent_id") REFERENCES "home_page"("id") ON DELETE CASCADE;
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "home_page_why_parents_side_gallery"
|
|
ADD CONSTRAINT "home_page_why_parents_side_gallery_image_id_media_id_fk"
|
|
FOREIGN KEY ("image_id") REFERENCES "media"("id") ON DELETE SET NULL;
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
|
-- 8. home_page_gallery_images array table
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
|
CREATE TABLE IF NOT EXISTS "home_page_gallery_images" (
|
|
"_order" integer NOT NULL,
|
|
"_parent_id" integer NOT NULL,
|
|
"id" varchar PRIMARY KEY NOT NULL,
|
|
"image_id" integer,
|
|
"alt" varchar
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS "home_page_gallery_images_order_idx"
|
|
ON "home_page_gallery_images" ("_order");
|
|
CREATE INDEX IF NOT EXISTS "home_page_gallery_images_parent_id_idx"
|
|
ON "home_page_gallery_images" ("_parent_id");
|
|
CREATE INDEX IF NOT EXISTS "home_page_gallery_images_image_idx"
|
|
ON "home_page_gallery_images" ("image_id");
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "home_page_gallery_images"
|
|
ADD CONSTRAINT "home_page_gallery_images_parent_id_fk"
|
|
FOREIGN KEY ("_parent_id") REFERENCES "home_page"("id") ON DELETE CASCADE;
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "home_page_gallery_images"
|
|
ADD CONSTRAINT "home_page_gallery_images_image_id_media_id_fk"
|
|
FOREIGN KEY ("image_id") REFERENCES "media"("id") ON DELETE SET NULL;
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
|
-- 9. locations collection
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
|
CREATE SEQUENCE IF NOT EXISTS "locations_id_seq";
|
|
|
|
CREATE TABLE IF NOT EXISTS "locations" (
|
|
"id" integer PRIMARY KEY DEFAULT nextval('locations_id_seq'),
|
|
"name" varchar NOT NULL,
|
|
"slug" varchar NOT NULL,
|
|
"tagline" varchar,
|
|
"short_desc" varchar,
|
|
"description" jsonb,
|
|
"image_id" integer,
|
|
"href" varchar,
|
|
"show_in_menu" boolean DEFAULT true,
|
|
"show_on_home" boolean DEFAULT true,
|
|
"sort" numeric DEFAULT 0,
|
|
"updated_at" timestamp(3) with time zone NOT NULL DEFAULT now(),
|
|
"created_at" timestamp(3) with time zone NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS "locations_slug_idx" ON "locations" ("slug");
|
|
CREATE INDEX IF NOT EXISTS "locations_updated_at_idx" ON "locations" ("updated_at");
|
|
CREATE INDEX IF NOT EXISTS "locations_created_at_idx" ON "locations" ("created_at");
|
|
CREATE INDEX IF NOT EXISTS "locations_image_idx" ON "locations" ("image_id");
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "locations"
|
|
ADD CONSTRAINT "locations_image_id_media_id_fk"
|
|
FOREIGN KEY ("image_id") REFERENCES "media"("id") ON DELETE SET NULL;
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
|
-- 10. locations_gallery array table
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
|
CREATE TABLE IF NOT EXISTS "locations_gallery" (
|
|
"_order" integer NOT NULL,
|
|
"_parent_id" integer NOT NULL,
|
|
"id" varchar PRIMARY KEY NOT NULL,
|
|
"image_id" integer
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS "locations_gallery_order_idx" ON "locations_gallery" ("_order");
|
|
CREATE INDEX IF NOT EXISTS "locations_gallery_parent_id_idx" ON "locations_gallery" ("_parent_id");
|
|
CREATE INDEX IF NOT EXISTS "locations_gallery_image_idx" ON "locations_gallery" ("image_id");
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "locations_gallery"
|
|
ADD CONSTRAINT "locations_gallery_parent_id_fk"
|
|
FOREIGN KEY ("_parent_id") REFERENCES "locations"("id") ON DELETE CASCADE;
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "locations_gallery"
|
|
ADD CONSTRAINT "locations_gallery_image_id_media_id_fk"
|
|
FOREIGN KEY ("image_id") REFERENCES "media"("id") ON DELETE SET NULL;
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
|
-- 11. reviews collection
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
|
CREATE SEQUENCE IF NOT EXISTS "reviews_id_seq";
|
|
|
|
CREATE TABLE IF NOT EXISTS "reviews" (
|
|
"id" integer PRIMARY KEY DEFAULT nextval('reviews_id_seq'),
|
|
"name" varchar NOT NULL,
|
|
"initial" varchar(2),
|
|
"avatar_bg_id" integer,
|
|
"ago" varchar,
|
|
"rating" numeric DEFAULT 5,
|
|
"text" varchar NOT NULL,
|
|
"source" "enum_reviews_source" DEFAULT 'google',
|
|
"show_on_home" boolean DEFAULT true,
|
|
"sort" numeric DEFAULT 0,
|
|
"updated_at" timestamp(3) with time zone NOT NULL DEFAULT now(),
|
|
"created_at" timestamp(3) with time zone NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS "reviews_updated_at_idx" ON "reviews" ("updated_at");
|
|
CREATE INDEX IF NOT EXISTS "reviews_created_at_idx" ON "reviews" ("created_at");
|
|
CREATE INDEX IF NOT EXISTS "reviews_avatar_bg_idx" ON "reviews" ("avatar_bg_id");
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "reviews"
|
|
ADD CONSTRAINT "reviews_avatar_bg_id_media_id_fk"
|
|
FOREIGN KEY ("avatar_bg_id") REFERENCES "media"("id") ON DELETE SET NULL;
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
|
-- 12. birthday_packages collection
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
|
CREATE SEQUENCE IF NOT EXISTS "birthday_packages_id_seq";
|
|
|
|
CREATE TABLE IF NOT EXISTS "birthday_packages" (
|
|
"id" integer PRIMARY KEY DEFAULT nextval('birthday_packages_id_seq'),
|
|
"name" varchar NOT NULL,
|
|
"slug" varchar NOT NULL,
|
|
"price" numeric NOT NULL,
|
|
"currency" varchar DEFAULT '₴',
|
|
"price_label" varchar,
|
|
"featured" boolean DEFAULT false,
|
|
"badge" varchar,
|
|
"cta_label" varchar DEFAULT 'Обрати пакет',
|
|
"cta_href" varchar,
|
|
"sort" numeric DEFAULT 0,
|
|
"updated_at" timestamp(3) with time zone NOT NULL DEFAULT now(),
|
|
"created_at" timestamp(3) with time zone NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS "birthday_packages_slug_idx" ON "birthday_packages" ("slug");
|
|
CREATE INDEX IF NOT EXISTS "birthday_packages_updated_at_idx" ON "birthday_packages" ("updated_at");
|
|
CREATE INDEX IF NOT EXISTS "birthday_packages_created_at_idx" ON "birthday_packages" ("created_at");
|
|
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
|
-- 13. birthday_packages_features array table
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
|
CREATE TABLE IF NOT EXISTS "birthday_packages_features" (
|
|
"_order" integer NOT NULL,
|
|
"_parent_id" integer NOT NULL,
|
|
"id" varchar PRIMARY KEY NOT NULL,
|
|
"text" varchar NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS "birthday_packages_features_order_idx"
|
|
ON "birthday_packages_features" ("_order");
|
|
CREATE INDEX IF NOT EXISTS "birthday_packages_features_parent_id_idx"
|
|
ON "birthday_packages_features" ("_parent_id");
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "birthday_packages_features"
|
|
ADD CONSTRAINT "birthday_packages_features_parent_id_fk"
|
|
FOREIGN KEY ("_parent_id") REFERENCES "birthday_packages"("id") ON DELETE CASCADE;
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
|
-- 14. payload_locked_documents_rels: add new collection FK columns
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
|
ALTER TABLE "payload_locked_documents_rels"
|
|
ADD COLUMN IF NOT EXISTS "locations_id" integer,
|
|
ADD COLUMN IF NOT EXISTS "reviews_id" integer,
|
|
ADD COLUMN IF NOT EXISTS "birthday_packages_id" integer;
|
|
|
|
CREATE INDEX IF NOT EXISTS "payload_locked_documents_rels_locations_id_idx"
|
|
ON "payload_locked_documents_rels" ("locations_id");
|
|
CREATE INDEX IF NOT EXISTS "payload_locked_documents_rels_reviews_id_idx"
|
|
ON "payload_locked_documents_rels" ("reviews_id");
|
|
CREATE INDEX IF NOT EXISTS "payload_locked_documents_rels_birthday_packages_id_idx"
|
|
ON "payload_locked_documents_rels" ("birthday_packages_id");
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "payload_locked_documents_rels"
|
|
ADD CONSTRAINT "payload_locked_documents_rels_locations_fk"
|
|
FOREIGN KEY ("locations_id") REFERENCES "locations"("id") ON DELETE CASCADE;
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "payload_locked_documents_rels"
|
|
ADD CONSTRAINT "payload_locked_documents_rels_reviews_fk"
|
|
FOREIGN KEY ("reviews_id") REFERENCES "reviews"("id") ON DELETE CASCADE;
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "payload_locked_documents_rels"
|
|
ADD CONSTRAINT "payload_locked_documents_rels_birthday_packages_fk"
|
|
FOREIGN KEY ("birthday_packages_id") REFERENCES "birthday_packages"("id") ON DELETE CASCADE;
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|