-- Migration 190: Align sag_sager status constraint with current case status model
-- Fixes PATCH /api/v1/sag/{id} failures when using statuses beyond 'åben'/'lukket'.

DO $$
DECLARE
    constraint_row RECORD;
BEGIN
    -- Drop legacy check constraints on sag_sager.status regardless of their generated name.
    FOR constraint_row IN
        SELECT c.conname
        FROM pg_constraint c
        JOIN pg_class t ON t.oid = c.conrelid
        JOIN pg_namespace n ON n.oid = t.relnamespace
        WHERE n.nspname = 'public'
          AND t.relname = 'sag_sager'
          AND c.contype = 'c'
          AND pg_get_constraintdef(c.oid) ILIKE '%status%'
    LOOP
        EXECUTE format('ALTER TABLE public.sag_sager DROP CONSTRAINT IF EXISTS %I', constraint_row.conname);
    END LOOP;
END $$;

UPDATE public.sag_sager
SET status = CASE
    WHEN lower(trim(status)) IN ('aaben', 'open') THEN 'åben'
    WHEN lower(trim(status)) IN ('i_gang', 'in_progress', 'under behandling') THEN 'under behandling'
    WHEN lower(trim(status)) IN ('on_hold', 'waiting', 'afventer') THEN 'afventer'
    WHEN lower(trim(status)) IN ('resolved', 'løst') THEN 'løst'
    WHEN lower(trim(status)) IN ('closed', 'afsluttet', 'lukket') THEN 'lukket'
    ELSE status
END;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM pg_constraint c
        JOIN pg_class t ON t.oid = c.conrelid
        JOIN pg_namespace n ON n.oid = t.relnamespace
        WHERE n.nspname = 'public'
          AND t.relname = 'sag_sager'
          AND c.conname = 'sag_sager_status_check'
    ) THEN
        ALTER TABLE public.sag_sager
            ADD CONSTRAINT sag_sager_status_check
            CHECK (status IN ('åben', 'under behandling', 'afventer', 'løst', 'lukket'));
    END IF;
END $$;