import os
from semantic_kernel import Kernel
from semantic_kernel.orchestration.sk_context import SKContext
from semantic_kernel.connectors.ai.open_ai import OpenAIChatCompletion
from semantic_kernel.utils.settings import openai_settings_from_dot_env
from semantic_kernel.skill_definition import sk_function, sk_function_context_parameter
from .SQLFunctions import SQLGetQuery

# Load environment variables from .env file
from dotenv import load_dotenv
load_dotenv()

class SQLskill:
    def __init__(self):
        self._kernel = Kernel()
        api_key, org_id = os.environ['OPENAI_API_KEY'], ""
        self._kernel.add_text_completion_service(
        "dv", OpenAIChatCompletion(os.environ['OPENAI_MODEL_NAME'], api_key, org_id)
    )
    @sk_function(
        description="Routes the request to the appropriate function",
        name="sql_request",
    )
    @sk_function_context_parameter(name="input", description="The raw question for querying")
    async def RouteRequest(self, context: SKContext) -> str:
        # Save the original user request
        request = context["input"]
        context["original_request"] = request
        
        # Define plugins directory
        pluginsDirectory = "./functions/SK/plugins"
        self._kernel.import_semantic_skill_from_directory(pluginsDirectory, "SQLskill")
        ## Semantic Functions
        CreateSQLQuery = self._kernel.skills.get_function("SQLskill", "CreateSQLQuery")
        CreateSQLResponse = self._kernel.skills.get_function("SQLskill", "CreateResponse")
        ## Native Functions
        self._kernel.import_skill(SQLGetQuery(self._kernel), skill_name="SQLGetQuery")
        GetSQLResult = self._kernel.skills.get_function("SQLGetQuery", "dbQuery")

        # Run the functions in a pipeline
        output = await self._kernel.run_async(
            CreateSQLQuery,
            GetSQLResult,
            CreateSQLResponse,
            input_context=context,
        )
        return output["input"].replace("\n\n", "\n")