"""Product Physical Supply Chain Metadata Model."""

import datetime

from oto import response
import sentry_sdk
from contextlib import nullcontext
from sqlalchemy import Column
from sqlalchemy import DateTime
from sqlalchemy import Integer
from sqlalchemy import SmallInteger
from sqlalchemy import String
from sqlalchemy.dialects.mysql import TINYINT

from ows_product_physical import features
from ows_product_physical.connector import mysql
from ows_product_physical.constant import error
from ows_product_physical.constant import field
from ows_product_physical.models import product_physical_supply_chain_info


class ProductPhysicalSupplyChainMetadata(mysql.BaseModel):
    """Product physical supply chain info model."""

    __tablename__ = 'product_physical_supply_chain_metadata'

    product_physical_supply_chain_metadata_id = Column(
        'id', Integer, primary_key=True, autoincrement=True, nullable=False
    )
    product_id = Column(Integer, nullable=False)
    store_id = Column(SmallInteger, nullable=False)
    embargo_date = Column(String, nullable=True)
    release_date = Column(String, nullable=True)
    sale_start_date = Column(String, nullable=True)
    initial_stock = Column(Integer, nullable=True)
    is_deleted = Column(TINYINT, default=0)
    date_added = Column(DateTime, nullable=False)
    date_updated = Column(DateTime, nullable=False)

    def to_dict(self):
        """Dict representation of a ProductPhysicalSupplyChainMetadata row."""
        return {
            'id': self.product_physical_supply_chain_metadata_id,
            'product_id': self.product_id,
            'store_id': self.store_id,
            'embargo_date': self.embargo_date
            and self.embargo_date.strftime('%Y-%m-%d'),
            'release_date': self.release_date
            and self.release_date.strftime('%Y-%m-%d'),
            'sale_start_date': self.sale_start_date
            and self.sale_start_date.strftime('%Y-%m-%d'),
            'initial_stock': self.initial_stock,
            'is_deleted': self.is_deleted,
            'date_added': self.date_added
            and self.date_added.strftime('%Y-%m-%d %H:%M:%S'),
            'date_updated': self.date_added
            and self.date_updated.strftime('%Y-%m-%d %H:%M:%S'),
        }


def get_product_supply_chain_metadata(product_id):
    """Fetch product physical supply chain metadata by product id.

    Args:
        product_id (int): product id of existing product

    Returns:
        Response: A response object with existing data.
    """
    with mysql.db_session() as session:
        try:
            result = (
                session.query(ProductPhysicalSupplyChainMetadata)
                .filter_by(product_id=product_id, is_deleted=0)
                .all()
            )

            if features.is_oa_physical_supply_chain_carved_in_enabled():
                supply_chain_metadata = []
                for item in result:
                    items = item.to_dict()
                    product_id = items.get('product_id')
                    store_id = items.get('store_id')
                    if items.get('store_id') in (
                        field.DIRECT_SHOT_STORE_ID,
                        field.WHEELS_STORE_ID,
                    ):
                        supplychain_info = product_physical_supply_chain_info \
                            .get_product_supply_chain_info_by_product_store_id(
                                product_id, store_id
                            )
                        if supplychain_info.status == 200:
                            items['returnability'] = supplychain_info.message[
                                'returnability'
                            ]
                            items['return_disposition'] = \
                                supplychain_info.message[
                                    'return_disposition'
                            ]
                    supply_chain_metadata.append(items)
                metadata = {'items': supply_chain_metadata}
            else:
                metadata = {'items': [item.to_dict() for item in result]}
            return response.Response(message=metadata, status=200)
        except Exception as exception:
            sentry_sdk.capture_exception(exception)
            return response.create_error_response(
                code=error.INTERNAL_ERROR, message='mysql error', status=500
            )


def set_physical_supply_chain_metadata(
    product_id,
    supplychain_data,
    session=None,
):
    """Create or update physical supply chain metadata for a product.

    Args:
        product_id (int): The product/release identifier.
        supplychain_data (dict): Metadata payload to persist.
        session (sqlalchemy.orm.session.Session, optional): DB session.

    Returns:
        response.Response: Response containing the updated metadata.
    """
    context = (
        mysql.db_session()
        if session is None
        else nullcontext(session)
    )

    with context as session:

        try:
            updated_metadata = []
            transaction_date = datetime.datetime.now(datetime.UTC)

            for data in supplychain_data['metadata']:

                transactional_data = _set_override_dates(
                    data,
                    transaction_date,
                )

                store_id = data.get('store_id')

                updated_dates = _set_existing_store_with_override_dates(
                    transactional_data,
                    store_id,
                    product_id,
                    session,
                    transaction_date,
                )

                if updated_dates.get('id'):

                    updated_metadata.append(updated_dates)

                else:

                    created_dates = _create_store_with_override_dates(
                        transactional_data,
                        store_id,
                        product_id,
                        session,
                        transaction_date,
                    )

                    updated_metadata.append(
                        _map_created_result(created_dates)
                    )

            if session.in_transaction():
                session.commit()

            return response.Response(
                status=200,
                message=updated_metadata,
            )

        except Exception as exception:

            session.rollback()

            sentry_sdk.capture_exception(exception)

            return response.create_error_response(
                code=error.INTERNAL_ERROR,
                message='mysql error',
                status=500,
            )

