"""
HiveTextRecognition Model.

This model stores text extracted from image assets by the Hive OCR service.
"""

from typing import Any

from sqlalchemy import (
    TIMESTAMP,
    Column,
    Index,
    Integer,
    Text,
    text,
)
from sqlalchemy.exc import IntegrityError

from assets.connectors import mysql
from assets.constants import api, asset_types
from assets.exceptions import HiveTextRecognitionExists
from assets.models.asset_final import AssetFinal
from assets.models.asset_upload import AssetUpload

IMAGE_ASSET_TYPES = [
    asset_types.TYPE_FILE_TIF,
    asset_types.TYPE_FILE_JPG,
    asset_types.TYPE_FILE_JPEG,
    asset_types.TYPE_FILE_TIFF,
]


class HiveTextRecognition(mysql.AuModel):
    """Table definition for hive_text_recognition table."""

    __tablename__ = "hive_text_recognition"

    asset_final_id = Column(Integer, nullable=False, primary_key=True)
    block_text = Column(Text, nullable=False)
    created_at = Column(
        TIMESTAMP, nullable=False, server_default=text("CURRENT_TIMESTAMP")
    )
    __table_args__ = (Index("asset_final_id"),)

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

        Returns:
            dict: Dictionary representation of object
        """
        return {
            "asset_final_id": self.asset_final_id,
            "block_text": self.block_text,
        }


def create_hive_text_recognition(asset_final_id: int, block_text: str) -> None:
    """Create a hive_text_recognition record.

    Args:
        asset_final_id (int): Asset final id.
        block_text (str): Text extracted from the image asset.
    """
    try:
        record = HiveTextRecognition(
            asset_final_id=asset_final_id,
            block_text=block_text,
        )
        with mysql.au_db_session() as session:
            session.add(record)
    except IntegrityError as e:
        raise HiveTextRecognitionExists(
            f"hive text recognition data already exists for asset_final_id {asset_final_id}"
        ) from e


def get_hive_text_recognition_by_product(product_id: int) -> dict[str, Any] | None:
    """Get hive text recognition result for a product's image asset.

    Args:
        product_id (int): Product id.

    Returns:
        dict: Dict with asset_final_id and block_text, or None if not found.
    """
    with mysql.au_db_session(read_only=True) as session:
        result = (
            session.query(HiveTextRecognition)
            .join(
                AssetFinal,
                AssetFinal.asset_final_id == HiveTextRecognition.asset_final_id,
            )
            .join(
                AssetUpload,
                AssetUpload.asset_upload_id == AssetFinal.asset_upload_id,
            )
            .filter(
                AssetUpload.product_id == product_id,
                AssetUpload.deleted == 0,
                AssetUpload.api_version == api.API_VERSION_V2,
                AssetUpload.asset_upload_type_id == 2,
                AssetFinal.asset_type.in_(IMAGE_ASSET_TYPES),
                AssetFinal.asset_subtype == asset_types.SUBTYPE_LARGE_COVER,
            )
            .first()
        )
        return result.as_dict() if result else None