from datetime import datetime
from sqlalchemy import orm

from . import db

class Resume(db.Model):
    """
    Resume model
    """
    __tablename__ = 'resume'
    resume_id = db.Column(db.Integer, primary_key=True)
    deleted = db.Column(db.DateTime)
    created = db.Column(db.DateTime, default=datetime.utcnow)
    last_updated = db.Column(db.DateTime, onupdate=datetime.utcnow)
    title = db.Column(db.String(100))
    description = db.Column(db.String(255))
    candidate_info_id = db.Column(db.Integer, db.ForeignKey('candidate_info.candidate_info_id'), nullable=False)
    
    candidate_info = db.relationship('CandidateInfo', foreign_keys=[candidate_info_id])
    educations = db.relationship(
        'Education',
        secondary='resume_education',
        primaryjoin='and_(Resume.resume_id==ResumeEducation.resume_id, ResumeEducation.deleted==None)',
        secondaryjoin='and_(Education.education_id==ResumeEducation.education_id, Education.deleted==None)',
        order_by='Education.start_date.desc()',
    )
    employments = db.relationship(
        'Employment',
        secondary='resume_employment',
        primaryjoin='and_(Resume.resume_id==ResumeEmployment.resume_id, ResumeEmployment.deleted==None)',
        secondaryjoin='and_(Employment.employment_id==ResumeEmployment.employment_id, Employment.deleted==None)',
        order_by='Employment.start_date.desc()',
    )
    employment_items = db.relationship(
        'EmploymentItem',
        secondary='join(ResumeEmployment, Employment, ResumeEmployment.employment_id==Employment.employment_id)',
        primaryjoin='and_(Resume.resume_id==ResumeEmployment.resume_id, ResumeEmployment.deleted==None)',
        secondaryjoin='and_(Employment.employment_id==EmploymentItem.employment_id, EmploymentItem.deleted==None)',
        order_by='Employment.start_date.desc()',
    )
    all_skills = db.relationship(
        'Skill',
        primaryjoin='and_(Resume.resume_id==Skill.resume_id, Skill.deleted==None)',
    )
    skills = db.relationship(
        'Skill',
        primaryjoin='and_(Resume.resume_id==Skill.resume_id, Skill.deleted==None, Skill.type=="skill")',
    )
    languages = db.relationship(
        'Skill',
        primaryjoin='and_(Resume.resume_id==Skill.resume_id, Skill.deleted==None, Skill.type=="language")',
    )
    technologies = db.relationship(
        'Skill',
        primaryjoin='and_(Resume.resume_id==Skill.resume_id, Skill.deleted==None, Skill.type=="technology")',
    )

class CandidateInfo(db.Model):
    """
    Candidate info model
    """
    __tablename__ = 'candidate_info'
    candidate_info_id = db.Column(db.Integer, primary_key=True)
    deleted = db.Column(db.DateTime)
    created = db.Column(db.DateTime, default=datetime.utcnow)
    last_updated = db.Column(db.DateTime, onupdate=datetime.utcnow)
    first_name = db.Column(db.String(100))
    last_name = db.Column(db.String(100))
    middle_initial = db.Column(db.String(5))
    phone = db.Column(db.String(20))
    email = db.Column(db.String(100))
    linkedin_suffix = db.Column(db.String(100))
    location_display = db.Column(db.String(100))
    relocation_status = db.Column(db.String(255))
    title = db.Column(db.String(100))

    name = orm.column_property(first_name + ' ' + last_name)
    full_name = orm.column_property(first_name + ' ' + middle_initial + '. ' + last_name)
    # name_initial = orm.column_property(first_name[0] + ' ' + last_name[0])
    # full_name_initial = orm.column_property(first_name[0] + middle_initial + last_name[0])


class Employment(db.Model):
    """
    Employment model
    """
    __tablename__ = 'employment'
    employment_id = db.Column(db.Integer, primary_key=True)
    deleted = db.Column(db.DateTime)
    created = db.Column(db.DateTime, default=datetime.utcnow)
    last_updated = db.Column(db.DateTime, onupdate=datetime.utcnow)
    title = db.Column(db.String(100))
    organization_name = db.Column(db.String(100))
    location_display = db.Column(db.String(100))
    start_date = db.Column(db.Date)
    end_date = db.Column(db.Date)

    employment_items = db.relationship('EmploymentItem', foreign_keys='[EmploymentItem.employment_id]')
    skills = db.relationship(
        'Skill',
        secondary='employment_skill_association',
        primaryjoin='and_(Employment.employment_id==EmploymentSkillAssociation.employment_id, EmploymentSkillAssociation.deleted==None)',
        secondaryjoin='and_(Skill.skill_id==EmploymentSkillAssociation.skill_id, Skill.deleted==None)',
    )


class EmploymentItem(db.Model):
    """
    Employment item model
    """
    __tablename__ = 'employment_item'
    employment_item_id = db.Column(db.Integer, primary_key=True)
    employment_id = db.Column(db.Integer, db.ForeignKey('employment.employment_id'), nullable=False)
    deleted = db.Column(db.DateTime)
    created = db.Column(db.DateTime, default=datetime.utcnow)
    last_updated = db.Column(db.DateTime, onupdate=datetime.utcnow)
    description = db.Column(db.String(255))

    employment = db.relationship('Employment', foreign_keys=[employment_id])


