app.py

import gradio as gr
import pandas as pd
import numpy as np
import os
from datetime import datetime
import tempfile
from collections import defaultdict

# Required columns for dyeing priority calculation
REQUIRED_COLS = [
    "Account", 
    "Order #",
    "DESIGN",
    "Labels",
    "Colours",
    "Kgs",
    "Pending"
]

# Additional columns that might be present
OPTIONAL_COLS = ["Sqm", "Unnamed: 0"]

def _normalize_columns(df: pd.DataFrame) -> pd.DataFrame:
    """Normalize column names by stripping whitespace"""
    df = df.copy()
    df.columns = [str(c).strip() for c in df.columns]
    return df

def _parse_colours(colour_str):
    """Parse colour string into list of individual colours"""
    if pd.isna(colour_str):
        return []
    
    # Handle various separators (comma, semicolon, pipe, etc.)
    colour_str = str(colour_str).strip()
    
    # Try different separators
    for sep in [',', ';', '|', '/', '+', '&']:
        if sep in colour_str:
            colours = [c.strip().upper() for c in colour_str.split(sep) if c.strip()]
            return colours
    
    # If no separators found, treat as single colour
    return [colour_str.upper()] if colour_str else []

def calculate_colour_totals(df: pd.DataFrame) -> pd.DataFrame:
    """Calculate total quantity required for each colour across all designs"""
    colour_totals = defaultdict(float)
    colour_details = defaultdict(list)  # Track which designs use each colour
    
    for _, row in df.iterrows():
        colours = _parse_colours(row['Colours'])
        kgs = pd.to_numeric(row['Kgs'], errors='coerce') or 0
        design = str(row.get('DESIGN', 'Unknown'))
        order_num = str(row.get('Order #', 'Unknown'))
        
        if colours and kgs > 0:
            # Distribute weight equally among colours if multiple colours
            kgs_per_colour = kgs / len(colours)
            for colour in colours:
                colour_totals[colour] += kgs_per_colour
                colour_details[colour].append({
                    'Design': design,
                    'Order': order_num,
                    'Kgs_Contribution': kgs_per_colour,
                    'Total_Order_Kgs': kgs
                })
    
    # Convert to DataFrame with detailed breakdown
    colour_rows = []
    for colour, total_kgs in sorted(colour_totals.items(), key=lambda x: x[1], reverse=True):
        designs_using = list(set([detail['Design'] for detail in colour_details[colour]]))
        orders_count = len(colour_details[colour])
        
        colour_rows.append({
            'Colour': colour,
            'Total_Kgs_Required': round(total_kgs, 2),
            'Designs_Using_This_Colour': ', '.join(sorted(designs_using)),
            'Number_of_Orders': orders_count,
            'Priority_Rank': len(colour_rows) + 1
        })
    
    colour_df = pd.DataFrame(colour_rows)
    return colour_df, colour_details

def create_detailed_colour_breakdown(colour_details: dict) -> pd.DataFrame:
    """Create detailed breakdown showing which orders contribute to each colour"""
    breakdown_rows = []
    
    for colour, details in colour_details.items():
        for detail in details:
            breakdown_rows.append({
                'Colour': colour,
                'Design': detail['Design'],
                'Order_Number': detail['Order'],
                'Kgs_for_This_Colour': round(detail['Kgs_Contribution'], 2),
                'Total_Order_Kgs': detail['Total_Order_Kgs']
            })
    
    breakdown_df = pd.DataFrame(breakdown_rows)
    # Sort by colour, then by kgs contribution (descending)
    breakdown_df = breakdown_df.sort_values(['Colour', 'Kgs_for_This_Colour'], ascending=[True, False])
    
    return breakdown_df

def detect_date_columns(df: pd.DataFrame) -> list:
    """Detect date columns in the dataframe"""
    date_columns = []
    
    for col in df.columns:
        col_str = str(col).strip()
        
        # Try to parse as datetime
        try:
            pd.to_datetime(col_str)
            date_columns.append(col)
        except:
            # Check for date patterns like "13/8", "14/8"
            if '/' in col_str and len(col_str.split('/')) == 2:
                try:
                    parts = col_str.split('/')
                    if all(part.isdigit() for part in parts):
                        date_columns.append(col)
                except:
                    pass
    
    return date_columns

