app.py

from flask import Flask, render_template_string, jsonify, Response
import pandas as pd
import os
from collections import Counter
from io import BytesIO

app = Flask(__name__)

# -----------------------------------------------------
# Load and preprocess data
# -----------------------------------------------------
if os.path.exists("BACKLOG.xlsx"):
    df = pd.read_excel("BACKLOG.xlsx")
else:
    df = pd.DataFrame({
        "SALES_ORDER_NO": [],
        "CUSTOMER NAME": [],
        "CREDIT BLOCK": [],
        "DELIVERY BLOCK": [],
        "PRICING BLOCK": [],
        "SHIP DEBIT BLOCK": [],
        "EXTENDED_RESALE": [],
        "ATP_DATE_RAW": []
    })

df.columns = df.columns.str.strip().str.upper()

SO_COL = "SALES_ORDER_NO"
CUSTOMER_COL = "CUSTOMER NAME"
WORTH_COL = "EXTENDED_RESALE"
BLOCK_COLUMNS = ["CREDIT BLOCK", "DELIVERY BLOCK", "PRICING BLOCK", "SHIP DEBIT BLOCK"]
DATE_COL = "ATP_DATE_RAW" if "ATP_DATE_RAW" in df.columns else None

BLOCK_MAP = {
    "CREDIT BLOCK": "Credit",
    "DELIVERY BLOCK": "Delivery",
    "PRICING BLOCK": "Pricing",
    "SHIP DEBIT BLOCK": "Ship Debit"
}

# -----------------------------------------------------
# Helper functions
# -----------------------------------------------------
def block_summary_df():
    tmp = df.copy()
    exploded = tmp.assign(BLOCK_TYPE=tmp[BLOCK_COLUMNS].apply(
        lambda row: " + ".join([BLOCK_MAP[c] for c in BLOCK_COLUMNS if str(row[c]).upper() == "X"]) or "No Block",
        axis=1))
    exploded = exploded[exploded["BLOCK_TYPE"] != "No Block"]
    agg = exploded.groupby("BLOCK_TYPE", as_index=False)[WORTH_COL].sum().sort_values(WORTH_COL, ascending=False)
    return agg


def actionable_focus_df():
    bs = block_summary_df()
    if bs.empty:
        return pd.DataFrame(), "No actionable focus area data."
    total_val = bs[WORTH_COL].sum()
    bs["SHARE_%"] = (bs[WORTH_COL] / total_val) * 100
    top2 = bs.head(2)
    blocks = ", ".join(top2["BLOCK_TYPE"])
    insight = f"{blocks} contribute {top2['SHARE_%'].sum():.1f}% of total blocked value."
    return top2, insight


def multiple_block_df():
    tmp = df.copy()
    tmp["BLOCK_COUNT"] = tmp[BLOCK_COLUMNS].apply(
        lambda row: sum(str(x).upper() == "X" for x in row), axis=1)
    multiblocks = tmp[tmp["BLOCK_COUNT"] > 1][[SO_COL, CUSTOMER_COL, WORTH_COL, "BLOCK_COUNT"]]
    return multiblocks


def quarter_trends_df():
    if DATE_COL not in df.columns:
        return pd.DataFrame()
    temp = df.copy()
    temp[DATE_COL] = pd.to_datetime(temp[DATE_COL], errors="coerce")
    temp["QUARTER"] = temp[DATE_COL].dt.to_period("Q").astype(str)
    recs = []
    for b in BLOCK_COLUMNS:
        subset = temp[temp[b].astype(str).eq("X")]
        grouped = subset.groupby("QUARTER")[WORTH_COL].sum().reset_index()
        grouped["BLOCK_TYPE"] = BLOCK_MAP[b]
        recs.append(grouped)
    qdf = pd.concat(recs, ignore_index=True)

    # Correct chronological sorting
    qdf = qdf.dropna(subset=["QUARTER"])
    qdf["YEAR"] = qdf["QUARTER"].str[:4].astype(int)
    qdf["QNUM"] = qdf["QUARTER"].str[-1].astype(int)
    qdf = qdf.sort_values(["YEAR", "QNUM"]).drop(columns=["YEAR", "QNUM"])
    return qdf


def get_top_faqs(limit=6):
    """Read logged queries and return top N most common questions."""
    if not os.path.exists("faq_log.csv"):
        return [
            "Which block type had the highest backlog last quarter?",
            "Which customers have the most open blocked orders?",
            "How many orders have multiple active blocks?",
            "What is the trend of delivery blocks across quarters?",
            "Which blocks contribute the most to total backlog value?"
        ]
    with open("faq_log.csv", "r", encoding="utf-8") as f:
        lines = [l.strip() for l in f if l.strip()]
    # keep last 500
    if len(lines) > 500:
        with open("faq_log.csv", "w", encoding="utf-8") as f:
            f.write("\n".join(lines[-500:]))
    common = [q for q, _ in Counter(lines).most_common(limit)]
    return common if common else ["Ask me about blocked orders or quarterly trends!"]