class ResumeEmployment(db.Model):
    """
    Associates resumes with employments
    """
    __tablename__ = 'resume_employment'
    resume_employment_id = db.Column(db.Integer, primary_key=True)
    resume_id = db.Column(db.Integer, db.ForeignKey('resume.resume_id'), nullable=False)
    employment_id = db.Column(db.Integer, db.ForeignKey('employment.employment_id'), nullable=False)
    deleted = db.Column(db.DateTime)
    created = db.Column(db.DateTime, default=datetime.utcnow)
    last_updated = db.Column(db.DateTime, onupdate=datetime.utcnow)


class Education(db.Model):
    """
    Education model
    """
    __tablename__ = 'education'
    education_id = db.Column(db.Integer, primary_key=True)
    deleted = db.Column(db.DateTime)
    created = db.Column(db.DateTime, default=datetime.utcnow)
    last_updated = db.Column(db.DateTime, onupdate=datetime.utcnow)
    organization_name = db.Column(db.String(100))
    location_display = db.Column(db.String(100))
    degree = db.Column(db.String(100))
    degree_short = db.Column(db.String(100))
    major = db.Column(db.String(100))
    minor_display = db.Column(db.String(100))
    honors_display = db.Column(db.String(100))
    start_date = db.Column(db.Date)
    end_date = db.Column(db.Date)

    education_items = db.relationship('EducationItem', foreign_keys='[EducationItem.education_id]')
    skills = db.relationship(
        'Skill',
        secondary='education_skill_association',
        primaryjoin='and_(Education.education_id==EducationSkillAssociation.education_id, EducationSkillAssociation.deleted==None)',
        secondaryjoin='and_(Skill.skill_id==EducationSkillAssociation.skill_id, Skill.deleted==None)',
    )


class EducationItem(db.Model):
    """
    Education item model
    """
    __tablename__ = 'education_item'
    education_item_id = db.Column(db.Integer, primary_key=True)
    education_id = db.Column(db.Integer, db.ForeignKey('education.education_id'), nullable=False)
    deleted = db.Column(db.DateTime)
    created = db.Column(db.DateTime, default=datetime.utcnow)
    last_updated = db.Column(db.DateTime, onupdate=datetime.utcnow)
    description = db.Column(db.String(255))
    link = db.Column(db.String(255))

    education = db.relationship('Education', foreign_keys=[education_id])


class ResumeEducation(db.Model):
    """
    Associates resumes with educations
    """
    __tablename__ = 'resume_education'
    resume_education_id = db.Column(db.Integer, primary_key=True)
    resume_id = db.Column(db.Integer, db.ForeignKey('resume.resume_id'), nullable=False)
    education_id = db.Column(db.Integer, db.ForeignKey('education.education_id'), nullable=False)
    deleted = db.Column(db.DateTime)
    created = db.Column(db.DateTime, default=datetime.utcnow)
    last_updated = db.Column(db.DateTime, onupdate=datetime.utcnow)


class Skill(db.Model):
    """
    A skill, such as a programming language or framework, tied to a resume.
    """
    __tablename__ = 'skill'
    OPTIONS = {
        'type': {
            'skill': 'Skill',
            'language': 'Language',
            'technology': 'Technology/Framework',
        }
    }
    skill_id = db.Column(db.Integer, primary_key=True)
    deleted = db.Column(db.DateTime)
    created = db.Column(db.DateTime, default=datetime.utcnow)
    last_updated = db.Column(db.DateTime, onupdate=datetime.utcnow)
    name = db.Column(db.String(100))
    description = db.Column(db.String(255))
    type = db.Column(db.String(100))
    resume_id = db.Column(db.Integer, db.ForeignKey('resume.resume_id'), nullable=False)

    resume = db.relationship('Resume', foreign_keys=[resume_id])


class EmploymentSkillAssociation(db.Model):
    """
    Associates an employment with a skill
    """
    __tablename__ = 'employment_skill_association'
    association_id = db.Column(db.Integer, primary_key=True)
    employment_id = db.Column(db.Integer, db.ForeignKey('employment.employment_id'), nullable=False)
    skill_id = db.Column(db.Integer, db.ForeignKey('skill.skill_id'), nullable=False)
    description = db.Column(db.String(255))
    deleted = db.Column(db.DateTime)
    created = db.Column(db.DateTime, default=datetime.utcnow)
    last_updated = db.Column(db.DateTime, onupdate=datetime.utcnow)

class EducationSkillAssociation(db.Model):
    """
    Associates an education with a skill
    """
    __tablename__ = 'education_skill_association'
    association_id = db.Column(db.Integer, primary_key=True)
    education_id = db.Column(db.Integer, db.ForeignKey('education.education_id'), nullable=False)
    skill_id = db.Column(db.Integer, db.ForeignKey('skill.skill_id'), nullable=False)
    description = db.Column(db.String(255))
    deleted = db.Column(db.DateTime)
    created = db.Column(db.DateTime, default=datetime.utcnow)
    last_updated = db.Column(db.DateTime, onupdate=datetime.utcnow)


class User(db.Model):
    """
    Basic user model
    """
    __tablename__ = 'user'
    OPTIONS = {
        'user_type': {
            'standard': 'Standard',
            'admin': 'Admin',
        }
    }
    user_id = db.Column(db.Integer, primary_key=True)
    active = db.Column(db.Boolean, default=True)
    created = db.Column(db.DateTime, default=datetime.utcnow())
    last_login = db.Column(db.DateTime)
    user_type = db.Column(db.String(15))
    user_name = db.Column(db.String(100), unique=True)
    password = db.Column(db.String(255))
    email = db.Column(db.String(100), unique=True)
    first_name = db.Column(db.String(100))
    last_name = db.Column(db.String(100))
    phone = db.Column(db.String(15))

    @property
    def full_name(self):
        return f"{self.first_name or ''} {self.last_name or ''}"