# -*- coding: utf-8 -*- 

from sqlalchemy import Column, Integer, String, Sequence, Boolean, DateTime, Numeric, Enum

from sqlalchemy.ext.declarative import declarative_base

#relationship

from sqlalchemy import ForeignKey

from sqlalchemy.orm import relationship, backref, object_session

from sqlalchemy import and_

from sqlalchemy.orm.exc import DetachedInstanceError 

import sqlalchemy.sql.functions as func

import job

from ..error import JobNotFound

Base = declarative_base()

class Userright(Base):

    """Allowed offers for one user. Default routes are sorted by **default** value."""

    __tablename__ = 'userright'

    user_name = Column('user', String(100), ForeignKey('apiuser.name'), primary_key=True)

    """username"""

    offer_name = Column('offer', String(100), ForeignKey('offer.name'), primary_key=True)

    """offername"""

    default = Column(Integer)

    """sorting defaults routes with this value"""

    offer = relationship("Offer")

    """connected :class:`Offer` object"""

    user = relationship("User")

    """connected :class:`User` object"""

    def __init__(self, offer, default=None):

        """Constructor of Userright class.

        :param `Offer` offer: a offer object

        :param integer default: default value

        """

        self.offer = offer

        self.default = default

    @property

    def bill(self):

        """returns a list of unbilled messages grouped by Job.info"""

        query = [ func.count(Message.id).label('anz'),      # anz of messages

                  func.sum(Message.price).label("price"),   # price of the messages

                  Job.info.label('info'),                   # info tag

                  ]

        filters = [ Message.isBilled==False,                # only unbilled messages

                   Job.user==self.user,                     # only jobs connected to user

                   Message.offer==self.offer,               # only messages in the right offer

                   Message.job_id==Job.id,                  # join Message and Job

                ]

        return object_session(self).query(*query).filter(and_(*filters)).group_by(Job.info)

class Offer(Base):

    """All possible Offers over a Message can be sended. **provider**, **typ** and **route** are used to get the data form configuration file."""

    __tablename__ = "offer"

    name = Column(String(100), primary_key=True)

    """name of the offer"""

    provider = Column(String(100))

    """provider name"""

    route = Column(String(100))

    """route of the provider"""

    typ = Column(String(100))

    """typ of message"""

    def __init__(self, **kwargs):

        Base.__init__(self,**kwargs)

    @classmethod

    def get(cls, session, provider, route, typ):

        """returns a Offer object."""

        return session.query(cls).filter(and_(cls.provider==provider, cls.route==route, cls.typ==typ)).first()

    @classmethod

    def routes(cls, session, typ):

        """returns a query object of all possible offers.

        :param string typ: get all offers that support this typ

        """

        return session.query(cls).filter_by(typ=typ)

    @classmethod

    def typs(cls, session):

        """returns a list of all possible types.

        """

        return session.query(cls.typ).distinct()

    @classmethod

    def providers(cls, session, typ):

        """returns a list of all possible providers.

        :param string typ: get all providers that support this typ

        """

        return session.query(cls.provider).filter_by(typ=typ).distinct()

class Message(Base):

    """A message that has created costs.

    **isBilled** is False since the bill is paid.

    """

    __tablename__ = "message"

    id = Column(Integer, Sequence('message_id_seq'), primary_key=True)

    """primary key of the message"""

    recipient = Column(String(100))

    """string representation of the recipient"""

    isBilled = Column(Boolean)

    """is bill paid?"""

    date = Column(DateTime)

    """date of sending the message"""

    price = Column(Numeric(8,4))

    """price of sending the message"""

    count = Column(Integer)

    """Count of sended messages"""

    exID = Column(String(100))

    """external API id """

    job_id = Column("job", String(40), ForeignKey('job.id'))

    """id of the connected job"""

    job = relationship("Job", backref=backref('messages'))

    """connected :class:`Job` object"""

    offer_id = Column("offer",String(100), ForeignKey('offer.name'))

    """sended message over this offer woth ithe offer.name"""

    offer = relationship("Offer", backref=backref('messages'))

    """connected :class:`Offer` object"""

    def __init__(self, **kwargs):

        Base.__init__(self,**kwargs)

