pfm-ocr/backend/compare_accuracy.py

import json
import os
import glob
import pandas as pd
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter

def clean_val(val):
    if val is None:
        return ""
    s = str(val).strip().upper()
    s = " ".join(s.split())
    s = s.replace("PT. ", "PT.")
    s = s.replace("✓", "").replace("✔", "").strip()
    return s

def main():
    jsonl_file = "backend/uploads/test_images_results.jsonl"
    manual_labels_pattern = "backend/uploads/manual_label_*.json"
    xlsx_file = "backend/pfm-web-app/public/comparison_report.xlsx"

    if not os.path.exists(jsonl_file):
        # Fallback to backend/uploads if run from different dir
        jsonl_file = "uploads/test_images_results.jsonl"
        manual_labels_pattern = "uploads/manual_label_*.json"
        xlsx_file = "pfm-web-app/public/comparison_report.xlsx"

    if not os.path.exists(jsonl_file):
        print(f"Error: JSONL file not found at {jsonl_file}")
        return

    # Load automated results
    auto_results = {}
    with open(jsonl_file, "r", encoding="utf-8") as f:
        for line in f:
            if not line.strip():
                continue
            try:
                data = json.loads(line)
                filename = data.get("filename")
                if filename:
                    auto_results[filename] = data
            except Exception as e:
                print(f"Skipping line: {e}")

    # Load manual labels
    manual_files = glob.glob(manual_labels_pattern)
    manual_labels = {}
    for mf in manual_files:
        try:
            with open(mf, "r", encoding="utf-8") as f:
                data = json.load(f)
                filename = data.get("filename")
                if filename:
                    manual_labels[filename] = data
        except Exception as e:
            print(f"Error reading manual label {mf}: {e}")

    print(f"Loaded {len(auto_results)} automated results.")
    print(f"Loaded {len(manual_labels)} manual labels.")

    # Fields to compare in headers
    header_fields = [
        ("noPO", "noPO", "PO Number"),
        ("noSO", "noSO", "SO Number"),
        ("noDO", "noDO", "DO Number"),
        ("tanggal", "tanggal", "Date"),
        ("plat", "platTruk", "Plat Nomor"),
        ("customer", "customerInfo", "Customer Name"),
        ("store", "orderUntuk", "Store Name"),
        ("alamat", "alamat", "Alamat")
    ]

    doc_comparison_rows = []
    item_comparison_rows = []

    # Counters for accuracy calculation
    stats = {
        "PO Number": {"match": 0, "total": 0},
        "SO Number": {"match": 0, "total": 0},
        "DO Number": {"match": 0, "total": 0},
        "Date": {"match": 0, "total": 0},
        "Plat Nomor": {"match": 0, "total": 0},
        "Customer Name": {"match": 0, "total": 0},
        "Store Name": {"match": 0, "total": 0},
        "Alamat": {"match": 0, "total": 0},
        "Item SKU": {"match": 0, "total": 0},
        "Item Banyak": {"match": 0, "total": 0},
        "Item Jumlah": {"match": 0, "total": 0}
    }

    for filename, manual in manual_labels.items():
        auto = auto_results.get(filename)
        if not auto:
            print(f"Warning: Automated result not found for {filename}")
            continue

        auto_meta = auto.get("metadata", {})
        
        # 1. Compare header fields
        for manual_key, auto_key, field_label in header_fields:
            m_val = clean_val(manual.get(manual_key))
            a_val = clean_val(auto_meta.get(auto_key))
            is_match = (m_val == a_val)
            
            doc_comparison_rows.append({
                "Filename": filename,
                "Field": field_label,
                "Automated Value (OCR)": a_val if a_val else "(empty)",
                "Manual Value (Ground Truth)": m_val if m_val else "(empty)",
                "Match": "Match" if is_match else "Mismatch"
            })
            
            stats[field_label]["total"] += 1
            if is_match:
                stats[field_label]["match"] += 1

        # 2. Compare items
        m_items = manual.get("items", [])
        # We also look at auto.get("items") or auto_meta.get("items")
        a_items = auto.get("items", [])
        if not a_items and "items" in auto_meta:
            a_items = auto_meta.get("items", [])

        # Create dictionaries of items indexed by codeBarang (SKU)
        m_items_dict = {clean_val(item.get("kodeBarang")): item for item in m_items if clean_val(item.get("kodeBarang"))}
        a_items_dict = {clean_val(item.get("kodeBarang")): item for item in a_items if clean_val(item.get("kodeBarang"))}

        # Check all unique SKUs across both manual and automated
        all_skus = set(list(m_items_dict.keys()) + list(a_items_dict.keys()))

        for sku in all_skus:
            m_item = m_items_dict.get(sku)
            a_item = a_items_dict.get(sku)

            # Check SKU existence match
            sku_match = (m_item is not None) and (a_item is not None)
            stats["Item SKU"]["total"] += 1
            if sku_match:
                stats["Item SKU"]["match"] += 1

            m_banyak = clean_val(m_item.get("banyak")) if m_item else ""
            a_banyak = clean_val(a_item.get("banyak")) if a_item else ""
            banyak_match = (m_banyak == a_banyak)
            
            stats["Item Banyak"]["total"] += 1
            if banyak_match:
                stats["Item Banyak"]["match"] += 1

            m_jumlah = clean_val(m_item.get("jumlah")) if m_item else ""
            a_jumlah = clean_val(a_item.get("jumlah")) if a_item else ""
            jumlah_match = (m_jumlah == a_jumlah)
            
            stats["Item Jumlah"]["total"] += 1
            if jumlah_match:
                stats["Item Jumlah"]["match"] += 1

            # Log code comparison
            item_comparison_rows.append({
                "Filename": filename,
                "Kode Barang (SKU)": sku,
                "Field": "SKU Existence",
                "Automated Value (OCR)": sku if a_item else "(not found)",
                "Manual Value (Ground Truth)": sku if m_item else "(not found)",
                "Match": "Match" if sku_match else "Mismatch"
            })

            # Log Banyak comparison
            item_comparison_rows.append({
                "Filename": filename,
                "Kode Barang (SKU)": sku,
                "Field": "Banyak (Qty Package)",
                "Automated Value (OCR)": a_banyak if a_banyak else "(empty)",
                "Manual Value (Ground Truth)": m_banyak if m_banyak else "(empty)",
                "Match": "Match" if banyak_match else "Mismatch"
            })

            # Log Jumlah comparison
            item_comparison_rows.append({
                "Filename": filename,
                "Kode Barang (SKU)": sku,
                "Field": "Jumlah (Qty Unit)",
                "Automated Value (OCR)": a_jumlah if a_jumlah else "(empty)",
                "Manual Value (Ground Truth)": m_jumlah if m_jumlah else "(empty)",
                "Match": "Match" if jumlah_match else "Mismatch"
            })

    # Prepare summary data
    summary_rows = []
    total_matches = 0
    total_fields = 0
    for field_label, counts in stats.items():
        match_cnt = counts["match"]
        total_cnt = counts["total"]
        pct = (match_cnt / total_cnt * 100.0) if total_cnt > 0 else 100.0
        summary_rows.append({
            "Field / Area": field_label,
            "Total Checks": total_cnt,
            "Matches": match_cnt,
            "Mismatches": total_cnt - match_cnt,
            "Accuracy (%)": round(pct, 2)
        })
        total_matches += match_cnt
        total_fields += total_cnt

    overall_accuracy = (total_matches / total_fields * 100.0) if total_fields > 0 else 100.0
    summary_rows.append({
        "Field / Area": "OVERALL TOTAL",
        "Total Checks": total_fields,
        "Matches": total_matches,
        "Mismatches": total_fields - total_matches,
        "Accuracy (%)": round(overall_accuracy, 2)
    })

    df_summary = pd.DataFrame(summary_rows)
    df_docs = pd.DataFrame(doc_comparison_rows)
    df_items = pd.DataFrame(item_comparison_rows)

    # Styling setup
    font_family = "Segoe UI"
    header_font = Font(name=font_family, size=11, bold=True, color="FFFFFF")
    regular_font = Font(name=font_family, size=10)
    bold_font = Font(name=font_family, size=10, bold=True)
    
    header_fill = PatternFill(start_color="1F4E78", end_color="1F4E78", fill_type="solid") # Dark Blue
    zebra_fill = PatternFill(start_color="F2F5F8", end_color="F2F5F8", fill_type="solid") # Zebra light blue-gray
    match_fill = PatternFill(start_color="E2EFDA", end_color="E2EFDA", fill_type="solid") # Light green
    mismatch_fill = PatternFill(start_color="FCE4D6", end_color="FCE4D6", fill_type="solid") # Light orange
    
    center_align = Alignment(horizontal="center", vertical="center")
    left_align = Alignment(horizontal="left", vertical="center")
    right_align = Alignment(horizontal="right", vertical="center")
    
    thin_side = Side(border_style="thin", color="D9D9D9")
    cell_border = Border(left=thin_side, right=thin_side, top=thin_side, bottom=thin_side)

    # Save to Excel
    os.makedirs(os.path.dirname(xlsx_file), exist_ok=True)
    with pd.ExcelWriter(xlsx_file, engine='openpyxl') as writer:
        df_summary.to_excel(writer, sheet_name='Summary Accuracy', index=False)
        df_docs.to_excel(writer, sheet_name='Header Field Comparison', index=False)
        df_items.to_excel(writer, sheet_name='Item SKU Comparison', index=False)

        # Style worksheets
        for sheet_name in ['Summary Accuracy', 'Header Field Comparison', 'Item SKU Comparison']:
            ws = writer.sheets[sheet_name]
            max_row = ws.max_row
            max_col = ws.max_column

            # Header row styling
            for col in range(1, max_col + 1):
                cell = ws.cell(row=1, column=col)
                cell.font = header_font
                cell.fill = header_fill
                cell.alignment = center_align

            # Data rows styling
            for row in range(2, max_row + 1):
                is_zebra = (row % 2 == 0)
                
                # Check for Match/Mismatch to apply colors on sheets 2 & 3
                match_val = None
                if sheet_name in ['Header Field Comparison', 'Item SKU Comparison']:
                    # Match column is the last column
                    match_cell = ws.cell(row=row, column=max_col)
                    match_val = match_cell.value

                for col in range(1, max_col + 1):
                    cell = ws.cell(row=row, column=col)
                    cell.font = regular_font
                    cell.border = cell_border
                    
                    # Apply alignments based on column
                    if sheet_name == 'Summary Accuracy':
                        if col == 1:
                            cell.alignment = left_align
                        else:
                            cell.alignment = right_align
                            
                        # Highlight overall total row
                        if row == max_row:
                            cell.font = bold_font
                            cell.fill = match_fill if overall_accuracy > 80 else mismatch_fill
                    else:
                        # For detail sheets
                        if col in [1, 3, 4]:
                            cell.alignment = left_align
                        else:
                            cell.alignment = center_align

                        # Color match / mismatch
                        if match_val == "Match":
                            cell.fill = match_fill
                        elif match_val == "Mismatch":
                            cell.fill = mismatch_fill
                        elif is_zebra:
                            cell.fill = zebra_fill

            # Auto-fit columns
            for col in ws.columns:
                max_len = max(len(str(cell.value or '')) for cell in col)
                col_letter = get_column_letter(col[0].column)
                ws.column_dimensions[col_letter].width = max(max_len + 4, 12)

    print(f"Comparison report generated at {xlsx_file}")

if __name__ == "__main__":
    main()