import json
import psycopg2
from psycopg2.extras import execute_values

def populate_table(json_file, table_name, mapping):
    print(f"Cargando {json_file} en {table_name}...")
    with open(json_file, 'r', encoding='utf-8') as f:
        data = json.load(f)
    
    conn = psycopg2.connect(
        host="localhost",
        database="sigem",
        user="sigem_user",
        password="sigem_pass",
        port="5432"
    )
    cur = conn.cursor()
    
    values = []
    for feature in data['features']:
        props = feature['properties']
        geom = json.dumps(feature['geometry'])
        
        row = []
        for field in mapping:
            row.append(props.get(field))
        row.append(geom)
        values.append(tuple(row))
    
    placeholders = ",".join(["%s"] * len(mapping))
    query = f"INSERT INTO {table_name} ({','.join(mapping)}, geom) VALUES %s"
    
    # Transform geometry from GeoJSON string using ST_GeomFromGeoJSON
    # Wait! execute_values doesn't easily support SQL functions in values.
    # Better use direct insert loop or formatted values.
    
    cur.execute(f"TRUNCATE TABLE {table_name}")
    
    for val in values:
        sql = f"INSERT INTO {table_name} ({','.join(mapping)}, geom) VALUES ({placeholders}, ST_SetSRID(ST_GeomFromGeoJSON(%s), 4326))"
        cur.execute(sql, val)
        
    conn.commit()
    cur.close()
    conn.close()
    print(f"Carga finalizada: {len(values)} registros.")

# Mapping para Distritos
populate_table(
    '/yvyape/proyectos/sigem-gis/snc_ly_dist.json', 
    'public.snc_raw_distritos', 
    ['nom_dist', 'cod_dist', 'cod_dpto']
)

# Mapping para Departamentos
populate_table(
    '/yvyape/proyectos/sigem-gis/snc_ly_dpto.json', 
    'public.snc_raw_departamentos', 
    ['nom_dpto', 'cod_dpto']
)