-- =====================================================
-- SISTEMA DE MULTILENGUAJE Y MÚLTIPLES MONEDAS
-- Mayan World Travel MVP - Fase 3
-- =====================================================

USE travel_mvp;

-- Tabla de idiomas soportados
CREATE TABLE languages (
    id INT PRIMARY KEY AUTO_INCREMENT,
    code VARCHAR(5) NOT NULL UNIQUE, -- es, en, fr
    name VARCHAR(50) NOT NULL,
    native_name VARCHAR(50) NOT NULL, -- Español, English, Français
    flag_emoji VARCHAR(10) DEFAULT '',
    is_default BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    rtl BOOLEAN DEFAULT FALSE, -- Right-to-left support
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Tabla de monedas soportadas
CREATE TABLE currencies (
    id INT PRIMARY KEY AUTO_INCREMENT,
    code VARCHAR(3) NOT NULL UNIQUE, -- USD, GTQ, EUR, MXN
    name VARCHAR(50) NOT NULL,
    symbol VARCHAR(10) NOT NULL,
    decimal_places INT DEFAULT 2,
    is_default BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    exchange_rate DECIMAL(10,6) DEFAULT 1.000000, -- Rate to default currency
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabla de traducciones dinámicas
CREATE TABLE translations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    language_id INT NOT NULL,
    translation_key VARCHAR(255) NOT NULL,
    translation_value TEXT NOT NULL,
    context VARCHAR(100) DEFAULT NULL, -- frontend, admin, email, etc.
    is_system BOOLEAN DEFAULT FALSE, -- System vs user-defined translations
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (language_id) REFERENCES languages(id) ON DELETE CASCADE,
    UNIQUE KEY unique_lang_key (language_id, translation_key),
    INDEX idx_translation_key (translation_key),
    INDEX idx_context (context),
    INDEX idx_language_context (language_id, context)
);

-- Tabla de traducciones de productos
CREATE TABLE product_translations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    language_id INT NOT NULL,
    nombre VARCHAR(200) NOT NULL,
    descripcion TEXT,
    descripcion_corta VARCHAR(500),
    incluye TEXT,
    no_incluye TEXT,
    itinerario TEXT,
    seo_title VARCHAR(200),
    seo_description VARCHAR(300),
    seo_keywords TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (product_id) REFERENCES productos(id) ON DELETE CASCADE,
    FOREIGN KEY (language_id) REFERENCES languages(id) ON DELETE CASCADE,
    UNIQUE KEY unique_product_lang (product_id, language_id),
    INDEX idx_product_lang (product_id, language_id)
);

-- Tabla de traducciones de categorías
CREATE TABLE category_translations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category_id INT NOT NULL,
    language_id INT NOT NULL,
    nombre VARCHAR(100) NOT NULL,
    descripcion TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (category_id) REFERENCES categorias(id) ON DELETE CASCADE,
    FOREIGN KEY (language_id) REFERENCES languages(id) ON DELETE CASCADE,
    UNIQUE KEY unique_category_lang (category_id, language_id)
);

-- Tabla de precios por moneda
CREATE TABLE product_prices (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    currency_id INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    discount_price DECIMAL(10,2) NULL,
    is_custom BOOLEAN DEFAULT FALSE, -- Manual vs auto-converted price
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (product_id) REFERENCES productos(id) ON DELETE CASCADE,
    FOREIGN KEY (currency_id) REFERENCES currencies(id) ON DELETE CASCADE,
    UNIQUE KEY unique_product_currency (product_id, currency_id),
    INDEX idx_product_currency (product_id, currency_id)
);

-- Tabla de detectores de localización automática
CREATE TABLE geo_localization (
    id INT PRIMARY KEY AUTO_INCREMENT,
    country_code VARCHAR(2) NOT NULL, -- GT, MX, US, etc.
    country_name VARCHAR(100) NOT NULL,
    suggested_language_id INT NOT NULL,
    suggested_currency_id INT NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (suggested_language_id) REFERENCES languages(id),
    FOREIGN KEY (suggested_currency_id) REFERENCES currencies(id),
    UNIQUE KEY unique_country (country_code)
);

