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_chains() -> (list[dict]):
    with psycopg.connect(**db_con_params, row_factory=psycopg.rows.dict_row) as conn:
        with conn.cursor() as cur:
            cur.execute("""
            SELECT name, color FROM chain
            """)
            chains = cur.fetchall()
            return chains

def get_all() -> (list[dict]):
    with psycopg.connect(**db_con_params, row_factory=psycopg.rows.dict_row) as conn:
        with conn.cursor() as cur:
            cur.execute("""
 SELECT
 shop.osm_id,
 shop.name,
 shop.brand,
 ST_Y(ST_Transform(shop.geom, 4326)) AS lat,
 ST_X(ST_Transform(shop.geom, 4326)) AS long,
 ST_AsGeoJSON(ST_Transform(polygon.geom, 4326)) as polygon
 FROM shop
 JOIN
 (
   SELECT (ST_DUMP(ST_VoronoiPolygons(ST_Collect(geom)))).geom as geom
   FROM shop
   WHERE subclass = 'supermarket'
 ) polygon ON ST_Contains(polygon.geom, shop.geom)
 WHERE subclass = 'supermarket'
 ;
            """)

            all = cur.fetchall()
            return all


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 name, 
            CASE WHEN brand ILIKE '%365%' THEN '365discount' ELSE BRAND END,
            ST_Y(ST_Transform(geom, 4326)) AS lat, ST_X(ST_Transform(geom, 4326)) AS long 
            FROM shop WHERE subclass = 'supermarket';
            """)

            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:
            """
            SELECT ST_AsGeoJSON(ST_Transform(ST_VoronoiPolygons(ST_Collect(geom)), 4326)) as geojson
            FROM shop 
            WHERE subclass = 'supermarket';
            """
            """
            SELECT *,
            ST_ClusterDBSCAN(<geom>, 0, 1) OVER() AS _clst
            FROM   <your_table>;
            """
            cur.execute("""
                SELECT ARRAY_AGG(name) as names, ST_AsGeoJSON(ST_Transform(ST_VoronoiPolygons(ST_Collect(geom)), 4326)) as geojson
                FROM shop
                WHERE subclass = 'supermarket' AND osm_id IN (7688736659, 7682568726, 1085855265, 4517989992)
                ;
            """)

            voronoi_polygons = cur.fetchone()

            return voronoi_polygons