app.py

# === Imports ===
import os
import oci
import re
import gradio as gr
import openai
from datetime import datetime
from bs4 import BeautifulSoup
from reportlab.platypus import SimpleDocTemplate, Paragraph, Spacer
from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle
from reportlab.lib.pagesizes import letter
from reportlab.lib.enums import TA_CENTER
from reportlab.lib import colors
import tempfile

# --- 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)

openrouter_key = os.environ.get("OPENROUTER")
openrouter = openai.OpenAI(api_key=openrouter_key, base_url="https://openrouter.ai/api/v1")

# --- OCI Object Storage: Explicit Fixed Configuration ---
oci_config = {
    "user": os.environ.get("OCI_USER"),
    "tenancy": os.environ.get("OCI_TENANCY"),
    "fingerprint": os.environ.get("OCI_FINGERPRINT"),
    "region": os.environ.get("OCI_REGION"),
    "key_content": os.environ.get("OCI_PRIVATE_KEY")
}
namespace = os.environ.get("OCI_NAMESPACE")
bucket_name = os.environ.get("OCI_BUCKET_NAME")

try:
    object_storage = oci.object_storage.ObjectStorageClient(oci_config)
except Exception as e:
    print("Failed to initialize OCI Object Storage client:", e)

# --- 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 - General AWR Healthcheck",
    "gpt-4-turbo": "Balanced - Production Performance Analysis",
    "gpt-4o": "Deepest Analysis - Exadata, RAC, Smart Scan, Critical Issues",
    "gpt-4.1": "Great for quick coding and analysis",
}

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

def awr_file_to_text(file_obj):
    if not file_obj:
        return ""
    filename = file_obj.name if hasattr(file_obj, "name") else str(file_obj)
    try:
        content = file_obj.read() if hasattr(file_obj, "read") else open(file_obj, "rb").read()
    except Exception:
        with open(file_obj, "rb") as f:
            content = f.read()
    try:
        text = content.decode()
    except Exception:
        text = content.decode("latin-1")
    return clean_awr_content(text)

def upload_awr_file(file_obj):
    filename = os.path.basename(file_obj)
    with open(file_obj, "rb") as f:
        content = f.read()
    object_storage.put_object(namespace, bucket_name, filename, content)
    return f"\u2705 Uploaded {filename}"

def list_awr_files():
    try:
        objects = object_storage.list_objects(namespace, bucket_name)
        return [obj.name for obj in objects.data.objects if obj.name.endswith(".html") or obj.name.endswith(".txt")]
    except Exception as e:
        return [f"Error listing objects: {str(e)}"]

def get_awr_file_text(filename):
    try:
        response = object_storage.get_object(namespace, bucket_name, filename)
        raw = response.data.content.decode()
        return clean_awr_content(raw)
    except Exception as e:
        return f"Error loading file: {str(e)}"

def generate_pdf(analysis_text, health_text, rating_text, retry_status_text):
    temp_file = tempfile.NamedTemporaryFile(delete=False, suffix=".pdf")
    pdf_path = temp_file.name
    doc = SimpleDocTemplate(pdf_path, pagesize=letter)
    styles = getSampleStyleSheet()
    elements = []
    header_style = ParagraphStyle(name="HeaderStyle", fontSize=16, alignment=TA_CENTER, textColor=colors.darkblue, spaceAfter=14)
    section_style = ParagraphStyle(name="SectionHeader", fontSize=14, textColor=colors.darkred, spaceAfter=8)
    body_style = ParagraphStyle(name="BodyStyle", fontSize=10, leading=14, spaceAfter=10)
    elements.append(Paragraph("Oracle AWR Analyzer Report", header_style))
    elements.append(Spacer(1, 12))
    sections = [
        ("AWR Analysis", analysis_text),
        ("Health Agent Findings", health_text),
        ("Rater Output", rating_text),
        ("Retry Status", retry_status_text)
    ]
    for title, content in sections:
        elements.append(Paragraph(title, section_style))
        elements.append(Paragraph(content.replace("\n", "<br/>"), body_style))
        elements.append(Spacer(1, 12))
    doc.build(elements)
    return pdf_path

