"""Script para crear la tabla funel_compra"""

from connection import get_connection
import logging

logging.basicConfig(level=logging.INFO, format='%(message)s')
logger = logging.getLogger(__name__)

def create_funel_compra_table():
    """Crea la tabla funel_compra en la BD"""
    
    conn = get_connection()
    
    if not conn:
        logger.error("❌ No se pudo conectar a la base de datos")
        return False
    
    try:
        cursor = conn.cursor()
        
        logger.info("📝 Creando tabla funel_compra...")
        
        sql = """
        CREATE TABLE IF NOT EXISTS `funel_compra` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `usuario` VARCHAR(255) NOT NULL,
            `mail` VARCHAR(255) NOT NULL,
            `accion` VARCHAR(255) NOT NULL,
            `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(),
            INDEX idx_usuario (usuario),
            INDEX idx_mail (mail),
            INDEX idx_accion (accion),
            INDEX idx_created_at (created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        """
        
        cursor.execute(sql)
        conn.commit()
        
        logger.info("✅ Tabla 'funel_compra' creada exitosamente")
        
        # Describir tabla
        cursor.execute("DESCRIBE funel_compra")
        fields = cursor.fetchall()
        cursor.close()
        conn.close()
        
        logger.info("\n📋 Estructura de la tabla:")
        logger.info("=" * 100)
        logger.info(f"{'Campo':<20} | {'Tipo':<25} | {'Null':<6} | {'Key':<8} | {'Extra':<30}")
        logger.info("-" * 100)
        for field in fields:
            field_name = field[0]
            field_type = field[1]
            field_null = field[2]
            field_key = field[3]
            field_extra = str(field[5]) if field[5] else '-'
            logger.info(f"{field_name:<20} | {field_type:<25} | {field_null:<6} | {field_key:<8} | {field_extra:<30}")
        logger.info("=" * 100)
        
        return True
        
    except Exception as e:
        logger.error(f"❌ Error al crear la tabla: {str(e)}")
        conn.close()
        return False


if __name__ == "__main__":
    create_funel_compra_table()