"""Releases model."""

from sqlalchemy import text
from sqlalchemy.exc import MultipleResultsFound, NoResultFound

from carveouts.connectors.mysql import db_connector
from carveouts.exceptions import ProductInvalid, VendorInvalid
from carveouts.models.schemas import Product


def get_product(product_id: int) -> Product:
    """Check if the product is valid.

    Args:
        product_id (int) : id of product or release

    Returns:
        Product
    """
    with db_connector.db_session() as session:
        result = session.execute(
            text(
                """
                SELECT release_id, upc
                FROM releases
                WHERE release_id = :product_id
                """
            ),
            {"product_id": product_id},
        )
    try:
        row = result.mappings().one()
        return Product(product_id=row.release_id, upc=row.upc)
    except NoResultFound:
        raise ProductInvalid(f"Product ID {product_id} is invalid") from None


def get_vendor_id(product_id: int) -> int:
    """Get vendor_id for given product."""
    with db_connector.db_session() as session:
        result = session.execute(
            text(
                """
                SELECT v.vendor_id
                FROM vendor v
                INNER JOIN project p ON p.vendor_id = v.vendor_id
                INNER JOIN releases r ON r.project_id = p.project_id
                WHERE r.release_id = :product_id
                """
            ),
            {"product_id": product_id},
        )

    row = result.first()
    if not row:
        raise VendorInvalid(f"Vendor account for product {product_id} is invalid.")

    return int(row.vendor_id)


def get_subaccount_id(product_id: int) -> int:
    """Get vendor_id for given product."""
    with db_connector.db_session() as session:
        result = session.execute(
            text(
                """
                SELECT subaccount_id
                FROM releases
                WHERE release_id = :product_id
                AND subaccount_id IS NOT NULL
                """
            ),
            {"product_id": product_id},
        )

    try:
        return int(result.scalar_one())
    except (NoResultFound, MultipleResultsFound):
        return 0