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

from gso.products.product_types.site import Site


def get_site_by_name(site_name: str) -> Site:
    """Get a site by its name.

    Args:
    ----
    site_name (str): The name of the site.
    """
    subscription = (
        SubscriptionTable.query.join(
            ProductTable, SubscriptionInstanceTable, SubscriptionInstanceValueTable, ResourceTypeTable
        )
        .filter(SubscriptionInstanceValueTable.value == site_name)
        .filter(ResourceTypeTable.resource_type == "site_name")
        .filter(SubscriptionTable.status == "active")
        .first()
    )
    if not subscription:
        raise ValueError(f"Site with name {site_name} not found.")
    return Site.from_subscription(subscription.subscription_id)