#!/usr/bin/env python3
"""Модуль для работы с жанрами"""
from db_utils import get_cursor


def get_root_genres():
    """Возвращает корневые жанры с количеством книг"""
    conn, cur = get_cursor()

    cur.execute("""
        SELECT g.id, g.code, g.title_ru, g.title_en,
               COUNT(DISTINCT bg.book_id) as books_count
        FROM genres g
        LEFT JOIN book_genres bg ON bg.genre_id = g.id
        WHERE g.is_root = TRUE
        GROUP BY g.id, g.code, g.title_ru, g.title_en
        ORDER BY books_count DESC
    """)

    genres = cur.fetchall()
    conn.close()
    return genres


def get_subgenres(root_code):
    """Возвращает поджанры для корневого жанра"""
    conn, cur = get_cursor()

    cur.execute("""
        SELECT g.id, g.code, g.title_ru, g.title_en,
               COUNT(DISTINCT bg.book_id) as books_count
        FROM genres g
        LEFT JOIN book_genres bg ON bg.genre_id = g.id
        WHERE g.root_code = %s AND g.is_root = FALSE
        GROUP BY g.id, g.code, g.title_ru, g.title_en
        ORDER BY books_count DESC
    """, (root_code,))

    genres = cur.fetchall()
    conn.close()
    return genres


def get_genre_books(genre_id, limit=100, offset=0):
    """Возвращает книги жанра"""
    conn, cur = get_cursor()

    cur.execute("""
        SELECT b.id, b.title, b.language, b.added_date,
               string_agg(DISTINCT a.full_name, ', ' ORDER BY ba.position) as authors
        FROM books b
        JOIN book_genres bg ON bg.book_id = b.id
        JOIN book_authors ba ON ba.book_id = b.id
        JOIN authors a ON a.id = ba.author_id
        WHERE bg.genre_id = %s
        GROUP BY b.id, b.title, b.language, b.added_date
        ORDER BY b.added_date DESC NULLS LAST
        LIMIT %s OFFSET %s
    """, (genre_id, limit, offset))

    books = cur.fetchall()
    conn.close()
    return books


def get_custom_genres():
    """Возвращает кастомные жанры (не из genres.xml)"""
    conn, cur = get_cursor()

    cur.execute("""
        SELECT g.code, g.parent_code, g.root_code,
               COUNT(DISTINCT bg.book_id) as books_count
        FROM genres g
        LEFT JOIN book_genres bg ON bg.genre_id = g.id
        WHERE g.is_custom = TRUE
        GROUP BY g.code, g.parent_code, g.root_code
        ORDER BY books_count DESC
    """)

    genres = cur.fetchall()
    conn.close()
    return genres