CREATE TYPE "public"."affiliate_submission_status" AS ENUM('PENDING', 'APPROVED', 'REJECTED');--> statement-breakpoint
CREATE TYPE "public"."request_status" AS ENUM('PENDING', 'FULFILLED', 'DISMISSED');--> statement-breakpoint
ALTER TYPE "public"."object_category" ADD VALUE 'Person';--> statement-breakpoint
ALTER TYPE "public"."object_category" ADD VALUE 'Apparel';--> statement-breakpoint
ALTER TYPE "public"."video_scan_status" ADD VALUE 'idle';--> statement-breakpoint
CREATE TABLE "affiliate_proposals" (
	"id" text PRIMARY KEY NOT NULL,
	"object_id" text,
	"video_id" text NOT NULL,
	"creator_id" text NOT NULL,
	"submitter_name" text,
	"submitter_email" text,
	"product_url" text NOT NULL,
	"affiliate_url" text NOT NULL,
	"product_name" text NOT NULL,
	"price" real,
	"image_url" text,
	"note" text,
	"status" "affiliate_submission_status" DEFAULT 'PENDING' NOT NULL,
	"created_at" timestamp (3) with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp (3) with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "affiliate_revenue" (
	"id" text PRIMARY KEY NOT NULL,
	"marketplace_match_id" text NOT NULL,
	"amount" real NOT NULL,
	"recorded_at" timestamp (3) with time zone DEFAULT now() NOT NULL,
	"order_id" text,
	"created_at" timestamp (3) with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp (3) with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "product_requests" (
	"id" text PRIMARY KEY NOT NULL,
	"video_id" text NOT NULL,
	"creator_id" text NOT NULL,
	"viewer_name" text,
	"viewer_email" text,
	"note" text NOT NULL,
	"image_url" text,
	"frame_timestamp" integer,
	"status" "request_status" DEFAULT 'PENDING' NOT NULL,
	"created_at" timestamp (3) with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp (3) with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
ALTER TABLE "youtube_channels" DROP CONSTRAINT "youtube_channels_channel_id_unique";--> statement-breakpoint
ALTER TABLE "youtube_videos" DROP CONSTRAINT "youtube_videos_video_id_unique";--> statement-breakpoint
DROP INDEX "interest_pledges_email_product_idx";--> statement-breakpoint
ALTER TABLE "interest_pledges" ALTER COLUMN "marketplace_match_id" DROP NOT NULL;--> statement-breakpoint
ALTER TABLE "detected_objects" ADD COLUMN "thumbnail_url" text;--> statement-breakpoint
ALTER TABLE "interest_pledges" ADD COLUMN "detected_object_id" text;--> statement-breakpoint
ALTER TABLE "marketplace_matches" ADD COLUMN "image_url" text;--> statement-breakpoint
ALTER TABLE "youtube_videos" ADD COLUMN "availability_status" text DEFAULT 'unknown' NOT NULL;--> statement-breakpoint
ALTER TABLE "youtube_videos" ADD COLUMN "scan_status" "video_scan_status" DEFAULT 'pending' NOT NULL;--> statement-breakpoint
ALTER TABLE "affiliate_proposals" ADD CONSTRAINT "affiliate_proposals_object_id_detected_objects_id_fk" FOREIGN KEY ("object_id") REFERENCES "public"."detected_objects"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "affiliate_proposals" ADD CONSTRAINT "affiliate_proposals_video_id_youtube_videos_id_fk" FOREIGN KEY ("video_id") REFERENCES "public"."youtube_videos"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "affiliate_proposals" ADD CONSTRAINT "affiliate_proposals_creator_id_users_id_fk" FOREIGN KEY ("creator_id") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "affiliate_revenue" ADD CONSTRAINT "affiliate_revenue_marketplace_match_id_marketplace_matches_id_fk" FOREIGN KEY ("marketplace_match_id") REFERENCES "public"."marketplace_matches"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "product_requests" ADD CONSTRAINT "product_requests_video_id_youtube_videos_id_fk" FOREIGN KEY ("video_id") REFERENCES "public"."youtube_videos"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "product_requests" ADD CONSTRAINT "product_requests_creator_id_users_id_fk" FOREIGN KEY ("creator_id") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
CREATE INDEX "affiliate_revenue_marketplace_match_id_idx" ON "affiliate_revenue" USING btree ("marketplace_match_id");--> statement-breakpoint
CREATE INDEX "affiliate_revenue_recorded_at_idx" ON "affiliate_revenue" USING btree ("recorded_at");--> statement-breakpoint
ALTER TABLE "interest_pledges" ADD CONSTRAINT "interest_pledges_detected_object_id_detected_objects_id_fk" FOREIGN KEY ("detected_object_id") REFERENCES "public"."detected_objects"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
CREATE INDEX "interest_pledges_detected_object_id_idx" ON "interest_pledges" USING btree ("detected_object_id");