-- Migration: Add specific PLA Basic colors
-- This migration adds the specific set of colors for PLA Basic filaments

-- First, let's add any missing colors to the colors table
INSERT INTO colors (name, hex) VALUES 
    ('Jade White', '#FFFFFF'),
    ('Beige', '#F7E6DE'),
    ('Light Gray', '#D0D2D4'),
    ('Yellow', '#F4EE2A'),
    ('Sunflower Yellow', '#FEC601'),
    ('Pumpkin Orange', '#FF8E16'),
    ('Orange', '#FF6A13'),
    ('Gold', '#E4BD68'),
    ('Bright Green', '#BDCF00'),
    ('Bambu Green', '#16C344'),
    ('Mistletoe Green', '#3F8E43'),
    ('Pink', '#F55A74'),
    ('Hot Pink', '#F5547D'),
    ('Magenta', '#EC008C'),
    ('Red', '#C12E1F'),
    ('Maroon Red', '#832140'),
    ('Purple', '#5E43B7'),
    ('Indigo Purple', '#482A60'),
    ('Turquoise', '#00B1B7'),
    ('Cyan', '#0086D6'),
    ('Cobalt Blue', '#0055B8'),
    ('Blue', '#0A2989'),
    ('Brown', '#9D432C'),
    ('Cocoa Brown', '#6F5034'),
    ('Bronze', '#847D48'),
    ('Gray', '#8E9089'),
    ('Silver', '#A6A9AA'),
    ('Blue Grey', '#5B6579'),
    ('Dark Gray', '#555555'),
    ('Black', '#000000'),
    ('Latte Brown', '#D3B7A7')
ON CONFLICT (name) 
DO UPDATE SET hex = EXCLUDED.hex;

-- Now add PLA Basic filaments for each of these colors
-- We'll add them with 1 refill and 1 spool each
INSERT INTO filaments (tip, finish, boja, boja_hex, refill, spulna, kolicina, cena)
SELECT 
    'PLA' as tip,
    'Basic' as finish,
    c.name as boja,
    c.hex as boja_hex,
    1 as refill,
    1 as spulna,
    2 as kolicina,
    '3499/3999' as cena
FROM colors c
WHERE c.name IN (
    'Jade White', 'Beige', 'Light Gray', 'Yellow', 'Sunflower Yellow',
    'Pumpkin Orange', 'Orange', 'Gold', 'Bright Green', 'Bambu Green',
    'Mistletoe Green', 'Pink', 'Hot Pink', 'Magenta', 'Red',
    'Maroon Red', 'Purple', 'Indigo Purple', 'Turquoise', 'Cyan',
    'Cobalt Blue', 'Blue', 'Brown', 'Cocoa Brown', 'Bronze',
    'Gray', 'Silver', 'Blue Grey', 'Dark Gray', 'Black', 'Latte Brown'
)
AND NOT EXISTS (
    SELECT 1 FROM filaments f 
    WHERE f.tip = 'PLA' 
    AND f.finish = 'Basic' 
    AND f.boja = c.name
);

-- Update any existing PLA Basic filaments to ensure they have correct inventory
UPDATE filaments 
SET refill = 1, spulna = 1, kolicina = 2, cena = '3499/3999'
WHERE tip = 'PLA' 
AND finish = 'Basic'
AND boja IN (
    'Jade White', 'Beige', 'Light Gray', 'Yellow', 'Sunflower Yellow',
    'Pumpkin Orange', 'Orange', 'Gold', 'Bright Green', 'Bambu Green',
    'Mistletoe Green', 'Pink', 'Hot Pink', 'Magenta', 'Red',
    'Maroon Red', 'Purple', 'Indigo Purple', 'Turquoise', 'Cyan',
    'Cobalt Blue', 'Blue', 'Brown', 'Cocoa Brown', 'Bronze',
    'Gray', 'Silver', 'Blue Grey', 'Dark Gray', 'Black', 'Latte Brown'
);

-- Zero out ALL other filaments (not PLA Basic with these specific colors)
UPDATE filaments 
SET refill = 0, spulna = 0, kolicina = 0
WHERE NOT (
    tip = 'PLA' 
    AND finish = 'Basic'
    AND boja IN (
        'Jade White', 'Beige', 'Light Gray', 'Yellow', 'Sunflower Yellow',
        'Pumpkin Orange', 'Orange', 'Gold', 'Bright Green', 'Bambu Green',
        'Mistletoe Green', 'Pink', 'Hot Pink', 'Magenta', 'Red',
        'Maroon Red', 'Purple', 'Indigo Purple', 'Turquoise', 'Cyan',
        'Cobalt Blue', 'Blue', 'Brown', 'Cocoa Brown', 'Bronze',
        'Gray', 'Silver', 'Blue Grey', 'Dark Gray', 'Black', 'Latte Brown'
    )
);