import os
from paddleocr import PaddleOCR
from PIL import Image, ImageEnhance
import gradio as gr
import pandas as pd
import re
from simple_salesforce import Salesforce

# Attributes to match in the image
ATTRIBUTES = [
    "Product name", "Colour", "Motortype", "Frequency", "Grossweight", "Ratio",
    "MotorFrame", "Model", "Speed", "Quantity", "Voltage", "Material", "Type",
    "Horsepower", "Consignee", "LOT", "Stage", "Outlet", "Serialnumber", "HeadSize",
    "Deliverysize", "Phase", "Size", "MRP", "Usebefore", "Height",
    "MaximumDischarge Flow", "DischargeRange", "Assembledby", "Manufacturedate",
    "Companyname", "Customercarenumber", "SellerAddress", "Selleremail", "GSTIN",
    "Totalamount", "Paymentstatus", "Paymentmethod", "Invoicedate", "Warranty",
    "Brand", "Motorhorsepower", "Power", "Motorphase", "Enginetype", "Tankcapacity",
    "Head", "Usage/Application", "Volts", "Hertz", "Frame", "Mounting", "Tollfreenumber",
    "Pipesize", "Manufacturer", "Office", "Size", "Ratio", "SRnumber", "volts", "weight",  
    "frame", "TypeOfEndUse", "Model Name", "cooling method",
]

# Salesforce credentials
SALESFORCE_USERNAME = "venkatramana@sandbox.com"
SALESFORCE_PASSWORD = "Venkat12345@"
SALESFORCE_SECURITY_TOKEN = "GhcJJmjBEefdnukJoz4CAQlR"

# Initialize PaddleOCR
ocr = PaddleOCR(use_angle_cls=True, lang='en')

# Environment variable for the Excel file path
EXCEL_FILE_PATH = os.getenv("EXCEL_FILE_PATH", "DataStorage.xlsx")

# Function to extract text using PaddleOCR
def extract_text(image):
    result = ocr.ocr(image)
    extracted_text = []
    for line in result[0]:
        extracted_text.append(line[1][0])
    return "\n".join(extracted_text)

# Function to find attributes and their values
def find_attributes(text):
    structured_data = {}
    for attr in ATTRIBUTES:
        pattern = rf"{re.escape(attr)}[:\-]?\s*(.+)"  # Match the attribute and capture its value
        match = re.search(pattern, text, re.IGNORECASE)
        if match:
            structured_data[attr] = match.group(1).strip()
    return structured_data

# Function to sanitize numeric values
def sanitize_numeric(value):
    try:
        # If the value is already numeric, return it as-is
        if isinstance(value, (int, float)):
            return value
        if '/' in value:  # Handle fraction strings like "1/2"
            numerator, denominator = value.split('/')
            return float(numerator) / float(denominator)
        sanitized = re.sub(r'[^\d\.\-]', '', value)  # Remove non-numeric characters
        return float(sanitized) if sanitized else None
    except (ValueError, ZeroDivisionError):
        return None

# Function to save structured data to the constant Excel file
def save_to_excel(data):
    if not data:
        return "No data to save."

    if not os.path.exists(EXCEL_FILE_PATH):
        df = pd.DataFrame([data])
        df.to_excel(EXCEL_FILE_PATH, index=False, engine="openpyxl")
    else:
        existing_df = pd.read_excel(EXCEL_FILE_PATH, engine="openpyxl")
        new_data_df = pd.DataFrame([data])
        updated_df = pd.concat([existing_df, new_data_df], ignore_index=True)
        updated_df.to_excel(EXCEL_FILE_PATH, index=False, engine="openpyxl")

    return EXCEL_FILE_PATH

# Function to update stock in Inventory Management
def update_stock_in_inventory_management(data, quantity):
    try:
        # Initialize Salesforce connection
        sf = Salesforce(
            username=SALESFORCE_USERNAME,
            password=SALESFORCE_PASSWORD,
            security_token=SALESFORCE_SECURITY_TOKEN,
        )

        # Query the existing stock for the product
        product_name = data.get("Product name")
        query = f"SELECT Id, Current_Stocks__c FROM Inventory_Management__c WHERE Product_Name__c = '{product_name}' LIMIT 1"
        result = sf.query(query)

        if result["totalSize"] == 0:
            return f"Product '{product_name}' not found in Inventory Management."

        product_id = result["records"][0]["Id"]
        current_stock = result["records"][0].get("Current_Stocks__c", 0)

        # Ensure current_stock is not None
        current_stock = current_stock or 0

        # Deduct stock by the quantity
        updated_stock = max(0, current_stock - quantity)  # Ensure stock doesn't go negative

        # Update the stock in Salesforce
        sf.Inventory_Management__c.update(product_id, {"Current_Stocks__c": updated_stock})

        return f"Stock updated successfully. {quantity} units deducted from '{product_name}'. Remaining stock: {updated_stock}."
    except Exception as e:
        return f"Error updating stock in Inventory Management: {str(e)}"

