from flask.ext.sqlalchemy import SQLAlchemy
from sqlalchemy import sql
from sqlalchemy.ext.declarative import declared_attr

import datetime
import decimal
from functools import partial
from collections import namedtuple

from . import app

db = SQLAlchemy(app)

__all__ = ["db", "Category", "SingleExpense", "ConstExpense", "CatExpense", "MonthExpense"]

ReqColumn = partial(db.Column, nullable = False)
ExpNum = db.Numeric(scale = 2, precision = 10)

def to_exp(d):
    """Converts decimal into expense"""
    return d.quantize(decimal.Decimal('.01'), rounding = decimal.ROUND_UP)

#
# Database Entities
class Model (db.Model):
    __abstract__ = True

    id = db.Column(db.Integer, primary_key=True)

    @declared_attr
    def __tablename__ (cls):
        return cls.__name__.lower()

    @classmethod
    def get_by (cls, *args, **kwargs):
        return cls.query.filter_by(*args, **kwargs).first()

    @classmethod
    def get_by_or_404 (cls, *args, **kwargs):
        return cls.query.filter_by(*args, **kwargs).first_or_404()

    @classmethod
    def get (cls, *args, **kwargs):
        return cls.query.get(*args, **kwargs)

    @classmethod
    def get_or_404 (cls, *args, **kwargs):
        return cls.query.get_or_404(*args, **kwargs)

class Category (Model):
    name = ReqColumn(db.Unicode(50), unique = True)
    parent_id = db.Column(db.Integer, db.ForeignKey('category.id'))

    children = db.relationship('Category',
                            backref=db.backref('parent', remote_side="Category.id"))

    def __init__ (self, name, parent_id = None):
        Model.__init__(self)
        self.name = name
        self.parent_id = parent_id

    def __repr__ (self):
        if self.parent:
            return '<Category "%s" of "%s">' % (self.name, self.parent.name)
        else:
            return '<Category "%s">' % self.name

class Expense (Model):
    __abstract__ = True

    description = db.Column(db.Unicode(50))
    expense     = ReqColumn(ExpNum)

    @declared_attr
    def category_id(cls):
        return ReqColumn(db.Integer, db.ForeignKey(Category.id))

    @declared_attr
    def category(cls):
        return db.relationship(Category, innerjoin = True)

class SingleExpense (Expense):
    year = ReqColumn(db.Integer)
    month = ReqColumn(db.SmallInteger)
    day = ReqColumn(db.SmallInteger)

    @classmethod
    def of_month (cls, month, year):
        comp = sql.and_(
                cls.month == month,
                cls.year == year)

        return cls.query.filter(comp)

    @property
    def date (self):
        return datetime.date(self.year, self.month, self.day)

    @date.setter
    def date (self, d):
        self.year = d.year
        self.month = d.month
        self.day = d.day

class ConstExpense (Expense):
    months  = ReqColumn(db.SmallInteger)
    start   = ReqColumn(db.Date, index = True)
    end     = ReqColumn(db.Date, index = True)
    prev_id = db.Column(db.Integer, db.ForeignKey('constexpense.id'))

    prev = db.relationship('ConstExpense', remote_side = "ConstExpense.id", uselist = False,
                         backref=db.backref('next', uselist = False))

    @property
    def monthly(self):
        return to_exp(self.expense / self.months)
    
    @classmethod
    def of_month (cls, month, year):
        d = datetime.date(year, month, 1)
        return cls.query.filter(sql.between(d, cls.start, cls.end))

#
# Work entities (not stored in DB)
#
class CatExpense (namedtuple('CatExpense', 'cat expense exps')):
    __slots__ = ()

    @property
    def all (self):
        return self.exps.order_by(SingleExpense.day).all()

class MonthExpense (namedtuple('MonthExpense', 'date catexps')):
    
    def __init__ (self, *args, **kwargs):
        self._consts = None
        super(MonthExpense, self).__init__(*args, **kwargs)

    @property
    def consts (self):
        if self._consts is None:
            self._consts = ConstExpense.of_month(self.date.month, self.date.year).all()

        return self._consts

    @property
    def constsum (self):
        s = sum(c.monthly for c in self.consts)
        return s or 0

    @property
    def sum (self):
        return self.constsum + sum(x.expense for x in self.catexps)

    @property
    def all (self):
        return SingleExpense.of_month(self.date.month, self.date.year).order_by(SingleExpense.day).all()

    def __str__ (self):
        return '<MonthExpense of "%s": %s>' % (self.date, self.sum)

#
# Extra indizes have to be here
#

db.Index('idx_single_date', SingleExpense.year, SingleExpense.month)
db.Index('idx_start_end', ConstExpense.start, ConstExpense.end)