# -----------------------------------------------------
# Chatbot Endpoint
# -----------------------------------------------------
@app.route("/chat_ollama/<query>")
def chat_ollama(query):
    q = query.lower().strip()

    # Log the user query to file
    try:
        with open("faq_log.csv", "a", encoding="utf-8") as f:
            f.write(q + "\n")
    except Exception as e:
        print("FAQ log write error:", e)

    reply = "No relevant insight found. Try asking about customers, block types, or quarterly trends."

    try:
        # --- CUSTOMER-LEVEL LOGIC ---
        if "customer" in q:
            cust_summary = (
                df.groupby(CUSTOMER_COL)[WORTH_COL]
                .sum()
                .sort_values(ascending=False)
                .reset_index()
            )
            top = cust_summary.iloc[0]
            cust_name, value = top[CUSTOMER_COL], top[WORTH_COL]

            block_counts = (
                df[df[CUSTOMER_COL] == cust_name][BLOCK_COLUMNS]
                .apply(lambda row: [BLOCK_MAP[c] for c in BLOCK_COLUMNS if str(row[c]).upper() == "X"], axis=1)
                .explode()
                .value_counts()
            )
            main_block = block_counts.index[0] if not block_counts.empty else "general"
            reply = (
                f"{cust_name} currently holds the highest backlog value (${value:,.0f}) "
                f"with {main_block} as the main issue. Coordinate cross-functionally to resolve {main_block.lower()} constraints."
            )

        # --- MULTIPLE BLOCK ORDERS ---
        elif "multiple" in q or "two blocks" in q or "more than one" in q:
            mb = multiple_block_df()
            if not mb.empty:
                count = mb[SO_COL].nunique()
                reply = (
                    f"{count} orders have multiple active blocks. "
                    f"Prioritize clearance for these to restore smoother flow."
                )
            else:
                reply = "No orders found with multiple active blocks."

        # --- BLOCK SUMMARY LOGIC ---
        elif "block" in q and "quarter" not in q:
            bs = block_summary_df()
            if not bs.empty:
                top = bs.iloc[0]
                reply = (
                    f"{top['BLOCK_TYPE']} block represents the largest backlog exposure "
                    f"(${top[WORTH_COL]:,.0f}). Engage respective teams to address {top['BLOCK_TYPE'].lower()} issues."
                )

        # --- QUARTERLY TREND LOGIC ---
        elif "quarter" in q:
            qdf = quarter_trends_df()
            if not qdf.empty:
                quarters = sorted(
                    qdf["QUARTER"].unique(),
                    key=lambda q: (int(q[:4]), int(q[-1]))
                )
                if quarters:
                    next_quarter = quarters[-1]
                    next_q_df = qdf[qdf["QUARTER"] == next_quarter]
                    if not next_q_df.empty:
                        top_block = next_q_df.sort_values(WORTH_COL, ascending=False).iloc[0]
                        reply = (
                            f"In {next_quarter}, {top_block['BLOCK_TYPE']} block shows the highest exposure "
                            f"(${top_block[WORTH_COL]:,.0f}). Prepare mitigation plans with owners."
                        )
                    else:
                        reply = f"No data found for {next_quarter}."
    except Exception as e:
        reply = f"(Error: {e})"

    return jsonify({"response": reply})

# -----------------------------------------------------
# Data APIs
# -----------------------------------------------------
@app.route("/data/summary")
def data_summary():
    bs = block_summary_df()
    if bs.empty:
        return jsonify({"columns": [], "rows": [], "insight": "No data available."})
    insight = f"{bs.iloc[0]['BLOCK_TYPE']} block has the highest backlog exposure (${bs.iloc[0][WORTH_COL]:,.0f})."
    return jsonify({
        "columns": list(bs.columns),
        "rows": bs.to_dict("records"),
        "insight": insight,
        "download": "/download/summary"
    })


@app.route("/data/focus_area")
def data_focus_area():
    top2, insight = actionable_focus_df()
    return jsonify({
        "columns": list(top2.columns),
        "rows": top2.to_dict("records"),
        "insight": insight,
        "download": "/download/focus"
    })