def compare_awrs(file_list, llm_model):
    if not file_list:
        return "No files selected."
    combined_text = ""
    for fname in file_list:
        content = get_awr_file_text(fname)
        combined_text += f"\n=== AWR: {fname} ===\n{content[:3000]}...\n"
    prompt = f"""You are a senior Oracle performance engineer. You will compare multiple AWR reports and highlight:
- Key differences in workload or system behavior
- Major trends or anomalies
- Which report shows better performance and why
- Exadata-specific metrics like Smart Scan, Flash I/O
- Suggestions to unify or improve system behavior
AWR Reports:
{combined_text}
"""
    response = client.chat.completions.create(
        model=llm_model,
        messages=[
            {"role": "system", "content": "You are a comparative AWR analysis expert."},
            {"role": "user", "content": prompt}
        ]
    )
    return response.choices[0].message.content.strip()

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

# === 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, 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 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
- I/O Subsystem stalls or errors
- ASM/Grid Disk issues
- Smart Scan failures
- Redo Log issues
- RAC Interconnect issues
AWR CONTENT:
{cleaned_content}
"""
        response = client.chat.completions.create(
            model=llm_model,
            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):
        prompt = f"Rate the following analysis from 1-5 stars and explain:\n\n{content}"
        response = openrouter.chat.completions.create(
            model="mistralai/Mixtral-8x7B-Instruct",
            messages=[{"role": "user", "content": prompt}]
        )
        return response.choices[0].message.content.strip()

# === MAIN AWR 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)
    stars = 0
    match = re.search(r"(\d+)", rating_text)
    if match:
        stars = int(match.group(1))
    retry_status = "✅ Accepted"
    if stars < threshold:
        analysis = analyzer.analyze(awr_text, performance_test_mode, exadata_model, rack_size, llm_model)
        rating_text = rater.rate(analysis)
        retry_status = "✅ Retry Occurred"
    return analysis, health_status, rating_text, retry_status

# === Gradio UI ===
with gr.Blocks() as demo:
    with gr.Tab("Manual AWR Analysis"):
        gr.Markdown("# Multi-Agent Oracle AWR Analyzer (Version 3.1)")
        awr_file = gr.File(label="Upload AWR Report (.html or .txt)", file_types=[".html", ".txt"])
        awr_text = gr.Textbox(label="AWR Report (pasted or loaded)", lines=30)
        awr_file.upload(awr_file_to_text, inputs=awr_file, outputs=awr_text)
        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.1", label="LLM Model")
        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])
        pdf_button = gr.Button("📄 Generate PDF")
        pdf_file = gr.File(label="Download PDF", type="filepath")  # ✅ fixed
        pdf_button.click(
            fn=generate_pdf,
            inputs=[output, health, rating, retry_status],
            outputs=pdf_file
        )

    with gr.Tab("Compare AWRs"):
        upload_file = gr.File(label="Upload AWR Report", file_types=[".html", ".txt"])
        upload_status = gr.Textbox(label="Upload Status")
        upload_file.upload(fn=upload_awr_file, inputs=upload_file, outputs=upload_status)
    
        refresh_button = gr.Button("🔃 Refresh File List")
        file_multiselect = gr.Dropdown(choices=[], label="Select AWR Files", multiselect=True)
        refresh_button.click(fn=lambda: gr.update(choices=list_awr_files()), outputs=file_multiselect)
    
        llm_compare = gr.Dropdown(choices=list(supported_llms.keys()), value="gpt-4.1", label="LLM Model for Comparison")
        compare_output = gr.Textbox(label="Comparison Output", lines=20)
        
        compare_btn = gr.Button("Compare Selected AWRs")
        compare_btn.click(fn=compare_awrs, inputs=[file_multiselect, llm_compare], outputs=compare_output)
    
        # PDF Export for Compare tab
        pdf_compare_button = gr.Button("📄 Generate Comparison PDF")
        pdf_compare_file = gr.File(label="Download Comparison PDF", type="filepath")
        pdf_compare_button.click(
            fn=lambda comparison_text: generate_pdf(comparison_text, "", "", ""),
            inputs=[compare_output],
            outputs=pdf_compare_file
        )

if __name__ == "__main__":
    demo.launch(debug=True)