app.py

from flask import Flask, render_template, request, send_file, redirect, url_for
import pandas as pd
import matplotlib.pyplot as plt
import numpy as np
import io
import os

app = Flask(__name__)

# --- Cache updated to hold two test files ---
data_cache = {
    "df1": None,  # Golden Data
    "df2_temp": None,  # Test 1 Data
    "df3_temp": None,  # Test 2 Data
    "limits": {},
    "cols": [],
    "golden_loaded": False,
    "test1_loaded": False,
    "test2_loaded": False,
    "comparison_file": None
}
# ----------------------------------------------


def process_golden_file(golden_file):
    """Load Golden data and extract limits."""
    # Use pandas ExcelFile to read multiple times from the file-like object
    xls = pd.ExcelFile(golden_file)
    limits_df1 = pd.read_excel(xls, nrows=4)
    df1 = pd.read_excel(xls) # Read the entire sheet again for data

    df1 = df1.drop([0, 1, 2, 3])
    df1 = df1.apply(pd.to_numeric, errors="coerce")

    limits_df1 = limits_df1.drop([0])
    ignore_cols = ["SITE_NUM", "PART_ID", "PASSFG", "SOFT_BIN", "T_TIME", "TEST_NUM"]
    # Identify columns to plot/analyze: must contain '_' and not be in ignore_cols
    cols_to_plot = [col for col in limits_df1.columns if "_" in str(col) and col not in ignore_cols]
    
    # Drop ignore columns from limits df to only get limits for relevant parameters
    limits_df1_filtered = limits_df1.drop(columns=ignore_cols, errors='ignore')

    limits = {
        col: {"LL": limits_df1_filtered.iloc[0][col], "UL": limits_df1_filtered.iloc[1][col]}
        for col in limits_df1_filtered.columns
        if pd.notna(limits_df1_filtered.iloc[0][col]) or pd.notna(limits_df1_filtered.iloc[1][col])
    }

    data_cache.update({
        "df1": df1,
        "limits": limits,
        "cols": cols_to_plot,
        "golden_loaded": True
    })
    return "Golden data loaded successfully!"


def process_test_file(test_file):
    """Load Test data."""
    df_test = pd.read_excel(test_file)
    df_test = df_test.drop([0, 1, 2, 3])
    df_test = df_test.apply(pd.to_numeric, errors="coerce")
    return df_test


# --- Comparison function updated for two test files ---
def generate_comparison_excel():
    """Generate comparison Excel (mean, std, min, max for Golden, Test 1, and Test 2)."""
    df1 = data_cache["df1"]
    df2 = data_cache["df2_temp"]
    df3 = data_cache["df3_temp"]
    
    ignore_cols = ["SITE_NUM", "PART_ID", "PASSFG", "SOFT_BIN", "T_TIME", "TEST_NUM"]
    # Use columns identified during Golden file processing
    common_cols = data_cache["cols"] 

    summary = []
    for col in common_cols:
        g_mean, t1_mean, t2_mean = df1[col].mean(), df2.get(col, pd.Series()).mean(), df3.get(col, pd.Series()).mean()
        g_std, t1_std, t2_std = df1[col].std(), df2.get(col, pd.Series()).std(), df3.get(col, pd.Series()).std()
        g_min, t1_min, t2_min = df1[col].min(), df2.get(col, pd.Series()).min(), df3.get(col, pd.Series()).min()
        g_max, t1_max, t2_max = df1[col].max(), df2.get(col, pd.Series()).max(), df3.get(col, pd.Series()).max()

        # Calculate differences relative to Golden mean
        diff1 = t1_mean - g_mean if pd.notna(t1_mean) and pd.notna(g_mean) else np.nan
        diff2 = t2_mean - g_mean if pd.notna(t2_mean) and pd.notna(g_mean) else np.nan
        
        summary.append([
            col, g_mean, t1_mean, t2_mean, diff1, diff2, 
            g_std, t1_std, t2_std, 
            g_min, t1_min, t2_min, 
            g_max, t1_max, t2_max
        ])

    comp_df = pd.DataFrame(summary, columns=[
        "Parameter", "Golden_Mean", "Test1_Mean", "Test2_Mean", "Test1_Mean_Diff", "Test2_Mean_Diff",
        "Golden_Std", "Test1_Std", "Test2_Std", 
        "Golden_Min", "Test1_Min", "Test2_Min", 
        "Golden_Max", "Test1_Max", "Test2_Max"
    ])

    path = "comparison_result.xlsx"
    comp_df.to_excel(path, index=False)
    data_cache["comparison_file"] = path
# -------------------------------------------------------------


