import pandas as pd
import os
import glob
import json
import re

# Configuration
root_dir = "/home/hieutt50/projects/DSOL/CustomerDocument"
inventory_csv = "/home/hieutt50/projects/DSOL/document_inventory.csv"
output_json = "/home/hieutt50/projects/DSOL/cross_references.json"

def get_search_keys(df_inventory):
    keys = {}
    
    for idx, row in df_inventory.iterrows():
        fname = row['file_name']
        fpath = row['file_path']
        
        # 1. File ID Pattern (e.g., 01.01.02.02.07)
        # Look for patterns like digits separated by dots, at least 3 segments
        id_match = re.search(r'(\d{2}\.\d{2}\.\d{2}[\.\d]*)', fname)
        if id_match:
            key = id_match.group(1)
            keys[key] = {"type": "FileID", "source": fname}
            
        # 2. Extract Job Names from JP1 files (hardcoded knowledge from previous step)
        if "JP1" in fname:
            # We might want to re-read the file to get exact job names, 
            # but for now let's use the ones we found in the report if we can, 
            # or just generic terms. 
            # Actually, let's add specific known jobs to the search list manually for now
            # to see if they appear in specs.
            pass

    # Add known keys from previous analysis
    known_jobs = [
        "JHConstBORegistOrder", "JHConstBOResult", "EDI_main_start", 
        "JHACStatusFileCheck", "DB抽出", "光SO"
    ]
    for job in known_jobs:
        keys[job] = {"type": "JobName", "source": "JP1_Definitions"}

    return keys

def find_references():
    print("Loading inventory...")
    df_inv = pd.read_csv(inventory_csv)
    search_keys = get_search_keys(df_inv)
    print(f"Identified {len(search_keys)} keys to search for.")
    
    references = []

    # Iterate over all files to search IN them
    for idx, row in df_inv.iterrows():
        target_path = row['file_path']
        target_name = row['file_name']
        
        try:
            # Read file content (all sheets, flattened)
            xls = pd.ExcelFile(target_path)
            all_text = ""
            for sheet in xls.sheet_names:
                df = pd.read_excel(xls, sheet_name=sheet, header=None)
                # Convert to string, drop NaNs, join
                text = " ".join(df.astype(str).values.flatten())
                all_text += " " + text
            
            # Search for keys
            for key, info in search_keys.items():
                # Don't find self-references
                if info['source'] == target_name:
                    continue
                    
                if key in all_text:
                    references.append({
                        "source_file": target_name,
                        "referenced_key": key,
                        "key_type": info['type'],
                        "target_context": info['source']
                    })
                    
        except Exception as e:
            print(f"Skipping {target_name}: {e}")

    # Save results
    with open(output_json, 'w') as f:
        json.dump(references, f, indent=2, ensure_ascii=False)
        
    print(f"Found {len(references)} cross-references.")
    
    # Print summary
    if references:
        df_refs = pd.DataFrame(references)
        print(df_refs.groupby(['key_type', 'referenced_key']).size().sort_values(ascending=False).head(10))

if __name__ == "__main__":
    find_references()