AskQuestion1.py

from langchain_community.utilities import SQLDatabase
from langchain_openai import ChatOpenAI
#from langchain_classic.chains import create_sql_query_chain
from langchain.chains import create_sql_query_chain #=> it is required for huggingface compatibility
from langchain_core.prompts import PromptTemplate
import os
import pymysql
from huggingface_hub import hf_hub_download
import re
from huggingface_hub import HfApi, login
from datetime import datetime

from datetime import datetime
from zoneinfo import ZoneInfo

# now_ist = datetime.now(ZoneInfo("Asia/Kolkata"))
# currint_time=now_ist.strftime("%d-%m-%Y %H:%M:%S")
# print(now_ist.strftime("%d-%m-%Y %H:%M:%S"))

def currentTime():
    now_ist = datetime.now(ZoneInfo("Asia/Kolkata"))
    currint_time=now_ist.strftime("%d-%m-%Y %H:%M:%S")
    return currint_time


os.environ["HF_TOKEN"] = os.getenv("HF_TOKEN")
api = HfApi(token=os.getenv("HF_TOKEN"))
repo_id = "manabb/nrl"
file_path_in_repo="LLMLogs.txt"

def update_log(newRecords):
    # Download existing, append, re-upload

    try:
        # Download current version
        downloaded_path = hf_hub_download(
            repo_id=repo_id, 
            filename=file_path_in_repo,
            repo_type="dataset"
        )
        
        # Append new line
        with open(downloaded_path, 'a', encoding='utf-8') as f:
            f.write("\n from AskQuestion1:"+currentTime()+"=>"+newRecords+"\n")
        
        # Re-upload (overwrites)
        api.upload_file(
            path_or_fileobj=downloaded_path,
            path_in_repo=file_path_in_repo,
            repo_id=repo_id,
            repo_type="dataset",
            commit_message="Append new log entry.."
        )
        
    except Exception:
        print("File not found - created new")


