import gradio as gr
import pandas as pd
from datetime import datetime
import warnings
warnings.filterwarnings('ignore')

# Global variables for data storage
backlog_df = None
billing_df = None
inventory_df = None

def initialize_data():
    """Load data files from the repository on startup"""
    global backlog_df, billing_df, inventory_df
    
    try:
        backlog_df = pd.read_excel("BACKLOG.xlsx")
        billing_df = pd.read_excel("BILLING.xlsx")
        inventory_df = pd.read_excel("INVENTORY.xlsx")
        return True
    except Exception as e:
        print(f"Error loading data: {e}")
        return False

def get_fulfilled_orders():
    """Get fulfilled orders - simplified view"""
    if billing_df is None:
        return pd.DataFrame({"Message": ["⚠️ Data not loaded"]})
    
    try:
        fulfilled_orders = []
        
        for idx, row in billing_df.iterrows():
            billing_qty = float(row.get('BILLING_QUANTITY', 0))
            extended_resale = float(row.get('EXTENDED_RESALE_USD', 0))
            
            if billing_qty > 0:
                fulfilled_orders.append({
                    'Sales Order': row.get('SALES_ORDER_NO', 'N/A'),
                    'End Customer': row.get('END_CUSTOMER_NAME', 'N/A'),
                    'Order Value': f"${extended_resale:,.2f}"
                })
        
        df = pd.DataFrame(fulfilled_orders)
        return df if len(df) > 0 else pd.DataFrame({"Message": ["No fulfilled orders found"]})
        
    except Exception as e:
        return pd.DataFrame({"Error": [str(e)]})

def get_pending_orders():
    """Get pending sales orders - simplified view"""
    if backlog_df is None:
        return pd.DataFrame({"Message": ["⚠️ Data not loaded"]})
    
    try:
        pending_orders = []
        
        for idx, row in backlog_df.iterrows():
            open_qty = float(row.get('OPEN_QUANTITY_OF_SO', 0))
            unit_price = float(row.get('UNIT_PRICE', 0))
            
            if open_qty > 0:
                pending_value = open_qty * unit_price
                
                pending_orders.append({
                    'Sales Order': row.get('SALES_ORDER_NO', 'N/A'),
                    'Material ID': row.get('SAP_MATERIAL_NO', 'N/A'),
                    'Open Qty': f"{open_qty:,.0f}",
                    'Pending Value': f"${pending_value:,.2f}",
                    'ATP Date': str(row.get('ATP_DATE_RAW', 'N/A'))[:10]
                })
        
        df = pd.DataFrame(pending_orders)
        return df if len(df) > 0 else pd.DataFrame({"Message": ["No pending orders found"]})
        
    except Exception as e:
        return pd.DataFrame({"Error": [str(e)]})

def get_open_quantity_orders():
    """Get orders with open quantity - simplified view"""
    if backlog_df is None:
        return pd.DataFrame({"Message": ["⚠️ Data not loaded"]})
    
    try:
        open_orders = []
        
        for idx, row in backlog_df.iterrows():
            open_qty = float(row.get('OPEN_QUANTITY_OF_SO', 0))
            unit_price = float(row.get('UNIT_PRICE', 0))
            
            if open_qty > 0:
                open_value = open_qty * unit_price
                
                open_orders.append({
                    'Sales Order': row.get('SALES_ORDER_NO', 'N/A'),
                    'Material ID': row.get('SAP_MATERIAL_NO', 'N/A'),
                    'Open Quantity': f"{open_qty:,.0f}",
                    'Open Value': f"${open_value:,.2f}",
                    'ATP Date': str(row.get('ATP_DATE_RAW', 'N/A'))[:10],
                    'Supplier': row.get('SUPPLIER_NAME', 'N/A')
                })
        
        df = pd.DataFrame(open_orders)
        return df if len(df) > 0 else pd.DataFrame({"Message": ["No open quantity found"]})
        
    except Exception as e:
        return pd.DataFrame({"Error": [str(e)]})

# Custom CSS and JavaScript for tab navigation
custom_css = """
<style>
    /* Style adjustments if needed */
</style>
"""

