from flask_sqlalchemy import SQLAlchemy

from sqlalchemy.orm import relationship

db = SQLAlchemy()

#
# Categorized projects
#

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

class Category(db.Model):
    id = db.Column(db.Integer, primary_key=True)

    name = db.Column(db.String)
    labels = relationship("Label", back_populates="category")
    projects = relationship("ProjectLabel", back_populates="category")

class Label(db.Model):

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

    category = relationship("Category", back_populates="labels")
    projects = relationship("ProjectLabel", back_populates="label")


class ProjectLabel(db.Model):
    project_id = db.Column(db.Integer, db.ForeignKey('project.id'), primary_key=True)
    category_id = db.Column(db.Integer, db.ForeignKey('category.id'), primary_key=True)
    label_id = db.Column(db.Integer, db.ForeignKey('label.id'))

    project = relationship("Project", back_populates="labels")
    category = relationship("Category", back_populates="projects")
    label = relationship("Label", back_populates="projects")

#
# 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):
    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 export_keys(self):
        return ['firstname', 'secondname', 'virtual', 'permanent', 'company_id', 'status_id', 'grade_id', 'bap_id']

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

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

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

    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:

            # deal with checked value
            if key in ['permanent', 'virtual']:
                setattr(self, key, form_request.form.get(key))
            else:
                setattr(self, key, form_request.form[key])

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