def find_earliest_order_date(df: pd.DataFrame) -> pd.Series:
    """Find the earliest date for each order from date columns"""
    date_columns = detect_date_columns(df)
    
    if not date_columns:
        # No date columns found, assign all orders as very old (high priority)
        return pd.Series([365] * len(df), index=df.index)  # 365 days old
    
    earliest_dates = []
    
    for idx, row in df.iterrows():
        order_dates = []
        
        for date_col in date_columns:
            cell_value = row[date_col]
            
            # Skip if cell is empty or contains non-date data
            if pd.isna(cell_value) or cell_value == 0 or cell_value == "":
                continue
            
            # Try to parse date from column name
            try:
                if '/' in str(date_col):
                    # Handle formats like "13/8" (day/month)
                    day, month = str(date_col).split('/')
                    # Assume current year
                    date_obj = pd.to_datetime(f"2025-{month.zfill(2)}-{day.zfill(2)}")
                else:
                    # Handle datetime column names
                    date_obj = pd.to_datetime(str(date_col))
                
                # If there's actual data in this cell (not empty/zero), consider this date
                if not pd.isna(cell_value) and str(cell_value).strip() != "" and str(cell_value) != "0":
                    order_dates.append(date_obj)
                    
            except:
                continue
        
        # Find earliest date for this order
        if order_dates:
            earliest_date = min(order_dates)
        else:
            # No valid dates found, assign a default old date
            earliest_date = pd.to_datetime("2024-01-01")
        
        earliest_dates.append(earliest_date)
    
    return pd.Series(earliest_dates, index=df.index)

