from sqlalchemy import Column, Integer, Boolean, String, ForeignKey, DateTime, Date, \
    SmallInteger, desc
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

from .connection import session

import datetime
import logging

Base = declarative_base()
logger = logging.getLogger('models')


class ModelBase:
    def save(self, commit=True):
        s = session()
        s.add(self)
        if commit:
            s.commit()

    @property
    def gender_text(self):
        if self.gender == 1:
            return 'Masculino'
        elif self.gender == 2:
            return 'Femenino'
        else:
            return 'Mixto'

    @property
    def is_group(self):
        return False


class Competition(Base):
    __tablename__ = 'exporter_exportercompetition'
    id = Column(Integer, primary_key=True)
    competition_id = Column(Integer)
    ts_created = Column(DateTime, default=datetime.datetime.utcnow)
    name = Column(String)
    date = Column(Date, default=datetime.datetime.utcnow)
    customer = Column(String)
    official = Column(Boolean)
    multi_table = Column(Boolean)
    enable_4 = Column(Boolean)
    weighing = Column(Boolean)
    results = Column(Boolean)

    @property
    def categories(self):
        """ Return all categories as the database only includes a single competition """
        return session().query(Category).all()


class Dojo(Base):
    __tablename__ = 'exporter_exporterdojo'
    id = Column(Integer, primary_key=True)
    dojo_id = Column(Integer)
    name = Column(String)
    sensei = Column(String)
    is_league = Column(Boolean)

    def __str__(self):
        return self.name


class Member(Base, ModelBase):
    __tablename__ = 'exporter_exportermember'
    id = Column(Integer, primary_key=True)
    member_id = Column(Integer, unique=True)
    first_names = Column(String)
    last_names = Column(String)
    birthday = Column(Date)
    gender = Column(SmallInteger)
    dojo_id = Column(Integer, ForeignKey('exporter_exporterdojo.dojo_id',
                                         ondelete='CASCADE'))
    dojo = relationship('Dojo', back_populates='members', lazy='subquery')

    @property
    def label(self):
        return f'{self.first_names} {self.last_names}'

    @staticmethod
    def calculate_age(birthday, date=None):
        """
        If parameter date is set, calculate the age at this date
        """
        local = datetime.date.today() if date is None else date
        years = (local.year - birthday.year - 1)
        date = datetime.date(birthday.year, local.month, local.day)
        if date >= birthday:
            years += 1

        return years

    @property
    def age(self):
        return self.calculate_age(self.birthday)

    @staticmethod
    def count():
        return session().query(Member.id).count()

    @staticmethod
    def next():
        return session().query(
            Member.member_id
        ).order_by(desc(Member.member_id)).first().member_id + 1


class Group(Base):
    __tablename__ = 'exporter_exportergroup'
    id = Column(Integer, primary_key=True)
    group_id = Column(Integer)
    name = Column(String)
    dojo_id = Column(Integer, ForeignKey('exporter_exporterdojo.dojo_id',
                                         ondelete='CASCADE'))
    dojo = relationship('Dojo', back_populates='groups', lazy='subquery')

    @property
    def label(self):
        return self.name

    @property
    def is_group(self):
        return True


class Category(Base, ModelBase):
    __tablename__ = 'exporter_exportercategory'
    id = Column(Integer, primary_key=True)
    category_id = Column(Integer)
    label = Column(String)
    modality = Column(SmallInteger)
    number = Column(SmallInteger)
    grouped = Column(Boolean)
    gender = Column(SmallInteger)
    age_from = Column(SmallInteger)
    age_to = Column(SmallInteger)

    @staticmethod
    def objects():
        return session().query(Category)

    @property
    def modality_text(self):
        return 'Kata' if self.modality == 1 else 'Kumite'

    def ages(self):
        return range(self.age_from, self.age_to+1)

    def genders(self):
        init = 1 if self.gender == 3 else self.gender
        return range(init, self.gender + 1)

    def __repr__(self):
        return f'<Category {self.category_id}>'


class CategoryMember(Base, ModelBase):
    __tablename__ = 'exporter_exportercategorymember'
    id = Column(Integer, primary_key=True)
    category_id = Column(Integer, ForeignKey('exporter_exportercategory.category_id',
                                             ondelete='CASCADE'))
    category = relationship('Category', back_populates='members', lazy='subquery')
    index = Column(SmallInteger)
    member_id = Column(Integer, ForeignKey('exporter_exportermember.member_id',
                                           ondelete='CASCADE'))
    member = relationship('Member', back_populates='categories', lazy='subquery')

    @property
    def item(self):
        return self.member

    @property
    def item_id(self):
        return self.member_id

    def update(self, **kwargs):
        s = session()
        s.query(CategoryMember).filter(CategoryMember.id==self.id).update(kwargs)
        s.commit()

    def __repr__(self):
        return f'<CategoryMember {self.id}>'