# Function to add stock in VENKATA_RAMANA_MOTORS__c
def add_stock_to_venkataramana(data, quantity):
    try:
        # Initialize Salesforce connection
        sf = Salesforce(
            username=SALESFORCE_USERNAME,
            password=SALESFORCE_PASSWORD,
            security_token=SALESFORCE_SECURITY_TOKEN,
        )

        # Target Salesforce object
        object_name = "VENKATA_RAMANA_MOTORS__c"
        sf_object = sf.__getattr__(object_name)

        # Fetch valid field names from the object schema
        schema = sf_object.describe()
        valid_fields = {field["name"] for field in schema["fields"]}

        # Prepare the record with valid fields
        record = {
            "Productname__c": data.get("Product name"),
            "Quantity__c": sanitize_numeric(quantity),
        }

        # Filter record to include only valid fields
        filtered_record = {k: v for k, v in record.items() if k in valid_fields and v is not None}

        # Add stock to Salesforce
        sf_object.create(filtered_record)
        return f"Data successfully added to {object_name}."
    except Exception as e:
        return f"Error adding stock to VENKATA_RAMANA_MOTORS__c: {str(e)}"

# Function to process image for the entry interface
def process_entry_image(image, quantity):
    try:
        extracted_text = extract_text(image)
        attributes = find_attributes(extracted_text)
        attributes["Quantity"] = quantity  # Include quantity in attributes

        if attributes:
            numbered_output = "\n".join(
                [f"{i + 1}. {key}: {value}" for i, (key, value) in enumerate(attributes.items())]
            )
            # Save to Excel
            file_path = save_to_excel(attributes)

            # Add stock to VENKATA_RAMANA_MOTORS__c
            salesforce_message = add_stock_to_venkataramana(attributes, quantity)

            return f"{numbered_output}\n\n{salesforce_message}", file_path
        else:
            return "No attributes found with values in the image.", None
    except Exception as e:
        return f"Error during processing: {str(e)}", None

# Function to process image for the exit interface
def process_exit_image(image, quantity):
    try:
        extracted_text = extract_text(image)
        attributes = find_attributes(extracted_text)
        attributes["Quantity"] = quantity  # Include quantity in attributes

        if attributes:
            numbered_output = "\n".join(
                [f"{i + 1}. {key}: {value}" for i, (key, value) in enumerate(attributes.items())]
            )
            # Save to Excel
            file_path = save_to_excel(attributes)

            # Deduct stock in Inventory Management
            stock_message = update_stock_in_inventory_management(attributes, quantity)

            return f"{numbered_output}\n\n{stock_message}", file_path
        else:
            return "No attributes found with values in the image.", None
    except Exception as e:
        return f"Error during processing: {str(e)}", None

# Gradio Interfaces
entry_interface = gr.Interface(
    fn=process_entry_image,
    inputs=[
        gr.Image(type="numpy"),
        gr.Number(label="Quantity", value=1, interactive=True),
    ],
    outputs=[
        gr.Text(label="Image Data Viewer"),
        gr.File(label="Data Storage Manager")
    ],
    title="Entry Interface - VENKATARAMANA MOTORS",
    description="Process images and add stock to VENKATA_RAMANA_MOTORS__c."
)

exit_interface = gr.Interface(
    fn=process_exit_image,
    inputs=[
        gr.Image(type="numpy"),
        gr.Number(label="Quantity", value=1, interactive=True),
    ],
    outputs=[
        gr.Text(label="Image Data Viewer"),
        gr.File(label="Data Storage Manager")
    ],
    title="Exit Interface - VENKATARAMANA MOTORS",
    description="Process images and deduct stock from Inventory Management."
)

if __name__ == "__main__":
    gr.TabbedInterface([entry_interface, exit_interface], ["Entry", "Exit"]).launch(share=True)