from psycopg2 import sql
from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT    
from sqlalchemy.orm import Session
import json

CONSTANT_COUNTRY_MAPPINGS = [
    {'iso': 'MX', 'table': '"polygon"."polygon_mx"'},
    {'iso': 'US', 'table': '"polygon"."polygon_us"'},
    {'iso': 'CA', 'table': '"tabla_canada"'},
    {'iso': 'BR', 'table': '"polygon"."polygon_br"'},
    {'iso': 'CL', 'table': '"polygon"."polygon_cl"'},
    {'iso': 'UY', 'table': '"polygon"."polygon_uy"'},
    {'iso': 'EC', 'table': '"polygon"."polygon_ec"'},
    {'iso': 'CO', 'table': '"polygon"."polygon_co"'},
    {'iso': 'PA', 'table': '"polygon"."polygon_pa"'},
    {'iso': 'PY', 'table': '"polygon"."polygon_py"'},
    {'iso': 'VZ', 'table': '"polygon"."polygon_vz"'},
    {'iso': 'PE', 'table': '"polygon"."polygon_pe"'},
    {'iso': 'HN', 'table': '"polygon"."polygon_hn"'},
    {'iso': 'NI', 'table': '"polygon"."polygon_ni"'},
    {'iso': 'BO', 'table': '"polygon"."polygon_bo"'},
    {'iso': 'AR', 'table': '"polygon"."polygon_ar"'},
    {'iso': 'PT', 'table': '"polygon"."polygon_pt"'},
    {'iso': 'DE', 'table': '"polygon"."polygon_de"'},
    {'iso': 'DE', 'table': '"polygon"."polygon_de"'},
    {'iso': 'VE', 'table': '"polygon"."polygon_ve"'},
    {'iso': 'NE', 'table': '"polygon"."polygon_ne"'},
    {'iso': 'CR', 'table': '"polygon"."polygon_cr"'},
    {'iso': 'PR', 'table': '"polygon"."polygon_pr"'},
    {'iso': 'AW', 'table': '"polygon"."polygon_aw"'},

    # Agrega más elementos según sea necesario
] 
def getCountry(session: Session,longitude: float ,latitude:float,skip: int = 0, limit: int = 100):
    connection = session.connection()
    cursor = connection.connection.cursor()
    cursor.execute(sql.SQL("SELECT iso2,data FROM polygon.countries WHERE polygon.ST_Contains(polygon , polygon.ST_MakePoint({}::double precision,{}::double precision )) and enable = true ").format(
    sql.Literal(longitude),
    sql.Literal(latitude)
    ))
    data = cursor.fetchone()
    if data is None:
        return None
    return data
def getTableByIso(iso_code):
    """
    Busca en la lista de mapeos de ISO y devuelve el nombre de la tabla asociada al código ISO dado.
    """
    for mapping in CONSTANT_COUNTRY_MAPPINGS:
        if mapping['iso'] == iso_code:
            return mapping['table']
    
    # Si no se encuentra ningún mapeo para el código ISO dado, devolver None o un valor predeterminado
    return None  # Puedes ajustar esto según tus necesidades

def getPolygon(connection ,tableName,latitude,longitude):
    connection = connection.connection()
    cursor = connection.connection.cursor()
    cursor.execute(
        f"SELECT data,id FROM {tableName} WHERE polygon.ST_Contains(polygon, polygon.ST_MakePoint(%s::double precision, %s::double precision))",
        (longitude, latitude)
    )
    return cursor.fetchone()

def updatePolygon(connection, tableName, updateData, id):
    
        conn = connection.connection()
        cursor = conn.connection.cursor()

        for entry in updateData:
            fieldName = entry['name']
            fileData = entry['data']
            json_path = f'{{"{fieldName}"}}'
            json_value = json.dumps(fileData)
            
            jsonb_set_func = f"jsonb_set(data, '{json_path}', '{json_value}', true)"
            
            sqlValue = f"UPDATE {tableName} SET data = {jsonb_set_func} WHERE id = %s"
            cleaned_sql = sqlValue.replace('\\', '')

            cursor.execute(cleaned_sql, (id,))

        conn.commit()
        cursor.close()
        
        return 111