src/database_schema.py

import logging
from src.database_manager import DatabaseManager

def create_all_tables():
    """Create all required database tables"""
    db_manager = DatabaseManager()
    
    # Users table
    users_table = """
    CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(50) UNIQUE NOT NULL,
        email VARCHAR(100) UNIQUE NOT NULL,
        password VARCHAR(255) NOT NULL,
        name VARCHAR(100) NOT NULL,
        role ENUM('practitioner', 'organization') NOT NULL,
        org INT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        last_login TIMESTAMP NULL,
        is_active BOOLEAN DEFAULT TRUE,
        INDEX idx_username (username),
        INDEX idx_email (email),
        INDEX idx_role (role)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    """
    
    # Organizations table
    organizations_table = """
    CREATE TABLE IF NOT EXISTS organizations (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(200) NOT NULL,
        email VARCHAR(100) UNIQUE NOT NULL,
        phone VARCHAR(20),
        country_code VARCHAR(10),
        department VARCHAR(100),
        location VARCHAR(200),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        is_active BOOLEAN DEFAULT TRUE,
        INDEX idx_name (name),
        INDEX idx_email (email)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    """
    
    # Questionnaires table
    questionnaires_table = """
    CREATE TABLE IF NOT EXISTS questionnaires (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        patient_name VARCHAR(100) NOT NULL,
        patient_age INT,
        patient_gender ENUM('Male', 'Female', 'Other'),
        wound_location VARCHAR(200),
        wound_duration VARCHAR(100),
        pain_level INT DEFAULT 0,
        previous_treatment TEXT,
        medical_history TEXT,
        medications TEXT,
        allergies TEXT,
        additional_notes TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        INDEX idx_user_id (user_id),
        INDEX idx_patient_name (patient_name),
        INDEX idx_created_at (created_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    """
    
    # Wound images table
    wound_images_table = """
    CREATE TABLE IF NOT EXISTS wound_images (
        id INT AUTO_INCREMENT PRIMARY KEY,
        questionnaire_id INT NOT NULL,
        image_url VARCHAR(500) NOT NULL,
        original_filename VARCHAR(255),
        file_size INT,
        image_width INT,
        image_height INT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (questionnaire_id) REFERENCES questionnaires(id) ON DELETE CASCADE,
        INDEX idx_questionnaire_id (questionnaire_id),
        INDEX idx_created_at (created_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    """
    
    # AI analyses table
    ai_analyses_table = """
    CREATE TABLE IF NOT EXISTS ai_analyses (
        id INT AUTO_INCREMENT PRIMARY KEY,
        questionnaire_id INT NOT NULL,
        image_id INT,
        analysis_data JSON,
        summary TEXT,
        recommendations TEXT,
        risk_score INT DEFAULT 0,
        risk_level ENUM('Low', 'Moderate', 'High', 'Unknown') DEFAULT 'Unknown',
        processing_time DECIMAL(5,2),
        model_version VARCHAR(50),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (questionnaire_id) REFERENCES questionnaires(id) ON DELETE CASCADE,
        FOREIGN KEY (image_id) REFERENCES wound_images(id) ON DELETE SET NULL,
        INDEX idx_questionnaire_id (questionnaire_id),
        INDEX idx_risk_level (risk_level),
        INDEX idx_created_at (created_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    """
    
    # Analysis sessions table (to link everything together)
    analysis_sessions_table = """
    CREATE TABLE IF NOT EXISTS analysis_sessions (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        questionnaire_id INT NOT NULL,
        image_id INT,
        analysis_id INT,
        session_duration DECIMAL(5,2),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (questionnaire_id) REFERENCES questionnaires(id) ON DELETE CASCADE,
        FOREIGN KEY (image_id) REFERENCES wound_images(id) ON DELETE SET NULL,
        FOREIGN KEY (analysis_id) REFERENCES ai_analyses(id) ON DELETE SET NULL,
        INDEX idx_user_id (user_id),
        INDEX idx_created_at (created_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    """
    
    # User sessions table (for session management)
    user_sessions_table = """
    CREATE TABLE IF NOT EXISTS user_sessions (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        session_token VARCHAR(255) UNIQUE NOT NULL,
        ip_address VARCHAR(45),
        user_agent TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        expires_at TIMESTAMP NOT NULL,
        is_active BOOLEAN DEFAULT TRUE,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        INDEX idx_session_token (session_token),
        INDEX idx_user_id (user_id),
        INDEX idx_expires_at (expires_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    """
    
    # Create tables
    tables = [
        ("users", users_table),
        ("organizations", organizations_table),
        ("questionnaires", questionnaires_table),
        ("wound_images", wound_images_table),
        ("ai_analyses", ai_analyses_table),
        ("analysis_sessions", analysis_sessions_table),
        ("user_sessions", user_sessions_table)
    ]
    
    for table_name, table_sql in tables:
        try:
            result = db_manager.execute_query(table_sql)
            if result is not None:
                logging.info(f"Table '{table_name}' created or already exists")
            else:
                logging.error(f"Failed to create table '{table_name}'")
        except Exception as e:
            logging.error(f"Error creating table '{table_name}': {e}")
    
    # Add foreign key constraints if they don't exist
    add_foreign_keys(db_manager)

def add_foreign_keys(db_manager):
    """Add foreign key constraints"""
    foreign_keys = [
        """
        ALTER TABLE users 
        ADD CONSTRAINT fk_users_org 
        FOREIGN KEY (org) REFERENCES organizations(id) 
        ON DELETE SET NULL
        """,
    ]
    
    for fk_sql in foreign_keys:
        try:
            # Check if foreign key already exists before adding
            db_manager.execute_query(fk_sql)
        except Exception as e:
            # Foreign key might already exist, which is fine
            logging.debug(f"Foreign key constraint note: {e}")

if __name__ == "__main__":
    create_all_tables()
    print("Database schema created successfully!")