"""art_relations DB connector."""

import pymysql
import pymysql.cursors


class ArtRelationsConnector:
    def __init__(self, host: str, user: str, password: str, database: str) -> None:
        """Initialize art_relations DB connector."""
        self._host = host
        self._user = user
        self._password = password
        self._database = database

    def get_upcs_by_display_upcs(self, display_upcs: set[str]) -> dict[str, int]:
        """Fetch the numeric UPC for each display UPC in a single query.

        Args:
            display_upcs: set of display UPC strings to resolve

        Returns:
            dict mapping each found display_upc to its numeric upc. Display UPCs
            not present in the database are omitted from the result.
        """
        if not display_upcs:
            return {}

        placeholders = ", ".join(["%s"] * len(display_upcs))
        query = (
            f"SELECT r.display_upc, r.upc FROM releases r "
            f"INNER JOIN distribution_format df ON r.distribution_format_id = df.distribution_format_id "
            f"WHERE r.display_upc IN ({placeholders}) AND df.context_type = 'digital'"
        )

        connection = pymysql.connect(
            host=self._host,
            user=self._user,
            password=self._password,
            database=self._database,
            cursorclass=pymysql.cursors.DictCursor,
        )
        try:
            with connection.cursor() as cursor:
                cursor.execute(query, list(display_upcs))
                rows = cursor.fetchall()
        finally:
            connection.close()

        return {str(row["display_upc"]): int(row["upc"]) for row in rows}