#!/usr/bin/env python3
"""
Script to check for conditional formatting and data validation in Excel file.
"""

import openpyxl
from openpyxl.styles import PatternFill

def check_formatting(input_file):
    """Check for conditional formatting and data validation."""
    
    print(f"Loading workbook: {input_file}")
    try:
        wb = openpyxl.load_workbook(input_file, data_only=False)
    except Exception as e:
        print(f"Error loading workbook: {e}")
        return
    
    print(f"Found {len(wb.sheetnames)} sheets: {wb.sheetnames}\n")
    
    # Check each sheet
    for sheet_name in wb.sheetnames:
        ws = wb[sheet_name]
        print(f"Sheet: {sheet_name}")
        print("-" * 60)
        
        # Check conditional formatting
        if ws.conditional_formatting:
            print(f"  Conditional Formatting: {len(ws.conditional_formatting)} rules found")
            for cf_range, rules in ws.conditional_formatting.items():
                print(f"    Range: {cf_range}")
                for rule in rules:
                    print(f"      Rule: {rule.type}, Formula: {getattr(rule, 'formula', 'N/A')}")
                    if hasattr(rule, 'fill') and rule.fill:
                        print(f"        Fill: {rule.fill}")
                    if hasattr(rule, 'font') and rule.font:
                        print(f"        Font: {rule.font}")
        else:
            print("  Conditional Formatting: None found")
        
        # Check data validation
        if hasattr(ws, 'data_validations') and ws.data_validations:
            count = len(list(ws.data_validations)) if hasattr(ws.data_validations, '__iter__') else 0
            if count > 0:
                print(f"  Data Validation: {count} rules found")
                for dv in ws.data_validations:
                    print(f"    Range: {dv.ranges}, Type: {dv.type}, Formula1: {getattr(dv, 'formula1', 'N/A')}")
            else:
                print("  Data Validation: None found")
        else:
            print("  Data Validation: None found")
        
        # Check for cells with green background fill
        green_cells = []
        for row in ws.iter_rows():
            for cell in row:
                if cell.fill and cell.fill.start_color:
                    # Check if it's green
                    color = cell.fill.start_color
                    if hasattr(color, 'rgb') and color.rgb:
                        rgb = color.rgb
                        # Green colors typically have high G component
                        if isinstance(rgb, str) and len(rgb) >= 6:
                            try:
                                r = int(rgb[2:4], 16)
                                g = int(rgb[4:6], 16)
                                b = int(rgb[6:8], 16) if len(rgb) >= 8 else 0
                                # Check if it's a greenish color (G > R and G > B)
                                if g > r and g > b and g > 150:
                                    green_cells.append((cell.coordinate, cell.value, rgb))
                            except:
                                pass
                    elif hasattr(color, 'theme') or hasattr(color, 'index'):
                        # Theme colors or indexed colors
                        if 'green' in str(color).lower() or '4' in str(color):  # 4 is often green in Excel
                            green_cells.append((cell.coordinate, cell.value, str(color)))
        
        if green_cells:
            print(f"  Cells with green fill: {len(green_cells)} found")
            for coord, value, color in green_cells[:10]:  # Show first 10
                print(f"    {coord}: {value} (color: {color})")
            if len(green_cells) > 10:
                print(f"    ... and {len(green_cells) - 10} more")
        else:
            print("  Cells with green fill: None found")
        
        # Check for formulas that might trigger Google Sheets auto-formatting
        formula_cells = []
        for row in ws.iter_rows():
            for cell in row:
                if cell.data_type == 'f' and cell.value:
                    formula = str(cell.value)
                    # Check for formulas that Google Sheets might auto-format
                    if any(func in formula.upper() for func in ['IF', 'ISBLANK', 'ISTEXT', 'ISNUMBER']):
                        formula_cells.append((cell.coordinate, formula[:50]))
        
        if formula_cells:
            print(f"  Conditional formulas: {len(formula_cells)} found")
            for coord, formula in formula_cells[:5]:  # Show first 5
                print(f"    {coord}: {formula}")
            if len(formula_cells) > 5:
                print(f"    ... and {len(formula_cells) - 5} more")
        
        print()

if __name__ == "__main__":
    input_file = "Finance Board Report Dec 2025 p.xlsx"
    check_formatting(input_file)