-- Dynamic Garment & Measurement Builder — run AFTER the previous SQL files
-- (needs shops table to already exist)

-- ============================================
-- 1. GARMENT TYPES (master list)
-- ============================================
CREATE TABLE IF NOT EXISTS `garment_types` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `code` VARCHAR(100) NOT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `garment_types_code_unique` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- 2. MEASUREMENT FIELDS (master catalog)
-- ============================================
CREATE TABLE IF NOT EXISTS `measurement_fields` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `code` VARCHAR(100) NOT NULL,
  `unit` VARCHAR(20) NOT NULL DEFAULT 'inch',
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `measurement_fields_code_unique` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- 3. SHOP_GARMENTS (which garments a shop offers)
-- ============================================
CREATE TABLE IF NOT EXISTS `shop_garments` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `shop_id` BIGINT UNSIGNED NOT NULL,
  `garment_type_id` BIGINT UNSIGNED NOT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `shop_garments_shop_garment_unique` (`shop_id`, `garment_type_id`),
  CONSTRAINT `shop_garments_shop_id_foreign` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`) ON DELETE CASCADE,
  CONSTRAINT `shop_garments_garment_type_id_foreign` FOREIGN KEY (`garment_type_id`) REFERENCES `garment_types` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- 4. SHOP_GARMENT_FIELDS (the dynamic builder config)
-- ============================================
CREATE TABLE IF NOT EXISTS `shop_garment_fields` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `shop_id` BIGINT UNSIGNED NOT NULL,
  `garment_type_id` BIGINT UNSIGNED NOT NULL,
  `measurement_field_id` BIGINT UNSIGNED NOT NULL,
  `custom_label` VARCHAR(255) NULL DEFAULT NULL,
  `display_order` INT UNSIGNED NOT NULL DEFAULT 0,
  `is_required` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `shop_garment_field_unique` (`shop_id`, `garment_type_id`, `measurement_field_id`),
  CONSTRAINT `sgf_shop_id_foreign` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`) ON DELETE CASCADE,
  CONSTRAINT `sgf_garment_type_id_foreign` FOREIGN KEY (`garment_type_id`) REFERENCES `garment_types` (`id`) ON DELETE CASCADE,
  CONSTRAINT `sgf_measurement_field_id_foreign` FOREIGN KEY (`measurement_field_id`) REFERENCES `measurement_fields` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- 5. SEED GARMENT TYPES
-- ============================================
INSERT INTO `garment_types` (`name`, `code`, `created_at`, `updated_at`) VALUES
('Shirt', 'shirt', NOW(), NOW()),
('Pant', 'pant', NOW(), NOW()),
('Blouse', 'blouse', NOW(), NOW()),
('Chudithar', 'chudithar', NOW(), NOW()),
('Panel Maxi', 'panel_maxi', NOW(), NOW()),
('Frock', 'frock', NOW(), NOW()),
('Nighty', 'nighty', NOW(), NOW()),
('Kurti', 'kurti', NOW(), NOW()),
('Saree Blouse', 'saree_blouse', NOW(), NOW());

-- ============================================
-- 6. SEED MEASUREMENT FIELDS
-- ============================================
INSERT INTO `measurement_fields` (`name`, `code`, `unit`, `created_at`, `updated_at`) VALUES
('Chest', 'chest', 'inch', NOW(), NOW()),
('Waist', 'waist', 'inch', NOW(), NOW()),
('Hip', 'hip', 'inch', NOW(), NOW()),
('Shoulder', 'shoulder', 'inch', NOW(), NOW()),
('Sleeve Length', 'sleeve_length', 'inch', NOW(), NOW()),
('Shirt/Top Length', 'shirt_length', 'inch', NOW(), NOW()),
('Neck', 'neck', 'inch', NOW(), NOW()),
('Armhole', 'armhole', 'inch', NOW(), NOW()),
('Bicep', 'bicep', 'inch', NOW(), NOW()),
('Wrist', 'wrist', 'inch', NOW(), NOW()),
('Pant Length', 'pant_length', 'inch', NOW(), NOW()),
('Inseam', 'inseam', 'inch', NOW(), NOW()),
('Thigh', 'thigh', 'inch', NOW(), NOW()),
('Knee', 'knee', 'inch', NOW(), NOW()),
('Bottom Width', 'bottom', 'inch', NOW(), NOW()),
('Blouse Length', 'blouse_length', 'inch', NOW(), NOW()),
('Front Neck Depth', 'front_neck_depth', 'inch', NOW(), NOW()),
('Back Neck Depth', 'back_neck_depth', 'inch', NOW(), NOW()),
('Cup Size', 'cup_size', 'inch', NOW(), NOW()),
('Dart Point', 'dart_point', 'inch', NOW(), NOW()),
('Kurti Length', 'kurti_length', 'inch', NOW(), NOW()),
('Slit Length', 'slit_length', 'inch', NOW(), NOW());