# --- Plot function updated for two test files ---
def generate_plot(col):
    """Generate and return a plot comparing Golden vs Test 1 vs Test 2."""
    df1, df2, df3 = data_cache["df1"], data_cache.get("df2_temp"), data_cache.get("df3_temp")
    limits = data_cache["limits"]

    plt.figure(figsize=(10, 6)) # Increased size for better visibility
    
    # Golden Plot
    x1 = np.arange(1, len(df1[col]) + 1)
    plt.plot(x1, df1[col], 'o-', label="Golden", color='blue', alpha=0.7)

    # Test 1 Plot
    if df2 is not None and col in df2.columns:
        x2 = np.arange(1, len(df2[col]) + 1)
        plt.plot(x2, df2[col], 's--', label="Test 1", color='red', alpha=0.7)

    # Test 2 Plot
    if df3 is not None and col in df3.columns:
        x3 = np.arange(1, len(df3[col]) + 1)
        plt.plot(x3, df3[col], 'x:', label="Test 2", color='purple', alpha=0.8)

    # Limits Plot
    if col in limits:
        ll, ul = limits[col].get("LL"), limits[col].get("UL")
        if pd.notna(ll):
            plt.axhline(ll, color='green', linestyle='--', label='LL', linewidth=1)
        if pd.notna(ul):
            plt.axhline(ul, color='orange', linestyle='--', label='UL', linewidth=1)

    plt.title(f"Parameter: {col}")
    plt.xlabel("Part # (sequence)")
    plt.ylabel("Value")
    plt.legend(fontsize='small', loc='best')
    plt.grid(True, linestyle='--', alpha=0.5)
    
    # Set x-ticks based on the largest dataset (assuming Golden is the reference)
    max_len = len(df1[col])
    if max_len > 1:
        plt.xticks(np.arange(1, max_len + 1, max(1, max_len // 10))) # Show max 10 ticks
    
    plt.tight_layout()

    buf = io.BytesIO()
    plt.savefig(buf, format='png', bbox_inches='tight')
    buf.seek(0)
    plt.close()
    return buf
# -------------------------------------------------------------


@app.route("/", methods=["GET", "POST"])
def index():
    if request.method == "POST":
        
        # 1. Upload Golden file
        if not data_cache["golden_loaded"]:
            golden_file = request.files.get("golden_file")
            if not golden_file:
                return render_template("index.html", error="Please upload the Golden file.")
            try:
                process_golden_file(golden_file)
                return redirect(url_for("index"))
            except Exception as e:
                return render_template("index.html", error=f"Error loading Golden file: {e}")

        # 2. Upload Test 1 file
        elif not data_cache["test1_loaded"]:
            test1_file = request.files.get("test1_file")
            if not test1_file:
                return render_template("index.html", error="Please upload the first Test file (Test 1).", **data_cache)
            try:
                df2 = process_test_file(test1_file)
                data_cache["df2_temp"] = df2
                data_cache["test1_loaded"] = True
                return redirect(url_for("index"))
            except Exception as e:
                return render_template("index.html", error=f"Error processing Test 1 file: {e}", **data_cache)

        # 3. Upload Test 2 file
        elif not data_cache["test2_loaded"]:
            test2_file = request.files.get("test2_file")
            if not test2_file:
                return render_template("index.html", error="Please upload the second Test file (Test 2).", **data_cache)
            try:
                df3 = process_test_file(test2_file)
                data_cache["df3_temp"] = df3
                data_cache["test2_loaded"] = True
                
                # Generate comparison and move to plot view after all files are loaded
                generate_comparison_excel() 
                
                return render_template(
                    "plot.html",
                    cols=data_cache["cols"],
                    file_ready=True
                )
            except Exception as e:
                return render_template("index.html", error=f"Error processing Test 2 file: {e}", **data_cache)

    return render_template("index.html", **data_cache)


@app.route("/plot_image/<col>")
def plot_image(col):
    # df2 and df3 are checked inside generate_plot
    if data_cache.get("df1") is None:
        return "No Golden data loaded."
    buf = generate_plot(col)
    return send_file(buf, mimetype="image/png")


@app.route("/download_comparison")
def download_comparison():
    """Download comparison Excel file."""
    path = data_cache.get("comparison_file")
    if path and os.path.exists(path):
        return send_file(path, as_attachment=True, download_name="three_way_comparison_result.xlsx")
    return "No comparison file available. Please upload all data first."


@app.route("/reset_golden")
def reset_golden():
    """Reset all data."""
    global data_cache
    if data_cache.get("comparison_file") and os.path.exists(data_cache["comparison_file"]):
        os.remove(data_cache["comparison_file"])
    
    data_cache = {
        "df1": None, "df2_temp": None, "df3_temp": None, 
        "limits": {}, "cols": [], 
        "golden_loaded": False, "test1_loaded": False, "test2_loaded": False, 
        "comparison_file": None
    }
    return redirect(url_for("index"))


if __name__ == "__main__":
    # Ensure a local directory exists for comparison file (optional but good practice)
    # if not os.path.exists("temp"):
    #     os.makedirs("temp")
    app.run(host="0.0.0.0", port=7860, debug=True)