CREATE TABLE `merch_order_items` (
	`id` varchar(36) NOT NULL,
	`order_id` varchar(36) NOT NULL,
	`product_id` varchar(36),
	`variant_id` varchar(36),
	`product_name` varchar(160) NOT NULL,
	`variant_label` varchar(120) NOT NULL,
	`unit_cents` int NOT NULL,
	`quantity` int NOT NULL,
	CONSTRAINT `merch_order_items_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `merch_orders` (
	`id` varchar(36) NOT NULL,
	`shop_id` varchar(36) NOT NULL,
	`organizer_id` varchar(36) NOT NULL,
	`buyer_name` varchar(160) NOT NULL,
	`buyer_email` varchar(255) NOT NULL,
	`buyer_phone` varchar(32) NOT NULL,
	`fulfillment` enum('pickup','delivery') NOT NULL,
	`delivery_address` text,
	`delivery_city` varchar(120),
	`delivery_fee_cents` int NOT NULL DEFAULT 0,
	`subtotal_cents` int NOT NULL,
	`total_cents` int NOT NULL,
	`currency` varchar(10) NOT NULL DEFAULT 'kes',
	`status` enum('pending','paid','cancelled','refunded') NOT NULL DEFAULT 'pending',
	`fulfillment_status` enum('processing','ready','shipped','delivered','collected') NOT NULL DEFAULT 'processing',
	`tracking_note` text,
	`pickup_code` varchar(12) NOT NULL,
	`payment_method` enum('card','mpesa') NOT NULL,
	`mpesa_checkout_request_id` varchar(255),
	`mpesa_receipt` text,
	`paystack_reference` varchar(255),
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `merch_orders_id` PRIMARY KEY(`id`),
	CONSTRAINT `merch_orders_mpesa_checkout_request_id_unique` UNIQUE(`mpesa_checkout_request_id`),
	CONSTRAINT `merch_orders_paystack_reference_unique` UNIQUE(`paystack_reference`)
);
--> statement-breakpoint
CREATE TABLE `merch_products` (
	`id` varchar(36) NOT NULL,
	`shop_id` varchar(36) NOT NULL,
	`event_id` varchar(36),
	`name` varchar(160) NOT NULL,
	`description` text,
	`image_url` text,
	`price_cents` int NOT NULL,
	`active` boolean NOT NULL DEFAULT true,
	`sort_order` int NOT NULL DEFAULT 0,
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `merch_products_id` PRIMARY KEY(`id`),
	CONSTRAINT `merch_price_non_negative` CHECK(`merch_products`.`price_cents` >= 0)
);
--> statement-breakpoint
CREATE TABLE `merch_shops` (
	`id` varchar(36) NOT NULL,
	`organizer_id` varchar(36) NOT NULL,
	`slug` varchar(80) NOT NULL,
	`name` varchar(120) NOT NULL,
	`description` text,
	`banner_url` text,
	`pickup_instructions` text,
	`pickup_enabled` boolean NOT NULL DEFAULT true,
	`delivery_enabled` boolean NOT NULL DEFAULT false,
	`delivery_fee_cents` int NOT NULL DEFAULT 0,
	`open` boolean NOT NULL DEFAULT false,
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `merch_shops_id` PRIMARY KEY(`id`),
	CONSTRAINT `merch_shops_organizer_id_unique` UNIQUE(`organizer_id`),
	CONSTRAINT `merch_shops_slug_unique` UNIQUE(`slug`)
);
--> statement-breakpoint
CREATE TABLE `merch_variants` (
	`id` varchar(36) NOT NULL,
	`product_id` varchar(36) NOT NULL,
	`label` varchar(120) NOT NULL,
	`price_cents` int,
	`stock` int NOT NULL DEFAULT 0,
	`sold` int NOT NULL DEFAULT 0,
	`sort_order` int NOT NULL DEFAULT 0,
	CONSTRAINT `merch_variants_id` PRIMARY KEY(`id`),
	CONSTRAINT `merch_stock_non_negative` CHECK(`merch_variants`.`stock` >= 0)
);
--> statement-breakpoint
ALTER TABLE `merch_order_items` ADD CONSTRAINT `merch_order_items_order_id_merch_orders_id_fk` FOREIGN KEY (`order_id`) REFERENCES `merch_orders`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `merch_order_items` ADD CONSTRAINT `merch_order_items_product_id_merch_products_id_fk` FOREIGN KEY (`product_id`) REFERENCES `merch_products`(`id`) ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `merch_order_items` ADD CONSTRAINT `merch_order_items_variant_id_merch_variants_id_fk` FOREIGN KEY (`variant_id`) REFERENCES `merch_variants`(`id`) ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `merch_orders` ADD CONSTRAINT `merch_orders_shop_id_merch_shops_id_fk` FOREIGN KEY (`shop_id`) REFERENCES `merch_shops`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `merch_orders` ADD CONSTRAINT `merch_orders_organizer_id_users_id_fk` FOREIGN KEY (`organizer_id`) REFERENCES `users`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `merch_products` ADD CONSTRAINT `merch_products_shop_id_merch_shops_id_fk` FOREIGN KEY (`shop_id`) REFERENCES `merch_shops`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `merch_products` ADD CONSTRAINT `merch_products_event_id_events_id_fk` FOREIGN KEY (`event_id`) REFERENCES `events`(`id`) ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `merch_shops` ADD CONSTRAINT `merch_shops_organizer_id_users_id_fk` FOREIGN KEY (`organizer_id`) REFERENCES `users`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `merch_variants` ADD CONSTRAINT `merch_variants_product_id_merch_products_id_fk` FOREIGN KEY (`product_id`) REFERENCES `merch_products`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
CREATE INDEX `idx_merch_order_items_order` ON `merch_order_items` (`order_id`);--> statement-breakpoint
CREATE INDEX `idx_merch_orders_shop` ON `merch_orders` (`shop_id`);--> statement-breakpoint
CREATE INDEX `idx_merch_orders_organizer` ON `merch_orders` (`organizer_id`);--> statement-breakpoint
CREATE INDEX `idx_merch_products_shop` ON `merch_products` (`shop_id`);--> statement-breakpoint
CREATE INDEX `idx_merch_variants_product` ON `merch_variants` (`product_id`);