def compute_dyeing_priority(df: pd.DataFrame, min_kgs: int = 100, weights: dict = None) -> tuple:
    """
    Compute dyeing priority based on:
    1. Oldest orders with minimum kgs per design
    2. Designs with fewest colours  
    3. Order age
    """
    
    # Default weights if not provided
    if weights is None:
        weights = {"AGE_WEIGHT": 50, "COLOUR_SIMPLICITY_WEIGHT": 30, "DESIGN_WEIGHT": 20}
    
    df = _normalize_columns(df)
    
    # Check for required columns (excluding Date which is now optional)
    missing = [c for c in REQUIRED_COLS if c not in df.columns]
    if missing:
        raise ValueError(f"Missing required columns: {missing}. Found columns: {list(df.columns)}")
    
    # Create working copy
    out = df.copy()
    
    # Find earliest order dates from date columns
    out["OrderDate"] = find_earliest_order_date(out)
    
    # Calculate age in days
    today = pd.Timestamp.now().normalize()
    out["OrderAgeDays"] = (today - out["OrderDate"]).dt.days
    out["OrderAgeDays"] = out["OrderAgeDays"].fillna(0).clip(lower=0)
    
    # Convert Kgs to numeric
    out["Kgs"] = pd.to_numeric(out["Kgs"], errors="coerce").fillna(0)
    
    # Parse colours and count them
    out["ColourList"] = out["Colours"].apply(_parse_colours)
    out["ColourCount"] = out["ColourList"].apply(len)
    
    # Group by design to calculate design-level metrics
    design_groups = out.groupby("DESIGN").agg({
        "Kgs": "sum",
        "OrderDate": "min",  # Oldest date for this design
        "OrderAgeDays": "max",  # Maximum age for this design
        "ColourCount": "first",  # Colour count should be same for same design
        "Order #": "count"  # Number of orders for this design
    }).reset_index()
    
    design_groups.columns = ["DESIGN", "Total_Kgs", "Oldest_Date", "Max_Age_Days", "ColourCount", "Order_Count"]
    
    # Filter designs that meet minimum kg requirement
    design_groups["MeetsMinKgs"] = design_groups["Total_Kgs"] >= min_kgs
    
    # Calculate scores for designs that meet criteria
    eligible_designs = design_groups[design_groups["MeetsMinKgs"]].copy()
    
    if len(eligible_designs) == 0:
        # If no designs meet criteria, include all for ranking
        eligible_designs = design_groups.copy()
        eligible_designs["MeetsMinKgs"] = False
    
    # Age Score (0-1, older = higher)
    if eligible_designs["Max_Age_Days"].max() > 0:
        eligible_designs["AgeScore_01"] = eligible_designs["Max_Age_Days"] / eligible_designs["Max_Age_Days"].max()
    else:
        eligible_designs["AgeScore_01"] = 0
    
    # Colour Simplicity Score (0-1, fewer colours = higher)
    if eligible_designs["ColourCount"].max() > 0:
        eligible_designs["ColourSimplicityScore_01"] = 1 - (eligible_designs["ColourCount"] / eligible_designs["ColourCount"].max())
    else:
        eligible_designs["ColourSimplicityScore_01"] = 0
    
    # Design Volume Score (0-1, more kgs = higher priority for production efficiency)
    if eligible_designs["Total_Kgs"].max() > 0:
        eligible_designs["VolumeScore_01"] = eligible_designs["Total_Kgs"] / eligible_designs["Total_Kgs"].max()
    else:
        eligible_designs["VolumeScore_01"] = 0
    
    # Calculate weighted priority scores
    w_age = weights["AGE_WEIGHT"] / 100.0
    w_colour = weights["COLOUR_SIMPLICITY_WEIGHT"] / 100.0  
    w_design = weights["DESIGN_WEIGHT"] / 100.0
    
    eligible_designs["AgeScore"] = eligible_designs["AgeScore_01"] * w_age
    eligible_designs["ColourSimplicityScore"] = eligible_designs["ColourSimplicityScore_01"] * w_colour
    eligible_designs["VolumeScore"] = eligible_designs["VolumeScore_01"] * w_design
    
    eligible_designs["PriorityScore"] = (
        eligible_designs["AgeScore"] + 
        eligible_designs["ColourSimplicityScore"] + 
        eligible_designs["VolumeScore"]
    )
    
    # Sort by priority
    eligible_designs = eligible_designs.sort_values(
        ["MeetsMinKgs", "PriorityScore", "Max_Age_Days"], 
        ascending=[False, False, False]
    )
    
    # Join back to original data to get detailed view
    detailed_results = out.merge(
        eligible_designs[["DESIGN", "Total_Kgs", "Max_Age_Days", "MeetsMinKgs", 
                         "AgeScore", "ColourSimplicityScore", "VolumeScore", "PriorityScore"]],
        on="DESIGN",
        how="left"
    )
    
    # Sort detailed results by priority
    detailed_results = detailed_results.sort_values(
        ["MeetsMinKgs", "PriorityScore", "OrderAgeDays"], 
        ascending=[False, False, False]
    )
    
    # Calculate colour totals with detailed breakdown
    colour_totals, colour_details = calculate_colour_totals(out)
    colour_breakdown = create_detailed_colour_breakdown(colour_details)
    
    return detailed_results, eligible_designs, colour_totals, colour_breakdown

