-- ============================================
-- FIX STORE CATEGORY ASSIGNMENTS
-- ============================================
-- This script fixes stores that are incorrectly assigned to categories
-- Category IDs:
-- 1 = Beauty & Health
-- 2 = Fashion
-- ============================================

-- Fix Blackberrys (Fashion brand, currently in Beauty & Health)
UPDATE stores SET store_category = 2 WHERE store_id = 6 AND store_title = 'Blackberrys';
DELETE FROM store_categories WHERE store_id = 6;
INSERT INTO store_categories (store_id, category_id) VALUES (6, 2);

-- Fix Go Colors (Fashion brand, currently in Beauty & Health)
UPDATE stores SET store_category = 2 WHERE store_id = 21 AND store_title = 'Go Colors';
DELETE FROM store_categories WHERE store_id = 21;
INSERT INTO store_categories (store_id, category_id) VALUES (21, 2);

-- Fix Montecarlo (Fashion brand, currently in Beauty & Health)
UPDATE stores SET store_category = 2 WHERE store_id = 40 AND store_title = 'Montecarlo';
DELETE FROM store_categories WHERE store_id = 40;
INSERT INTO store_categories (store_id, category_id) VALUES (40, 2);

-- Fix Boat (Electronics/Accessories, currently in Fashion - should be Electronics)
UPDATE stores SET store_category = 7 WHERE store_id = 99 AND store_title = 'Boat';
DELETE FROM store_categories WHERE store_id = 99;
INSERT INTO store_categories (store_id, category_id) VALUES (99, 7);

-- Fix Innisfree (Beauty brand, currently in Fashion - should be Beauty & Health)
UPDATE stores SET store_category = 1 WHERE store_id = 100 AND store_title = 'Innisfree';
DELETE FROM store_categories WHERE store_id = 100;
INSERT INTO store_categories (store_id, category_id) VALUES (100, 1);

-- Fix House of Masaba (Fashion brand, currently in Beauty & Health)
UPDATE stores SET store_category = 2 WHERE store_id = 106 AND store_title = 'House of Masaba';
DELETE FROM store_categories WHERE store_id = 106;
INSERT INTO store_categories (store_id, category_id) VALUES (106, 2);

-- Verify the changes
SELECT store_id, store_title, store_category, 
       CASE store_category 
           WHEN 1 THEN 'Beauty & Health'
           WHEN 2 THEN 'Fashion'
           WHEN 7 THEN 'Electronics'
           ELSE 'Other'
       END as category_name
FROM stores 
WHERE store_id IN (6, 21, 40, 99, 100, 106);

-- Check store_categories junction table
SELECT sc.store_id, s.store_title, sc.category_id, c.category_title
FROM store_categories sc
JOIN stores s ON sc.store_id = s.store_id
JOIN categories c ON sc.category_id = c.category_id
WHERE sc.store_id IN (6, 21, 40, 99, 100, 106)
ORDER BY sc.store_id;
