import elixir
from elixir import Field, ManyToOne, OneToMany, OneToOne, ColumnProperty, using_options, using_options_defaults
from sqlalchemy import types as T
from sqlalchemy import sql, Index

import datetime
from functools import partial
from collections import namedtuple

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

#
# DB Setup
#

elixir.metadata.bind = "sqlite:///test.sqlite"
elixir.metadata.bind.echo = True

#
# Global definitions
#

ReqField = partial(Field, required = True)
ExpNum = T.Numeric(scale = 2, precision = 10)

class Entity (elixir.Entity):
    using_options(abstract = True)

    using_options_defaults(shortnames = True)

#
# Database Entities
#

class Category (Entity):
    name = Field(T.String(50), unique = True)

    parent = ManyToOne('Category')
    children = OneToMany('Category')

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

class Expense (Entity):
    using_options(abstract = True)

    description = Field(T.String(50))
    expense     = ReqField(ExpNum)
    category    = ManyToOne('Category', required = True, innerjoin = True)

class SingleExpense (Expense):
    year = ReqField(T.Integer)
    month = ReqField(T.SmallInteger)
    day = ReqField(T.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  = ReqField(T.SmallInteger)
    start   = ReqField(T.Date, index = True)
    end     = ReqField(T.Date, index = True)

    next = OneToOne('ConstExpense', inverse = 'prev')
    prev = ManyToOne('ConstExpense')
    
    monthly = ColumnProperty(lambda c: sql.cast(c.expense / c.months, ExpNum))
    
    @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')):
    __slots__ = ()
    
    @property
    def constsum (self):
        c = ConstExpense.of_month(self.date.month, self.date.year)
        return c.value(sql.functions.sum(ConstExpense.monthly)) or None

    @property
    def consts (self):
        return ConstExpense.of_month(self.date.month, self.date.year).all()

    @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)

#
# Rest
#

elixir.setup_all()

session = elixir.session

#
# Extra indizes have to be here
#

Index('idx_single_date', SingleExpense.year, SingleExpense.month)

if __name__ == "__main__":
    elixir.create_all()