import sqlite3
import os
import glob
import shutil
import sox

shutil.rmtree('static/clip')
os.makedirs('static/clip', exist_ok=True)

db = sqlite3.connect("file:songbook.sqlite?mode=ro", uri=True)

cur = db.cursor()

for song, motif, start, duration, album, track in db.execute('''
    select
        clip.song_id, motif_id, start_ms, duration_ms, max(album_id), track
    from
        clip
	join
		song_album
	on
		clip.song_id=song_album.song_id
	group by
		clip.song_id, motif_id'''
    ):

    # get source path

    album_folder = f'{album - 1:02}. *'
    song_file = f'{track:03}. *.flac'

    source = glob.glob(f"/mnt/petal/media/Audio/Music/Soundtracks/Final Fantasy XIV/{album_folder}/{song_file}")
    assert(len(source) == 1)
    source = source[0]

    print(source)

    # get clip filename

    destination = f'static/clip/{song:04}-{motif:03}.mp3'

    print(destination)

    source_duration = sox.file_info.duration(source)

    # trim and fade start

    fade_length = 2

    if start < fade_length:
        trim_start = 0
        fade_in = 0
    else: 
        trim_start = start - fade_length
        fade_in = fade_length

    # trim and fade end (to be continued)

    if source_duration - (start + duration ) < fade_length:
        trim_end = None
        fade_out = 0
    else:
        trim_end = start + duration + fade_length
        fade_out = fade_length

    # call sox

    tf = sox.Transformer()
    tf.trim(trim_start, trim_end)
    tf.fade(fade_in, fade_out, 't')
    sox_status = tf.build_file(source, destination, return_output=True)

    # error check

    if sox_status != (0, '', ''):
        print(sox_status)