-- Corrigir constraints da tabela configuracoes

-- 1. Remover a constraint incorreta (configuracoes_tipo_key)
DO $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM pg_constraint 
        WHERE conname = 'configuracoes_tipo_key'
        AND conrelid = 'public.configuracoes'::regclass
    ) THEN
        ALTER TABLE public.configuracoes DROP CONSTRAINT configuracoes_tipo_key;
        RAISE NOTICE 'Constraint configuracoes_tipo_key removida';
    END IF;
END $$;

-- 2. Garantir que existe constraint UNIQUE na coluna 'chave'
DO $$
BEGIN
    -- Primeiro remover duplicados na coluna 'chave' (mantendo o mais recente)
    DELETE FROM public.configuracoes
    WHERE id IN (
        SELECT id
        FROM (
            SELECT id, chave,
                   ROW_NUMBER() OVER (PARTITION BY chave ORDER BY updated_at DESC NULLS LAST, created_at DESC) as rn
            FROM public.configuracoes
            WHERE chave IS NOT NULL
        ) t
        WHERE rn > 1
    );
    RAISE NOTICE 'Duplicados removidos da coluna chave';
    
    -- Adicionar constraint UNIQUE em 'chave' se não existir
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint 
        WHERE conname = 'configuracoes_chave_key'
        AND conrelid = 'public.configuracoes'::regclass
    ) THEN
        ALTER TABLE public.configuracoes ADD CONSTRAINT configuracoes_chave_key UNIQUE (chave);
        RAISE NOTICE 'Constraint UNIQUE adicionada na coluna chave';
    END IF;
END $$;

-- 3. Verificar constraints existentes
SELECT 
    conname as constraint_name,
    contype as constraint_type,
    a.attname as column_name
FROM pg_constraint c
JOIN pg_attribute a ON a.attnum = ANY(c.conkey) AND a.attrelid = c.conrelid
WHERE c.conrelid = 'public.configuracoes'::regclass
ORDER BY conname;

-- 4. Verificar estrutura final
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = 'public' 
AND table_name = 'configuracoes'
ORDER BY ordinal_position;

-- 5. Mostrar dados
SELECT id, chave, tipo, descricao, created_at, updated_at
FROM public.configuracoes
ORDER BY chave;