#!/bin/python
# -*- coding: utf-8 -*-
"""
This module uses the ORM of SQLAlchemy to create classes of relations of the
CoastDat2 database schema.

You have to create a configuration file in ~/.open_eGo/config.ini .

    [home]
    username =
    password =
    host =
    port =
    db =

"""


__copyright__ = "ZNES"
__license__ = "GNU Affero General Public License Version 3 (AGPL-3.0)"
__url__ = "https://github.com/openego/data_processing/blob/master/LICENSE"
__author__ = "s3pp"


from sqlalchemy import MetaData, create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.automap import automap_base
from geoalchemy2 import Geometry, shape  # Geometry type used by SQLA
import configparser as cp
import os


def readcfg(path):
    """ Reads the configuration file

    Parameters
    ----------

    path : str
        Filepath.

    Returns
    -------
    cfg : configparser.ConfigParser
        Used for configuration file parser language.

    """

    cfg = cp.ConfigParser()
    cfg.read(path)

    return cfg


def dbconnect(section, cfg):
    """ Uses db connection parameters to establish a connection.

    Parameters
    ----------
    section : str
        Section in configuration file.
    cfg : configparser.ConfigParser
        Used for configuration file parser language.

    Returs
    ------
    conn : sqlalchemy.engine.Engine

    """

    conn = create_engine(
        "postgresql+psycopg2://{user}:{password}@{host}:{port}/{db}".format(
            user=cfg.get(section, 'username'),
            password=cfg.get(section, 'password'),
            host=cfg.get(section, 'host'),
            port=cfg.get(section, 'port'),
            db=cfg.get(section, 'db')))
    return conn


def meta_definition(meta, conn):
    """ Populates SQLAlchemy Meta object
    """
    meta.reflect(bind=conn, schema='coastdat')
    meta.reflect(bind=conn, schema='public',
                 only=['weather_measurement_point'])


def other_classes():
    """ Maps additional classes not mapped by SQLAlchemy automap.

    Parameters
    ----------
    base : AutomapBase
        Auto-generates mapped classes.

    Notes
    -----
    Here define classes explicitly, if they are not auto-generated.
    """

    class Located(Base):
        __tablename__ = 'coastdat.located'
        __table_args__ = ({'autoload': True},)

    class Scheduled(Base):
        __tablename__ = 'coastdat.scheduled'
        __table_args__ = ({'autoload': True},)

    class Typified(Base):
        __tablename__ = 'coastdat.typified'
        __table_args__ = ({'autoload': True},)

    # Base.metadata.create_all()

    return Located, Scheduled, Typified

print('Connecting to database.')

# read configuration file
path = os.path.join(os.path.expanduser("~"), '.open_eGo', 'config.ini')
config = readcfg(path=path)

# establish DB connection
section = 'home'
conn = dbconnect(section=section, cfg=config)

# instantiate container object MetaData
meta = MetaData()
meta_definition(meta=meta, conn=conn)

# map classes, automap does not gather all classes
Base = automap_base(metadata=meta)

# map other classes; TODO: classes are locals of func
Located, Scheduled, Typified = other_classes()

Base.prepare()

# simplify class names
Datatype, Projection, Spatial, Timeseries, Year, Point =\
    Base.classes.datatype, Base.classes.projection, Base.classes.spatial,\
    Base.classes.timeseries, Base.classes.year,\
    Base.classes.weather_measurement_point

session = sessionmaker(bind=conn)()

print('Retrieve data...')
query = session.query(Point.name, Point.type_of_generation, Point.geom)
Points =  [(name, type_of_generation, shape.to_shape(geom))
           for name, type_of_generation, geom in query.all()]

print('Done!')

@@ -1865,32 +1865,151 @@
 flect(bind=conn,
+ schema='model_draft',%0A                 only=%5B'ego_weather_measurement_point'%5D)  %0A        %0A    #meta.reflect(bind=conn,
  schema='public'
@@ -2010,24 +2010,25 @@
 ublic',%0A    
+#
             
@@ -2063,16 +2063,56 @@
 point'%5D)
+  # table with given/ own defined points
 %0A%0A%0Adef o
@@ -3011,20 +3011,24 @@
 tion = '
-home
+coastdat
 '%0Aconn =
@@ -3574,24 +3574,72 @@
 s.year,%5C%0A   
+ Base.classes.ego_weather_measurement_point%0A   #
  Base.classe
@@ -3789,16 +3789,33 @@
 eration,
+ Point.scnenario,
  Point.g
@@ -3856,16 +3856,27 @@
 eration,
+ scnenario,
  shape.t
@@ -3930,16 +3930,27 @@
 eration,
+ scnenario,
  geom in

add scenario