import pandas as pd
import numpy as np
import gradio as gr
import os
import tempfile
import logging
from simple_salesforce import Salesforce
import plotly.express as px

# Logging setup
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

# Salesforce credentials
SALESFORCE_USERNAME = "vijaypulmamidi.dev2025@sathkrutha.com"
SALESFORCE_PASSWORD = "Vij@y9100754977"
SALESFORCE_SECURITY_TOKEN = "CaZSEwVmB3EIAiV6G8ukdDp0"

# Connect to Salesforce
sf = Salesforce(username=SALESFORCE_USERNAME, password=SALESFORCE_PASSWORD, security_token=SALESFORCE_SECURITY_TOKEN)
logger.info("Connected to Salesforce.")

def find_salesforce_project(project_name, sf):
    try:
        query = f"SELECT Id FROM Project__c WHERE Name = '{project_name}' LIMIT 1"
        result = sf.query(query)
        if result['totalSize'] > 0:
            return result['records'][0]['Id']
    except Exception as e:
        logger.warning(f"Salesforce project lookup failed for '{project_name}': {e}")
    return None

def insert_reconciliation_to_salesforce(df, sf):
    inserted_count = 0
    project_cache = {}

    for index, row in df.iterrows():
        project_id = None
        if 'project_id' in df.columns and pd.notna(row['project_id']):
            project_name = row['project_id']
            if project_name in project_cache:
                project_id = project_cache[project_name]
            else:
                project_id = find_salesforce_project(project_name, sf)
                if project_id:
                    project_cache[project_name] = project_id
                else:
                    logger.info(f"Project '{project_name}' not found in Salesforce, skipping project ID linkage.")

        record = {
            'Material_Type__c': row['material_type'],
            'Planned_Quantity__c': row['planned_quantity'],
            'Received_Quantity__c': row['received_quantity'],
            'Used_Quantity__c': row['used_quantity'],
            'AI_Suggestion__c': row.get('ai_suggestion', ''),
            'Reconciliation_Status__c': row.get('reconciliation_status', '')
        }
        if project_id:
            record['Project_ID__c'] = project_id

        try:
            sf.Material_Reconciliation_Record__c.create(record)
            inserted_count += 1
        except Exception as e:
            logger.error(f"Failed to insert record for material {row['material_type']}: {e}")

    return f"Inserted {inserted_count} records into Salesforce"

def generate_suggestion(row):
    if row['deviation'] > 5:
        excess = row['used_quantity'] - row['planned_quantity']
        return f"Overuse Alert: Reduce future orders by {excess:.0f} units of {row['material_type']}."
    elif row['deviation'] < -5:
        surplus = abs(row['planned_quantity'] - row['used_quantity'])
        return f"Surplus Detected: {surplus:.0f} units unused. Consider reducing future orders."
    return "Usage as planned. No action needed."

