from typing import Any, Coroutine
from unittest.mock import MagicMock, patch

import pytest
import pytest_asyncio
from fastapi.testclient import TestClient
from sqlalchemy.sql import text
from syrupy.assertion import SnapshotAssertion

from delivery_metadata.api.app import app
from delivery_metadata.clients.art_relations import get_product
from delivery_metadata.clients.art_relations.product import ArtRelationsProduct
from delivery_metadata.constants import (
    DistributionFormatId,
    ProductTypeId,
)
from delivery_metadata.exceptions import (
    ProductMissingReleaseDate,
    ProductMissingSaleStartDate,
    ProductNotApproved,
    ProductNotFound,
)


@pytest_asyncio.fixture
async def create_product_tables(
    art_relations_product_mock: ArtRelationsProduct, test_client: TestClient
) -> None:
    async with app.state.art_relations_connector.db_session(
        transaction=True, turn_off_foreign_key_checks=True
    ) as session:
        await session.execute(text("TRUNCATE TABLE releases"))

        await session.execute(
            text(
                """
                INSERT INTO releases (
                    release_id,
                    upc,
                    display_upc,
                    product_type_id,
                    release_name,
                    original_release_date,
                    release_date,
                    delivered_version,
                    meta_language,
                    distribution_format_id,
                    product_code,
                    vendor_catalog_number,
                    genre_id,
                    p_line,
                    c_line,
                    label,
                    sale_start_date,
                    release_status,
                    preorder_date,
                    project_id
                )
                VALUES (
                    :release_id,
                    :upc,
                    :display_upc,
                    :product_type_id,
                    :release_name,
                    :original_release_date,
                    :release_date,
                    :delivered_version,
                    :meta_language,
                    :distribution_format_id,
                    :product_code,
                    :vendor_catalog_number,
                    :genre_id,
                    :p_line,
                    :c_line,
                    :label,
                    :sale_start_date,
                    :release_status,
                    :preorder_date,
                    :project_id
                )
                """
            ),
            [
                {
                    "release_id": 123,
                    "upc": 12345678901,
                    "display_upc": "12345678901",
                    "product_type_id": ProductTypeId.MUSIC.value,
                    "release_name": "test product 1",
                    "original_release_date": "2024-08-12",
                    "release_date": "2024-08-12",
                    "delivered_version": "",
                    "meta_language": "ENG",
                    "distribution_format_id": DistributionFormatId.DIGITAL_AUDIO.value,
                    "product_code": "test product code 1",
                    "release_sony_product_no": None,
                    "vendor_catalog_number": None,
                    "genre_id": 1,
                    "p_line": None,
                    "c_line": None,
                    "label": "test imprint label",
                    "sale_start_date": "2024-08-12",
                    "release_status": "label_processing",
                    "preorder_date": "2024-08-10",
                    "project_id": 1,
                },
                {
                    "release_id": 456,
                    "upc": 45678901234,
                    "display_upc": "45678901234",
                    "product_type_id": 2,  # unsupported product_type_id
                    "release_name": "test product 2",
                    "original_release_date": "2024-08-12",
                    "release_date": "2024-08-12",
                    "delivered_version": "",
                    "meta_language": "ENG",
                    "distribution_format_id": 2,  # unused distribution format id
                    "product_code": "test product code 2",
                    "release_sony_product_no": None,
                    "vendor_catalog_number": None,
                    "genre_id": 2,
                    "p_line": "2021 Test",
                    "c_line": "2021 Test",
                    "label": "test imprint label 2",
                    "sale_start_date": "2024-08-12",
                    "release_status": "in_content",
                    "preorder_date": "2024-08-10",
                    "project_id": 2,
                },
                {
                    "release_id": art_relations_product_mock.product_id,
                    "upc": art_relations_product_mock.upc,
                    "display_upc": art_relations_product_mock.display_upc,
                    "product_type_id": art_relations_product_mock.product_type_id.value,
                    "release_name": art_relations_product_mock.product_name,
                    "original_release_date": (
                        art_relations_product_mock.original_release_date
                    ),
                    "release_date": art_relations_product_mock.release_date,
                    "delivered_version": art_relations_product_mock.delivered_version,
                    "meta_language": "ENG",
                    "distribution_format_id": (
                        art_relations_product_mock.distribution_format_id.value
                    ),
                    "product_code": art_relations_product_mock.product_code,
                    "release_sony_product_no": (
                        art_relations_product_mock.release_sony_product_no
                    ),
                    "vendor_catalog_number": (
                        art_relations_product_mock.vendor_catalog_number
                    ),
                    "genre_id": art_relations_product_mock.genre_id,
                    "label": art_relations_product_mock.imprint,
                    "p_line": art_relations_product_mock.p_line,
                    "c_line": art_relations_product_mock.c_line,
                    "sale_start_date": art_relations_product_mock.sale_start_date,
                    "release_status": "in_content",
                    "preorder_date": art_relations_product_mock.preorder_date,
                    "project_id": art_relations_product_mock.project_id,
                },
                {
                    "release_id": 789,
                    "upc": 987654321,
                    "display_upc": "987654321",
                    "product_type_id": ProductTypeId.MUSIC.value,
                    "release_name": "test product 1",
                    "original_release_date": "2024-08-12",
                    "release_date": "2024-08-12",
                    "delivered_version": "",
                    "meta_language": "ENG",
                    "distribution_format_id": DistributionFormatId.DIGITAL_AUDIO.value,
                    "product_code": "test product code 1",
                    "release_sony_product_no": None,
                    "vendor_catalog_number": None,
                    "genre_id": 1,
                    "p_line": "2022 Label",
                    "c_line": "2022 Label Test",
                    "label": "test imprint label",
                    "sale_start_date": "2024-08-12",
                    "release_status": "in_content",
                    "preorder_date": "2024-08-10",
                    "project_id": 3,
                },
            ],
        )

    async with app.state.art_relations_connector.db_session(
        transaction=True, turn_off_foreign_key_checks=True
    ) as session:
        await session.execute(text("TRUNCATE TABLE project"))

        await session.execute(
            text(
                """
                INSERT INTO project (
                    project_id,
                    vendor_id
                )
                VALUES (
                    :project_id,
                    :vendor_id
                )
                """
            ),
            [
                {
                    "project_id": 1,
                    "vendor_id": 7123,
                },
                {
                    "project_id": 2,
                    "vendor_id": 7123,
                },
                {
                    "project_id": 3,
                    "vendor_id": 7123,
                },
                {
                    "project_id": 34123,
                    "vendor_id": 7123,
                },
            ],
        )

    async with app.state.art_relations_connector.db_session(
        transaction=True, turn_off_foreign_key_checks=True
    ) as session:
        await session.execute(text("TRUNCATE TABLE vendor"))

        await session.execute(
            text(
                """
                INSERT INTO vendor (
                    vendor_id,
                    company,
                    owner
                )
                VALUES (
                    :vendor_id,
                    :company,
                    :owner
                )
                """
            ),
            [
                {
                    "vendor_id": 7123,
                    "company": "test label",
                    "owner": "orchard",
                },
            ],
        )

    async with app.state.art_relations_connector.db_session(
        transaction=True, turn_off_foreign_key_checks=True
    ) as session:
        await session.execute(text("TRUNCATE TABLE language"))

        await session.execute(
            text(
                """
                INSERT INTO language (
                    language_code,
                    language,
                    iso_639_1_code,
                    iso_code_639_3_code,
                    apple_compatible
                )
                VALUES (
                    :language_code,
                    :language,
                    :iso_639_1_code,
                    :iso_code_639_3_code,
                    :apple_compatible
                )
                """
            ),
            [
                {
                    "language_code": "ENG",
                    "language": "English",
                    "iso_639_1_code": "EN",
                    "iso_code_639_3_code": "",
                    "apple_compatible": 1,
                },
                {
                    "language_code": "MAH",
                    "language": "Marshallese",
                    "iso_639_1_code": "MH",
                    "iso_code_639_3_code": "",
                    "apple_compatible": 1,
                },
            ],
        )

    async with app.state.art_relations_connector.db_session(
        transaction=True, turn_off_foreign_key_checks=True
    ) as session:
        await session.execute(text("TRUNCATE TABLE release_grid"))

    async with app.state.art_relations_connector.db_session(
        transaction=True, turn_off_foreign_key_checks=True
    ) as session:
        await session.execute(text("TRUNCATE TABLE ioda_release_mapping"))

        await session.execute(
            text(
                """
                INSERT INTO ioda_release_mapping(ioda_release_id, orchard_upc)
                VALUES (3452323, 75679660923)
                """
            )
        )

    async with app.state.art_relations_connector.db_session(
        transaction=True, turn_off_foreign_key_checks=True
    ) as session:
        await session.execute(text("TRUNCATE TABLE release_subgenre"))

        await session.execute(
            text(
                """
                INSERT INTO release_subgenre (
                    upc,
                    release_id,
                    subgenre_id
                )
                VALUES (
                    :upc,
                    :release_id,
                    :subgenre_id
                )
                """
            ),
            [
                {
                    "upc": 12345678901,
                    "release_id": 123,
                    "subgenre_id": 511,
                },
                {
                    "upc": 45678901234,
                    "release_id": 456,
                    "subgenre_id": 1158,
                },
                {
                    "upc": 75679660923,
                    "release_id": 4953147,
                    "subgenre_id": 36,
                },
            ],
        )

    async with app.state.art_relations_connector.db_session(
        transaction=True, turn_off_foreign_key_checks=True
    ) as session:
        await session.execute(text("TRUNCATE TABLE distribution_format"))

        await session.execute(
            text(
                """
                    INSERT INTO distribution_format (
                        distribution_format_id,
                        context_type
                    )
                    VALUES (
                        :distribution_format_id,
                        :context_type
                    )
                """
            ),
            [
                {
                    "distribution_format_id": 1,
                    "context_type": "digital",
                },
                {
                    "distribution_format_id": 57,
                    "context_type": "digital",
                },
                {
                    "distribution_format_id": 68,
                    "context_type": "physical",
                },
            ],
        )