@app.route("/data/multiple_blocks")
def data_multiple_blocks():
    mb = multiple_block_df()
    insight = (
        f"{mb[SO_COL].nunique()} orders have multiple active blocks. "
        "Prioritize clearance for these to restore smoother flow."
        if not mb.empty else "No multiple block orders found."
    )
    return jsonify({
        "columns": list(mb.columns),
        "rows": mb.to_dict("records"),
        "insight": insight,
        "download": "/download/multiple"
    })


@app.route("/data/quarter_trends")
def data_quarter_trends():
    qdf = quarter_trends_df()
    if qdf.empty:
        return jsonify({"columns": [], "rows": [], "insight": "No quarterly data found."})
    next_quarter = qdf["QUARTER"].max()
    next_q_df = qdf[qdf["QUARTER"] == next_quarter]
    if not next_q_df.empty:
        top_block = next_q_df.sort_values(WORTH_COL, ascending=False).iloc[0]
        insight = (
            f"In {next_quarter}, {top_block['BLOCK_TYPE']} Block held the highest backlog exposure "
            f"(${top_block[WORTH_COL]:,.0f}). Coordinate with teams to expedite clearance."
        )
    else:
        insight = "No valid quarter trend data found."
    return jsonify({
        "columns": list(qdf.columns),
        "rows": qdf.to_dict("records"),
        "insight": insight,
        "download": "/download/quarter"
    })

# -----------------------------------------------------
# HTML Template with adaptive FAQ placeholder
# -----------------------------------------------------
HTML = """
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Blocks Assistant</title>
<style>
body{margin:0;font-family:'Segoe UI',sans-serif;overflow:hidden;}
.container{display:flex;flex-direction:row;height:100vh;width:100vw;}
.dashboard{flex:0 0 70%;height:100vh;border:none;}
.assistant{flex:0 0 30%;display:flex;flex-direction:column;border-left:1px solid #ccc;background:#f9f9f9;}
.header{background:#0A2048;color:white;padding:14px 18px;font-weight:600;font-size:16px;}
.tiles{padding:10px;display:grid;grid-template-columns:1fr 1fr;gap:10px;}
.tile{background:#f4f4f4;color:#0A2048;border-radius:10px;text-align:center;box-shadow:0 1px 4px rgba(0,0,0,.2);padding:10px;font-weight:600;cursor:pointer;transition:0.2s;}
.tile:hover{background:#e2e2e2;}
#faqTile{background:linear-gradient(135deg,#0A2048,#1c398b);color:white;font-style:italic;grid-column:span 2;}
#faqTile:hover{background:linear-gradient(135deg,#1a2b60,#334b9a);}
.chat{flex:1;overflow-y:auto;padding:10px;background:#fafafa;}
.msg-bot{background:white;border:1px solid #ddd;border-radius:10px;padding:10px;margin-bottom:8px;line-height:1.4;}
.table-wrap{overflow-x:auto;overflow-y:auto;max-height:60vh;margin-top:6px;border:1px solid #ddd;border-radius:6px;background:#fff;}
table{border-collapse:collapse;width:max-content;min-width:100%;font-size:13px;table-layout:auto;}
th,td{border:1px solid #ddd;padding:6px 10px;text-align:left;white-space:nowrap;}
th{background:#f1f1f1;position:sticky;top:0;z-index:2;}
.download{display:inline-block;margin-top:8px;color:#0A2048;font-weight:600;text-decoration:none;padding:6px 10px;border:1px solid #0A2048;border-radius:6px;}
.download:hover{background:#0A2048;color:white;}
.searchBox{display:flex;border-top:1px solid #ddd;padding:8px;}
input{flex:1;border:1px solid #ccc;border-radius:6px;padding:8px;}
button{margin-left:6px;background:#0A2048;color:white;border:none;border-radius:6px;padding:8px 14px;cursor:pointer;}
button:hover{background:#122b6b;}
</style>
</head>
<body>
<div class="container">
<iframe title="Midterm Control Tower" src="https://app.powerbi.com/reportEmbed?reportId=c74ac0ec-b0f8-48b9-b7a7-4baab36d9ac5&autoAuth=true&ctid=41f88ecb-ca63-404d-97dd-ab0a169fd138" frameborder="0" allowFullScreen="true" class="dashboard"></iframe>
<div class="assistant">
  <div class="header">Blocks Assistant</div>
  <div class="tiles">
    <div class="tile" onclick="loadTile('/data/summary')">Overall Block Summary</div>
    <div class="tile" onclick="loadTile('/data/focus_area')">Actionable Focus Area</div>
    <div class="tile" onclick="loadTile('/data/multiple_blocks')">Multiple Block Orders</div>
    <div class="tile" onclick="loadTile('/data/quarter_trends')">Quarter-wise Trends</div>
    <div class="tile" id="faqTile" onclick="autoAskFAQ()">💡 FAQ: <span id="faqText"></span></div>
  </div>
  <div id="chat" class="chat">
    <div class="msg-bot"><b>Hello!</b><br>I'm your Blocks Assistant. How can I help you today?</div>
  </div>
  <div class="searchBox">
    <input id="searchInput" placeholder="Ask about blocks or customers..." onkeypress="if(event.key==='Enter')sendChat()">
    <button onclick="sendChat()">Search</button>
  </div>
</div>
</div>
<script>
function markdownToHTML(t){return t.replace(/\\*\\*(.*?)\\*\\*/g,'<strong>$1</strong>').replace(/\\n/g,'<br>');}
async function loadTile(url){
 const res=await fetch(url);const j=await res.json();
 const chat=document.getElementById('chat');
 const section=document.createElement('div');section.className='msg-bot';section.style.borderTop='2px solid #eee';section.style.marginTop='10px';
 let html=`<p>${j.insight||''}</p>`;
 if(j.columns?.length){
   html+=`<div class='table-wrap'><table><tr>${j.columns.map(c=>`<th>${c}</th>`).join('')}</tr>`;
   html+=j.rows.map(r=>`<tr>${j.columns.map(c=>`<td>${r[c]??''}</td>`).join('')}</tr>`).join('')+'</table></div>';
   if(j.download){html+=`<a class='download' href='${window.location.origin + j.download}' target='_blank'>📥 Download CSV</a>`;}
 }
 section.innerHTML=html;chat.appendChild(section);chat.scrollTop=chat.scrollHeight;
}
async function sendChat(){
 const q=document.getElementById('searchInput').value.trim();if(!q)return;
 const chat=document.getElementById('chat');
 const u=document.createElement('div');u.className='msg-bot';u.style.background='#0A2048';u.style.color='white';u.textContent=q;chat.appendChild(u);
 document.getElementById('searchInput').value='';
 const thinking=document.createElement('div');thinking.className='msg-bot';thinking.textContent='Thinking...';chat.appendChild(thinking);chat.scrollTop=chat.scrollHeight;
 const res=await fetch('/chat_ollama/'+encodeURIComponent(q));const j=await res.json();thinking.remove();
 const b=document.createElement('div');b.className='msg-bot';b.innerHTML=markdownToHTML(j.response);chat.appendChild(b);chat.scrollTop=chat.scrollHeight;
}
const faqs=[PLACEHOLDER_FAQS];
let faqIndex=0;
function rotateFAQ(){const faqText=document.getElementById('faqText');if(!faqText)return;faqText.textContent=faqs[faqIndex];faqIndex=(faqIndex+1)%faqs.length;}
setInterval(rotateFAQ,5000);rotateFAQ();
function autoAskFAQ(){const q=document.getElementById('faqText').textContent;document.getElementById('searchInput').value=q;sendChat();}
</script>
</body>
</html>
"""

