from fastapi import APIRouter, File, UploadFile, Depends, HTTPException,Query
from langchain_openai import ChatOpenAI
#from langchain.schema import AIMessage, HumanMessage, SystemMessage
#from langchain.schema import AIMessage, HumanMessage, SystemMessage
from langchain_core.messages import AIMessage, HumanMessage, SystemMessage
from langchain_core.messages import AIMessage, HumanMessage, SystemMessage
from .prompts import decompose_prompt,gl_sql_query_prompt
from .response_formate import QueryDecomposeResponse,GLSQLQueryResponse
import psycopg2
from psycopg2.extras import RealDictCursor
import json


from src.core.database import get_db,get_db_on_the_fly
from sqlalchemy.ext.asyncio import AsyncSession


ai_router = APIRouter(prefix="/ai", tags=["ai module"])


api_key = "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"

llm = ChatOpenAI(model="gpt-4o",api_key=api_key)


chat_history = [] 
system_message = SystemMessage(content="You are a helpful AI assistant.")
chat_history.append(system_message)  



def run_query(query: str) -> list[dict]:
    """
    Executes a SQL query and returns the results as a list of JSON-like dicts.
    Only intended for read-only (SELECT) queries.
    """
    dbname = "postgres"
    user = "adminfpna"
    password =  "Maven%404321" 
    host = "fpna-dev-postgresql.postgres.database.azure.com"
    port = 5432

    conn = psycopg2.connect(
        dbname=dbname,
        user=user,
        password=password,
        host=host,
        port=port
    )
    try:
        with conn.cursor(cursor_factory=RealDictCursor) as cur:
            cur.execute(query)
            if cur.description:
                return cur.fetchall()
            return []
    finally:
        conn.close()



def run_query(query: str) -> list[dict]:
    """
    Executes a SQL query and returns the results as a list of JSON-like dicts.
    Only intended for read-only (SELECT) queries.
    """
    dbname = "postgres"
    user = "adminfpna"
    password =  "Maven%402025" 
    host = "fpna-dev-postgresql.postgres.database.azure.com"
    port = 5432

    conn = psycopg2.connect(
        dbname=dbname,
        user=user,
        password=password,
        host=host,
        port=port
    )
    try:
        with conn.cursor(cursor_factory=RealDictCursor) as cur:
            cur.execute(query)
            if cur.description:
                return cur.fetchall()
            return []
    finally:
        conn.close()

@ai_router.get("/chat")
def chat(query:str):
    prompt = decompose_prompt("thrinath inc",query)
    structured_llm = llm.with_structured_output(QueryDecomposeResponse)
    result_1 = structured_llm.invoke(prompt)

    prompt = gl_sql_query_prompt(result_1)

    print("prompt",prompt)

    structured_llm = llm.with_structured_output(GLSQLQueryResponse)
    result_2 = structured_llm.invoke(prompt)

    print("result_2",result_2)

    # result_1 = json.dumps(result_1)

    if(result_1.clarification_needed.lower() == "yes"):
        return {
        "query_decompose_response":result_1
        }

    temp_1 = []

    for i in result_2.queries:
       temp_2 = {}
       temp_3 =  run_query(i.sql_query)

       temp_2["question"] = i.question
       temp_2["sql_query"] = i.sql_query
       temp_2["sql_query_result"] = temp_3
       

       temp_1.append(temp_2)

    query = temp_1[0]["sql_query"]

    print(query)

    result  = run_query(query)


    a = {
        "question":query,
        "gl_sql":query,
        "sql_query_result": result
    }

    a_str = json.dumps(a)

    # Now embed it into the prompt
    prompt = f"""
    answer user question based on the sql query response by interpreting the given object

    object:
    {a_str}
"""
    llm_answer = llm.invoke(prompt)

    

    print(prompt)
    
    
    return {
        "query_decompose_response":result_1,
        "gl_sql_query_response":temp_1,
        "sql_query_result": result,
        "llm_answer": llm_answer.content
    }



from pydantic import BaseModel
from typing import Optional
from .service import ask_ai

class ChatRequest(BaseModel):
    message: str
    context: Optional[str] = None

@ai_router.post("")
async def chat_ask(request: ChatRequest):
    reply = await ask_ai(request.message, request.context)
    return {"reply": reply}