# @File: backend/services/bizmail/plans.py
# @Desc: Plan CRUD helpers for Bizmail360. Plans are the pricing catalog
#        shown on /bizmail360/pricing and selected during order.
from __future__ import annotations

from typing import List, Optional

from models.bizmail import BizmailPlan
from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession


async def list_plans(
    db: AsyncSession, *, include_inactive: bool = False
) -> List[BizmailPlan]:
    """Return plans sorted by `sort_order` then `id`."""
    stmt = select(BizmailPlan)
    if not include_inactive:
        stmt = stmt.where(BizmailPlan.is_active.is_(True))
    stmt = stmt.order_by(BizmailPlan.sort_order.asc(), BizmailPlan.id.asc())
    rows = (await db.execute(stmt)).scalars().all()
    return list(rows)


async def get_plan(db: AsyncSession, plan_id: int) -> Optional[BizmailPlan]:
    """Return a plan by primary key."""
    return (
        await db.execute(select(BizmailPlan).where(BizmailPlan.id == plan_id))
    ).scalar_one_or_none()


async def get_plan_by_slug(db: AsyncSession, slug: str) -> Optional[BizmailPlan]:
    """Return a plan by slug (unique)."""
    return (
        await db.execute(select(BizmailPlan).where(BizmailPlan.slug == slug))
    ).scalar_one_or_none()