#!/usr/bin/env python

import sqlite3
import bottle
import urllib

db = sqlite3.connect('iTunesLibrary.db')
db.row_factory = sqlite3.Row

ARTIST_COUNT_QUERY = """
select a.*, count(t.track_id) as total_tracks
from track t
inner join artist a on t.artist = a.id
{where}
group by t.artist
"""

GENRE_COUNT_QUERY = """
select g.*, count(t.track_id) as total_tracks
from track t
inner join genre g on t.genre = g.id
{where}
group by t.genre
"""

ALBUM_COUNT_QUERY = """
select a.id, a.title, count(t.track_id) as total_tracks
from track t
inner join album a on t.album = a.id
{where}
group by t.album
"""

TRACK_QUERY = """
select
t.track_id, t.name, t.track_number, t.disc_number,
ar.artist,
ab.title, ab.track_count, ab.disc_count,
g.name
from track t
inner join artist ar on t.artist = ar.id
inner join album ab on t.album = ab.id
inner join genre g on t.genre = g.id
{where}
"""

def _format_json(curs):
    data = []

    for item in curs.fetchall():
        data.append({
            'id': item[0],
            'name': item[1].encode('utf-8'),
            'tracks': item[2],
        })

    return data


def _run_query(curs, query, where="", params=None):
    query = query.format(where=where)

    if params:
        curs.execute(query, params)
    else:
        curs.execute(query)


def _build_where(query):
    where = []
    params = []

    if 'genre' in query:
        where.append('t.genre = ?')
        params.append(query['genre'])

    if 'artist' in query:
        where.append('t.artist = ?')
        params.append(query['artist'])

    if 'album' in query:
        where.append('t.album = ?')
        params.append(query['album'])

    if where:
        where = 'WHERE ' + ' AND '.join(where)
    else:
        where = ""

    return where, params


@bottle.route('/genre')
def genre_controller():
    curs = db.cursor()
    curs.execute(GENRE_COUNT_QUERY.format(where=""))
    return { 'data': _format_json(curs) }


@bottle.route('/browser')
def browser_controller():
    curs = db.cursor()
    where, params = _build_where(bottle.request.query)

    _run_query(curs, ARTIST_COUNT_QUERY, where, params)
    artists = _format_json(curs)

    _run_query(curs, GENRE_COUNT_QUERY, where, params)
    genres = _format_json(curs)

    _run_query(curs, ALBUM_COUNT_QUERY, where, params)
    albums = _format_json(curs)

    return {
        'data': {
            'artists': artists,
            'genres': genres,
            'albums': albums,
        }
    }


@bottle.route('/tracks')
def tracks_controller():
    curs = db.cursor()
    where, params = _build_where(bottle.request.query)
    _run_query(curs, TRACK_QUERY, where, params)
    return { 'data': [dict(row) for row in curs.fetchall()] }


bottle.debug(True)
bottle.run(host='localhost', port=8100)