from sqlalchemy import Column, ForeignKey, Float
from sqlalchemy import Integer, String
from sqlalchemy import create_engine, BigInteger
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, sessionmaker
from sqlalchemy.sql import ClauseElement

from Settings import SQ_LITE_FILE

Base = declarative_base()


class TrackPso(Base):
    __tablename__ = 'track'

    id = Column(Integer, primary_key=True)
    source_city_id = Column(Integer, ForeignKey('track_source.id'), index=True)
    destination_city_id = Column(Integer, ForeignKey('track_destination.id'), index=True)
    duration = Column(Integer)
    start = Column(Integer)
    end = Column(Integer)


class TrackSourcePso(Base):
    __tablename__ = 'track_source'

    id = Column(Integer, primary_key=True)
    name = Column(String)


class TrackDestinationPso(Base):
    __tablename__ = 'track_destination'

    id = Column(Integer, primary_key=True)
    name = Column(String)


class CityPso(Base):
    __tablename__ = 'city'

    id = Column(Integer, primary_key=True)
    name = Column(String)


class DayPso(Base):
    __tablename__ = 'day'

    id = Column(Integer, primary_key=True)
    name = Column(String)


class DepartPso(Base):
    __tablename__ = 'depart'

    id = Column(Integer, primary_key=True)
    name = Column(String)


class LatestVacationPso(Base):
    __tablename__ = 'latest_vacation'

    id = Column(Integer, primary_key=True)
    key_id = Column(Integer, ForeignKey('vacationkey.id'), index=True)
    captured = Column(Integer)
    cost = Column(Integer)
    lastCost = Column(Integer)
    minCost = Column(Integer)
    maxCost = Column(Integer)
    percentDiff = Column(Float)
    count = Column(Integer)

    key = relationship("VacationKeyPso", lazy="joined")


class OperatorPso(Base):
    __tablename__ = 'operator'

    id = Column(Integer, primary_key=True)
    name = Column(String)


class ResortPso(Base):
    __tablename__ = 'resort'

    id = Column(Integer, primary_key=True)
    name = Column(String)


class ReturnPso(Base):
    __tablename__ = 'return'

    id = Column(Integer, primary_key=True)
    name = Column(String)


class VacationCapturePso(Base):
    __tablename__ = 'vacation_capture'

    id = Column(Integer, primary_key=True)
    key_id = Column(Integer, ForeignKey('vacationkey.id'), index=True)
    captured = Column(Integer)
    cost = Column(Integer)

    key = relationship("VacationKeyPso")


class VacationKeyPso(Base):
    __tablename__ = 'vacationkey'

    id = Column(Integer, primary_key=True)
    name = Column(String)

    resort_id = Column(Integer, ForeignKey('resort.id'), index=True)
    operator_id = Column(Integer, ForeignKey('operator.id'), index=True)
    city_id = Column(Integer, ForeignKey('city.id'), index=True)
    depart_id = Column(Integer, ForeignKey('depart.id'), index=True)
    treturn_id = Column(Integer, ForeignKey('return.id'), index=True)
    days_id = Column(Integer, ForeignKey('day.id'))

    resort = relationship("ResortPso")
    operator = relationship("OperatorPso")
    city = relationship("CityPso")
    depart = relationship("DepartPso")
    treturn = relationship("ReturnPso")
    days = relationship("DayPso")


class TableDao():
    def __init__(self, pso, session):
        self.pso = pso
        self.session = session

    def gets(self):
        return self.get_table_as_dict()

    def get(self, id):
        return self.get_as_dict(id)

    def by_name(self, name):
        return self.by_field(self.pso.name, name)

    def by_field(self, field, field_value):
        results = []
        for row in self.session.query(self.pso).filter(field == field_value).all():
            results.append(self.as_dict(row))
        return results

    def get_table_as_dict(self):
        results = []
        for row in self.session.query(self.pso):
            results.append(self.as_dict(row))
        return results

    def get_as_dict(self, id):
        row = self.session.query(self.pso).get(id)
        return self.as_dict(row)

    def as_dict(self, row):
        return {c.name: getattr(row, c.name) for c in row.__table__.columns}


class TrackDao(TableDao):
    def __init__(self, session):
        super().__init__(TrackPso, session)


class TrackSourceDao(TableDao):
    def __init__(self, session):
        super().__init__(TrackSourceDao, session)


class TrackDestinationDao(TableDao):
    def __init__(self, session):
        super().__init__(TrackDestinationPso, session)


class CityDao(TableDao):
    def __init__(self, session):
        super().__init__(CityPso, session)


class DayDao(TableDao):
    def __init__(self, session):
        super().__init__(DayPso, session)


class DepartDao(TableDao):
    def __init__(self, session):
        super().__init__(DepartPso, session)


class LatestDao(TableDao):
    def __init__(self, session):
        super().__init__(LatestVacationPso, session)

    def by_key(self, key_id):
        return self.by_field(LatestVacationPso.key_id, key_id)


class OperatorDao(TableDao):
    def __init__(self, session):
        super().__init__(OperatorPso, session)


class ResortDao(TableDao):
    def __init__(self, session):
        super().__init__(ResortPso, session)


class ReturnDao(TableDao):
    def __init__(self, session):
        super().__init__(ReturnPso, session)


