"""Product Model."""

from datetime import date

from oto import response
import sentry_sdk
from sqlalchemy import (
    asc,
    bindparam,
    BigInteger,
    Column,
    Date,
    desc,
    Enum,
    exc,
    Integer,
    String,
    text,
)
from sqlalchemy.engine.row import Row
from sqlalchemy.exc import SQLAlchemyError

from product.connectors import mysql
from product.constants import error, header
from product.constants.error import ERROR_MESSAGE_RELEASE_ARTIST_NOT_FOUND
from product.models.release_artist import ReleaseArtist
from product.models.sql.product_document import (
    PRODUCT_DOCUMENT,
    PRODUCT_DOCUMENT_WITH_COMPANY_BRAND,
    PRODUCT_DOCUMENT_WITH_TENANT_UUIDS, PRODUCT_TENANT_UUIDS, INCREASE_GROUP_CONCAT_LEN,
)
from product.models.sql.products_by_isrc import PRODUCTS_BY_ISRC
from product.models.sql.products_documents import PRODUCTS_DOCUMENTS

PRODUCT_STATUSES = [
    "label_processing",
    "transfer_to_content",
    "in_content",
    "all",
    "orchard_processing",
    "label_confirmation",
]
DEFAULT_PRODUCT_STATUS = "label_processing"
DEFAULT_PAGE_LIMIT = 50
PRODUCTS_BY_UPCS = """
    SELECT
        vp.release_id,
        vp.display_upc,
        vp.distribution_format_id,
        vp.project_id,
        vp.product_type_id,
        vp.upc,
        vp.context_type,
        vp.vendor_id,
        vp.subaccount_id,
        vp.release_date,
        vp.release_status,
        vp.release_name,
        vp.deletions,
        pt.product_type
    FROM vw_product vp
    INNER JOIN product_type pt
    ON pt.id = vp.product_type_id
    WHERE vp.upc IN {}
"""

PRODUCTS_BY_UPCS_VIEW = """
    SELECT
        vp.release_id,
        vp.display_upc,
        vp.distribution_format_id,
        vp.project_id,
        vp.product_type_id,
        vp.upc,
        vp.context_type,
        vp.vendor_id,
        vp.subaccount_id,
        vp.release_date,
        vp.release_status,
        vp.release_name,
        vp.deletions,
        pt.product_type,
        vp.format,
        vp.artist_name
    FROM vw_product vp
    INNER JOIN product_type pt
    ON pt.id = vp.product_type_id
    WHERE vp.upc IN {}
"""


PRODUCT_ID_BY_UPC_DATALOADER = """
    SELECT 
        products.upc, 
        v_product.release_id AS product_id
    FROM (
        {}
    ) AS products
    LEFT JOIN vw_product v_product ON v_product.upc = products.upc
    ORDER BY row_id ASC
"""

PRODUCT_CODES_BY_VENDOR_UUID = """
    SELECT
        vp.product_code,
        vp.release_id
    FROM vw_product vp
    LEFT JOIN vendor v
    ON v.vendor_id = vp.vendor_id
    WHERE
        v.vendor_uuid = :account_uuid
        AND vp.product_code in :product_codes
"""

PRODUCT_CODES_BY_SUBACCOUNT_UUID = """
    SELECT
        vp.product_code,
        vp.release_id
    FROM vw_product vp
    LEFT JOIN subaccount s
    ON s.subaccount_id = vp.subaccount_id
    WHERE
        s.subaccount_uuid = :account_uuid
        AND vp.product_code in :product_codes
"""


PRODUCT_OWNERSHIP_BY_PRODUCT_ID_DATALOADER = """
    SELECT
        v_product.release_id AS product_id,
        CASE
            WHEN v_product.subaccount_id IS NULL THEN 'account'
            ELSE 'subaccount'
        END AS tenant_type,
        COALESCE(v_product.subaccount_id, v_product.vendor_id) AS tenant_id
    FROM (
        {}
    ) AS products
    LEFT JOIN vw_product v_product ON v_product.release_id = products.product_id
    ORDER BY products.row_id ASC
"""


