# coding: utf-8
"""
class Timeseries (Base)
=======================
"""
from sqlalchemy import Column, DateTime, Float, ForeignKey, Integer, text, String, \
                       Text, CheckConstraint, UniqueConstraint, PrimaryKeyConstraint, \
                       Table
from sqlalchemy.orm import relationship
from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy.ext.declarative import declarative_base
from toardb.stationmeta.models import StationmetaCore
from toardb.variables.models import Variable
from toardb.contacts.models import Person

Base = declarative_base()
metadata = Base.metadata

# many-to-many relationships
timeseries_timeseries_roles_table = Table('timeseries_timeseries_roles', Base.metadata,
    Column('timeseries_id', Integer, ForeignKey('timeseries.id')),
    Column('role_id', Integer, ForeignKey('timeseries_roles.id'))
)


class TimeseriesRole(Base):
    """ Table "public.timeseries_roles"

    +---------------+---------+-----------+----------+----------------------------------------------+
    |    Column     |  Type   | Collation | Nullable |                   Default                    |
    +===============+=========+===========+==========+==============================================+
    | id            | integer |           | not null | nextval('timeseries_roles_id_seq'::regclass) |
    +---------------+---------+-----------+----------+----------------------------------------------+
    | role          | integer |           | not null |                                              |
    +---------------+---------+-----------+----------+----------------------------------------------+
    | status        | integer |           | not null |                                              |
    +---------------+---------+-----------+----------+----------------------------------------------+
    | person_id     | integer |           | not null |                                              |
    +---------------+---------+-----------+----------+----------------------------------------------+
    Indexes:
        "timeseries_roles_pkey" PRIMARY KEY, btree (id)
        "timeseries_roles_role_person_id_uniq" UNIQUE CONSTRAINT, btree (role, person_id)
        "timeseries_roles_person_id" btree (person_id)
    Check constraints:
        "timeseries_roles_role_check" CHECK (role >= 0)
        "timeseries_roles_status_check" CHECK (status >= 0)
    Foreign-key constraints:
        "timeseries_roles_person_id_3e26200e_fk_persons_id" FOREIGN KEY (person_id) REFERENCES persons(id) DEFERRABLE INITIALLY DEFERRED
    """

    __tablename__ = 'timeseries_roles'
    __table_args__ = (
        CheckConstraint('role >= 0'),
        CheckConstraint('status >= 0'),
        UniqueConstraint('role', 'person_id'),
        {'extend_existing': True}
    )

    id = Column(Integer, primary_key=True, server_default=text("nextval('timeseries_roles_id_seq'::regclass)"))
    role = Column(Integer, nullable=False)
    status = Column(Integer, nullable=False)
# do not use string declaration here (not working for pytest)
# use the explicit class name here,
# see: https://groups.google.com/forum/#!topic/sqlalchemy/YjGhE4d6K4U
    person_id = Column(ForeignKey(Person.id, deferrable=True, initially='DEFERRED'), nullable=False, index=True)
    person = relationship(Person)

    timeseries = relationship("Timeseries",
        secondary=timeseries_timeseries_roles_table,
        backref="roles")


