-- =====================================================
-- MARKETPLACE INTEGRATION SYSTEM - FASE 4
-- Multi-channel Distribution APIs
-- =====================================================

USE travel_mvp;

-- Tabla de marketplaces/canales de distribución
CREATE TABLE marketplaces (
    id INT PRIMARY KEY AUTO_INCREMENT,
    
    -- Marketplace info
    marketplace_name VARCHAR(100) NOT NULL,
    marketplace_key VARCHAR(50) NOT NULL UNIQUE,
    marketplace_type ENUM('ota', 'experiences', 'direct', 'affiliate') NOT NULL,
    
    -- API Configuration
    api_endpoint VARCHAR(500) NOT NULL,
    api_version VARCHAR(20) DEFAULT '1.0',
    auth_type ENUM('api_key', 'oauth', 'basic_auth', 'bearer_token') NOT NULL,
    
    -- Credentials (encrypted)
    api_key VARCHAR(500),
    api_secret VARCHAR(500),
    client_id VARCHAR(200),
    client_secret VARCHAR(500),
    access_token TEXT,
    refresh_token TEXT,
    token_expires_at TIMESTAMP NULL,
    
    -- Business terms
    commission_rate DECIMAL(5,4) DEFAULT 0.1500, -- 15% default
    currency_code VARCHAR(3) DEFAULT 'USD',
    payment_terms VARCHAR(100), -- 'net_30', 'weekly', etc.
    
    -- Integration settings
    is_active BOOLEAN DEFAULT TRUE,
    auto_sync BOOLEAN DEFAULT FALSE,
    sync_frequency ENUM('realtime', 'hourly', 'daily', 'manual') DEFAULT 'daily',
    last_sync_at TIMESTAMP NULL,
    
    -- Rate limiting
    rate_limit_requests INT DEFAULT 1000,
    rate_limit_period INT DEFAULT 3600, -- per hour
    
    -- Status and monitoring
    health_status ENUM('healthy', 'warning', 'error', 'disabled') DEFAULT 'healthy',
    last_error TEXT,
    error_count INT DEFAULT 0,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_marketplace_key (marketplace_key),
    INDEX idx_active_marketplaces (is_active, health_status),
    INDEX idx_sync_schedule (auto_sync, sync_frequency)
);

-- Tabla de productos distribuidos en marketplaces
CREATE TABLE marketplace_products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    
    -- Internal reference
    product_id INT NOT NULL,
    marketplace_id INT NOT NULL,
    tenant_id INT NULL,
    
    -- Marketplace specific data
    external_product_id VARCHAR(200) NOT NULL,
    external_sku VARCHAR(100),
    
    -- Marketplace customizations
    marketplace_title VARCHAR(300),
    marketplace_description TEXT,
    marketplace_price DECIMAL(10,2),
    marketplace_currency VARCHAR(3),
    
    -- Images and media
    marketplace_images JSON, -- Array of marketplace-specific image URLs
    marketplace_videos JSON,
    
    -- Availability settings
    availability_sync BOOLEAN DEFAULT TRUE,
    inventory_buffer INT DEFAULT 0, -- Reserve X spots from marketplace
    
    -- Pricing strategy
    pricing_strategy ENUM('fixed', 'markup', 'dynamic', 'parity') DEFAULT 'markup',
    pricing_markup_percentage DECIMAL(5,4) DEFAULT 0.2000, -- 20% markup
    pricing_rules JSON, -- Advanced pricing rules
    
    -- Sync status
    sync_status ENUM('pending', 'synced', 'error', 'disabled') DEFAULT 'pending',
    last_synced_at TIMESTAMP NULL,
    sync_error_message TEXT,
    
    -- Performance metrics
    total_views INT DEFAULT 0,
    total_bookings INT DEFAULT 0,
    total_revenue DECIMAL(12,2) DEFAULT 0,
    last_booking_at TIMESTAMP NULL,
    
    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 (marketplace_id) REFERENCES marketplaces(id) ON DELETE CASCADE,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE SET NULL,
    
    UNIQUE KEY unique_marketplace_product (marketplace_id, external_product_id),
    INDEX idx_product_marketplace (product_id, marketplace_id),
    INDEX idx_sync_status (sync_status, last_synced_at)
);