@pytest.mark.asyncio
@patch("delivery_metadata.clients.art_relations.product.convert_date_to_string")
async def test_get_product(
    convert_date_to_string_mock: MagicMock,
    create_product_tables: Coroutine[Any, Any, None],
    create_genre_tables: Coroutine[Any, Any, None],
    snapshot: SnapshotAssertion,
) -> None:
    convert_date_to_string_mock.side_effect = [
        "2024-08-12",
        "2024-08-12",
        "2024-08-12",
        "2024-08-10",
    ]
    result = await get_product(75679660923)
    assert result == snapshot


@pytest.mark.asyncio
async def test_get_product_not_found(
    create_product_tables: Coroutine[Any, Any, None],
    create_genre_tables: Coroutine[Any, Any, None],
) -> None:
    with pytest.raises(ProductNotFound) as e:
        await get_product(85679660923)

    assert str(e.value) == "No product found for upc: 85679660923"


@pytest.mark.asyncio
async def test_get_product_not_approved(
    create_product_tables: Coroutine[Any, Any, None],
    create_genre_tables: Coroutine[Any, Any, None],
) -> None:
    with pytest.raises(ProductNotApproved) as e:
        await get_product(12345678901)

    assert str(e.value) == "Product with upc: 12345678901 has not been approved."


