"""
AssetFinal Model.

This AssetFinal model uses sqlalchemy. It's used to store information about
encoded (final) assets
"""

from typing import Any

from sqlalchemy import (
    Column,
    ColumnElement,
    Enum,
    ForeignKey,
    Integer,
    String,
)
from sqlalchemy.exc import IntegrityError
from sqlalchemy.sql import bindparam, text

from assets.connectors import mysql
from assets.constants import api, asset_types as asset_type_const, error
from assets.exceptions import (
    AssetFinalExists,
    AssetFinalNotFound,
    BitsPerSampleNotFound,
)
from assets.utils import raw_queries


class AssetFinal(mysql.AuModel):
    """Table definition for asset_final table."""

    __tablename__ = "asset_final"

    asset_final_id = Column("id", Integer, primary_key=True, autoincrement=True)
    asset_upload_id = Column(Integer, ForeignKey("asset_upload.id"))
    asset_type = Column(String(16), nullable=True, default=None)
    asset_subtype = Column(
        Enum(*asset_type_const.ASSET_SUBTYPES), default=asset_type_const.SUBTYPE_NONE
    )
    bucket = Column(String(128), nullable=False)
    filename = Column(String(128), nullable=False)
    duration = Column(Integer, default=0)
    channels = Column(Integer, default=None)
    codec = Column(String(255), default=None)
    sample_rate = Column(Integer, default=None)
    bit_rate = Column(Integer, default=None)
    bit_depth = Column(Integer, default=None)

    def as_dict(self) -> dict[str, Any]:
        """Return object as dict.

        Returns:
            dict: Dictionary representation of object
        """
        asset_final_dict = {
            "id": self.asset_final_id,
            "asset_upload_id": self.asset_upload_id,
            "asset_type": self.asset_type,
            "asset_subtype": self.asset_subtype,
            "bucket": self.bucket,
            "filename": self.filename,
            "duration": self.duration,
            "channels": self.channels,
            "codec": self.codec,
            "sample_rate": self.sample_rate,
            "bit_rate": self.bit_rate,
            "bit_depth": self.bit_depth,
        }
        return asset_final_dict


def create_asset_final(
    asset_upload_id: int,
    asset_type: str | None,
    asset_subtype: str,
    bucket: str,
    filename: str,
    duration: int,
    channels: int | None,
    codec: str | None,
    sample_rate: int | None,
    bit_rate: int | None,
    bit_depth: int | None,
) -> None:
    """Create new final asset item in the table.

    Args:
        asset_upload_id (int): Asset upload id.
        asset_type (str | None): Asset type.
        asset_subtype (str): Asset subtype.
        bucket (str): Asset S3 bucket.
        filename (str): Asset filename.
        duration (int): Asset audio duration.
        channels (int | None): Asset audio channels.
        codec (str | None): Asset codec.
        sample_rate (int | None): Asset audio frequency.
        bit_rate (int | None): Asset audio bit rate.
        bit_depth (int | None): Asset audio bit depth.
    """
    try:
        asset_final = AssetFinal(
            asset_upload_id=asset_upload_id,
            asset_type=asset_type,
            asset_subtype=asset_subtype,
            bucket=bucket,
            filename=filename,
            duration=duration,
            channels=channels,
            codec=codec,
            sample_rate=sample_rate,
            bit_rate=bit_rate,
            bit_depth=bit_depth,
        )
        with mysql.au_db_session() as session:
            session.add(asset_final)
    except IntegrityError as e:
        raise AssetFinalExists(
            "AssetFinal already exists for "
            f"Asset Upload ID: {asset_upload_id}; "
            f"Asset Type: {asset_type}; "
            f"Asset Subtype: {asset_subtype};"
            f"SQL error: {str(e)}"
        ) from e


def get_asset_final(filename: str) -> dict[str, Any]:
    """Return final asset info by filename.

    Args:
        filename (str): unique filename

    Returns:
        dict: AssetFinal.as_dict()
    """
    return _get_asset_final_by_filters([(AssetFinal.filename == filename)])


def get_asset_final_by_id(asset_final_id: int) -> dict[str, Any]:
    """Return final asset info by asset_final_id.

    Args:
        asset_final_id (int): unique asset_final_id

    Returns:
        dict: AssetFinal.as_dict()
    """
    return _get_asset_final_by_filters([(AssetFinal.asset_final_id == asset_final_id)])


def get_asset_final_by_asset_upload_ids(
    asset_upload_ids: int | list[int], asset_type: str | None = None
) -> dict[int, list[dict[str, Any]]]:
    """Return final assets by asset_upload_id.

    Args:
        asset_upload_ids (list): List of AssetUpload ids.
        asset_type (str): asset type

    Returns:
        dict: collection of AssetFinal.as_dict()
    """
    if isinstance(asset_upload_ids, int):
        asset_upload_ids = [asset_upload_ids]

    result: dict[int, list[dict[str, Any]]] = {}
    for asset_upload_id in asset_upload_ids:
        result[asset_upload_id] = []

    filters: list[ColumnElement[bool]] = [
        (AssetFinal.asset_upload_id.in_(asset_upload_ids))
    ]
    if asset_type:
        filters.append((AssetFinal.asset_type == asset_type))

    with mysql.au_db_session(read_only=True) as session:
        asset_finals = session.query(AssetFinal).filter(*filters).all()

        for asset_final in asset_finals:
            data = asset_final.as_dict()
            result[data["asset_upload_id"]].append(data)

    if all(result[key] == [] for key in result.keys()):
        raise AssetFinalNotFound(error.ERROR_ASSET_FINAL_NOT_FOUND)

    return result


