-- =====================================================
-- MULTI-TENANT ARCHITECTURE - FASE 4
-- Sistema SaaS para Franquicias Mayan World Travel
-- =====================================================

USE travel_mvp;

-- Tabla de tenants (franquicias)
CREATE TABLE tenants (
    id INT PRIMARY KEY AUTO_INCREMENT,
    tenant_key VARCHAR(50) NOT NULL UNIQUE, -- Identificador único para URLs
    company_name VARCHAR(200) NOT NULL,
    business_name VARCHAR(200) NOT NULL,
    
    -- Contact information
    contact_email VARCHAR(150) NOT NULL,
    contact_phone VARCHAR(20),
    contact_address TEXT,
    
    -- Geographic coverage
    country_code VARCHAR(2) NOT NULL,
    city VARCHAR(100),
    region VARCHAR(100),
    timezone VARCHAR(50) DEFAULT 'America/Guatemala',
    
    -- Business configuration
    default_language VARCHAR(5) DEFAULT 'es',
    default_currency VARCHAR(3) DEFAULT 'USD',
    business_model ENUM('commission', 'subscription', 'hybrid') DEFAULT 'commission',
    commission_rate DECIMAL(5,4) DEFAULT 0.1000, -- 10% default
    
    -- Branding & customization
    logo_url VARCHAR(500),
    primary_color VARCHAR(7) DEFAULT '#007bff', -- Hex color
    secondary_color VARCHAR(7) DEFAULT '#6c757d',
    custom_css TEXT, -- Custom styles
    custom_domain VARCHAR(200), -- Optional custom domain
    
    -- Subscription & billing
    plan_type ENUM('starter', 'professional', 'enterprise') DEFAULT 'starter',
    monthly_fee DECIMAL(10,2) DEFAULT 99.00,
    max_products INT DEFAULT 100,
    max_bookings_per_month INT DEFAULT 500,
    max_users INT DEFAULT 5,
    
    -- Feature flags (JSON)
    enabled_features JSON, -- {'whatsapp': true, 'analytics': false, 'multilang': true}
    
    -- Status & lifecycle
    status ENUM('trial', 'active', 'suspended', 'cancelled') DEFAULT 'trial',
    trial_ends_at DATE,
    subscription_starts_at DATE,
    last_payment_at TIMESTAMP NULL,
    next_billing_date DATE,
    
    -- Metadata
    onboarding_completed BOOLEAN DEFAULT FALSE,
    setup_wizard_step INT DEFAULT 1, -- 1-5 steps
    notes TEXT,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_tenant_key (tenant_key),
    INDEX idx_status_plan (status, plan_type),
    INDEX idx_country_city (country_code, city),
    INDEX idx_billing_date (next_billing_date),
    INDEX idx_trial_ends (trial_ends_at)
);

-- Tabla de usuarios por tenant (separación de usuarios)
CREATE TABLE tenant_users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    tenant_id INT NOT NULL,
    user_id INT NOT NULL,
    role ENUM('owner', 'admin', 'manager', 'employee') NOT NULL,
    
    -- Permissions (JSON)
    permissions JSON, -- {'products': ['read', 'write'], 'bookings': ['read']}
    
    -- Access control
    is_active BOOLEAN DEFAULT TRUE,
    last_login_at TIMESTAMP NULL,
    last_activity_at TIMESTAMP NULL,
    
    -- Invitation system
    invited_by INT NULL,
    invited_at TIMESTAMP NULL,
    invitation_accepted_at TIMESTAMP NULL,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES usuarios(id) ON DELETE CASCADE,
    FOREIGN KEY (invited_by) REFERENCES usuarios(id) ON DELETE SET NULL,
    
    UNIQUE KEY unique_tenant_user (tenant_id, user_id),
    INDEX idx_tenant_role (tenant_id, role),
    INDEX idx_user_tenants (user_id)
);

-- Tabla de configuraciones por tenant
CREATE TABLE tenant_configurations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    tenant_id INT NOT NULL,
    config_key VARCHAR(100) NOT NULL,
    config_value TEXT NOT NULL,
    config_type ENUM('string', 'integer', 'boolean', 'json', 'encrypted') DEFAULT 'string',
    
    -- Metadata
    description TEXT,
    is_public BOOLEAN DEFAULT FALSE, -- Can be accessed by frontend
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    
    UNIQUE KEY unique_tenant_config (tenant_id, config_key),
    INDEX idx_tenant_configs (tenant_id, is_public)
);

