import os
from paddleocr import PaddleOCR
from PIL import Image
import gradio as gr
import requests
import re
from simple_salesforce import Salesforce
import pandas as pd
import matplotlib.pyplot as plt
from io import BytesIO
from fuzzywuzzy import process
import kaleido  # Ensure kaleido is imported

# Attribute mappings: readable names to Salesforce API names
ATTRIBUTE_MAPPING = {
    "Product name": "Productname__c",
    "Colour": "Colour__c",
    "Motortype": "Motortype__c",
    "Frequency": "Frequency__c",
    "Grossweight": "Grossweight__c",
    "Ratio": "Ratio__c",
    "MotorFrame": "Motorframe__c",
    "Model": "Model__c",
    "Speed": "Speed__c",
    "Quantity": "Quantity__c",
    "Voltage": "Voltage__c",
    "Material": "Material__c",
    "Type": "Type__c",
    "Horsepower": "Horsepower__c",
    "Consignee": "Consignee__c",
    "LOT": "LOT__c",
    "Stage": "Stage__c",
    "Outlet": "Outlet__c",
    "Serialnumber": "Serialnumber__c",
    "HeadSize": "Headsize__c",
    "Deliverysize": "Deliverysize__c",
    "Phase": "Phase__c",
    "Size": "Size__c",
    "MRP": "MRP__c",
    "Usebefore": "Usebefore__c",
    "Height": "Height__c",
    "MaximumDischarge Flow": "Maximumdischargeflow__c",
    "DischargeRange": "Dischargeflow__c",
    "Assembledby": "Manufacturer__c",
    "Manufacturedate": "Manufacturedate__c",
    "Companyname": "Companyname__c",
    "Customercarenumber": "Customercarenumber__c",
    "SellerAddress": "Selleraddress__c",
    "Selleremail": "Selleremail__c",
    "GSTIN": "GSTIN__c",
    "Totalamount": "Totalamount__c",
    "Paymentstatus": "Paymentstatus__c",
    "Paymentmethod": "Paymentstatus__c",
    "Invoicedate": "Manufacturedate__c",
    "Warranty": "Warranty__c",
    "Brand": "Brand__c",
    "Motorhorsepower": "Motorhorsepower__c",
    "Power": "Power__c",
    "Motorphase": "Motorphase__c",
    "Enginetype": "Enginetype__c",
    "Tankcapacity": "Tankcapacity__c",
    "Head": "Head__c",
    "Usage/Application": "Usage_Application__c",
    "Volts": "volts__c",
    "Hertz": "Hertz__c",
    "Frame": "frame__c",
    "Mounting": "Mounting__c",
    "Tollfreenumber": "Tollfreenumber__c",
    "Pipesize": "Pipesize__c",
    "Manufacturer": "Manufacturer__c",
    "Office": "Office__c",
    "SRnumber": "SRnumber__c",
    "TypeOfEndUse": "TypeOfEndUse__c",
    "Model Name": "Model_Name_Number__c",
    "coolingmethod": "coolingmethod__c"
}

# List of product names to match
PRODUCT_NAMES = [
    "Centrifugal mono block pump", "SINGLE PHASE MOTOR STARTER", "EasyPact EZC 100",
    "Openwell Submersible Pumpset", "Electric Motor", "Self Priming Pump",
    "Control panel for single phase submersible pumps", "MOTOR", "Submersible pump set",
    "Fusion submersible pump set", "DCT", "Shock proof water proof", "CG COMMERCIAL MOTORS", "Fusion",
    "control panel for single phase submerisible pumps",
    "single phase digital starter dry run and timer panel", "5HP AV1 XL Kirloskar Pump",
    "Phase stainless steel submersible pump", "Submersible pump", "WB15X",
    "Vtype self priming pump", "SP SHINE DISC", "havells submersible pump",
    "Havells open well Submersible pump", "Bertolini pump CK3 90pp",
    "WPA 772 Water Pump Assy", "bertolini TTL triplex high pressure plunger pumps",
    "Generic plunger high pressure pump", "Apple Normal, Banana",
    "Cast Iron KSb centrifugal pump", "5.5kw Water Pump",
    "KSB reliable i line centrifuged pumps", "Apple Normal, Orange, Banana",
    "Positive API 6745 hydraulic diaphragm pump", "1/2 inch Fuel Hose Pipe", "Kirloskar Water Pump",
    "Rotodel motor pump", "PVC Electrical Insulation Materials",
    "Electric kirloskar domestic water pump", "Electrical Insulation Materials",
    "sellowell motor pump", "bhupathi submersible pump set",
    "Flowshine Submersible pump set", "Index submersible pump",
    "Wintoss Plastic Electric Switch Board", "Electric 18 watt ujagar cooler pump",
    "Generator Service", "LG WM FHT1207ZWL, LG REF GL-S292RSCY",
    "Water tank, Filters, Water Pump", "MS Control Submersible Panel",
    "Centrifugal Monoblock Pumps", "Electric Motor with Pump BodyBlue and White",
    "Various Repair and Maintenance Parts", "Earthmax Pump",
    "Water Tank, Filters, Water Pump", "Centrifugal Water Pump for Agriculture",
    "mono block pumps"
]

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

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

