"""Voice command service — CRUD for user-specific dynamic commands."""

from uuid import uuid4

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

from app.models.voice_command import VoiceCommand


async def list_commands(
    db: AsyncSession,
    user_id: str,
    active_only: bool = True,
) -> list[dict]:
    stmt = select(VoiceCommand).where(VoiceCommand.user_id == user_id)
    if active_only:
        stmt = stmt.where(VoiceCommand.is_active == True)
    stmt = stmt.order_by(VoiceCommand.count.desc())
    result = await db.execute(stmt)
    return [
        {
            "id": str(c.id),
            "phrase": c.phrase,
            "action": c.action,
            "agent_name": c.agent_name,
            "count": c.count,
            "is_active": c.is_active,
        }
        for c in result.scalars().all()
    ]


async def create_command(
    db: AsyncSession,
    user_id: str,
    phrase: str,
    action: str,
    agent_name: str | None = None,
) -> dict:
    cmd = VoiceCommand(
        id=uuid4(),
        user_id=user_id,
        phrase=phrase,
        action=action,
        agent_name=agent_name,
        count=0,
        is_active=True,
    )
    db.add(cmd)
    await db.commit()
    await db.refresh(cmd)
    return {
        "id": str(cmd.id),
        "phrase": cmd.phrase,
        "action": cmd.action,
        "agent_name": cmd.agent_name,
        "count": cmd.count,
        "is_active": cmd.is_active,
    }


async def increment_command(
    db: AsyncSession,
    command_id: str,
) -> bool:
    result = await db.execute(
        update(VoiceCommand)
        .where(VoiceCommand.id == command_id)
        .values(count=VoiceCommand.count + 1)
    )
    await db.commit()
    return result.rowcount > 0


async def delete_command(db: AsyncSession, command_id: str, user_id: str) -> bool:
    result = await db.execute(
        select(VoiceCommand).where(
            VoiceCommand.id == command_id,
            VoiceCommand.user_id == user_id,
        )
    )
    cmd = result.scalar_one_or_none()
    if not cmd:
        return False
    await db.delete(cmd)
    await db.commit()
    return True


async def get_suggested_command(
    db: AsyncSession,
    user_id: str,
    min_count: int = 3,
) -> dict | None:
    """Find a frequently used command that could be suggested."""
    result = await db.execute(
        select(VoiceCommand)
        .where(VoiceCommand.user_id == user_id)
        .where(VoiceCommand.count >= min_count)
        .where(VoiceCommand.is_active == True)
        .order_by(VoiceCommand.count.desc())
        .limit(1)
    )
    cmd = result.scalar_one_or_none()
    if not cmd:
        return None
    return {
        "phrase": cmd.phrase,
        "action": cmd.action,
        "agent_name": cmd.agent_name,
    }