app.py

# === Imports ===
import os
import re
import gradio as gr
import openai
from datetime import datetime
from bs4 import BeautifulSoup

# --- API Keys ---
openai_api_key = os.environ.get("OPENAI_API_KEY")
if not openai_api_key:
    raise ValueError("OPENAI_API_KEY environment variable is not set.")

client = openai.OpenAI(api_key=openai_api_key)

# --- Exadata Specs ---
exadata_specs = {
    "X7": {"Quarter Rack": {"max_iops": 350000, "max_throughput": 25}, "Half Rack": {"max_iops": 700000, "max_throughput": 50}, "Full Rack": {"max_iops": 1400000, "max_throughput": 100}},
    "X8": {"Quarter Rack": {"max_iops": 380000, "max_throughput": 28}, "Half Rack": {"max_iops": 760000, "max_throughput": 56}, "Full Rack": {"max_iops": 1520000, "max_throughput": 112}},
    "X9": {"Quarter Rack": {"max_iops": 450000, "max_throughput": 30}, "Half Rack": {"max_iops": 900000, "max_throughput": 60}, "Full Rack": {"max_iops": 1800000, "max_throughput": 120}},
    "X10": {"Quarter Rack": {"max_iops": 500000, "max_throughput": 35}, "Half Rack": {"max_iops": 1000000, "max_throughput": 70}, "Full Rack": {"max_iops": 2000000, "max_throughput": 140}},
    "X11M": {"Quarter Rack": {"max_iops": 600000, "max_throughput": 40}, "Half Rack": {"max_iops": 1200000, "max_throughput": 80}, "Full Rack": {"max_iops": 2400000, "max_throughput": 160}},
}

# --- Supported LLM Models ---
supported_llms = {
    "gpt-3.5-turbo": "Fastest / lowest cost (basic analysis), Standard AWR Healthcheck",
    "gpt-4-turbo": "Balanced (recommended default), Production Performance Analysis",
    "gpt-4o": "Deep + technical (best), Deep Dive, Exadata, RAC Stability, Risk Audit",
}

# --- Utils ---
def clean_awr_content(content):
    if "<html" in content.lower():
        soup = BeautifulSoup(content, "html.parser")
        return soup.get_text()
    return content

# === AGENTS ===

class CriticalAnalyzerAgent:
    def analyze(self, content, performance_test_mode, exadata_model, rack_size, llm_model):
        cleaned_content = clean_awr_content(content)
        if len(cleaned_content) > 128000:
            cleaned_content = cleaned_content[:128000] + "\n\n[TRUNCATED]..."

        prompt = f"""
You are an expert Oracle DBA performance analyst specialized in AWR + Exadata.

Please perform advanced analysis on the following report:

======== AWR REPORT START ========
{cleaned_content}
======== AWR REPORT END ========

Required Output:
- Performance Summary (with metric values)
- Detailed Bottlenecks + Risks (quantified)
- Forecast + Predictions
- Monitoring Recommendations
- Exadata Statistics (IO, Flash Cache, Smart Scan)
- Recommended Next Steps to Bridge Gaps
"""

        if performance_test_mode and exadata_model and rack_size:
            specs = exadata_specs.get(exadata_model, {}).get(rack_size, {})
            if specs:
                prompt += f"""

This was a PERFORMANCE TEST on Oracle Exadata {exadata_model} {rack_size}.
Theoretical Max:
- IOPS: {specs['max_iops']}
- Throughput: {specs['max_throughput']} GB/s

Compare observed vs theoretical. Recommend actions to close the performance gap.
"""

        response = client.chat.completions.create(
            model=llm_model,
            messages=[
                {"role": "system", "content": "You are an expert Oracle DBA."},
                {"role": "user", "content": prompt}
            ]
        )

        return response.choices[0].message.content.strip()

