"""
Field Manager Model.

This field_manager model uses sqlalchemy. It provides core functionality
for getting Sony label fields by vendor_id or subaccount_id.
"""

from sqlalchemy import Column
from sqlalchemy import Integer
from sqlalchemy import String

from sony_metadata import response
from sony_metadata.connectors import mysql


class FieldManager(mysql.BaseModel):
    """Field Manager."""

    __tablename__ = 'field_manager'

    field_manager_id = Column(Integer, primary_key=True)
    vendor_id = Column(Integer)
    subaccount_id = Column(Integer)
    financial_label_code = Column(String)
    financial_label_name = Column(String)
    rep_owner_code = Column(String)
    rep_owner_name = Column(String)
    gras_imprint_code = Column(String)
    gras_imprint_name = Column(String)
    sap_profit_center_code = Column(String)
    sap_profit_center_name = Column(String)


vendor_columns = (
    FieldManager.vendor_id,
    FieldManager.financial_label_code,
    FieldManager.financial_label_name,
    FieldManager.rep_owner_code,
    FieldManager.rep_owner_name,
    FieldManager.gras_imprint_code,
    FieldManager.gras_imprint_name,
    FieldManager.sap_profit_center_code,
    FieldManager.sap_profit_center_name)

subaccount_columns = (FieldManager.subaccount_id,) + vendor_columns


def get_vendor_labels(vendor_id):
    """Get labels for a vendor from the database.

    Args:
        vendor_id (int): vendor_id whose label data we want.

    Returns:
        response.Response: If vendor_id found, Response message contains a
            dictionary with Sony labels associated with the vendor. If no
            vendor_id is found, a Response is returned with a not found error
            code and a http code of 404.
    """
    with mysql.db_session() as session:
        row = session.query(*vendor_columns).filter(
            FieldManager.vendor_id == vendor_id,
            FieldManager.subaccount_id.is_(None))
        if row.count():
            return response.Response(row.first()._asdict())
    # if no vendor found
    return response.create_not_found_response('No vendor found')


def get_subaccount_labels(subaccount_id):
    """Get labels for a subaccount from the database.

    Args:
        subaccount (int): subaccount_id whose label data we want.

    Returns:
        response.Response: If subaccount_id found, Response message contains a
            dictionary with Sony labels associated with the vendor. If no
            subaccount_id is found, a Response is returned with a not found
            error code and a http code of 404.
    """
    with mysql.db_session() as session:
        row = session.query(*subaccount_columns).filter(
            FieldManager.subaccount_id == subaccount_id,
            FieldManager.subaccount_id.isnot(None))
        if row.count():
            return response.Response(row.first()._asdict())
    # if no subaccount found
    return response.create_not_found_response('No subaccount found')


def add_update_vendor_metadata(vendor_id, metadata, subaccount_id=None):
    """Add or update vendor info to the database.

    Args:
        vendor_id (int): the id of the label
        metadata (dict): dictionary w/ label fields to add or update
        subaccount_id (int): the id of the subaccount

    Returns:
        response.Response: response with status code
    """
    field_manager = FieldManager(
        vendor_id=vendor_id,
        subaccount_id=subaccount_id,
        financial_label_code=metadata['financial_label_code'],
        financial_label_name=metadata['financial_label_name'],
        rep_owner_code=metadata['rep_owner_code'],
        rep_owner_name=metadata['rep_owner_name'],
        gras_imprint_code=metadata['gras_imprint_code'],
        gras_imprint_name=metadata['gras_imprint_name'],
        sap_profit_center_code=metadata.get('sap_profit_center_code', None),
        sap_profit_center_name=metadata.get('sap_profit_center_name', None))

    with mysql.db_session() as session:

        row = session.query(*vendor_columns).filter(
            FieldManager.vendor_id == vendor_id
        )

        if subaccount_id:
            row = row.filter(
                FieldManager.subaccount_id == subaccount_id
            )

        if row.count():
            data = {'financial_label_code': metadata['financial_label_code'],
                    'financial_label_name': metadata['financial_label_name'],
                    'rep_owner_code': metadata['rep_owner_code'],
                    'rep_owner_name': metadata['rep_owner_name'],
                    'gras_imprint_code': metadata['gras_imprint_code'],
                    'gras_imprint_name': metadata['gras_imprint_name'],
                    'sap_profit_center_code': metadata.get('sap_profit_center_code', None),
                    'sap_profit_center_name': metadata.get('sap_profit_center_name', None)}

            update_query = session.query(FieldManager).filter(
                FieldManager.vendor_id == vendor_id
            )

            if subaccount_id:
                update_query = update_query.filter(
                    FieldManager.subaccount_id == subaccount_id
                )

            update_query.update(data)
        else:
            session.add(field_manager)

        result = session.commit()

        return response.Response(result)


def delete_vendor_metadata(vendor_id, subaccount_id=None):
    """Delete vendor metadata from the database.

    Args:
        vendor_id (int): the id of the label
        subaccount_id (int): Subaccount ID

    Returns:
        response.Response: response with status code
    """
    with mysql.db_session() as session:

        session.query(FieldManager).filter(
            FieldManager.vendor_id == vendor_id,
            FieldManager.subaccount_id == subaccount_id
        ).delete()

    result = session.commit()

    return response.Response(result)