from uuid import UUID

from asyncio_redis import Subscription
from orchestrator.db import (
    ProductTable,
    ResourceTypeTable,
    SubscriptionInstanceTable,
    SubscriptionInstanceValueTable,
    SubscriptionTable,
)

from gso.schemas.enums import ProductType, SubscriptionStatus


def get_active_subscriptions(
    product_type: str,
    fields: list[str],
) -> list[Subscription]:
    """Retrieve active subscriptions for a specific product type.

    :param product_type: The type of the product for which to retrieve subscriptions.
    :type product_type: str
    :param fields: List of fields to be included in the returned Subscription objects.
    :type fields: list[str]

    :return: A list of Subscription objects that match the query.
    :rtype: list[Subscription]
    """
    dynamic_fields = [getattr(SubscriptionTable, field) for field in fields]

    return (
        SubscriptionTable.query.join(ProductTable)
        .filter(
            ProductTable.product_type == product_type,
            SubscriptionTable.status == SubscriptionStatus.ACTIVE,
        )
        .with_entities(*dynamic_fields)
        .all()
    )


def get_active_site_subscriptions(fields: list[str]) -> list[Subscription]:
    """Retrieve active subscriptions specifically for sites.

    :param fields: The fields to be included in the returned Subscription objects.
    :type fields: list[str]

    :return: A list of Subscription objects for sites.
    :rtype: list[Subscription]
    """
    return get_active_subscriptions(ProductType.SITE, fields)


def get_active_router_subscriptions(fields: list[str]) -> list[Subscription]:
    """Retrieve active subscriptions specifically for routers.

    :param fields: The fields to be included in the returned Subscription objects.
    :type fields: list[str]

    :return: A list of Subscription objects for routers.
    :rtype: list[Subscription]
    """
    return get_active_subscriptions(product_type=ProductType.ROUTER, fields=fields)


def get_product_id_by_name(product_name: ProductType) -> UUID:
    """Retrieve the :term:`UUID` of a product by its name.

    :param product_name: The name of the product.
    :type product_name: ProductType

    :return UUID: The :term:`UUID` of the product.
    :rtype: UUID
    """
    return ProductTable.query.filter_by(name=product_name).first().product_id


def get_active_site_subscription_by_name(site_name: str) -> Subscription:
    """Retrieve an active subscription for a site by the site's name.

    :param site_name: The name of the site for which to retrieve the subscription.
    :type site_name: str

    :return: The Subscription object for the site.
    :rtype: Subscription
    """
    return (
        SubscriptionTable.query.join(ProductTable)
        .join(SubscriptionInstanceTable)
        .join(SubscriptionInstanceValueTable)
        .join(ResourceTypeTable)
        .filter(SubscriptionInstanceValueTable.value == site_name)
        .filter(ResourceTypeTable.resource_type == "site_name")
        .filter(SubscriptionTable.status == SubscriptionStatus.ACTIVE)
        .first()
    )