class Timeseries(Base):
    """ Table "public.timeseries"

    +---------------------+--------------------------+-----------+----------+----------------------------------------+
    |       Column        |           Type           | Collation | Nullable |                Default                 |
    +=====================+==========================+===========+==========+========================================+
    | id                  | integer                  |           | not null | nextval('timeseries_id_seq'::regclass) |
    +---------------------+--------------------------+-----------+----------+----------------------------------------+
    | label               | character varying(128)   |           | not null |                                        |
    +---------------------+--------------------------+-----------+----------+----------------------------------------+
    | order               | integer                  |           | not null |                                        |
    +---------------------+--------------------------+-----------+----------+----------------------------------------+
    | access_rights       | integer                  |           | not null |                                        |
    +---------------------+--------------------------+-----------+----------+----------------------------------------+
    | sampling_frequency  | integer                  |           | not null |                                        |
    +---------------------+--------------------------+-----------+----------+----------------------------------------+
    | aggregation         | integer                  |           | not null |                                        |
    +---------------------+--------------------------+-----------+----------+----------------------------------------+
    | data_start_date     | timestamp with time zone |           | not null |                                        |
    +---------------------+--------------------------+-----------+----------+----------------------------------------+
    | data_end_date       | timestamp with time zone |           | not null |                                        |
    +---------------------+--------------------------+-----------+----------+----------------------------------------+
    | measurement_method  | character varying(128)   |           | not null |                                        |
    +---------------------+--------------------------+-----------+----------+----------------------------------------+
    | sampling_height     | double precision         |           | not null |                                        |
    +---------------------+--------------------------+-----------+----------+----------------------------------------+
    | additional_metadata | jsonb                    |           | not null |                                        |
    +---------------------+--------------------------+-----------+----------+----------------------------------------+
    | date_added          | timestamp with time zone |           | not null |                                        |
    +---------------------+--------------------------+-----------+----------+----------------------------------------+
    | date_modified       | timestamp with time zone |           | not null |                                        |
    +---------------------+--------------------------+-----------+----------+----------------------------------------+
    | station_id          | integer                  |           |          |                                        |
    +---------------------+--------------------------+-----------+----------+----------------------------------------+
    | variable_id         | integer                  |           |          |                                        |
    +---------------------+--------------------------+-----------+----------+----------------------------------------+
    Indexes:
        "timeseries_pkey" PRIMARY KEY, btree (id)
        "timeseries_station_id_variable_id_label_uniq" UNIQUE CONSTRAINT, btree (station_id, variable_id, label)
        "timeseries_station_id" btree (station_id)
        "timeseries_variable_id" btree (variable_id)
    Check constraints:
        "timeseries_access_rights_check" CHECK (access_rights >= 0)
        "timeseries_aggregation_check" CHECK (aggregation >= 0)
        "timeseries_order_check" CHECK ("order" >= 0)
        "timeseries_sampling_frequency_check" CHECK (sampling_frequency >= 0)
    Foreign-key constraints:
        "timeseries_station_id_fk_stationmeta_core_id" FOREIGN KEY (station_id) REFERENCES stationmeta_core(id) DEFERRABLE INITIALLY DEFERRED
        "timeseries_variable_id_fk_variables_id" FOREIGN KEY (variable_id) REFERENCES variables(id) DEFERRABLE INITIALLY DEFERRED
    Referenced by:
        TABLE "data" CONSTRAINT "data_timeseries_id_fk_timeseries_id" FOREIGN KEY (timeseries_id) REFERENCES timeseries(id) DEFERRABLE INITIALLY DEFERRED
        TABLE "timeseries_timeseries_annotations" CONSTRAINT "timeseries_timeseries_annotations_timeseries_id_fk_timeseries_id" FOREIGN KEY (timeseries_id) REFERENCES timeseries(id) DEFERRABLE INITIALLY DEFERRED
        TABLE "timeseries_timeseries_roles" CONSTRAINT "timeseries_timeseries_roles_timeseries_id_fk_timeseries_id" FOREIGN KEY (timeseries_id) REFERENCES timeseries(id) DEFERRABLE INITIALLY DEFERRED
        TABLE "timeseries_timeseries_programmes" CONSTRAINT "timeseries_timeseries_programmes_timeseries_id_fk_timeseries_id" FOREIGN KEY (timeseries_id) REFERENCES timeseries(id) DEFERRABLE INITIALLY DEFERRED
    """

    __tablename__ = 'timeseries'
    __table_args__ = (
        CheckConstraint('"order" >= 0'),
        CheckConstraint('access_rights >= 0'),
        CheckConstraint('aggregation >= 0'),
        CheckConstraint('sampling_frequency >= 0'),
        UniqueConstraint('station_id', 'variable_id', 'label'),
    )

    id = Column(Integer, primary_key=True, server_default=text("nextval('timeseries_id_seq'::regclass)"))
    label = Column(String(128), nullable=False)
    order = Column(Integer, nullable=False)
    access_rights = Column(Integer, nullable=False)
    sampling_frequency = Column(Integer, nullable=False)
    aggregation = Column(Integer, nullable=False)
    data_start_date = Column(DateTime(True), nullable=False)
    data_end_date = Column(DateTime(True), nullable=False)
    measurement_method = Column(String(128), nullable=False)
    sampling_height = Column(Float(53), nullable=False)
    additional_metadata = Column(JSONB(astext_type=Text()), nullable=True)

# date_added, date_modified:
# how to have them changed automatically

    date_added = Column(DateTime(True), nullable=False)
    date_modified = Column(DateTime(True), nullable=False)
# do not use string declaration here (not working for pytest)
# use the explicit class name here,
# see: https://groups.google.com/forum/#!topic/sqlalchemy/YjGhE4d6K4U
    station_id = Column(ForeignKey(StationmetaCore.id, deferrable=True, initially='DEFERRED'), index=True)
    variable_id = Column(ForeignKey(Variable.id, deferrable=True, initially='DEFERRED'), index=True)
# how to reactivate the following two lines?!
#   station = relationship('StationmetaCore')
#   variable = relationship('Variable')