import sqlite3

from whoosh import fields, index

TRACK_QUERY = """
SELECT
    t.track_id, t.composer, t.explicit, t.disc_number, t.name as track_name,
    t.track_number, t.year as track_year, g.name as genre, a.artist,
    ab.title as album, ab.artist as album_artist, ab.compilation,
    ab.disc_count, ab.gapless, ab.release_date, ab.track_count,
    ab.year as album_year, p.bpm, p.bit_rate, p.sample_rate, p.total_time,
    k.kind
FROM
    track t
INNER JOIN
    genre g
ON  t.genre = g.id
INNER JOIN
    artist a
ON  t.artist = a.id
INNER JOIN
    album ab
ON  t.album = ab.id
INNER JOIN
    track_physical p
ON  t.track_id = p.track_id
INNER JOIN
    kind k
ON  p.kind = k.id
"""

def safe_unicode(value):
    return value
    return value.decode("utf-8") if value else None

def safe_int(value):
    return int(value) if value is not None else None

def to_boolean(value):
    return 1 if value else 0

def first_value(*items):
    for item in items:
        if item is not None:
            return item

    return None


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

curs = db.cursor()
curs.execute(TRACK_QUERY)

schema = fields.Schema(
    track_id=fields.ID(stored=True),
    composer=fields.TEXT(stored=True),
    explicit=fields.BOOLEAN(stored=True),
    disc_number=fields.NUMERIC(stored=True),

    track_name=fields.NGRAM(stored=True),
    genre=fields.NGRAM(stored=True),
    artist=fields.NGRAM(stored=True),
    album=fields.NGRAM(stored=True),

    track_number=fields.NUMERIC(stored=True),
    year=fields.NUMERIC(stored=True),
    compilation=fields.BOOLEAN(stored=True),
    disc_count=fields.NUMERIC(stored=True),
    gapless=fields.BOOLEAN(stored=True),
    release_date=fields.DATETIME(stored=True),
    track_count=fields.NUMERIC(stored=True),
    bpm=fields.NUMERIC(stored=True),
    bit_rate=fields.NUMERIC(stored=True),
    sample_rate=fields.NUMERIC(stored=True),
    total_time=fields.NUMERIC(stored=True),
    kind=fields.TEXT(stored=True)
)

idx = index.create_in("indexdir", schema)
writer = idx.writer()

for record in curs.fetchall():
    writer.add_document(
        track_id=str(record['track_id']).decode("ascii"),

        composer=safe_unicode(record['composer']),
        genre=safe_unicode(record['genre']),
        album=safe_unicode(record['album']),
        artist=safe_unicode(first_value(record['artist'], record['album_artist'])),
        track_name=safe_unicode(record['track_name']),
        kind=safe_unicode(record['kind']),

        #release_date=record['release_date'],

        explicit=to_boolean(record['explicit']),
        compilation=to_boolean(record['compilation']),
        gapless=to_boolean(record['gapless']),

        disc_number=safe_int(record['disc_number']),
        track_number=safe_int(record['track_number']),
        year=safe_int(first_value(record['track_year'], record['album_year'])),
        disc_count=safe_int(record['disc_count']),
        track_count=safe_int(record['track_count']),
        bpm=safe_int(record['bpm']),
        bit_rate=safe_int(record['bit_rate']),
        sample_rate=safe_int(record['sample_rate']),
        total_time=safe_int(record['total_time']),
        )

writer.commit()
db.close()