# Path: plenario/database.py
# def create_database(bind: Engine, database: str) -> None:
# def drop_database(bind: Engine, database: str) -> None:
# def create_extension(bind: Engine, extension: str) -> None:
# def psql(path: str) -> None:
# def postgres_session_context():
# def redshift_session_context():


import json
from geoalchemy2 import Geometry
from sqlalchemy import BigInteger, Boolean, Column, DateTime, Float, ForeignKey, ForeignKeyConstraint, String, Table, \
    func as sqla_fn
from sqlalchemy.dialects.postgresql import DOUBLE_PRECISION, JSONB
from sqlalchemy.orm import relationship
from plenario.database import postgres_base, postgres_engine, postgres_session, redshift_base
        (SELECT ST_SetSRID(ST_GeomFromGeoJSON({geojson}), 4326)) AS ref_geom
      FROM
        sensor__node_metadata JOIN sensor__sensor_to_node
        ON id=node
      WHERE
          sensor_network = '{network}'
      GROUP BY
        node,
        location
      ORDER BY
        location <#> (SELECT ST_SetSRID(ST_GeomFromGeoJSON ({geojson}), 4326))
      LIMIT {k_10}
    )

    SELECT
      node,
      RANK() OVER(ORDER BY ST_Distance(location, ref_geom)) AS act_r
    FROM bbox_results
    WHERE
      sensors && '{sensors}'::VARCHAR[]
    ORDER BY act_r
    LIMIT {k};
    """.format(
        geojson=point,
        network=network,
        k=k,
        k_10=k_10,
        sensors='{' + ','.join(sensors) + '}'
    )


# Path: plenario/database.py
# def create_database(bind: Engine, database: str) -> None:
# def drop_database(bind: Engine, database: str) -> None:
# def create_extension(bind: Engine, extension: str) -> None:
# def psql(path: str) -> None:
# def postgres_session_context():
# def redshift_session_context():


# Path: plenario/models/SensorNetwork.py
import json
from geoalchemy2 import Geometry
from sqlalchemy import BigInteger, Boolean, Column, DateTime, Float, ForeignKey, ForeignKeyConstraint, String, Table, \
    func as sqla_fn
from sqlalchemy.dialects.postgresql import DOUBLE_PRECISION, JSONB
from sqlalchemy.orm import relationship
from plenario.database import postgres_base, postgres_engine, postgres_session, redshift_base

        (SELECT ST_SetSRID(ST_GeomFromGeoJSON({geojson}), 4326)) AS ref_geom
      FROM
        sensor__node_metadata JOIN sensor__sensor_to_node
        ON id=node
      WHERE
          sensor_network = '{network}'
      GROUP BY
        node,
        location
      ORDER BY
        location <#> (SELECT ST_SetSRID(ST_GeomFromGeoJSON ({geojson}), 4326))
      LIMIT {k_10}
    )

    SELECT
      node,
      RANK() OVER(ORDER BY ST_Distance(location, ref_geom)) AS act_r
    FROM bbox_results
    WHERE
      sensors && '{sensors}'::VARCHAR[]
    ORDER BY act_r
    LIMIT {k};
    """.format(
        geojson=point,
        network=network,
        k=k,
        k_10=k_10,
        sensors='{' + ','.join(sensors) + '}'
    )


    return postgres_engine.execute(query).fetchall()