CREATE TYPE "public"."cancellation_policy" AS ENUM('LEGAL_ONLY', 'FLEXIBLE', 'CUSTOM_WINDOW');--> statement-breakpoint
CREATE TYPE "public"."fill_rule" AS ENUM('ALL_OR_NOTHING', 'MINIMUM_THRESHOLD');--> statement-breakpoint
CREATE TYPE "public"."group_deal_state" AS ENUM('COLLECTING', 'THRESHOLD_MET', 'EXECUTING', 'PARTIAL_PENDING', 'SUCCEEDED', 'FAILED', 'VENDOR_HONORED', 'EXTENDED');--> statement-breakpoint
CREATE TYPE "public"."reservation_status" AS ENUM('HELD', 'CAPTURED', 'RELEASED', 'CANCELLED', 'WAITLISTED', 'HOLD_EXPIRED');--> statement-breakpoint
ALTER TYPE "public"."deal_type" ADD VALUE 'GROUP';--> statement-breakpoint
CREATE TABLE "group_deals" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"deal_id" uuid NOT NULL,
	"fill_rule" "fill_rule" NOT NULL,
	"min_group_size" integer NOT NULL,
	"max_group_size" integer NOT NULL,
	"per_customer_limit" integer DEFAULT 1 NOT NULL,
	"cancellation_policy" "cancellation_policy" DEFAULT 'LEGAL_ONLY' NOT NULL,
	"cancellation_window_hours" integer,
	"tiered_pricing_enabled" boolean DEFAULT false NOT NULL,
	"early_bird_enabled" boolean DEFAULT false NOT NULL,
	"early_bird_slots" integer,
	"early_bird_discount_percent" integer,
	"social_sharing_enabled" boolean DEFAULT false NOT NULL,
	"social_sharing_reward" text,
	"bulk_pickup_enabled" boolean DEFAULT false NOT NULL,
	"bulk_pickup_details" text,
	"group_state" "group_deal_state" DEFAULT 'COLLECTING' NOT NULL,
	"current_reservation_count" integer DEFAULT 0 NOT NULL,
	"threshold_met_at" timestamp with time zone,
	"extended_deadline" timestamp with time zone,
	"extension_count" integer DEFAULT 0 NOT NULL,
	"partial_decision_deadline" timestamp with time zone,
	"vendor_honored_at" timestamp with time zone,
	"executed_at" timestamp with time zone,
	"failed_at" timestamp with time zone,
	"source_group_deal_id" uuid,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "group_deals_deal_id_unique" UNIQUE("deal_id"),
	CONSTRAINT "group_deals_min_max" CHECK ("group_deals"."min_group_size" <= "group_deals"."max_group_size"),
	CONSTRAINT "group_deals_min_positive" CHECK ("group_deals"."min_group_size" >= 2)
);
--> statement-breakpoint
CREATE TABLE "group_reservations" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"group_deal_id" uuid NOT NULL,
	"deal_id" uuid NOT NULL,
	"user_id" uuid,
	"guest_email" text,
	"guest_phone" text,
	"quantity" integer DEFAULT 1 NOT NULL,
	"unit_price" numeric(10, 2) NOT NULL,
	"total_amount" numeric(10, 2) NOT NULL,
	"commission_amount" numeric(10, 2) NOT NULL,
	"vendor_amount" numeric(10, 2) NOT NULL,
	"status" "reservation_status" DEFAULT 'HELD' NOT NULL,
	"payment_method_id" uuid,
	"provider_authorization_id" text,
	"provider_transaction_id" text,
	"hold_expires_at" timestamp with time zone,
	"hold_refreshed_at" timestamp with time zone,
	"is_early_bird" boolean DEFAULT false NOT NULL,
	"purchase_id" uuid,
	"cancelled_at" timestamp with time zone,
	"cancellation_reason" text,
	"idempotency_key" text NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "group_reservations_idempotency_key_unique" UNIQUE("idempotency_key")
);
--> statement-breakpoint
CREATE TABLE "group_tiers" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"group_deal_id" uuid NOT NULL,
	"min_participants" integer NOT NULL,
	"price_per_unit" numeric(10, 2) NOT NULL,
	"discount_percent" integer NOT NULL,
	"sort_order" integer DEFAULT 0 NOT NULL,
	CONSTRAINT "group_tiers_discount_range" CHECK ("group_tiers"."discount_percent" BETWEEN 50 AND 100)
);
--> statement-breakpoint
CREATE TABLE "group_waitlist" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"group_deal_id" uuid NOT NULL,
	"user_id" uuid,
	"guest_email" text,
	"guest_phone" text,
	"quantity" integer DEFAULT 1 NOT NULL,
	"payment_method_id" uuid,
	"position" integer NOT NULL,
	"promoted_at" timestamp with time zone,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