class CategoryGroup(Base, ModelBase):
    __tablename__ = 'exporter_exportercategorygroup'
    id = Column(Integer, primary_key=True)
    category_id = Column(Integer, ForeignKey('exporter_exportercategory.category_id',
                                             ondelete='CASCADE'))
    category = relationship('Category', back_populates='groups', lazy='subquery')
    group_id = Column(Integer, ForeignKey('exporter_exportergroup.group_id',
                                          ondelete='CASCADE'))
    group = relationship('Group', back_populates='categories', lazy='subquery')
    index = Column(SmallInteger)

    @property
    def item(self):
        return self.group

    @property
    def item_id(self):
        return self.group_id

    def update(self, **kwargs):
        s = session()
        s.query(CategoryGroup).filter(CategoryGroup.id==self.id).update(kwargs)
        s.commit()

    def __repr__(self):
        return f'<CategoryGroup {self.id}>'


class CategoryFight(Base, ModelBase):
    __tablename__ = 'categoryfight'
    id = Column(Integer, primary_key=True)
    ts_created = Column(Date, default=datetime.datetime.utcnow)
    index = Column(SmallInteger)
    # -- IMPORTANT: the code stores 'category_id' instead of 'Category.id'
    category_id = Column(Integer, ForeignKey('exporter_exportercategory.category_id',
                                             ondelete='CASCADE'))
    category = relationship('Category', back_populates='fights', lazy='subquery')
    winner_member_id = Column(Integer,
                              ForeignKey('exporter_exportercategorymember.member_id',
                                         ondelete='CASCADE'))
    winner_member = relationship('CategoryMember', lazy='subquery',
                                 foreign_keys=[winner_member_id])
    loser_member_id = Column(Integer,
                             ForeignKey('exporter_exportercategorymember.member_id',
                                        ondelete='CASCADE'))
    loser_member = relationship('CategoryMember', lazy='subquery',
                                foreign_keys=[loser_member_id])
    winner_group_id = Column(Integer,
                              ForeignKey('exporter_exportercategorygroup.group_id',
                                         ondelete='CASCADE'))
    winner_group = relationship('CategoryGroup', lazy='subquery',
                                 foreign_keys=[winner_group_id])
    loser_group_id = Column(Integer,
                             ForeignKey('exporter_exportercategorygroup.group_id',
                                        ondelete='CASCADE'))
    loser_group = relationship('CategoryGroup', lazy='subquery',
                               foreign_keys=[loser_group_id])
    third_place = Column(Boolean, default=False)

    @property
    def item(self):
        """ Method used to display the (winner) label in the graphicsview """
        return (self.winner_group.item if self.category.grouped else
                self.winner_member.item)

    @property
    def item_id(self):
        return (self.winner_group.item_id if self.category.grouped else
                self.winner_member.item_id)

    def __repr__(self):
        return f'<CategoryFight {self.id}>'


Dojo.members = relationship('Member', order_by=Member.id, back_populates='dojo',
                            lazy='subquery')
Dojo.groups = relationship('Group', order_by=Group.id, back_populates='dojo',
                           lazy='subquery')

Member.categories = relationship('CategoryMember', order_by=CategoryMember.id,
                                 back_populates='member', lazy='subquery')
Category.members = relationship('CategoryMember', order_by=CategoryMember.id,
                                 back_populates='category', lazy='subquery')
Category.groups = relationship('CategoryGroup', order_by=CategoryGroup.id,
                               back_populates='category', lazy='subquery')
Category.fights = relationship('CategoryFight', order_by=CategoryFight.id,
                               back_populates='category', lazy='subquery')

Group.categories = relationship('CategoryGroup', order_by=CategoryGroup.id,
                                 back_populates='group', lazy='subquery')


def read_competition(opts=None):
    s = session(opts)
    return s.query(Competition).first()


_competition = None


def get_current_competiton():
    return _competition


def competition_load(**kwargs):
    s = session(migrate=True, reinit=True, **kwargs)
    competition = s.query(Competition).first()

    updated = False
    for category in competition.categories:
        records = category.groups if category.grouped else category.members
        model = CategoryGroup if category.grouped else CategoryMember
        #print(category.label, "=>", len(records))
        if len(records) == 1 and records[0].index != 10:
            s.query(model).filter(model.id==records[0].id).update({'index':14})
            updated = True
        elif len(records) == 2 and (records[0].index != 12 or records[1].index != 13):
            s.query(model).filter(model.id==records[0].id).update({'index':12})
            s.query(model).filter(model.id==records[1].id).update({'index':13})
            updated = True

    if updated:
        logger.info("Updating database due index refresh")
        s.commit()
        competition = s.query(Competition).first()

    global _competition
    _competition = competition
    return competition
