-- Standardize data types for refill and spulna columns
-- First, convert existing string values to integers

-- Create temporary columns
ALTER TABLE filaments ADD COLUMN refill_new INTEGER DEFAULT 0;
ALTER TABLE filaments ADD COLUMN spulna_new INTEGER DEFAULT 0;

-- Convert refill values
UPDATE filaments 
SET refill_new = CASE 
  WHEN refill ~ '^\d+$' THEN CAST(refill AS INTEGER)
  WHEN LOWER(refill) = 'da' THEN 1
  ELSE 0
END;

-- Convert spulna values (extract number from "X spulna" format)
UPDATE filaments 
SET spulna_new = CASE 
  WHEN spulna ~ '^(\d+)\s*spuln' THEN 
    CAST(SUBSTRING(spulna FROM '^(\d+)\s*spuln') AS INTEGER)
  WHEN spulna ~ '^\d+$' THEN CAST(spulna AS INTEGER)
  ELSE 0
END;

-- Drop old columns and rename new ones
ALTER TABLE filaments DROP COLUMN refill;
ALTER TABLE filaments DROP COLUMN spulna;
ALTER TABLE filaments RENAME COLUMN refill_new TO refill;
ALTER TABLE filaments RENAME COLUMN spulna_new TO spulna;

-- Update kolicina to ensure it matches refill + spulna
UPDATE filaments SET kolicina = refill + spulna;

-- Add check constraint to ensure kolicina is always the sum of refill and spulna
ALTER TABLE filaments ADD CONSTRAINT check_kolicina 
  CHECK (kolicina = refill + spulna);