import io
import os
import tempfile
from numbers_parser import Document
from openpyxl import Workbook
from openpyxl.styles import PatternFill, Font, Alignment
import gradio as gr
import pandas as pd
from pathlib import Path

def get_excel_color(numbers_color):
    """Convert Numbers color to Excel color format"""
    if not numbers_color:
        return None
    # Convert RGB values to hex color
    r = int(numbers_color.red * 255)
    g = int(numbers_color.green * 255)
    b = int(numbers_color.blue * 255)
    return f"{r:02x}{g:02x}{b:02x}"

def apply_cell_formatting(cell, cell_data):
    """Apply formatting from Numbers cell to Excel cell"""
    if hasattr(cell_data, 'background_color') and cell_data.background_color:
        color = get_excel_color(cell_data.background_color)
        if color:
            cell.fill = PatternFill(start_color=color, end_color=color, fill_type="solid")
    
    if hasattr(cell_data, 'font_bold'):
        cell.font = Font(bold=cell_data.font_bold)

def numbers_to_xlsx(numbers_file):
    """
    Converts a Numbers file to XLSX format preserving colors and formatting.

    Args:
        numbers_file: The uploaded Numbers file object from Gradio.

    Returns:
        str: Path to the converted xlsx file, or None if conversion fails.
    """
    if not numbers_file:
        return None
    
    try:
        output_dir = "outputs"
        os.makedirs(output_dir, exist_ok=True)
        output_path = os.path.join(output_dir, "converted.xlsx")
        
        # Read the Numbers file
        doc = Document(numbers_file.name)
        
        # Get all sheets and validate
        sheets = doc.sheets
        if not sheets:
            print("No sheets found in the document")
            return None
            
        # Create a new workbook
        wb = Workbook()
        wb.remove(wb.active)  # Remove default sheet
        
        for sheet_index, sheet in enumerate(sheets):
            if not sheet.tables:
                continue
                
            # Create new sheet
            ws = wb.create_sheet(title=f"Sheet{sheet_index + 1}")
            table = sheet.tables[0]
            
            # Get all rows with their formatting
            rows = list(table.rows())
            if not rows:
                continue
            
            # Write data and apply formatting
            for row_idx, row in enumerate(rows, 1):
                for col_idx, cell_data in enumerate(row, 1):
                    # Write cell value
                    cell = ws.cell(row=row_idx, column=col_idx)
                    cell.value = cell_data.value if hasattr(cell_data, 'value') else cell_data
                    
                    # Apply formatting if available
                    if hasattr(cell_data, 'background_color') or hasattr(cell_data, 'font_bold'):
                        apply_cell_formatting(cell, cell_data)
            
            # Adjust column widths
            for column in ws.columns:
                max_length = 0
                column_letter = column[0].column_letter
                for cell in column:
                    try:
                        if len(str(cell.value)) > max_length:
                            max_length = len(str(cell.value))
                    except:
                        pass
                adjusted_width = (max_length + 2)
                ws.column_dimensions[column_letter].width = adjusted_width
            
            # Freeze the header row
            ws.freeze_panes = 'A2'
        
        # Save the workbook
        wb.save(output_path)
        
        # Verify the file was created and has content
        if os.path.exists(output_path) and os.path.getsize(output_path) > 0:
            return output_path
        else:
            print("Output file is empty or was not created")
            return None
            
    except Exception as e:
        print(f"Error converting file: {str(e)}")
        return None

# Define the Gradio interface with correct file handling
interface = gr.Interface(
    fn=numbers_to_xlsx,
    inputs=gr.File(label="Numbers File", file_types=[".numbers"]),
    outputs=gr.File(label="XLSX file", file_types=[".xlsx"]),
    title="Numbers to XLSX Converter",
    description="Convert your Numbers files to Excel format easily and download the result.",
    examples=None,
    cache_examples=False
)

# Launch the Gradio app
if __name__ == "__main__":
    interface.launch()