from itertools import groupby #used to hadle iterations
import sqlite3
from webbrowser import get
from flask import Flask, render_template, request, flash, redirect, url_for


# FUNCTIONS:

def get_db_connection():
    conn  = sqlite3.connect('database.db')
    conn.row_factory = sqlite3.Row
    return conn

# Added row_factory attribute to the slqite 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 APP:

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


@app.route('/')
def index():
    conn = get_db_connection()
    todos = conn.execute('SELECT i.id, i.done, i.content, l.title \
                          FROM items i JOIN lists l \
                          ON i.list_id = l.id ORDER BY l.title;').fetchall()

    lists = {}

    # for each list and group of items in groupby() grouper obj
    for k, g in groupby(todos, key=lambda t: t['title']):
        items = []
        # go through each to-do item row int the groupby() 
        for item in g:
            # get the assignee of the current todo item
            assignees = conn.execute('SELECT a.id, a.name FROM assignees a \
                                    JOIN items_assignees i_a \
                                    ON a.id = i_a.assignee_id \
                                    WHERE i_a.item_id = ?',
                                    (item['id'],)).fetchall()
            #convert the item row into a dictionary to add assignees
            item = dict(item)
            item['assignees'] = assignees

            items.append(item)

        # Build the list of dictionaries
        # the list's name (ex: Home/Study/Work) as the key
        # and a list of dictionaries of to-do items
        # belonging to that list as the value

        lists[k] = list(items)

    conn.close()
    return render_template('index.html', lists=lists)



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

    if request.method == 'POST':
        content = request.form['content']
        list_title = request.form['list']

# --- create the new lists here as well ---
        new_list = request.form['new_list']

        # if a new title (list) is submitted add it to the database
        if list_title == 'New List' and new_list:
            conn.execute('INSERT INTO lists (title) VALUES (?)',
                            (new_list,))
            conn.commit()
            #update list_title to refer to the newly added list
            list_title = new_list
            
        if not content:
            flash('Content is required!')
            return redirect(url_for('index'))

        list_id = conn.execute('SELECT id FROM lists WHERE title = (?);',
                                 (list_title,)).fetchone()['id']
        conn.execute('INSERT INTO items (content, list_id) VALUES (?, ?)',
                     (content, list_id))
        conn.commit()
        conn.close()
        return redirect(url_for('index'))

    lists = conn.execute('SELECT title FROM lists;').fetchall()

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


@app.route('/<int:id>/do/', methods=('POST',))
def do(id):
     conn = get_db_connection()
     conn.execute('UPDATE items SET done = 1 WHERE id = ?', (id,))
     conn.commit()
     conn.close()
     return redirect(url_for('index'))


@app.route('/<int:id>/undo/', methods=('POST',))
def undo(id):
     conn = get_db_connection()
     conn.execute('UPDATE items SET done = 0 WHERE id = ?', (id,))
     conn.commit()
     conn.close()
     return redirect(url_for('index'))


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

    todo = conn.execute('SELECT i.id, i.list_id, i.done, i.content, l.title \
                         FROM items i JOIN lists l \
                         ON i.list_id = l.id WHERE i.id = ?', (id,)).fetchone()

    lists = conn.execute('SELECT title FROM lists;').fetchall()

    if request.method == 'POST':
        content = request.form['content']
        list_title = request.form['list']
    
        if not content:
            flash('Content is required!')
            return redirect(url_for('edit', id=id))

        list_id = conn.execute('SELECT id FROM lists WHERE title = (?);',
                                (list_title,)).fetchone()['id']

        conn.execute('UPDATE items SET content = ?, list_id = ? \
                    WHERE id = ?',
                    (content, list_id, id))
        conn.commit()
        conn.close()
        return redirect(url_for('index'))

    return render_template('edit.html', todo=todo, lists=lists)


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