import os
import json
import psycopg2
from datetime import datetime

# Database connection parameters
DB_CONFIG = {
    "dbname": "ems",
    "user": "ems",
    "password": "qFadbNBSaEoc",
    "host": "localhost",
    "port": 5432,
}

# Root directory where the folders are located
ROOT_DIR = "./"

def insert_bounds(entity_id, entity_type, geometry, conn):
    """Insert data into the bounds table."""
    try:
        with conn.cursor() as cur:
            # Check if the GeoJSON is a GeometryCollection
            if geometry.get('type') == 'GeometryCollection':
                # Extract polygons from the GeometryCollection and convert them to MultiPolygon
                geometry = {
                    "type": "MultiPolygon",
                    "coordinates": [
                        geom["coordinates"] for geom in geometry["geometries"] if geom["type"] == "Polygon"
                    ]
                }

            # Insert into the bounds table
            cur.execute(
                """
                INSERT INTO bounds (entity_id, entity_type, geometry)
                VALUES (%s, %s, 
                    ST_Transform(
                        ST_Multi(
                            ST_GeomFromGeoJSON(%s::JSON)
                        ), 
                    3857)
                );
                """,
                (entity_id, entity_type, json.dumps(geometry))
            )
        conn.commit()
    except Exception as e:
        print(f"Error inserting entity_id {entity_id} of type {entity_type}: {e}")
        conn.rollback()

def process_geojson_files():
    """Process all GeoJSON files in the directory structure."""
    try:
        conn = psycopg2.connect(**DB_CONFIG)
        for folder in os.listdir(ROOT_DIR):
            folder_path = os.path.join(ROOT_DIR, folder)
            if os.path.isdir(folder_path):
                entity_type = folder  # Folder name is the entity_type
                for file in os.listdir(folder_path):
                    if file.endswith(".geojson"):
                        entity_id = int(os.path.splitext(file)[0])  # Extract ID from filename
                        file_path = os.path.join(folder_path, file)
                        with open(file_path, "r") as f:
                            geometry = json.load(f)
                            insert_bounds(entity_id, entity_type, geometry, conn)
        conn.close()
    except Exception as e:
        print(f"Error processing files: {e}")

if __name__ == "__main__":
    process_geojson_files()