from sqlalchemy import text

from delivery_metadata.api.app import app
from delivery_metadata.models.schemas import TerritoryDates
from delivery_metadata.utils.datetime import convert_date_to_string


async def get_territory_dates(upc: int) -> list[TerritoryDates]:
    async with app.state.art_relations_connector.db_session() as session:
        result = await session.execute(
            text(
                """
                SELECT c.country_code,
                       rcd.release_date AS country_release_date,
                       rcd.sale_start_date AS country_sale_start_date,
                       rcd.preorder_date AS country_preorder_date
                FROM release_country_dates rcd
                INNER JOIN country c ON c.id = rcd.country_id
                WHERE rcd.upc = :upc
                ORDER BY c.country_code
            """
            ),
            {"upc": upc},
        )

        return [
            TerritoryDates(
                country_code=territory_dates_result.country_code,
                country_release_date=(
                    convert_date_to_string(territory_dates_result.country_release_date)
                    if territory_dates_result.country_release_date
                    else None
                ),
                country_sale_start_date=(
                    convert_date_to_string(
                        territory_dates_result.country_sale_start_date
                    )
                    if territory_dates_result.country_sale_start_date
                    else None
                ),
                country_preorder_date=(
                    convert_date_to_string(territory_dates_result.country_preorder_date)
                    if territory_dates_result.country_preorder_date
                    else None
                ),
            )
            for territory_dates_result in result.mappings().all()
        ]