import sqlite3
from utils import TEMP_DIR

def tools_call(session_hash):
    dir_path = TEMP_DIR / str(session_hash)
    connection = sqlite3.connect(f'{dir_path}/data_source.db')
    print("Querying Database in Tools.py");
    cur=connection.execute('select * from data_source')
    columns = [i[0] for i in cur.description]
    print("COLUMNS 2")
    print(columns)
    cur.close()
    connection.close()

    return [
        {
            "type": "function",
            "function": {
                "name": "sql_query_func",
                "description": f"This a tool useful to query a SQLite table called 'data_source' with the following Columns: {columns}",
                "parameters": {
                    "type": "object",
                    "properties": {
                        "queries": {
                            "type": "array",
                            "description": "The query to use in the search. Infer this from the user's message. It should be a question or a statement",
                            "items": {
                                "type": "string",
                            }
                        }
                    },
                    "required": ["queries"],
                },
            },
        },
        {
        "type": "function",
            "function": {
                "name": "chart_generation_func",
                "description": f"""This a chart generation tool useful to generate charts and graphs from queried data from our SQL table called 'data_source 
                with the following Columns: {columns}. Returns an iframe string which will be displayed inline in our chat window. Do not edit the string returned 
                from the chart_generation_func function in any way and display it fully to the user in the chat window. You can add your own text supplementary 
                to it for context if desired.""",
                "parameters": {
                    "type": "object",
                    "properties": {
                        "data": {
                            "type": "array",
                            "description": """The list containing a dictionary that contains the 'data' portion of the plotly chart generation. Infer this from the user's message.""",
                            "items": {
                                "type": "string",
                            }
                        },
                        "layout": {
                            "type": "array",
                            "description": """The dictionary that contains the 'layout' portion of the plotly chart generation""",
                            "items": {
                                "type": "string",
                            }
                        }
                    },
                    "required": ["data"],
                },
            }, 
        },
        {
        "type": "function",
            "function": {
                "name": "table_generation_func",
                "description": f"""This an table generation tool useful to format data as a table from queried data from our SQL table called 
                'data_source with the following Columns: {columns}. Returns an html string generated from the pandas library and pandas.to_html() 
                function which will be displayed inline in our chat window. There will also be a link to download the CSV included in the HTML string. 
                This link should open in a new window. Do not edit the string returned by the function in any way when displaying to the user, as the user needs 
                all of the information returned by the function in it's exact state.""",
                "parameters": {
                    "type": "object",
                    "properties": {
                        "data": {
                            "type": "array",
                            "description": """The data points to use in the table generation. Infer this from the user's message. 
                            Send a python dictionary object with query data that correspond to data that will be converted into a pandas DataFrame and that correspond to the users request.
                            The keys of this python dictionary object will be the names of the columns and values will be a list of values for each object.
                            Make sure this is a dictionary object and not a string or an array.
                            Send nothing else.""",
                            "items": {
                                "type": "string",
                            }
                        }
                    },
                    "required": ["data"],
                },
            }, 
        }
    ]