class Product(mysql.BaseModel):
    """Product Model.

    Represents vw_product view in art_relations.
    """

    __tablename__ = "vw_product"

    release_id = Column(Integer, primary_key=True, autoincrement=True, nullable=False)
    distribution_format_id = Column(Integer)
    project_id = Column(BigInteger)
    product_type_id = Column(Integer)
    upc = Column(BigInteger, nullable=False)
    context_type = Column(Enum("physical", "digital"))
    vendor_id = Column(Integer)
    subaccount_id = Column(Integer)
    product_code = Column(String)
    release_name = Column(String)
    release_status = Column(Enum(*PRODUCT_STATUSES))
    release_date = Column(Date)
    display_upc = Column(String)
    deletions = Column(Enum("Y", "N"))
    not_for_distribution = Column(String)

    def to_dict(self):
        """Return a dictionary of a product's properties."""
        release_date = (
            str(self.release_date)
            if isinstance(self.release_date, date)
            else self.release_date
        )

        return {
            "product_id": self.release_id,
            "distribution_format_id": self.distribution_format_id,
            "project_id": self.project_id,
            "product_type_id": self.product_type_id,
            "upc": self.upc,
            "display_upc": self.display_upc,
            "context_type": self.context_type,
            "vendor_id": self.vendor_id or 0,
            "subaccount_id": self.subaccount_id or 0,
            "release_date": release_date,
            "status": self.release_status,
            "product_name": self.release_name,
            "deletions": self.deletions,
            "not_for_distribution": self.not_for_distribution,
        }


def get_product_by_id(release_id):
    """Get product information by product id.

    Args:
        release_id (int): unique identifier for the product (release_id).
    Returns:
        response.Response: containing dict of product or error.
    """
    with mysql.db_session(read_only=True) as session:
        product = session.query(Product).get(release_id)

        if not product:
            return response.create_not_found_response()

        product = product.to_dict()
        return response.Response(message=product)


def get_product_document_by_id(release_id, with_company_brand=False, with_tenant_uuids=False):
    """Get the document as defined by cloudsearch-etl by the product id.

    Args:
        release_id (int): Unique identifier for the product (release_id).
        with_company_brand (bool): Include company brand fields in result or not.
        with_tenant_uuids (bool): Include 4 tenant level UUID fields in result or not.

    """
    sql_query = PRODUCT_DOCUMENT
    array_fields = ["track_list", "track_type", "isrc_list"]
    if with_company_brand:
        sql_query = PRODUCT_DOCUMENT_WITH_COMPANY_BRAND
    elif with_tenant_uuids:
        sql_query = PRODUCT_DOCUMENT_WITH_TENANT_UUIDS
        array_fields.append("spatial_isrc_list")

    with mysql.db_session(read_only=True) as session:
        try:
            select_params = {"release_id": release_id}

            session.execute(INCREASE_GROUP_CONCAT_LEN)
            product = session.execute(sql_query, select_params).fetchone()

            result = dict(product)
            if result["release_id"] is None:
                return response.create_not_found_response()

            for track_field in array_fields:
                if result[track_field]:
                    result[track_field] = result[track_field].split("\n")

            return response.Response(result)
        except SQLAlchemyError as exception:
            sentry_sdk.capture_exception(exception)
            return response.create_fatal_response()


def get_tenant_uuids(release_id):
    """Get tenant level UUID fields by the product id.

    Args:
        release_id (int): Unique identifier for the product (release_id).

    """
    sql_query = PRODUCT_TENANT_UUIDS
    with mysql.db_session(read_only=True) as session:
        try:
            select_params = {"release_id": release_id}
            product = session.execute(sql_query, select_params).fetchone()

            result = dict(product)
            if not result.get("product_id"):
                return response.create_not_found_response()
            return response.Response(result)
        except SQLAlchemyError as exception:
            sentry_sdk.capture_exception(exception)
            return response.create_fatal_response()


def get_products_documents_by_ids(release_ids):
    """Get the documents by the product ids."""
    sql_query = PRODUCTS_DOCUMENTS

    release_id_list = []
    for release_id in release_ids.split(","):
        try:
            release_id_int = int(release_id)
        except ValueError:
            release_id_int = 0
        release_id_list.append(release_id_int)

    with mysql.db_session(read_only=True) as session:
        select_params = {"release_ids": release_id_list}

        product_rows = session.execute(sql_query, select_params).fetchall()
        product_rows_dict = {row["release_id"]: dict(row) for row in product_rows}
        result = [
            {"document": product_rows_dict.get(release_id, None)}
            for release_id in release_id_list
        ]
        return response.Response(message=result)


