-- Migration 023: Consolidar soporte multi-pais en tenants
-- Ejecutar en phpMyAdmin: Import -> marcar "Force all queries, do not abort on errors"
-- Los errores "Duplicate column name" son normales si la columna ya existe.

-- 1. Backfill country
UPDATE tenants SET country = 'NI' WHERE country IS NULL OR country = '';

-- 2. Backfill currency
UPDATE tenants SET currency = 'NIO' WHERE (currency IS NULL OR currency = '') AND country = 'NI';
UPDATE tenants SET currency = 'HNL' WHERE (currency IS NULL OR currency = '') AND country = 'HN';
UPDATE tenants SET currency = 'USD' WHERE (currency IS NULL OR currency = '') AND country IN ('SV', 'US');

-- 3. Backfill timezone
UPDATE tenants SET timezone = 'America/Managua'     WHERE (timezone IS NULL OR timezone = '') AND country = 'NI';
UPDATE tenants SET timezone = 'America/Tegucigalpa' WHERE (timezone IS NULL OR timezone = '') AND country = 'HN';
UPDATE tenants SET timezone = 'America/El_Salvador'  WHERE (timezone IS NULL OR timezone = '') AND country = 'SV';
UPDATE tenants SET timezone = 'America/New_York'    WHERE (timezone IS NULL OR timezone = '') AND country = 'US';

-- 4. Columna perfil_voz (falla silenciosamente si ya existe con "Force all queries")
ALTER TABLE tenants ADD COLUMN perfil_voz VARCHAR(10) NULL AFTER country;

-- 5. Backfill perfil_voz desde country
UPDATE tenants SET perfil_voz = CASE country
    WHEN 'NI' THEN 'es-CA'
    WHEN 'HN' THEN 'es-CA'
    WHEN 'SV' THEN 'es-CA'
    WHEN 'US' THEN 'en-US'
    ELSE 'es-CA'
END
WHERE perfil_voz IS NULL OR perfil_voz = '';

-- Verificacion
SELECT id, name, country, currency, timezone, perfil_voz FROM tenants LIMIT 10;