import pandas as pd
import gradio as gr

# Load account mapping from a mapping file
def load_mapping(mapping_file):
    try:
        mapping_df = pd.read_excel(mapping_file)
        # Ensure columns exist
        if 'Account Name' not in mapping_df or 'Category' not in mapping_df:
            raise ValueError("Mapping file must contain 'Account Name' and 'Category' columns.")
        
        return dict(zip(mapping_df['Account Name'].str.lower(), mapping_df['Category']))
    except Exception as e:
        raise Exception(f"Error loading mapping file: {e}")

# Generate financial statements based on the trial balance and mapping
def generate_financial_statements(file, mapping):
    try:
        df = pd.read_excel(file, usecols=['Account', 'Balance'])
        
        # Ensure required columns exist
        if 'Account' not in df or 'Balance' not in df:
            raise ValueError("Trial balance file must contain 'Account' and 'Balance' columns.")
        
        df['Account'] = df['Account'].astype(str).fillna('')
        df['Balance'] = df['Balance'] / 1000  # Convert values to thousands (000)

        # Apply account mapping to categorize accounts
        df['Category'] = df['Account'].str.lower().map(mapping).fillna('Other')

        # Income Statement calculation based on categories
        revenue = df[df['Category'] == 'Revenue']['Balance'].sum()
        cost_of_sales = df[df['Category'] == 'Cost of Sales']['Balance'].sum()
        gross_profit = revenue - cost_of_sales
        
        operating_expenses = df[df['Category'] == 'Operating Expenses']['Balance'].sum()
        operating_profit = gross_profit - operating_expenses

        finance_costs = df[df['Category'] == 'Finance Costs']['Balance'].sum()
        profit_before_tax = operating_profit - finance_costs

        tax_expense = df[df['Category'] == 'Tax Expense']['Balance'].sum()
        profit_for_the_year = profit_before_tax - tax_expense

        # Generate the income statement with formatting
        income_statement = f"""
        INCOME STATEMENT (in 000s)

        Revenue:                        {revenue:,.2f}
        Cost of Sales:                 ({cost_of_sales:,.2f})
        ------------------------------------------------
        Gross Profit:                   {gross_profit:,.2f}

        Operating Expenses:            ({operating_expenses:,.2f})
        ------------------------------------------------
        Operating Profit:                {operating_profit:,.2f}

        Finance Costs:                 ({finance_costs:,.2f})
        ------------------------------------------------
        Profit Before Tax:               {profit_before_tax:,.2f}

        Tax Expense:                   ({tax_expense:,.2f})
        ------------------------------------------------
        Profit for the Year:             {profit_for_the_year:,.2f}
        """

        # Create Balance Sheet
        balance_sheet = df[df['Category'].isin(['Assets', 'Liabilities', 'Equity'])].copy()
        balance_sheet_summary = balance_sheet.groupby('Category').agg({'Balance': 'sum'}).reset_index()

        # Handle empty balance sheet case
        if balance_sheet_summary.empty:
            balance_sheet_summary_str = "No assets, liabilities, or equity accounts found."
        else:
            balance_sheet_summary_str = balance_sheet_summary.to_string(index=False)

        return income_statement, balance_sheet_summary_str

    except Exception as e:
        return f"Error processing the file: {e}", None

# Gradio Blocks interface
with gr.Blocks() as demo:
    gr.Markdown("# Financial Statement Generator")
    gr.Markdown("Upload a trial balance Excel file and a mapping file to generate an Income Statement and a Balance Sheet.")

    file_input = gr.File(label="Upload Trial Balance Excel File")
    mapping_input = gr.File(label="Upload Mapping File")
    income_output = gr.Textbox(label="Income Statement", lines=20)
    balance_output = gr.Textbox(label="Balance Sheet", lines=10)

    def process_file(trial_balance_file, mapping_file):
        # Debugging print statements
        print("Trial Balance File:", trial_balance_file)
        print("Mapping File:", mapping_file)

        # Check if files are uploaded
        if trial_balance_file is None:
            return "Error: Please upload a trial balance Excel file.", None
        if mapping_file is None:
            return "Error: Please upload a mapping file.", None
        
        try:
            # Read the content of the files
            mapping = load_mapping(mapping_file)
            income_statement, balance_sheet = generate_financial_statements(trial_balance_file, mapping)
            return income_statement, balance_sheet
        except Exception as e:
            return f"Error: {e}", None

    # Trigger processing when files are uploaded
    file_input.change(fn=process_file, inputs=[file_input, mapping_input], outputs=[income_output, balance_output])

# Launch the Gradio app
if __name__ == "__main__":
    demo.launch()  # Set share=True only for public access if needed