from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.orm import relationship

db = SQLAlchemy()


#
#
#

class Formable:
    """
    Parent class allowing some html form  facilities

    """
    export_keys = []

    def from_request(self, form_request):
        """
        Get a form request structure and fill in our fields

        :param form_request:
        :return:
        """
        for key in self.export_keys:
            setattr(self, key, form_request.form.get(key))

    def to_struct(self):
        """
        Export the orm object to a structure easily used in jinja

        :return:  nothing
        """
        _struct = {'id': self.id}
        for key in self.export_keys:
            _value = getattr(self, key)
            _struct[key] = '' if _value is None else _value
        return _struct


#
# Categorized projects
#
# There is one label list,
# each label belongs to one or more categories.
#
# The projects are labelled by one or more label.
#
# Thus this is modeled with classes
#    Project, Label and Category
# And many_to_many association are done through
#    ProjectLabel and CategoryLabel
#

class Project(db.Model, Formable):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)
    labels = relationship("ProjectLabel", back_populates="project")

    # add keys to import/export
    export_keys = ['name']

    def from_request(self, form_request):
        """
        overide parent method to deal with category labels

        :param form_request:
        :return:
        """
        super(Project, self).from_request(form_request)
        self.labels.clear()
        form_labels = []
        for _c in Category.query.all():
            form_labels = form_labels + form_request.form.getlist(_c.name)
        for label_id in form_labels:
            n_l = Label.query.get(int(label_id))
            n_pl = ProjectLabel(project=self, label=n_l)
            self.labels.append(n_pl)

    def to_struct(self):
        """
        overide parent method to include one key: agent.fullname

        Mainly we add the 'labels' element containing list of label names
        and we add the 'category_labels' element containing a dict
        where category key contains labels for that project.
        :return:
        """
        _struct = super(Project, self).to_struct()
        _struct['labels'] = [_l.label.name for _l in self.labels]
        _struct['category_labels'] = {}
        for _c in Category.query.all():
            category_labels = []
            for _l in self.labels:
                if _l.label in [_cl.label for _cl in _c.labels]:
                    category_labels.append(_l.label.name)
            _struct['category_labels'][_c.name] = category_labels
        return _struct


class ProjectLabel(db.Model):
    """
    Labelling projects.
    On project can have many labels.
    And one label will be set to many projects
    """
    id = db.Column(db.Integer, primary_key=True)
    project_id = db.Column(db.Integer, db.ForeignKey('project.id'))
    label_id = db.Column(db.Integer, db.ForeignKey('label.id'))
    project = relationship("Project", back_populates="labels")
    label = relationship("Label", back_populates="projects")


class Label(db.Model, Formable):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String, unique=True)
    category_id = db.Column(db.Integer, db.ForeignKey('category.id'))
    projects = relationship("ProjectLabel", back_populates="label")
    categories = relationship("CategoryLabel", back_populates="label")

    export_keys = ['name']

    def from_request(self, form_request):
        """
        overide parent method to deal with category labels

        :param form_request:
        :return:
        """
        super(Label, self).from_request(form_request)
        self.categories.clear()
        for _c_id in form_request.form.getlist("categories"):
            n_c = Category.query.get(int(_c_id))
            _cl = CategoryLabel(category=n_c, label=self)
            self.categories.append(_cl)

    def to_struct(self):
        """
        overide parent method to include one key: categories

        Mainly we add the 'categories' element containing list of categories names
        :return:
        """
        _struct = super(Label, self).to_struct()
        _struct['categories'] = [_cl.category.name for _cl in self.categories]
        return _struct


class CategoryLabel(db.Model):
    """
    Categorizing labels:
    one label can be added to many categories
    one category hosts many labels
    """
    id = db.Column(db.Integer, primary_key=True)
    category_id = db.Column(db.Integer, db.ForeignKey('category.id'))
    label_id = db.Column(db.Integer, db.ForeignKey('label.id'))
    category = relationship("Category", back_populates="labels")
    label = relationship("Label", back_populates="categories")


class Category(db.Model, Formable):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)
    labels = relationship("CategoryLabel", back_populates="category")

    export_keys = ['name']

    def from_request(self, form_request):
        """
        overide parent method to deal with category labels

        :param form_request:
        :return:
        """
        super(Category, self).from_request(form_request)
        self.labels.clear()
        for _l_id in form_request.form.getlist("labels"):
            n_c = Label.query.get(int(_l_id))
            _cl = CategoryLabel(label=n_c, category=self)
            self.labels.append(_cl)

    def to_struct(self):
        """
        overide parent method to include one key: labels

        Mainly we add the 'labels' element containing list of labels names
        :return:
        """
        _struct = super(Category, self).to_struct()
        _struct['labels'] = [_cl.label.name for _cl in self.labels]
        return _struct


#
# Agents
#

class AgentBap(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(16))
    agents = relationship("Agent", back_populates="bap")


class AgentGrade(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(16))
    agents = relationship("Agent", back_populates="grade")


class AgentStatus(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(16))
    agents = relationship("Agent", back_populates="status")


class Company(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(16))
    agents = relationship("Agent", back_populates="company")


class Agent(db.Model, Formable):
    id = db.Column(db.Integer, primary_key=True)
    firstname = db.Column(db.String(100))
    secondname = db.Column(db.String(100))
    virtual = db.Column(db.Integer)  # integer boolean
    permanent = db.Column(db.Integer)  # integer boolean
    company_id = db.Column(db.Integer, db.ForeignKey('company.id'))
    grade_id = db.Column(db.Integer, db.ForeignKey('agent_grade.id'))
    status_id = db.Column(db.Integer, db.ForeignKey('agent_status.id'))
    bap_id = db.Column(db.Integer, db.ForeignKey('agent_bap.id'))
    grade = relationship("AgentGrade", back_populates="agents")
    bap = relationship("AgentBap", back_populates="agents")
    status = relationship("AgentStatus", back_populates="agents")
    company = relationship("Company", back_populates="agents")

    @property
    def fullname(self):
        return f"{self.secondname} {self.firstname}"

    @property
    def namefull(self):
        return f"{self.firstname} {self.secondname}"

    # has to be set as we inherit Formable
    #
    export_keys = ['firstname', 'secondname', 'virtual', 'permanent', 'company_id', 'status_id', 'grade_id', 'bap_id']

    def to_struct(self):
        """
        overide parent method to include one key: agent.fullname
        :return:
        """
        _struct = super(Agent, self).to_struct()
        _struct['fullname'] = self.fullname
        return _struct


class Service(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), unique=True)
    abbr = db.Column(db.String(50), unique=True)


class Capacity(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), unique=True)


class Period(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), unique=True)
    num_months = db.Column(db.Integer)


class Charge(db.Model, Formable):
    id = db.Column(db.Integer, primary_key=True)
    agent_id = db.Column(db.Integer, db.ForeignKey('agent.id'))
    project_id = db.Column(db.Integer, db.ForeignKey('project.id'))
    service_id = db.Column(db.Integer, db.ForeignKey('service.id'))
    capacity_id = db.Column(db.Integer, db.ForeignKey('capacity.id'))
    period_id = db.Column(db.Integer, db.ForeignKey('period.id'))
    charge_rate = db.Column(db.Integer)

    # Overwrite Formable default to fit our own members
    #
    export_keys = ['agent_id', 'project_id', 'service_id', 'capacity_id', 'period_id', 'charge_rate']