#  THE LIBRARY #


from itertools import groupby  # to handle complex iterations
import os
from pydoc_data.topics import topics

import sqlite3
from webbrowser import get
from flask import Flask, render_template, url_for, request, redirect, flash


# ----- functions ----- #

def get_db_connection():
    conn = sqlite3.connect('library.db')
    conn.row_factory = sqlite3.Row
    return conn
# Added row_factory attribute to the sqlite connection, in this way you can have name-based access to columns; this means that the database connection will return rows that behave like regular Python dictionaries.


# ----- FLASK ----- #

class PrefixMiddleware(object):
    def __init__(self, app, prefix=""):
        self.app = app
        self.prefix = prefix

    def __call__(self, environ, start_response):

        if environ["PATH_INFO"].startswith(self.prefix):
            environ["PATH_INFO"] = environ["PATH_INFO"][len(self.prefix):]
            environ["SCRIPT_NAME"] = self.prefix
            return self.app(environ, start_response)
        else:
            start_response("404", [("Content-Type", "text/plain")])
            return ["This url does not belong to the app.".encode()]


app = Flask(__name__)
app.config['SECRET KEY'] = 'this should be a secret random string'

# register the middleware to prefix all the requests with our base_url
app.wsgi_app = PrefixMiddleware(app.wsgi_app, prefix='/soupboat/library-db')


@ app.route("/")
def home():
    conn = get_db_connection()
    todos = conn.execute('SELECT c.id, c.content, cat.category_name  \
                          FROM cards c JOIN categories cat \
                          ON c.category_id = cat.id ORDER BY cat.category_name').fetchall()

    categories = {}
    topics = {}  # hint for later to fetch all the topics ehhe

    # for each category and group of cards for each cat in groupby() grouper object
    for k, g in groupby(todos, key=lambda t: t['category_name']):
        cards = []

        for card in g:
            topics = conn.execute('SELECT t.id, t.content FROM topics t \
                                JOIN topic_cards t_c \
                                ON t.id = t_c.topic_id \
                                WHERE t_c.card_id = ?',
                                  (card['id'],)).fetchall()

            card = dict(card)
            card['topics'] = topics
            print('card is:', card)
            cards.append(card)

        categories[k] = list(cards)

    for cat, cards in categories.items():  # ♥  dict.items () method is used to display the data elements of the dict in the form of a list of tuple pairs
        print(cat)
        for card in cards:
            topicss = [t['content'] for t in card['topics']]
            print('    ', card['content'])
            print('         ', list(topicss))

    conn.close()
    return render_template('home.html', categories=categories)


@ app.route("/add/", methods=['GET', 'POST'])
def create():
    conn = get_db_connection()

    if request.method == 'POST':
        content = request.form['content']
        category_name = request.form['cat']
        # can this be a category? o be general?
        topic_tag = request.form['topic_tag']

        # create a new category and topic:
        new_category = request.form['new_category']
        new_topic = request.form['new_topic']
        # if a new category_category_name is created add it to the table of categories
        if category_name == 'New category' and new_category:
            conn.execute('INSERT INTO categories (category_name) VALUES (?)',
                         (new_category,))
            conn.commit()
            # update category_name to refer to the newly added category
            category_name = new_category

        # if topic_tag == 'New topic' and new_topic:
        #     conn.execute('INSERT INTO topics (content) VALUES (?)',
        #                  (new_topic,))
        #     conn.commit()
        #     topic_tag = new_topic

        #     cat_id = conn.execute('SELECT id FROM categories WHERE category_name = (?);',
        #                           (category_name,)).fetchone()['id']

        #     topic_id = conn.execute('SELECT id FROM topics WHERE content = (?);',
        #                             (topic_tag,)).fetchone()['id']
        #     conn.execute('INSERT INTO topic_cards (topic_id, card_id) VALUES (?,?)',
        #                  (topic_id, cat_id))

        if topic_tag =='New topic' and new_topic:
            conn.execute('INSERT INTO topics (content) VALUES (?)',
                        (new_topic,))
            conn.commit()
            topic_tag = new_topic


        if not content:
            flash('plz write a content!')
            return redirect(url_for('home'))

        cat_id = conn.execute('SELECT id FROM categories WHERE category_name = (?);',
                              (category_name,)).fetchone()['id']

        conn.execute('INSERT INTO cards (content, category_id) VALUES (?,?)',
                     (content, cat_id))
        conn.commit()

        topic_id = conn.execute('SELECT id FROM topics WHERE content = (?);',
                                (topic_tag,)).fetchone()['id']
        card_id = conn.execute('SELECT id FROM cards WHERE content = (?);',
                                (content,)).fetchone()['id']
        conn.execute('INSERT INTO topic_cards (topic_id, card_id) VALUES (?,?)',
                     (topic_id, card_id))

        conn.commit()
        conn.close()
        return redirect(url_for('home'))

    categories = conn.execute(
        'SELECT category_name FROM categories;').fetchall()
    topics = conn.execute('SELECT content FROM topics;').fetchall()

    conn.close()
    return render_template('create.html', categories=categories, topics=topics)


@ app.route('/<int:id>/edit/', methods=('GET', 'POST'))
def edit(id):
    conn = get_db_connection()

    todo = conn.execute('SELECT c.id, c.category_id, c.content, cat.category_name \
                         FROM cards c JOIN categories cat \
                         ON c.category_id = cat.id WHERE c.id = ?', (id,)).fetchone()

    categories = conn.execute(
        'SELECT category_name FROM categories;').fetchall()

    # fetch all the topics' content from the topics table with a list comprehension    
    all_topics = [t['content'] for t in conn.execute('SELECT content FROM topics;').fetchall()]
    # print(all_topics)

    # fetch the cuurent topics of the card to edit
    fetchedtopics= conn.execute('SELECT t.id, t.content FROM topics t \
                                JOIN topic_cards t_c \
                                ON t.id = t_c.topic_id \
                                WHERE t_c.card_id = ?',
                                (id,)).fetchall()

    current_topics = [t['content'] for t in fetchedtopics]
    print('current topics are:', current_topics)

    if request.method == 'POST':
        content = request.form['content']
        category_name = request.form['cat']

        if not content:
            flash('plz insert any content!')
            return redirect(url_for('home'))

        cat_id = conn.execute('SELECT id FROM categories WHERE category_name = (?);',
                              (category_name,)).fetchone()['id']

        conn.execute('UPDATE cards SET content = ?, category_id = ? \
                    WHERE id = ?',
                     (content, cat_id, id))
        conn.commit()
        conn.close()
        return redirect(url_for('home'))

    return render_template('edit.html', todo=todo, categories=categories, all_topics=all_topics, current_topics=current_topics)


@ app.route('/<int:id>/delete/', methods=('POST',))
def delete(id):
    conn = get_db_connection()
    conn.execute('DELETE FROM cards WHERE id = ?', (id,))
    conn.commit()
    conn.close()
    return redirect(url_for('home'))

app.run(port=3150)


# TODO:


# - check for double tags
# - add more topic tags 

# - add title to cards
# - list the cards
# - put its category inside ?