-- Adiciona coluna payment_date na tabela payables (se não existir)
-- Nota: MySQL não suporta IF NOT EXISTS em ALTER TABLE, então execute este script apenas uma vez
-- Se a coluna já existir, você receberá um erro que pode ser ignorado

-- Verifica se a coluna existe antes de adicionar (para MySQL 5.7+)
SET @dbname = DATABASE();
SET @tablename = 'payables';
SET @columnname = 'payment_date';
SET @preparedStatement = (SELECT IF(
    (
        SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
        WHERE
            (TABLE_SCHEMA = @dbname)
            AND (TABLE_NAME = @tablename)
            AND (COLUMN_NAME = @columnname)
    ) > 0,
    "SELECT 'Coluna payment_date já existe' AS message;",
    CONCAT("ALTER TABLE ", @tablename, " ADD COLUMN ", @columnname, " DATE NULL COMMENT 'Data de pagamento' AFTER status;")
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- Adiciona índice (se não existir)
SET @preparedStatement = (SELECT IF(
    (
        SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
        WHERE
            (TABLE_SCHEMA = @dbname)
            AND (TABLE_NAME = @tablename)
            AND (INDEX_NAME = 'idx_payment_date')
    ) > 0,
    "SELECT 'Índice idx_payment_date já existe' AS message;",
    CONCAT("ALTER TABLE ", @tablename, " ADD INDEX idx_payment_date (", @columnname, ");")
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- Atualiza payment_date para contas já pagas baseado na data da transação
UPDATE payables p
INNER JOIN transactions t ON t.reference_id = p.id AND t.reference_type = 'payable' AND t.type = 'outflow'
SET p.payment_date = DATE(t.created_at)
WHERE p.status = 'paid' AND (p.payment_date IS NULL OR p.payment_date = '0000-00-00');