"""Projections Model.

Model representing marketing projections metadata.
"""

from copy import deepcopy

import sqlalchemy
from oto import response
from sqlalchemy import exc
from sqlalchemy.orm import relationship

from sales_goals.connectors import mysql
from sales_goals.logic import countries
from sales_goals.models import error_handlers
from sales_goals.models import marketing_drivers


class DigitalProjection(mysql.BaseModel):
    """Digital Projection Model."""

    __tablename__ = 'digital_marketing_projections'

    pk = sqlalchemy.Column(
        'id', sqlalchemy.Integer, primary_key=False, autoincrement=True)
    product_id = sqlalchemy.Column(
        sqlalchemy.Integer, primary_key=True, nullable=False)
    downloads_total = sqlalchemy.Column(
        sqlalchemy.Integer, default=None, server_default=None)
    other_total = sqlalchemy.Column(
        sqlalchemy.Integer, default=None, server_default=None)
    internal_note = sqlalchemy.Column(
        sqlalchemy.Text)
    updated_at = sqlalchemy.Column(
        sqlalchemy.DateTime, nullable=False, default=sqlalchemy.func.now(),
        onupdate=sqlalchemy.func.now())
    updated_by = sqlalchemy.Column(sqlalchemy.VARCHAR(127))

    def to_dict(self):
        """Create a dictionary representation of the object."""
        return {
            'product_id': self.product_id,
            'internal_note': self.internal_note,
        }


class DigitalProjectionForStore(mysql.BaseModel):
    """Projection for a store Model."""

    __tablename__ = 'digital_marketing_projections_for_store'

    pk = sqlalchemy.Column(
        'id', sqlalchemy.Integer, primary_key=True, autoincrement=True)
    product_id = sqlalchemy.Column(
        sqlalchemy.Integer,
        sqlalchemy.ForeignKey('digital_marketing_projections.product_id'),
        nullable=False
    )
    store_id = sqlalchemy.Column(
        sqlalchemy.Integer,
        sqlalchemy.ForeignKey('digital_stores.id'),
        nullable=False
    )
    projection = sqlalchemy.Column(
        sqlalchemy.Integer, default=None, server_default=None)
    updated_at = sqlalchemy.Column(
        sqlalchemy.DateTime, nullable=False, default=sqlalchemy.func.now(),
        onupdate=sqlalchemy.func.now())
    updated_by = sqlalchemy.Column(sqlalchemy.VARCHAR(127))

    store = relationship(
        'DigitalStore',
        lazy='joined',
        order_by='DigitalStore.digital_store_id'
    )

    def to_dict(self):
        """Create a dictionary representation of the object."""
        return {
            'product_id': self.product_id,
            'projection': self.projection,
        }


class DigitalProjectionByCountry(mysql.BaseModel):
    """Digital Projection By Country Model."""

    __tablename__ = 'digital_marketing_projections_by_country'

    pk = sqlalchemy.Column(
        'id', sqlalchemy.Integer, primary_key=False, autoincrement=True)
    product_id = sqlalchemy.Column(
        sqlalchemy.Integer, primary_key=True, nullable=False)
    country_id = sqlalchemy.Column(
        sqlalchemy.Integer, primary_key=True, nullable=False)
    country_name = sqlalchemy.Column(sqlalchemy.String, nullable=False)
    downloads_projection = sqlalchemy.Column(
        sqlalchemy.Integer, default=None, server_default=None)
    other_projection = sqlalchemy.Column(
        sqlalchemy.Integer, default=None, server_default=None)
    updated_at = sqlalchemy.Column(
        sqlalchemy.DateTime, nullable=False, default=sqlalchemy.func.now(),
        onupdate=sqlalchemy.func.now())
    updated_by = sqlalchemy.Column(sqlalchemy.VARCHAR(127))

    def to_dict(self, country_store_projections=None):
        if country_store_projections is None:
            country_store_projections = []
        """Create a dictionary representation of the object."""
        return {
            'id': self.pk,
            'product_id': self.product_id,
            'territory_id': self.country_id,
        }