def get_asset_final_by_asset_upload_id_and_subtype(
    asset_upload_id: int, asset_subtype: str
) -> dict[str, Any]:
    """Return final asset info by asset_upload_id and asset subtype.

    Args:
        asset_upload_id (int): Asset upload id.
        asset_subtype (str): Asset subtype

    Returns:
        dict: AssetFinal.as_dict()
    """
    return _get_asset_final_by_filters(
        [
            (AssetFinal.asset_upload_id == asset_upload_id),
            (AssetFinal.asset_subtype == asset_subtype),
        ]
    )


def get_asset_final_by_asset_upload_id_and_type(
    asset_upload_id: int, asset_type: str
) -> dict[str, Any]:
    """Return final asset info by asset_upload_id and asset type.

    Args:
        asset_upload_id (int): Asset upload id.
        asset_type (str): Asset type

    Returns:
        dict: AssetFinal.as_dict()
    """
    return _get_asset_final_by_filters(
        [
            (AssetFinal.asset_upload_id == asset_upload_id),
            (AssetFinal.asset_type == asset_type),
        ]
    )


def _get_asset_final_by_filters(filters: list[ColumnElement[bool]]) -> dict[str, Any]:
    """Return final asset info by filters."""
    with mysql.au_db_session(read_only=True) as session:
        asset_final = session.query(AssetFinal).filter(*filters).first()
        if asset_final is None:
            raise AssetFinalNotFound(error.ERROR_ASSET_FINAL_NOT_FOUND)
        return asset_final.as_dict()


def get_product_asset(
    product_ids: list[int], asset_types: list[str] | None = None
) -> list[dict[str, Any]]:
    """Get assets final for products.

    Args:
        product_ids (list[int]): Product ids.
        asset_types (list[str]): Asset types in filter in.

    Returns:
        list: list of asset final records.
    """
    if asset_types is None:
        asset_types = []
    asset_types = [x.upper() for x in asset_types]

    sql_query = text(raw_queries.GET_PRODUCT_ASSETS_SQL)
    with mysql.au_db_session(read_only=True) as session:
        result = session.execute(
            sql_query.bindparams(
                bindparam("product_ids", expanding=True),
                bindparam("asset_types", expanding=True),
            ),  # explicit bindparams only needed for sqlite :-(
            {
                "product_ids": tuple(product_ids),
                "filter_asset_types": bool(asset_types),
                "asset_types": tuple(asset_types) if bool(asset_types) else (),
            },
        )

        rows = result.mappings().all()

        if not rows:
            raise AssetFinalNotFound(error.ERROR_ASSET_FINAL_NOT_FOUND)

        return [
            {
                "product_id": row["product_id"],
                "asset_type": row["asset_type"],
                "track_unique_id": row["track_unique_id"],
                "s3_bucket": row["bucket"],
                "s3_key": row["filename"],
                "updated_timestamp": row["updated_timestamp"].strftime(
                    "%Y-%m-%d %H:%M:%S"
                ),
                "updated_timestamp_us_east": row["updated_timestamp_us_east"].strftime(
                    "%Y-%m-%d %H:%M:%S"
                ),
                "duration": row["duration"],
                "duration_ms": row["duration_ms"],
            }
            for row in rows
        ]


def get_product_asset_upload_and_final(
    product_id: int, asset_types: list[str]
) -> list[dict[str, Any]]:
    """Get assets final and assets uploading for a product.

    Args:
        product_id (int): Product id.
        asset_types (list): List of asset types.

    Returns:
        list: list of asset final and asset upload records.
    """

    sql_query = raw_queries.GET_PRODUCT_ASSETS_UPLOAD_AND_FINAL_SQL.format(
        product_id=product_id,
        asset_types=", ".join("'{0}'".format(a) for a in asset_types),
    )

    with mysql.au_db_session(read_only=True) as session:
        result = session.execute(text(sql_query))
        rows = result.mappings().all()

        return [
            {
                "product_id": row["product_id"],
                "asset_type": row["asset_type"],
                "track_unique_id": row["track_unique_id"],
                "s3_bucket": row["bucket"],
                "s3_key": row["filename"],
                "updated_date": row["updated_timestamp"],
                "asset_upload_type_id": row["asset_upload_type_id"],
            }
            for row in rows
        ]


def get_bit_depths_for_products(product_id: int) -> list[int]:
    """Check desired bit depth by product_id.

    Args:
        product_id (int): reference to the product.
    Returns:
        list: valid bit-depth values of all assets for provided upc.
    """
    sql_query = raw_queries.GET_ASSET_UPLOAD_AND_FINAL_BY_PRODUCT_ID.format(
        product_id=product_id,
        asset_type=asset_type_const.TYPE_FILE_WAV,
        api_version=api.API_VERSION_V2,
    )

    with mysql.au_db_session(read_only=True) as session:
        result = session.execute(text(sql_query))
        rows = result.mappings().all()

        if rows:
            return [row.bit_depth for row in rows]
        else:
            raise BitsPerSampleNotFound(error.NO_BITS_PER_SAMPLE_FOUND)