# -----------------------------------------------------
# Dynamic injection of top FAQs
# -----------------------------------------------------
@app.route("/")
def home():
    faqs = get_top_faqs()
    faq_js_array = "[" + ",".join([f'"{f}"' for f in faqs]) + "]"
    html_with_faqs = HTML.replace("const faqs=[PLACEHOLDER_FAQS];", f"const faqs={faq_js_array};")
    return render_template_string(html_with_faqs)

# -----------------------------------------------------
# CSV Download Endpoints
# -----------------------------------------------------
def df_to_csv_response(dataframe, filename):
    csv_data = dataframe.to_csv(index=False)
    return Response(csv_data, mimetype="text/csv", headers={"Content-Disposition": f"attachment;filename={filename}.csv"})

@app.route("/download/summary")
def download_summary():
    bs = block_summary_df()
    if bs.empty:
        return jsonify({"error": "No data"}), 404
    return df_to_csv_response(bs, "block_summary")

@app.route("/download/focus")
def download_focus():
    top2, _ = actionable_focus_df()
    if top2.empty:
        return jsonify({"error": "No data"}), 404
    return df_to_csv_response(top2, "actionable_focus")

@app.route("/download/multiple")
def download_multiple():
    mb = multiple_block_df()
    if mb.empty:
        return jsonify({"error": "No data"}), 404
    return df_to_csv_response(mb, "multiple_blocks")

@app.route("/download/quarter")
def download_quarter():
    qdf = quarter_trends_df()
    if qdf.empty:
        return jsonify({"error": "No data"}), 404
    return df_to_csv_response(qdf, "quarter_trends")

if __name__ == "__main__":
    app.run(host="0.0.0.0", port=7860)