app.py

# --- PYTHON 3.13  COMPATIBILITY MONKEY-PATCH ---
import sys
import types

if 'audioop' not in sys.modules:
    mock_audioop = types.ModuleType('audioop')
    mock_audioop.error = Exception
    mock_audioop.getsample = lambda data, width, index: 0 
    sys.modules['audioop'] = mock_audioop
# ----------------------------------------------

import os
import sqlite3
import json
import requests
import pandas as pd
from datetime import datetime
from flask import Flask, request, jsonify, render_template_string, redirect, session, url_for
from groq import Groq 

app = Flask(__name__)
app.secret_key = "strides_pharma_domain_secret_encryption_key_2026"

app.config.update(
    SESSION_COOKIE_SECURE=True,
    SESSION_COOKIE_SAMESITE='None',
)

DATA_DIR = "/data" if os.path.exists("/data") else "."
DB_NAME = os.path.join(DATA_DIR, "strides_architecture.db")

def init_db():
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS project_tasks (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
            phase TEXT, task_name TEXT, owner TEXT, timeline TEXT, priority TEXT
        )
    ''')
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS chat_sessions (
            session_id TEXT PRIMARY KEY, user_owner TEXT, title TEXT, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS chat_history (
            id INTEGER PRIMARY KEY AUTOINCREMENT, session_id TEXT, role TEXT, content TEXT, timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    cursor.execute("SELECT COUNT(*) FROM project_tasks")
    if cursor.fetchone()[0] == 0:
        cursor.executemany('INSERT INTO project_tasks (phase, task_name, owner, timeline, priority) VALUES (?,?,?,?,?)', [
            ('BRD-Scope', 'Automated Audit Trail Log Consolidation', 'A. Sharma', 'Q3-2026', 'Critical'),
            ('GxP-Compliance', '21 CFR Part 11 Electronic Signature Verification', 'R. Verma', 'Q3-2026', 'Critical')
        ])
    conn.commit()
    conn.close()

init_db()

USER_CREDENTIALS = {"admin": "strides", "compliance_tester": "AuditPass123"}
GROQ_MODELS = ["llama-3.1-8b-instant", "llama-3.1-70b-versatile"]
OPENROUTER_MODELS = ["nvidia/nemotron-3-ultra-550b-a55b:free"]

def get_current_user():
    return session.get("username")

@app.route("/login", methods=["POST"])
def login():
    username = request.form.get("username")
    password = request.form.get("password")
    if USER_CREDENTIALS.get(username) == password:
        session["username"] = username
        return redirect(url_for("index"))
    return render_template_string(HTML_TEMPLATE, error="Invalid credentials profile verification fail.", show_login=True)

@app.route("/logout")
def logout():
    session.clear()
    return redirect(url_for("index"))

@app.route("/api/sessions", methods=["GET"])
def api_get_sessions():
    user = get_current_user()
    if not user: return jsonify({"error": "Unauthorized"}), 401
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()
    cursor.execute("SELECT session_id, title FROM chat_sessions WHERE user_owner = ? ORDER BY updated_at DESC", (user,))
    rows = cursor.fetchall()
    conn.close()
    if not rows:
        sid = f"session_{user}_initial"
        conn = sqlite3.connect(DB_NAME)
        c = conn.cursor()
        c.execute("INSERT OR IGNORE INTO chat_sessions (session_id, user_owner, title) VALUES (?,?,?)", (sid, user, "Initial Session Thread"))
        conn.commit()
        conn.close()
        rows = [(sid, "Initial Session Thread")]
    return jsonify([{"id": r[0], "title": r[1]} for r in rows])

@app.route("/api/sessions/new", methods=["POST"])
def api_new_session():
    user = get_current_user()
    if not user: return jsonify({"error": "Unauthorized"}), 401
    new_id = f"session_{user}_{int(datetime.now().timestamp())}"
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()
    cursor.execute("INSERT INTO chat_sessions (session_id, user_owner, title) VALUES (?, ?, ?)", (new_id, user, "New Chat Session"))
    conn.commit()
    conn.close()
    return jsonify({"status": "success", "id": new_id})

@app.route("/api/history/<session_id>", methods=["GET"])
def api_get_history(session_id):
    if not get_current_user(): return jsonify({"error": "Unauthorized"}), 401
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()
    cursor.execute("SELECT role, content FROM chat_history WHERE session_id = ? ORDER BY timestamp ASC", (session_id,))
    rows = cursor.fetchall()
    conn.close()
    return jsonify([{"role": r[0], "content": r[1]} for r in rows])

@app.route("/api/chat", methods=["POST"])
def api_chat():
    user = get_current_user()
    if not user: return jsonify({"error": "Unauthorized"}), 401
    data = request.json or {}
    sid = data.get("session_id")
    msg = data.get("message", "").strip()
    provider = data.get("provider")
    api_key = data.get("api_key")
    model = data.get("model")

    if not msg or not api_key:
        return jsonify({"error": "Missing message or API key"}), 400

    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()
    cursor.execute("INSERT INTO chat_history (session_id, role, content) VALUES (?, 'user', ?)", (sid, msg))
    
    cursor.execute("SELECT COUNT(*) FROM chat_history WHERE session_id = ?", (sid,))
    if cursor.fetchone()[0] <= 2:
        cursor.execute("UPDATE chat_sessions SET title = ? WHERE session_id = ?", (msg[:25] + "...", sid))
    conn.commit()

    cursor.execute("SELECT role, content FROM chat_history WHERE session_id = ? ORDER BY timestamp ASC", (sid,))
    history = [{"role": r[0], "content": r[1]} for r in cursor.fetchall()]
    conn.close()

    sys_prompt = "You are a Pharma Business Analyst for Strides. Output specifications and save entries if data schema formats matches."
    messages = [{"role": "system", "content": sys_prompt}] + history

    try:
        if provider == "Groq":
            client = Groq(api_key=api_key)
            resp = client.chat.completions.create(model=model, messages=messages, temperature=0.0)
            ai_out = resp.choices[0].message.content.strip()
        else:
            headers = {"Authorization": f"Bearer {api_key}", "Content-Type": "application/json"}
            payload = {"model": model, "messages": messages, "temperature": 0.0}
            r = requests.post("https://openrouter.ai/api/v1/chat/completions", headers=headers, json=payload)
            ai_out = r.json()['choices'][0]['message']['content'].strip()

        if "||JSON_DATA:" in ai_out:
            try:
                payload_str = ai_out.split("||JSON_DATA:")[1].split("||")[0].strip()
                p = json.loads(payload_str)
                conn = sqlite3.connect(DB_NAME)
                conn.cursor().execute("INSERT INTO project_tasks (phase, task_name, owner, timeline, priority) VALUES (?,?,?,?,?)",
                             (p.get('phase'), p.get('task_name'), p.get('owner'), p.get('timeline'), p.get('priority')))
                conn.commit()
                conn.close()
                ai_out += "\n\n⚙️ [SYSTEM LOGGED SUCCESS]"
            except: pass

        conn = sqlite3.connect(DB_NAME)
        conn.cursor().execute("INSERT INTO chat_history (session_id, role, content) VALUES (?, 'assistant', ?)", (sid, ai_out))
        conn.commit()
        conn.close()
        return jsonify({"reply": ai_out})
    except Exception as e:
        return jsonify({"reply": f"❌ Gateway Execution Error: {str(e)}"})

@app.route("/api/query", methods=["POST"])
def api_query():
    if not get_current_user(): return jsonify({"error": "Unauthorized"}), 401
    data = request.json or {}
    q = data.get("query", "")
    api_key = data.get("api_key")
    provider = data.get("provider")
    model = data.get("model")
    
    sys_prompt = "You are a SQLite assistant. Return ONLY a valid executable raw SQLite query targeting table 'project_tasks'."
    try:
        if provider == "Groq":
            client = Groq(api_key=api_key)
            resp = client.chat.completions.create(model=model, messages=[{"role":"system","content":sys_prompt},{"role":"user","content":q}], temperature=0.0)
            sql = resp.choices[0].message.content.strip()
        else:
            r = requests.post("https://openrouter.ai/api/v1/chat/completions", headers={"Authorization": f"Bearer {api_key}"}, json={"model": model, "messages": [{"role":"system","content":sys_prompt},{"role":"user","content":q}]})
            sql = r.json()['choices'][0]['message']['content'].strip()
        
        sql = sql.replace("```sql", "").replace("```", "").replace("`", "").strip()
        conn = sqlite3.connect(DB_NAME)
        box_df = pd.read_sql_query(sql, conn)
        conn.close()
        return jsonify({"sql": sql, "data": box_df.to_dict(orient="records")})
    except Exception as e:
        return jsonify({"error": str(e)})

HTML_TEMPLATE = """
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Strides Pharma AI</title>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css">
    <style>
        body { background: #f4f6f9; height: 100vh; display: flex; flex-direction: column; margin: 0; }
        .login-box { max-width: 400px; margin: 100px auto; background: white; padding: 30px; border-radius: 8px; box-shadow: 0 4px 15px rgba(0,0,0,0.1); }
        .app-container { display: flex; flex: 1; overflow: hidden; height: 100vh; }
        .sidebar { width: 300px; background: #2c3e50; color: white; padding: 20px; display: flex; flex-direction: column; justify-content: space-between; }
        .main-content { flex: 1; padding: 20px; display: flex; flex-direction: column; overflow-y: auto; }
        .chat-box { flex: 1; border: 1px solid #ddd; background: white; border-radius: 8px; padding: 15px; overflow-y: auto; margin-bottom: 15px; min-height: 350px; max-height: 500px;}
        .msg { margin-bottom: 12px; padding: 10px; border-radius: 6px; }
        .msg.user { background: #e1f5fe; text-align: right; margin-left: 50px; }
        .msg.assistant { background: #f1f8e9; text-align: left; margin-right: 50px; }
    </style>
</head>
<body>

{% if show_login %}
<div class="container">
    <div class="login-box">
        <h4 class="text-center mb-4">💊 Strides Security Gate</h4>
        <form action="/login" method="POST">
            <div class="mb-3"><label>Username</label><input name="username" class="form-control" required autocomplete="off"></div>
            <div class="mb-3"><label>Password</label><input name="password" type="password" class="form-control" required></div>
            <button type="submit" class="btn btn-primary w-100">Secure Access Login</button>
        </form>
        {% if error %}<div class="text-danger mt-2 text-center">{{ error }}</div>{% endif %}
    </div>
</div>
{% else %}
<div class="app-container">
    <div class="sidebar">
        <div>
            <h5>🕒 Session Explorer</h5>
            <select id="sessionSelect" class="form-select mb-3" onchange="loadHistory()"></select>
            <button onclick="newSession()" class="btn btn-outline-light btn-sm w-100 mb-2">➕ New Chat Thread</button>
            <hr>
            <h5>🔑 Gateway Options</h5>
            <label class="small">API Provider</label>
            <select id="provider" class="form-select mb-2" onchange="updateModels()">
                <option value="Groq">Groq</option>
                <option value="OpenRouter">OpenRouter</option>
            </select>
            <label class="small">API Key</label>
            <input id="apiKey" type="password" class="form-control mb-2" placeholder="sk-...">
            <label class="small">Target Model</label>
            <select id="model" class="form-select"></select>
        </div>
        <div>
            <span class="d-block mb-2 small text-light">Logged in as: <strong>{{ username }}</strong></span>
            <a href="/logout" class="btn btn-danger btn-sm w-100">Log Out</a>
        </div>
    </div>
    
    <div class="main-content">
        <ul class="nav nav-tabs mb-3">
            <li class="nav-item"><a class="nav-link active" data-bs-toggle="tab" href="#chatTab">🤖 Requirement Chat Workspace</a></li>
            <li class="nav-item"><a class="nav-link" data-bs-toggle="tab" href="#queryTab">🔎 Inventory SQL Explorer</a></li>
        </ul>
        
        <div class="tab-content flex-grow-1">
            <div id="chatTab" class="tab-pane fade show active">
                <div id="chatBox" class="chat-box"></div>
                <div class="input-group">
                    <input id="chatInput" class="form-control" placeholder="Type data log requests here...">
                    <button onclick="sendChat()" class="btn btn-primary">Send</button>
                </div>
            </div>
            
            <div id="queryTab" class="tab-pane fade">
                <div class="input-group mb-3">
                    <input id="queryInput" class="form-control" placeholder="Search inventory records using natural language...">
                    <button onclick="runQuery()" class="btn btn-secondary">Run Subsystem Evaluation</button>
                </div>
                <div id="sqlStatus" class="fw-bold text-success mb-2"></div>
                <div class="table-responsive"><table id="dataTable" class="table table-striped table-bordered"></table></div>
            </div>
        </div>
    </div>
</div>

<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
<script>
    const GROQ_MODELS = ["llama-3.1-8b-instant", "llama-3.1-70b-versatile"];
    const OR_MODELS = ["nvidia/nemotron-3-ultra-550b-a55b:free"];

    function init() {
        updateModels();
        loadSessions();
    }

    function updateModels() {
        const p = document.getElementById("provider").value;
        const mSelect = document.getElementById("model");
        if(!mSelect) return;
        mSelect.innerHTML = "";
        const list = (p === "Groq") ? GROQ_MODELS : OR_MODELS;
        list.forEach(m => { mSelect.innerHTML += `<option value="${m}">${m}</option>`; });
    }

    async function loadSessions() {
        const select = document.getElementById("sessionSelect");
        if(!select) return;
        const r = await fetch("/api/sessions");
        if (!r.ok) return;
        const data = await r.json();
        const currentSelected = select.value;
        select.innerHTML = "";
        data.forEach(s => { select.innerHTML += `<option value="${s.id}">${s.title}</option>`; });
        if(currentSelected && data.find(s => s.id === currentSelected)) {
            select.value = currentSelected;
        } else if (data.length > 0) {
            select.value = data[0].id;
        }
        loadHistory();
    }

    async function newSession() {
        const r = await fetch("/api/sessions/new", {method:"POST"});
        if (r.ok) { 
            const res = await r.json();
            await loadSessions();
            document.getElementById("sessionSelect").value = res.id;
            loadHistory();
        }
    }

    async function loadHistory() {
        const select = document.getElementById("sessionSelect");
        if(!select) return;
        const sid = select.value;
        if (!sid) return;
        const r = await fetch(`/api/history/${sid}`);
        const data = await r.json();
        const box = document.getElementById("chatBox");
        box.innerHTML = "";
        data.forEach(m => { box.innerHTML += `<div class="msg ${m.role}"><strong>${m.role}:</strong> ${m.content}</div>`; });
        box.scrollTop = box.scrollHeight;
    }

    async function sendChat() {
        const inp = document.getElementById("chatInput");
        const txt = inp.value.trim();
        if(!txt) return;
        const sid = document.getElementById("sessionSelect").value;
        const box = document.getElementById("chatBox");
        
        box.innerHTML += `<div class="msg user"><strong>user:</strong> ${txt}</div>`;
        inp.value = "";
        box.scrollTop = box.scrollHeight;

        const payload = {
            session_id: sid, message: txt,
            provider: document.getElementById("provider").value,
            api_key: document.getElementById("apiKey").value,
            model: document.getElementById("model").value
        };

        const r = await fetch("/api/chat", {method:"POST", headers:{"Content-Type":"application/json"}, body:JSON.stringify(payload)});
        const res = await r.json();
        box.innerHTML += `<div class="msg assistant"><strong>assistant:</strong> ${res.reply}</div>`;
        box.scrollTop = box.scrollHeight;
        
        const select = document.getElementById("sessionSelect");
        const activeOpt = select.options[select.selectedIndex];
        if (activeOpt && activeOpt.innerText.includes("New Chat Session")) {
             loadSessions();
        }
    }

    async function runQuery() {
        const inp = document.getElementById("queryInput").value;
        const payload = {
            query: inp,
            api_key: document.getElementById("apiKey").value,
            provider: document.getElementById("provider").value,
            model: document.getElementById("model").value
        };
        const r = await fetch("/api/query", {method:"POST", headers:{"Content-Type":"application/json"}, body:JSON.stringify(payload)});
        const res = await r.json();
        if(res.error) {
            document.getElementById("sqlStatus").innerText = `Error: ${res.error}`;
            return;
        }
        document.getElementById("sqlStatus").innerText = `Executed Query: ${res.sql}`;
        const table = document.getElementById("dataTable");
        table.innerHTML = "";
        if(res.data.length > 0) {
            let headers = Object.keys(res.data[0]);
            let tr = "<tr>";
            headers.forEach(h => tr += `<th>${h}</th>`);
            tr += "</tr>";
            res.data.forEach(row => {
                tr += "<tr>";
                headers.forEach(h => tr += `<td>${row[h]}</td>`);
                tr += "</tr>";
            });
            table.innerHTML = tr;
        } else {
            table.innerHTML = "<tr><td>No records matched or returned.</td></tr>";
        }
    }

    window.onload = init;
</script>
{% endif %}
</body>
</html>
"""

@app.route("/")
def index():
    user = get_current_user()
    if not user:
        return render_template_string(HTML_TEMPLATE, show_login=True)
    return render_template_string(HTML_TEMPLATE, show_login=False, username=user)

# Ensure the bottom of app.py looks exactly like this:
if __name__ == "__main__":
    app.run(host="0.0.0.0", port=7860)