def _set_override_dates(data, transaction_date):
    """Set product physical supply chain metadata for existing records.

    Args:
        data (int): data to be inserted
        transaction_date (string): datetime to be set for transaction.

    Returns:
        Response: A dict with dates which are to be set.
    """
    date_fields = ['release_date', 'sale_start_date', 'embargo_date']
    date = [key for key, value in data.items() if key in date_fields]
    transactional_data = {
        'date_updated': transaction_date.strftime('%Y-%m-%d %H:%M:%S')
    }
    for date_type in date:
        transactional_data[date_type] = (
            data[date_type] if data[date_type] != '' else None
        )

    return transactional_data


def _set_existing_store_with_override_dates(
    transactional_data, store_id, product_id, session, transaction_date
):
    """Set product physical supply chain metadata for existing records.

    Args:
        transactional_data (dict): data to be inserted
        store_id (int): id of store
        product_id (int): id of product
        transaction_date (string): datetime to be set for transaction.

    Returns:
        Response: A dict with data which got updated.
    """
    existing_store_with_product = (
        session.query(ProductPhysicalSupplyChainMetadata)
        .filter_by(product_id=product_id, store_id=store_id, is_deleted=0)
        .first()
    )
    if existing_store_with_product:
        # Filter only valid model columns
        valid_fields = {
            c.name
            for c in ProductPhysicalSupplyChainMetadata.__table__.columns
        }
        filtered_data = {
            k: v
            for k, v in transactional_data.items()
            if k in valid_fields
        }

        session.query(ProductPhysicalSupplyChainMetadata).filter_by(
            product_id=product_id, store_id=store_id, is_deleted=0
        ).update(filtered_data)

        metadata_id = (
            existing_store_with_product.product_physical_supply_chain_metadata_id
        )

        transactional_data.update(
            {
                'id': metadata_id,
                'store_id': store_id,
                'date_updated': (
                    transaction_date
                    and transaction_date.strftime('%Y-%m-%d %H:%M:%S')
                ),
                'product_id': product_id,
            }
        )
    else:
        transactional_data.update({'product_id': None})
    return transactional_data


def _create_store_with_override_dates(
    transactional_data, store_id, product_id, session, transaction_date
):
    """Set product physical supply chain metadata for existing records.

    Args:
        transactional_data (dict): data to be inserted
        store_id (int): id of store
        product_id (int): id of product
        transaction_date (string): datetime to be set for transaction.

    Returns:
        Response: A dict with data which got created.
    """
    transactional_data.update(
        {
            'store_id': store_id,
            'date_added': transaction_date and
            transaction_date.strftime('%Y-%m-%d %H:%M:%S'),
            'product_id': product_id,
        }
    )
    valid_fields = {
        c.name for c in ProductPhysicalSupplyChainMetadata.__table__.columns
    }
    filtered_data = {
        k: v for k, v in transactional_data.items() if k in valid_fields
    }

    create = ProductPhysicalSupplyChainMetadata(**filtered_data)
    session.add(create)
    session.flush()

    return create

def _map_created_result(created_data):
    return {
        'id': created_data.
        product_physical_supply_chain_metadata_id,
        'product_id': created_data.product_id,
        'store_id': created_data.store_id,
        'embargo_date': created_data.embargo_date,
        'release_date': created_data.release_date,
        'sale_start_date': created_data.sale_start_date,
        'date_added': created_data.date_added,
        'date_updated': created_data.date_updated,
    }


def delete_supply_chain_metadata(metadata_id):
    """Set is_deleted to 1 on delete in product_physical_supply_chain_metadata.

    Args:
        supplychain_metadata_id (int): id of the record to be deleted

    Returns:
        Response: Response containing the result of deleting the record.
    """
    with mysql.db_session() as session:
        try:
            deletion_data = {
                'is_deleted': 1,
                'date_updated': datetime.datetime.now(datetime.UTC),
            }
            session.query(ProductPhysicalSupplyChainMetadata).filter(
                ProductPhysicalSupplyChainMetadata.
                product_physical_supply_chain_metadata_id
                == metadata_id
            ).update(deletion_data)
            return response.Response(message={'status': 'ok'})
        except Exception as exception:
            sentry_sdk.capture_exception(exception)
            return response.create_error_response(
                code=error.INTERNAL_ERROR, message='mysql error', status=500
            )


def delete_supply_chain_metadata_by_product_and_store(product_id, store_id):
    """Delete supply chain metadata by product_id and store_id.

    Args:
        product_id: Product key.
        store_id: Store Id.

    Return:
         Response: Response containing the result of deleting the record.
    """
    with mysql.db_session() as session:
        try:
            deletion_data = {
                'is_deleted': 1,
                'date_updated': datetime.datetime.now(datetime.UTC),
            }
            session.query(ProductPhysicalSupplyChainMetadata).filter(
                ProductPhysicalSupplyChainMetadata.product_id == product_id,
                ProductPhysicalSupplyChainMetadata.store_id == store_id,
                ProductPhysicalSupplyChainMetadata.is_deleted == 0,
            ).update(deletion_data)
            return response.Response(message={'status': 'ok'})
        except Exception as exception:
            sentry_sdk.capture_exception(exception)
            return response.create_error_response(
                code=error.INTERNAL_ERROR, message='mysql error', status=500
            )


def delete_supply_chain_metadata_by_product(product_id):
    """Delete supply chain metadata by product_id.

    Args:
        product_id: Product identifier ie.release_id.

    Return:
         Response: Response containing the result of deleting the records.
    """
    with mysql.db_session() as session:
        try:
            session.query(ProductPhysicalSupplyChainMetadata).filter(
                ProductPhysicalSupplyChainMetadata.product_id == product_id,
            ).delete()
            return response.Response(status=204)
        except Exception as exception:
            sentry_sdk.capture_exception(exception)
            return response.create_error_response(
                code=error.INTERNAL_ERROR, message='mysql error', status=500
            )