"""
Analyze all customer Excel files to identify structure, issues, and patterns.
"""
import os
from pathlib import Path
import json
from openpyxl import load_workbook
from openpyxl.utils.exceptions import InvalidFileException
import time

def analyze_excel_file(file_path: str) -> dict:
    """Analyze a single Excel file for structure and potential issues."""
    result = {
        "file_path": file_path,
        "file_name": os.path.basename(file_path),
        "file_size_mb": round(os.path.getsize(file_path) / (1024 * 1024), 2),
        "extension": Path(file_path).suffix,
        "status": "unknown",
        "sheets": [],
        "total_sheets": 0,
        "has_inflated_sheets": False,
        "inflated_sheet_names": [],
        "has_merged_cells": False,
        "has_comments": False,
        "has_images": False,
        "encoding_issue": False,
        "error": None,
        "load_time_seconds": 0
    }

    # Check for encoding issues in filename
    try:
        file_path.encode('utf-8').decode('utf-8')
    except:
        result["encoding_issue"] = True

    start_time = time.time()

    try:
        # Load workbook (data_only=False to get formulas)
        wb = load_workbook(file_path, data_only=True, keep_vba=False)
        result["load_time_seconds"] = round(time.time() - start_time, 2)
        result["total_sheets"] = len(wb.sheetnames)

        for sheet_name in wb.sheetnames:
            sheet = wb[sheet_name]
            sheet_info = {
                "name": sheet_name,
                "max_row": sheet.max_row,
                "max_column": sheet.max_column,
                "is_inflated": sheet.max_row > 5000,
                "has_merged_cells": len(sheet.merged_cells.ranges) > 0,
                "merged_cells_count": len(sheet.merged_cells.ranges)
            }

            # Check for comments (openpyxl stores them as sheet.comments)
            if hasattr(sheet, '_comments') and sheet._comments:
                sheet_info["has_comments"] = True
                sheet_info["comments_count"] = len(sheet._comments)
                result["has_comments"] = True
            else:
                sheet_info["has_comments"] = False
                sheet_info["comments_count"] = 0

            # Check for images/drawings
            if hasattr(sheet, '_images') and sheet._images:
                sheet_info["has_images"] = True
                sheet_info["images_count"] = len(sheet._images)
                result["has_images"] = True
            else:
                sheet_info["has_images"] = False
                sheet_info["images_count"] = 0

            if sheet_info["is_inflated"]:
                result["has_inflated_sheets"] = True
                result["inflated_sheet_names"].append(sheet_name)

            if sheet_info["has_merged_cells"]:
                result["has_merged_cells"] = True

            result["sheets"].append(sheet_info)

        wb.close()
        result["status"] = "success"

    except InvalidFileException as e:
        result["status"] = "error"
        result["error"] = f"Invalid file format: {str(e)}"
        result["load_time_seconds"] = round(time.time() - start_time, 2)
    except Exception as e:
        result["status"] = "error"
        result["error"] = str(e)
        result["load_time_seconds"] = round(time.time() - start_time, 2)

    return result

def main():
    # Find all Excel files
    base_dir = "/home/hieutt50/projects/DSOL/AIHackathonFolder/Phase2/CustomerDocument"
    excel_files = []

    for root, dirs, files in os.walk(base_dir):
        for file in files:
            if file.endswith(('.xlsx', '.xls', '.xlsm')):
                excel_files.append(os.path.join(root, file))

    print(f"Found {len(excel_files)} Excel files")

    results = []
    for i, file_path in enumerate(excel_files, 1):
        print(f"[{i}/{len(excel_files)}] Analyzing: {os.path.basename(file_path)}")
        result = analyze_excel_file(file_path)
        results.append(result)

        # Print summary
        if result["status"] == "success":
            print(f"  ✓ {result['total_sheets']} sheets, {result['file_size_mb']}MB, "
                  f"inflated: {result['has_inflated_sheets']}, load time: {result['load_time_seconds']}s")
        else:
            print(f"  ✗ Error: {result['error']}")

    # Save results
    output_file = "/home/hieutt50/projects/DSOL/customer_files_analysis.json"
    with open(output_file, 'w', encoding='utf-8') as f:
        json.dump(results, f, indent=2, ensure_ascii=False)

    print(f"\nResults saved to: {output_file}")

    # Print summary statistics
    total = len(results)
    success = sum(1 for r in results if r["status"] == "success")
    errors = sum(1 for r in results if r["status"] == "error")
    inflated = sum(1 for r in results if r["has_inflated_sheets"])
    merged = sum(1 for r in results if r["has_merged_cells"])
    comments = sum(1 for r in results if r["has_comments"])
    images = sum(1 for r in results if r["has_images"])
    xls_files = sum(1 for r in results if r["extension"] == ".xls")
    xlsx_files = sum(1 for r in results if r["extension"] == ".xlsx")
    xlsm_files = sum(1 for r in results if r["extension"] == ".xlsm")

    print("\n" + "="*60)
    print("SUMMARY STATISTICS")
    print("="*60)
    print(f"Total files: {total}")
    print(f"Successfully analyzed: {success} ({success/total*100:.1f}%)")
    print(f"Errors: {errors} ({errors/total*100:.1f}%)")
    print(f"\nFile formats:")
    print(f"  .xlsx: {xlsx_files}")
    print(f"  .xls:  {xls_files}")
    print(f"  .xlsm: {xlsm_files}")
    print(f"\nFeatures detected:")
    print(f"  Inflated sheets (>5000 rows): {inflated} ({inflated/total*100:.1f}%)")
    print(f"  Merged cells: {merged} ({merged/total*100:.1f}%)")
    print(f"  Comments: {comments} ({comments/total*100:.1f}%)")
    print(f"  Images: {images} ({images/total*100:.1f}%)")

if __name__ == "__main__":
    main()