class HealthAgent:
    def check_health(self, content):
        cleaned_content = clean_awr_content(content)
        if len(cleaned_content) > 128000:
            cleaned_content = cleaned_content[:128000] + "\n\n[TRUNCATED]..."

        prompt = f"""
You are the Oracle AWR Health Analysis Agent.

Your primary responsibility is to detect and report ANY and ALL database health risks, alerts, warnings, or failures in the AWR report.

You MUST:
- Identify all issues marked as CRITICAL, WARNING, ALERT, FAILED, OFFLINE, CONFINED, DROPPED, or ERROR.
- Never omit or generalize. If something appears important, call it out.
- Classify each issue into: 🚨 CRITICAL / ⚠️ WARNING / ✅ INFO
- For CRITICAL and WARNING, provide suggested actions or considerations.
- Always confirm at the end if no CRITICAL or WARNING issues were found.

Special Attention Areas:
- Flash Cache or Flash Disk Failures (including confined, offline, failed or dropped disks)
- I/O Subsystem stalls or device errors
- ASM Disk or Grid Disk issues
- Smart Scan failures or skipped Smart Scans
- Redo Log issues (e.g. log file sync slow)
- RAC Interconnect problems (e.g. gc buffer busy waits, ORA-12170)

You are NOT allowed to generalize or omit any issue, no matter how small.

Specifically analyze Exadata Alerts Summary and Exadata Alerts Detail

Example Output:
🚨 CRITICAL
- [issue]
⚠️ WARNING
- [issue]
✅ INFO
- No other warnings or critical issues detected.

AWR CONTENT:
{cleaned_content}
"""

        response = client.chat.completions.create(
            model="gpt-4-turbo",
            messages=[
                {"role": "system", "content": "You are the strict Oracle AWR Health Analysis Agent."},
                {"role": "user", "content": prompt}
            ]
        )

        return response.choices[0].message.content.strip()


class RaterAgent:
    def rate(self, content, llm_model):
        prompt = f"Rate the following analysis from 1-5 stars and explain:\n\n{content}"
        response = client.chat.completions.create(
            model=llm_model,
            messages=[{"role": "user", "content": prompt}]
        )
        return response.choices[0].message.content.strip()

# === Main Process ===
def process_awr(awr_text, threshold, performance_test_mode, exadata_model, rack_size, llm_model):
    analyzer = CriticalAnalyzerAgent()
    health = HealthAgent()
    rater = RaterAgent()

    if not awr_text.strip():
        return "No AWR text provided", "", "", ""

    analysis = analyzer.analyze(awr_text, performance_test_mode, exadata_model, rack_size, llm_model)
    health_status = health.check_health(awr_text, llm_model)
    rating_text = rater.rate(analysis, llm_model)

    stars = 0
    match = re.search(r"(\d+)", rating_text)
    if match:
        stars = int(match.group(1))

    retry_status = "✅ Accepted"

    if stars < threshold:
        analysis_retry = analyzer.analyze(awr_text, performance_test_mode, exadata_model, rack_size, llm_model)
        rating_text_retry = rater.rate(analysis_retry, llm_model)
        retry_status = "✅ Retry Occurred"
        analysis = analysis_retry
        rating_text = rating_text_retry

    return analysis, health_status, rating_text, retry_status

# === Gradio UI ===
with gr.Blocks() as demo:
    gr.Markdown("# 🧠 Multi-Agent Oracle AWR Analyzer (Production Edition)")

    awr_text = gr.Textbox(label="Paste AWR Report", lines=30)
    threshold = gr.Slider(0, 5, value=3, step=1, label="Correctness Threshold (Stars)")
    performance_test_mode = gr.Checkbox(label="Performance Test Mode")
    exadata_model = gr.Dropdown(choices=list(exadata_specs.keys()), label="Exadata Model", visible=False)
    rack_size = gr.Dropdown(choices=["Quarter Rack", "Half Rack", "Full Rack"], label="Rack Size", visible=False)
    llm_selector = gr.Dropdown(choices=list(supported_llms.keys()), value="gpt-4-turbo", label="LLM Model")

    def toggle_visibility(mode):
        return gr.update(visible=mode), gr.update(visible=mode)

    performance_test_mode.change(toggle_visibility, inputs=performance_test_mode, outputs=[exadata_model, rack_size])

    analyze_btn = gr.Button("Analyze AWR Report")
    output = gr.Textbox(label="AWR Analysis", lines=20)
    health = gr.Textbox(label="Health Agent Findings", lines=10)
    rating = gr.Textbox(label="Rater", lines=3)
    retry_status = gr.Textbox(label="Retry Status")

    analyze_btn.click(process_awr, 
                      inputs=[awr_text, threshold, performance_test_mode, exadata_model, rack_size, llm_selector], 
                      outputs=[output, health, rating, retry_status])

demo.launch(debug=True)