#!/usr/bin/env python3
"""
Загрузка справочника жанров fb2 из genres.xml в PostgreSQL.

Использование:
    python parse_genres.py <genres.xml> <postgres_connection_string>
"""
import xml.etree.ElementTree as ET
import psycopg2
from psycopg2.extras import execute_values
import sys


def parse_genres_xml(xml_path, db_conn_string):
    print("Connecting to database...")
    conn = psycopg2.connect(db_conn_string)
    cur = conn.cursor()

    print("Clearing genres table...")
    cur.execute("TRUNCATE TABLE genres RESTART IDENTITY CASCADE")
    conn.commit()

    print(f"Parsing XML: {xml_path}")
    tree = ET.parse(xml_path)
    root = tree.getroot()

    genres = []
    seen_codes = set()

    for genre in root.findall('genre'):
        genre_code = genre.get('value')

        title_ru = None
        title_en = None

        for descr in genre.findall('root-descr'):
            lang = descr.get('lang')
            title = descr.get('genre-title')
            if lang == 'ru':
                title_ru = title
            elif lang == 'en':
                title_en = title

        if genre_code not in seen_codes:
            genres.append({
                'code': genre_code,
                'title_ru': title_ru,
                'title_en': title_en,
                'parent_code': None,
                'root_code': genre_code,
                'is_root': True,
                'genre_alt': []
            })
            seen_codes.add(genre_code)

        subgenres = genre.find('subgenres')
        if subgenres is not None:
            for subgenre in subgenres.findall('subgenre'):
                sub_code = subgenre.get('value')

                sub_title_ru = None
                sub_title_en = None
                genre_alt = []

                for descr in subgenre.findall('genre-descr'):
                    lang = descr.get('lang')
                    title = descr.get('title')
                    if lang == 'ru':
                        sub_title_ru = title
                    elif lang == 'en':
                        sub_title_en = title

                for alt in subgenre.findall('genre-alt'):
                    genre_alt.append(alt.get('value'))

                if sub_code not in seen_codes:
                    genres.append({
                        'code': sub_code,
                        'title_ru': sub_title_ru,
                        'title_en': sub_title_en,
                        'parent_code': genre_code,
                        'root_code': genre_code,
                        'is_root': False,
                        'genre_alt': genre_alt if genre_alt else None
                    })
                    seen_codes.add(sub_code)

    print(f"Found {len(genres)} unique genres")

    batch = []
    for g in genres:
        batch.append((
            g['code'],
            g['title_ru'],
            g['title_en'],
            g['parent_code'],
            g['root_code'],
            g['is_root'],
            False,  # is_custom
            g['genre_alt']
        ))

    execute_values(cur, """
        INSERT INTO genres (code, title_ru, title_en, parent_code, root_code,
                            is_root, is_custom, genre_alt)
        VALUES %s
    """, batch)
    conn.commit()

    cur.execute("SELECT COUNT(*) FROM genres WHERE is_root = TRUE")
    root_count = cur.fetchone()[0]

    cur.execute("SELECT COUNT(*) FROM genres WHERE is_root = FALSE")
    sub_count = cur.fetchone()[0]

    print(f"Loaded: {root_count} root genres, {sub_count} subgenres")
    conn.close()
    print("Done!")


if __name__ == "__main__":
    if len(sys.argv) != 3:
        print("Usage: python parse_genres.py <genres.xml> <postgres_connection_string>")
        sys.exit(1)
    parse_genres_xml(sys.argv[1], sys.argv[2])