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!")