-- Migration: 0066_deal_skus_expand
-- Phase A (expand-only): add deal_variant_axes, deal_variant_options, deal_skus,
-- cache cols on deals, nullable FK cols on cart_items + purchases, then backfill.
-- NO DROP COLUMN. NO SET NOT NULL. Those happen in Task 22.

--> statement-breakpoint
CREATE TYPE "public"."variant_axis_kind" AS ENUM('SIZE', 'COLOR', 'TIMESLOT', 'CUSTOM');

--> statement-breakpoint
CREATE TABLE "deal_variant_axes" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "deal_id" uuid NOT NULL,
  "axis_order" integer NOT NULL,
  "kind" "variant_axis_kind" NOT NULL,
  "name_he" text NOT NULL,
  "name_en" text NOT NULL,
  "is_active" boolean NOT NULL DEFAULT true,
  "created_at" timestamp with time zone NOT NULL DEFAULT now()
);

--> statement-breakpoint
CREATE TABLE "deal_variant_options" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "axis_id" uuid NOT NULL,
  "option_order" integer NOT NULL,
  "value_code" text NOT NULL,
  "label_he" text NOT NULL,
  "label_en" text NOT NULL,
  "slot_start" timestamp with time zone,
  "slot_end" timestamp with time zone,
  "swatch_hex" text,
  "is_active" boolean NOT NULL DEFAULT true,
  "created_at" timestamp with time zone NOT NULL DEFAULT now()
);

--> statement-breakpoint
CREATE TABLE "deal_skus" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "deal_id" uuid NOT NULL,
  "option_ids" uuid[] NOT NULL,
  "option_ids_hash" text NOT NULL,
  "original_price" numeric(10, 2) NOT NULL,
  "discount_percent" integer NOT NULL,
  "discounted_price" numeric(10, 2) NOT NULL,
  "quantity_total" integer NOT NULL,
  "quantity_sold" integer NOT NULL DEFAULT 0,
  "is_active" boolean NOT NULL DEFAULT true,
  "created_at" timestamp with time zone NOT NULL DEFAULT now(),
  CONSTRAINT "sku_sold_le_qty" CHECK (quantity_sold <= quantity_total),
  CONSTRAINT "sku_discount_percent_range" CHECK (discount_percent BETWEEN 50 AND 100)
);

--> statement-breakpoint
ALTER TABLE "deal_variant_axes"
  ADD CONSTRAINT "deal_variant_axes_deal_id_deals_id_fk"
  FOREIGN KEY ("deal_id") REFERENCES "public"."deals"("id") ON DELETE restrict ON UPDATE no action;

--> statement-breakpoint
ALTER TABLE "deal_variant_options"
  ADD CONSTRAINT "deal_variant_options_axis_id_deal_variant_axes_id_fk"
  FOREIGN KEY ("axis_id") REFERENCES "public"."deal_variant_axes"("id") ON DELETE restrict ON UPDATE no action;

--> statement-breakpoint
ALTER TABLE "deal_skus"
  ADD CONSTRAINT "deal_skus_deal_id_deals_id_fk"
  FOREIGN KEY ("deal_id") REFERENCES "public"."deals"("id") ON DELETE cascade ON UPDATE no action;

--> statement-breakpoint
ALTER TABLE "cart_items"
  ADD COLUMN "deal_sku_id" uuid;

--> statement-breakpoint
ALTER TABLE "cart_items"
  ADD CONSTRAINT "cart_items_deal_sku_id_deal_skus_id_fk"
  FOREIGN KEY ("deal_sku_id") REFERENCES "public"."deal_skus"("id") ON DELETE cascade ON UPDATE no action;

--> statement-breakpoint
ALTER TABLE "purchases"
  ADD COLUMN "deal_sku_id" uuid;

--> statement-breakpoint
ALTER TABLE "purchases"
  ADD CONSTRAINT "purchases_deal_sku_id_deal_skus_id_fk"
  FOREIGN KEY ("deal_sku_id") REFERENCES "public"."deal_skus"("id") ON DELETE restrict ON UPDATE no action;

--> statement-breakpoint
ALTER TABLE "deals"
  ADD COLUMN "min_price" numeric(10, 2),
  ADD COLUMN "max_price" numeric(10, 2),
  ADD COLUMN "max_discount_percent" integer,
  ADD COLUMN "stock_remaining" integer;

--> statement-breakpoint
ALTER TABLE "deal_variant_axes"
  ADD CONSTRAINT "deal_variant_axes_deal_order_uniq" UNIQUE ("deal_id", "axis_order");

--> statement-breakpoint
ALTER TABLE "deal_variant_options"
  ADD CONSTRAINT "deal_variant_options_axis_code_uniq" UNIQUE ("axis_id", "value_code");

--> statement-breakpoint
ALTER TABLE "deal_skus"
  ADD CONSTRAINT "deal_skus_deal_combo_uniq" UNIQUE ("deal_id", "option_ids_hash");

-- Backfill: one default SKU per existing deal
INSERT INTO deal_skus
  (deal_id, option_ids, option_ids_hash,
   original_price, discount_percent, discounted_price,
   quantity_total, quantity_sold)
SELECT id, ARRAY[]::uuid[], 'default',
       original_price, discount_percent, discounted_price,
       quantity_total, quantity_sold
FROM deals
ON CONFLICT (deal_id, option_ids_hash) DO NOTHING;

-- Repoint cart_items to default SKU
UPDATE cart_items ci
   SET deal_sku_id = (SELECT id FROM deal_skus WHERE deal_id = ci.deal_id LIMIT 1)
 WHERE deal_sku_id IS NULL;

-- Repoint purchases to default SKU
UPDATE purchases p
   SET deal_sku_id = (SELECT id FROM deal_skus WHERE deal_id = p.deal_id LIMIT 1)
 WHERE deal_sku_id IS NULL;

-- Seed denormalized cache columns from default SKU values
UPDATE deals d SET
  min_price            = s.discounted_price,
  max_price            = s.discounted_price,
  max_discount_percent = s.discount_percent,
  stock_remaining      = s.quantity_total - s.quantity_sold
FROM deal_skus s
WHERE s.deal_id = d.id
  AND d.min_price IS NULL;