# 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 match product name using fuzzy matching
def match_product_name(extracted_text):
    best_match = None
    best_score = 0

    for line in extracted_text.split("\n"):
        match, score = process.extractOne(line, PRODUCT_NAMES)
        if score > best_score:
            best_match = match
            best_score = score

    return best_match if best_score >= 70 else None  # Threshold of 70 for a match

# Function to extract attributes and their values
def extract_attributes(extracted_text):
    attributes = {}

    for readable_attr, sf_attr in ATTRIBUTE_MAPPING.items():
        pattern = rf"{re.escape(readable_attr)}[:\-]?\s*(.+)"
        match = re.search(pattern, extracted_text, re.IGNORECASE)
        if match:
            attributes[readable_attr] = match.group(1).strip()

    return attributes

# Function to filter attributes for valid Salesforce fields
def filter_valid_attributes(attributes, valid_fields):
    return {ATTRIBUTE_MAPPING[key]: value for key, value in attributes.items() if ATTRIBUTE_MAPPING[key] in valid_fields}

#📊  Function to interact with Salesforce based on mode and type
def interact_with_salesforce(mode, entry_type, quantity, extracted_text):
    try:
        sf = Salesforce(
            username=SALESFORCE_USERNAME,
            password=SALESFORCE_PASSWORD,
            security_token=SALESFORCE_SECURITY_TOKEN
        )

        # Mapping mode and entry_type to Salesforce object and field
        object_name = None
        field_name = None
        field_names = []
        product_field_name = "Product_Name__c"
        model_field_name = None  # Correct field for model name

        if mode == "Entry":
            if entry_type == "Sales":
                object_name = "VENKATA_RAMANA_MOTORS__c"
                field_name = "Quantity__c"
            elif entry_type == "Non-Sales":
                object_name = "UNBILLING_DATA__c"
                field_name = "TotalQuantity__c"
        elif mode == "Exit":
            if entry_type == "Sales":
                object_name = "Inventory_Management__c"
                field_names = ["Quantity_Sold__c", "soldstock__c"]
                model_field_name = "Modal_Name__c"
            elif entry_type == "Non-Sales":
                object_name = "Un_Billable__c"
                field_names = ["Sold_Out__c", "soldstock__c"]
                model_field_name = "Model_Name__c"

        if not object_name or (not field_name and not field_names):
            return "Invalid mode or entry type."

        # Get valid fields for the specified Salesforce object
        sf_object = sf.__getattr__(object_name)
        schema = sf_object.describe()
        valid_fields = {field["name"] for field in schema["fields"]}

        # Extract product name and attributes
        product_name = match_product_name(extracted_text)
        attributes = extract_attributes(extracted_text)
        model_name = attributes.get("Model Name", "").strip()

        if not product_name:
            return "Product name could not be matched from the extracted text."

        attributes["Product name"] = product_name

        # Handling "Exit" Mode (Updating Records)
        if mode == "Exit":
            # Query should only match exact product name or exact model name
            query_conditions = []
            if model_name:
                query_conditions.append(f"{model_field_name} = '{model_name}'")
            query_conditions.append(f"{product_field_name} = '{product_name}'")

            query = f"SELECT Id, {', '.join(field_names)} FROM {object_name} WHERE {' OR '.join(query_conditions)} LIMIT 1"
            response = sf.query(query)

            if response["records"]:
                record_id = response["records"][0]["Id"]
                updated_fields = {field: quantity for field in field_names}
                sf_object.update(record_id, updated_fields)
                return f"✅ Updated record for product '{product_name}' ({model_name}) in {object_name}. Updated fields: {updated_fields}."
            else:
                return f"❌ No matching record found for product '{product_name}' ({model_name}) in {object_name}."

        # Handling "Entry" Mode (Creating Records)
        else:
            filtered_attributes = filter_valid_attributes(attributes, valid_fields)
            filtered_attributes[field_name] = quantity
            sf_object.create(filtered_attributes)
            return f"✅ Data successfully exported to Salesforce object {object_name}."

    except Exception as e:
        return f"❌ Error interacting with Salesforce: {str(e)}"

