from app.models import SessionLocal, OrderRecord, DitRecord, MatchResult
from app.services.fuzzy_matcher import FuzzyMatcher, normalize_pn_for_matching, normalize_customer_name, normalize_id
import pandas as pd
import sys

def debug_specific_match():
    db = SessionLocal()
    with open("debug_match_output.txt", "w", encoding="utf-8") as f:
        try:
            target_pn = "PJQ5514S6C-AU R2 002A1"
            target_norm_pn = normalize_pn_for_matching(target_pn)
            target_erp = "184100"
            
            f.write(f"Target Norm PN: {target_norm_pn}\n")
            f.write(f"Target ERP: {target_erp}\n")
            
            # 1. Check DIT Records
            dits = db.query(DitRecord).all()
            matching_dits = []
            for d in dits:
                d_norm_pn = normalize_pn_for_matching(d.pn)
                d_erp = normalize_id(d.erp_account)
                if d_norm_pn == target_norm_pn or d_erp == target_erp:
                    matching_dits.append(d)
            
            f.write(f"\nFound {len(matching_dits)} DIT records matching PN or ERP:\n")
            for d in matching_dits:
                f.write(f"  ID: {d.id}, Op: {d.op_id}, Cust: {d.customer}, ERP: {d.erp_account}, PN: {d.pn}, Date: {d.date}\n")
                f.write(f"    Norm PN: {normalize_pn_for_matching(d.pn)}, Norm ERP: {normalize_id(d.erp_account)}\n")
                
            # 2. Check Order Records
            orders = db.query(OrderRecord).all()
            matching_orders = []
            for o in orders:
                o_norm_pn = normalize_pn_for_matching(o.pn)
                o_cust_id = normalize_id(o.cust_id)
                if o_norm_pn == target_norm_pn or o_cust_id == target_erp:
                    matching_orders.append(o)
                    
            f.write(f"\nFound {len(matching_orders)} Order records matching PN or ERP:\n")
            for o in matching_orders:
                f.write(f"  ID: {o.id}, OrderNo: {o.order_no}, Cust: {o.customer}, CustID: {o.cust_id}, PN: {o.pn}, Qty: {o.qty}, Date: {o.date}\n")
                f.write(f"    Norm PN: {normalize_pn_for_matching(o.pn)}, Norm CustID: {normalize_id(o.cust_id)}\n")
                
            # 3. Simulate Matching
            if matching_dits:
                for dit in matching_dits:
                    f.write(f"\nSimulating matching for DIT {dit.id} ({dit.op_id})...\n")
                    
                    dit_norm_pn = normalize_pn_for_matching(dit.pn)
                    dit_erp = normalize_id(dit.erp_account)
                    
                    for o in matching_orders:
                        o_norm_pn = normalize_pn_for_matching(o.pn)
                        o_cust_id = normalize_id(o.cust_id)
                        
                        if dit_norm_pn == o_norm_pn:
                            f.write(f"  PN Match with Order {o.id} ({o.order_no})\n")
                            if dit_erp and o_cust_id and dit_erp == o_cust_id:
                                f.write(f"    SUCCESS: Silver Key Match found!\n")
                            else:
                                from app.services.fuzzy_matcher import calculate_similarity
                                score, reason = calculate_similarity(dit.customer, o.customer)
                                f.write(f"    Name Similarity with {o.customer}: {score} ({reason})\n")
                        # else:
                        #     f.write(f"  PN Mismatch: DIT({dit_norm_pn}) != Order({o_norm_pn})\n")

            # 4. Check existing MatchResults
            if matching_dits:
                dit_ids = [d.id for d in matching_dits]
                matches = db.query(MatchResult).filter(MatchResult.dit_id.in_(dit_ids)).all()
                f.write(f"\nFound {len(matches)} existing MatchResults for these DITs:\n")
                for m in matches:
                    f.write(f"  DIT: {m.dit_id}, Target: {m.target_type} {m.target_id}, Score: {m.score}, Status: {m.status}, Priority: {m.match_priority}\n")

        finally:
            db.close()

if __name__ == "__main__":
    debug_specific_match()