def reconcile_materials(csv_file):
    if isinstance(csv_file, str):
        df = pd.read_csv(csv_file)
    elif hasattr(csv_file, 'name'):
        df = pd.read_csv(csv_file.name)
    else:
        csv_file.seek(0)
        df = pd.read_csv(csv_file)

    df.columns = df.columns.str.strip().str.lower().str.replace(' ', '_')

    col_map = {
        'project_id': 'project_id',
        'material_type': 'material_type',
        'planned_quantity': 'planned_quantity',
        'received_quantity': 'received_quantity',
        'used_quantity': 'used_quantity',
    }

    mapped_cols = {}
    for expected_col in col_map:
        for actual_col in df.columns:
            if actual_col == expected_col:
                mapped_cols[expected_col] = actual_col
                break

    df.rename(columns=mapped_cols, inplace=True)

    required = ['material_type', 'planned_quantity', 'received_quantity', 'used_quantity']
    missing = [col for col in required if col not in df.columns]
    if missing:
        return None, f"Error: Missing required column(s): {', '.join(missing)}", None, None, None, None

    for col in ['planned_quantity', 'received_quantity', 'used_quantity']:
        df[col] = pd.to_numeric(df[col], errors='coerce').fillna(0)

    df['used_quantity'] = df[['used_quantity', 'received_quantity']].min(axis=1)
    df['balance_quantity'] = df['received_quantity'] - df['used_quantity']
    df['deviation'] = df.apply(
        lambda row: ((row['used_quantity'] - row['planned_quantity']) / row['planned_quantity']) * 100
        if row['planned_quantity'] != 0 else 0,
        axis=1
    )

    df['anomaly'] = df['deviation'].apply(lambda d: -1 if abs(d) > 5 else 1)
    df['ai_suggestion'] = df.apply(generate_suggestion, axis=1)
    df['reconciliation_status'] = df['deviation'].apply(lambda d: 'Flagged' if abs(d) > 5 else 'Complete')

    salesforce_result = insert_reconciliation_to_salesforce(df, sf)

    output_text = f"Material Reconciliation Results\n=============================\n\n"
    output_text += f"{salesforce_result}\n\nDetailed Records:\n"
    for i, row in df.iterrows():
        output_text += f"Record {i + 1}:\n"
        if 'project_id' in df.columns and pd.notna(row.get('project_id')):
            output_text += f"  Project ID: {row['project_id']}\n"
        output_text += f"  Material Type: {row['material_type']}\n"
        output_text += f"  Planned Quantity: {row['planned_quantity']}\n"
        output_text += f"  Received Quantity: {row['received_quantity']}\n"
        output_text += f"  Used Quantity: {row['used_quantity']}\n"
        output_text += f"  Balance Quantity: {row['balance_quantity']}\n"
        output_text += f"  Deviation: {row['deviation']:.2f}%\n"
        output_text += f"  Anomaly: {'Yes' if row['anomaly'] == -1 else 'No'}\n"
        output_text += f"  AI Suggestion: {row['ai_suggestion']}\n"
        output_text += f"  Reconciliation Status: {row['reconciliation_status']}\n"
        output_text += "-----------------------------\n"

    with tempfile.NamedTemporaryFile(delete=False, suffix='.csv') as tmp:
        output_file = tmp.name
        df.to_csv(output_file, index=False)

    bar_fig = px.bar(
        df, x='material_type', y='deviation',
        color='reconciliation_status',
        title='Deviation by Material Type',
        labels={'deviation': 'Deviation (%)'},
        color_discrete_map={'Flagged': '#FF4B4B', 'Complete': '#36A2EB'}
    )

    pie_data = df['reconciliation_status'].value_counts().reset_index()
    pie_data.columns = ['Reconciliation_Status', 'Count']
    pie_fig = px.pie(
        pie_data, names='Reconciliation_Status', values='Count',
        title='Reconciliation Status Distribution',
        color_discrete_map={'Flagged': '#FF4B4B', 'Complete': '#36A2EB'}
    )

    ai_summary = "\n".join([f"{row['material_type']}: {row['ai_suggestion']}" for _, row in df.iterrows()])
    return output_file, output_text, df, bar_fig, pie_fig, ai_summary

# Gradio UI
with gr.Blocks(css='button:has(span:contains("Share via Link")) { display: none !important; }') as interface:
    gr.Markdown("# Material Reconciliation Dashboard")
    with gr.Row():
        with gr.Column(scale=1):
            csv_input = gr.File(label="Upload CSV", file_types=[".csv"])
            submit_button = gr.Button("Reconcile Materials")
        with gr.Column(scale=2):
            output_text = gr.Textbox(label="Detailed Results", lines=20)
    with gr.Row():
        with gr.Column(scale=2):
            output_table = gr.Dataframe(label="Reconciled Data")
        with gr.Column(scale=1):
            ai_summary_output = gr.Textbox(label="AI Suggestions Summary")
    with gr.Row():
        with gr.Column(scale=1):
            bar_plot = gr.Plot(label="Deviation Plot")
        with gr.Column(scale=1):
            pie_plot = gr.Plot(label="Status Distribution")
    output_file = gr.File(label="Download Reconciled CSV")

    submit_button.click(
        fn=reconcile_materials,
        inputs=csv_input,
        outputs=[output_file, output_text, output_table, bar_plot, pie_plot, ai_summary_output]
    )

interface.launch()