from os import getcwd as pwd
import sqlite3

def sql_03():
    """Create a new SQL database called BookInfo that will store a list of
        authors and the books they wrote. It will have two tables. The first one
        should be called Authors and contain the following data:
          Name                                     Place of Birth
         ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
          Agatha Christie                          Torquay
          Cecelia Ahern                            Dublin
          J.K. Rowling                             Bristol
          Oscar Wilde                              Dublin
        The second should be called Books and contain the following data:
          -  Title                                      Author          Date Published
         ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ ━━━━━━━━━━━━━━━ ━━━━━━━━━━━━━━
          1  De Profundis                               Oscar Wilde     1905
          2  Harry Potter and the chamber of secrets    J.K. Rowling    1998
          3  Harry Potter and the prisioner of Azhkaban J.K. Rowling    1999
          4  Lyrebird                                   Cecelia Ahern   2017
          5  Murder on the Orient Express               Agatha Christie 1934
          6  Perfect                                    Cecelia Ahern   2017
          7  The marble collector                       Cecelia Ahern   2016
          8  The murder on the links                    Agatha Christie 1923
          9  The picture of Dorian Gray                 Oscar Wilde     1890
         10  The secret adversary                       Agatha Christie 1921
         11  The seven dials mistery                    Agatha Christie 1929
         12  The year I met you                         Cecelia Ahern   2014"""
    db_path = f"{pwd()}/sqlite/db/BookInfo.db"
    libros = [
        ["De Profundis", "Oscar Wilde", "1905"],
        ["Harry Potter and the chamber of secrets", "J.K. Rowling", "1998"],
        ["Harry Potter and the prisioner of Azhkaban", "J.K. Rowling", "1999"],
        ["Lyrebird", "Cecelia Ahern", "2017"],
        ["Murder on the Orient Express" , "Agatha Christie", "1934"],
        ["Perfect", "Cecelia Ahern", "2017"],
        ["The marble collector", "Cecelia Ahern", "2016"],
        ["The murder on the links", "Agatha Christie", "1923"],
        ["The picture of Dorian Gray", "Oscar Wilde", "1890"],
        ["The secret adversary", "Agatha Christie", "1921"],
        ["The seven dials mistery", "Agatha Christie", "1929"],
        ["The year I met you", "Cecelia Ahern", "2014"]
    ]
    autores = [
         ["Agatha Christie", "Torquay"],
         ["Cecelia Ahern", "Dublin"],
         ["J.K. Rowling", "Bristol"],
         ["Oscar Wilde","Dublin"]
    ]
    with sqlite3.connect(db_path) as db:
        cursor = db.cursor()
        cursor.execute("DROP TABLE IF EXISTS autores")
        query = """CREATE TABLE IF NOT EXISTS autores(
                    id INTEGER PRIMARY KEY,
                    nombre TEXT NOT NULL,
                    nacimiento TEXT NOT NULL)"""
        cursor.execute(query)
        db.commit()

        cursor.execute("DROP TABLE IF EXISTS libros")
        query = """CREATE TABLE IF NOT EXISTS libros(
                    id INTEGER PRIMARY KEY,
                    titulo TEXT NOT NULL,
                    autor TEXT NOT NULL,
                    fecha TEXT NOT NULL)"""
        cursor.execute(query)
        db.commit()
        for libro in libros:
            query = """INSERT INTO libros(titulo, autor, fecha)
                        VALUES(?,?,?)"""
            cursor.execute(query, [libro[0], libro[1], libro[2]])
        db.commit()
        for autor in autores:
            query = """INSERT INTO autores(nombre, nacimiento)
                        VALUES(?,?)"""
            cursor.execute(query, [autor[0], autor[1]])
        db.commit()