-- Tabla de reservas desde marketplaces
CREATE TABLE marketplace_bookings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    
    -- Internal references
    booking_id INT NOT NULL,
    marketplace_id INT NOT NULL,
    marketplace_product_id INT NOT NULL,
    
    -- External references
    external_booking_id VARCHAR(200) NOT NULL,
    external_customer_id VARCHAR(200),
    
    -- Customer information (from marketplace)
    customer_data JSON NOT NULL, -- Complete customer info from marketplace
    
    -- Booking details
    marketplace_booking_date TIMESTAMP NOT NULL,
    marketplace_total_amount DECIMAL(10,2) NOT NULL,
    marketplace_currency VARCHAR(3) NOT NULL,
    marketplace_commission DECIMAL(10,2) NOT NULL,
    
    -- Payment and settlement
    payment_status ENUM('pending', 'captured', 'settled', 'refunded') DEFAULT 'pending',
    settlement_date DATE NULL,
    settlement_amount DECIMAL(10,2) NULL,
    
    -- Communication
    confirmation_sent_to_marketplace BOOLEAN DEFAULT FALSE,
    voucher_sent_to_customer BOOLEAN DEFAULT FALSE,
    
    -- Status tracking
    marketplace_status VARCHAR(50) NOT NULL, -- As reported by marketplace
    internal_status_mapping ENUM('confirmed', 'pending', 'cancelled') NOT NULL,
    
    -- Sync tracking
    last_status_sync TIMESTAMP NULL,
    sync_attempts INT DEFAULT 0,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (booking_id) REFERENCES reservas(id) ON DELETE CASCADE,
    FOREIGN KEY (marketplace_id) REFERENCES marketplaces(id) ON DELETE RESTRICT,
    FOREIGN KEY (marketplace_product_id) REFERENCES marketplace_products(id) ON DELETE RESTRICT,
    
    UNIQUE KEY unique_external_booking (marketplace_id, external_booking_id),
    INDEX idx_booking_status (payment_status, settlement_date),
    INDEX idx_marketplace_bookings (marketplace_id, marketplace_booking_date DESC)
);

-- Tabla de inventario sincronizado
CREATE TABLE marketplace_inventory (
    id INT PRIMARY KEY AUTO_INCREMENT,
    
    marketplace_product_id INT NOT NULL,
    availability_date DATE NOT NULL,
    
    -- Inventory levels
    total_capacity INT NOT NULL,
    marketplace_allocation INT NOT NULL,
    reserved_bookings INT DEFAULT 0,
    available_spots INT AS (marketplace_allocation - reserved_bookings) PERSISTENT,
    
    -- Pricing for the date
    base_price DECIMAL(10,2) NOT NULL,
    marketplace_price DECIMAL(10,2) NOT NULL,
    
    -- Restrictions
    min_advance_booking INT DEFAULT 24, -- hours
    max_advance_booking INT DEFAULT 365, -- days
    cutoff_time TIME DEFAULT '18:00:00',
    
    -- Status
    is_available BOOLEAN DEFAULT TRUE,
    availability_notes TEXT,
    
    -- Sync tracking
    last_synced_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    sync_version INT DEFAULT 1,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (marketplace_product_id) REFERENCES marketplace_products(id) ON DELETE CASCADE,
    
    UNIQUE KEY unique_product_date (marketplace_product_id, availability_date),
    INDEX idx_availability_date (availability_date, is_available),
    INDEX idx_sync_tracking (last_synced_at, sync_version)
) PARTITION BY RANGE (YEAR(availability_date)) (
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p2026 VALUES LESS THAN (2027),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- Tabla de logs de sincronización
CREATE TABLE marketplace_sync_logs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    
    marketplace_id INT NOT NULL,
    sync_type ENUM('inventory', 'products', 'bookings', 'rates', 'availability') NOT NULL,
    sync_direction ENUM('push', 'pull', 'bidirectional') NOT NULL,
    
    -- Sync details
    sync_started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    sync_completed_at TIMESTAMP NULL,
    sync_status ENUM('running', 'completed', 'failed', 'partial') DEFAULT 'running',
    
    -- Results
    records_processed INT DEFAULT 0,
    records_successful INT DEFAULT 0,
    records_failed INT DEFAULT 0,
    
    -- Data payload
    sync_payload JSON,
    error_details JSON,
    
    -- Performance
    response_time_ms INT NULL,
    api_calls_made INT DEFAULT 0,
    
    FOREIGN KEY (marketplace_id) REFERENCES marketplaces(id) ON DELETE CASCADE,
    
    INDEX idx_marketplace_sync (marketplace_id, sync_type, sync_started_at DESC),
    INDEX idx_sync_status (sync_status, sync_completed_at)
) PARTITION BY RANGE (YEAR(sync_started_at)) (
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p2026 VALUES LESS THAN (2027),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- Tabla de mapeo de campos (para diferentes APIs)
CREATE TABLE marketplace_field_mappings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    
    marketplace_id INT NOT NULL,
    entity_type ENUM('product', 'booking', 'customer', 'availability') NOT NULL,
    
    -- Field mapping
    internal_field VARCHAR(100) NOT NULL,
    external_field VARCHAR(100) NOT NULL,
    field_type ENUM('string', 'integer', 'decimal', 'boolean', 'date', 'array', 'object') NOT NULL,
    
    -- Transformation rules
    is_required BOOLEAN DEFAULT FALSE,
    default_value TEXT,
    transformation_rules JSON, -- Mapping/conversion rules
    
    -- Validation
    validation_rules JSON,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (marketplace_id) REFERENCES marketplaces(id) ON DELETE CASCADE,
    
    UNIQUE KEY unique_field_mapping (marketplace_id, entity_type, internal_field),
    INDEX idx_entity_mappings (marketplace_id, entity_type)
);