ALTER TABLE "users" ALTER COLUMN "phone" DROP NOT NULL;--> statement-breakpoint
ALTER TABLE "users" ALTER COLUMN "phone_index" DROP NOT NULL;--> statement-breakpoint
ALTER TABLE "addresses" ADD COLUMN "is_default" boolean DEFAULT false NOT NULL;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "password_hash" text;--> statement-breakpoint
ALTER TABLE "vendors" ADD COLUMN "preferences_profile" jsonb DEFAULT '{}'::jsonb;--> statement-breakpoint
ALTER TABLE "group_deals" ADD CONSTRAINT "group_deals_deal_id_deals_id_fk" FOREIGN KEY ("deal_id") REFERENCES "public"."deals"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "group_reservations" ADD CONSTRAINT "group_reservations_group_deal_id_group_deals_id_fk" FOREIGN KEY ("group_deal_id") REFERENCES "public"."group_deals"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "group_reservations" ADD CONSTRAINT "group_reservations_deal_id_deals_id_fk" FOREIGN KEY ("deal_id") REFERENCES "public"."deals"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "group_reservations" ADD CONSTRAINT "group_reservations_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "group_reservations" ADD CONSTRAINT "group_reservations_payment_method_id_payment_methods_id_fk" FOREIGN KEY ("payment_method_id") REFERENCES "public"."payment_methods"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "group_reservations" ADD CONSTRAINT "group_reservations_purchase_id_purchases_id_fk" FOREIGN KEY ("purchase_id") REFERENCES "public"."purchases"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "group_tiers" ADD CONSTRAINT "group_tiers_group_deal_id_group_deals_id_fk" FOREIGN KEY ("group_deal_id") REFERENCES "public"."group_deals"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "group_waitlist" ADD CONSTRAINT "group_waitlist_group_deal_id_group_deals_id_fk" FOREIGN KEY ("group_deal_id") REFERENCES "public"."group_deals"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "group_waitlist" ADD CONSTRAINT "group_waitlist_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "group_waitlist" ADD CONSTRAINT "group_waitlist_payment_method_id_payment_methods_id_fk" FOREIGN KEY ("payment_method_id") REFERENCES "public"."payment_methods"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
CREATE INDEX "group_deals_state_idx" ON "group_deals" USING btree ("group_state");--> statement-breakpoint
CREATE INDEX "group_deals_deal_id_idx" ON "group_deals" USING btree ("deal_id");--> statement-breakpoint
CREATE INDEX "group_reservations_group_deal_idx" ON "group_reservations" USING btree ("group_deal_id");--> statement-breakpoint
CREATE INDEX "group_reservations_user_idx" ON "group_reservations" USING btree ("user_id");--> statement-breakpoint
CREATE INDEX "group_reservations_status_idx" ON "group_reservations" USING btree ("status");--> statement-breakpoint
CREATE INDEX "group_tiers_group_deal_idx" ON "group_tiers" USING btree ("group_deal_id");--> statement-breakpoint
CREATE INDEX "group_waitlist_group_deal_idx" ON "group_waitlist" USING btree ("group_deal_id","position");