#!/usr/bin/env python3
"""
Parse Payroll.xlsx file to understand structure and design
"""
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.index) if cell.fill.start_color else None,
        'font_color': str(cell.font.color.index) if cell.font.color 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,
        'border': bool(cell.border.left.style if cell.border and cell.border.left else False)
    }
    return style

def parse_sheet(ws, sheet_name, max_rows=200, max_cols=50):
    """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': []
    }
    
    # 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)
                }
                
                # Check for formulas
                if cell.data_type == 'f':
                    cell_info['formula'] = cell.value
                    data['formulas'][cell_ref] = cell.value
                
                data['cells'][cell_ref] = cell_info
                row_data.append({
                    'col': col,
                    'col_letter': openpyxl.utils.get_column_letter(col),
                    'value': str(value) if value else '',
                    'cell_ref': cell_ref
                })
        
        if row_data:
            data['data_rows'].append({
                'row': row,
                'cells': row_data
            })
    
    return data

try:
    wb = load_workbook('Payroll.xlsx', data_only=False)  # data_only=False to get formulas
    
    print("=" * 80)
    print("PAYROLL EXCEL FILE STRUCTURE ANALYSIS")
    print("=" * 80)
    
    # List all sheets
    print(f"\nTotal Sheets: {len(wb.sheetnames)}")
    print(f"Sheet Names: {wb.sheetnames}")
    
    all_sheets_data = {}
    
    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=300, max_cols=100)
        all_sheets_data[sheet_name] = sheet_data
        
        # Print first 20 rows summary
        print(f"\nFirst 20 rows of data:")
        for row_info in sheet_data['data_rows'][:20]:
            row_num = row_info['row']
            cells_preview = []
            for cell_info in row_info['cells'][:10]:  # First 10 cells
                value = cell_info['value'][:30]  # Truncate long values
                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'][:20]:  # First 20
                print(f"  {merged}")
        
        # Print formulas found
        if sheet_data['formulas']:
            print(f"\nFormulas found ({len(sheet_data['formulas'])}):")
            for cell_ref, formula in list(sheet_data['formulas'].items())[:20]:  # First 20
                print(f"  {cell_ref}: {formula}")
        
        # Sample cell styles
        print(f"\nSample styled cells:")
        style_count = 0
        for row in range(1, min(20, ws.max_row + 1)):
            for col in range(1, min(20, ws.max_column + 1)):
                cell = ws.cell(row, col)
                if cell.fill.start_color.index != '00000000':  # Not default fill
                    cell_ref = openpyxl.utils.get_column_letter(col) + str(row)
                    print(f"  {cell_ref}: Fill={cell.fill.start_color.index}, Font={cell.font.name}, Bold={cell.font.bold}, Size={cell.font.size}")
                    style_count += 1
                    if style_count >= 10:
                        break
            if style_count >= 10:
                break
    
    wb.close()
    
    # Save detailed analysis to JSON
    output_file = 'payroll_excel_analysis.json'
    with open(output_file, 'w', encoding='utf-8') as f:
        json.dump(all_sheets_data, f, indent=2, ensure_ascii=False, default=str)
    
    print("\n" + "=" * 80)
    print(f"ANALYSIS COMPLETE - Details saved to {output_file}")
    print("=" * 80)
    
except Exception as e:
    print(f"Error: {e}")
    import traceback
    traceback.print_exc()
