from pydantic import computed_field
from sqlalchemy import text
from sqlalchemy.exc import NoResultFound

from delivery_metadata.api.app import app
from delivery_metadata.constants import (
    DistributionContext,
    DistributionFormatId,
    ProductTypeId,
)
from delivery_metadata.exceptions import (
    ProductMissingReleaseDate,
    ProductMissingSaleStartDate,
    ProductNotApproved,
    ProductNotFound,
)
from delivery_metadata.models import Model
from delivery_metadata.utils.datetime import convert_date_to_string


class ArtRelationsProduct(Model):
    product_id: int
    upc: int
    display_upc: str
    product_type_id: ProductTypeId
    original_release_date: str | None
    release_date: str
    sale_start_date: str
    preorder_date: str | None
    product_name: str
    delivered_version: str | None
    release_grid: str | None
    metadata_language_ietf_rfc_5646_code: str | None
    distribution_format_id: DistributionFormatId
    product_code: str | None
    release_sony_product_no: str | None
    vendor_catalog_number: str | None
    genre_id: int
    subgenre_id: int
    genre_name: str
    subgenre_name: str | None
    p_line: str | None
    c_line: str
    imprint: str
    distribution_context: DistributionContext
    ioda_release_id: int | None
    project_id: int
    vendor_id: int
    vendor_owner: str

    @computed_field  # type: ignore[prop-decorator]
    @property
    def content_id(self) -> int:
        return self.ioda_release_id if self.ioda_release_id else self.upc


async def get_product(upc: int) -> ArtRelationsProduct:
    async with app.state.art_relations_connector.db_session() as session:
        result = await session.execute(
            text(
                """
                WITH rs AS (
                    SELECT sg.orchard_id AS subgenre_id, sg.name AS subgenre_name, sg.genre_id
                    FROM subgenre sg
                    INNER JOIN release_subgenre rs ON rs.subgenre_id = sg.orchard_id
                    WHERE rs.upc = :upc
                )
                SELECT
                    r.release_id AS product_id,
                    r.upc,
                    r.display_upc,
                    r.product_type_id,
                    r.release_name AS product_name,
                    r.original_release_date,
                    r.release_date,
                    r.sale_start_date,
                    r.preorder_date,
                    r.delivered_version,
                    r.distribution_format_id,
                    r.product_code,
                    r.vendor_catalog_number,
                    r.genre_id,
                    r.label AS imprint,
                    r.p_line,
                    r.c_line,
                    r.release_status,
                    r.project_id,
                    rg.grid AS release_grid,
                    rg.product_no AS release_sony_product_no,
                    lower(metadata_language.iso_639_1_code) as metadata_language_iso_639_1_code,
                    lower(metadata_language.iso_code_639_3_code) as metadata_language_iso_639_3_code,
                    g.genre AS genre_name,
                    rs.subgenre_id,
                    rs.subgenre_name,
                    df.context_type AS distribution_context,
                    irm.ioda_release_id,
                    v.vendor_id,
                    v.owner AS vendor_owner
                FROM releases r
                INNER JOIN project p ON p.project_id = r.project_id
                INNER JOIN vendor v ON p.vendor_id = v.vendor_id
                INNER JOIN genre g ON g.genre_id = r.genre_id
                INNER JOIN distribution_format df ON r.distribution_format_id = df.distribution_format_id
                LEFT JOIN language metadata_language ON metadata_language.language_code = r.meta_language
                LEFT JOIN release_grid rg ON rg.release_id = r.release_id
                LEFT JOIN rs ON r.genre_id = rs.genre_id
                LEFT JOIN ioda_release_mapping irm ON irm.orchard_upc = r.upc
                WHERE r.upc = :upc
                """
            ),
            {
                "upc": upc,
            },
        )

    try:
        product_result = result.mappings().one()
    except NoResultFound as exc:
        raise ProductNotFound(f"No product found for upc: {upc}") from exc

    if product_result.release_status != "in_content":
        raise ProductNotApproved(f"Product with upc: {upc} has not been approved.")
    if not product_result.release_date:
        raise ProductMissingReleaseDate(
            f"Product with upc: {upc} is missing a release date."
        )
    if not product_result.sale_start_date:
        raise ProductMissingSaleStartDate(
            f"Product with upc: {upc} is missing a sale start date."
        )

    return ArtRelationsProduct(
        product_id=product_result.product_id,
        upc=product_result.upc,
        display_upc=product_result.display_upc,
        product_type_id=product_result.product_type_id,
        original_release_date=(
            convert_date_to_string(product_result.original_release_date)
            if product_result.original_release_date
            else None
        ),
        release_date=convert_date_to_string(product_result.release_date),
        sale_start_date=convert_date_to_string(product_result.sale_start_date),
        preorder_date=(
            convert_date_to_string(product_result.preorder_date)
            if product_result.preorder_date
            else None
        ),
        product_name=product_result.product_name,
        delivered_version=product_result.delivered_version,
        release_grid=product_result.release_grid,
        metadata_language_ietf_rfc_5646_code=(
            product_result.metadata_language_iso_639_1_code
            or product_result.metadata_language_iso_639_3_code
        ),
        distribution_format_id=product_result.distribution_format_id,
        product_code=product_result.product_code,
        release_sony_product_no=product_result.release_sony_product_no,
        vendor_catalog_number=product_result.vendor_catalog_number,
        genre_id=product_result.genre_id,
        subgenre_id=product_result.subgenre_id,
        genre_name=product_result.genre_name,
        subgenre_name=product_result.subgenre_name,
        imprint=product_result.imprint,
        p_line=product_result.p_line,
        c_line=product_result.c_line,
        distribution_context=product_result.distribution_context,
        ioda_release_id=product_result.ioda_release_id,
        project_id=product_result.project_id,
        vendor_id=product_result.vendor_id,
        vendor_owner=product_result.vendor_owner,
    )