import os
import duckdb
import fiona
import shapely
import pandas as pd
from pyproj import CRS, Transformer
import geopandas as gpd
from pathlib import Path
from fiona.crs import from_epsg
from shapely.geometry import Polygon, LineString, Point, shape, mapping



def funcion_1(variables):
    shapefile_1 = variables["shape1"]
    filter_shp_1 = variables["filter_shape1"]
    shapefile_2 = variables["shape2"]

    global_epsg = '4326'

#    crs_shp_1 = CRS.from_file(shapefile_1)
#    epsg_shp_1 = crs_shp_1.to_epsg()
    filteres_features_shape_1 = []
    geometries_converted = []
    final_filtered_geometries = []

    geometries_shp_1 = []
    transformer_1 = None
    with fiona.open(shapefile_1, 'r') as src:
        crs = src.crs
        columns_shp_1 = src.schema['properties']
        if crs:
            crs_shp_1 = CRS(crs)
            epsg_shp_1 = crs_shp_1.to_epsg()
        if int(epsg_shp_1) != int(global_epsg):
            transformer_1 = Transformer.from_crs(f'EPSG:{epsg_shp_1}', f'EPSG:{global_epsg}', always_xy=True)
        for item in columns_shp_1.keys():
            for feature in src:
                name_value = feature['properties'].get(item)
                try:
                    if name_value and filter_shp_1 in name_value:
                        if transformer_1:
                            transformed_geom = shapely.ops.transform(lambda x, y: transformer_1.transform(x, y), shape(feature['geometry']))
                            geometries_shp_1.append(transformed_geom)
                        else:
                            geometries_shp_1.append(shape(feature['geometry']))
                except:
                    pass

    #print('--------------------------------------------------------------------')

    transformer_2 = None
    with fiona.open(shapefile_2, 'r') as src:
        crs = src.crs
        columns_shp_2 = src.schema['properties']
        if crs:
            crs_shp_2 = CRS(crs)
            epsg_shp_2 = crs_shp_2.to_epsg()
        if int(epsg_shp_2) != int(global_epsg):
            transformer_2 = Transformer.from_crs(f'EPSG:{epsg_shp_2}', f'EPSG:{global_epsg}', always_xy=True)
        for feature2 in src:
            if transformer_2:
                geom = shapely.ops.transform(lambda x, y: transformer_2.transform(x, y), shape(feature2['geometry']))
            else:
                geom = shape(feature2['geometry'])
            for geoms_shape_1 in geometries_shp_1:
                if geoms_shape_1.intersects(geom) or geoms_shape_1.touches(geom):
#                if geoms_shape_1.contains(geom):
                    final_filtered_geometries.append(geom)

    schema = {
        'geometry': 'Polygon',  # Cambia aquí según el tipo de geometría que contenga tu archivo
    }

    path_shape = f'/home/data3/master_folder/raster/shp/salida_prueba_1/geometrias_from_{filter_shp_1}.shp'
    # Abre un nuevo archivo shapefile en modo escritura
    with fiona.open(path_shape, 'w', driver='ESRI Shapefile', schema=schema, crs=from_epsg(4326)) as output:
        # Itera sobre las geometrías filtradas y escribe cada una en el nuevo archivo shapefile
        for feature in final_filtered_geometries:
            output.write({
                'geometry': mapping(feature),
            })

    path_geojson = f'/home/data3/master_folder/vectorial/geojson/geometrias_from_{filter_shp_1}.shp'

#    conn = duckdb.connect(database='/home/data12/duckdb/pruebas_javi.db')
#    conn.sql('INSTALL httpfs')
#    conn.sql('LOAD httpfs')
#    conn.sql('INSTALL spatial')
#    conn.sql('LOAD spatial')
#    conn.sql('INSTALL postgres')
#    conn.sql('LOAD postgres')
#    format = 'GeoJSON'
#    consulta = f"COPY (SELECT * EXCLUDE(geom), ST_TRANSFORM(ST_GeomFromText(geom), 'EPSG:{global_epsg}', 'EPSG:{global_epsg}') AS geom FROM '{path_shape}') TO '{path_geojson}' (FORMAT GDAL, DRIVER '{format}')"
#    print(consulta)
#    conn.sql(consulta)

    command = f'ogr2ogr -f "GeoJSON" -t_srs EPSG:4326 {path_geojson} {path_shape}'
    print(command)
    os.system(command)
    return {'funcion_realizada': True, 'path_out': path_geojson}



#data = {"shape1":"/home/data3/master_folder/raster/shp/shapefiles_provincias_espana/shapefiles_provincias_espana.shp", "shape2":"/home/data12/duckdb/codigos_postales/codigos_postales.shp"}
data = {"shape1":"/home/data3/master_folder/raster/shp/prueba_prov_spain.shp", "filter_shape1":"Madrid", "shape2":"/home/data12/duckdb/codigos_postales/codigos_postales.shp"}

funcion_1(variables=data)