def get_product_by_upc(upc):
    """Get the product with the given upc.

    Args:
        upc (int): the UPC value to search for.
    Returns:
        response.Response: containing dict of product or error.
    """
    with mysql.db_session() as session:
        try:
            product = session.query(Product).filter(Product.upc == upc).one_or_none()

            if not product:
                return response.create_not_found_response()

            product = product.to_dict()

            return response.Response(message=product)
        except SQLAlchemyError as exception:
            sentry_sdk.capture_exception(exception)
            return response.create_fatal_response()


def _get_products_by_product_code_for_account(
    product_codes, account_type, account_uuid, session
):
    """Get products for an account by passed product_codes.
    Result items contain "product_code" and "release_id" columns only.
    Args:
        product_codes (list[str]): product codes to find.
        account_type (str): the account type - one of subaccount or vendor.
        account_uuid (str): the account_uuid of the above type.
    Returns:
        response.Response: containing dict of product or error.
    """
    query_parameters = {
        'product_codes': product_codes,
        'account_uuid': account_uuid,
    }
    query = (
        PRODUCT_CODES_BY_VENDOR_UUID
        if account_type == header.GRASS_ACCOUNT_TYPE_VENDOR
        else PRODUCT_CODES_BY_SUBACCOUNT_UUID
    )
    query = text(query)
    query = query.bindparams(bindparam('product_codes', expanding=True))
    return session.execute(query, query_parameters)


def get_product_id_by_product_code_for_account(
    product_code, account_type, account_uuid
):
    """Get product_id for an account by product_code.
    Product must be uniquely defined by the passed parameters.
    In case of finding multiple records it returns error.
    Args:
        product_code (str): product code to find.
        account_type (str): the account type - one of subaccount or vendor.
        account_uuid (str): the account_uuid of the above type.
    Returns:
        response.Response: containing dict of product or error.
    """
    with (mysql.db_session() as session):
        try:
            products = _get_products_by_product_code_for_account(
                [product_code], account_type, account_uuid, session
            )
            product_ids = [p.release_id for p in products]

            if not product_ids:
                return response.create_not_found_response()

            if len(product_ids) > 1:
                return response.create_error_response(
                    code=error.ERROR_CODE_BAD_REQUEST,
                    message=error.ERROR_MESSAGE_MULTIPLE_PRODUCTS_FOUND,
                    status=400,
                )

            return response.Response(message=product_ids[0])
        except SQLAlchemyError as exception:
            sentry_sdk.capture_exception(exception)
            return response.create_fatal_response()


def get_first_product_by_product_code_for_account(
    product_code, account_type, account_id
):
    """Get the product for an account by product_code.

    Args:
        product_code (str): product code to find.
        account_type (str): the account type - one of subaccount or vendor.
        account_id (int): the account_id of the above type.
    Returns:
        response.Response: containing dict of product or error.
    """
    product_account_id_key = "{}_id".format(account_type)
    with mysql.db_session() as session:
        try:
            product = (
                session.query(Product)
                .filter(
                    Product.product_code == product_code,
                    getattr(Product, product_account_id_key) == account_id,
                )
                .first()
            )

            if not product:
                return response.create_not_found_response()

            product = product.to_dict()
            return response.Response(message=product)
        except SQLAlchemyError as exception:
            sentry_sdk.capture_exception(exception)
            return response.create_fatal_response()


def get_products_by_product_code_for_account(
    product_codes, account_type, account_uuid
):
    """Get the product for an account by product_code.

    Args:
        product_codes (list[str]): product code to find.
        account_type (str): the account type - one of subaccount or vendor.
        account_uuid (UUID): the account_id of the above type.
    Returns:
        response.Response: containing list of product codes
    """
    with (mysql.db_session() as session):
        try:
            product_codes = _get_products_by_product_code_for_account(
                product_codes, account_type, account_uuid, session
            )
            product_codes = [code.product_code for code in product_codes]

            return response.Response(message=product_codes)
        except SQLAlchemyError as exception:
            sentry_sdk.capture_exception(exception)
            return response.create_fatal_response()


