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@y910075"
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):
    # Read CSV
    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)

    # Normalize column names
    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['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
    )

    # Rule-based anomaly detection based on ±5% threshold
    df['anomaly'] = df['deviation'].apply(lambda d: -1 if abs(d) > 5 else 1)

    # AI Suggestions
    df['ai_suggestion'] = df.apply(generate_suggestion, axis=1)

    # Reconciliation Status
    df['reconciliation_status'] = df['deviation'].apply(lambda d: 'Flagged' if abs(d) > 5 else 'Complete')

    # Insert into Salesforce
    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 (%)', 'material_type': 'Material Type'},
        color_discrete_map={'Flagged': '#FF4B4B', 'Complete': '#36A2EB'},
        height=400,
        template='plotly_white'
    )
    bar_fig.update_layout(
        title={'x': 0.5, 'xanchor': 'center', 'font': {'size': 20}},
        xaxis_title="Material Type",
        yaxis_title="Deviation (%)",
        font=dict(size=12),
        showlegend=True,
        margin=dict(l=50, r=50, t=80, b=50)
    )

    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'},
        height=400,
        template='plotly_white'
    )
    pie_fig.update_layout(
        title={'x': 0.5, 'xanchor': 'center', 'font': {'size': 20}},
        font=dict(size=12),
        margin=dict(l=50, r=50, t=80, b=50)
    )

    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

# Custom CSS for enhanced styling
custom_css = """
body {
    font-family: 'Arial', sans-serif;
    background-color: #f4f7fa;
}
.gradio-container {
    max-width: 1200px;
    margin: auto;
    padding: 20px;
}
h1 {
    color: #1a3c6e;
    text-align: center;
    font-size: 2.2em;
    margin-bottom: 20px;
}
.gr-button {
    background-color: #1a3c6e !important;
    color: white !important;
    border-radius: 8px !important;
    padding: 10px 20px !important;
    font-weight: bold !important;
    transition: background-color 0.3s ease;
}
.gr-button:hover {
    background-color: #2a5b9e !important;
}
.gr-file, .gr-textbox, .gr-dataframe {
    border-radius: 8px !important;
    border: 1px solid #d1d5db !important;
}
.gr-file label, .gr-textbox label, .gr-dataframe label, .gr-plot label {
    font-weight: bold;
    color: #1a3c6e;
    margin-bottom: 8px;
}
.gr-row {
    margin-bottom: 20px;
}
.gr-column {
    padding: 10px;
}
.gr-dataframe table {
    border-collapse: collapse;
    width: 100%;
}
.gr-dataframe th, .gr-dataframe td {
    border: 1px solid #e5e7eb;
    padding: 8px;
    text-align: left;
}
.gr-dataframe th {
    background-color: #e6eef8;
    color: #1a3c6e;
}
.status-message {
    color: #1a3c6e;
    font-style: italic;
    margin-top: 10px;
}
"""

# Gradio UI
with gr.Blocks(theme=gr.themes.Soft(), css=custom_css) as interface:
    gr.Markdown(
        """
        # Material Reconciliation Dashboard
        Upload a CSV file containing material data (columns: `project_id`, `material_type`, `planned_quantity`, `received_quantity`, `used_quantity`) to reconcile materials, view AI suggestions, and sync with Salesforce. Results include detailed reports, visualizations, and a downloadable CSV.
        """
    )
    with gr.Row():
        with gr.Column(scale=1):
            csv_input = gr.File(
                label="Upload CSV",
                file_types=[".csv"],
                elem_id="csv-input",
                tooltip="Upload a CSV file with columns: project_id, material_type, planned_quantity, received_quantity, used_quantity"
            )
            submit_button = gr.Button(
                "Reconcile Materials",
                variant="primary",
                elem_id="submit-button",
                loading_text="Processing..."
            )
            status_message = gr.Markdown(
                "", elem_classes=["status-message"], visible=False
            )
        with gr.Column(scale=2):
            output_text = gr.Textbox(
                label="Detailed Results",
                lines=20,
                placeholder="Reconciliation results will appear here...",
                show_copy_button=True
            )
    with gr.Row():
        with gr.Column(scale=2):
            output_table = gr.Dataframe(
                label="Reconciled Data",
                headers=[
                    'project_id', 'material_type', 'planned_quantity', 'received_quantity',
                    'used_quantity', 'balance_quantity', 'deviation', 'anomaly',
                    'ai_suggestion', 'reconciliation_status'
                ],
                wrap=True,
                interactive=False
            )
        with gr.Column(scale=1):
            ai_summary_output = gr.Textbox(
                label="AI Suggestions Summary",
                placeholder="AI suggestions will appear here...",
                show_copy_button=True
            )
    with gr.Row():
        with gr.Column(scale=1):
            bar_plot = gr.Plot(
                label="Deviation by Material Type",
                tooltip="Bar chart showing deviation percentages by material type"
            )
        with gr.Column(scale=1):
            pie_plot = gr.Plot(
                label="Reconciliation Status Distribution",
                tooltip="Pie chart showing the distribution of reconciliation statuses"
            )
    output_file = gr.File(
        label="Download Reconciled CSV",
        tooltip="Download the reconciled data as a CSV file"
    )

    def update_status_message():
        return gr.Markdown.update(
            value="Processing complete! Review the results below.",
            visible=True
        )

    submit_button.click(
        fn=reconcile_materials,
        inputs=csv_input,
        outputs=[output_file, output_text, output_table, bar_plot, pie_plot, ai_summary_output],
        _js="() => { document.querySelector('.status-message').innerText = 'Processing...'; return true; }"
    ).then(
        fn=update_status_message,
        inputs=None,
        outputs=status_message
    )

interface.launch()