import psycopg2
from psycopg2.extras import RealDictCursor


def _get_connection():
    return psycopg2.connect(dbname="books", user="postgres", password="Password!")


def create_tables():
    print("Tworzę tabele...")
    with _get_connection() as connection:
        cursor = connection.cursor()
        cursor.execute("""CREATE TABLE IF NOT EXISTS authors(
            id SERIAL PRIMARY KEY,
            first_name VARCHAR(50) NOT NULL,
            last_name VARCHAR(50) NOT NULL
        )""")

        cursor.execute("""CREATE TABLE IF NOT EXISTS books(
                id SERIAL PRIMARY KEY,
                title VARCHAR(50) NOT NULL,
                author_id INTEGER NOT NULL REFERENCES authors(id)
        )""")


def add_author(author_name: str):
    try:
        first_name, last_name = author_name.split(" ")
        print(f"Dodaję autora {first_name} {last_name}.")

        with _get_connection() as connection:
            cursor = connection.cursor()
            cursor.execute(
                "INSERT INTO authors(first_name, last_name) VALUES(%s,%s)",
                (
                    first_name,
                    last_name
                )
            )
            connection.commit()
    except ValueError:
        print("Autor musi posiadać imię i nazwisko np. Henryk Sienkiewicz.")


def list_authors():
    with _get_connection() as connection:
        cursor = connection.cursor(cursor_factory=RealDictCursor)
        cursor.execute("SELECT id, first_name, last_name FROM authors")
        return cursor.fetchall()


def add_book(title: str, author: str):
    first_name, last_name = author.split(" ")
    with _get_connection() as connection:
        cursor = connection.cursor(cursor_factory=RealDictCursor)
        # 1. Pobieram konkretnego autora
        author = get_author(first_name, last_name)
        # 2. Jeżeli autora nie ma to wyrzucam wyjątek
        if author is None:
            raise ValueError(f"Nie mam w bazie autora {first_name} {last_name}.")

        # 3. Dodaję nową książkę
        cursor.execute(
            "INSERT INTO books(title, author_id) VALUES(%s, %s)",
            (title, author["id"])
        )


def get_author(first_name: str, last_name: str):
    with _get_connection() as connection:
        cursor = connection.cursor(cursor_factory=RealDictCursor)
        cursor.execute(
            "SELECT id, first_name, last_name FROM authors WHERE first_name=%s AND last_name=%s",
            (first_name, last_name)
        )
        return cursor.fetchone()


def list_books():
    with _get_connection() as connection:
        cursor = connection.cursor(cursor_factory=RealDictCursor)
        cursor.execute(
            """select
                  authors.first_name,
                  authors.last_name,
                  books.title
                from authors
                inner join books on authors.id=books.author_id"""
        )
        return cursor.fetchall()