CREATE TABLE `age_groups` (
	`id` int unsigned AUTO_INCREMENT NOT NULL,
	`name` varchar(80) NOT NULL,
	`slug` varchar(100) NOT NULL,
	`min_age` int unsigned,
	`max_age` int unsigned,
	`sort_order` int unsigned NOT NULL DEFAULT 0,
	`is_active` boolean NOT NULL DEFAULT true,
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `age_groups_id` PRIMARY KEY(`id`),
	CONSTRAINT `age_groups_slug_unique` UNIQUE(`slug`)
);
--> statement-breakpoint
CREATE TABLE `auth_otps` (
	`id` int unsigned AUTO_INCREMENT NOT NULL,
	`actor_type` enum('customer','staff') NOT NULL,
	`email` varchar(255) NOT NULL,
	`code_hash` varchar(255) NOT NULL,
	`purpose` enum('signup','login','password_reset') NOT NULL DEFAULT 'login',
	`attempts` int unsigned NOT NULL DEFAULT 0,
	`expires_at` timestamp NOT NULL,
	`consumed_at` timestamp,
	`created_at` timestamp NOT NULL DEFAULT (now()),
	CONSTRAINT `auth_otps_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `brands` (
	`id` int unsigned AUTO_INCREMENT NOT NULL,
	`name` varchar(120) NOT NULL,
	`slug` varchar(140) NOT NULL,
	`description` text,
	`is_active` boolean NOT NULL DEFAULT true,
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `brands_id` PRIMARY KEY(`id`),
	CONSTRAINT `brands_slug_unique` UNIQUE(`slug`)
);
--> statement-breakpoint
CREATE TABLE `collections` (
	`id` int unsigned AUTO_INCREMENT NOT NULL,
	`name` varchar(120) NOT NULL,
	`slug` varchar(140) NOT NULL,
	`description` text,
	`is_active` boolean NOT NULL DEFAULT true,
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `collections_id` PRIMARY KEY(`id`),
	CONSTRAINT `collections_slug_unique` UNIQUE(`slug`)
);
--> statement-breakpoint
CREATE TABLE `customers` (
	`id` int unsigned AUTO_INCREMENT NOT NULL,
	`email` varchar(255) NOT NULL,
	`name` varchar(255),
	`phone_number` varchar(30),
	`email_verified_at` timestamp,
	`last_login_at` timestamp,
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `customers_id` PRIMARY KEY(`id`),
	CONSTRAINT `customers_email_unique` UNIQUE(`email`),
	CONSTRAINT `customers_phone_number_unique` UNIQUE(`phone_number`)
);
--> statement-breakpoint
CREATE TABLE `face_shapes` (
	`id` int unsigned AUTO_INCREMENT NOT NULL,
	`name` varchar(120) NOT NULL,
	`slug` varchar(140) NOT NULL,
	`is_active` boolean NOT NULL DEFAULT true,
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `face_shapes_id` PRIMARY KEY(`id`),
	CONSTRAINT `face_shapes_slug_unique` UNIQUE(`slug`)
);
--> statement-breakpoint
CREATE TABLE `frame_colors` (
	`id` int unsigned AUTO_INCREMENT NOT NULL,
	`name` varchar(80) NOT NULL,
	`slug` varchar(100) NOT NULL,
	`hex_code` varchar(7),
	`is_active` boolean NOT NULL DEFAULT true,
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `frame_colors_id` PRIMARY KEY(`id`),
	CONSTRAINT `frame_colors_slug_unique` UNIQUE(`slug`)
);
--> statement-breakpoint
CREATE TABLE `frame_shapes` (
	`id` int unsigned AUTO_INCREMENT NOT NULL,
	`name` varchar(120) NOT NULL,
	`slug` varchar(140) NOT NULL,
	`is_active` boolean NOT NULL DEFAULT true,
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `frame_shapes_id` PRIMARY KEY(`id`),
	CONSTRAINT `frame_shapes_slug_unique` UNIQUE(`slug`)
);
--> statement-breakpoint
CREATE TABLE `genders` (
	`id` int unsigned AUTO_INCREMENT NOT NULL,
	`name` varchar(80) NOT NULL,
	`slug` varchar(100) NOT NULL,
	`is_active` boolean NOT NULL DEFAULT true,
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `genders_id` PRIMARY KEY(`id`),
	CONSTRAINT `genders_slug_unique` UNIQUE(`slug`)
);
--> statement-breakpoint
CREATE TABLE `lens_features` (
	`id` int unsigned AUTO_INCREMENT NOT NULL,
	`name` varchar(140) NOT NULL,
	`slug` varchar(160) NOT NULL,
	`description` text,
	`is_active` boolean NOT NULL DEFAULT true,
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `lens_features_id` PRIMARY KEY(`id`),
	CONSTRAINT `lens_features_slug_unique` UNIQUE(`slug`)
);
--> statement-breakpoint
CREATE TABLE `media_assets` (
	`id` int unsigned AUTO_INCREMENT NOT NULL,
	`owner_type` enum('product','brand','product_type','collection','review','staff','customer') NOT NULL,
	`owner_id` int unsigned NOT NULL,
	`url` varchar(512) NOT NULL,
	`storage_key` varchar(1024),
	`file_name` varchar(255),
	`mime_type` varchar(120),
	`size_bytes` int unsigned,
	`alt_text` varchar(255),
	`sort_order` int unsigned NOT NULL DEFAULT 0,
	`is_primary` boolean NOT NULL DEFAULT false,
	`uploaded_by_staff_id` int unsigned,
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `media_assets_id` PRIMARY KEY(`id`),
	CONSTRAINT `media_assets_owner_url_unique` UNIQUE(`owner_type`,`owner_id`,`url`)
);
--> statement-breakpoint
CREATE TABLE `occasions` (
	`id` int unsigned AUTO_INCREMENT NOT NULL,
	`name` varchar(120) NOT NULL,
	`slug` varchar(140) NOT NULL,
	`description` text,
	`is_active` boolean NOT NULL DEFAULT true,
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `occasions_id` PRIMARY KEY(`id`),
	CONSTRAINT `occasions_slug_unique` UNIQUE(`slug`)
);
--> statement-breakpoint
CREATE TABLE `order_items` (
	`id` int unsigned AUTO_INCREMENT NOT NULL,
	`order_id` int unsigned NOT NULL,
	`product_id` int unsigned,
	`product_name` varchar(255) NOT NULL,
	`sku` varchar(80) NOT NULL,
	`quantity` int unsigned NOT NULL,
	`unit_price` int unsigned NOT NULL,
	`discount_amount` int unsigned NOT NULL DEFAULT 0,
	`line_total` int unsigned NOT NULL,
	`created_at` timestamp NOT NULL DEFAULT (now()),
	CONSTRAINT `order_items_id` PRIMARY KEY(`id`),
	CONSTRAINT `order_items_order_sku_unique` UNIQUE(`order_id`,`sku`)
);
--> statement-breakpoint
CREATE TABLE `orders` (
	`id` int unsigned AUTO_INCREMENT NOT NULL,
	`order_number` varchar(40) NOT NULL,
	`customer_id` int unsigned NOT NULL,
	`managed_by_staff_id` int unsigned,
	`status` enum('pending','confirmed','processing','ready_for_pickup','out_for_delivery','completed','cancelled','refunded') NOT NULL DEFAULT 'pending',
	`fulfillment_method` enum('pickup','delivery') NOT NULL,
	`contact_phone` varchar(30) NOT NULL,
	`delivery_address` text,
	`subtotal` int unsigned NOT NULL,
	`total_discount` int unsigned NOT NULL DEFAULT 0,
	`total_amount` int unsigned NOT NULL,
	`payment_method` enum('cash_on_delivery','online','bank_transfer') NOT NULL DEFAULT 'cash_on_delivery',
	`payment_status` enum('pending','paid','failed','refunded') NOT NULL DEFAULT 'pending',
	`customer_notes` text,
	`staff_notes` text,
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `orders_id` PRIMARY KEY(`id`),
	CONSTRAINT `orders_order_number_unique` UNIQUE(`order_number`)
);
--> statement-breakpoint
CREATE TABLE `price_ranges` (
	`id` int unsigned AUTO_INCREMENT NOT NULL,
	`name` varchar(120) NOT NULL,
	`slug` varchar(140) NOT NULL,
	`min_price` int unsigned NOT NULL,
	`max_price` int unsigned,
	`sort_order` int unsigned NOT NULL DEFAULT 0,
	`is_active` boolean NOT NULL DEFAULT true,
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `price_ranges_id` PRIMARY KEY(`id`),
	CONSTRAINT `price_ranges_slug_unique` UNIQUE(`slug`)
);
--> statement-breakpoint
CREATE TABLE `product_face_shapes` (
	`product_id` int unsigned NOT NULL,
	`face_shape_id` int unsigned NOT NULL,
	CONSTRAINT `product_face_shapes_product_id_face_shape_id_pk` PRIMARY KEY(`product_id`,`face_shape_id`)
);
--> statement-breakpoint
CREATE TABLE `product_genders` (
	`product_id` int unsigned NOT NULL,
	`gender_id` int unsigned NOT NULL,
	CONSTRAINT `product_genders_product_id_gender_id_pk` PRIMARY KEY(`product_id`,`gender_id`)
);
--> statement-breakpoint
CREATE TABLE `product_lens_features` (
	`product_id` int unsigned NOT NULL,
	`lens_feature_id` int unsigned NOT NULL,
	CONSTRAINT `product_lens_features_product_id_lens_feature_id_pk` PRIMARY KEY(`product_id`,`lens_feature_id`)
);
--> statement-breakpoint
CREATE TABLE `product_occasions` (
	`product_id` int unsigned NOT NULL,
	`occasion_id` int unsigned NOT NULL,
	CONSTRAINT `product_occasions_product_id_occasion_id_pk` PRIMARY KEY(`product_id`,`occasion_id`)
);
--> statement-breakpoint
CREATE TABLE `product_reviews` (
	`id` int unsigned AUTO_INCREMENT NOT NULL,
	`product_id` int unsigned NOT NULL,
	`customer_id` int unsigned NOT NULL,
	`rating` int unsigned NOT NULL,
	`title` varchar(160),
	`comment` text,
	`is_approved` boolean NOT NULL DEFAULT false,
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `product_reviews_id` PRIMARY KEY(`id`),
	CONSTRAINT `product_reviews_customer_product_unique` UNIQUE(`product_id`,`customer_id`)
);
--> statement-breakpoint
CREATE TABLE `product_types` (
	`id` int unsigned AUTO_INCREMENT NOT NULL,
	`name` varchar(120) NOT NULL,
	`slug` varchar(140) NOT NULL,
	`description` text,
	`is_active` boolean NOT NULL DEFAULT true,
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `product_types_id` PRIMARY KEY(`id`),
	CONSTRAINT `product_types_slug_unique` UNIQUE(`slug`)
);
--> statement-breakpoint
CREATE TABLE `products` (
	`id` int unsigned AUTO_INCREMENT NOT NULL,
	`sku` varchar(80) NOT NULL,
	`name` varchar(255) NOT NULL,
	`slug` varchar(280) NOT NULL,
	`description` text,
	`brand_id` int unsigned,
	`product_type_id` int unsigned,
	`collection_id` int unsigned,
	`frame_shape_id` int unsigned,
	`frame_color_id` int unsigned,
	`age_group_id` int unsigned,
	`size_id` int unsigned,
	`base_price` int unsigned NOT NULL,
	`discount_percent` int unsigned NOT NULL DEFAULT 0,
	`sale_price` int unsigned NOT NULL,
	`stock_quantity` int unsigned NOT NULL DEFAULT 0,
	`status` enum('draft','active','archived','out_of_stock') NOT NULL DEFAULT 'draft',
	`is_featured` boolean NOT NULL DEFAULT false,
	`average_rating` decimal(3,2) NOT NULL DEFAULT '0.00',
	`rating_count` int unsigned NOT NULL DEFAULT 0,
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `products_id` PRIMARY KEY(`id`),
	CONSTRAINT `products_sku_unique` UNIQUE(`sku`),
	CONSTRAINT `products_slug_unique` UNIQUE(`slug`)
);
--> statement-breakpoint
CREATE TABLE `sizes` (
	`id` int unsigned AUTO_INCREMENT NOT NULL,
	`name` varchar(20) NOT NULL,
	`slug` varchar(40) NOT NULL,
	`sort_order` int unsigned NOT NULL DEFAULT 0,
	`is_active` boolean NOT NULL DEFAULT true,
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `sizes_id` PRIMARY KEY(`id`),
	CONSTRAINT `sizes_slug_unique` UNIQUE(`slug`)
);
--> statement-breakpoint
CREATE TABLE `staff_users` (
	`id` int unsigned AUTO_INCREMENT NOT NULL,
	`name` varchar(255) NOT NULL,
	`email` varchar(255) NOT NULL,
	`password_hash` varchar(255) NOT NULL,
	`role` enum('seller','admin') NOT NULL DEFAULT 'seller',
	`is_active` boolean NOT NULL DEFAULT true,
	`email_verified_at` timestamp,
	`last_login_at` timestamp,
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `staff_users_id` PRIMARY KEY(`id`),
	CONSTRAINT `staff_users_email_unique` UNIQUE(`email`)
);
--> statement-breakpoint
ALTER TABLE `media_assets` ADD CONSTRAINT `media_assets_uploaded_by_staff_id_staff_users_id_fk` FOREIGN KEY (`uploaded_by_staff_id`) REFERENCES `staff_users`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `order_items` ADD CONSTRAINT `order_items_order_id_orders_id_fk` FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `order_items` ADD CONSTRAINT `order_items_product_id_products_id_fk` FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `orders` ADD CONSTRAINT `orders_customer_id_customers_id_fk` FOREIGN KEY (`customer_id`) REFERENCES `customers`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `orders` ADD CONSTRAINT `orders_managed_by_staff_id_staff_users_id_fk` FOREIGN KEY (`managed_by_staff_id`) REFERENCES `staff_users`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `product_face_shapes` ADD CONSTRAINT `product_face_shapes_product_id_products_id_fk` FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `product_face_shapes` ADD CONSTRAINT `product_face_shapes_face_shape_id_face_shapes_id_fk` FOREIGN KEY (`face_shape_id`) REFERENCES `face_shapes`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `product_genders` ADD CONSTRAINT `product_genders_product_id_products_id_fk` FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `product_genders` ADD CONSTRAINT `product_genders_gender_id_genders_id_fk` FOREIGN KEY (`gender_id`) REFERENCES `genders`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `product_lens_features` ADD CONSTRAINT `product_lens_features_product_id_products_id_fk` FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `product_lens_features` ADD CONSTRAINT `product_lens_features_lens_feature_id_lens_features_id_fk` FOREIGN KEY (`lens_feature_id`) REFERENCES `lens_features`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `product_occasions` ADD CONSTRAINT `product_occasions_product_id_products_id_fk` FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `product_occasions` ADD CONSTRAINT `product_occasions_occasion_id_occasions_id_fk` FOREIGN KEY (`occasion_id`) REFERENCES `occasions`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `product_reviews` ADD CONSTRAINT `product_reviews_product_id_products_id_fk` FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `product_reviews` ADD CONSTRAINT `product_reviews_customer_id_customers_id_fk` FOREIGN KEY (`customer_id`) REFERENCES `customers`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `products` ADD CONSTRAINT `products_brand_id_brands_id_fk` FOREIGN KEY (`brand_id`) REFERENCES `brands`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `products` ADD CONSTRAINT `products_product_type_id_product_types_id_fk` FOREIGN KEY (`product_type_id`) REFERENCES `product_types`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `products` ADD CONSTRAINT `products_collection_id_collections_id_fk` FOREIGN KEY (`collection_id`) REFERENCES `collections`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `products` ADD CONSTRAINT `products_frame_shape_id_frame_shapes_id_fk` FOREIGN KEY (`frame_shape_id`) REFERENCES `frame_shapes`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `products` ADD CONSTRAINT `products_frame_color_id_frame_colors_id_fk` FOREIGN KEY (`frame_color_id`) REFERENCES `frame_colors`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `products` ADD CONSTRAINT `products_age_group_id_age_groups_id_fk` FOREIGN KEY (`age_group_id`) REFERENCES `age_groups`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `products` ADD CONSTRAINT `products_size_id_sizes_id_fk` FOREIGN KEY (`size_id`) REFERENCES `sizes`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
CREATE INDEX `auth_otps_email_idx` ON `auth_otps` (`email`);--> statement-breakpoint
CREATE INDEX `auth_otps_lookup_idx` ON `auth_otps` (`actor_type`,`email`,`purpose`);--> statement-breakpoint
CREATE INDEX `media_assets_owner_idx` ON `media_assets` (`owner_type`,`owner_id`);--> statement-breakpoint
CREATE INDEX `media_assets_uploaded_by_staff_idx` ON `media_assets` (`uploaded_by_staff_id`);--> statement-breakpoint
CREATE INDEX `order_items_order_idx` ON `order_items` (`order_id`);--> statement-breakpoint
CREATE INDEX `order_items_product_idx` ON `order_items` (`product_id`);--> statement-breakpoint
CREATE INDEX `orders_customer_idx` ON `orders` (`customer_id`);--> statement-breakpoint
CREATE INDEX `orders_status_idx` ON `orders` (`status`);--> statement-breakpoint
CREATE INDEX `orders_managed_by_staff_idx` ON `orders` (`managed_by_staff_id`);--> statement-breakpoint
CREATE INDEX `product_reviews_product_idx` ON `product_reviews` (`product_id`);--> statement-breakpoint
CREATE INDEX `product_reviews_customer_idx` ON `product_reviews` (`customer_id`);--> statement-breakpoint
CREATE INDEX `products_brand_idx` ON `products` (`brand_id`);--> statement-breakpoint
CREATE INDEX `products_product_type_idx` ON `products` (`product_type_id`);--> statement-breakpoint
CREATE INDEX `products_collection_idx` ON `products` (`collection_id`);--> statement-breakpoint
CREATE INDEX `products_price_idx` ON `products` (`sale_price`);--> statement-breakpoint
CREATE INDEX `products_status_idx` ON `products` (`status`);