# Function to pull structured data from Salesforce and display as a table
def pull_data_from_salesforce():
    try:
        sf = Salesforce(
            username=SALESFORCE_USERNAME,
            password=SALESFORCE_PASSWORD,
            security_token=SALESFORCE_SECURITY_TOKEN
        )
       
        query_inventory = "SELECT Product_Name__c, Current_Stocks__c, soldstock__c FROM Inventory_Management__c LIMIT 100"
        query_unbillable= "SELECT Product_Name__c, Current_Stock__c, soldstock__c FROM Un_Billable__c LIMIT 100"
        
        
        response_inventory = sf.query_all(query_inventory)
        response_unbillable = sf.query_all(query_unbillable)
       
        records_inventory = response_inventory.get("records", [])
        records_unbillable = response_unbillable.get("records", [])
        
        if not records_inventory and not records_unbillable:
            return "No data found in Salesforce.", None, None, None
        
        records = records_inventory + records_unbillable
        df = pd.DataFrame(records)
        df = df.drop(columns=['attributes'], errors='ignore')
       
        # Rename columns for better readability
        df.rename(columns={
            "Product_Name__c": "Product Name",
            "Modal_Name__c": "Model Name (Inventory)",
            "Model_Name__c": "Model Name (Unbillable)",
            "Current_Stocks__c": "Current Stocks",
            "soldstock__c": "Sold Stock"
        }, inplace=True)
       
        excel_path = "salesforce_data.xlsx"
        df.to_excel(excel_path, index=False)
       
        # Generate interactive vertical bar graph using Matplotlib
        fig, ax = plt.subplots(figsize=(12, 8))
        df.plot(kind='bar', x="Product Name", y="Current Stocks", ax=ax, legend=False)
        ax.set_title("Stock Distribution by Product Name")
        ax.set_xlabel("Product Name")
        ax.set_ylabel("Current Stocks")
        plt.xticks(rotation=45, ha="right", fontsize=10)
        plt.tight_layout()
        buffer = BytesIO()
        plt.savefig(buffer, format="png")
        buffer.seek(0)
        img = Image.open(buffer)
       
        return "Data successfully retrieved.", df, excel_path, img
    except Exception as e:
        return f"Error fetching data: {str(e)}", None, None, None

# Unified function to handle image processing and Salesforce interaction
def process_image(image, mode, entry_type, quantity):
    extracted_text = extract_text(image)
    if not extracted_text:
        return "No text detected in the image.", None

    product_name = match_product_name(extracted_text)
    attributes = extract_attributes(extracted_text)
    if product_name:
        attributes["Product name"] = product_name

    # Interact with Salesforce
    message = interact_with_salesforce(mode, entry_type, quantity, extracted_text)

    numbered_output = "\n".join([f"{key}: {value}" for key, value in attributes.items()])
    return f"Extracted Text:\n{extracted_text}\n\nAttributes and Values:\n{numbered_output}", message

# Gradio Interface
def app():
    return gr.TabbedInterface([
        gr.Interface(
            fn=process_image,
            inputs=[
                gr.Image(type="numpy", label="📄ᴜᴘʟᴏᴀᴅ ɪᴍᴀɢᴇ"),
                gr.Dropdown(label="📌 Mode", choices=["Entry", "Exit"], value="Entry"),
                gr.Radio(label="📦 Entry Type", choices=["Sales", "Non-Sales"], value="Sales"),
                gr.Number(label="🔢 Quantity", value=1, interactive=True),
            ],
            outputs=[
                gr.Text(label="📝 Extracted Image Data"),
                gr.Text(label="🚀 Result")
            ],
            title="🏢 𝑽𝑬𝑵𝑲𝑨𝑻𝑨𝑹𝑨𝑴𝑨𝑵𝑨 𝑴𝑶𝑻𝑶𝑹𝑺",
            description="📦 𝐈𝐍𝐕𝐄𝐍𝐓𝐎𝐑𝐘 𝐌𝐀𝐍𝐀𝐆𝐄𝐌𝐄𝐍𝐓"
        ),
        gr.Interface(
            fn=pull_data_from_salesforce,
            inputs=[],
            outputs=[
                gr.Text(label="Status"),
                gr.Dataframe(label="📦 Salesforce Data Table"),
                gr.File(label="Download Salesforce Data"),
                gr.Image(label="📉 Stock Distribution Bar Graph")
            ],
            title="📊 Salesforce Data Export",
            description="View, visualize (zoom-in/out), and download Salesforce data (Product Name, Model Name, Current Stocks)."
        )
    ], ["📥 OCR Processing", "📊 Salesforce Data Export"])

if __name__ == "__main__":
    app().launch(share=True)

if __name__ == "__main__":
    app().launch(share=True)