CREATE TYPE "public"."draw_status" AS ENUM('committed', 'revealed', 'verified');--> statement-breakpoint
CREATE TYPE "public"."payment_method" AS ENUM('yape', 'card', 'pago_efectivo', 'other');--> statement-breakpoint
CREATE TYPE "public"."purchase_status" AS ENUM('pending', 'paid', 'failed', 'expired', 'refunded');--> statement-breakpoint
CREATE TYPE "public"."raffle_status" AS ENUM('draft', 'open', 'closed', 'drawn');--> statement-breakpoint
CREATE TABLE "audit_log" (
	"id" bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "audit_log_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807 START WITH 1 CACHE 1),
	"entity" text NOT NULL,
	"entity_id" text NOT NULL,
	"action" text NOT NULL,
	"actor" text NOT NULL,
	"detail" jsonb,
	"at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "draws" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"raffle_id" uuid NOT NULL,
	"status" "draw_status" DEFAULT 'committed' NOT NULL,
	"server_seed_hash" text NOT NULL,
	"drand_round" bigint NOT NULL,
	"snapshot_ticket_count" integer NOT NULL,
	"snapshot_hash" text NOT NULL,
	"committed_at" timestamp with time zone DEFAULT now() NOT NULL,
	"server_seed" text,
	"drand_randomness" text,
	"final_seed" text,
	"revealed_at" timestamp with time zone,
	CONSTRAINT "draws_raffle_id_unique" UNIQUE("raffle_id")
);
--> statement-breakpoint
CREATE TABLE "participants" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"full_name" text NOT NULL,
	"phone_e164" text NOT NULL,
	"email_normalized" text NOT NULL,
	"identity_hash" text NOT NULL,
	"data_consent_at" timestamp with time zone NOT NULL,
	"terms_version" text NOT NULL,
	"marketing_consent" boolean DEFAULT false NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "pricing_tiers" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"raffle_id" uuid NOT NULL,
	"label" text NOT NULL,
	"extra_chances" integer NOT NULL,
	"extra_price_cents" integer NOT NULL,
	"sort_order" integer NOT NULL
);
--> statement-breakpoint
CREATE TABLE "prizes" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"raffle_id" uuid NOT NULL,
	"position" integer NOT NULL,
	"name" text NOT NULL,
	"description" text,
	"market_value_cents" integer,
	"image_url" text,
	"sponsor_name" text,
	"is_confirmed" boolean DEFAULT false NOT NULL
);
--> statement-breakpoint
CREATE TABLE "purchases" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"raffle_id" uuid NOT NULL,
	"participant_id" uuid NOT NULL,
	"status" "purchase_status" DEFAULT 'pending' NOT NULL,
	"chances" integer NOT NULL,
	"amount_cents" integer NOT NULL,
	"fee_cover_cents" integer DEFAULT 0 NOT NULL,
	"currency" text DEFAULT 'PEN' NOT NULL,
	"method" "payment_method",
	"idempotency_key" uuid NOT NULL,
	"provider_name" text NOT NULL,
	"provider_charge_id" text,
	"provider_status" text,
	"utm_source" text,
	"utm_campaign" text,
	"influencer_code" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"paid_at" timestamp with time zone,
	"expires_at" timestamp with time zone
);
--> statement-breakpoint
CREATE TABLE "raffles" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"slug" text NOT NULL,
	"name" text NOT NULL,
	"status" "raffle_status" DEFAULT 'draft' NOT NULL,
	"opens_at" timestamp with time zone NOT NULL,
	"closes_at" timestamp with time zone NOT NULL,
	"draw_at" timestamp with time zone NOT NULL,
	"base_price_cents" integer NOT NULL,
	"max_chances_per_purchase" integer DEFAULT 4 NOT NULL,
	"fee_opt_in_enabled" boolean DEFAULT true NOT NULL,
	"bases_url" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "raffles_slug_unique" UNIQUE("slug")
);
--> statement-breakpoint
CREATE TABLE "tickets" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"raffle_id" uuid NOT NULL,
	"purchase_id" uuid NOT NULL,
	"participant_id" uuid NOT NULL,
	"ticket_no" bigint NOT NULL,
	"voided" boolean DEFAULT false NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "webhook_events" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"provider" text NOT NULL,
	"provider_event_id" text NOT NULL,
	"event_type" text NOT NULL,
	"payload" jsonb NOT NULL,
	"signature_valid" boolean NOT NULL,
	"processed_at" timestamp with time zone,
	"received_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "winners" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"draw_id" uuid NOT NULL,
	"prize_id" uuid NOT NULL,
	"ticket_id" uuid NOT NULL,
	"participant_id" uuid NOT NULL,
	"selection_index" integer NOT NULL,
	"dni" text,
	"prize_claimed_at" timestamp with time zone
);
--> statement-breakpoint
ALTER TABLE "draws" ADD CONSTRAINT "draws_raffle_id_raffles_id_fk" FOREIGN KEY ("raffle_id") REFERENCES "public"."raffles"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "pricing_tiers" ADD CONSTRAINT "pricing_tiers_raffle_id_raffles_id_fk" FOREIGN KEY ("raffle_id") REFERENCES "public"."raffles"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "prizes" ADD CONSTRAINT "prizes_raffle_id_raffles_id_fk" FOREIGN KEY ("raffle_id") REFERENCES "public"."raffles"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "purchases" ADD CONSTRAINT "purchases_raffle_id_raffles_id_fk" FOREIGN KEY ("raffle_id") REFERENCES "public"."raffles"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "purchases" ADD CONSTRAINT "purchases_participant_id_participants_id_fk" FOREIGN KEY ("participant_id") REFERENCES "public"."participants"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "tickets" ADD CONSTRAINT "tickets_raffle_id_raffles_id_fk" FOREIGN KEY ("raffle_id") REFERENCES "public"."raffles"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "tickets" ADD CONSTRAINT "tickets_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 "tickets" ADD CONSTRAINT "tickets_participant_id_participants_id_fk" FOREIGN KEY ("participant_id") REFERENCES "public"."participants"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "winners" ADD CONSTRAINT "winners_draw_id_draws_id_fk" FOREIGN KEY ("draw_id") REFERENCES "public"."draws"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "winners" ADD CONSTRAINT "winners_prize_id_prizes_id_fk" FOREIGN KEY ("prize_id") REFERENCES "public"."prizes"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "winners" ADD CONSTRAINT "winners_ticket_id_tickets_id_fk" FOREIGN KEY ("ticket_id") REFERENCES "public"."tickets"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "winners" ADD CONSTRAINT "winners_participant_id_participants_id_fk" FOREIGN KEY ("participant_id") REFERENCES "public"."participants"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
CREATE INDEX "audit_entity_idx" ON "audit_log" USING btree ("entity","entity_id");--> statement-breakpoint
CREATE INDEX "audit_at_idx" ON "audit_log" USING btree ("at");--> statement-breakpoint
CREATE UNIQUE INDEX "participants_identity_uq" ON "participants" USING btree ("identity_hash");--> statement-breakpoint
CREATE INDEX "participants_phone_idx" ON "participants" USING btree ("phone_e164");--> statement-breakpoint
CREATE INDEX "participants_email_idx" ON "participants" USING btree ("email_normalized");--> statement-breakpoint
CREATE INDEX "prizes_raffle_idx" ON "prizes" USING btree ("raffle_id","position");--> statement-breakpoint
CREATE UNIQUE INDEX "purchases_idem_uq" ON "purchases" USING btree ("idempotency_key");--> statement-breakpoint
CREATE UNIQUE INDEX "purchases_charge_uq" ON "purchases" USING btree ("provider_charge_id");--> statement-breakpoint
CREATE INDEX "purchases_raffle_status_idx" ON "purchases" USING btree ("raffle_id","status");--> statement-breakpoint
CREATE INDEX "purchases_participant_idx" ON "purchases" USING btree ("participant_id");--> statement-breakpoint
CREATE UNIQUE INDEX "tickets_raffle_no_uq" ON "tickets" USING btree ("raffle_id","ticket_no");--> statement-breakpoint
CREATE INDEX "tickets_raffle_active_idx" ON "tickets" USING btree ("raffle_id","voided");--> statement-breakpoint
CREATE INDEX "tickets_purchase_idx" ON "tickets" USING btree ("purchase_id");--> statement-breakpoint
CREATE UNIQUE INDEX "webhook_provider_event_uq" ON "webhook_events" USING btree ("provider","provider_event_id");--> statement-breakpoint
CREATE UNIQUE INDEX "winners_prize_uq" ON "winners" USING btree ("prize_id");--> statement-breakpoint
CREATE UNIQUE INDEX "winners_ticket_uq" ON "winners" USING btree ("draw_id","ticket_id");