"""Create summary statistics from file analysis."""
import json

with open("/home/hieutt50/projects/DSOL/customer_files_analysis.json", 'r', encoding='utf-8') as f:
    results = json.load(f)

# Overall stats
total = len(results)
success = [r for r in results if r["status"] == "success"]
errors = [r for r in results if r["status"] == "error"]

print("=" * 80)
print("CUSTOMER FILES ANALYSIS SUMMARY")
print("=" * 80)
print(f"\nTotal files found: {total}")
print(f"Successfully analyzed: {len(success)} ({len(success)/total*100:.1f}%)")
print(f"Errors: {len(errors)} ({len(errors)/total*100:.1f}%)")

# File format breakdown
xls = [r for r in results if r["extension"] == ".xls"]
xlsx = [r for r in results if r["extension"] == ".xlsx"]
xlsm = [r for r in results if r["extension"] == ".xlsm"]

print(f"\n--- File Formats ---")
print(f".xls (old format):  {len(xls):2d} ({len(xls)/total*100:.1f}%)")
print(f".xlsx (modern):     {len(xlsx):2d} ({len(xlsx)/total*100:.1f}%)")
print(f".xlsm (with macros):{len(xlsm):2d} ({len(xlsm)/total*100:.1f}%)")

# Error analysis
print(f"\n--- Error Analysis ---")
xls_errors = [r for r in errors if r["extension"] == ".xls"]
print(f"Old .xls format errors: {len(xls_errors)} / {len(xls)} ({len(xls_errors)/len(xls)*100:.0f}%)")
print(f"Other errors: {len(errors) - len(xls_errors)}")

if len(errors) > len(xls_errors):
    print("\nNon-.xls errors:")
    for r in errors:
        if r["extension"] != ".xls":
            print(f"  - {r['file_name']}: {r['error'][:80]}")

# Success file characteristics
print(f"\n--- Successfully Analyzed Files ---")

# Size distribution
small = [r for r in success if r["file_size_mb"] < 0.1]
medium = [r for r in success if 0.1 <= r["file_size_mb"] < 1.0]
large = [r for r in success if r["file_size_mb"] >= 1.0]

print(f"Small (<0.1MB):  {len(small):2d} ({len(small)/len(success)*100:.1f}%)")
print(f"Medium (0.1-1MB):{len(medium):2d} ({len(medium)/len(success)*100:.1f}%)")
print(f"Large (>=1MB):   {len(large):2d} ({len(large)/len(success)*100:.1f}%)")

# Sheet count
single_sheet = [r for r in success if r["total_sheets"] == 1]
few_sheets = [r for r in success if 2 <= r["total_sheets"] <= 5]
many_sheets = [r for r in success if r["total_sheets"] > 5]

print(f"\nSheet count:")
print(f"Single sheet:   {len(single_sheet):2d} ({len(single_sheet)/len(success)*100:.1f}%)")
print(f"2-5 sheets:     {len(few_sheets):2d} ({len(few_sheets)/len(success)*100:.1f}%)")
print(f"6+ sheets:      {len(many_sheets):2d} ({len(many_sheets)/len(success)*100:.1f}%)")

# Special features
inflated = [r for r in success if r["has_inflated_sheets"]]
merged = [r for r in success if r["has_merged_cells"]]
comments = [r for r in success if r["has_comments"]]
images = [r for r in success if r["has_images"]]

print(f"\n--- Special Features Detected ---")
print(f"Inflated sheets (>5000 rows): {len(inflated):2d} ({len(inflated)/len(success)*100:.1f}%)")
print(f"Merged cells:                 {len(merged):2d} ({len(merged)/len(success)*100:.1f}%)")
print(f"Comments:                     {len(comments):2d} ({len(comments)/len(success)*100:.1f}%)")
print(f"Images:                       {len(images):2d} ({len(images)/len(success)*100:.1f}%)")

if inflated:
    print(f"\nInflated files (need special handling):")
    for r in inflated:
        inflated_sheets = ', '.join(r['inflated_sheet_names'])
        print(f"  - {r['file_name']}")
        print(f"    Sheets: {inflated_sheets}")
        print(f"    Max rows: {max(s['max_row'] for s in r['sheets'])}")

# Load time analysis
load_times = [r["load_time_seconds"] for r in success]
avg_load = sum(load_times) / len(load_times)
slow_files = [r for r in success if r["load_time_seconds"] > 5]

print(f"\n--- Performance Metrics ---")
print(f"Average load time: {avg_load:.2f}s")
print(f"Slow files (>5s):  {len(slow_files)} ({len(slow_files)/len(success)*100:.1f}%)")

if slow_files:
    print(f"\nSlowest files:")
    for r in sorted(slow_files, key=lambda x: x["load_time_seconds"], reverse=True)[:5]:
        print(f"  - {r['file_name']}: {r['load_time_seconds']}s ({r['file_size_mb']}MB, {r['total_sheets']} sheets)")

# Recommendations
print(f"\n" + "=" * 80)
print("KEY FINDINGS & RECOMMENDATIONS")
print("=" * 80)

print(f"\n1. OLD FILE FORMAT (.xls) - P0 CRITICAL")
print(f"   - {len(xls)} files ({len(xls)/total*100:.0f}%) are old .xls format")
print(f"   - ALL {len(xls_errors)} fail to load with openpyxl")
print(f"   - ACTION: Add .xls support using xlrd or convert to .xlsx")

print(f"\n2. INFLATED SHEETS - P1 IMPORTANT")
print(f"   - {len(inflated)} files have sheets with >5000 rows")
print(f"   - Can cause slow processing and timeouts")
print(f"   - ACTION: V2 pipeline already skips border detection for inflated sheets")

print(f"\n3. MERGED CELLS - P2 NICE-TO-HAVE")
print(f"   - {len(merged)} files ({len(merged)/len(success)*100:.0f}%) use merged cells")
print(f"   - Very common pattern in customer files")
print(f"   - ACTION: Ensure proper header extraction from merged cells")

print(f"\n4. PERFORMANCE - P1 IMPORTANT")
print(f"   - {len(large)} large files (>=1MB) may be slow")
print(f"   - {len(slow_files)} files take >5s just to load")
print(f"   - ACTION: Consider timeout limits, async processing, progress tracking")

print(f"\n5. IMAGES & COMMENTS - P2 NICE-TO-HAVE")
print(f"   - {len(images)} files have images")
print(f"   - {len(comments)} files have comments")
print(f"   - ACTION: V2 pipeline already handles these")

success_rate = len(success) / total * 100
print(f"\n" + "=" * 80)
print(f"CURRENT SUCCESS RATE: {success_rate:.1f}% ({len(success)}/{total} files)")
print(f"TARGET SUCCESS RATE:  85-90% (acceptable trade-off)")
print(f"GAP: Need to handle .xls files to reach target")
print("=" * 80)