def ask_question(question: str):


    # Database connection
    
    # MySQL FreeDB connection
    db = SQLDatabase.from_uri(
        # "mysql+pymysql://freedb_manabb:%YZN6TrPbM2k$ef@sql.freedb.tech:3306/freedb_manabdatabase"
        "mysql+pymysql://ypccjqbi_manabview:t~#[B_42%fl^ApZM@103.191.208.227:3306/ypccjqbi_manabcommercial"
    )

    # OpenAI API key
    api_key = os.getenv("OPENAI_API_KEY")
    if api_key:
        os.environ["OPENAI_API_KEY"] = api_key

    llm = ChatOpenAI(
        model="gpt-4o-mini",
        temperature=0
    )

    table_info = db.get_table_info()
    # print("Table Info:", table_info)

    top_k = 5
    input = question

    # MySQL specific prompt
    PROMPT = """
You are a MySQL expert.

Given an input question, create a syntactically correct MySQL query.

Schema:
{table_info}

Rules:
1. Use ONLY this table: manabCommercial1
2. Allowed SQL: SELECT only
3. Never generate the following statements:
   INSERT, UPDATE, DELETE, DROP, ALTER, CREATE, TRUNCATE.
4. If EFileNo is provided in the user input, the SQL query must include: WHERE EFileNo = '<value>'.
5. If PRNo is provided in the user input, the SQL query must include:
   WHERE PRNo = '<value>'.
6. Do NOT wrap SQL in markdown (no ```sql)
7. Status column includes all the updates and comments, while completed column is usually Completed/NotCompleted
8. Any value starting with PUR- must be treated as EFileNo.
9. Any value starting with 1000 must be treated as PRNo.
10. EFileNo and PRNo are text fields, therefore comparisons must always use quotes.
11. In case of ambiguity, if an EFileNo is present in the user input, generate the SQL query using WHERE EFileNo = '<value>'.

Examples:

Question: What is the current status of PUR-123
SQLQuery:
SELECT status FROM manabCommercial1 WHERE EFileNo='PUR-123';

Question: Give list of the files which are completed
SQLQuery:
SELECT EFileNo FROM manabCommercial1 WHERE completed='Completed';

Question: Show the proposals which are in progress or not completed
SQLQuery:
SELECT EFileNo FROM manabCommercial1 WHERE completed='NotCompleted' ;


Question: What is the reason for delay in completion of file no PUR-xyz
SQLQuery:
SELECT status FROM manabCommercial1 WHERE EFileNo='PUR-xyz' LIMIT {top_k};

Question: Show the main events of file PUR-abc
SQLQuery:
SELECT status FROM manabCommercial1 WHERE EFileNo='PUR-abc' LIMIT {top_k};


Question: What is the progress of PUR-mno
SQLQuery:
SELECT status FROM manabCommercial1 WHERE EFileNo='PUR-mno' LIMIT {top_k};

Now generate SQL for the following question.

Question:
{input}

SQLQuery:
"""

    prompt = PromptTemplate.from_template(PROMPT)

    chain = create_sql_query_chain(
        llm,
        db,
        prompt
    )

    sql_query = chain.invoke({"question": input})
    
    sql_query = sql_query.replace("```sql", "").replace("```", "").strip()

    # print("Generated SQL:", sql_query)
    # result = db.run(sql_query,fetch="all")
    db._engine.dispose()  # Close the database connection

    # def get_db_connection():
    #     conn = pymysql.connect(
    #         host="sql.freedb.tech",
    #         user="freedb_manabb",
    #         password="%YZN6TrPbM2k$ef",
    #         database="freedb_manabdatabase",
    #         port=3306,
    #         autocommit=True
    #     )
    #     return conn
    def get_db_connection():
        conn = pymysql.connect(
            host="103.191.208.227",
            user="ypccjqbi_manabview",
            password="t~#[B_42%fl^ApZM",
            database="ypccjqbi_manabcommercial",
            port=3306,
            autocommit=True
        )
        return conn
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute(sql_query)
    rows = cursor.fetchall()
    cursor.close()
    conn.close()
    
    
    # if not result:
    #     result_text = "No rows"
    # else:
    #     result_text = str(result)
    result_text=""
    if not rows:
        result_text = "No rows"
    else:
        result_text = "\n".join(str(col) for row in rows for col in row)
    # print("Result:", result_text)
        

    # full_response = llm.invoke(
    #     f"Query: {sql_query}\nResult: {result}\nAnswer the original question: {question}"
    # )
    
    
    full_response = llm.invoke(
    f"""STRICT RULES - Answer ONLY using the Result below. Do NOT hallucinate or add external knowledge.
        1. If Result is empty (e.g., '[]', '', or 'No rows'), respond EXACTLY: "No relevant results found in the database."
        2. Write the response in clear and well-structured English with correct grammar and spelling.
        3. The final two sentences in the Result represent the current status. Clearly highlight or explicitly state these two status sentences in the answer.
        4. Do not invent information. Every statement must be derived from the Result.
        5. If the Result contains the name of any person, replace the name with:
            "A GENTLEMAN"
                Example:
                If the Result contains "Mr. Sharma approved the file",
                write it as:
                "A GENTLEMAN approved the file".
        6. If the question is about delay:
            a) Identify the oldest and latest dates mentioned in the Result.
            b) Calculate the difference in days.
            • If the difference ≤ 70 days → state that the timeline is within acceptable limits.
            • If the difference > 70 days → explain the delay using only the information from the Result and provide explanation in bullet point.
            
            

        Query: {sql_query}
        Result: {result_text}
        Original question: {question}

        Answer:"""
        )
    xx=f"""
    Original Querry: {question}
    sql_querry: {sql_query}
    sql_respons: {result_text}
    llm final response: {full_response.content}
    """
    update_log(xx)
    return full_response.content