from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

import datetime

from Models import Candidate
from Models import Recruiter
from Models import Client
from Models import Position
from Models import Interview
from Models import init_database

class DataProviderService:
    def __init__(self, engine):
        """
        :param engine: The engine route and login details
        :return: a new instance of DAL class
        :type engine: string
        """
        if not engine:
            raise ValueError('The values specified in engine parameter has to be supported by SQLAlchemy')
        self.engine = engine
        db_engine = create_engine(engine)
        db_session = sessionmaker(bind=db_engine)
        self.session = db_session()

    def init_database(self):
        """
        Initializes the database tables and relationships
        :return: None
        """
        init_database(self.engine)

    def add_candidate(self, first_name, last_name, email, birthday=None, phone=None, languages="", skills=""):
        """

        Creates and saves a new candidate to the database.

        :param first_name: First Name of the candidate
        :param last_name: Last Name of the candidate
        :param email: Email address of the candidate
        :param birthday: Birthday of the candidate
        :param phone: Telephone number of the candidate
        :param languages: Language skills of the candidate
        :param skills: Skills which the candidate has
        :return: The id of the new Candidate
        """

        new_candidate = Candidate(first_name=first_name,
                                  last_name=last_name,
                                  email=email,
                                  birthday=birthday,
                                  phone=phone,
                                  languages=languages,
                                  skills=skills)

        self.session.add(new_candidate)
        self.session.commit()

        return new_candidate.id

    def get_candidate(self, id=None, serialize=False):
        """
        If the id parameter is  defined then it looks up the candidate with the given id,
        otherwise it loads all the candidates

        :param id: The id of the candidate which needs to be loaded (default value is None)
        :return: The candidate or candidates.
        """

        all_candidates = []

        if id is None:
            all_candidates = self.session.query(Candidate).order_by(Candidate.last_name).all()
        else:
            all_candidates = self.session.query(Candidate).filter(Candidate.id==id).all()

        if serialize:
            return [cand.serialize() for cand in all_candidates]
        else:
            return all_candidates

    def update_candidate(self, id, new_candidate):
        updated_candidate = None
        candidates = self.get_candidate(id)
        candidate = None
        if len(candidates) is not 1:
            return updated_candidate
        else:
            candidate = candidates[0]

        if candidate:
            candidate.email = new_candidate["email"]
            candidate.phone = new_candidate["phone"]
            candidate.first_name = new_candidate["first_name"]
            candidate.last_name = new_candidate["last_name"]
            self.session.add(candidate)
            self.session.commit()
            updated_candidate = self.get_candidate(id)[0]

        return updated_candidate.serialize()

    def delete_candidate(self, id):
        if id:
            items_deleted = self.session.query(Candidate).filter(Candidate.id == id).delete()
            return items_deleted > 0
        return False

    def fill_database(self):
        #
        # Candidates
        #
        cand1 = Candidate(first_name="John",
                          last_name="Doe",
                          email="john@example.com",