def save_dyeing_results(detailed_df, design_summary, colour_totals, colour_breakdown, output_path, min_kgs, weights):
    """Save all results with multiple sheets"""
    
    with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
        
        # Sheet 1: Colour Requirements Summary (MAIN PRIORITY - what you need most!)
        colour_totals.to_excel(writer, sheet_name='COLOUR_REQUIREMENTS', index=False)
        
        # Sheet 2: Detailed Colour Breakdown (which orders contribute to each colour)
        colour_breakdown.to_excel(writer, sheet_name='Colour_Order_Breakdown', index=False)
        
        # Sheet 3: Design Summary (design-level priority ranking)
        design_summary.to_excel(writer, sheet_name='Design_Priority_Summary', index=False)
        
        # Sheet 4: Detailed Order Priority
        detailed_df.to_excel(writer, sheet_name='Order_Priority_Detail', index=False)
        
        # Sheet 5: Instructions
        instructions_data = [
            ['🎨 DYEING PRIORITY & COLOUR REQUIREMENTS ANALYSIS'],
            [''],
            ['📋 SHEET EXPLANATIONS:'],
            [''],
            ['1. COLOUR_REQUIREMENTS - 🎯 MAIN OUTPUT YOU NEED'],
            ['   • Total kgs needed for each colour (consolidated across all designs)'],
            ['   • No colour repetition - each colour listed once with total quantity'],
            ['   • Sorted by quantity (highest first) for production planning'],
            ['   • Shows which designs use each colour and order count'],
            [''],
            ['2. Colour_Order_Breakdown - Detailed breakdown'],
            ['   • Shows exactly which orders contribute to each colour total'],
            ['   • Useful for tracking and verification'],
            [''],
            ['3. Design_Priority_Summary - Design-level priorities'],
            ['   • Ranked by priority score for production sequence'],
            [''],
            ['4. Order_Priority_Detail - Individual order details'],
            ['   • All orders with calculated priority scores'],
            [''],
            ['🎯 PRIORITY METHODOLOGY:'],
            [f'• Age Weight: {weights["AGE_WEIGHT"]}% - Prioritizes older orders'],
            [f'• Colour Simplicity Weight: {weights["COLOUR_SIMPLICITY_WEIGHT"]}% - Fewer colours = higher priority'],
            [f'• Design Volume Weight: {weights["DESIGN_WEIGHT"]}% - Larger quantities get priority'],
            [f'• Minimum Kgs Threshold: {min_kgs} - Only designs with total kgs >= this value are prioritized'],
            [''],
            ['🎨 COLOUR CONSOLIDATION LOGIC:'],
            ['• If RED is used in Design-A (100kg) and Design-B (50kg)'],
            ['• Output shows: RED = 150kg total (no repetition)'],
            ['• Helps plan exact dye batch quantities needed'],
            ['• Multi-colour orders split proportionally (e.g., "Red,Blue" 100kg = 50kg each)'],
            [''],
            ['📊 USAGE RECOMMENDATIONS:'],
            ['• Use COLOUR_REQUIREMENTS sheet for dye purchasing/batching'],
            ['• Use Design_Priority_Summary for production sequence planning'],
            ['• Check Colour_Order_Breakdown for detailed verification'],
            [''],
            [f'Generated on: {datetime.now().strftime("%Y-%m-%d %H:%M:%S")}']
        ]
        
        instructions_df = pd.DataFrame(instructions_data, columns=['Instructions'])
        instructions_df.to_excel(writer, sheet_name='Instructions', index=False)

# Gradio Interface Functions
def load_excel(file):
    """Load Excel file and return available sheet names"""
    if file is None:
        return gr.Dropdown(choices=[]), "Please upload a file first."
    
    try:
        xls = pd.ExcelFile(file.name)
        return gr.Dropdown(choices=xls.sheet_names, value=xls.sheet_names[0]), "✅ File loaded successfully!"
    except Exception as e:
        return gr.Dropdown(choices=[]), f"❌ Error loading file: {str(e)}"

def validate_weights(age_weight, colour_weight, design_weight):
    """Validate that weights sum to 100%"""
    total = age_weight + colour_weight + design_weight
    if total == 100:
        return "✅ Weights are valid (sum = 100%)"
    else:
        return f"⚠️ Weights sum to {total}%. Please adjust to equal 100%."

