-- ============================================================
-- Migration 013 — Geolocalização em media_files
-- Adiciona latitude e longitude à tabela de mídias.
-- Compatível com MySQL 5.7, 8.0+ e MariaDB 10.3+.
-- Usa INFORMATION_SCHEMA para verificar tudo antes de alterar.
-- ALTER IGNORE TABLE removido (inválido no MySQL 8.0+).
-- ============================================================

SET @dbname = DATABASE();

-- latitude
SET @col_lat = (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = @dbname
      AND TABLE_NAME   = 'media_files'
      AND COLUMN_NAME  = 'latitude'
);
SET @sql_lat = IF(@col_lat = 0,
    'ALTER TABLE media_files ADD COLUMN latitude DECIMAL(10,8) NULL AFTER storage_path',
    'SELECT ''latitude already exists'' AS msg'
);
PREPARE stmt FROM @sql_lat; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- longitude
SET @col_lon = (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = @dbname
      AND TABLE_NAME   = 'media_files'
      AND COLUMN_NAME  = 'longitude'
);
SET @sql_lon = IF(@col_lon = 0,
    'ALTER TABLE media_files ADD COLUMN longitude DECIMAL(11,8) NULL AFTER latitude',
    'SELECT ''longitude already exists'' AS msg'
);
PREPARE stmt FROM @sql_lon; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- índice composto (via INFORMATION_SCHEMA.STATISTICS — funciona em qualquer versão MySQL/MariaDB)
SET @idx_geo = (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
    WHERE TABLE_SCHEMA = @dbname
      AND TABLE_NAME   = 'media_files'
      AND INDEX_NAME   = 'idx_media_geo'
);
SET @sql_idx = IF(@idx_geo = 0,
    'ALTER TABLE media_files ADD INDEX idx_media_geo (latitude, longitude)',
    'SELECT ''idx_media_geo already exists'' AS msg'
);
PREPARE stmt FROM @sql_idx; EXECUTE stmt; DEALLOCATE PREPARE stmt;