-- AlterTable
ALTER TABLE "origin_goods" ADD COLUMN     "craft_label" TEXT,
ADD COLUMN     "family_id" BIGINT,
ADD COLUMN     "logistics_label" TEXT,
ADD COLUMN     "sku_code" TEXT,
ADD COLUMN     "warehouse_label" TEXT;

-- CreateTable
CREATE TABLE "product_families" (
    "family_id" BIGSERIAL NOT NULL,
    "family_code" TEXT,
    "family_name" TEXT NOT NULL,
    "family_image" TEXT,
    "country_id" BIGINT,
    "category_id" BIGINT,
    "primary_origin_good_id" BIGINT,
    "detail" JSONB,
    "size_chart" JSONB,
    "package_specs" JSONB,
    "price_matrix" JSONB,
    "auto_managed" BOOLEAN NOT NULL DEFAULT true,
    "stale" BOOLEAN NOT NULL DEFAULT false,
    "created_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(6) NOT NULL,

    CONSTRAINT "product_families_pkey" PRIMARY KEY ("family_id")
);

-- CreateTable
CREATE TABLE "family_price_overrides" (
    "family_price_override_id" BIGSERIAL NOT NULL,
    "family_id" BIGINT NOT NULL,
    "size_id" TEXT NOT NULL,
    "color_id" TEXT NOT NULL,
    "craft" TEXT NOT NULL,
    "logistics" TEXT NOT NULL,
    "price" DECIMAL(12,2) NOT NULL,
    "note" TEXT,
    "created_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(6) NOT NULL,

    CONSTRAINT "family_price_overrides_pkey" PRIMARY KEY ("family_price_override_id")
);

-- CreateIndex
CREATE UNIQUE INDEX "product_families_family_code_key" ON "product_families"("family_code");

-- CreateIndex
CREATE INDEX "product_families_country_id_idx" ON "product_families"("country_id");

-- CreateIndex
CREATE INDEX "product_families_category_id_idx" ON "product_families"("category_id");

-- CreateIndex
CREATE UNIQUE INDEX "family_price_overrides_family_id_size_id_color_id_craft_log_key" ON "family_price_overrides"("family_id", "size_id", "color_id", "craft", "logistics");

-- CreateIndex
CREATE INDEX "origin_goods_family_id_idx" ON "origin_goods"("family_id");

-- CreateIndex
CREATE INDEX "origin_goods_sku_code_idx" ON "origin_goods"("sku_code");

-- CreateIndex
CREATE INDEX "origin_goods_logistics_label_idx" ON "origin_goods"("logistics_label");

-- CreateIndex
CREATE INDEX "origin_goods_craft_label_idx" ON "origin_goods"("craft_label");

-- AddForeignKey
ALTER TABLE "origin_goods" ADD CONSTRAINT "origin_goods_family_id_fkey" FOREIGN KEY ("family_id") REFERENCES "product_families"("family_id") ON DELETE SET NULL ON UPDATE NO ACTION;

-- AddForeignKey
ALTER TABLE "product_families" ADD CONSTRAINT "product_families_country_id_fkey" FOREIGN KEY ("country_id") REFERENCES "countries"("country_id") ON DELETE SET NULL ON UPDATE NO ACTION;

-- AddForeignKey
ALTER TABLE "product_families" ADD CONSTRAINT "product_families_category_id_fkey" FOREIGN KEY ("category_id") REFERENCES "categories"("category_id") ON DELETE SET NULL ON UPDATE NO ACTION;

-- AddForeignKey
ALTER TABLE "family_price_overrides" ADD CONSTRAINT "family_price_overrides_family_id_fkey" FOREIGN KEY ("family_id") REFERENCES "product_families"("family_id") ON DELETE CASCADE ON UPDATE NO ACTION;
