import uuid

from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession

from app.models.reporting import Connector


async def get_connector(db: AsyncSession, tenant_id: uuid.UUID, connector_id: object, expected_type: str) -> Connector | None:
    """Looks up a tenant's Connector by id, returning None (never raising) if
    it doesn't exist, isn't enabled, or isn't the expected type — every
    caller (Task node handlers, app/crud/payments.py) treats that the same
    way: "this integration isn't usable right now"."""
    try:
        cid = uuid.UUID(str(connector_id))
    except (ValueError, TypeError):
        return None
    connector = (
        await db.execute(select(Connector).where(Connector.tenant_id == tenant_id, Connector.id == cid))
    ).scalar_one_or_none()
    if connector is None or connector.status != "enabled" or connector.type != expected_type:
        return None
    return connector


async def first_enabled_connector(db: AsyncSession, tenant_id: uuid.UUID, connector_type: str) -> Connector | None:
    """The tenant's oldest enabled Connector of a type — for platform
    features with no per-use connector picker (e.g. new-user verification
    email/SMS, app/crud/user_verification.py)."""
    return (
        await db.execute(
            select(Connector)
            .where(Connector.tenant_id == tenant_id, Connector.type == connector_type, Connector.status == "enabled")
            .order_by(Connector.created_at)
            .limit(1)
        )
    ).scalar_one_or_none()