class DigitalProjectionByCountryForStore(mysql.BaseModel):
    """Projection for a store Model."""

    __tablename__ = 'digital_marketing_projections_by_country_for_store'

    pk = sqlalchemy.Column(
        'id', sqlalchemy.Integer, primary_key=True, autoincrement=True)
    product_id = sqlalchemy.Column(
        sqlalchemy.Integer,
        sqlalchemy.ForeignKey(
            'digital_marketing_projections_by_country.product_id'),
        nullable=False
    )
    store_id = sqlalchemy.Column(
        sqlalchemy.Integer,
        sqlalchemy.ForeignKey('digital_stores.id'),
        nullable=False
    )
    country_id = sqlalchemy.Column(
        sqlalchemy.Integer,
        sqlalchemy.ForeignKey(
            'digital_marketing_projections_by_country.country_id'),
        nullable=False
    )
    country_name = sqlalchemy.Column(sqlalchemy.String, nullable=False)
    projection = sqlalchemy.Column(
        sqlalchemy.Integer, default=None, server_default=None)
    updated_at = sqlalchemy.Column(
        sqlalchemy.DateTime, nullable=False, default=sqlalchemy.func.now(),
        onupdate=sqlalchemy.func.now())
    updated_by = sqlalchemy.Column(sqlalchemy.VARCHAR(127))

    store = relationship(
        'DigitalStore',
        lazy='joined',
        order_by='DigitalStore.digital_store_id'
    )

    def to_dict(self):
        """Create a dictionary representation of the object."""
        return {
            'product_id': self.product_id,
            'store_id': self.store_id,
            'projection': self.projection,
        }


class ProjectMarketingProjections(mysql.BaseModel):
    """Project Marketing Projections Model."""

    __tablename__ = 'project_marketing_projections'

    project_marketing_projections_id = sqlalchemy.Column(
        'id', sqlalchemy.Integer, primary_key=False, autoincrement=True)
    project_id = sqlalchemy.Column(
        sqlalchemy.Integer, primary_key=True, nullable=False)
    internal_note = sqlalchemy.Column(
        sqlalchemy.Text)
    updated_at = sqlalchemy.Column(
        sqlalchemy.DateTime, nullable=False, default=sqlalchemy.func.now(),
        onupdate=sqlalchemy.func.now())
    updated_by = sqlalchemy.Column(sqlalchemy.VARCHAR(127))

    def to_dict(self):
        """Create a dictionary representation of the object."""
        return {
            'project_id': self.project_id,
            'internal_note': self.internal_note,
        }


@error_handlers.sqlalchemy_error_handler
def get_projections_by_product_id(product_id):
    """Fetch digital projections by product id.

    Args:
        product_id (int): Product id.

    Returns:
        response.Response: object containing projection
            if available else not found response.
    """
    with mysql.sales_goals_session_scope() as session:
        projection = session.query(DigitalProjection).filter(
            DigitalProjection.product_id == product_id).first()

        if not projection:
            return response.create_not_found_response()

        result = projection.to_dict()

        result['projections'] = get_territory_projections(
            session, product_id)

        return response.Response(message=result)


@error_handlers.sqlalchemy_error_handler
def dataload_digital_product_projections(product_ids):
    """Fetch bulk digital product projections.

    Args:
        product_ids (list): Product ids.

    Returns:
        response.Response: object containing projection
            if available else not found response.
    """
    with mysql.sales_goals_session_scope() as session:
        global_projections_list = session.query(DigitalProjection).filter(
            DigitalProjection.product_id.in_(product_ids)).all()

        global_projections = [p.to_dict() for p in global_projections_list]

        territory_projections = get_bulk_territory_projections(
            session, product_ids)

        result = []
        for global_p in global_projections:
            product_id = global_p['product_id']
            result.append({
                **global_p,
                'projections': territory_projections[product_id],
            })

        return response.Response(message=result)


def get_global_stores_dict(session, product_id):
    """Get the global projections."""
    stores = session.query(DigitalProjectionForStore).filter(
        DigitalProjectionForStore.product_id == product_id,
    ).all()

    result = {}
    for projection in stores:
        store_name = projection.store.store
        store_total = store_name.lower() + '_total'
        result[store_total] = projection.projection
    return result


def get_territory_projections(session, product_id):
    """Get the territory projections."""
    projections = session.query(DigitalProjectionByCountry).filter(
        DigitalProjectionByCountry.product_id == product_id).all()
    projection_stores = session.query(
        DigitalProjectionByCountryForStore).filter(
        DigitalProjectionByCountryForStore.product_id == product_id).all()

    country_projections = []
    for projection in projections:
        country_id = projection.country_id
        projection_for_country = [ps for ps in projection_stores
                                  if ps.country_id == country_id]

        country_projections.append(
            projection.to_dict(projection_for_country))
    return country_projections


