CREATE TYPE "public"."store_order_status" AS ENUM('pending_invoice', 'invoiced', 'paid', 'shipped', 'delivered', 'cancelled');--> statement-breakpoint
CREATE TABLE "store_order_lines" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"order_id" uuid NOT NULL,
	"product_id" uuid,
	"product_title" text NOT NULL,
	"product_sku" text,
	"product_slug" text,
	"unit_price_minor" integer NOT NULL,
	"quantity" integer NOT NULL,
	"line_total_minor" integer NOT NULL,
	"position" integer DEFAULT 0 NOT NULL
);
--> statement-breakpoint
CREATE TABLE "store_orders" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"order_number" text NOT NULL,
	"user_id" uuid,
	"customer_name" text NOT NULL,
	"customer_email" text NOT NULL,
	"shipping_address" text NOT NULL,
	"shipping_country" text NOT NULL,
	"customer_phone" text,
	"notes" text DEFAULT '' NOT NULL,
	"subtotal_minor" integer DEFAULT 0 NOT NULL,
	"shipping_minor" integer DEFAULT 0 NOT NULL,
	"total_minor" integer DEFAULT 0 NOT NULL,
	"currency" text DEFAULT 'GBP' NOT NULL,
	"status" "store_order_status" DEFAULT 'pending_invoice' NOT NULL,
	"invoice_url" text,
	"tracking_url" text,
	"tracking_number" text,
	"admin_notes" text DEFAULT '' NOT NULL,
	"invoiced_at" timestamp,
	"paid_at" timestamp,
	"shipped_at" timestamp,
	"delivered_at" timestamp,
	"cancelled_at" timestamp,
	"created_at" timestamp DEFAULT now() NOT NULL,
	"updated_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
ALTER TABLE "store_order_lines" ADD CONSTRAINT "store_order_lines_order_id_store_orders_id_fk" FOREIGN KEY ("order_id") REFERENCES "public"."store_orders"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "store_order_lines" ADD CONSTRAINT "store_order_lines_product_id_store_products_id_fk" FOREIGN KEY ("product_id") REFERENCES "public"."store_products"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "store_orders" ADD CONSTRAINT "store_orders_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
CREATE INDEX "store_order_lines_order_idx" ON "store_order_lines" USING btree ("order_id");--> statement-breakpoint
CREATE UNIQUE INDEX "store_orders_order_number_idx" ON "store_orders" USING btree ("order_number");--> statement-breakpoint
CREATE INDEX "store_orders_user_idx" ON "store_orders" USING btree ("user_id");--> statement-breakpoint
CREATE INDEX "store_orders_status_idx" ON "store_orders" USING btree ("status");--> statement-breakpoint
CREATE INDEX "store_orders_created_idx" ON "store_orders" USING btree ("created_at");