-- Adicionar coluna establishment_id numérica à tabela establishments
-- Execute: sudo -u postgres psql -d maktubia_points -f add-establishment-id-numeric.sql

-- Verificar se a coluna establishment_id já existe
DO $$
BEGIN
    -- Verificar se establishment_id já existe
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name = 'establishments' 
        AND column_name = 'establishment_id'
    ) THEN
        -- Adicionar coluna establishment_id como SERIAL
        ALTER TABLE establishments 
        ADD COLUMN establishment_id SERIAL;
        
        -- Criar índice único se não existir
        CREATE UNIQUE INDEX IF NOT EXISTS establishments_establishment_id_unique 
        ON establishments(establishment_id);
        
        -- Se a tabela tem est_id como PRIMARY KEY, manter ambos
        -- Se não tem PRIMARY KEY, definir establishment_id como PRIMARY KEY
        IF NOT EXISTS (
            SELECT 1 FROM information_schema.table_constraints 
            WHERE table_name = 'establishments' 
            AND constraint_type = 'PRIMARY KEY'
        ) THEN
            ALTER TABLE establishments 
            ADD PRIMARY KEY (establishment_id);
        END IF;
        
        RAISE NOTICE 'Coluna establishment_id adicionada com sucesso';
    ELSE
        -- Se já existe, verificar se é numérica
        IF EXISTS (
            SELECT 1 FROM information_schema.columns 
            WHERE table_name = 'establishments' 
            AND column_name = 'establishment_id'
            AND data_type != 'integer'
        ) THEN
            -- Se não é numérica, criar nova coluna e migrar dados
            ALTER TABLE establishments 
            ADD COLUMN IF NOT EXISTS establishment_id_new SERIAL;
            
            -- Tentar migrar dados existentes se possível
            UPDATE establishments 
            SET establishment_id_new = CAST(establishment_id AS INTEGER) 
            WHERE establishment_id ~ '^[0-9]+$';
            
            -- Remover coluna antiga e renomear nova
            ALTER TABLE establishments 
            DROP COLUMN IF EXISTS establishment_id;
            
            ALTER TABLE establishments 
            RENAME COLUMN establishment_id_new TO establishment_id;
            
            RAISE NOTICE 'Coluna establishment_id convertida para numérica';
        ELSE
            RAISE NOTICE 'Coluna establishment_id já existe e é numérica';
        END IF;
    END IF;
END $$;

-- Verificar resultado
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns 
WHERE table_name = 'establishments' 
AND column_name IN ('establishment_id', 'est_id')
ORDER BY column_name;