import os
import json
import uuid
import duckdb

EXTENSIONS = ['httpfs', 'spatial', 'postgres']

path_db = '/home/isl_debug.db'
TABLE_NAME = 'functions_udf_debug'
function = 'pruebas'

def crear_tabla():
    consulta = f"CREATE TABLE {TABLE_NAME} (FUNCTION_GUID VARCHAR, FUNCION_NAME VARCHAR, FUNCION_PATH VARCHAR, DESCRIPCION VARCHAR, DESCRIPCION_CORTA VARCHAR, VARIABLES VARCHAR, EJEMPLO VARCHAR, SALIDA VARCHAR, IMAGE VARCHAR, TAGS VARCHAR, UNIQUE (FUNCTION_GUID, FUNCION_NAME))"
    conn.sql(consulta)

def filter_dictionary(guid, dictionary):
    print(f'El guid es: {guid}')
    print(f'El diccionario es: {dictionary}')
    if dictionary:
        for key, value in dictionary.items():
            if isinstance(value,dict):
                filter_dictionary(guid,value)
            elif isinstance(value,str):
                dictionary[key] = value.replace("'", f" {guid}")
    print(f'El diccionario final es: {dictionary}')
    return dictionary

conn = duckdb.connect(database=path_db)
for extension in EXTENSIONS:
    conn.sql(f'INSTALL {extension}')
    conn.sql(f'LOAD {extension}')

crear_tabla()

guid = (str(uuid.uuid4()))[:10]
with open(f"/home/data3/master_folder/isl/repositorios/filter_geometries/explanation.json") as json_file:
    json_data = json.load(json_file)
print(json_data)
variables_example = {key: str(value['example']) for (key, value) in json_data['variables'].items()}
data_example = {'disk': 'data3', 'udf_selected': function, 'variables': variables_example}
consulta_modificar_tabla = f"""INSERT INTO {TABLE_NAME} (FUNCTION_GUID, FUNCION_NAME, FUNCION_PATH, DESCRIPCION, DESCRIPCION_CORTA, VARIABLES, EJEMPLO, SALIDA, IMAGE, TAGS) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"""
conn.execute(consulta_modificar_tabla, [guid, json_data['name'], function, json_data['explanation'], json_data['short_explanation'], ((str(json_data['variables']).replace("'", '"')).replace('False', '"False"')).replace('True','"True"'), ((str(data_example).replace("'", '"')).replace('False', '"False"')).replace('True','"True"'), ((str(json_data['output']).replace("'", '"')).replace('False', '"False"')).replace('True','"True"'), "https://sd1.d3.ipsilum.com/foto/imagen_prueba_fused.jpg", "['General']"])

read_table = conn.sql(f"SELECT * FROM {TABLE_NAME}").fetchall()[0]
print(read_table)

dict_to_append = {"name":read_table[1], "path":read_table[2], "explanation":read_table[3], "short_explanation":read_table[4], "variables": json.loads(read_table[5]), "example":json.loads(read_table[6]), "image":read_table[8], "tags":read_table[9]}
print(dict_to_append)

os.remove(path_db)