import openai
import openpyxl
import PyPDF2
import pandas as pd
from PIL import Image
import pytesseract
import io
import os
import gradio as gr

openai.api_key = os.environ.get("Open_ai_key")

def extract_transactions(file):
    """Extract raw text content from various supported file formats."""
    filename = file.name.lower()

    try:
        if filename.endswith((".xlsx", ".xls")):
            workbook = openpyxl.load_workbook(file.name, data_only=True)
            sheet = workbook.active
            return "\n".join(
                "\t".join(str(cell.value or "") for cell in row)
                for row in sheet.iter_rows()
            )

        elif filename.endswith(".csv"):
            df = pd.read_csv(file.name)
            return df.to_string(index=False)

        elif filename.endswith(".pdf"):
            reader = PyPDF2.PdfReader(file)
            return "\n".join(page.extract_text() or "" for page in reader.pages)

        elif filename.endswith((".jpg", ".jpeg", ".png")):
            image = Image.open(io.BytesIO(file.read()))
            return pytesseract.image_to_string(image)

        else:
            raise ValueError("Unsupported file format.")

    except Exception as e:
        raise ValueError(f"Error processing {file.name}: {e}")


def reconcile_statements_openai(erp_file, external_file)::
    yield "⏳ Processing your request...", ""

    try:
        erp_data = extract_transactions(erp_file)
        external_data = extract_transactions(external_file)


        prompt = f"""
You are a financial analyst specializing in account reconciliations. Your task is to compare two data sets: one from an ERP system and the other from a 

Bank or Vendor statement.

The goal is to identify which transactions match across both data sets, and which transactions are unmatched or potentially erroneous.

Each dataset contains transaction entries with **Date**, **Amount**, and **Description**.

ERP descriptions may include prefixes like "Vendor Payment - ", while the bank descriptions are simpler (e.g., "Utilities" instead of "Vendor Payment - Utilities"). Please normalize these for accurate comparison.

---

Please follow this format in your response:

1. 📘 **Introduction**  
   Briefly explain what reconciliation means and how you'll approach it.

2. ✅ **Matched Transactions**  
   Make a table to show side by side comparison of matched transactions

3. 🏦 **Unmatched Bank Transactions**  
   List any transactions found in 
   a. External file but not in the ERP file OR 
   b. ERP file but Not in External file

4. 🧾 **Summary & Suggested Next Steps**  
   Explain what the discrepancies might mean and what the user should do next.

---

Here is the ERP data:
{erp_data}

---

Here is the External (Bank or Vendor) data::
{external_data}
"""

        response = openai.ChatCompletion.create(
            model="gpt-4.1-nano",
            messages=[
                {"role": "system", "content": "You are a financial analyst who specializes in reconciling financial data."},
                {"role": "user", "content": prompt}
            ],
            temperature=0.2,
        )

        content = response.choices[0].message['content']

        html = f"""
        <div style="font-family: 'Segoe UI', sans-serif; line-height: 1.5;">
            <h2>🔍 Reconciliation Report</h2>
            <pre>{content}</pre>
        </div>
        """
        yield "✅ Done!", html

    except Exception as e:
        yield "❌ Error occurred", f"<h3>Error</h3><pre>{e}</pre>"

# Gradio UI
with gr.Blocks(css="""
#company-logo {
    width: 25%;
    margin: auto;
    display: block;
}
""") as iface:
    gr.Image("logo_Icon.png", elem_id="company-logo", label="Beiing Human")
    gr.Markdown("## 📊 ERP vs Bank/Vendor Reconciliation Tool")
    with gr.Row():
        erp_file = gr.File(label="📁 Upload ERP Statement", type="filepath")
        external_file = gr.File(label="🏦 Upload Bank or Vendor Statement", type="filepath")
    btn = gr.Button("🔍 Reconcile")
    with gr.Row():
        support_btn = gr.Button(
            "📧 Contact Support",
            link="mailto:rishi@beiinghuman.com?subject=Support%20Request&body=Hi%20Beiing%20Human%20Team%2C%0A%0AI%20have%20a%20question%20about%20the%20reconciliation%20tool.%20Here%20are%20the%20details..."
        )
        support_email_display = gr.Markdown("📬 Or email us at: `rishi@beiinghuman.com` (copy & paste)")

    #support_btn = gr.Button("📧 Contact Support: rishi@beiinghuman.com", link="mailto:rishi@beiinghuman.com?subject=Support%20Request&body=Hi%20Beiing%20Human%20Team%2C%0A%0AI%20have%20a%20question%20about%20the%20reconciliation%20tool.%20Here%20are%20the%20details...")

    
    status = gr.Markdown()
    result = gr.HTML()

    btn.click(
        fn=reconcile_statements_openai,
        inputs=[erp_file, external_file],
        outputs=[status, result]
    )

iface.launch()