class Job(Base):

    """A complete Job.

    - **status** show the status of the job (``init``, ``started``, ``sending``, ``sended`` or ``error``). 

    """

    __tablename__ = "job"

    id = Column(Integer, Sequence('job_id_seq'), primary_key=True)

    """job id"""

    info = Column(String(100))

    """job info, for billing porpuse"""

    status = Column(Enum("init","started","sending","sended","error"))

    """status of a job"""

    user_id = Column("user", String(100), ForeignKey('apiuser.name'))

    """connected user id"""

    user = relationship("User", backref=backref('jobs'))

    """connected :class:`User` object"""

    def __init__(self, **kwargs):

        """

        .. automethod:: __repr__

        """

        Base.__init__(self,**kwargs)

    @property

    def extend(self):

        """returns the connected :class:`iro.model.job.ExJob`"""

        return job.exJobs[self.id]

    def __repr__(self):

        """string representation of the Job class.

        :return: ``<Job('id' ,'info', 'status', 'user_id')>``

        """

        try:

            return "<Job('%s' ,'%s', '%s', '%s')>"%(self.id,self.info, self.status, self.user_id)

        except DetachedInstanceError:

            return Base.__repr__(self)

    @classmethod

    def get(cls, session, id):

        """returns a job object from a given id"""

        return session.query(cls).filter_by(id=id).first()

class User(Base):

    """One user in iro"""

    __tablename__ = "apiuser"

    name = Column(String(100), primary_key=True)

    """Username"""

    ng_kunde = Column(Integer)

    """Connection to the netzguerilla userdatabase"""

    apikey = Column(String(50),unique=True)

    """apikey only [0-9a-f]"""

    rights = relationship('Userright')

    """all allowed offers to send with."""

    def __init__(self, name, apikey):

        """Constructor of User class.

        :param string name: username

        :param string apikey: apikey for the user

        .. automethod:: __repr__

        """

        self.name=name

        self.apikey=apikey

    def __repr__(self):

        """string representation of the user class.

        :return: ``<User('name', 'apikey')>``

        """

        try:

            return "<User('%s','%s')>"%(self.name,self.apikey)

        except DetachedInstanceError:

            return Base.__repr__(self)

    def routes(self, typ, default = False):

        """returns a query object to get all possible routes for a given typ

        :param string typ: the typ

        :param boolean default: use only default routes

        """

        filters=[User.name == self.name,

                Offer.typ == typ, 

                ]

        if default:

            filters.append(Userright.default != None)

        return object_session(self).query(Userright.offer_name).join(Offer,User).filter(and_(*filters)).order_by(Userright.default)

    def providers(self, typ, default = False):

        """return a query object for all possible providers for a given typ

        :param string typ: the typ

        :param boolean default: use only default routes

        """

        filters=[User.name == self.name,

                Offer.typ == typ, 

                ]

        if default:

            filters.append(Userright.default != None)

        return object_session(self).query(Offer.provider).join(Userright,User).filter(and_(*filters))

    def has_right(self, typ, offer_name = None, provider = None, route = None):

        """if a user has the right to use a offer, provider e. al. (arguments are and connected).

        :param string typ: the typ

        :param string offer_name: offer name

        :param string provider: provider name

        :param string route: a route name

        :return: offer_name or  None (not allwoed)

        :raises: :class:`sqlalchemy.orm.exc.MultipleResultsFound` if not a single offer match"""

        filters=[User.name == self.name,

                Offer.typ == typ, 

                ]

        if offer_name:

            filters.append(Userright.offer_name==offer_name)

        if provider:

            filters.append(Offer.provider==provider)

        if route:

            filters.append(Offer.route==route)

        return object_session(self).query(Userright.offer_name).join(Offer,User).filter(and_(*filters)).scalar()

    def job(self, id):

        """returns a job object.

        :param integer id: id of a Job

        :return: :class:`Job`

        :raises: :exc:`iro.error.JobNotFound`

        """

        job = object_session(self).query(Job).join(User).filter(and_(User.name == self.name, Job.id==id)).first()

        if job is None:

            raise JobNotFound()

        return job