o /^iQ @sdZddlZddlZddlmZmZmZddl m Z ddl m Z ddl mZddlmZddlmZdd lmZ d0d ed eeefd dfd dZddZde d eeeffddZ d0d ede de fddZ d0d ede de fddZdefddZdeeefdefddZdeeefdefdd Zd!eeeefd"efd#d$Zd"ed%ejd!eeeefd&efd'd(Z deeefdefd)d*Z!deeeffd+d,Z"d-eeefdefd.d/Z#dS)1at 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 N)DictAnyList) JSONLoader) DiffEngine)ImportPreviewEngine) UpdateEngine) DataService)ObjectId data_serviceconfigreturncCs|dd}tdtd|stddSt||d}t|d}t}ttdt |||tdtj d rRtj d rRt |||||tj d rdtdt ||dSdS) z Render the JSON vs DB comparison tab. Args: mongo_service: MongoService instance data_service: DataService instance config: Application configuration dictionary json_sources base_pathu🔄 JSON vs MongoDB Comparisona 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 u7❌ Not connected to MongoDB. Please check the sidebar.N collections---comparison_collectioncomparison_sourcecomparison_results)sttitlemarkdown is_connectederrorrrr_init_comparison_state_render_selection_panel session_stateget_render_load_panel_render_results_panel) mongo_servicer r settings_servicejson_base_path json_loader diff_enginepreview_enginer&./var/www/html/IGF-ODF-V3/ui/tabs/json_vs_db.pyrender_json_vs_db_tabs2         r(cCstdtjvr dtj_dtjvrdtj_dtjvrdtj_dtjvr$dtj_dtjvr-dtj_dtjvr8d tj_dSdS) z,Initialize session state for comparison tab.rNrrcomparison_loadedFlast_comparison_keydata_editor_keyr)rrrrrr)r*r+r&r&r&r'rZs       rr#cCstdtd\}}|+|}|r#tjd|ddd}|tj_ntd|jdtj_d}Wdn1ss  z+_render_selection_panel..r Collectioncomparison_collection_selectorzGSelect a collection (only showing collections with data in this source)validation_enabled📋 descriptionu$⚠️ No collections with data in ``_F supportedz ODF Folderfolderz JSON Files file_countz MongoDB Docs,)r subheadercolumnsget_available_sources selectboxrrwarningritemscount_files_in_sourceappendrget_validation_infoinfor*rr)get_collection_infometriccount_documents)r r#r col1col2available_sourcesselected_sourcecollections_configall_collectionsavailable_collections coll_namerBselected_collectionvalidation_info current_keyrMcol3db_countr&r&r'ros     (   $rr$cCstdtddg\}}| tdWdn1s wY|!tjdddd r@t|||||WddSWddS1sKwYdS) a 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) u2️⃣ Load and Comparer@zFClick 'Load & Compare' to analyze differences between JSON and MongoDBNu🔄 Load & Compareload_compare_btnprimaryr/type)rrDrErMbutton_perform_comparison)r r r#r$r!rQrRr&r&r'rs*  "rc' CsDtjj}tjj}tdV|||}|rMtdd|D}t|dkr;td|td WddS||vrMt d| d |d d } |rU| } |j || d } Wdn1sfwYtd | ||| } Wdn1swYd} |r||} | rtd#ddlm}ddlm}|tjj}|}|||| } Wdn1swY| tj_| tj_dtj_tjjd7_dtjvrtj`dtjvrtj`t}|D]}|dd|Dq|r|dd|n|}tdd|D}||}| dd}| ddd}| ddd}| dg}t d!gd|d |d!|rJd"!|nd#d$|d%| d&d'|d(d"!| d)d*|rrd"!|nd+d,| d&d-d.| d/d-d0| dd1d-d2| dd3d-d4|d5|d6| dd7d-d8| dd9d-d:t|d-d;| rg| d}|d<dkrUtd=|d>d-d?|d@d-dA|d<d-dB|dCdD tj"dEddFUt#| dGdD]E\}}|dH}|dI}|dJ|dKdL|}t$dM|dN|dO|D]} tdP| dQdR| dSdT| dUq%t$dVqWdn 1sOwYn t dW|d>d-dB|dCdXn|ru||rut%dYtj"dZd[dFt$d\t&d]t|t&d^t| t&d_| rt'd`d| dDnda|rt$dbdcdd|dDdde}!t(d"!|!| rt$dfdgdd| dDdde}"t(d"!|"t$dht&di| d)t&dj|t&dk||r:| d)r:t$dl|d}#t&dm| d)D]}$|#|$dn}%t&do|$dp|%q%| rd| d)rdt&dq| d}&| d)D]}$|&|$dn}%t&do|$dp|%qOWdn 1sowY|drkr|rt%dsd"!| d)dtd"!|du|| d&krt dvt)dS)wz Perform the comparison between JSON and MongoDB. Args: mongo_service: MongoService instance data_service: DataService instance json_loader: JSONLoader instance diff_engine: DiffEngine instance zLoading data...css|]}|dVqdS)_target_collectionNr3r4rr&r&r' sz&_perform_comparison..r^u7⚠️ ERROR: Mixed collections detected in JSON data: zBThis should not happen. Each collection must be loaded separately.Nu8⚠️ WARNING: JSON records are marked for collection 'z' but you selected ''i)limitzComparing records...z'Validating foreign key relationships...r)ForeignKeyValidator) MongoServiceTnew_records_preview_dfupdate_preview_dfcss|] }|ds|VqdS)r>N startswithr4kr&r&r'rhTsrecs |] }|ddrdVqdS_validation_passedFr^Nr3rfr&r&r'rhZmatching_strategy primary_onlysummaryprimary_key_matchesalternative_key_matchesalternative_keysuD ✅ **Comparison Complete** - **Target Collection:** `z` - **JSON Source:** `z%` - **Expected Document Types:** , rz - **Validation:** / json_countzG records passed document type validation - **Matching Strategy:** `z` - **Primary Keys:** matching_keysz - **Alternative Keys:** Nonez - JSON Records: rCz - MongoDB Records: r]z - Exact Matches: exact_match_countz - Duplicates: duplicate_countz (Primary: z, Alternative: z) - New Records: new_record_countz - New Fields: new_field_countz - Total JSON Fields: z# (all nested fields flattened) invalid_countui ❌ **Foreign Key Validation Failed** - **Total Records Checked:** total_recordsz" - **Valid Records:** valid_countz$ - **Invalid Records:** z) - **Foreign Keys Checked:** foreign_keys_checkedur ⚠️ Some records have invalid foreign key references. See details below. u"❌ Invalid Foreign Key References)expandedinvalidrecorderrorscodeidRecord #z**z . Record: `z`**u ❌ Field `fieldz` = `valuez`: rruc ✅ **Foreign Key Validation Passed** - **Records Checked:** zL All foreign key references are valid! uBℹ️ Foreign key validation is configured but was not performed.u🔍 Debug InformationFz### Data Loading DetailszJSON records loaded: zMongoDB records loaded: zMongoDB records are flattened: css|]}d|vVqdS).Nr&rqr&r&r'rhsN/Az+### Sample JSON Record Keys (first record):cSg|] }|ds|qSr>rorqr&r&r'r7z'_perform_comparison..z.### Sample MongoDB Record Keys (first record):cSrrrorqr&r&r'r7rz### Business Key MatchingzPrimary keys configured: zAlternative keys configured: zMatching strategy: z### Sample Business Key ValueszFrom first JSON record:z NOT FOUNDz : zFrom first MongoDB record:primary_or_alternativeu\💡 **Multi-Key Matching Enabled:** - System first tries to match by primary keys: z0 - If no match, tries alternative keys: zj - This allows matching athletes across different competitions even if their codes differ uc⚠️ Note: Some files were skipped due to document type mismatch. Check console logs for details.)*rrrrspinnerload_collection_from_sourcesetlenrrHpopget_record_limitload_flattened_recordscompare_collectionsget_foreign_keysservices.foreign_key_validatorrkservices.mongorlr validate_foreign_keysrfk_validation_resultsr)r+rmrnupdatekeysrsumget_expected_document_typessuccessjoinexpander enumeraterrMtextanyrrerun)'r r r#r$r!collection_name source_name json_recordstarget_collections record_limit db_recordsresultsr foreign_keysrkrl fk_validatorall_records_to_validateall_json_fieldsrtarget_collectionvalidation_passed_countexpected_typesrwprimary_matches alt_matchesr| fk_summaryidxitemr record_idrsample_json_keyssample_db_keys sample_jsonr/r sample_dbr&r&r'rdsV                          ,    0          %    rdr%c CsTtjj}tdtd\}}}}|tjd|ddddWdn1s+wY|tjd |dd |dd d d d Wdn1sOwY|tjd|dd|ddd dd Wdn1sswY|tjd|ddddWdn1swYtdtd|dddd|dd dd|dddd|dddg\}}} } | t||Wdn1swY|t |||Wdn1swY| t |||Wdn 1s wY| t |WddS1s#wYdS)z Render comparison results with tabs. Args: preview_engine: ImportPreviewEngine instance mongo_service: MongoService instance u3️⃣ Review Differencesz Exact MatchesryrzRecords that are identicalr0N Duplicatesrnormalz8Records with matching business keys but different values)delta delta_colorr0z New RecordsrzRecords not found in MongoDB New FieldsrzFields in JSON not in MongoDBru✅ Exact Matches ()u⚠️ Duplicates (u🆕 New Records (u📋 New Fields () rrrrDrErOrtabs_render_exact_matches_tab_render_duplicates_tab_render_new_records_tab_render_new_fields_tab) r%r rrQrRr\col4tab1tab2tab3tab4r&r&r'rsl            $rrcCs|d}|s tddStdd|D}tdt|d|t|kr,tdtd ||d }|jsh|jd d |j Dd d}tdt|dt|j dtdtj |dddddSdS)z%Render exact matches tab (read-only). exact_matchesuℹ️ No exact matches foundNcsrsrtr3rfr&r&r'rh#rvz,_render_exact_matches_tab..u✅ z* records are identical in JSON and MongoDBu/✅ All records passed document type validationu These records are already in MongoDB and match exactly. **No action needed.** 🟠 **Orange background** = These values exist in MongoDB (identical to JSON) exactcSsg|] }|dr|qSrror4cr&r&r'r76rz-_render_exact_matches_tab..ignore)rEr📊 Displaying  records with z% fields (all nested fields flattened)u<**🟠 These values are identical in both JSON and MongoDB**Tuse_container_width hide_indexheight) rrMrrrrbuild_preview_dataframeemptydroprE dataframe)rr%rvalidated_countdf display_dfr&r&r'rs*     "  rcsT|d}|s tddStdd|D}tdd|D}tdd|D}tdt|d |t|kr?td ntd |d t|d |dkrptd|dd|dd|dd|dgd ntdd|ddtdtdt|D]R\}}|dd} |dd} | dkrd nd!} | dkrd"nd#} t | d$|d%d&| d'| t d(| t d)| Wdn1swYqtd*| |j s(fd+d,} td-j j| d%d.}td/td0tj|d1d1d2d3td4td*t||d5|dSdS)6z+Render duplicates tab with comparison view. duplicatesuℹ️ No duplicates foundNcs$|] }|dddrdVqdS json_recordruFr^Nr3r4dr&r&r'rhQ"z)_render_duplicates_tab..cs"|] }|ddkrdVqdS) match_method primary_keyr^Nr3rr&r&r'rhT csr)ralternative_keyr^Nr3rr&r&r'rhUr⚠️ z potential duplicate(s) foundu9✅ All duplicate records passed document type validation ⚠️ Only rz duplicates passed validationru"🔍 **Match Methods:** - z matched by primary keys: r~rz - z matched by alternative keys: r|u 💡 Alternative key matches allow finding the same athlete across different competitions even when their athlete code differs. z1 These records match by business keys: **z ** u9 **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. z### Duplicate Recordsrunknown matching_keyrru🔑u🔍z Primary KeyzAlternative Keyz Duplicate #r^z - Matched by rzMatch Method: zMatching Values: rcsg}tjD]U\}}|dkr|dq|dkrC|ddkr%|dq|ddkr1|dq|ddkr=|d q|dq|d krM|d q|d krW|d q|dq|S)Nrr}statusnewz,background-color: #90EE90; font-weight: bold differentz,background-color: #FFD700; font-weight: boldmissing_in_jsonz,background-color: #FFB6C1; font-weight: bold json_valuez9background-color: #E3F2FD; border-left: 3px solid #2196F3db_valuez9background-color: #FFF3E0; border-left: 3px solid #FF9800)rrErK)rowstylesrcolcomp_dfr&r'highlight_comparisons$           z4_render_duplicates_tab..highlight_comparisonz### Field-by-Field Comparison)axisrz- field comparisons with full nested structureTrru **Color Legend:** - 🟢 **Green** = New field in JSON (not in MongoDB) - 🟡 **Yellow** = Different values between JSON and MongoDB - 🔴 **Pink** = Field exists in MongoDB but missing in JSON - 🔵 **Blue background** = Value from JSON file - 🟠 **Orange background** = Value from MongoDB - ⚪ **White** = Matching values r)rrMrrHrrrrrrr$build_duplicate_comparison_dataframerstyleapplyr_render_update_controls)rr%r rrrrrduprr match_icon match_labelr  styled_dfr&r r'rDsr          &      rrrc Cstdtdt}dtjvr||tj_tjj}|jr'tddStgd\}}}}} |tj ddd d rL|j |d gd tj_t Wdn1sVwY|tj d ddd rt|j |dgd tj_t Wdn1s~wY|tj dddd r| |tj_t Wdn1swY| tj dddd r||tj_t Wdn1swYt d||} td\}}}}|td| dWdn1swY|td| dWdn 1swY|td| dWdn 1s+wY|td| d Wdn 1sFwYt dt d!t d"tj|d#d#d$d%tjjd&d#d'd(tjjd)d#d'd(tjjd*d#d+d(tjjd,d#d'd(tjjd-d#d+d(tjjd.d#d+d(tjjd/d0d1gd#d2d'd3d4d5tjjd6} | tj_| dd7krt dtd8| dd9| dd:td;dd?d@rt|| |||WddSWddS1swYdSdS)Az Render update controls for selective field updates. Args: duplicates: List of duplicate records collection_name: Name of the collection mongo_service: MongoService instance u🔄 Selective Field Updatesu 💡 **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 rnuℹ️ No fields to updateN)r,r^r^r^r^u✅ Update New Fieldsupdate_new_fieldszMark all new fields for update)r/r0r) status_filteru✅ Update Differentupdate_different_fieldsz$Mark all different fields for updateru ❌ Skip Allskip_all_updateszSkip all updates 🔄 Resetreset_update_decisionszReset to defaultsrrzRecords to Updaterecords_to_updatezFields to Updatefields_to_updater new_fieldszDifferent Fieldsdifferent_fieldsz### Select Fields to UpdateuR **Color Legend:** - 🟢 **Green** (new) - Field exists in JSON but not in MongoDB - 🟡 **Gold** (different) - Field value differs between JSON and MongoDB - 🟣 **Pink** (missing_in_json) - Field exists in MongoDB but not in JSON - 🔵 **Light Blue** - JSON value - 🟠 **Light Orange** - MongoDB value Tfixedrrsmall)disabledwidthzDB IDz Field NamemediumStatusz JSON Valuez MongoDB ValueActionskiprz#Choose whether to update this fieldr.requiredr0r#) _record_index_db_idrrrr_update_decisionupdate_preview_editor_rrnum_rowsr column_configr/ru⚠️ Ready to update z record(s) with z field(s)r@r^u🚀 Apply Updatesapply_updates_btnr`ra)rrDrMrrbuild_update_preview_dataframernrrErcmark_all_for_updatermark_all_for_skiprget_update_summaryrO data_editorr0 NumberColumn TextColumnSelectboxColumnr+rH_apply_field_updates) rrr update_enginerrQrRr\rcol5ry edited_dfr&r&r'rs          '1  $r preview_dfr;c Cstd||}td|dd|d|dd|dd |d d td \}}| tjd ddddr4|||}|sRtd WddS||} | r/zt dt |dOd} g} |D]@} zdt | ddi} | | | d}|j dkr| d7} Wqmty}z| d| ddt|WYd}~qmd}~wwWdn1swY| dkrtd| d|d | rtd!t | d"| dd#D] }td$|qdtj_dtj_d%tj_td&ntd'| r| D]}t|qWn"ty.}ztd(t|WYd}~n d}~wwtd)Wdn 1s?wY|#tjd*d+dd,ratd-tWddSWddS1smwYdS).a2 Apply selected field updates to MongoDB. Args: collection_name: Name of collection preview_df: Preview DataFrame with update decisions duplicates: Original duplicate records update_engine: UpdateEngine instance mongo_service: MongoService instance z### Confirm Updatesu& ⚠️ **You are about to update rz record(s) in z+** This action will: - Update rz field(s) in MongoDB - Preserve all other fields unchanged - Cannot be undone automatically **Fields to update:** - New fields: rz - Different fields: rz r,u✅ Confirm Updatesconfirm_updates_btnr`Tr/rbru#❌ No update operations to performNz Updating z record(s)...r_idfilterrr^zRecord record_indexru✅ Successfully updated z record(s) in collection: `r=rz update(s) failed:z - F1💡 Reload the comparison to see updated resultsu⚠️ No records were updatedu❌ Failed to update: u❌ Collection not found ❌ Cancelcancel_updates_btnr/rzUpdates cancelled)rrr5rHrErcprepare_update_operationsrget_collectionrrr update_onemodified_count ExceptionrKstrrrrrnrr)rMr)rr>rr;r ryrQrR operations collection updated_countrop filter_dictresulterr&r&r'r:ms         *    ;  $r:c Cs|d}|s tddS|r|ddddnd}tdd |D}td t|d |d |t|krBtd |dntd|dt|dtddtjvrb| |dtj_ tjj }|j rkdSt gd\}}} } |tj ddddr||tj_ tWdn1swY| tj dddr||tj_ tWdn1swY| tj dddr| |dtj_ tWdn1swYtdd d!|j D} td"t|d#t| d$tjjd%gd&dd'd(d)tjjd*dd+d(d,d-} |j D]:} | d.sU|| j}|d/vr:tjj| d0d1| | <q|d2krKtjj| d3d1| | <qtjj| d4d1| | <qtj|ddd5d6| d7tjjd8}|tj_ ||}td9t d:\}}} |td;t|d<Wdn 1swY|td=t|d>Wdn 1swY| td?t|d@Wdn 1swYt|d<dkrAtdtdAt|d<dBdCtjvrdtj_t d:dDg\}}|"tj dEdFddGr |dH|d<||dItj_tWdn 1s+wYtjjdurCttjj||dSdSdS)Jz,Render new records tab with import controls. new_recordsuℹ️ No new records foundNrrrercsrrr3rfr&r&r'rhrz*_render_new_records_tab..u🆕 z0 new record(s) ready for import to collection: `r=u✅ All z( records passed document type validationrrz records passed validationu 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 rmr)r,r^r^r^u✅ Import Selected Recordsr`T)rbru❌ Mark All Skip mark_all_skip)r/rreset_decisionsrcSrrrorr&r&r'r7rz+_render_new_records_tab..rrzS data fields (all nested fields flattened). Scroll horizontally to see all columns.zImport?)pendingimportr'z$Select whether to import this recordr!r(Rowz Row index)r"r0r#)_import_decision _row_indexr>)int64int32float64float32zNumeric value from JSON filerboolzBoolean value from JSON filezValue from JSON filer inew_records_editor_r.z### Import Summaryr@z To ImportrZzTo Skipr'PendingrYu⚠️ Ready to import z record(s)pending_importr^u🚀 Apply Importsapply_imports_btnrar)rimport_indicesrVr>)rrMrrrrrHrrrrmrrErcmark_all_for_importrr4r0r9r7rpdtypeCheckboxColumnr8r6r+extract_import_decisionsrOre_show_import_confirmation)rr%r rVrvalidated_recordsrrQrRr\r data_columnsr0r rir= decisionsr&r&r'rs                  rcCsb|d}|s tddStdt|dtd|D] }td|qtddS) zRender new fields tab.ruℹ️ No new fields detectedNr;z new field(s) found in JSONz These fields exist in JSON but not in MongoDB. **Recommendation:** Use the Schema Manager to add these fields before importing. u• uH💡 Go to the collection tab and use Schema Manager to add these fields)rrMrHrrr)rrrr&r&r'rs  r pending_datac Cs|d}|d}|d}|d}tdtdt|d|dtd \}}|tjd d d d drg} |D]} t| trNd| vrN| | dq;| | q;| r^| d d|n|} |j || |d d} t| dkr}td WddS| | } | durtd| d WddSzMt dt| d| d | | }Wdn1swYtdt|jd| ddtj_dtj_dtj_dtj_tdtWnty}ztdt|WYd}~nd}~wwWdn 1swY|'tjd d!d d"r4dtj_td#tWddSWddS1s@wYdS)$a  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 rrgrVr>u### ⚠️ Confirm Importz **You are about to import z record(s) to `zp`** This action will: - Insert new documents into MongoDB - Cannot be undone automatically r,u✅ Confirm Importconfirm_import_btnr`Tr@rrre) unflattenu#❌ No records prepared for import!Nu❌ Collection `z%` not found or MongoDB not connected!z Importing z records to z...u✅ Successfully imported z records to collection: `r=FrEu❌ Failed to import: rFcancel_import_btnrHzImport cancelled)rrrrrErc isinstancedictrKrprepare_records_for_importrJr insert_manyr inserted_idsrrmrr)rerMballoonsrMrNr)rpr%r rrgoriginal_recordsr>rQrRrrecrrecords_to_importrPrTrUr&r&r'rls       &    8  $rl)N)$__doc__ streamlitrpandaspdtypingrrrservices.json_loaderrservices.diff_enginerservices.import_preview_enginerservices.update_enginerservices.data_servicer bsonr rNr(rrrrdrrrr DataFramer:rrrlr&r&r&r's        @f , ^F*   ( j  4