"""Create and execute MySQL queries for tests."""

from os import getenv
from typing import Any

from tests.testutils.mysql.mysql_client import MySQLClient


class QueryHelper:
    """Create and execute MySQL queries for tests."""

    @staticmethod
    def _get_rds_client() -> MySQLClient:
        """Initialize a MySQL client using RDS environment variables."""
        host = getenv("DB_CLIENT_HOST")
        db_user = getenv("DB_CLIENT_USER")
        db_password = getenv("DB_CLIENT_PASS")
        database = getenv("DB_CLIENT_DB")
        return MySQLClient(host, database, db_user, db_password)

    @staticmethod
    def delete_asset_records(
        filename: str | None = None, product_id: int | None = None
    ) -> None:
        """Delete V2 asset upload, status, final records by filename or product_id."""
        parameter: str
        parameter_value: Any
        if filename:
            parameter = "filename"
            parameter_value = filename
        elif product_id:
            parameter = "product_id"
            parameter_value = product_id

        rds_mysql_client = QueryHelper._get_rds_client()

        asset_upload_query = (
            "SELECT id FROM asset_upload WHERE {}=%s AND api_version=2".format(
                parameter
            )
        )
        for row in rds_mysql_client.execute_query(asset_upload_query, parameter_value)[
            "rows"
        ]:
            asset_upload_id = row["id"]

            delete_asset_final = "DELETE FROM asset_final WHERE asset_upload_id=%s"
            rds_mysql_client.execute_query(delete_asset_final, asset_upload_id)

            delete_asset_status = "DELETE FROM asset_status WHERE asset_upload_id=%s"
            rds_mysql_client.execute_query(delete_asset_status, asset_upload_id)

            delete_asset_upload = (
                "DELETE FROM asset_upload WHERE id=%s AND api_version=2"
            )
            rds_mysql_client.execute_query(delete_asset_upload, asset_upload_id)

    @staticmethod
    def get_image_asset_record_by_filename(
        filename: str | None = None,
        deleted: str = "0",
        is_correction: str = "0",
        api_version: int = 2,
    ) -> str | None:
        """Retrieve image asset record from asset_upload table."""
        rds_mysql_client = QueryHelper._get_rds_client()

        asset_upload_query = f"SELECT filename FROM asset_upload WHERE \
            api_version='{api_version}' AND \
            filename='{filename}' AND \
            deleted='{deleted}' AND \
            is_correction='{is_correction}'"
        result = rds_mysql_client.execute_query(asset_upload_query, None)
        return result["rows"][0]["filename"] if result["rows"] else None