-- Tabla de historial de tasas de cambio
CREATE TABLE exchange_rate_history (
    id INT PRIMARY KEY AUTO_INCREMENT,
    currency_id INT NOT NULL,
    rate DECIMAL(10,6) NOT NULL,
    source VARCHAR(50) DEFAULT 'manual', -- manual, api, scheduled
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (currency_id) REFERENCES currencies(id),
    INDEX idx_currency_date (currency_id, created_at)
);

-- =====================================================
-- DATOS INICIALES
-- =====================================================

-- Idiomas soportados
INSERT INTO languages (code, name, native_name, flag_emoji, is_default, is_active) VALUES
('es', 'Spanish', 'Español', '🇪🇸', TRUE, TRUE),
('en', 'English', 'English', '🇺🇸', FALSE, TRUE),
('fr', 'French', 'Français', '🇫🇷', FALSE, TRUE);

-- Monedas soportadas
INSERT INTO currencies (code, name, symbol, is_default, is_active, exchange_rate) VALUES
('USD', 'US Dollar', '$', TRUE, TRUE, 1.000000),
('GTQ', 'Guatemalan Quetzal', 'Q', FALSE, TRUE, 7.850000),
('EUR', 'Euro', '€', FALSE, TRUE, 0.850000),
('MXN', 'Mexican Peso', '$', FALSE, TRUE, 18.500000);

-- Configuración de localización por países
INSERT INTO geo_localization (country_code, country_name, suggested_language_id, suggested_currency_id) VALUES
('GT', 'Guatemala', 1, 2), -- Español, GTQ
('MX', 'México', 1, 4),    -- Español, MXN
('US', 'United States', 2, 1), -- English, USD
('CA', 'Canada', 2, 1),    -- English, USD
('FR', 'France', 3, 3),    -- Français, EUR
('ES', 'España', 1, 3),    -- Español, EUR
('BZ', 'Belize', 2, 1),    -- English, USD
('CR', 'Costa Rica', 1, 1); -- Español, USD

-- Traducciones básicas del sistema
SET @es_id = (SELECT id FROM languages WHERE code = 'es');
SET @en_id = (SELECT id FROM languages WHERE code = 'en');
SET @fr_id = (SELECT id FROM languages WHERE code = 'fr');

INSERT INTO translations (language_id, translation_key, translation_value, context, is_system) VALUES
-- Navegación
(@es_id, 'nav.home', 'Inicio', 'frontend', TRUE),
(@en_id, 'nav.home', 'Home', 'frontend', TRUE),
(@fr_id, 'nav.home', 'Accueil', 'frontend', TRUE),

(@es_id, 'nav.products', 'Tours', 'frontend', TRUE),
(@en_id, 'nav.products', 'Tours', 'frontend', TRUE),
(@fr_id, 'nav.products', 'Circuits', 'frontend', TRUE),

(@es_id, 'nav.contact', 'Contacto', 'frontend', TRUE),
(@en_id, 'nav.contact', 'Contact', 'frontend', TRUE),
(@fr_id, 'nav.contact', 'Contact', 'frontend', TRUE),

-- Botones comunes
(@es_id, 'btn.book_now', 'Reservar Ahora', 'frontend', TRUE),
(@en_id, 'btn.book_now', 'Book Now', 'frontend', TRUE),
(@fr_id, 'btn.book_now', 'Réserver Maintenant', 'frontend', TRUE),

(@es_id, 'btn.view_details', 'Ver Detalles', 'frontend', TRUE),
(@en_id, 'btn.view_details', 'View Details', 'frontend', TRUE),
(@fr_id, 'btn.view_details', 'Voir Détails', 'frontend', TRUE),

-- Formularios
(@es_id, 'form.name', 'Nombre', 'frontend', TRUE),
(@en_id, 'form.name', 'Name', 'frontend', TRUE),
(@fr_id, 'form.name', 'Nom', 'frontend', TRUE),

(@es_id, 'form.email', 'Correo Electrónico', 'frontend', TRUE),
(@en_id, 'form.email', 'Email', 'frontend', TRUE),
(@fr_id, 'form.email', 'Email', 'frontend', TRUE),

