-- 1. Create table amperage_reference if not exists
CREATE TABLE IF NOT EXISTS `amperage_reference` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `category_id` INT(10) UNSIGNED NOT NULL,
  `amperage` INT(10) UNSIGNED NOT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_cat_amp` (`category_id`, `amperage`),
  KEY `idx_category` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Seed standard amperages for Motorcycle and UPS batteries
INSERT IGNORE INTO `amperages` (`amperage`, `label`, `is_active`, `sort_order`) VALUES
(3, '3 Ah', 1, 3),
(4, '4 Ah', 1, 4),
(5, '5 Ah', 1, 5),
(7, '7 Ah', 1, 7),
(9, '9 Ah', 1, 9),
(11, '11 Ah', 1, 11),
(12, '12 Ah', 1, 12),
(18, '18 Ah', 1, 18),
(28, '28 Ah', 1, 28),
(42, '42 Ah', 1, 42),
(65, '65 Ah', 1, 65);

-- 3. Seed standard case types for Motorcycle batteries
INSERT IGNORE INTO `battery_case_types` (`name`, `standard`, `display_order`) VALUES
('YTX5L-BS', 'JIS', 50),
('YTX7L-BS', 'JIS', 51),
('YTX9-BS', 'JIS', 52),
('YTX12-BS', 'JIS', 53),
('YTZ10S', 'JIS', 54),
('YB9-B', 'JIS', 55),
('YB12B-B', 'JIS', 56),
('YT12A-BS', 'JIS', 57);

-- 4. Seed category amperage references
-- Category 1: Car
INSERT IGNORE INTO `amperage_reference` (`category_id`, `amperage`) VALUES
(1, 35), (1, 45), (1, 50), (1, 55), (1, 60), (1, 62), (1, 66),
(1, 70), (1, 74), (1, 80), (1, 88), (1, 90), (1, 100), (1, 110),
(1, 120), (1, 135), (1, 150), (1, 170), (1, 190), (1, 200), (1, 220), (1, 240);

-- Category 2: UPS (includes 5 Ah)
INSERT IGNORE INTO `amperage_reference` (`category_id`, `amperage`) VALUES
(2, 5), (2, 7), (2, 9), (2, 12), (2, 18), (2, 28), (2, 42), (2, 65),
(2, 100), (2, 120), (2, 150), (2, 200);

-- Category 3: Motorcycle
INSERT IGNORE INTO `amperage_reference` (`category_id`, `amperage`) VALUES
(3, 3), (3, 4), (3, 5), (3, 7), (3, 9), (3, 11), (3, 12);

-- Category 4: Lithium (includes 5 Ah)
INSERT IGNORE INTO `amperage_reference` (`category_id`, `amperage`) VALUES
(4, 5), (4, 7), (4, 9), (4, 12), (4, 18), (4, 28), (4, 42), (4, 65),
(4, 100), (4, 120), (4, 150), (4, 200);