-- Tabla de productos por tenant (aislamiento de datos)
CREATE TABLE tenant_productos (
    id INT PRIMARY KEY AUTO_INCREMENT,
    tenant_id INT NOT NULL,
    product_id INT NOT NULL, -- Reference to main productos table
    
    -- Tenant-specific overrides
    custom_name VARCHAR(200),
    custom_description TEXT,
    custom_price DECIMAL(10,2),
    custom_images JSON, -- Array of image URLs
    
    -- Availability & inventory
    is_available BOOLEAN DEFAULT TRUE,
    max_capacity_override INT NULL,
    booking_cutoff_hours INT DEFAULT 24,
    
    -- Tenant-specific settings
    requires_approval BOOLEAN DEFAULT FALSE,
    auto_confirm BOOLEAN DEFAULT TRUE,
    cancellation_policy TEXT,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES productos(id) ON DELETE CASCADE,
    
    UNIQUE KEY unique_tenant_product (tenant_id, product_id),
    INDEX idx_tenant_available (tenant_id, is_available)
);

-- Tabla de reservas por tenant
CREATE TABLE tenant_reservas (
    id INT PRIMARY KEY AUTO_INCREMENT,
    tenant_id INT NOT NULL,
    booking_id INT NOT NULL, -- Reference to main reservas table
    
    -- Tenant-specific data
    internal_reference VARCHAR(50), -- Tenant's own booking reference
    agent_notes TEXT,
    special_requests TEXT,
    
    -- Commission & pricing
    base_price DECIMAL(10,2) NOT NULL,
    tenant_commission DECIMAL(10,2) NOT NULL,
    tenant_fee DECIMAL(10,2) DEFAULT 0,
    net_amount DECIMAL(10,2) NOT NULL,
    
    -- Payment tracking
    commission_paid BOOLEAN DEFAULT FALSE,
    commission_paid_at TIMESTAMP NULL,
    payment_method VARCHAR(50),
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (booking_id) REFERENCES reservas(id) ON DELETE CASCADE,
    
    UNIQUE KEY unique_tenant_booking (tenant_id, booking_id),
    INDEX idx_tenant_bookings (tenant_id, created_at DESC),
    INDEX idx_commission_due (tenant_id, commission_paid)
);

