-- Script para adicionar coluna is_active à tabela roles na VPS
-- Execute: sudo -u postgres psql -d maktubia_points -f deploy/fix-roles-is-active.sql

-- Adicionar coluna is_active se não existir
DO $$ 
BEGIN
    -- is_active
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name='roles' AND column_name='is_active'
    ) THEN
        ALTER TABLE roles ADD COLUMN is_active BOOLEAN DEFAULT TRUE;
        UPDATE roles SET is_active = TRUE WHERE is_active IS NULL;
        RAISE NOTICE '✅ Coluna is_active adicionada à tabela roles';
    ELSE
        RAISE NOTICE 'ℹ️  Coluna is_active já existe na tabela roles';
    END IF;
    
    -- created_at
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name='roles' AND column_name='created_at'
    ) THEN
        ALTER TABLE roles ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
        UPDATE roles SET created_at = CURRENT_TIMESTAMP WHERE created_at IS NULL;
        RAISE NOTICE '✅ Coluna created_at adicionada à tabela roles';
    ELSE
        RAISE NOTICE 'ℹ️  Coluna created_at já existe na tabela roles';
    END IF;
    
    -- updated_at
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name='roles' AND column_name='updated_at'
    ) THEN
        ALTER TABLE roles ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
        UPDATE roles SET updated_at = CURRENT_TIMESTAMP WHERE updated_at IS NULL;
        RAISE NOTICE '✅ Coluna updated_at adicionada à tabela roles';
    ELSE
        RAISE NOTICE 'ℹ️  Coluna updated_at já existe na tabela roles';
    END IF;
END $$;

-- Verificar estrutura da tabela
SELECT 
    column_name, 
    data_type, 
    is_nullable, 
    column_default
FROM information_schema.columns 
WHERE table_name = 'roles'
ORDER BY ordinal_position;

-- Verificar dados
SELECT 
    role_id, 
    role_name, 
    is_active, 
    created_at, 
    updated_at
FROM roles
ORDER BY role_id;