def is_display_upc_used_by_account(
    account_type, account_id, display_upc, context_type=None
):
    """Check if display_upc is used by an account.

    Check if display_upc has been used by an account, or by an account for a
    given context_type.

    Args:
        account_type (str): account type
        account_id (int): account id
        display_upc (str): display upc of the product
        context_type (str): distribution_format.context_type(s) to check. If
            not specified both physical and digital will be checked.

    Returns:
        bool
    """
    context_types = [context_type] if context_type else ["physical", "digital"]
    with mysql.db_session() as session:
        try:
            product_count = (
                session.query(Product)
                .filter(
                    Product.deletions == "N",
                    getattr(Product, account_type + "_id") == account_id,
                    Product.display_upc == int(display_upc),
                    Product.context_type.in_(context_types),
                )
                .count()
            )

            return response.Response(message=bool(product_count))
        except SQLAlchemyError as exception:
            sentry_sdk.capture_exception(exception)
            return response.create_fatal_response()


def get_products_by_account_id(
    vendor_id=None,
    subaccount_id=None,
    status=None,
    page_offset=None,
    page_limit=None,
    start_date=None,
    end_date=None,
    sort_order=None,
    deletions=None,
    sort_by=None,
):
    """Fetch a paginated list of products with the given vendor_id.

    Args:
        vendor_id (int): vendor id.
        subaccount_id (int): subaccount id.
        page_offset (int): offset to use for pagination.
        page_limit (int): max number of items per page.
        status (str): whether the product release_status is label_processing,
            transfer_to_content or in_content. 'all' returns products in
            any release_status.
        start_date (date): start date to filter the products
            by release date.
        end_date (date): end date to filter the products
            by release date.
        sort_order (str): sort by {sort_by} asc or desc
        deletions (str): Filter by a specific deletion status.
        sort_by (str): Column name the query result should be sorted by. If none specified or
            value is invalid, defaults to release_date.

    Returns:
        response.Response: object containing paginated result set.
    """
    page_offset = int(page_offset or 0)
    page_limit = int(page_limit or DEFAULT_PAGE_LIMIT)
    product_status = status or DEFAULT_PRODUCT_STATUS
    sort_dir_map = {"asc": asc, "desc": desc}
    sort_func = desc
    sort_by = sort_by if sort_by and sort_by in Product.__table__.c else "release_date"

    if product_status not in PRODUCT_STATUSES:
        return response.create_error_response(
            code=error.ERROR_CODE_BAD_REQUEST,
            status=400,
            message=error.ERROR_MESSAGE_INVALID_STATUS,
        )

    filters = {}
    if product_status != "all":
        filters["release_status"] = product_status

    if deletions:
        filters["deletions"] = deletions

    date_filter = []
    if start_date:
        date_filter.append(Product.release_date.__ge__(start_date))
    if end_date:
        date_filter.append(Product.release_date.__le__(end_date))
    if sort_order:
        sort_func = sort_dir_map.get((sort_order.lower() or "desc"), desc)

    with mysql.db_session() as session:
        if subaccount_id and subaccount_id > 0:
            filters["subaccount_id"] = subaccount_id
        else:
            filters["vendor_id"] = vendor_id

        full_query = (
            session.query(Product)
            .filter_by(**filters)
            .order_by(sort_func(Product.__table__.c[sort_by]))
        )

        if date_filter:
            full_query = full_query.filter(*date_filter)

        total_records = len([r.to_dict() for r in full_query.all()])
        paginated_query = full_query.offset(page_offset).limit(page_limit)
        products = [r.to_dict() for r in paginated_query.all()]

        session.expunge_all()

        result_data = {
            "items": products,
            "pagination": {
                "type": "standard",
                "offset": page_offset,
                "limit": page_limit,
                "total_records": total_records,
            },
        }
        return response.Response(message=result_data)


