"""
Connect to database, add a categories column if non-existant,
for each element of the db, ask for a category and update the db.
"""
import sqlite3
from pyfzf.pyfzf import FzfPrompt
import enquiries

fzf = FzfPrompt()

conn = sqlite3.connect("books-cat.sqlite")
conn.row_factory = sqlite3.Row
c = conn.cursor()

categories = [
    # TODO : Put your categories here
]


def ensure_categories():
    c.execute("PRAGMA table_info(book)")
    columns = c.fetchall()

    if not any([col["name"] == "category" for col in columns]):
        c.execute("ALTER TABLE book ADD COLUMN category TEXT DEFAULT ''")
        conn.commit()
        print("Added category column")


def add_category(category):
    c.execute("SELECT * FROM book WHERE category=''")
    books = c.fetchall()

    selection = fzf.prompt([b["title"] for b in books], f"-m --prompt \"{category} > \"")
    for title in selection:
        isbn = [book["isbn"] for book in books if book["title"] == title][0]
        c.execute("UPDATE book SET category=? WHERE isbn=?", (category, isbn))
        conn.commit()

ensure_categories()
add_category(enquiries.choose("Choose a category", categories))