import sqlite3
import json
from datetime import datetime

# Load ticket data
with open("C:/Users/anike/PycharmProjects/AI_Agents_crew/ticket_data.json") as f:
    tickets = json.load(f)

# Connect to SQLite
conn = sqlite3.connect("tickets.db")
cursor = conn.cursor()

# def get_ticket_by_number(ticket_number):
#     cursor.execute("SELECT * FROM tickets WHERE ticket_number = ?", (ticket_number,))
#     row = cursor.fetchone()
#     if row:
#         return dict(zip([desc[0] for desc in cursor.description], row))
#     return None
#
# # Example
# ticket = get_ticket_by_number("TKT-1050")
# print(ticket)


#
# # Drop if exists
# cursor.execute("DROP TABLE IF EXISTS tickets")
#
# # Create table
# cursor.execute("""
# CREATE TABLE tickets (
#     ticket_number TEXT PRIMARY KEY,
#     opened_at TEXT,
#     closed_at TEXT,
#     description TEXT,
#     resolution_comment TEXT,
#     closed_by TEXT,
#     type TEXT,
#     status TEXT,
#     severity TEXT,
#     reported_by TEXT,
#     assigned_to TEXT,
#     last_updated TEXT,
#     environment TEXT,
#     tags TEXT
# )
# """)
#
# # Insert data
# for ticket in tickets:
#     cursor.execute("""
#     INSERT INTO tickets VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
#     """, (
#         ticket["ticket_number"],
#         ticket["opened_at"],
#         ticket["closed_at"],
#         ticket["description"],
#         ticket["resolution_comment"],
#         ticket["closed_by"],
#         ticket["type"],
#         ticket["status"],
#         ticket["severity"],
#         ticket["reported_by"],
#         ticket["assigned_to"],
#         ticket["last_updated"],
#         ticket["environment"],
#         ",".join(ticket["tags"]),
#     ))
#
# conn.commit()

import requests

def extract_query_intent_with_llama(prompt: str, model_url: str = "http://localhost:11434/api/generate") -> dict:
    system_prompt = """Extract the intent from the user's query. Return a JSON object with at least one of:
            - "ticket_number" (e.g., "TKT-1050")
            - "tag" (e.g., "database", "login", "UI")
            - "keyword" (e.g., a symptom like 'rate limiter', 'timeout', etc.)
            
            Only include keys that are present or clearly implied. Do NOT add extra fields.
            Example output:
            { "ticket_number": "TKT-1050" }
            or
            { "tag": "database" }
            or
            { "keyword": "rate limiter" }
"""

    response = requests.post(
        model_url,
        json={
            "model": "llama3.2",
            "prompt": f"<|system|>\n{system_prompt}\n<|user|>\n{prompt}\n<|assistant|>",
            "stream": False
        }
    )

    try:
        raw = response.json()["response"]
        # Clean JSON if LLM adds extra text
        raw = raw.strip().split("\n")[0].strip("` ")
        return eval(raw) if raw.startswith("{") else {}
    except Exception as e:
        print(f"LLM parsing failed: {e}")
        return {}

intent = extract_query_intent_with_llama("Summarize ticket TKT-1050 ?")
print(intent)