from pycen import con_gn
from sqlalchemy.sql import text

def update_additional_values(
        con,table='cor_counting_occtax',shema='pr_occtax',
        jsonb_column='additional_fields',keyfield='code_atlas'):
    sql = text('''
        UPDATE %(sch)s.%(tab)s
        SET "%(jsonb_c)s" = JSONB_SET(
            "%(jsonb_c)s", 
            '{%(keyf)s}'::text[], 
            TO_JSONB(
                ref_nomenclatures.get_nomenclature_label(("%(jsonb_c)s"::json#>>'{%(keyf)s}')::int,
                'default')
            ), 
            false)
        WHERE ("%(jsonb_c)s"::json#>>'{%(keyf)s}')::text ~ '^[0-9\.]+$' 
            AND ("%(jsonb_c)s"::json#>>'{%(keyf)s}')::int in (
                SELECT id_nomenclature FROM ref_nomenclatures.t_nomenclatures t
                JOIN ref_nomenclatures.bib_nomenclatures_types b USING (id_type) 
                WHERE lower(b.mnemonique) = '%(keyf)s'
        )
    '''%{'keyf':keyfield,'tab':table,'sch':shema,'jsonb_c':jsonb_column})
    # print(sql)

    with con.begin() as cnx:
        cnx.execute(sql)
    
def update_additional_key(
    con,table='cor_counting_occtax',shema='pr_occtax',jsonb_column='additional_fields',
    keyfield='Strate de végétation',new_keyfield='strate_flore'
    ):
    sql = text('''
        UPDATE %(sch)s.%(tab)s
        SET "%(jsonb_c)s" = "%(jsonb_c)s" - '%(keyf)s' || jsonb_build_object(
            '%(new_keyf)s', "%(jsonb_c)s"->'%(keyf)s'
        )
        WHERE "%(jsonb_c)s" ? '%(keyf)s';
    '''%{'keyf':keyfield,'new_keyf':new_keyfield,'tab':table,'sch':shema,'jsonb_c':jsonb_column})
    # print(sql)

    with con.begin() as cnx:
        cnx.execute(sql)