SIDEL_ScriptsManager/scripts/init_sidel_db.py

#!/usr/bin/env python3
"""
Script de inicialización de base de datos para SIDEL ScriptsManager
Implementa el esquema completo según las especificaciones del proyecto
"""

import os
import sqlite3
import sys
from pathlib import Path
from datetime import datetime
import hashlib
import json

# Agregar el directorio raíz del proyecto al path
project_root = Path(__file__).parent.parent
sys.path.insert(0, str(project_root))

def create_database_schema(db_path):
    """
    Crear el esquema completo de la base de datos SQLite
    según las especificaciones SIDEL ScriptsManager
    """
    
    # Crear directorio si no existe
    db_dir = os.path.dirname(db_path)
    if db_dir:
        os.makedirs(db_dir, exist_ok=True)
    
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    
    print(f"Inicializando base de datos SQLite en: {db_path}")
    
    # === TABLA USERS ===
    print("Creando tabla users...")
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username VARCHAR(50) UNIQUE NOT NULL,
            email VARCHAR(100) UNIQUE NOT NULL,
            password_hash VARCHAR(255) NOT NULL,
            user_level VARCHAR(20) NOT NULL CHECK (user_level IN ('admin', 'developer', 'operator', 'viewer')),
            preferred_language VARCHAR(5) DEFAULT 'en' CHECK (preferred_language IN ('en', 'es', 'it', 'fr')),
            preferred_theme VARCHAR(10) DEFAULT 'light' CHECK (preferred_theme IN ('light', 'dark')),
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            last_login TIMESTAMP,
            is_active BOOLEAN DEFAULT TRUE
        )
    """)
    
    # === TABLA SCRIPT_GROUPS ===
    print("Creando tabla script_groups...")
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS script_groups (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name VARCHAR(100) NOT NULL,
            directory_path VARCHAR(255) NOT NULL,
            description TEXT,
            required_level VARCHAR(20) NOT NULL CHECK (required_level IN ('admin', 'developer', 'operator', 'viewer')),
            conda_environment VARCHAR(100) DEFAULT 'base',
            is_active BOOLEAN DEFAULT TRUE,
            discovered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    """)
    
    # === TABLA SCRIPTS ===
    print("Creando tabla scripts...")
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS scripts (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            group_id INTEGER REFERENCES script_groups(id) ON DELETE CASCADE,
            filename VARCHAR(100) NOT NULL,
            display_name VARCHAR(100),
            description TEXT,
            description_long_path VARCHAR(255),
            tags TEXT,
            required_level VARCHAR(20) NOT NULL CHECK (required_level IN ('admin', 'developer', 'operator', 'viewer')),
            parameters JSON,
            execution_timeout INTEGER DEFAULT 300,
            flask_port INTEGER,
            is_active BOOLEAN DEFAULT TRUE,
            last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            UNIQUE(group_id, filename)
        )
    """)
    
    # === TABLA USER_SCRIPT_TAGS ===
    print("Creando tabla user_script_tags...")
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS user_script_tags (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
            script_id INTEGER REFERENCES scripts(id) ON DELETE CASCADE,
            tags TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            UNIQUE(user_id, script_id)
        )
    """)
    
    # === TABLA CONDA_ENVIRONMENTS ===
    print("Creando tabla conda_environments...")
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS conda_environments (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name VARCHAR(100) UNIQUE NOT NULL,
            path VARCHAR(255) NOT NULL,
            python_version VARCHAR(20),
            is_available BOOLEAN DEFAULT TRUE,
            detected_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            last_verified TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    """)
    
    # === TABLA USER_PROJECTS ===
    print("Creando tabla user_projects...")
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS user_projects (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
            project_name VARCHAR(100) NOT NULL,
            group_id INTEGER REFERENCES script_groups(id) ON DELETE CASCADE,
            description TEXT,
            is_default BOOLEAN DEFAULT FALSE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            last_accessed TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            UNIQUE(user_id, project_name, group_id)
        )
    """)
    
    # === TABLA PORT_ALLOCATIONS ===
    print("Creando tabla port_allocations...")
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS port_allocations (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            port_number INTEGER UNIQUE NOT NULL CHECK (port_number >= 5200 AND port_number <= 5400),
            script_id INTEGER REFERENCES scripts(id) ON DELETE CASCADE,
            user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
            project_id INTEGER REFERENCES user_projects(id) ON DELETE CASCADE,
            process_id INTEGER,
            status VARCHAR(20) NOT NULL CHECK (status IN ('allocated', 'active', 'released')),
            allocated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            released_at TIMESTAMP
        )
    """)
    
    # === TABLA SCRIPT_PROCESSES ===
    print("Creando tabla script_processes...")
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS script_processes (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            script_id INTEGER REFERENCES scripts(id) ON DELETE CASCADE,
            user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
            process_id INTEGER NOT NULL,
            flask_port INTEGER CHECK (flask_port >= 5200 AND flask_port <= 5400),
            tab_session_id VARCHAR(100),
            status VARCHAR(20) NOT NULL CHECK (status IN ('starting', 'running', 'stopped', 'failed')),
            started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            last_ping TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            ended_at TIMESTAMP
        )
    """)
    
    # === TABLA EXECUTION_LOGS ===
    print("Creando tabla execution_logs...")
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS execution_logs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            script_id INTEGER REFERENCES scripts(id) ON DELETE CASCADE,
            user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
            project_id INTEGER REFERENCES user_projects(id) ON DELETE CASCADE,
            session_id VARCHAR(100),
            execution_uuid VARCHAR(36) UNIQUE NOT NULL,
            status VARCHAR(20) NOT NULL CHECK (status IN ('running', 'completed', 'failed', 'terminated')),
            start_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            end_time TIMESTAMP,
            duration_seconds INTEGER,
            output TEXT,
            error_output TEXT,
            debug_output TEXT,
            exit_code INTEGER,
            parameters JSON,
            conda_environment VARCHAR(100),
            flask_port INTEGER,
            log_level VARCHAR(10) DEFAULT 'INFO' CHECK (log_level IN ('DEBUG', 'INFO', 'WARNING', 'ERROR')),
            tags TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    """)
    
    # === ÍNDICES PARA OPTIMIZACIÓN ===
    print("Creando índices...")
    
    # Índices para búsquedas frecuentes
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_users_username ON users(username)")
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_users_email ON users(email)")
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_scripts_group_id ON scripts(group_id)")
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_user_projects_user_id ON user_projects(user_id)")
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_user_projects_group_id ON user_projects(group_id)")
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_execution_logs_user_id ON execution_logs(user_id)")
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_execution_logs_script_id ON execution_logs(script_id)")
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_execution_logs_project_id ON execution_logs(project_id)")
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_execution_logs_status ON execution_logs(status)")
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_execution_logs_start_time ON execution_logs(start_time)")
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_port_allocations_port ON port_allocations(port_number)")
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_port_allocations_status ON port_allocations(status)")
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_script_processes_user_id ON script_processes(user_id)")
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_script_processes_status ON script_processes(status)")
    
    conn.commit()
    conn.close()
    
    print("✅ Esquema de base de datos creado exitosamente")

def create_default_data(db_path):
    """
    Crear datos iniciales según especificaciones SIDEL
    """
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    
    print("Insertando datos iniciales...")
    
    # === ENTORNOS CONDA POR DEFECTO ===
    print("Agregando entornos conda...")
    conda_envs = [
        ('scriptsmanager', '/opt/conda/envs/scriptsmanager', '3.12', True),
        ('tsnet', '/opt/conda/envs/tsnet', '3.12', True),
        ('base', '/opt/conda', '3.12', True)
    ]
    
    for env_name, env_path, python_ver, is_available in conda_envs:
        cursor.execute("""
            INSERT OR IGNORE INTO conda_environments 
            (name, path, python_version, is_available) 
            VALUES (?, ?, ?, ?)
        """, (env_name, env_path, python_ver, is_available))
    
    # === USUARIO ADMINISTRADOR POR DEFECTO ===
    print("Creando usuario administrador por defecto...")
    
    # Hash de la contraseña "admin123" (cambiar en producción)
    password = "admin123"
    password_hash = hashlib.sha256(password.encode()).hexdigest()
    
    cursor.execute("""
        INSERT OR IGNORE INTO users 
        (username, email, password_hash, user_level, preferred_language, preferred_theme) 
        VALUES (?, ?, ?, ?, ?, ?)
    """, ('admin', 'admin@sidel.com', password_hash, 'admin', 'en', 'light'))
    
    # === GRUPOS DE SCRIPTS POR DEFECTO ===
    print("Creando grupos de scripts...")
    
    script_groups = [
        ('Water Hammer Simulation', 'app/backend/script_groups/hammer', 'Scripts for water hammer analysis using TSNet', 'operator', 'tsnet'),
        ('Data Processing', 'app/backend/script_groups/data_processing', 'Data analysis and processing scripts', 'developer', 'scriptsmanager'),
        ('System Utilities', 'app/backend/script_groups/system_utilities', 'System administration and maintenance scripts', 'admin', 'scriptsmanager')
    ]
    
    for name, path, desc, req_level, conda_env in script_groups:
        cursor.execute("""
            INSERT OR IGNORE INTO script_groups 
            (name, directory_path, description, required_level, conda_environment) 
            VALUES (?, ?, ?, ?, ?)
        """, (name, path, desc, req_level, conda_env))
    
    conn.commit()
    conn.close()
    
    print("✅ Datos iniciales insertados exitosamente")

def create_directory_structure(base_path):
    """
    Crear estructura de directorios según especificaciones SIDEL
    """
    print("Creando estructura de directorios...")
    
    directories = [
        # Estructura principal de datos
        "data/script_groups",
        "data/system",
        "logs/executions",
        "logs/system", 
        "logs/audit",
        "backup/daily",
        "instance",
        
        # Estructura de scripts backend (SOLO app/backend/script_groups/)
        "app/backend/script_groups/hammer",
        "app/backend/script_groups/data_processing", 
        "app/backend/script_groups/system_utilities",
        "app/backend/logs/executions",
        
        # Directorios para traducciones e imágenes
        "app/translations",
        "app/static/images",
        "app/static/css",
        "app/static/js",
        "app/static/icons",
        
        # Templates
        "app/templates/admin"
    ]
    
    for directory in directories:
        dir_path = os.path.join(base_path, directory)
        os.makedirs(dir_path, exist_ok=True)
        print(f"  ✓ {directory}")
    
    # Verificar que NO existe backend/script_groups/
    wrong_path = os.path.join(base_path, "backend/script_groups")
    if os.path.exists(wrong_path):
        print(f"❌ ERROR: Encontrado directorio incorrecto: {wrong_path}")
        print("   Según especificaciones SIDEL, los scripts deben estar SOLO en app/backend/script_groups/")
        return False
    
    print("✅ Estructura de directorios creada correctamente")
    return True

def main():
    """Función principal de inicialización"""
    
    print("=" * 60)
    print("SIDEL ScriptsManager - Inicialización de Base de Datos")
    print("=" * 60)
    
    # Determinar rutas
    if os.getenv('DOCKER_CONTAINER'):
        # Ejecutándose en Docker
        base_path = "/app"
        db_path = "/app/data/scriptsmanager.db"
    else:
        # Ejecutándose localmente
        base_path = str(project_root)
        db_path = os.path.join(base_path, "data", "scriptsmanager.db")
    
    print(f"Directorio base: {base_path}")
    print(f"Base de datos: {db_path}")
    print()
    
    try:
        # Crear estructura de directorios
        if not create_directory_structure(base_path):
            return 1
        
        # Crear esquema de base de datos
        create_database_schema(db_path)
        
        # Insertar datos iniciales
        create_default_data(db_path)
        
        print()
        print("=" * 60)
        print("✅ INICIALIZACIÓN COMPLETADA EXITOSAMENTE")
        print("=" * 60)
        print()
        print("Credenciales del administrador por defecto:")
        print("  Usuario: admin")
        print("  Contraseña: admin123")
        print("  ⚠️  CAMBIAR ESTA CONTRASEÑA EN PRODUCCIÓN")
        print()
        print("Entornos conda configurados:")
        print("  - scriptsmanager (Frontend Flask)")
        print("  - tsnet (Scripts Water Hammer)")
        print("  - base (Fallback)")
        print()
        print("Puerto de la aplicación: 5002")
        print("Rango de puertos para scripts: 5200-5400")
        print()
        
        return 0
        
    except Exception as e:
        print(f"❌ ERROR durante la inicialización: {e}")
        import traceback
        traceback.print_exc()
        return 1

if __name__ == "__main__":
    exit_code = main()
    sys.exit(exit_code)