class VacationCaptureDao(TableDao):
    def __init__(self, session):
        super().__init__(VacationCapturePso, session)

    def by_key(self, key_id):
        return self.by_field(VacationCapturePso.key_id, key_id)

    def by_depart(self, depart_id):
        results = []
        for row in self.session.query(VacationCapturePso).join(VacationKeyPso).all():
            results.append(self.as_dict(row))
        return results


class VacationKeyDao(TableDao):
    def __init__(self, session):
        super().__init__(VacationKeyPso, session)

    def by_depart(self, depart_id):
        return self.by_field(VacationKeyPso.depart_id, depart_id)


class DbAccess:
    engine = create_engine('sqlite:///' + SQ_LITE_FILE + '?check_same_thread=False', echo=False)
    Session = sessionmaker(bind=engine)
    session = Session()

    trackDao = TrackDao(session)
    trackSourceDao = TrackSourceDao(session)
    trackDestinationDao = TrackDestinationDao(session)
    cityDao = CityDao(session)
    dayDao = DayDao(session)
    departDao = DepartDao(session)
    latestDao = LatestDao(session)
    operatorDao = OperatorDao(session)
    resortDao = ResortDao(session)
    returnDao = ReturnDao(session)
    vacationCaptureDao = VacationCaptureDao(session)
    vacationKeyDao = VacationKeyDao(session)

    def create_tables(self):
        Base.metadata.create_all(self.engine)

        self.get_or_create(TrackSourcePso, name="Ottawa")
        self.get_or_create(TrackDestinationPso, name="Mayan Riviera")
        self.get_or_create(TrackDestinationPso, name="Aruba")

    def get_or_create(self, model, **kwargs):
        instance = self.session.query(model).filter_by(**kwargs).first()
        if instance:
            return instance
        else:
            instance = model(**kwargs)
            self.session.add(instance)
            self.session.commit()
            return instance

    def insert_vacation_packages(self, VacationPackages):

        latest_map = {}
        for row in DbAccess.session.query(LatestVacationPso):
            latest_map[row.key.name] = row

        resort_map = {}
        for row in DbAccess.session.query(ResortPso):
            resort_map[row.name] = row

        city_map = {}
        for row in DbAccess.session.query(CityPso):
            city_map[row.name] = row

        operator_map = {}
        for row in DbAccess.session.query(OperatorPso):
            operator_map[row.name] = row

        keys_map = {}
        for row in DbAccess.session.query(VacationKeyPso):
            keys_map[row.name] = row

        departs_map = {}
        for row in DbAccess.session.query(VacationKeyPso):
            departs_map[row.name] = row

        returns_map = {}
        for row in DbAccess.session.query(VacationKeyPso):
            returns_map[row.name] = row

        days_map = {}
        for row in DbAccess.session.query(DayPso):
            days_map[row.name] = row

        to_insert = []
        for package in VacationPackages:
            package.cost = 1000
            if package.name not in resort_map:
                newPso = ResortPso(name=package.name)
                resort_map[package.name] = newPso
                DbAccess.session.add(newPso)
            if package.operator not in operator_map:
                newPso = OperatorPso(name=package.operator)
                operator_map[package.operator] = newPso
                DbAccess.session.add(newPso)
            if package.city not in city_map:
                newPso = CityPso(name=package.city)
                city_map[package.city] = newPso
                DbAccess.session.add(newPso)
            if package.depart not in departs_map:
                newPso = DepartPso(name=package.depart)
                departs_map[package.depart] = newPso
                DbAccess.session.add(newPso)
            if package.treturn not in returns_map:
                newPso = ReturnPso(name=package.treturn)
                returns_map[package.treturn] = newPso
                DbAccess.session.add(newPso)
            if package.days not in days_map:
                newPso = DayPso(name=package.days)
                days_map[package.days] = newPso
                DbAccess.session.add(newPso)

            key = package.create_key()
            if key not in keys_map:
                newPso = VacationKeyPso(name=key,
                                        resort=resort_map[package.name],
                                        operator=operator_map[package.operator],
                                        city=city_map[package.city],
                                        depart=departs_map[package.depart],
                                        treturn=returns_map[package.treturn],
                                        days=days_map[package.days],
                                        )
                keys_map[key] = newPso
                DbAccess.session.add(newPso)

            newPso = VacationCapturePso(
                captured=package.captured,
                key=keys_map[key],
                cost=package.cost
            )
            to_insert.append(newPso)

            if key not in latest_map:
                newPso = LatestVacationPso(key=keys_map[key],
                                           captured=package.captured,
                                           cost=package.cost,
                                           lastCost=0,
                                           minCost=package.cost,
                                           maxCost=package.cost,
                                           percentDiff=0,
                                           count=1,
                                           )
                latest_map[key] = newPso
                DbAccess.session.add(newPso)

            else:
                latest = latest_map[key]
                if latest.cost != package.cost:
                    latest.lastCost = latest.cost
                    latest.cost = package.cost
                    latest.minCost = min([latest.cost, latest.lastCost])
                    latest.maxCost = max([latest.cost, latest.lastCost])
                    latest.count = latest.count + 1
                    latest.percentDiff = (latest.cost - latest.lastCost) / latest.lastCost * 100

        DbAccess.session.add_all(to_insert)
        DbAccess.session.commit()