@pytest.mark.asyncio
async def test_get_product_missing_release_date(
    create_product_tables: Coroutine[Any, Any, None],
    create_genre_tables: Coroutine[Any, Any, None],
) -> None:
    async with app.state.art_relations_connector.db_session(
        transaction=True, turn_off_foreign_key_checks=True
    ) as session:
        await session.execute(
            text("UPDATE releases SET release_date = NULL WHERE upc = :upc"),
            {"upc": 987654321},
        )

    with pytest.raises(ProductMissingReleaseDate) as e:
        await get_product(987654321)

    assert str(e.value) == "Product with upc: 987654321 is missing a release date."


@pytest.mark.asyncio
async def test_get_product_missing_sale_start_date(
    create_product_tables: Coroutine[Any, Any, None],
    create_genre_tables: Coroutine[Any, Any, None],
) -> None:
    async with app.state.art_relations_connector.db_session(
        transaction=True, turn_off_foreign_key_checks=True
    ) as session:
        await session.execute(
            text("UPDATE releases SET sale_start_date = NULL WHERE upc = :upc"),
            {"upc": 987654321},
        )

    with pytest.raises(ProductMissingSaleStartDate) as e:
        await get_product(987654321)

    assert str(e.value) == "Product with upc: 987654321 is missing a sale start date."