-- Adiciona colunas faltantes na tabela students
-- Execute este script se as colunas codigo, contato_aluno e contato_responsavel não existirem

-- Verifica e adiciona coluna 'codigo' se não existir
SET @dbname = DATABASE();
SET @tablename = 'students';
SET @columnname = 'codigo';
SET @preparedStatement = (SELECT IF(
    (
        SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
        WHERE
            (TABLE_SCHEMA = @dbname)
            AND (TABLE_NAME = @tablename)
            AND (COLUMN_NAME = @columnname)
    ) > 0,
    'SELECT 1',
    CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(50) NULL AFTER sexo')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- Verifica e adiciona coluna 'contato_aluno' se não existir
SET @columnname = 'contato_aluno';
SET @preparedStatement = (SELECT IF(
    (
        SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
        WHERE
            (TABLE_SCHEMA = @dbname)
            AND (TABLE_NAME = @tablename)
            AND (COLUMN_NAME = @columnname)
    ) > 0,
    'SELECT 1',
    CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(255) NULL AFTER cpf_responsavel')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- Verifica e adiciona coluna 'contato_responsavel' se não existir
SET @columnname = 'contato_responsavel';
SET @preparedStatement = (SELECT IF(
    (
        SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
        WHERE
            (TABLE_SCHEMA = @dbname)
            AND (TABLE_NAME = @tablename)
            AND (COLUMN_NAME = @columnname)
    ) > 0,
    'SELECT 1',
    CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(255) NULL AFTER contato_aluno')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- Versão simplificada (caso a versão acima não funcione):
-- ALTER TABLE students ADD COLUMN IF NOT EXISTS codigo VARCHAR(50) NULL AFTER sexo;
-- ALTER TABLE students ADD COLUMN IF NOT EXISTS contato_aluno VARCHAR(255) NULL AFTER cpf_responsavel;
-- ALTER TABLE students ADD COLUMN IF NOT EXISTS contato_responsavel VARCHAR(255) NULL AFTER contato_aluno;