@mysql.wrap_db_errors
def get_upcs_by_product_ids(product_list):
    """Get upc list by product id.

    Args:
        product_list (list): list of product_ids.
    Returns:
        response.Response: list of upcs.
    """
    with mysql.db_session() as session:
        upcs = (
            session.query(Product.release_id.label("product_id"), Product.upc)
            .filter(Product.release_id.in_((product_list)))
            .all()
        )

        if not upcs:
            return response.create_not_found_response(
                message=error.ERROR_MESSAGE_ALL_PRODUCTS_FAILED
            )

        products = [upc._asdict() for upc in upcs]
        products_list = {"items": products}
        return response.Response(message=products_list)


def get_products_by_upcs(upcs, sort_param="", sort_dir="ASC"):
    """Get products by the given upcs.

    Args:
        upcs (list): the UPC values to search for.
    Returns:
        response.Response: containing products
    """
    query = PRODUCTS_BY_UPCS_VIEW
    if sort_param:
        query += " ORDER BY vp.{} {}".format(sort_param, sort_dir)

    with mysql.db_session() as session:
        try:
            formatted_upcs = ",".join(str(upc) for upc in upcs)
            formatted_upcs = "(" + formatted_upcs + ")"
            product_rows = session.execute(query.format(formatted_upcs)).fetchall()

            if not product_rows:
                return response.create_not_found_response(
                    message=error.ERROR_MESSAGE_ALL_UPCS_FAILED
                )

            results = list(map(map_product_for_bulk, product_rows))
            return response.Response(message=results)
        except Exception as exception:
            sentry_sdk.capture_exception(exception)
            return response.create_fatal_response()


def get_product_id_by_upc_dataloaded(upcs):
    """Get product_ids by the given upcs dataloaded.

    Args:
        upcs (list): the UPC values to search for.
    Returns:
        response.Response: containing products
    """
    query = PRODUCT_ID_BY_UPC_DATALOADER

    with mysql.db_session() as session:
        try:
            formatted_upcs = " UNION ALL ".join(
                f"SELECT {i} AS row_id, :upc{i} AS upc" for i in range(len(upcs))
            )
            query_upcs = {f'upc{i}': upc for i, upc in enumerate(upcs)}
            product_rows = session.execute(query.format(formatted_upcs), query_upcs).fetchall()

            if not product_rows:
                return response.create_not_found_response(
                    message=error.ERROR_MESSAGE_ALL_UPCS_FAILED
                )

            results = list(map(dict, product_rows))
            return response.Response(message=results)
        except Exception as exception:
            print(f"Error in get_product_id_by_upc_dataloaded: {exception}")
            sentry_sdk.capture_exception(exception)
            return response.create_fatal_response()


def map_product_for_bulk(row):
    """Map a product row to dict.

    Args:
        row (sqlalchemy.engine.ResultProxy): the row containing the product.

    Returns:
        dict: containing the product.
    """
    row_dict = dict(row)

    product_type = row_dict["product_type"]
    display_upc = row_dict["display_upc"]

    del row_dict["product_type"]
    del row_dict["display_upc"]
    product_format = row_dict["format"]
    artist_name = row_dict["artist_name"]
    del row_dict["format"]
    del row_dict["artist_name"]

    product = Product(**row_dict).to_dict()
    product["product_type"] = product_type
    product["display_upc"] = display_upc
    product["format"] = product_format
    product["artist_name"] = artist_name

    return product


def get_product_by_display_upc(vendor_id, display_upc, context_type, show_deletions=False):
    """Fetch the vendor product with the given `display_upc` and context.

    display_upc's are not globally unique, but should be unique for a given
    vendor and context type. We should expect to get 0 or 1 products returned.

    Args:
        vendor_id (int): the vendor id to query by.
        display_upc (str): the display upc value to query by.
        context_type (str|None): optional, one of digital/physical
        show_deletions (bool): Whether to return deleted products or not
    Returns:
        response.Response: List of products or error.
    """
    with mysql.db_session() as session:
        filters = {
            "vendor_id": vendor_id,
            "display_upc": display_upc,
            "context_type": context_type,
            "deletions": "N"
        }
        if show_deletions:
            filters.pop("deletions")

        try:
            query = session.query(Product).filter_by(**filters)
        except exc.SQLAlchemyError as exception:
            sentry_sdk.capture_exception(exception)
            return response.create_fatal_response()

        product_list = []
        for r in query.all():
            product = r.to_dict()
            product_list.append(product)

        return response.Response(message=product_list)


