#!/usr/bin/python3

# CREATE MUSIC DATABASE  AND EPISODE TABLES --------------------------

import sys, os, datetime, fnmatch, glob, random, time, pathlib, re
from datetime import timedelta
from os.path import join
from tinytag import TinyTag 
from random import shuffle
import sqlite3, json
import uuid
#import pypika # sql query builder
from pypika import Query, Table, Field, Column

music_library_path = "/home/rob/uho/music/"
conn = sqlite3.connect("database/uho_music.db")

#TODO fix getting bandcamp urls into db
labelnames = [("sploh", "https://sploh.bandcamp.com"), ("terraformer"), ("pharamafabric") ]

# todo find album with no album name
mus_lib = Query \
    .create_table("MUSIC_LIBRARY") \
    .columns(
        Column("id", "VARCHAR(32)", nullable=False),
        Column("label", "VARCHAR(100)", nullable=True),
        Column("album", "VARCHAR(100)", nullable=True),
        Column("track", "VARCHAR(200)", nullable=True),
        Column("artist", "VARCHAR(120)", nullable=True),
        Column("genre", "VARCHAR(120)", nullable=True),
        Column("trackdur", "FLOAT", nullable=True),
        Column("year", "INT", nullable=True),
        Column("url", "VARCHAR(120)", nullable=True),
        Column("path", "VARCHAR(120)", nullable=False),
        Column("lastplay", "DATETINE", nullable=True),
        Column("comment", "VARCHAR(120)", nullable=True))\
    .unique("id") \
    .primary_key("id")

def database_create(conn):    
    # the MUSIC LIBRARY TABLE
    #TODO UNIQUE constraint prevents duplicats but what if tracks  are in another locaton?fixme    
    conn.execute(str(mus_lib))
    print('''MUSIC LIBRARY Table created successfully''');

    print('''
⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⣀⣀⣠⠤⠤⣄⣀⣀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀
⠀⠀⠀⠀⠀⠀⠀⢀⣤⠾⠛⠉⠀⠀⠀⠀⠀⠀⠉⠛⠷⣤⡀⠀⠀⠀⠀⠀⠀⠀
⠀⠀⠀⠀⠀⠀⠐⠟⠁⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠈⠻⠂⠀⠀⠀⠀⠀⠀
⠀⠀⠀⠀⣰⡟⠁⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠈⢻⣆⠀⠀⠀⠀
⠀⠀⠀⣰⠏⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠹⣆⠀⠀⠀
⠀⠀⢰⡏⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⢹⡆⠀⠀
⠀⠀⣾⠀⠀⠀⠀⣀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⣀⠀⠀⠀⠀⣷⠀⠀
⠀⢰⡇⠀⣰⣶⠀⣿⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⣿⠀⣶⣆⠀⢸⡆⠀
⠀⢈⠁⢠⣿⣿⠀⣿⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⣿⠀⣿⣿⡄⠈⡁⠀
⠀⢸⡇⢸⣿⣿⠀⣿⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⣿⠀⣿⣿⡇⢸⡇⠀
⠀⠈⠁⠸⣿⣿⠀⣿⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⣿⠀⣿⣿⠇⠈⠁⠀
⠀⠀⠀⠀⠻⣿⠀⣿⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⣿⠀⣿⠟⠀⠀⠀⠀
⠀⠀⠀⠀⠀⠀⠀⠛⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠛⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀
    ''');
#TODO meta idea about uho and soundwave in the above ascii
def database_create_episodes_table(conn):
    # the show database

    q = Query \
        .create_table("EPISODES") \
        .columns(
            Column("id", "INT", nullable=True),
            Column("episode", "INT", nullable=True),
            Column("track_number", "INT", nullable=True),
            Column("date", "VARCHAR(120)", nullable=True),
            Column("album", "VARCHAR(200)", nullable=True),
            Column("track", "VARCHAR(120)", nullable=True),
            Column("artist", "VARCHAR(120)", nullable=True),
            Column("trackdur", "REAL", nullable=True),
            Column("genre", "VARCHAR(120)", nullable=True),
            Column("year", 'DATETIME', nullable=True),
            Column("path", "VARCHAR(120)", nullable=False),
            Column("label", "VARCHAR(120)", nullable=False),
            Column("comment", "VARCHAR(120)", nullable=False),
            Column("episode_total_dur", "VARCHAR(120)", nullable=False),
            Column("episode_artists", "VARCHAR(120)", nullable=False),
            Column("something_else", "VARCHAR(120)", nullable=False))\
        .unique("id") \
        .primary_key("id")
    
    conn.execute(str(q))
    print("EPISODES Table created successfully");
    
def mk_db_entry(conn):
    print("ADDING TRACKS TO DATABASE > > > one moment please! ");
    label_url = "https://fixme.bandcamp.com/"
    
    for subdir, dirs, files in os.walk(music_library_path):

        for file in files:
            for m in [".flac", ".FLAC", ".mp3"]: # get audio files    
                if m in file:
                    filepath = pathlib.Path(subdir + "/"  +file)
                    label = filepath.parts[5]
                    # get LABEL etc name from path 
                    # TODO THIS will break when path changes - make relative to cwd
                    track = TinyTag.get(os.path.join(subdir, file))# get metadata from file
                    cursor = conn.cursor()
                    mus_lib =  Table('MUSIC_LIBRARY')
                    id = str(uuid.uuid4())
                    q = mus_lib.insert(id, label,  track.album, track.title, track.artist, \
                                       track.genre, track.duration, track.year, \
                                       label_url, str(filepath), 1970-1-1, track.comment)
                    cursor.execute(str(q));
                    conn.commit()                      
                    
def count_tracks(conn):
    query = f"SELECT COUNT(*) FROM MUSIC_LIBRARY"
    cursor = conn.cursor()
    cursor.execute(query)
    result = cursor.fetchone()
    row_count = result[0]
    print(f'''\n WHOOP! THE MUSIC LIBRARY CONTAINS {row_count} TRACKS!!
--------------------------------------------''')

def main():

    database_create(conn)
    database_create_episodes_table(conn)
    mk_db_entry(conn)
    count_tracks(conn)

if __name__ == "__main__":
    main()