""" JSON vs DB Comparison Tab This tab provides a visual comparison interface between JSON files and MongoDB records. Users can review differences, make import decisions, and manually apply imports. Key features: - Load JSON from ODF directory structure - Compare with MongoDB records - Visual diff display with color coding - Manual import decisions - No automatic writes """ import streamlit as st import pandas as pd from typing import Dict, Any, List from services.json_loader import JSONLoader from services.diff_engine import DiffEngine from services.import_preview_engine import ImportPreviewEngine from services.update_engine import UpdateEngine from services.data_service import DataService from bson import ObjectId from ui.tabs.json_vs_db_individual import _render_individual_record_updates def render_json_vs_db_tab( mongo_service, data_service: DataService, config: Dict[str, Any], settings_service=None ) -> None: """ Render the JSON vs DB comparison tab. Args: mongo_service: MongoService instance data_service: DataService instance config: Application configuration dictionary """ json_base_path = config['json_sources']['base_path'] st.title("🔄 JSON vs MongoDB Comparison") st.markdown(""" Compare JSON files with MongoDB records and manually import selected data. **Workflow:** 1. Select collection and JSON source 2. Load and compare data 3. Review differences 4. Make import decisions 5. Apply selected imports """) # Check MongoDB connection if not mongo_service.is_connected(): st.error("❌ Not connected to MongoDB. Please check the sidebar.") return # Initialize services with config json_loader = JSONLoader(json_base_path, config['collections']) diff_engine = DiffEngine(config['collections']) preview_engine = ImportPreviewEngine() # Initialize session state _init_comparison_state() st.markdown("---") # Step 1: Selection _render_selection_panel(mongo_service, json_loader, config) st.markdown("---") # Step 2: Load and Compare if st.session_state.get('comparison_collection') and st.session_state.get('comparison_source'): _render_load_panel( mongo_service, data_service, json_loader, diff_engine, settings_service ) # Step 3: Results if st.session_state.get('comparison_results'): st.markdown("---") _render_results_panel(preview_engine, mongo_service) def _init_comparison_state(): """Initialize session state for comparison tab.""" if 'comparison_collection' not in st.session_state: st.session_state.comparison_collection = None if 'comparison_source' not in st.session_state: st.session_state.comparison_source = None if 'comparison_results' not in st.session_state: st.session_state.comparison_results = None if 'comparison_loaded' not in st.session_state: st.session_state.comparison_loaded = False if 'last_comparison_key' not in st.session_state: st.session_state.last_comparison_key = None if 'data_editor_key' not in st.session_state: st.session_state.data_editor_key = 0 def _render_selection_panel(mongo_service, json_loader: JSONLoader, config: Dict[str, Any]): """ Render collection and source selection panel. Args: mongo_service: MongoService instance json_loader: JSONLoader instance config: Application configuration dictionary """ st.subheader("1ī¸âƒŖ Select Source and Collection") col1, col2 = st.columns(2) with col1: # Source selector first - show all available source folders available_sources = json_loader.get_available_sources() if available_sources: selected_source = st.selectbox( "JSON Source", options=available_sources, key="comparison_source_selector", help="Select JSON source directory (e.g., tokyo_2020, paris_2024)" ) st.session_state.comparison_source = selected_source else: st.warning(f"⚠ī¸ No JSON sources found in {json_loader.base_path}") st.session_state.comparison_source = None selected_source = None with col2: # Collection selector - filter to collections that have data in selected source collections_config = config['collections'] # Get all collections that allow manual import all_collections = [ name for name, coll_config in collections_config.items() if coll_config.get('enabled', True) and coll_config.get('allow_manual_import', False) ] # Filter to collections that have JSON files in the selected source if selected_source: available_collections = [] for coll_name in all_collections: file_count = json_loader.count_files_in_source(coll_name, selected_source) if file_count > 0: available_collections.append(coll_name) else: available_collections = [] if available_collections: selected_collection = st.selectbox( "Collection", options=available_collections, key="comparison_collection_selector", help="Select a collection (only showing collections with data in this source)" ) st.session_state.comparison_collection = selected_collection # Show validation info for selected collection validation_info = json_loader.get_validation_info(selected_collection) if validation_info['validation_enabled']: st.info(f"📋 {validation_info['description']}") else: if selected_source: st.warning(f"⚠ī¸ No collections with data in `{selected_source}`") st.session_state.comparison_collection = None selected_collection = None # CRITICAL: Check if collection or source changed and clear results current_key = f"{selected_collection}_{st.session_state.comparison_source}" if st.session_state.last_comparison_key != current_key: st.session_state.comparison_results = None st.session_state.comparison_loaded = False st.session_state.last_comparison_key = current_key # Show collection info if selected_collection and st.session_state.comparison_source: info = json_loader.get_collection_info( selected_collection, st.session_state.comparison_source ) if info['supported']: col1, col2, col3 = st.columns(3) with col1: st.metric("ODF Folder", info['folder']) with col2: st.metric("JSON Files", info['file_count']) with col3: db_count = mongo_service.count_documents(selected_collection) st.metric("MongoDB Docs", f"{db_count:,}") def _render_load_panel( mongo_service, data_service: DataService, json_loader: JSONLoader, diff_engine: DiffEngine, settings_service=None ): """ Render load and compare panel. Args: mongo_service: MongoService instance data_service: DataService instance json_loader: JSONLoader instance diff_engine: DiffEngine instance settings_service: SettingsService instance (optional) """ st.subheader("2ī¸âƒŖ Load and Compare") col1, col2 = st.columns([3, 1]) with col1: st.info("Click 'Load & Compare' to analyze differences between JSON and MongoDB") with col2: if st.button( "🔄 Load & Compare", key="load_compare_btn", type="primary" ): _perform_comparison( mongo_service, data_service, json_loader, diff_engine, settings_service ) def _perform_comparison( mongo_service, data_service: DataService, json_loader: JSONLoader, diff_engine: DiffEngine, settings_service=None ): """ Perform the comparison between JSON and MongoDB. Args: mongo_service: MongoService instance data_service: DataService instance json_loader: JSONLoader instance diff_engine: DiffEngine instance """ collection_name = st.session_state.comparison_collection source_name = st.session_state.comparison_source with st.spinner("Loading data..."): # Load JSON records (already flattened by json_loader) json_records = json_loader.load_collection_from_source( collection_name, source_name ) # CRITICAL: Validate that all records belong to the same collection if json_records: target_collections = set(r.get('_target_collection') for r in json_records) if len(target_collections) > 1: st.error(f"⚠ī¸ ERROR: Mixed collections detected in JSON data: {target_collections}") st.error("This should not happen. Each collection must be loaded separately.") return # Verify all records are for the selected collection if collection_name not in target_collections: st.warning(f"⚠ī¸ WARNING: JSON records are marked for collection '{target_collections.pop()}' but you selected '{collection_name}'") # Load MongoDB records (flattened for comparison) # Get record limit from settings (default 5000) record_limit = 5000 if settings_service: record_limit = settings_service.get_record_limit() db_records = data_service.load_flattened_records( collection_name, limit=record_limit ) with st.spinner("Comparing records..."): # Run comparison results = diff_engine.compare_collections( collection_name, json_records, db_records ) # Run foreign key validation if configured fk_validation_results = None if settings_service: foreign_keys = settings_service.get_foreign_keys(collection_name) if foreign_keys: with st.spinner("Validating foreign key relationships..."): from services.foreign_key_validator import ForeignKeyValidator from services.mongo import MongoService fk_validator = ForeignKeyValidator(st.session_state.mongo_service) # Validate all JSON records (new + duplicates) all_records_to_validate = json_records fk_validation_results = fk_validator.validate_foreign_keys( collection_name, all_records_to_validate, foreign_keys ) # Store results st.session_state.comparison_results = results st.session_state.fk_validation_results = fk_validation_results st.session_state.comparison_loaded = True # Increment data_editor_key to force refresh of all data_editor components st.session_state.data_editor_key += 1 # Clear any cached preview dataframes if 'new_records_preview_df' in st.session_state: del st.session_state.new_records_preview_df if 'update_preview_df' in st.session_state: del st.session_state.update_preview_df # Count total flattened fields all_json_fields = set() for record in json_records: all_json_fields.update(k for k in record.keys() if not k.startswith('_')) # Get target collection info target_collection = json_records[0].get('_target_collection', collection_name) if json_records else collection_name # Check if validation was performed validation_passed_count = sum(1 for r in json_records if r.get('_validation_passed', False)) # Get expected document types expected_types = json_loader.get_expected_document_types(target_collection) # Get matching info matching_strategy = results.get('matching_strategy', 'primary_only') primary_matches = results['summary'].get('primary_key_matches', 0) alt_matches = results['summary'].get('alternative_key_matches', 0) alternative_keys = results.get('alternative_keys', []) # Check for JSON duplicates json_duplicates = results.get('json_duplicates', {}) if json_duplicates.get('has_duplicates', False): st.warning(f""" ⚠ī¸ **DUPLICATES DETECTED IN JSON DATA!** Found **{json_duplicates['duplicate_count']} duplicate identities** with **{json_duplicates['total_duplicate_records']} total duplicate records** in your JSON files. These are duplicates WITHIN your JSON data (same person appearing multiple times). The system will automatically consolidate them during import by merging their codes. - Unique identities: {json_duplicates['unique_count']} - Duplicate identities: {json_duplicates['duplicate_count']} - Total records that will be consolidated: {json_duplicates['total_duplicate_records']} 💡 **What happens during import:** - Duplicates will be merged into single records - All codes will be stored in 'codes' array - Only unique people will be imported """) st.success(f""" ✅ **Comparison Complete** - **Target Collection:** `{target_collection}` - **JSON Source:** `{source_name}` - **Expected Document Types:** {', '.join(expected_types) if expected_types else 'Any'} - **Validation:** {validation_passed_count}/{results['json_count']} records passed document type validation - **Matching Strategy:** `{matching_strategy}` - **Primary Keys:** {', '.join(results['matching_keys'])} - **Alternative Keys:** {', '.join(alternative_keys) if alternative_keys else 'None'} - JSON Records: {results['json_count']:,} - JSON Duplicates: {results['summary'].get('json_duplicate_count', 0):,} identities ({results['summary'].get('json_duplicate_records', 0):,} records) - MongoDB Records: {results['db_count']:,} - Exact Matches: {results['summary']['exact_match_count']:,} - Duplicates (JSON vs DB): {results['summary']['duplicate_count']:,} (Primary: {primary_matches}, Alternative: {alt_matches}) - New Records: {results['summary']['new_record_count']:,} - New Fields: {results['summary']['new_field_count']:,} - Total JSON Fields: {len(all_json_fields):,} (all nested fields flattened) """) # Display foreign key validation results if fk_validation_results: fk_summary = fk_validation_results['summary'] if fk_summary['invalid_count'] > 0: st.error(f""" ❌ **Foreign Key Validation Failed** - **Total Records Checked:** {fk_summary['total_records']:,} - **Valid Records:** {fk_summary['valid_count']:,} - **Invalid Records:** {fk_summary['invalid_count']:,} - **Foreign Keys Checked:** {fk_summary['foreign_keys_checked']} ⚠ī¸ Some records have invalid foreign key references. See details below. """) # Show invalid records with st.expander("❌ Invalid Foreign Key References", expanded=True): for idx, item in enumerate(fk_validation_results['invalid'], 1): record = item['record'] errors = item['errors'] record_id = record.get('code', record.get('id', f'Record #{idx}')) st.markdown(f"**{idx}. Record: `{record_id}`**") for error in errors: st.error(f"❌ Field `{error['field']}` = `{error['value']}`: {error['error']}") st.markdown("---") else: st.success(f""" ✅ **Foreign Key Validation Passed** - **Records Checked:** {fk_summary['total_records']:,} - **Foreign Keys Checked:** {fk_summary['foreign_keys_checked']} All foreign key references are valid! """) elif settings_service and settings_service.get_foreign_keys(collection_name): st.info("ℹī¸ Foreign key validation is configured but was not performed.") # Debug information with st.expander("🔍 Debug Information", expanded=False): st.markdown("### Data Loading Details") st.text(f"JSON records loaded: {len(json_records)}") st.text(f"MongoDB records loaded: {len(db_records)}") st.text(f"MongoDB records are flattened: {any('.' in k for k in db_records[0].keys()) if db_records else 'N/A'}") if json_records: st.markdown("### Sample JSON Record Keys (first record):") sample_json_keys = [k for k in json_records[0].keys() if not k.startswith('_')][:20] st.code(', '.join(sample_json_keys)) if db_records: st.markdown("### Sample MongoDB Record Keys (first record):") sample_db_keys = [k for k in db_records[0].keys() if not k.startswith('_')][:20] st.code(', '.join(sample_db_keys)) st.markdown("### Business Key Matching") st.text(f"Primary keys configured: {results['matching_keys']}") st.text(f"Alternative keys configured: {alternative_keys}") st.text(f"Matching strategy: {matching_strategy}") if json_records and results['matching_keys']: st.markdown("### Sample Business Key Values") sample_json = json_records[0] st.text("From first JSON record:") for key in results['matching_keys']: value = sample_json.get(key, 'NOT FOUND') st.text(f" {key}: {value}") if db_records and results['matching_keys']: st.text("From first MongoDB record:") sample_db = db_records[0] for key in results['matching_keys']: value = sample_db.get(key, 'NOT FOUND') st.text(f" {key}: {value}") # Show info about matching strategy if matching_strategy == 'primary_or_alternative' and alternative_keys: st.info(f"""💡 **Multi-Key Matching Enabled:** - System first tries to match by primary keys: {', '.join(results['matching_keys'])} - If no match, tries alternative keys: {', '.join(alternative_keys)} - This allows matching athletes across different competitions even if their codes differ """) # Show warning if some files were skipped if validation_passed_count < results['json_count']: st.warning(f"⚠ī¸ Note: Some files were skipped due to document type mismatch. Check console logs for details.") st.rerun() def _render_results_panel(preview_engine: ImportPreviewEngine, mongo_service): """ Render comparison results with tabs. Args: preview_engine: ImportPreviewEngine instance mongo_service: MongoService instance """ results = st.session_state.comparison_results st.subheader("3ī¸âƒŖ Review Differences") # Summary metrics col1, col2, col3, col4 = st.columns(4) with col1: st.metric( "Exact Matches", results['summary']['exact_match_count'], help="Records that are identical" ) with col2: st.metric( "Duplicates", results['summary']['duplicate_count'], delta=results['summary']['duplicate_count'], delta_color="normal", help="Records with matching business keys but different values" ) with col3: st.metric( "New Records", results['summary']['new_record_count'], delta=results['summary']['new_record_count'], delta_color="normal", help="Records not found in MongoDB" ) with col4: st.metric( "New Fields", results['summary']['new_field_count'], help="Fields in JSON not in MongoDB" ) st.markdown("---") # Tabs for different result categories tab1, tab2, tab3, tab4 = st.tabs([ f"✅ Exact Matches ({results['summary']['exact_match_count']})", f"⚠ī¸ Duplicates ({results['summary']['duplicate_count']})", f"🆕 New Records ({results['summary']['new_record_count']})", f"📋 New Fields ({results['summary']['new_field_count']})" ]) with tab1: _render_exact_matches_tab(results, preview_engine) with tab2: _render_duplicates_tab(results, preview_engine, mongo_service) with tab3: _render_new_records_tab(results, preview_engine, mongo_service) with tab4: _render_new_fields_tab(results) def _render_exact_matches_tab(results: Dict[str, Any], preview_engine: ImportPreviewEngine): """Render exact matches tab (read-only).""" exact_matches = results['exact_matches'] if not exact_matches: st.info("ℹī¸ No exact matches found") return # Check validation status validated_count = sum(1 for r in exact_matches if r.get('_validation_passed', False)) st.success(f"✅ {len(exact_matches)} records are identical in JSON and MongoDB") if validated_count == len(exact_matches): st.info(f"✅ All records passed document type validation") st.markdown(""" These records are already in MongoDB and match exactly. **No action needed.** 🟠 **Orange background** = These values exist in MongoDB (identical to JSON) """) # Build preview DataFrame df = preview_engine.build_preview_dataframe(exact_matches, 'exact') if not df.empty: # Remove metadata columns for display display_df = df.drop(columns=[c for c in df.columns if c.startswith('_')], errors='ignore') st.info(f"📊 Displaying {len(display_df)} records with {len(display_df.columns)} fields (all nested fields flattened)") st.markdown("**🟠 These values are identical in both JSON and MongoDB**") st.dataframe( display_df, use_container_width=True, hide_index=True, height=400 ) def _render_duplicates_tab( results: Dict[str, Any], preview_engine: ImportPreviewEngine, mongo_service ): """Render duplicates tab with comparison view.""" duplicates = results['duplicates'] if not duplicates: st.info("ℹī¸ No duplicates found") return # Check validation status validated_count = sum(1 for d in duplicates if d['json_record'].get('_validation_passed', False)) # Count matches by method primary_matches = sum(1 for d in duplicates if d.get('match_method') == 'primary_key') alt_matches = sum(1 for d in duplicates if d.get('match_method') == 'alternative_key') st.warning(f"⚠ī¸ {len(duplicates)} potential duplicate(s) found") if validated_count == len(duplicates): st.info(f"✅ All duplicate records passed document type validation") else: st.warning(f"⚠ī¸ Only {validated_count}/{len(duplicates)} duplicates passed validation") # Show matching method breakdown if alt_matches > 0: st.info(f"""🔍 **Match Methods:** - {primary_matches} matched by primary keys: {', '.join(results['matching_keys'])} - {alt_matches} matched by alternative keys: {', '.join(results.get('alternative_keys', []))} 💡 Alternative key matches allow finding the same athlete across different competitions even when their athlete code differs. """) else: st.markdown(f""" These records match by business keys: **{', '.join(results['matching_keys'])}** """) st.markdown(""" **Update Workflow:** 1. Review field-by-field comparison below 2. Select which fields to update (✅ Update or ❌ Skip) 3. Apply updates to MongoDB 💡 **Use Case:** When athlete info changes (name, birthdate, nationality), you can selectively update only the changed fields. """) # Show individual duplicate records with match method st.markdown("### Duplicate Records") for idx, dup in enumerate(duplicates): match_method = dup.get('match_method', 'unknown') matching_key = dup.get('matching_key', 'N/A') match_icon = "🔑" if match_method == 'primary_key' else "🔍" match_label = "Primary Key" if match_method == 'primary_key' else "Alternative Key" with st.expander(f"{match_icon} Duplicate #{idx + 1} - Matched by {match_label}: {matching_key}"): st.text(f"Match Method: {match_label}") st.text(f"Matching Values: {matching_key}") st.markdown("---") # Build comparison DataFrame comp_df = preview_engine.build_duplicate_comparison_dataframe(duplicates) if not comp_df.empty: # Enhanced color coding with better visibility and contrast def highlight_comparison(row): styles = [] status = row.get('status', '') for idx, col in enumerate(comp_df.columns): if col == 'field': # Field name - bold with dark background for visibility styles.append('font-weight: bold; color: #1a1a1a; background-color: #f5f5f5') elif col == 'status': if status == 'new': # New field - bright green with dark text styles.append('background-color: #4CAF50; color: white; font-weight: bold') elif status == 'different': # Different - bright orange/amber with dark text styles.append('background-color: #FF9800; color: white; font-weight: bold') elif status == 'missing_in_json': # Missing in JSON - red with white text styles.append('background-color: #f44336; color: white; font-weight: bold') elif status == 'match': # Match - gray styles.append('background-color: #9E9E9E; color: white') else: styles.append('') elif col == 'json_value': # JSON values - blue theme with good contrast if status == 'new': styles.append('background-color: #C8E6C9; color: #1B5E20; font-weight: bold; border-left: 4px solid #4CAF50') elif status == 'different': styles.append('background-color: #BBDEFB; color: #0D47A1; font-weight: bold; border-left: 4px solid #2196F3') else: styles.append('background-color: #E3F2FD; color: #1565C0; border-left: 4px solid #2196F3') elif col == 'db_value': # MongoDB values - orange theme with good contrast if status == 'different': styles.append('background-color: #FFE0B2; color: #E65100; font-weight: bold; border-left: 4px solid #FF9800') elif status == 'missing_in_json': styles.append('background-color: #FFCDD2; color: #B71C1C; font-weight: bold; border-left: 4px solid #f44336') else: styles.append('background-color: #FFF3E0; color: #E65100; border-left: 4px solid #FF9800') else: styles.append('') return styles st.markdown("### Field-by-Field Comparison") # Display styled dataframe styled_df = comp_df.style.apply(highlight_comparison, axis=1) st.info(f"📊 Displaying {len(comp_df)} field comparisons with full nested structure") st.dataframe( styled_df, use_container_width=True, hide_index=True, height=400 ) # Enhanced Legend with visual indicators st.markdown(""" **🎨 Color Legend:** | Status | Color | Meaning | |--------|-------|---------| | đŸŸĸ **NEW** | Green badge | Field exists in JSON but NOT in MongoDB | | 🟠 **DIFFERENT** | Orange badge | Field value differs between JSON and MongoDB | | 🔴 **MISSING** | Red badge | Field exists in MongoDB but NOT in JSON | | âšĒ **MATCH** | Gray badge | Values are identical | **Value Columns:** - đŸ”ĩ **Blue background** = JSON value (source) - 🟠 **Orange background** = MongoDB value (current) """) st.markdown("---") # Add update functionality _render_update_controls( duplicates, results['collection_name'], mongo_service ) def _render_update_controls( duplicates: List[Dict[str, Any]], collection_name: str, mongo_service ): """ Render update controls for selective field updates. Args: duplicates: List of duplicate records collection_name: Name of the collection mongo_service: MongoService instance """ st.subheader("🔄 Selective Field Updates") # View mode selector col1, col2 = st.columns([3, 1]) with col1: st.info(""" 💡 **How it works:** - Select which fields you want to update in MongoDB - New fields are selected by default - Different fields require your decision - Matching fields are skipped by default """) with col2: # Initialize view mode in session state if 'update_view_mode' not in st.session_state: st.session_state.update_view_mode = 'aggregated' view_mode = st.radio( "View Mode", options=['aggregated', 'individual'], format_func=lambda x: "📊 All Fields" if x == 'aggregated' else "📝 By Record", key='update_view_mode_selector', help="Aggregated: All fields in one table\nIndividual: Field comparison per record" ) if view_mode != st.session_state.update_view_mode: st.session_state.update_view_mode = view_mode st.rerun() # Initialize update engine update_engine = UpdateEngine() # Render based on view mode if st.session_state.update_view_mode == 'individual': _render_individual_record_updates(duplicates, collection_name, mongo_service, update_engine) return # Initialize update preview DataFrame in session state (for aggregated view) if 'update_preview_df' not in st.session_state: st.session_state.update_preview_df = update_engine.build_update_preview_dataframe(duplicates) # Action buttons - two rows for better organization st.markdown("#### Quick Actions") col1, col2, col3 = st.columns(3) with col1: if st.button("✅ UPDATE ALL FIELDS", key="update_all_fields", type="primary", help="Mark ALL fields (new + different) for update", use_container_width=True): st.session_state.update_preview_df = update_engine.mark_all_for_update( st.session_state.update_preview_df, status_filter=['new', 'different'] ) st.rerun() with col2: if st.button("❌ Skip All", key="skip_all_updates", help="Skip all updates", use_container_width=True): st.session_state.update_preview_df = update_engine.mark_all_for_skip(st.session_state.update_preview_df) st.rerun() with col3: if st.button("🔄 Reset", key="reset_update_decisions", help="Reset to defaults", use_container_width=True): st.session_state.update_preview_df = update_engine.build_update_preview_dataframe(duplicates) st.rerun() # Second row - selective updates col1, col2, col3 = st.columns(3) with col1: if st.button("✅ Update New Fields Only", key="update_new_fields", help="Mark only NEW fields for update", use_container_width=True): st.session_state.update_preview_df = update_engine.mark_all_for_update( st.session_state.update_preview_df, status_filter=['new'] ) st.rerun() with col2: if st.button("✅ Update Different Fields Only", key="update_different_fields", help="Mark only DIFFERENT fields for update", use_container_width=True): st.session_state.update_preview_df = update_engine.mark_all_for_update( st.session_state.update_preview_df, status_filter=['different'] ) st.rerun() st.markdown("---") # Get current DataFrame from session state df = st.session_state.update_preview_df if df.empty: st.info("ℹī¸ No fields to update") return # Show update summary summary = update_engine.get_update_summary(df) col1, col2, col3, col4 = st.columns(4) with col1: st.metric("Records to Update", summary['records_to_update']) with col2: st.metric("Fields to Update", summary['fields_to_update']) with col3: st.metric("New Fields", summary['new_fields']) with col4: st.metric("Different Fields", summary['different_fields']) st.markdown("---") # Editable table for field selection st.markdown("### Select Fields to Update") # Display color legend st.markdown(""" **🎨 Status Legend:** đŸŸĸ NEW (add to DB) | 🟠 DIFFERENT (update in DB) | 🔴 MISSING (only in DB) """) edited_df = st.data_editor( df, use_container_width=True, hide_index=True, num_rows="fixed", height=400, column_config={ '_record_index': st.column_config.NumberColumn( 'Record #', disabled=True, width='small' ), '_db_id': st.column_config.TextColumn( 'DB ID', disabled=True, width='small' ), 'field': st.column_config.TextColumn( 'Field Name', disabled=True, width='medium' ), 'status': st.column_config.TextColumn( 'Status', disabled=True, width='small' ), 'json_value': st.column_config.TextColumn( 'JSON Value', disabled=True, width='medium' ), 'db_value': st.column_config.TextColumn( 'MongoDB Value', disabled=True, width='medium' ), '_update_decision': st.column_config.SelectboxColumn( 'Action', options=['skip', 'update'], required=True, help="Choose whether to update this field", width='small' ) }, key=f"update_preview_editor_{st.session_state.data_editor_key}" ) # Update session state st.session_state.update_preview_df = edited_df # Apply updates button if summary['fields_to_update'] > 0: st.markdown("---") st.warning(f"⚠ī¸ Ready to update {summary['records_to_update']} record(s) with {summary['fields_to_update']} field(s)") # Initialize pending updates state if 'pending_updates' not in st.session_state: st.session_state.pending_updates = None col1, col2 = st.columns([3, 1]) with col2: if st.button( "🚀 Apply Updates", key="apply_updates_btn", type="primary" ): # Store pending update data in session state st.session_state.pending_updates = { 'collection_name': collection_name, 'preview_df': edited_df, 'duplicates': duplicates, 'summary': summary } st.rerun() # Show confirmation dialog if pending updates exist if st.session_state.pending_updates is not None: _show_update_confirmation( st.session_state.pending_updates, update_engine, mongo_service ) def _show_update_confirmation( pending_data: Dict[str, Any], update_engine: UpdateEngine, mongo_service ): """ Show update confirmation dialog and execute updates. Args: pending_data: Dictionary with collection_name, preview_df, duplicates, summary update_engine: UpdateEngine instance mongo_service: MongoService instance """ collection_name = pending_data['collection_name'] preview_df = pending_data['preview_df'] duplicates = pending_data['duplicates'] summary = pending_data['summary'] st.markdown("### ⚠ī¸ Confirm Updates") st.error(f""" **You are about to update {summary['records_to_update']} record(s) in `{collection_name}`** This action will: - Update {summary['fields_to_update']} field(s) in MongoDB - Preserve all other fields unchanged - Cannot be undone automatically **Fields to update:** - New fields: {summary['new_fields']} - Different fields: {summary['different_fields']} """) col1, col2 = st.columns(2) with col1: if st.button( "✅ Confirm Updates", key="confirm_updates_btn", type="primary", use_container_width=True ): # Prepare update operations operations = update_engine.prepare_update_operations(preview_df, duplicates) if not operations: st.error("❌ No update operations to perform") return # Get collection collection = mongo_service.get_collection(collection_name) if collection is not None: try: with st.spinner(f"Updating {len(operations)} record(s)..."): # Execute updates updated_count = 0 errors = [] for op in operations: try: # Convert string _id back to ObjectId filter_dict = {'_id': ObjectId(op['filter']['_id'])} result = collection.update_one(filter_dict, op['update']) if result.modified_count > 0: updated_count += 1 except Exception as e: errors.append(f"Record {op['record_index']}: {str(e)}") if updated_count > 0: st.success(f"✅ Successfully updated {updated_count} record(s) in collection: `{collection_name}`") if errors: st.warning(f"⚠ī¸ {len(errors)} update(s) failed:") for error in errors[:5]: # Show first 5 errors st.text(f" - {error}") # Clear preview and pending updates st.session_state.update_preview_df = None st.session_state.comparison_results = None st.session_state.comparison_loaded = False st.session_state.pending_updates = None st.info("💡 Reload the comparison to see updated results") else: st.warning("⚠ī¸ No records were updated") if errors: for error in errors: st.error(error) except Exception as e: st.error(f"❌ Failed to update: {str(e)}") else: st.error("❌ Collection not found") with col2: if st.button( "❌ Cancel", key="cancel_updates_btn", use_container_width=True ): st.session_state.pending_updates = None st.info("Updates cancelled") st.rerun() def _render_new_records_tab( results: Dict[str, Any], preview_engine: ImportPreviewEngine, mongo_service ): """Render new records tab with import controls.""" new_records = results['new_records'] if not new_records: st.info("ℹī¸ No new records found") return # Get target collection from first record target_collection = new_records[0]['json_record'].get('_target_collection', 'unknown') if new_records else 'unknown' # Check validation status validated_records = sum(1 for r in new_records if r['json_record'].get('_validation_passed', False)) st.success(f"🆕 {len(new_records)} new record(s) ready for import to collection: `{target_collection}`") if validated_records == len(new_records): st.info(f"✅ All {validated_records} records passed document type validation") else: st.warning(f"⚠ī¸ Only {validated_records}/{len(new_records)} records passed validation") st.markdown(""" These records are not in MongoDB. You can: - Review and edit the data - Select records to import - Apply imports manually đŸ”ĩ **Blue background** = Values from JSON files (not yet in MongoDB) ✅ **Validated** = Document type matches target collection """) # Initialize preview DataFrame in session state if 'new_records_preview_df' not in st.session_state: st.session_state.new_records_preview_df = preview_engine.build_preview_dataframe( new_records, 'new' ) df = st.session_state.new_records_preview_df if df.empty: return # Action buttons col1, col2, col3, col4 = st.columns([2, 1, 1, 1]) with col2: if st.button("✅ Import Selected Records", type="primary", use_container_width=True): st.session_state.new_records_preview_df = preview_engine.mark_all_for_import(df) st.rerun() with col3: if st.button("❌ Mark All Skip", key="mark_all_skip"): st.session_state.new_records_preview_df = preview_engine.mark_all_for_skip(df) st.rerun() with col4: if st.button("🔄 Reset", key="reset_decisions"): st.session_state.new_records_preview_df = preview_engine.build_preview_dataframe( new_records, 'new' ) st.rerun() st.markdown("---") # Show field count info data_columns = [c for c in df.columns if not c.startswith('_')] st.info(f"📊 Displaying {len(df)} records with {len(data_columns)} data fields (all nested fields flattened). Scroll horizontally to see all columns.") # Build column config with automatic type detection column_config = { '_import_decision': st.column_config.SelectboxColumn( 'Import?', options=['pending', 'import', 'skip'], required=True, help="Select whether to import this record", width='small' ), '_row_index': st.column_config.NumberColumn( 'Row', disabled=True, help="Row index", width='small' ) } # Add config for data columns with automatic type detection # First pass: detect and convert boolean columns to strings for col in df.columns: if not col.startswith('_'): dtype = df[col].dtype # Check if column contains boolean values is_boolean = False if dtype == 'bool' or str(dtype) == 'boolean': is_boolean = True elif dtype == 'object': non_null_values = df[col].dropna() if len(non_null_values) > 0: sample_values = list(non_null_values.head(100)) bool_count = sum(1 for v in sample_values if isinstance(v, bool)) if bool_count > 0 and bool_count == len(sample_values): is_boolean = True # Convert boolean columns to string to avoid type conflicts if is_boolean: df[col] = df[col].apply(lambda x: str(x) if pd.notna(x) else '') # Second pass: build column config for col in df.columns: if not col.startswith('_'): dtype = df[col].dtype if dtype in ['int64', 'int32', 'float64', 'float32']: column_config[col] = st.column_config.NumberColumn( col, help=f"Numeric value from JSON file" ) else: # For all other types (including converted booleans), use TextColumn column_config[col] = st.column_config.TextColumn( col, help=f"Value from JSON file" ) # Editable table with horizontal scrolling edited_df = st.data_editor( df, use_container_width=True, hide_index=True, num_rows="fixed", height=500, column_config=column_config, key=f"new_records_editor_{st.session_state.data_editor_key}" ) # Update session state st.session_state.new_records_preview_df = edited_df # Extract decisions decisions = preview_engine.extract_import_decisions(edited_df) # Show summary st.markdown("### Import Summary") col1, col2, col3 = st.columns(3) with col1: st.metric("To Import", len(decisions['import'])) with col2: st.metric("To Skip", len(decisions['skip'])) with col3: st.metric("Pending", len(decisions['pending'])) # Apply button if len(decisions['import']) > 0: st.markdown("---") st.warning(f"⚠ī¸ Ready to import {len(decisions['import'])} record(s)") # Initialize confirmation state if 'pending_import' not in st.session_state: st.session_state.pending_import = None col1, col2 = st.columns([3, 1]) with col2: if st.button( "🚀 Apply Imports", key="apply_imports_btn", type="primary" ): # Store pending import data in session state st.session_state.pending_import = { 'collection_name': results['collection_name'], 'import_indices': decisions['import'], 'new_records': new_records, 'preview_df': edited_df } st.rerun() # Show confirmation dialog if pending import exists if st.session_state.pending_import is not None: _show_import_confirmation( st.session_state.pending_import, preview_engine, mongo_service ) def _render_new_fields_tab(results: Dict[str, Any]): """Render new fields tab.""" new_fields = results['new_fields'] if not new_fields: st.info("ℹī¸ No new fields detected") return st.warning(f"📋 {len(new_fields)} new field(s) found in JSON") st.markdown(""" These fields exist in JSON but not in MongoDB. **Recommendation:** Use the Schema Manager to add these fields before importing. """) # Display fields for field in new_fields: st.text(f"â€ĸ {field}") st.info("💡 Go to the collection tab and use Schema Manager to add these fields") def _show_import_confirmation( pending_data: Dict[str, Any], preview_engine: ImportPreviewEngine, mongo_service ): """ Show import confirmation dialog and execute import. Args: pending_data: Dictionary with collection_name, import_indices, new_records, preview_df preview_engine: ImportPreviewEngine instance mongo_service: MongoService instance """ collection_name = pending_data['collection_name'] import_indices = pending_data['import_indices'] original_records = pending_data['new_records'] preview_df = pending_data['preview_df'] st.markdown("### ⚠ī¸ Confirm Import") st.error(f""" **You are about to import {len(import_indices)} record(s) to `{collection_name}`** This action will: - Insert new documents into MongoDB - Cannot be undone automatically """) col1, col2 = st.columns(2) with col1: if st.button( "✅ Confirm Import", key="confirm_import_btn", type="primary", use_container_width=True ): # Extract json_record from original_records structure # original_records is a list of dicts with 'json_record' key json_records = [] for rec in original_records: if isinstance(rec, dict) and 'json_record' in rec: json_records.append(rec['json_record']) else: json_records.append(rec) # Get target collection from records target_collection = json_records[0].get('_target_collection', collection_name) if json_records else collection_name # Prepare records (unflatten for MongoDB) # For people collection, this will automatically consolidate duplicates records_to_import = preview_engine.prepare_records_for_import( preview_df, json_records, import_indices, unflatten=True, # Convert back to nested structure for MongoDB collection_name=target_collection, mongo_service=mongo_service ) if len(records_to_import) == 0: st.error("❌ No records prepared for import!") return # Insert to MongoDB using the TARGET collection collection = mongo_service.get_collection(target_collection) if collection is None: st.error(f"❌ Collection `{target_collection}` not found or MongoDB not connected!") return try: with st.spinner(f"Importing {len(records_to_import)} records to {target_collection}..."): result = collection.insert_many(records_to_import) st.success(f"✅ Successfully imported {len(result.inserted_ids)} records to collection: `{target_collection}`") # Clear preview and pending import st.session_state.new_records_preview_df = None st.session_state.comparison_results = None st.session_state.comparison_loaded = False st.session_state.pending_import = None st.info("💡 Reload the comparison to see updated results") st.balloons() except Exception as e: st.error(f"❌ Failed to import: {str(e)}") with col2: if st.button( "❌ Cancel", key="cancel_import_btn", use_container_width=True ): st.session_state.pending_import = None st.info("Import cancelled") st.rerun()