#!/usr/bin/env python3
"""
Parse Petty.xlsx file to understand structure, formulas, and calculations
"""
import sys
import json
try:
    import openpyxl
    from openpyxl import load_workbook
except ImportError:
    print("Installing openpyxl...")
    import subprocess
    subprocess.check_call([sys.executable, "-m", "pip", "install", "openpyxl"])
    import openpyxl
    from openpyxl import load_workbook

def get_cell_style(cell):
    """Get cell style information"""
    style = {
        'fill': str(cell.fill.start_color.rgb) if cell.fill.start_color and cell.fill.start_color.rgb else None,
        'font_color': str(cell.font.color.rgb) if cell.font.color and cell.font.color.rgb else None,
        'font_bold': cell.font.bold,
        'font_size': cell.font.size,
        'font_name': cell.font.name,
        'alignment_h': str(cell.alignment.horizontal) if cell.alignment.horizontal else None,
        'alignment_v': str(cell.alignment.vertical) if cell.alignment.vertical else None,
        'number_format': cell.number_format
    }
    return style

def parse_sheet(ws, sheet_name, max_rows=500, max_cols=100):
    """Parse a sheet and return structured data"""
    data = {
        'sheet_name': sheet_name,
        'max_row': ws.max_row,
        'max_column': ws.max_column,
        'merged_cells': [],
        'cells': {},
        'formulas': {},
        'data_rows': [],
        'headers': []
    }
    
    # Get merged cells
    for merged in ws.merged_cells.ranges:
        data['merged_cells'].append(str(merged))
    
    # Read cells with data
    for row in range(1, min(ws.max_row + 1, max_rows)):
        row_data = []
        for col in range(1, min(ws.max_column + 1, max_cols)):
            cell = ws.cell(row, col)
            cell_ref = openpyxl.utils.get_column_letter(col) + str(row)
            value = cell.value
            
            if value is not None:
                cell_info = {
                    'value': str(value) if value else '',
                    'data_type': str(type(value).__name__),
                    'style': get_cell_style(cell),
                    'row': row,
                    'col': col,
                    'col_letter': openpyxl.utils.get_column_letter(col)
                }
                
                # Check for formulas
                if cell.data_type == 'f':
                    cell_info['formula'] = cell.value
                    data['formulas'][cell_ref] = {
                        'formula': cell.value,
                        'row': row,
                        'col': col,
                        'col_letter': openpyxl.utils.get_column_letter(col)
                    }
                
                data['cells'][cell_ref] = cell_info
                row_data.append(cell_info)
        
        if row_data:
            data['data_rows'].append({
                'row': row,
                'cells': row_data
            })
    
    return data

try:
    wb = load_workbook('Petty.xlsx', data_only=False)  # data_only=False to get formulas
    
    print("=" * 80)
    print("PETTY CASH EXCEL FILE STRUCTURE & FORMULA ANALYSIS")
    print("=" * 80)
    
    # List all sheets
    print(f"\nTotal Sheets: {len(wb.sheetnames)}")
    print(f"Sheet Names: {wb.sheetnames}")
    
    all_sheets_data = {}
    all_formulas = {}
    
    for sheet_name in wb.sheetnames:
        ws = wb[sheet_name]
        print(f"\n{'='*80}")
        print(f"SHEET: {sheet_name}")
        print(f"Max Row: {ws.max_row}, Max Column: {ws.max_column}")
        print(f"{'='*80}")
        
        # Parse sheet
        sheet_data = parse_sheet(ws, sheet_name, max_rows=500, max_cols=100)
        all_sheets_data[sheet_name] = sheet_data
        all_formulas[sheet_name] = sheet_data['formulas']
        
        # Print formulas found
        if sheet_data['formulas']:
            print(f"\nFORMULAS FOUND ({len(sheet_data['formulas'])}):")
            print("-" * 80)
            for cell_ref, formula_info in sorted(sheet_data['formulas'].items(), key=lambda x: (x[1]['row'], x[1]['col'])):
                print(f"  {cell_ref} (Row {formula_info['row']}, Col {formula_info['col_letter']}):")
                print(f"    Formula: {formula_info['formula']}")
                
                # Try to get calculated value
                try:
                    calc_cell = ws.cell(formula_info['row'], formula_info['col'])
                    if calc_cell.value:
                        print(f"    Calculated Value: {calc_cell.value}")
                except:
                    pass
                print()
        
        # Print first 30 rows summary
        print(f"\nFirst 30 rows of data:")
        print("-" * 80)
        for row_info in sheet_data['data_rows'][:30]:
            row_num = row_info['row']
            cells_preview = []
            for cell_info in row_info['cells'][:15]:  # First 15 cells
                value = str(cell_info['value'])[:40]  # Truncate long values
                if cell_info.get('formula'):
                    value = f"[FORMULA: {cell_info['formula'][:30]}...]"
                cells_preview.append(f"{cell_info['col_letter']}{row_num}:{value}")
            if cells_preview:
                print(f"  Row {row_num}: {' | '.join(cells_preview)}")
        
        # Print merged cells
        if sheet_data['merged_cells']:
            print(f"\nMerged Cells ({len(sheet_data['merged_cells'])}):")
            for merged in sheet_data['merged_cells'][:30]:  # First 30
                print(f"  {merged}")
        
        # Sample cell styles with colors
        print(f"\nSample styled cells (with colors):")
        style_count = 0
        for row in range(1, min(50, ws.max_row + 1)):
            for col in range(1, min(30, ws.max_column + 1)):
                cell = ws.cell(row, col)
                fill_color = cell.fill.start_color.rgb if cell.fill.start_color and cell.fill.start_color.rgb else None
                if fill_color and fill_color != '00000000':  # Not default fill
                    cell_ref = openpyxl.utils.get_column_letter(col) + str(row)
                    font_name = cell.font.name
                    font_bold = cell.font.bold
                    font_size = cell.font.size
                    value_preview = str(cell.value)[:20] if cell.value else ''
                    print(f"  {cell_ref}: Fill={fill_color}, Font={font_name}, Bold={font_bold}, Size={font_size}, Value={value_preview}")
                    style_count += 1
                    if style_count >= 20:
                        break
            if style_count >= 20:
                break
    
    wb.close()
    
    # Save detailed analysis to JSON
    output_file = 'petty_excel_analysis.json'
    with open(output_file, 'w', encoding='utf-8') as f:
        json.dump({
            'sheets': all_sheets_data,
            'all_formulas': all_formulas
        }, f, indent=2, ensure_ascii=False, default=str)
    
    print("\n" + "=" * 80)
    print(f"ANALYSIS COMPLETE - Details saved to {output_file}")
    print("=" * 80)
    
    # Print summary of all formulas
    print("\n" + "=" * 80)
    print("FORMULA SUMMARY BY SHEET")
    print("=" * 80)
    for sheet_name, formulas in all_formulas.items():
        if formulas:
            print(f"\n{sheet_name} - {len(formulas)} formulas:")
            for cell_ref, formula_info in sorted(formulas.items(), key=lambda x: (x[1]['row'], x[1]['col'])):
                print(f"  {cell_ref}: {formula_info['formula']}")
    
except Exception as e:
    print(f"Error: {e}")
    import traceback
    traceback.print_exc()
