-- Migration: Add name column to products table and remove product_type
-- This adds a proper product name field separate from type

-- Step 1: Add name column to products table
ALTER TABLE `products`
ADD COLUMN `name` VARCHAR(255) NULL AFTER `part_number`;

-- Step 2: Copy existing product_type values to name column to preserve data
-- If you have existing products, this will copy the type name as the product name
UPDATE `products` p
LEFT JOIN `types` t ON p.type_id = t.id
SET p.`name` = COALESCE(t.name, p.product_type)
WHERE p.`name` IS NULL;

-- Step 3: Make name column NOT NULL after data is migrated
ALTER TABLE `products`
MODIFY COLUMN `name` VARCHAR(255) NOT NULL;

-- Step 4: Remove the old product_type column
ALTER TABLE `products`
DROP COLUMN `product_type`;
ALTER TABLE `products`
    ADD COLUMN `name` VARCHAR(255) NULL AFTER `part_number`;
-- Note: The structure is now:
-- - name: Product name (e.g., "Safety Goggles - Clear Lens")
-- - type_id: Links to types table (e.g., "Safety Goggles")
-- - category_id: Links to categories table (e.g., "PPE")
-- - Types table has category_id, creating the nested relationship
