import os

import psycopg
import yaml

with open(os.path.join('configs', 'database.yml'), 'r') as file:
    db_con_params = yaml.safe_load(file.read())


def get_supermarkets() -> (list[dict], list[str]):
    with psycopg.connect(**db_con_params, row_factory=psycopg.rows.dict_row) as conn:
        with conn.cursor() as cur:
            cur.execute("""
            SELECT osm_type, osm_id, name, 
            ST_Y(ST_Transform(geom, 4326)) AS lat, ST_X(ST_Transform(geom, 4326)) AS long 
            FROM shop WHERE subclass = 'supermarket' AND name='Løvbjerg';
            """)

            supermarkets = cur.fetchall()
            return supermarkets


def voronoi_polygons() -> (list[dict], list[str]):
    with psycopg.connect(**db_con_params, row_factory=psycopg.rows.dict_row) as conn:
        with conn.cursor() as cur:
            cur.execute("""
                SELECT ST_AsGeoJSON(ST_Transform(ST_VoronoiPolygons(ST_Collect(geom)), 4326)) as geojson
                FROM shop 
                WHERE subclass = 'supermarket' 
                AND name = 'Løvbjerg';
            """)

            voronoi_polygons = cur.fetchone()['geojson']

            return voronoi_polygons