custom_js = """
<script>
function setActiveTab() {
    // Get tab parameter from URL
    const urlParams = new URLSearchParams(window.location.search);
    const tabParam = urlParams.get('tab');
    
    if (tabParam !== null) {
        const tabIndex = parseInt(tabParam);
        
        // Wait for Gradio to load, then switch tabs
        setTimeout(() => {
            // Find all tab buttons
            const tabButtons = document.querySelectorAll('.tab-nav button');
            
            if (tabButtons && tabButtons[tabIndex]) {
                // Click the correct tab
                tabButtons[tabIndex].click();
            }
        }, 500);
    }
}

// Run when page loads
if (document.readyState === 'loading') {
    document.addEventListener('DOMContentLoaded', setActiveTab);
} else {
    setActiveTab();
}
</script>
"""

def create_dashboard():
    """Create the multi-page order details dashboard"""
    
    # Initialize data on startup
    data_loaded = initialize_data()
    
    with gr.Blocks(
        title="Order Details Dashboard", 
        theme=gr.themes.Soft(),
        css=custom_css,
        head=custom_js
    ) as dashboard:
        
        gr.Markdown("""
        # 📊 Sales Order Details Dashboard
        """)
        
        if not data_loaded:
            gr.Markdown("""
            ⚠️ **Data files not found.** Please upload BACKLOG.xlsx, BILLING.xlsx, and INVENTORY.xlsx to the Space files.
            """)
        
        # Multi-page tabs
        with gr.Tabs() as tabs:
            
            # Tab 1: Orders Fulfilled
            with gr.TabItem("✅ Orders Fulfilled", id=0):
                gr.Markdown("### Orders Fulfilled")
                
                fulfilled_table = gr.Dataframe(
                    value=get_fulfilled_orders() if data_loaded else pd.DataFrame({"Message": ["Data not loaded"]}),
                    wrap=True
                )
                
                with gr.Row():
                    refresh_fulfilled_btn = gr.Button("🔄 Refresh", variant="secondary")
                
                refresh_fulfilled_btn.click(
                    fn=get_fulfilled_orders,
                    outputs=fulfilled_table
                )
            
            # Tab 2: Pending Sales Orders
            with gr.TabItem("⏳ Pending Sales Orders", id=1):
                gr.Markdown("### Pending Sales Orders")
                
                pending_table = gr.Dataframe(
                    value=get_pending_orders() if data_loaded else pd.DataFrame({"Message": ["Data not loaded"]}),
                    wrap=True
                )
                
                with gr.Row():
                    refresh_pending_btn = gr.Button("🔄 Refresh", variant="secondary")
                
                refresh_pending_btn.click(
                    fn=get_pending_orders,
                    outputs=pending_table
                )
            
            # Tab 3: Open Order Quantity
            with gr.TabItem("📦 Open Order Quantity", id=2):
                gr.Markdown("### Open Order Quantity")
                
                open_qty_table = gr.Dataframe(
                    value=get_open_quantity_orders() if data_loaded else pd.DataFrame({"Message": ["Data not loaded"]}),
                    wrap=True
                )
                
                with gr.Row():
                    refresh_open_btn = gr.Button("🔄 Refresh", variant="secondary")
                
                refresh_open_btn.click(
                    fn=get_open_quantity_orders,
                    outputs=open_qty_table
                )
            
            # Tab 4: Pending Order Value
            with gr.TabItem("💰 Pending Order Value", id=3):
                gr.Markdown("### Pending Order Value")
                
                value_table = gr.Dataframe(
                    value=get_pending_orders() if data_loaded else pd.DataFrame({"Message": ["Data not loaded"]}),
                    wrap=True
                )
                
                with gr.Row():
                    refresh_value_btn = gr.Button("🔄 Refresh", variant="secondary")
                
                refresh_value_btn.click(
                    fn=get_pending_orders,
                    outputs=value_table
                )
        
        gr.Markdown("""
        ---
        **Order Details Dashboard** | Last Updated: """ + datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
    
    return dashboard

# Launch the dashboard
if __name__ == "__main__":
    dashboard = create_dashboard()
    dashboard.launch(
        server_name="0.0.0.0",
        server_port=7860,
        ssr_mode=False,
        auth=[
            ("kinnari", "alert2025"),
            ("neha", "alert2025"),
            ("niketha", "alert2025"),
            ("sahith", "alert2025"),
            ("shriya", "alert2025"),
        ],
        auth_message="📊 Order Details Dashboard - Enter Your Credentials"
    )