def get_bulk_territory_projections(session, product_ids):
    """Get bulk territory projections."""
    projections = session.query(DigitalProjectionByCountry).filter(
        DigitalProjectionByCountry.product_id.in_(product_ids)).all()
    projection_stores = session.query(
        DigitalProjectionByCountryForStore).filter(
        DigitalProjectionByCountryForStore.product_id.in_(product_ids)).all()

    country_projections = {id: [] for id in product_ids}
    for projection in projections:
        product_id = projection.product_id
        country_id = projection.country_id
        projection_for_country = [
            ps for ps in projection_stores
            if ps.country_id == country_id
            and ps.product_id == product_id
        ]

        country_projections[product_id].append(
            projection.to_dict(projection_for_country))

    return country_projections


@error_handlers.sqlalchemy_error_handler
def upsert_global_projections(product_id, projection_data, orchard_user_id):
    """
    Create or update projection for a product.

    Args:
        projection_data (dict): Projection data to be used.
        product_id (int): Product id.
        orchard_user_id (str): Orchard user id

    Returns:
        response.Response: object containing projection
            if available else not found response.
    """
    with mysql.sales_goals_session_scope() as session:
        try:
            projection = upsert_digital_projection(
                session, product_id, projection_data, orchard_user_id)
        except exc.IntegrityError:
            session.rollback()
            projection = None
        if projection is None:
            projection = upsert_digital_projection(
                session, product_id, projection_data, orchard_user_id)
        projection_response = projection.to_dict()
        return response.Response(message=projection_response, status=201)


def upsert_digital_projection_store(
        session, store_name, product_id, new_projection, orchard_user_id):
    """Upsert the digital projection store."""
    store = marketing_drivers.fetch_digital_store_by_name(store_name)
    projection_for_store = \
        session.query(DigitalProjectionForStore).filter(
            DigitalProjectionForStore.product_id == product_id,
            DigitalProjectionForStore.store_id == store.digital_store_id,
        ).first()

    if projection_for_store:
        projection_for_store.projection = new_projection
        projection_for_store.updated_by = orchard_user_id
    else:
        projection_for_store = DigitalProjectionForStore(
            product_id=product_id,
            store_id=store.digital_store_id,
            projection=new_projection,
            updated_by=orchard_user_id,
        )
    session.add(projection_for_store)
    session.flush()


def upsert_digital_projection(
        session, product_id, projection_data, orchard_user_id):
    """Upsert the digital projection."""
    projection = session.query(
        DigitalProjection).with_for_update().filter(
        DigitalProjection.product_id == product_id).first()

    update_internal_note = False

    if projection_data.get('internal_note') is not None:
        update_internal_note = True
        internal_note = projection_data.pop('internal_note', None)

    if projection:
        projection.updated_by = orchard_user_id
    else:
        projection = DigitalProjection(
            product_id=product_id,
            updated_by=orchard_user_id
        )

    if update_internal_note:
        projection.internal_note = internal_note

    session.add(projection)
    session.flush()
    session.refresh(projection)
    return projection


@error_handlers.sqlalchemy_error_handler
def upsert_territory_projections(product_id, projection_data, orchard_user_id):
    """
    Create or update projection for a product.

    Args:
        product_id (int): Product id.
        projection_data (dict): Projection data to be used.
        orchard_user_id (str): Orchard user id

    Returns:
        response.Response: object containing projection
            if available else not found response.
    """
    with mysql.sales_goals_session_scope() as session:
        result = deepcopy(projection_data['items'])
        for projection_item in projection_data['items']:
            projection = upsert_digital_territory_projection(
                session, product_id, projection_item, orchard_user_id)

            set_pk_on_result(result, projection)

            store_names = get_store_names_from_dict(
                projection_item, '_projection')

            for store_name in store_names:
                upsert_digital_territory_projection_store(
                    session,
                    store_name,
                    product_id,
                    projection_item,
                    orchard_user_id
                )

        return response.Response(message={'items': result}, status=201)


