"""SQLAlchemy model for Master delivery history records."""


from sqlalchemy import BigInteger
from sqlalchemy import Column
from sqlalchemy import column
from sqlalchemy import DateTime
from sqlalchemy import Integer
from sqlalchemy import String
from sqlalchemy import table
from sqlalchemy import UniqueConstraint
from sqlalchemy.exc import SQLAlchemyError

from deliveryhistory.connectors import mysql
from deliveryhistory.response import create_fatal_response
from deliveryhistory.response import Response
from deliveryhistory.util import handler_util


class MastersDeliveryHistory(mysql.BaseModel):
    """Model that stores Master delivery history records."""

    __tablename__ = 'masters_delivery_history'

    id = Column(Integer, primary_key=True)  # noqa
    isrc = Column(String(25), nullable=False)
    tuid = Column(BigInteger, nullable=False)
    upc = Column(String(20), nullable=False)
    partner_id = Column(Integer, nullable=False)
    delivery_date = Column(DateTime, nullable=False)
    __table_args__ = (UniqueConstraint('isrc', 'partner_id'),)

    def __init__(self, isrc, tuid, upc, partner_id, delivery_date):
        """Initialize of object MastersDeliveryHistory.

        Args:
            isrc (string): ISRC.
            tuid (int): Track Unique Identifier.
            upc (string): Universal Product Code.
            partner_id (int): Partner unique identifier.
            delivery_date (datetime): time of record creation.

        """
        self.isrc = isrc
        self.tuid = tuid
        self.upc = upc
        self.partner_id = partner_id
        self.delivery_date = delivery_date


def get_delivery_history(partner_id, isrcs):
    """Get delivery history record from DB.

    Return list of delivery history record, filtered by args.

    Args:
        partner_id (int): Partner ID.
        isrcs (list): list of ISRC codes for searching.

    Returns:
        Response: list of delivery history records.

    """
    try:
        with mysql.session_scope() as db_session:
            result = (
                db_session.query(MastersDeliveryHistory).
                filter(
                    MastersDeliveryHistory.partner_id == partner_id,
                    MastersDeliveryHistory.isrc.in_(isrcs)).all())
    except SQLAlchemyError as ex:
        return create_fatal_response(str(ex))

    paginated = handler_util.add_pagination([row.to_dict() for row in result])
    return Response(paginated)


def save_delivery_history(records):
    """Save delivery history records into DB.

    Args:
        records (list): list of dict, that store records.

    Returns:
        Amount of inserted records.

    """
    result = ''
    try:
        with mysql.session_scope() as db_session:
            history_data_to_insert = [{
                'isrc': record['isrc'],
                'tuid': record['tuid'],
                'partner_id': record['partner_id'],
                'upc': record['upc'],
                'delivery_date': record['delivery_date']}
                for record in records]

            masters_delivery_history_table = table(
                'masters_delivery_history',
                column('id'), column('isrc'), column('tuid'),
                column('upc'), column('partner_id'), column('delivery_date'))
            UniqueConstraint(
                masters_delivery_history_table.c.isrc,
                masters_delivery_history_table.c.partner_id)

            query = masters_delivery_history_table.insert().prefix_with('Ignore')
            result = db_session.execute(query, history_data_to_insert)
    except SQLAlchemyError as ex:
        return create_fatal_response(str(ex))

    return Response(
        {'records_created': 0 if result == '' else result.rowcount})