def preview_dyeing_data(file, sheet_name):
    """Preview the selected sheet data for dyeing analysis"""
    if file is None or not sheet_name:
        return "Please upload a file and select a sheet first.", pd.DataFrame()
    
    try:
        df = pd.read_excel(file.name, sheet_name=sheet_name)
        
        # Show basic info
        preview_info = f"📊 **Sheet: {sheet_name}**\n"
        preview_info += f"- Rows: {len(df)}\n"
        preview_info += f"- Columns: {len(df.columns)}\n\n"
        
        # Check for required columns
        df_norm = df.copy()
        df_norm.columns = [str(c).strip() for c in df_norm.columns]
        missing = [c for c in REQUIRED_COLS if c not in df_norm.columns]
        
        if missing:
            preview_info += f"❌ **Missing required columns:** {missing}\n\n"
        else:
            preview_info += "✅ **All required columns found!**\n\n"
        
        # Detect date columns
        date_columns = detect_date_columns(df_norm)
        if date_columns:
            preview_info += f"📅 **Date columns detected:** {len(date_columns)} columns\n"
            preview_info += f"   Sample dates: {date_columns[:5]}\n\n"
        else:
            preview_info += "⚠️ **No date columns detected** - will use default prioritization\n\n"
        
        # Show some statistics
        if 'Kgs' in df_norm.columns:
            total_kgs = pd.to_numeric(df_norm['Kgs'], errors='coerce').sum()
            preview_info += f"**Total Kgs:** {total_kgs:,.1f}\n"
        
        if 'DESIGN' in df_norm.columns:
            unique_designs = df_norm['DESIGN'].nunique()
            preview_info += f"**Unique Designs:** {unique_designs}\n"
        
        preview_info += f"\n**Available columns:**\n"
        for i, col in enumerate(df.columns, 1):
            marker = "📅" if col in date_columns else ""
            preview_info += f"{i}. {col} {marker}\n"
        
        # Show first few rows
        preview_df = df.head(5)
        
        return preview_info, preview_df
        
    except Exception as e:
        return f"❌ Error previewing data: {str(e)}", pd.DataFrame()

def process_dyeing_priority(file, sheet_name, age_weight, colour_weight, design_weight, min_kgs):
    """Main processing function for dyeing priorities"""
    
    if file is None:
        return None, None, None, "❌ Please upload a file first."
    
    if not sheet_name:
        return None, None, None, "❌ Please select a sheet."
    
    # Validate weights
    total_weight = age_weight + colour_weight + design_weight
    if total_weight != 100:
        return None, None, None, f"❌ Error: Total weight must equal 100% (currently {total_weight}%)"
    
    try:
        # Load data
        df = pd.read_excel(file.name, sheet_name=sheet_name)
        
        if df.empty:
            return None, None, None, "❌ The selected sheet is empty."
        
        # Prepare weights
        weights = {
            "AGE_WEIGHT": age_weight,
            "COLOUR_SIMPLICITY_WEIGHT": colour_weight,
            "DESIGN_WEIGHT": design_weight
        }
        
        # Compute priorities
        detailed_results, design_summary, colour_totals, colour_breakdown = compute_dyeing_priority(
            df, min_kgs=min_kgs, weights=weights
        )
        
        # Create temporary output file
        output_path = tempfile.NamedTemporaryFile(delete=False, suffix='.xlsx').name
        save_dyeing_results(detailed_results, design_summary, colour_totals, colour_breakdown, output_path, min_kgs, weights)
        
        # Create success message
        total_designs = len(design_summary)
        eligible_designs = sum(design_summary['MeetsMinKgs'])
        total_colours = len(colour_totals)
        top_colours = colour_totals.head(3)['Colour'].tolist() if len(colour_totals) > 0 else []
        
        success_msg = f"✅ Dyeing Priority Analysis Complete!\n"
        success_msg += f"📊 SUMMARY:\n"
        success_msg += f"- Total Designs Analyzed: {total_designs}\n"
        success_msg += f"- Designs Meeting {min_kgs}kg Threshold: {eligible_designs}\n" 
        success_msg += f"- Unique Colours Required: {total_colours}\n"
        if top_colours:
            success_msg += f"- Top 3 Colours by Volume: {', '.join(top_colours)}\n"
        success_msg += f"- Highest Priority Score: {design_summary['PriorityScore'].max():.3f}\n\n"
        success_msg += f"🎨 COLOUR REQUIREMENTS sheet contains consolidated totals!\n"
        success_msg += f"📥 Download complete analysis below"
        
        return output_path, design_summary.head(10), colour_totals.head(15), success_msg
        
    except Exception as e:
        return None, None, None, f"❌ Error processing data: {str(e)}"