def upsert_digital_territory_projection_store(
        session, store_name, product_id, projection_item, orchard_user_id):
    """Upsert the digital projection store."""
    new_projection = projection_item[store_name + '_projection']
    country_id = projection_item['territory_id']
    country_name = countries.get_country_name_from_id(country_id)

    store = marketing_drivers.fetch_digital_store_by_name(store_name)
    store_id = store.digital_store_id
    projection_for_store = \
        session.query(DigitalProjectionByCountryForStore).filter(
            DigitalProjectionByCountryForStore.product_id == product_id,
            DigitalProjectionByCountryForStore.store_id == store_id,
            DigitalProjectionByCountryForStore.country_id == country_id,
        ).first()

    if projection_for_store:
        projection_for_store.projection = new_projection
        projection_for_store.updated_by = orchard_user_id
    else:
        projection_for_store = DigitalProjectionByCountryForStore(
            product_id=product_id,
            store_id=store.digital_store_id,
            projection=new_projection,
            updated_by=orchard_user_id,
            country_id=country_id,
            country_name=country_name
        )
    session.add(projection_for_store)
    session.flush()


def upsert_digital_territory_projection(
        session, product_id, projection_data, orchard_user_id):
    """Upsert the digital projection."""
    country_id = projection_data['territory_id']
    country_name = countries.get_country_name_from_id(country_id)

    projection = session.query(DigitalProjectionByCountry).filter(
        DigitalProjectionByCountry.product_id == product_id,
        DigitalProjectionByCountry.country_id == country_id,
    ).first()

    if projection:
        projection.updated_by = orchard_user_id
    else:
        projection = DigitalProjectionByCountry(
            product_id=product_id,
            updated_by=orchard_user_id,
            country_id=country_id,
            country_name=country_name
        )

    session.add(projection)
    session.flush()
    session.refresh(projection)
    return projection


@error_handlers.sqlalchemy_error_handler
def clear_product_territory_projections(product_id):
    """
    Delete all territory projections for a product.

    Args:
        product_id (int): Product id.
    """
    with mysql.sales_goals_session_scope() as session:
        session.query(DigitalProjectionByCountry).filter(
            DigitalProjectionByCountry.product_id == int(product_id)
        ).delete()


@error_handlers.sqlalchemy_error_handler
def delete_territory_projection(product_id, projection_id):
    """
    Delete projection for a product.

    Args:
        product_id (int): Product id.
        projection_id (int): Projection id.

    Returns:
        response.Response: success or error
    """
    with mysql.sales_goals_session_scope() as session:
        projection = session.query(DigitalProjectionByCountry).filter(
            DigitalProjectionByCountry.pk == projection_id
        ).first()
        if not projection:
            return response.create_not_found_response()

        session.delete(projection)
        return response.Response(message=projection)


def get_store_names_from_dict(projections, key_suffix):
    """Get the store names from a dict of stores."""
    store_totals = [key for key in projections.keys()
                    if key.endswith(key_suffix)]
    return [store_total.replace(key_suffix, '')
            for store_total in store_totals]


def set_pk_on_result(projections, projection):
    """Set the primary key on the returned results."""
    matched_projection = [x for x in projections
                          if x['territory_id'] == projection.country_id][0]
    matched_projection['id'] = projection.pk


@error_handlers.sqlalchemy_error_handler
def upsert_project_marketing_projection(
        project_id, projection_data, orchard_user_id):
    """Upsert the project marketing projection."""
    with mysql.sales_goals_session_scope() as session:
        projection = session.query(ProjectMarketingProjections).filter(
            ProjectMarketingProjections.project_id == project_id).first()

        update_internal_note = False

        if projection_data.get('internal_note') is not None:
            update_internal_note = True
            internal_note = projection_data.pop('internal_note', None)

        if projection:
            projection.updated_by = orchard_user_id
        else:
            projection = ProjectMarketingProjections(
                project_id=project_id,
                updated_by=orchard_user_id
            )

        if update_internal_note:
            projection.internal_note = internal_note

        session.add(projection)
        session.flush()
        session.refresh(projection)
        projection_response = projection.to_dict()

        return response.Response(message=projection_response, status=201)


@error_handlers.sqlalchemy_error_handler
def get_project_marketing_projection(project_id):
    """Get the project marketing projection."""
    with mysql.sales_goals_session_scope() as session:
        projection = session.query(ProjectMarketingProjections).filter(
            ProjectMarketingProjections.project_id == project_id).first()

        if not projection:
            return {'internal_note': ''}
        return projection.to_dict()


@error_handlers.sqlalchemy_error_handler
def dataload_project_marketing_projection(project_ids):
    """Dataload the project marketing projections."""
    with mysql.sales_goals_session_scope() as session:
        projection_list = session.query(ProjectMarketingProjections).filter(
            ProjectMarketingProjections.project_id.in_(project_ids)).all()

        global_projections = [p.to_dict() for p in projection_list]

        return response.Response(message=global_projections)