-- Migration: Fix refill-only colors to have 0 spools instead of 1
-- These colors should only be available as refills, not as regular spools

-- First, add Nardo Gray to colors table if it doesn't exist
INSERT INTO colors (name, hex) VALUES 
    ('Nardo Gray', '#747474')
ON CONFLICT (name) DO NOTHING;

-- Update all refill-only colors to have spulna = 0
-- This ensures they can only be purchased as refills
UPDATE filaments 
SET 
    spulna = 0,
    kolicina = refill  -- Total quantity should equal refill count only
WHERE tip = 'PLA' 
AND finish = 'Basic'
AND boja IN (
    -- Colors specified by user as refill-only
    'Nardo Gray',
    'Blue Grey',     -- Note: Database uses "Blue Grey" not "Blue Gray"
    'Light Gray',
    'Brown',
    'Beige',
    'Bronze',
    'Purple',
    'Cobalt Blue',
    'Turquoise',
    'Bright Green',
    'Yellow',
    'Gold',
    'Orange',
    'Maroon Red'
);

-- Also handle any existing entries with alternate spellings
UPDATE filaments 
SET 
    spulna = 0,
    kolicina = refill
WHERE tip = 'PLA' 
AND finish = 'Basic'
AND (
    boja = 'Blue Gray'  -- Handle alternate spelling if it exists
);

-- Ensure these colors maintain their refill-only status
-- even if they don't exist yet (for future inserts)
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,
    0 as refill,      -- Start with 0 refills
    0 as spulna,      -- Always 0 spools for refill-only
    0 as kolicina,    -- Total is 0
    '3499' as cena    -- Refill price only
FROM colors c
WHERE c.name IN (
    'Nardo Gray',
    'Blue Grey',
    'Light Gray', 
    'Brown',
    'Beige',
    'Bronze',
    'Purple',
    'Cobalt Blue',
    'Turquoise',
    'Bright Green',
    'Yellow',
    'Gold',
    'Orange',
    'Maroon Red'
)
AND NOT EXISTS (
    SELECT 1 FROM filaments f 
    WHERE f.tip = 'PLA' 
    AND f.finish = 'Basic' 
    AND f.boja = c.name
);

-- Add a comment to track which colors are refill-only
COMMENT ON TABLE filaments IS 'Filament inventory. Note: The following PLA Basic colors are refill-only (spulna must always be 0): Nardo Gray, Blue Grey, Light Gray, Brown, Beige, Bronze, Purple, Cobalt Blue, Turquoise, Bright Green, Yellow, Gold, Orange, Maroon Red';