-- Tabla de analytics por tenant
CREATE TABLE tenant_analytics (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    tenant_id INT NOT NULL,
    
    -- Date dimension
    metric_date DATE NOT NULL,
    metric_hour INT NULL, -- 0-23 for hourly metrics
    
    -- Core metrics
    unique_visitors INT DEFAULT 0,
    page_views INT DEFAULT 0,
    product_views INT DEFAULT 0,
    bookings INT DEFAULT 0,
    revenue DECIMAL(12,2) DEFAULT 0,
    commission DECIMAL(12,2) DEFAULT 0,
    
    -- Conversion metrics
    conversion_rate DECIMAL(5,4) DEFAULT 0,
    avg_order_value DECIMAL(10,2) DEFAULT 0,
    
    -- Traffic sources (JSON)
    traffic_sources JSON, -- {'direct': 45, 'google': 30, 'social': 25}
    top_products JSON, -- [{'id': 1, 'views': 100, 'bookings': 5}]
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    
    UNIQUE KEY unique_tenant_date_hour (tenant_id, metric_date, metric_hour),
    INDEX idx_tenant_date (tenant_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
);

-- Tabla de facturación
CREATE TABLE tenant_invoices (
    id INT PRIMARY KEY AUTO_INCREMENT,
    tenant_id INT NOT NULL,
    
    -- Invoice details
    invoice_number VARCHAR(50) NOT NULL UNIQUE,
    invoice_date DATE NOT NULL,
    due_date DATE NOT NULL,
    
    -- Amounts
    subtotal DECIMAL(10,2) NOT NULL,
    tax_rate DECIMAL(5,4) DEFAULT 0,
    tax_amount DECIMAL(10,2) DEFAULT 0,
    total_amount DECIMAL(10,2) NOT NULL,
    
    -- Billing period
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,
    
    -- Line items (JSON)
    line_items JSON NOT NULL, -- Detailed breakdown
    
    -- Payment tracking
    status ENUM('draft', 'sent', 'paid', 'overdue', 'cancelled') DEFAULT 'draft',
    paid_at TIMESTAMP NULL,
    payment_method VARCHAR(50),
    payment_reference VARCHAR(100),
    
    -- Communication
    sent_at TIMESTAMP NULL,
    reminder_sent_count INT DEFAULT 0,
    last_reminder_sent_at TIMESTAMP NULL,
    
    notes TEXT,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    
    INDEX idx_tenant_invoices (tenant_id, invoice_date DESC),
    INDEX idx_status_due (status, due_date),
    INDEX idx_invoice_number (invoice_number)
);

-- Tabla de logs de actividad por tenant
CREATE TABLE tenant_activity_logs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    tenant_id INT NOT NULL,
    user_id INT NULL,
    
    -- Activity details
    activity_type VARCHAR(50) NOT NULL, -- 'login', 'booking_created', 'product_updated', etc.
    activity_description TEXT NOT NULL,
    
    -- Context data (JSON)
    context_data JSON, -- Additional data about the activity
    
    -- Technical data
    ip_address VARCHAR(45),
    user_agent TEXT,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES usuarios(id) ON DELETE SET NULL,
    
    INDEX idx_tenant_activity (tenant_id, created_at DESC),
    INDEX idx_activity_type (activity_type, created_at DESC),
    INDEX idx_user_activity (user_id, created_at DESC)
) PARTITION BY RANGE (YEAR(created_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
);

-- =====================================================
-- STORED PROCEDURES PARA MULTI-TENANT
-- =====================================================

DELIMITER //

-- Procedure para crear nuevo tenant
CREATE PROCEDURE CreateTenant(
    IN p_tenant_key VARCHAR(50),
    IN p_company_name VARCHAR(200),
    IN p_contact_email VARCHAR(150),
    IN p_country_code VARCHAR(2),
    IN p_plan_type VARCHAR(20),
    IN p_owner_user_id INT
)
BEGIN
    DECLARE v_tenant_id INT;
    
    START TRANSACTION;
    
    -- Crear tenant
    INSERT INTO tenants (
        tenant_key, company_name, business_name, contact_email, 
        country_code, plan_type, status
    ) VALUES (
        p_tenant_key, p_company_name, p_company_name, p_contact_email, 
        p_country_code, p_plan_type, 'trial'
    );
    
    SET v_tenant_id = LAST_INSERT_ID();
    
    -- Asignar owner al tenant
    INSERT INTO tenant_users (tenant_id, user_id, role, permissions)
    VALUES (v_tenant_id, p_owner_user_id, 'owner', '{"all": ["read", "write", "delete"]}');
    
    -- Configuraciones por defecto
    INSERT INTO tenant_configurations (tenant_id, config_key, config_value, config_type) VALUES
    (v_tenant_id, 'booking_confirmation_auto', 'true', 'boolean'),
    (v_tenant_id, 'email_notifications_enabled', 'true', 'boolean'),
    (v_tenant_id, 'whatsapp_integration_enabled', 'false', 'boolean'),
    (v_tenant_id, 'analytics_enabled', 'true', 'boolean'),
    (v_tenant_id, 'multilanguage_enabled', 'false', 'boolean');
    
    -- Log de actividad
    INSERT INTO tenant_activity_logs (tenant_id, user_id, activity_type, activity_description)
    VALUES (v_tenant_id, p_owner_user_id, 'tenant_created', 'Tenant created successfully');
    
    COMMIT;
    
    SELECT v_tenant_id as tenant_id, 'success' as status;
    
END//

-- Procedure para calcular facturación mensual
CREATE PROCEDURE CalculateMonthlyBilling(IN p_tenant_id INT, IN p_billing_month DATE)
BEGIN
    DECLARE v_base_fee DECIMAL(10,2);
    DECLARE v_bookings_count INT DEFAULT 0;
    DECLARE v_commission_total DECIMAL(10,2) DEFAULT 0;
    DECLARE v_overage_fee DECIMAL(10,2) DEFAULT 0;
    DECLARE v_plan_type VARCHAR(20);
    DECLARE v_max_bookings INT;
    
    -- Obtener información del plan
    SELECT plan_type, monthly_fee, max_bookings_per_month 
    INTO v_plan_type, v_base_fee, v_max_bookings
    FROM tenants WHERE id = p_tenant_id;
    
    -- Calcular bookings del mes
    SELECT COUNT(*), COALESCE(SUM(tenant_commission), 0)
    INTO v_bookings_count, v_commission_total
    FROM tenant_reservas tr
    JOIN reservas r ON tr.booking_id = r.id
    WHERE tr.tenant_id = p_tenant_id 
    AND YEAR(r.created_at) = YEAR(p_billing_month)
    AND MONTH(r.created_at) = MONTH(p_billing_month);
    
    -- Calcular overage si excede el límite
    IF v_bookings_count > v_max_bookings THEN
        SET v_overage_fee = (v_bookings_count - v_max_bookings) * 2.00; -- $2 por booking extra
    END IF;
    
    -- Crear factura
    INSERT INTO tenant_invoices (
        tenant_id, invoice_number, invoice_date, due_date,
        subtotal, total_amount, period_start, period_end,
        line_items
    ) VALUES (
        p_tenant_id,
        CONCAT('INV-', p_tenant_id, '-', DATE_FORMAT(p_billing_month, '%Y%m')),
        LAST_DAY(p_billing_month),
        DATE_ADD(LAST_DAY(p_billing_month), INTERVAL 30 DAY),
        v_base_fee + v_overage_fee,
        v_base_fee + v_overage_fee,
        DATE(DATE_FORMAT(p_billing_month, '%Y-%m-01')),
        LAST_DAY(p_billing_month),
        JSON_OBJECT(
            'base_fee', v_base_fee,
            'plan_type', v_plan_type,
            'bookings_count', v_bookings_count,
            'overage_bookings', GREATEST(0, v_bookings_count - v_max_bookings),
            'overage_fee', v_overage_fee,
            'commission_earned', v_commission_total
        )
    );
    
END//

DELIMITER ;

-- =====================================================
-- TRIGGERS PARA MULTI-TENANT
-- =====================================================

DELIMITER //

-- Trigger para actualizar analytics en tiempo real
CREATE TRIGGER update_tenant_analytics
AFTER INSERT ON tenant_reservas
FOR EACH ROW
BEGIN
    INSERT INTO tenant_analytics (tenant_id, metric_date, bookings, revenue, commission)
    VALUES (NEW.tenant_id, CURDATE(), 1, NEW.base_price, NEW.tenant_commission)
    ON DUPLICATE KEY UPDATE
        bookings = bookings + 1,
        revenue = revenue + NEW.base_price,
        commission = commission + NEW.tenant_commission;
END//

DELIMITER ;

-- =====================================================
-- VISTAS PARA REPORTING MULTI-TENANT
-- =====================================================

-- Vista de dashboard para tenants
CREATE VIEW tenant_dashboard AS
SELECT 
    t.id,
    t.tenant_key,
    t.company_name,
    t.status,
    t.plan_type,
    
    -- Current period stats
    COALESCE(ta.bookings, 0) as current_month_bookings,
    COALESCE(ta.revenue, 0) as current_month_revenue,
    COALESCE(ta.commission, 0) as current_month_commission,
    
    -- Product count
    (SELECT COUNT(*) FROM tenant_productos tp WHERE tp.tenant_id = t.id AND tp.is_available = 1) as active_products,
    
    -- User count
    (SELECT COUNT(*) FROM tenant_users tu WHERE tu.tenant_id = t.id AND tu.is_active = 1) as active_users,
    
    -- Billing info
    t.next_billing_date,
    t.monthly_fee
    
FROM tenants t
LEFT JOIN tenant_analytics ta ON t.id = ta.tenant_id 
    AND ta.metric_date = CURDATE() 
    AND ta.metric_hour IS NULL;

-- Vista para master dashboard (para admin central)
CREATE VIEW master_dashboard AS
SELECT 
    COUNT(*) as total_tenants,
    COUNT(CASE WHEN status = 'active' THEN 1 END) as active_tenants,
    COUNT(CASE WHEN status = 'trial' THEN 1 END) as trial_tenants,
    COUNT(CASE WHEN status = 'suspended' THEN 1 END) as suspended_tenants,
    
    SUM(monthly_fee) as total_mrr,
    AVG(monthly_fee) as avg_revenue_per_tenant,
    
    -- This month's stats
    (SELECT SUM(bookings) FROM tenant_analytics 
     WHERE YEAR(metric_date) = YEAR(CURDATE()) 
     AND MONTH(metric_date) = MONTH(CURDATE())) as total_bookings_this_month,
     
    (SELECT SUM(revenue) FROM tenant_analytics 
     WHERE YEAR(metric_date) = YEAR(CURDATE()) 
     AND MONTH(metric_date) = MONTH(CURDATE())) as total_revenue_this_month
     
FROM tenants
WHERE status != 'cancelled';

-- =====================================================
-- DATOS INICIALES PARA TESTING
-- =====================================================

-- Tenant de ejemplo (para testing)
INSERT INTO tenants (
    tenant_key, company_name, business_name, contact_email, 
    country_code, plan_type, status, primary_color, secondary_color
) VALUES 
(
    'demo-guatemala', 'Guatemala Maya Tours', 'Guatemala Maya Tours', 
    'demo@guatemalamayatours.com', 'GT', 'professional', 'active',
    '#28a745', '#dc3545'
);

-- Configuraciones del tenant demo
SET @demo_tenant_id = LAST_INSERT_ID();

INSERT INTO tenant_configurations (tenant_id, config_key, config_value, config_type) VALUES
(@demo_tenant_id, 'whatsapp_number', '+50212345678', 'string'),
(@demo_tenant_id, 'booking_confirmation_auto', 'true', 'boolean'),
(@demo_tenant_id, 'payment_gateway', 'stripe', 'string'),
(@demo_tenant_id, 'default_cancellation_policy', '24 hours before the tour', 'string');