# Create Gradio Interface
def create_dyeing_interface():
    with gr.Blocks(title="Dyeing Urgency Priority Calculator", theme=gr.themes.Soft()) as demo:
        
        gr.Markdown("""
        # 🎨 Dyeing Urgency Priority Calculator
        
        Upload your Excel file with dyeing/textile manufacturing data to calculate production priorities based on:
        - **Order Age**: Prioritize older orders first (detects dates from column headers)
        - **Colour Simplicity**: Fewer colours = easier production
        - **Design Volume**: Larger quantities for efficiency
        
        **Expected Columns**: Account, Order #, DESIGN, Labels, Colours, Kgs, Pending
        **Date Detection**: Automatically detects date columns (like 2025-01-08, 13/8, etc.)
        """)
        
        with gr.Row():
            with gr.Column(scale=1):
                gr.Markdown("## 📁 File Upload & Selection")
                
                file_input = gr.File(
                    label="Upload Excel File", 
                    file_types=[".xlsx", ".xls"],
                    type="filepath"
                )
                
                sheet_dropdown = gr.Dropdown(
                    label="Select Sheet", 
                    choices=[], 
                    interactive=True
                )
                
                file_status = gr.Textbox(label="File Status", interactive=False)
                
            with gr.Column(scale=1):
                gr.Markdown("## ⚖️ Priority Weights (must sum to 100%)")
                
                age_weight = gr.Slider(
                    minimum=0, maximum=100, value=50, step=1,
                    label="Age Weight (%)",
                    info="Higher = prioritize older orders more"
                )
                
                colour_weight = gr.Slider(
                    minimum=0, maximum=100, value=30, step=1,
                    label="Colour Simplicity Weight (%)", 
                    info="Higher = prioritize designs with fewer colours"
                )
                
                design_weight = gr.Slider(
                    minimum=0, maximum=100, value=20, step=1,
                    label="Design Volume Weight (%)",
                    info="Higher = prioritize larger quantity designs"
                )
                
                weight_status = gr.Textbox(label="Weight Validation", interactive=False)
                
                min_kgs = gr.Number(
                    label="Minimum Kgs Threshold per Design",
                    value=100,
                    info="Only designs with total kgs >= this value get priority"
                )
        
        with gr.Row():
            preview_btn = gr.Button("👁️ Preview Data", variant="secondary")
            process_btn = gr.Button("🎨 Calculate Dyeing Priorities", variant="primary", size="lg")
        
        with gr.Row():
            with gr.Column():
                gr.Markdown("## 📊 Data Preview") 
                preview_info = gr.Textbox(label="Data Information", lines=10, interactive=False)
                preview_table = gr.Dataframe(label="Sample Data")
        
        with gr.Row():
            with gr.Column():
                gr.Markdown("## 🏆 Priority Results")
                results_info = gr.Textbox(label="Processing Status", interactive=False)
                
            with gr.Column():
                download_file = gr.File(label="📥 Download Complete Analysis")
        
        with gr.Row():
            with gr.Column():
                gr.Markdown("## 📋 Top Design Priorities")
                design_results = gr.Dataframe(label="Design Priority Summary")
                
            with gr.Column():
                gr.Markdown("## 🎨 Colour Requirements (Consolidated)")
                colour_results = gr.Dataframe(
                    label="Total Kgs Required Per Colour",
                    headers=["Colour", "Total Kgs", "Used in Designs", "Orders Count"],
                    interactive=False
                )
        
        # Event handlers
        file_input.change(
            fn=load_excel,
            inputs=[file_input],
            outputs=[sheet_dropdown, file_status]
        )
        
        for weight_input in [age_weight, colour_weight, design_weight]:
            weight_input.change(
                fn=validate_weights,
                inputs=[age_weight, colour_weight, design_weight], 
                outputs=[weight_status]
            )
        
        preview_btn.click(
            fn=preview_dyeing_data,
            inputs=[file_input, sheet_dropdown],
            outputs=[preview_info, preview_table]
        )
        
        process_btn.click(
            fn=process_dyeing_priority,
            inputs=[file_input, sheet_dropdown, age_weight, colour_weight, design_weight, min_kgs],
            outputs=[download_file, design_results, colour_results, results_info]
        )
        
        # Initialize weight validation
        demo.load(
            fn=validate_weights,
            inputs=[age_weight, colour_weight, design_weight],
            outputs=[weight_status]
        )
    
    return demo

# Launch the app
if __name__ == "__main__":
    demo = create_dyeing_interface()
    demo.launch(
        #server_name="0.0.0.0",
        #server_port=7860,
        share=True,
        debug=True
    )