# -*- 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()

__tables__=["User", "Job", "Message", "Offer", "Userright"]

class Userright(Base):
    """Über welche Routen darf ein Benutzer Daten verschicken und welches sind die Standardrouten (<em>default!=None</em>) für den Benuter. Geordnert werden die Standardrouten nach default."""
    __tablename__ = 'userright'
    user_name = Column('user', String(100), ForeignKey('apiuser.name'), primary_key=True)
    offer_name = Column('offer', String(100), ForeignKey('offer.name'), primary_key=True)
    default = Column(Integer)
    offer = relationship("Offer")
    user = relationship("User")

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

    @property
    def bill(self):
        '''getting 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):
    """Alle Routen über die SMS, Faxe und Mails verschickt werden könnnen. <em>provider</em>, <em>typ</em> und <em>route</em> werden verwendet, um die entsprechenden Zugangsdaten laden zu können."""
    __tablename__ = "offer"
    name = Column(String(100), primary_key=True)
    provider = Column(String(100))
    route = Column(String(100))
    typ = Column(String(100))
  
    @classmethod
    def get(cls, session, provider, route, typ):
        return session.query(cls).filter(and_(cls.provider==provider, cls.route==route, cls.typ==typ)).first()

    @classmethod
    def routes(cls, session, typ):
        return session.query(cls).filter_by(typ=typ)


class Message(Base):
    """Wenn ein Vorgang von Iro Kosten erzeugt hat wird eine neue Zeile eingefügt. Solange nicht bezahlt wurde ist <em>isBilled=0</em>."""
    __tablename__ = "message"
    id = Column(Integer, Sequence('message_id_seq'), primary_key=True)
    recipient = Column(String(100))
    isBilled = Column(Boolean)
    date = Column(DateTime)
    price = Column(Numeric(8,4))
    job_id = Column("job", String(40), ForeignKey('job.id'))
    job = relationship("Job", backref=backref('messages'))
    offer_id = Column("offer",String(100), ForeignKey('offer.name'))
    offer = relationship("Offer", backref=backref('messages'))


class Job(Base):
    """Ein kompletter Auftrag, der an Iro zum verschicken übergeben wird. Status zeigt den generellen Status des Auftrages an (<em>init</em>, <em>started</em>, <em>sending</em>, <em>sended</em> oder <em>error</em>). <em>info</em> wird verwendet um dem Benutzer eine Möglickeit zu geben verschiede Auftragsgruppen zu erstellen."""
    __tablename__ = "job"
    id = Column(Integer, Sequence('job_id_seq'), primary_key=True)
    info = Column(String(100))
    status = Column(Enum("init","started","sending","sended","error"))
    user_id = Column("user", String(100), ForeignKey('apiuser.name'))
    user = relationship("User", backref=backref('jobs'))

    @property
    def extend(self):
        return job.exJobs[self.id]


    def __repr__(self):
        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):
        return session.query(cls).filter_by(id=id).first()

class User(Base):
    """Die Benutzerdatenbank von Iro. <em>ng_kunde</em> ist der verknüpfte netzguerilla.net Benutzer, der die Rechnung zahlt."""
    __tablename__ = "apiuser"
    name = Column(String(100), primary_key=True)
    ng_kunde = Column(Integer)
    apikey = Column(String(50),unique=True)
    rights = relationship('Userright')
    def __init__(self, name, apikey):
        self.name=name
        self.apikey=apikey

    def __repr__(self):
        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'''
        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'''
        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):
        '''returns offer_name, if the user is allowed to use offer otherwise None
           ->raise 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):
        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