from __future__ import annotations

from fastapi import APIRouter, Depends, Query
from fastapi.exceptions import HTTPException
from sqlalchemy import func, select
from sqlalchemy.ext.asyncio import AsyncSession

from core.authx import auth
from core.db import get_db
from models.audio_model import Audio, AudioPlaylist
from schemas.audio_schema import Audios, AudioSchema, Playlists

router = APIRouter()

@router.get("/audio", response_model=Audios, dependencies=[Depends(auth.access_token_required)])
async def audio(session: AsyncSession = Depends(get_db), id: int = Query(...), page: int = Query(1, ge=1),  # noqa: B008
    per_page: int = Query(24, ge=1)):

    offset = (page - 1) * per_page
    query = select(Audio).where(Audio.playlist_id == id).order_by(func.random()).offset(offset).limit(per_page)
    result = await session.execute(query)

    audio = result.scalars().all()

    return {"audios": audio}

@router.get("/audio_file", response_model=AudioSchema)
async def audio_file(session: AsyncSession = Depends(get_db), id: int = Query(...)):  # noqa: B008
    query = select(Audio).where(Audio.id==id)
    result = await session.execute(query)
    audio = result.scalar()

    if audio is None:
        raise HTTPException(status_code=404, detail="Audio not found")

    return { "id": audio.id, "path": audio.path, "name": audio.name, "filename": audio.filename, "preview_path": audio.preview_path }

@router.get('/audioplaylists', response_model=Playlists, dependencies=[Depends(auth.access_token_required)])
async def audioplaylists(session: AsyncSession = Depends(get_db)):  # noqa: B008

    stmt = (
        select(
            AudioPlaylist.id, 
            AudioPlaylist.title, 
            func.count(Audio.id).label("audios_count")
        )
        .outerjoin(Audio, Audio.playlist_id == AudioPlaylist.id)
        .group_by(AudioPlaylist.id, AudioPlaylist.title)
        .order_by(AudioPlaylist.title)
    )
    
    rows = (await session.execute(stmt)).all()
    return {
        "playlists": [
            {
                "id": r.id, 
                "title": r.title, 
                "audios_count": r.audios_count
            } 
            for r in rows
        ]
    }