-- Cache SDS product details separately from website merchandising configuration.
CREATE TABLE "origin_good_details" (
    "origin_good_id" BIGINT NOT NULL,
    "product_code" TEXT,
    "english_name" TEXT,
    "blank_design_url" TEXT,
    "details_page_video_url" TEXT,
    "texture_name" TEXT,
    "production_cycle_hours" INTEGER,
    "min_weight_g" DECIMAL(12,3),
    "reminder" TEXT,
    "production_process" TEXT,
    "material_description" TEXT,
    "product_performance" TEXT,
    "applicable_scenarios" TEXT,
    "washing_instructions" TEXT,
    "special_description" TEXT,
    "design_explanation" TEXT,
    "design_area" TEXT,
    "picture_request" TEXT,
    "size_chart" JSONB,
    "package_specs" JSONB,
    "options" JSONB,
    "media" JSONB,
    "upstream_updated_at" TIMESTAMPTZ(6),
    "synced_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT "origin_good_details_pkey" PRIMARY KEY ("origin_good_id")
);

CREATE TABLE "origin_good_variants" (
    "origin_good_variant_id" BIGSERIAL NOT NULL,
    "origin_good_id" BIGINT NOT NULL,
    "sds_variant_id" TEXT NOT NULL,
    "sku" TEXT NOT NULL,
    "size_id" TEXT,
    "size_name" TEXT,
    "color_id" TEXT,
    "color_name" TEXT,
    "color_hex" TEXT,
    "image_url" TEXT,
    "price" DECIMAL(12,2),
    "original_price" DECIMAL(12,2),
    "weight_g" DECIMAL(12,3),
    "box_length_cm" DECIMAL(12,3),
    "box_width_cm" DECIMAL(12,3),
    "box_height_cm" DECIMAL(12,3),
    "enabled" BOOLEAN NOT NULL DEFAULT true,
    "sort_order" INTEGER NOT NULL DEFAULT 0,
    "design_data" JSONB,
    "created_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT "origin_good_variants_pkey" PRIMARY KEY ("origin_good_variant_id")
);

CREATE UNIQUE INDEX "origin_good_variants_origin_good_id_sds_variant_id_key"
ON "origin_good_variants"("origin_good_id", "sds_variant_id");
CREATE INDEX "origin_good_variants_origin_good_id_sort_order_idx"
ON "origin_good_variants"("origin_good_id", "sort_order");
CREATE INDEX "origin_good_variants_sku_idx" ON "origin_good_variants"("sku");

ALTER TABLE "origin_good_details"
ADD CONSTRAINT "origin_good_details_origin_good_id_fkey"
FOREIGN KEY ("origin_good_id") REFERENCES "origin_goods"("origin_good_id")
ON DELETE CASCADE ON UPDATE NO ACTION;

ALTER TABLE "origin_good_variants"
ADD CONSTRAINT "origin_good_variants_origin_good_id_fkey"
FOREIGN KEY ("origin_good_id") REFERENCES "origin_goods"("origin_good_id")
ON DELETE CASCADE ON UPDATE NO ACTION;
