-- Order Management feature — run AFTER etailorz_garment_builder.sql
-- (needs shops, garment_types, and orders tables to already exist)

-- ============================================
-- 1. CUSTOMERS
-- ============================================
CREATE TABLE IF NOT EXISTS `customers` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `shop_id` BIGINT UNSIGNED NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `phone` VARCHAR(20) NULL DEFAULT NULL,
  `address` VARCHAR(255) NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `customers_shop_id_foreign` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- 2. ADD PAYMENT/DELIVERY FIELDS TO orders
-- ============================================
ALTER TABLE `orders`
  ADD CONSTRAINT `orders_customer_id_foreign` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL,
  ADD COLUMN `discount` DECIMAL(10,2) NOT NULL DEFAULT 0.00 AFTER `total_amount`,
  ADD COLUMN `advance_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00 AFTER `discount`,
  ADD COLUMN `delivery_date` DATE NULL DEFAULT NULL AFTER `advance_amount`,
  ADD COLUMN `notes` TEXT NULL DEFAULT NULL AFTER `delivery_date`;

-- ============================================
-- 3. ORDER ITEMS (one row per garment in an order)
-- ============================================
CREATE TABLE IF NOT EXISTS `order_items` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_id` BIGINT UNSIGNED NOT NULL,
  `garment_type_id` BIGINT UNSIGNED NOT NULL,
  `age_type` VARCHAR(50) NULL DEFAULT NULL,
  `measurements` JSON NULL DEFAULT NULL,
  `notes` TEXT NULL DEFAULT NULL,
  `qty` INT UNSIGNED NOT NULL DEFAULT 1,
  `unit_price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `addon_total` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `total_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `order_items_order_id_foreign` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
  CONSTRAINT `order_items_garment_type_id_foreign` FOREIGN KEY (`garment_type_id`) REFERENCES `garment_types` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;