""" Database Migration Script for Quality Parameters Safely adds columns and creates tables with proper error handling """ import mysql.connector from mysql.connector import Error import os from dotenv import load_dotenv # Load environment variables load_dotenv() # Database configuration DB_CONFIG = { 'host': os.getenv('DB_HOST', '127.0.0.1'), 'port': int(os.getenv('DB_PORT', 3306)), 'user': os.getenv('DB_USER', 'admin'), 'password': os.getenv('DB_PASSWORD', 'mcube@admin123'), 'database': os.getenv('DB_NAME', 'voicebot_cluster') } def run_migration(): """Run the database migration""" try: # Connect to database connection = mysql.connector.connect(**DB_CONFIG) cursor = connection.cursor() print("✓ Connected to database") # 1. Create quality_parameters table print("\n[1/4] Creating quality_parameters table...") cursor.execute(""" CREATE TABLE IF NOT EXISTS quality_parameters ( id INT AUTO_INCREMENT PRIMARY KEY, bid VARCHAR(50) NOT NULL, parameter_group VARCHAR(255) NOT NULL, parameter_name VARCHAR(255) NOT NULL, parameter_type VARCHAR(100), check_description TEXT, detailed_description TEXT, max_score INT NOT NULL, sample_utterances TEXT, applicability_condition VARCHAR(255), auto_detect_na BOOLEAN DEFAULT FALSE, default_na BOOLEAN DEFAULT FALSE, applicable_once_per_customer BOOLEAN DEFAULT FALSE, applicable_threshold INT, is_fatal BOOLEAN DEFAULT FALSE, enable_subjective_marking BOOLEAN DEFAULT FALSE, status VARCHAR(50) DEFAULT 'Applicable', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_bid (bid), UNIQUE KEY unique_param (bid, parameter_group, parameter_name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci """) connection.commit() print("✓ quality_parameters table created") # 2. Add columns to callanalytics tables tables = ['7987_callanalytics', '7408_callanalytics', '7491_callanalytics'] columns_to_add = [ ('parameter_scores', 'JSON', 'Scores for each quality parameter'), ('parameter_detections', 'JSON', 'Detection details including transcript segments and timestamps'), ('total_possible_score', 'INT', 'Total possible score excluding N/A parameters'), ('parameters_not_applicable', 'JSON', 'List of parameters that were not applicable'), ('talk_listen_ratio', 'VARCHAR(20)', 'Agent:Customer talk ratio (e.g., "60:40")'), ('agent_talk_time', 'FLOAT', 'Total time agent spoke in seconds'), ('customer_talk_time', 'FLOAT', 'Total time customer spoke in seconds'), ('dead_air_percentage', 'FLOAT', 'Percentage of dead air in call'), ('agent_speak_percentage', 'INT', 'Agent speak time as percentage'), ('customer_speak_percentage', 'INT', 'Customer speak time as percentage'), ('talk_listen_assessment', 'VARCHAR(255)', 'Assessment of talk-listen ratio') ] for idx, table in enumerate(tables): print(f"\n[{idx+2}/4] Updating {table}...") # Check which columns already exist cursor.execute(f""" SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '{DB_CONFIG['database']}' AND TABLE_NAME = '{table}' """) existing_columns = {row[0] for row in cursor.fetchall()} # Add only missing columns for col_name, col_type, col_comment in columns_to_add: if col_name not in existing_columns: try: cursor.execute(f""" ALTER TABLE {table} ADD COLUMN {col_name} {col_type} COMMENT '{col_comment}' """) connection.commit() print(f" ✓ Added column: {col_name}") except Error as e: print(f" ⚠ Warning: Could not add {col_name}: {e}") else: print(f" - Column {col_name} already exists, skipping") # 3. Insert default quality parameters for testing (only if not exist) print("\n[4/4] Inserting default quality parameters for BID 7987...") # Check if parameters already exist cursor.execute("SELECT COUNT(*) FROM quality_parameters WHERE bid = '7987'") count = cursor.fetchone()[0] if count == 0: default_parameters = [ ('7987', 'Opening & Introduction', 'Proper greeting, clear identification', 'Required', 'Did the agent properly greet and identify themselves?', 'The agent should introduce themselves clearly, state their name and company, and provide the reason for the call.', 5, 'Hello, this is [Name] from Jubilant Foods calling...', True), ('7987', 'Active Listening & Understanding', 'Comprehensive Needs Assessment', 'Required', 'Did the agent confirm customer\'s name/issue and listen without interruptions?', 'Agent should actively listen, confirm understanding, and avoid interrupting the customer.', 10, 'Let me confirm - you mentioned that...; I understand your concern about...', True), ('7987', 'Product/Process Knowledge', 'Accuracy of information', 'Required', 'Was the information provided accurate and clear?', 'Agent demonstrates knowledge of products/processes and provides accurate, clear explanations.', 15, 'Our policy is...; The process works like this...; Let me explain how...', True), ('7987', 'Tone & Attitude', 'Maintain calm, positive, empathetic tone', 'Required', 'Did the agent maintain a professional, empathetic tone throughout?', 'Agent maintains a calm, positive, and empathetic tone from the first word to the last.', 10, 'I understand how frustrating that must be; I\'m here to help you with this...', False), ('7987', 'Problem Resolution', 'Effectiveness of solution offered', 'Required', 'Was the issue effectively resolved (First Call Resolution)?', 'Agent provides an effective solution and aims for First Call Resolution (FCR).', 20, 'I\'ve resolved this by...; Your issue has been fixed...', True), ('7987', 'Objection Handling', 'Focus on what can be done', 'Conditional', 'Did the agent handle objections positively?', 'Agent focuses on what can be done rather than limitations when handling objections.', 10, 'What I can do for you is...; Let me see how we can help...', True), ('7987', 'Professionalism & Etiquette', 'Tone, empathy, courtesy', 'Required', 'Did the agent demonstrate professionalism and courtesy?', 'Agent maintains professional tone, shows empathy, and follows compliance requirements.', 10, 'Thank you for your patience; I appreciate you calling us...', False), ('7987', 'Cross Selling', 'Specific product mentioned', 'Conditional', 'Did the agent mention any cross-sell product appropriately?', 'Agent identifies opportunity and mentions specific cross-sell products when appropriate.', 10, 'You might also be interested in...; We also offer...', True), ('7987', 'Wrap-up & Next Steps', 'Clear summary and next steps', 'Required', 'Did the agent provide a clear summary and outline next steps?', 'Agent provides clear summary, confirms customer satisfaction, and outlines any next steps.', 10, 'To summarize, we\'ve...; Your next steps are...; Is there anything else I can help you with?', False) ] insert_query = """ INSERT INTO quality_parameters (bid, parameter_group, parameter_name, parameter_type, check_description, detailed_description, max_score, sample_utterances, auto_detect_na) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s) """ cursor.executemany(insert_query, default_parameters) connection.commit() print(f"✓ Inserted {len(default_parameters)} default quality parameters") else: print(f"✓ Quality parameters already exist for BID 7987 ({count} parameters), skipping insert") print("\n" + "="*60) print("✓ Migration completed successfully!") print("="*60) except Error as e: print(f"\n✗ Error during migration: {e}") return False finally: if connection and connection.is_connected(): cursor.close() connection.close() print("\n✓ Database connection closed") return True if __name__ == "__main__": print("="*60) print("Database Migration: Quality Parameters System") print("="*60) run_migration()