-- Tabla de webhooks de marketplace
CREATE TABLE marketplace_webhooks (
    id INT PRIMARY KEY AUTO_INCREMENT,
    
    marketplace_id INT NOT NULL,
    webhook_url VARCHAR(500) NOT NULL,
    webhook_secret VARCHAR(200),
    
    -- Event configuration
    supported_events JSON NOT NULL, -- ['booking.created', 'booking.cancelled', etc.]
    is_active BOOLEAN DEFAULT TRUE,
    
    -- Security
    signature_header VARCHAR(100) DEFAULT 'X-Signature',
    signature_algorithm ENUM('sha1', 'sha256', 'md5') DEFAULT 'sha256',
    
    -- Monitoring
    total_calls INT DEFAULT 0,
    successful_calls INT DEFAULT 0,
    failed_calls INT DEFAULT 0,
    last_called_at TIMESTAMP NULL,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (marketplace_id) REFERENCES marketplaces(id) ON DELETE CASCADE,
    
    INDEX idx_active_webhooks (is_active, marketplace_id)
);

-- Tabla de analytics por canal
CREATE TABLE marketplace_analytics (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    
    marketplace_id INT NOT NULL,
    metric_date DATE NOT NULL,
    metric_hour INT NULL, -- For hourly metrics
    
    -- Traffic metrics
    impressions INT DEFAULT 0,
    clicks INT DEFAULT 0,
    click_through_rate DECIMAL(5,4) DEFAULT 0,
    
    -- Conversion metrics
    inquiries INT DEFAULT 0,
    bookings INT DEFAULT 0,
    conversion_rate DECIMAL(5,4) DEFAULT 0,
    
    -- Revenue metrics
    gross_revenue DECIMAL(12,2) DEFAULT 0,
    commission_paid DECIMAL(12,2) DEFAULT 0,
    net_revenue DECIMAL(12,2) DEFAULT 0,
    
    -- Product performance
    top_products JSON, -- [{'product_id': 1, 'bookings': 5, 'revenue': 500}]
    avg_booking_value DECIMAL(10,2) DEFAULT 0,
    
    -- Customer metrics
    new_customers INT DEFAULT 0,
    returning_customers INT DEFAULT 0,
    customer_satisfaction DECIMAL(3,2) DEFAULT 0,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (marketplace_id) REFERENCES marketplaces(id) ON DELETE CASCADE,
    
    UNIQUE KEY unique_marketplace_date_hour (marketplace_id, metric_date, metric_hour),
    INDEX idx_marketplace_analytics (marketplace_id, metric_date DESC)
) PARTITION BY RANGE (YEAR(metric_date)) (
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p2026 VALUES LESS THAN (2027),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- =====================================================
-- STORED PROCEDURES PARA MARKETPLACE INTEGRATION
-- =====================================================

DELIMITER //

-- Procedure para sincronizar inventario a marketplace
CREATE PROCEDURE SyncInventoryToMarketplace(
    IN p_marketplace_id INT,
    IN p_product_id INT,
    IN p_date_range_days INT
)
BEGIN
    DECLARE v_marketplace_product_id INT;
    DECLARE v_sync_log_id BIGINT;
    DECLARE v_records_processed INT DEFAULT 0;
    DECLARE v_records_successful INT DEFAULT 0;
    
    -- Iniciar log de sincronización
    INSERT INTO marketplace_sync_logs (marketplace_id, sync_type, sync_direction)
    VALUES (p_marketplace_id, 'inventory', 'push');
    SET v_sync_log_id = LAST_INSERT_ID();
    
    -- Obtener marketplace_product_id
    SELECT id INTO v_marketplace_product_id
    FROM marketplace_products
    WHERE marketplace_id = p_marketplace_id AND product_id = p_product_id
    LIMIT 1;
    
    IF v_marketplace_product_id IS NULL THEN
        UPDATE marketplace_sync_logs 
        SET sync_status = 'failed',
            error_details = JSON_OBJECT('error', 'Product not found in marketplace'),
            sync_completed_at = CURRENT_TIMESTAMP
        WHERE id = v_sync_log_id;
        
        SELECT 'Product not found in marketplace' as error;
        RETURN;
    END IF;
    
    -- Sincronizar inventario para el rango de fechas
    BEGIN
        DECLARE done INT DEFAULT FALSE;
        DECLARE v_date DATE;
        DECLARE v_total_capacity INT;
        DECLARE v_current_bookings INT;
        DECLARE v_marketplace_price DECIMAL(10,2);
        
        DECLARE date_cursor CURSOR FOR
            SELECT DATE_ADD(CURRENT_DATE, INTERVAL seq DAY) as sync_date
            FROM (
                SELECT 0 as seq UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 
                UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12 UNION SELECT 13
                UNION SELECT 14 UNION SELECT 15 UNION SELECT 16 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION SELECT 20
                UNION SELECT 21 UNION SELECT 22 UNION SELECT 23 UNION SELECT 24 UNION SELECT 25 UNION SELECT 26 UNION SELECT 27
                UNION SELECT 28 UNION SELECT 29 UNION SELECT 30
            ) as seq_table
            WHERE seq < p_date_range_days;
            
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
        
        OPEN date_cursor;
        
        sync_loop: LOOP
            FETCH date_cursor INTO v_date;
            IF done THEN
                LEAVE sync_loop;
            END IF;
            
            SET v_records_processed = v_records_processed + 1;
            
            -- Obtener capacidad del producto
            SELECT capacidad_maxima INTO v_total_capacity
            FROM productos 
            WHERE id = p_product_id;
            
            -- Calcular reservas existentes
            SELECT COUNT(*) INTO v_current_bookings
            FROM reservas 
            WHERE producto_id = p_product_id 
            AND fecha_salida = v_date
            AND estado IN ('confirmada', 'pagada');
            
            -- Calcular precio dinámico si está disponible
            SELECT calculated_price INTO v_marketplace_price
            FROM dynamic_prices
            WHERE product_id = p_product_id 
            AND calculation_date = v_date
            ORDER BY created_at DESC
            LIMIT 1;
            
            IF v_marketplace_price IS NULL THEN
                SELECT precio INTO v_marketplace_price
                FROM productos WHERE id = p_product_id;
            END IF;
            
            -- Insertar/actualizar inventario
            INSERT INTO marketplace_inventory (
                marketplace_product_id, availability_date, total_capacity,
                marketplace_allocation, reserved_bookings, base_price, marketplace_price
            ) VALUES (
                v_marketplace_product_id, v_date, v_total_capacity,
                v_total_capacity, v_current_bookings, v_marketplace_price, v_marketplace_price * 1.15
            ) ON DUPLICATE KEY UPDATE
                total_capacity = v_total_capacity,
                reserved_bookings = v_current_bookings,
                marketplace_price = v_marketplace_price * 1.15,
                last_synced_at = CURRENT_TIMESTAMP,
                sync_version = sync_version + 1;
            
            SET v_records_successful = v_records_successful + 1;
        END LOOP;
        
        CLOSE date_cursor;
    END;
    
    -- Finalizar log
    UPDATE marketplace_sync_logs 
    SET sync_status = 'completed',
        records_processed = v_records_processed,
        records_successful = v_records_successful,
        sync_completed_at = CURRENT_TIMESTAMP
    WHERE id = v_sync_log_id;
    
    SELECT 'Inventory sync completed' as message, 
           v_records_processed as processed,
           v_records_successful as successful;
END//

-- Procedure para procesar webhook de marketplace
CREATE PROCEDURE ProcessMarketplaceWebhook(
    IN p_marketplace_id INT,
    IN p_event_type VARCHAR(100),
    IN p_payload JSON
)
BEGIN
    DECLARE v_booking_id INT;
    DECLARE v_external_booking_id VARCHAR(200);
    DECLARE v_marketplace_product_id INT;
    
    CASE p_event_type
        WHEN 'booking.created' THEN
            -- Extraer datos del webhook
            SET v_external_booking_id = JSON_UNQUOTE(JSON_EXTRACT(p_payload, '$.booking_id'));
            SET v_marketplace_product_id = (
                SELECT id FROM marketplace_products 
                WHERE marketplace_id = p_marketplace_id 
                AND external_product_id = JSON_UNQUOTE(JSON_EXTRACT(p_payload, '$.product_id'))
                LIMIT 1
            );
            
            IF v_marketplace_product_id IS NOT NULL THEN
                -- Crear reserva interna
                INSERT INTO reservas (
                    codigo_reserva, producto_id, cliente_nombre, cliente_email, cliente_telefono,
                    fecha_salida, fecha_regreso, numero_personas, precio_final, estado
                ) VALUES (
                    CONCAT('MKT-', v_external_booking_id),
                    (SELECT product_id FROM marketplace_products WHERE id = v_marketplace_product_id),
                    JSON_UNQUOTE(JSON_EXTRACT(p_payload, '$.customer.name')),
                    JSON_UNQUOTE(JSON_EXTRACT(p_payload, '$.customer.email')),
                    JSON_UNQUOTE(JSON_EXTRACT(p_payload, '$.customer.phone')),
                    JSON_UNQUOTE(JSON_EXTRACT(p_payload, '$.travel_date')),
                    JSON_UNQUOTE(JSON_EXTRACT(p_payload, '$.travel_date')),
                    JSON_EXTRACT(p_payload, '$.travelers'),
                    JSON_EXTRACT(p_payload, '$.total_amount'),
                    'confirmada'
                );
                
                SET v_booking_id = LAST_INSERT_ID();
                
                -- Crear registro de marketplace booking
                INSERT INTO marketplace_bookings (
                    booking_id, marketplace_id, marketplace_product_id,
                    external_booking_id, customer_data, marketplace_booking_date,
                    marketplace_total_amount, marketplace_currency, marketplace_commission,
                    marketplace_status, internal_status_mapping
                ) VALUES (
                    v_booking_id, p_marketplace_id, v_marketplace_product_id,
                    v_external_booking_id, JSON_EXTRACT(p_payload, '$.customer'),
                    JSON_UNQUOTE(JSON_EXTRACT(p_payload, '$.created_at')),
                    JSON_EXTRACT(p_payload, '$.total_amount'),
                    JSON_UNQUOTE(JSON_EXTRACT(p_payload, '$.currency')),
                    JSON_EXTRACT(p_payload, '$.commission'),
                    'confirmed', 'confirmed'
                );
            END IF;
            
        WHEN 'booking.cancelled' THEN
            -- Actualizar estado de reserva
            SET v_external_booking_id = JSON_UNQUOTE(JSON_EXTRACT(p_payload, '$.booking_id'));
            
            UPDATE reservas r
            JOIN marketplace_bookings mb ON r.id = mb.booking_id
            SET r.estado = 'cancelada'
            WHERE mb.marketplace_id = p_marketplace_id 
            AND mb.external_booking_id = v_external_booking_id;
            
            UPDATE marketplace_bookings
            SET marketplace_status = 'cancelled',
                internal_status_mapping = 'cancelled'
            WHERE marketplace_id = p_marketplace_id 
            AND external_booking_id = v_external_booking_id;
            
        ELSE
            -- Log evento no manejado
            INSERT INTO marketplace_sync_logs (marketplace_id, sync_type, sync_direction, sync_status, error_details)
            VALUES (p_marketplace_id, 'bookings', 'pull', 'failed', 
                    JSON_OBJECT('error', 'Unhandled event type', 'event', p_event_type));
    END CASE;
END//

DELIMITER ;

-- =====================================================
-- TRIGGERS PARA MARKETPLACE SYNC
-- =====================================================

DELIMITER //

-- Trigger para sincronizar cuando cambia disponibilidad
CREATE TRIGGER sync_marketplace_on_booking_change
AFTER UPDATE ON reservas
FOR EACH ROW
BEGIN
    -- Actualizar inventario de marketplace cuando cambia el estado de una reserva
    IF NEW.estado != OLD.estado THEN
        UPDATE marketplace_inventory mi
        JOIN marketplace_products mp ON mi.marketplace_product_id = mp.id
        SET mi.reserved_bookings = (
            SELECT COUNT(*) FROM reservas 
            WHERE producto_id = mp.product_id 
            AND fecha_salida = mi.availability_date
            AND estado IN ('confirmada', 'pagada')
        ),
        mi.last_synced_at = CURRENT_TIMESTAMP,
        mi.sync_version = mi.sync_version + 1
        WHERE mp.product_id = NEW.producto_id
        AND mi.availability_date = NEW.fecha_salida;
    END IF;
END//

DELIMITER ;

-- =====================================================
-- VISTAS PARA MARKETPLACE REPORTING
-- =====================================================

-- Vista de performance por marketplace
CREATE VIEW marketplace_performance AS
SELECT 
    m.marketplace_name,
    m.marketplace_key,
    COUNT(DISTINCT mp.id) as products_listed,
    COUNT(mb.id) as total_bookings,
    COALESCE(SUM(mb.marketplace_total_amount), 0) as gross_revenue,
    COALESCE(SUM(mb.marketplace_commission), 0) as total_commission,
    COALESCE(AVG(mb.marketplace_total_amount), 0) as avg_booking_value,
    m.health_status,
    m.last_sync_at
FROM marketplaces m
LEFT JOIN marketplace_products mp ON m.id = mp.marketplace_id AND mp.sync_status = 'synced'
LEFT JOIN marketplace_bookings mb ON mp.id = mb.marketplace_product_id
WHERE m.is_active = TRUE
GROUP BY m.id, m.marketplace_name, m.marketplace_key, m.health_status, m.last_sync_at
ORDER BY gross_revenue DESC;

-- Vista de inventario consolidado
CREATE VIEW consolidated_inventory AS
SELECT 
    p.id as product_id,
    p.nombre as product_name,
    mi.availability_date,
    SUM(mi.total_capacity) as total_capacity,
    SUM(mi.marketplace_allocation) as total_allocated,
    SUM(mi.reserved_bookings) as total_reserved,
    SUM(mi.available_spots) as total_available,
    COUNT(mi.id) as marketplace_count
FROM productos p
JOIN marketplace_products mp ON p.id = mp.product_id
JOIN marketplace_inventory mi ON mp.id = mi.marketplace_product_id
WHERE mi.is_available = TRUE
AND mi.availability_date >= CURRENT_DATE
GROUP BY p.id, p.nombre, mi.availability_date
ORDER BY p.nombre, mi.availability_date;

-- =====================================================
-- DATOS INICIALES - CONFIGURACIÓN DE MARKETPLACES
-- =====================================================

-- Booking.com (simulado)
INSERT INTO marketplaces (
    marketplace_name, marketplace_key, marketplace_type, api_endpoint,
    auth_type, commission_rate, currency_code, sync_frequency
) VALUES (
    'Booking.com Experiences',
    'booking_com',
    'ota',
    'https://distribution-xml.booking.com/2.7',
    'api_key',
    0.1800, -- 18% commission
    'USD',
    'daily'
);

-- GetYourGuide
INSERT INTO marketplaces (
    marketplace_name, marketplace_key, marketplace_type, api_endpoint,
    auth_type, commission_rate, currency_code, sync_frequency
) VALUES (
    'GetYourGuide',
    'getyourguide',
    'experiences',
    'https://api.getyourguide.com/v1',
    'bearer_token',
    0.2000, -- 20% commission
    'USD',
    'hourly'
);

-- Viator
INSERT INTO marketplaces (
    marketplace_name, marketplace_key, marketplace_type, api_endpoint,
    auth_type, commission_rate, currency_code, sync_frequency
) VALUES (
    'Viator',
    'viator',
    'experiences',
    'https://api.viator.com/partner',
    'api_key',
    0.1500, -- 15% commission
    'USD',
    'daily'
);

-- Airbnb Experiences
INSERT INTO marketplaces (
    marketplace_name, marketplace_key, marketplace_type, api_endpoint,
    auth_type, commission_rate, currency_code, sync_frequency
) VALUES (
    'Airbnb Experiences',
    'airbnb_exp',
    'experiences',
    'https://api.airbnb.com/v2/experiences',
    'oauth',
    0.2200, -- 22% commission
    'USD',
    'daily'
);

-- Expedia
INSERT INTO marketplaces (
    marketplace_name, marketplace_key, marketplace_type, api_endpoint,
    auth_type, commission_rate, currency_code, sync_frequency
) VALUES (
    'Expedia Activities',
    'expedia',
    'ota',
    'https://api.expediagroup.com/v3/activities',
    'bearer_token',
    0.1700, -- 17% commission
    'USD',
    'daily'
);