from datetime import date
from fastapi import APIRouter, Depends, HTTPException
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import text
from app.database import get_db

router = APIRouter(prefix="/authors", tags=["Authors"])


@router.get("/")
async def get_all_authors(db: AsyncSession = Depends(get_db)):
    result = await db.execute(text("SELECT * FROM authors ORDER BY name"))
    return result.mappings().all()


@router.get("/{author_id}")
async def get_author(author_id: int, db: AsyncSession = Depends(get_db)):
    result = await db.execute(
        text("SELECT * FROM authors WHERE id = :id"),
        {"id": author_id}
    )
    author = result.mappings().one_or_none()
    if not author:
        raise HTTPException(status_code=404, detail="Author not found")
    return author


@router.get("/{author_id}/books")
async def get_books_by_author(author_id: int, db: AsyncSession = Depends(get_db)):
    result = await db.execute(
        text("""
            SELECT b.id, b.title, b.published, b.genre
            FROM books b
            WHERE b.author_id = :author_id
            ORDER BY b.published DESC
        """),
        {"author_id": author_id}
    )
    return result.mappings().all()


@router.post("/", status_code=201)
async def create_author(
    name: str, bio: str = None, born_date: date = None,
    db: AsyncSession = Depends(get_db)
):
    await db.execute(
        text("""
            INSERT INTO authors (name, bio, born_date)
            VALUES (:name, :bio, :born_date)
        """),
        {"name": name, "bio": bio, "born_date": born_date}
    )
    await db.commit()
    return {"message": f"Author '{name}' created"}


@router.put("/{author_id}")
async def update_author(
    author_id: int, name: str = None, bio: str = None,
    db: AsyncSession = Depends(get_db)
):
    result = await db.execute(
        text("SELECT id FROM authors WHERE id = :id"), {"id": author_id}
    )
    if not result.one_or_none():
        raise HTTPException(status_code=404, detail="Author not found")

    await db.execute(
        text("""
            UPDATE authors
            SET name = COALESCE(:name, name),
                bio  = COALESCE(:bio, bio)
            WHERE id = :id
        """),
        {"id": author_id, "name": name, "bio": bio}
    )
    await db.commit()
    return {"message": "Author updated"}


@router.delete("/{author_id}", status_code=204)
async def delete_author(author_id: int, db: AsyncSession = Depends(get_db)):
    result = await db.execute(
        text("SELECT id FROM authors WHERE id = :id"), {"id": author_id}
    )
    if not result.one_or_none():
        raise HTTPException(status_code=404, detail="Author not found")

    await db.execute(
        text("DELETE FROM authors WHERE id = :id"), {"id": author_id}
    )
    await db.commit()