-- Insert comprehensive vehicle part categories
-- This script is idempotent and can be run multiple times safely

-- Ensure categories table exists
CREATE TABLE IF NOT EXISTS `categories` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL UNIQUE,
  `description` TEXT NULL,
  `isActive` TINYINT(1) NOT NULL DEFAULT 1,
  `createdAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert all vehicle part categories
INSERT INTO `categories` (`name`, `description`, `isActive`) VALUES
-- ENGINE & POWERTRAIN
('engines', 'Complete engines, engine blocks, cylinder heads', 1),
('engine_components', 'Pistons, rings, crankshafts, camshafts, valves', 1),
('transmissions', 'Manual, automatic, CVT transmissions', 1),
('clutch_systems', 'Clutch plates, pressure plates, flywheels', 1),
('drivetrain', 'Drive shafts, axles, differentials, transfer cases', 1),
('turbochargers', 'Turbochargers and superchargers', 1),
('engine_mounts', 'Engine mounts and brackets', 1),

-- COOLING SYSTEM
('radiators', 'Radiators and cooling system parts', 1),
('cooling_fans', 'Cooling fans and fan assemblies', 1),
('water_pumps', 'Water pumps and components', 1),
('thermostats', 'Thermostats and temperature controls', 1),
('hoses_belts', 'Radiator hoses, heater hoses, timing belts', 1),
('coolant_reservoirs', 'Coolant reservoirs and overflow tanks', 1),

-- ELECTRICAL & LIGHTING
('headlights', 'Headlights - Halogen, LED, HID, Xenon', 1),
('taillights', 'Taillights and brake lights', 1),
('turn_signals', 'Turn signals and indicators', 1),
('fog_lights', 'Fog lights and auxiliary lighting', 1),
('interior_lights', 'Interior lighting components', 1),
('batteries', 'Car batteries and battery components', 1),
('alternators', 'Alternators and generators', 1),
('starters', 'Starters and starter motors', 1),
('ignition_systems', 'Spark plugs, coils, distributors', 1),
('wiring_harnesses', 'Wiring harnesses and electrical wiring', 1),
('fuses_relays', 'Fuses and relays', 1),
('sensors', 'O2, MAF, MAP, temperature, pressure sensors', 1),

-- BODY & EXTERIOR
('bumpers', 'Front and rear bumpers', 1),
('fenders', 'Fenders and fender panels', 1),
('hoods', 'Hoods and hood components', 1),
('doors', 'Complete doors, door panels, handles', 1),
('trunk_lids', 'Trunk lids and tailgates', 1),
('quarter_panels', 'Quarter panels and body panels', 1),
('rocker_panels', 'Rocker panels and side panels', 1),
('grilles', 'Grilles and grille components', 1),
('mirrors', 'Side mirrors and rearview mirrors', 1),
('door_handles', 'Door handles and exterior handles', 1),
('window_regulators', 'Window regulators and mechanisms', 1),
('ing', 'Weatherstripping and seals', 1),

-- GLASS & WINDOWS
('windshields', 'Windshields and front glass', 1),
('side_windows', 'Side windows and door glass', 1),
('rear_windows', 'Rear windows and back glass', 1),
('sunroofs', 'Sunroofs and moonroofs', 1),

-- SUSPENSION & STEERING
('shock_absorbers', 'Shock absorbers and struts', 1),
('springs', 'Coil springs and leaf springs', 1),
('control_arms', 'Control arms and suspension arms', 1),
('ball_joints', 'Ball joints and suspension joints', 1),
('tie_rods', 'Tie rods and steering linkages', 1),
('steering_racks', 'Steering racks and power steering pumps', 1),
('power_steering', 'Power steering components', 1),
('sway_bars', 'Sway bars and stabilizer links', 1),
('bushings', 'Bushings and suspension mounts', 1),

-- BRAKES
('brake_pads', 'Brake pads and brake shoes', 1),
('brake_rotors', 'Brake rotors and drums', 1),
('brake_calipers', 'Brake calipers and brake components', 1),
('brake_lines', 'Brake lines and hoses', 1),
('master_cylinders', 'Master cylinders and brake cylinders', 1),
('brake_boosters', 'Brake boosters and assist systems', 1),
('abs_components', 'ABS components and sensors', 1),

-- WHEELS & TIRES
('wheels', 'Alloy wheels, steel wheels, custom wheels', 1),
('tires', 'All types and sizes of tires', 1),
('wheel_bearings', 'Wheel bearings and hub assemblies', 1),
('hub_assemblies', 'Hub assemblies and wheel hubs', 1),
('lug_nuts', 'Lug nuts, bolts, and wheel hardware', 1),
('wheel_covers', 'Wheel covers and center caps', 1),

-- EXHAUST SYSTEM
('exhaust_manifolds', 'Exhaust manifolds and headers', 1),
('catalytic_converters', 'Catalytic converters and emissions', 1),
('mufflers', 'Mufflers and exhaust silencers', 1),
('exhaust_pipes', 'Exhaust pipes and tubing', 1),
('resonators', 'Resonators and exhaust components', 1),
('oxygen_sensors', 'Oxygen sensors and exhaust sensors', 1),

-- INTERIOR PARTS
('seats', 'Front and rear seats, seat covers', 1),
('dashboards', 'Dashboards and instrument panels', 1),
('center_consoles', 'Center consoles and console components', 1),
('door_panels', 'Door panels and interior trim', 1),
('headliners', 'Headliners and ceiling components', 1),
('carpet', 'Carpet and floor mats', 1),
('steering_wheels', 'Steering wheels and steering components', 1),
('airbags', 'Airbags and safety systems', 1),
('seat_belts', 'Seat belts and restraint systems', 1),
('switches', 'Interior switches and controls', 1),

-- AIR CONDITIONING & HEATING
('ac_compressors', 'AC compressors and components', 1),
('condensers', 'AC condensers and heat exchangers', 1),
('evaporators', 'Evaporators and AC components', 1),
('heater_cores', 'Heater cores and heating systems', 1),
('blower_motors', 'Blower motors and fan motors', 1),
('ac_hoses', 'AC hoses and refrigerant lines', 1),

-- FUEL SYSTEM
('fuel_pumps', 'Fuel pumps and fuel delivery', 1),
('fuel_injectors', 'Fuel injectors and injection systems', 1),
('fuel_filters', 'Fuel filters and fuel system filters', 1),
('fuel_tanks', 'Fuel tanks and fuel storage', 1),
('fuel_lines', 'Fuel lines and fuel hoses', 1),
('throttle_bodies', 'Throttle bodies and throttle components', 1),
('carburetors', 'Carburetors for older vehicles', 1),

-- TRANSMISSION & DRIVETRAIN
('transmission_mounts', 'Transmission mounts and brackets', 1),
('cv_joints', 'CV joints and CV boots', 1),
('universal_joints', 'Universal joints and U-joints', 1),
('transfer_cases', 'Transfer cases for 4WD/AWD vehicles', 1),

-- FILTERS & FLUIDS
('oil_filters', 'Oil filters and engine filters', 1),
('air_filters', 'Air filters and intake filters', 1),
('cabin_air_filters', 'Cabin air filters and interior filters', 1),
('transmission_filters', 'Transmission filters and fluid filters', 1),

-- MISCELLANEOUS
('body_kits', 'Body kits and aerodynamic parts', 1),
('spoilers', 'Spoilers and wings', 1),
('running_boards', 'Running boards and step bars', 1),
('roof_racks', 'Roof racks and cargo systems', 1),
('tow_hooks', 'Tow hooks and towing equipment', 1),
('license_plate_frames', 'License plate frames and holders', 1),
('emblems', 'Emblems and badges', 1),
('other', 'Other spare parts and custom parts', 1)

ON DUPLICATE KEY UPDATE 
  `description` = VALUES(`description`),
  `isActive` = VALUES(`isActive`),
  `updatedAt` = CURRENT_TIMESTAMP;

-- Display summary
SELECT 
  COUNT(*) as total_categories,
  SUM(CASE WHEN isActive = 1 THEN 1 ELSE 0 END) as active_categories
FROM categories;