def get_products_by_accountid_and_upcs(upcs, vendor_id, subaccount_id):
    """Get products by account id and upc(s).

    Args:
        upcs (list): list of upcs.
        vendor_id (int): vendor id.
        subaccount_id (int): subaccount id.
    Returns:
       Response: containing dict of products.
    """
    filters = []
    if vendor_id:
        filters.append(Product.vendor_id == vendor_id)
    elif subaccount_id:
        filters.append(Product.subaccount_id == subaccount_id)

    filters.append(Product.upc.in_((upcs)))

    with mysql.db_session() as session:
        result = session.query(Product).filter(*filters).all()
        products = [r.to_dict() for r in result]
        return response.Response(message=products)


def get_products_by_isrc(isrc):
    """Get the products for a given ISRC."""
    sql_query = PRODUCTS_BY_ISRC

    with mysql.db_session(read_only=True) as session:
        select_params = {"isrc": isrc}

        product_rows = session.execute(sql_query, select_params).fetchall()

        if product_rows:
            products = [Product(**dict(row)).to_dict() for row in product_rows]
            result_data = {"items": products}
            return response.Response(message=result_data)

        return response.create_not_found_response(
            message=error.ERROR_MESSAGE_PRODUCTS_NOT_FOUND.format(isrc=isrc)
        )


def get_product_by_release_artist_id(release_artist_id):
    """Get product by release_artist_id.

    Args:
        release_artist_id (int): release_artist id.
    Returns:
       Response: Product dict.
    """
    with mysql.db_session() as session:
        release_artist = (
            session.query(ReleaseArtist)
            .filter(ReleaseArtist.release_artist_id == release_artist_id)
            .one_or_none()
        )
        if not release_artist:
            return response.create_not_found_response(
                message=ERROR_MESSAGE_RELEASE_ARTIST_NOT_FOUND.format(
                    release_artist_id=release_artist_id
                )
            )

        release_id = release_artist.release_id
        product = (
            session.query(Product)
            .filter(Product.release_id == release_id)
            .one_or_none()
        )
        return response.Response(message=product.to_dict())


def filter_upcs_with_existing_products(upcs):
    """Given a list of UPC values, only return those which have existing
       product records in the database.

    Args:
        upcs (int[]): the UPC values to search for.
    Returns:
       repsonse.reponse containing list of upcs which have been found on
       existing products.
   """
    with mysql.db_session() as session:
        try:
            products = session.query(Product).filter(Product.upc.in_(upcs)).all()
            return response.Response(message=[product.upc for product in products])

        except SQLAlchemyError as exception:
            sentry_sdk.capture_exception(exception)
            return response.create_fatal_response()


def _dict_or_null_from_product_row(row: Row) -> dict | None:
    """Return row as dictionary if product_id is present (not None)."""
    row_dict = dict(row)
    return row_dict if row_dict.get('product_id') else None


def get_product_ownership_by_product_id_dataloaded(product_ids: list[int]) -> response.Response:
    """Get product ownership by product_ids dataloader format.

    Args:
        product_ids (list): the product/release id values to search for.
    Returns:
        response.Response: containing products
    """
    if not product_ids:
        return response.Response(message=[])

    query = PRODUCT_OWNERSHIP_BY_PRODUCT_ID_DATALOADER
    with mysql.db_session() as session:
        try:
            formatted_product_ids_query = " UNION ALL ".join(
                f"SELECT {i} AS row_id, :product_id{i} AS product_id"
                for i in range(len(product_ids))
            )
            query_product_ids = {
                f'product_id{i}': product_id
                for i, product_id in enumerate(product_ids)
            }
            product_rows = session.execute(
                query.format(formatted_product_ids_query),
                query_product_ids,
            ).fetchall()

            results = list(map(_dict_or_null_from_product_row, product_rows))
            return response.Response(message=results)
        except Exception as exception:
            sentry_sdk.capture_exception(exception)
            return response.create_fatal_response()