(@es_id, 'form.phone', 'Teléfono', 'frontend', TRUE),
(@en_id, 'form.phone', 'Phone', 'frontend', TRUE),
(@fr_id, 'form.phone', 'Téléphone', 'frontend', TRUE),

-- Estados de reserva
(@es_id, 'booking.status.pending', 'Pendiente', 'frontend', TRUE),
(@en_id, 'booking.status.pending', 'Pending', 'frontend', TRUE),
(@fr_id, 'booking.status.pending', 'En attente', 'frontend', TRUE),

(@es_id, 'booking.status.confirmed', 'Confirmada', 'frontend', TRUE),
(@en_id, 'booking.status.confirmed', 'Confirmed', 'frontend', TRUE),
(@fr_id, 'booking.status.confirmed', 'Confirmée', 'frontend', TRUE),

(@es_id, 'booking.status.paid', 'Pagada', 'frontend', TRUE),
(@en_id, 'booking.status.paid', 'Paid', 'frontend', TRUE),
(@fr_id, 'booking.status.paid', 'Payée', 'frontend', TRUE);

-- =====================================================
-- TRIGGERS PARA ACTUALIZACIÓN AUTOMÁTICA
-- =====================================================

DELIMITER //

-- Trigger para actualizar precios automáticamente cuando cambia la tasa de cambio
CREATE TRIGGER update_product_prices_on_rate_change 
AFTER UPDATE ON currencies
FOR EACH ROW
BEGIN
    IF NEW.exchange_rate != OLD.exchange_rate THEN
        -- Actualizar precios automáticos (no personalizados)
        UPDATE product_prices pp
        JOIN productos p ON pp.product_id = p.id
        SET pp.price = (p.precio * NEW.exchange_rate),
            pp.discount_price = CASE 
                WHEN p.precio_descuento IS NOT NULL 
                THEN (p.precio_descuento * NEW.exchange_rate)
                ELSE NULL
            END,
            pp.updated_at = CURRENT_TIMESTAMP
        WHERE pp.currency_id = NEW.id AND pp.is_custom = FALSE;
        
        -- Insertar en historial
        INSERT INTO exchange_rate_history (currency_id, rate, source)
        VALUES (NEW.id, NEW.exchange_rate, 'auto_update');
    END IF;
END//

DELIMITER ;

-- =====================================================
-- VISTAS PARA CONSULTAS OPTIMIZADAS
-- =====================================================

-- Vista para productos con traducciones
CREATE VIEW products_i18n AS
SELECT 
    p.id,
    p.categoria_id,
    p.precio,
    p.precio_descuento,
    p.duracion,
    p.capacidad_maxima,
    p.dificultad,
    p.imagen_principal,
    p.galeria,
    p.activo,
    p.destacado,
    l.code as language_code,
    COALESCE(pt.nombre, p.nombre) as nombre,
    COALESCE(pt.descripcion, p.descripcion) as descripcion,
    COALESCE(pt.descripcion_corta, p.descripcion_corta) as descripcion_corta,
    COALESCE(pt.incluye, p.incluye) as incluye,
    COALESCE(pt.no_incluye, p.no_incluye) as no_incluye,
    COALESCE(pt.itinerario, p.itinerario) as itinerario,
    pt.seo_title,
    pt.seo_description,
    pt.seo_keywords
FROM productos p
CROSS JOIN languages l
LEFT JOIN product_translations pt ON p.id = pt.product_id AND l.id = pt.language_id
WHERE l.is_active = TRUE;

-- Vista para precios en múltiples monedas
CREATE VIEW product_prices_view AS
SELECT 
    p.id as product_id,
    p.nombre,
    c.code as currency_code,
    c.symbol,
    COALESCE(pp.price, p.precio * c.exchange_rate) as price,
    COALESCE(pp.discount_price, 
        CASE WHEN p.precio_descuento IS NOT NULL 
             THEN p.precio_descuento * c.exchange_rate 
             ELSE NULL END
    ) as discount_price,
    pp.is_custom
FROM productos p
CROSS JOIN currencies c
LEFT JOIN product_prices pp ON p.id = pp.product_id AND c.id = pp.currency_id
WHERE c.is_active = TRUE AND p.activo = TRUE;