#!/bin/bash
 
 #- Sqlite Datenbank von Mixxx mit Flac Dateien im Dateibaum abgleichen. -#
 
 . output_format
 . dir_exists
 . basedir_name_basen
 . checkfor
 . find_all_processing_files
 . analyze_flac_meta_tags
 . leadet_zero
 . file_backup
 . sort_txt_lists
 . find_os
 . find_usb_path
 
 
 audio_verzeichnis="${1}"
 sqlite_database_path="${HOME}/.mixxx/mixxxdb.sqlite"
 id3_app="id3v2"
 
 rating_playlist="Rating-8-10"
 cue_playlist="Cue-Files"
 filesammlung="bpm-mod-flac-dateien.csv"
 sqlite3="sqlite3"
 #playlist_path="/media/dream-plug/daten/MUSIC/Playlists/"
 find_usb_path "daten/MUSIC/Playlists/"
 
 playlist_path="${usb_path}/"
 
 
 grep_list="demo
 tmp
 Auto DJ
 Cue-Files
 "
 checkfor "${sqlite3}" "flac"
 
 clear;
 library_eintraege=`"${sqlite3}" "${sqlite_database_path}" "select count(*) from track_locations"`
 
 to_processing_files="${library_eintraege}"
 find_all_processing_files="$((to_processing_files))"
 find_to_processing_files="$((to_processing_files))"
 start_zeit=`date +%s`
 
 
 dir_exists "${1}";
 
 
 
 fs_deleted_needs_verification_reset(){
 	"${1}" "${2}" "update track_locations set fs_deleted='0'" && \
 	"${1}" "${2}" "update track_locations set needs_verification='0'" && \
 	echo -e "${green_bg}fs_deleted needs_verification${ende1} zurueckgesetzt"	
 }
 
 search_dir_filename(){
 	find "${1}" -type f -iname "*${4}*" | grep "[fmo][lpg][a3g]" | while read in_file; do 
 		[ -n "`echo "${in_file}" | grep "${3}" | grep "${2}"`" ] && \
 		#find_all_processing_files
 		echo -e "${green}${4}--${2}${ende1}" && \
 		[ -n "`"${8}" "${7}" "select location from track_locations where id like '${5}'"`" ] && \
 		
 		analyze_flac_meta_tags "${in_file}" "metaflac" "flac" && \
 		in_file=`echo "${in_file}" | sed "s/'/''/g"` && \
 		album_title=`echo "${album_title}" | sed "s/'/''/g"` && \
 		basedir_name_basen "${in_file}" && \
 
 		echo -e "${in_file} - ${5} - ${red}${album_genre}${ende1}" && \
 		"${8}" "${7}" "update track_locations set location='${in_file}' where id like '${5}'" && \
 
 		"${8}" "${7}" "update track_locations set filename='${basen}.${ext}' where id like '${5}'" && \
 		"${8}" "${7}" "update track_locations set directory='${basedir}' where id like '${5}'" && \
 
 		"${8}" "${7}" "update library set comment='${track_comment}' where id like '${5}'"
 	done
 }
 
 read_path_table(){
 	db_audio_title=`sqlite3 "${4}" "select title from library where id like '${3}'"`
 	db_audio_path=`sqlite3 "${4}" "select location from track_locations where id like '${3}'"`
 	echo "${db_audio_title}--${db_audio_path}"
 
 	basedir_name_basen "${db_audio_path}"
 	
 	[ -n "${db_audio_title}" ] && search_dir_filename "${audio_verzeichnis}" "${dirup2}" "${dirup1}" "${db_audio_title}" "${3}" "${2}" "${4}" "${5}"
 }
 
 bpm_copy(){
 	comment=`sqlite3 "${1}" "select comment from library where id like '${2}'"` && \
 	[ -n "`echo "${comment}" | grep "BPm"`" ] && comment=`echo "${comment}" | awk -F BPm '{ print $1 }'` && \
 	echo "${comment} ${2}" && \
 	sqlite3 "${1}" "update library set bpm='${comment}' where id like '${2}'"
 }
 
 process_database(){
 	library_eintraege_1="${3}"
 	while [ "${library_eintraege_1}" -gt 0 ]
 	do 
 		read_path_table "${audio_verzeichnis}" "${1}" "${library_eintraege_1}" "${2}" "${4}"
 		#bpm_copy "${2}" "${library_eintraege_1}"
 		library_eintraege_1=$((library_eintraege_1-1))
 	done
 }
 
 insert_playlist_tracks(){
 	library_eintraege_3=$((library_eintraege_3+1))
 	echo "${library_eintraege_3} ${3}"
 	"${1}" "${2}" "insert into PlaylistTracks (playlist_id,track_id) values ('${4}','${3}');"
 }
 
 select_rating_files(){
 	"${3}" "${4}"  < <( 
 	cat <<-EOF
 		SELECT l.id from library l, track_locations tl WHERE l.id = tl.id AND tl.filename LIKE '%\_R\_8%' ESCAPE '\' ORDER BY l.bpm;
 	EOF
 	) | while read genre_tracks; do
 		library_eintraege_2=$((library_eintraege_2+1))
 		echo "${library_eintraege_2} ${genre_tracks}"
 
 		"${3}" "${4}" "update library set rating='3' where id like '${genre_tracks}'"
 	done
 	library_eintraege_2=""
 
 	"${3}" "${4}"  < <( 
 	cat <<-EOF
 		SELECT l.id from library l, track_locations tl WHERE l.id = tl.id AND tl.filename LIKE '%\_R\_9%' ESCAPE '\' ORDER BY l.bpm;
 	EOF
 	) | while read genre_tracks; do
 		library_eintraege_2=$((library_eintraege_2+1))
 		echo "${library_eintraege_2} ${genre_tracks}"
 
 		"${3}" "${4}" "update library set rating='4' where id like '${genre_tracks}'"
 	done
 	library_eintraege_2=""
 
 	"${3}" "${4}"  < <( 
 	cat <<-EOF
 		SELECT l.id from library l, track_locations tl WHERE l.id = tl.id AND tl.filename LIKE '%\_R\_10%' ESCAPE '\' ORDER BY l.bpm;
 	EOF
 	) | while read genre_tracks; do
 		library_eintraege_2=$((library_eintraege_2+1))
 		echo "${library_eintraege_2} ${genre_tracks}"
 
 		"${3}" "${4}" "update library set rating='5' where id like '${genre_tracks}'"
 	done
 	library_eintraege_2=""
 }
 
 insert_cue_tracks(){
 	echo "select_cue_files_tracks ${5} ${3}"
 	"${1}" "${4}" "insert into PlaylistTracks (playlist_id,track_id) values ('${5}','${3}');"
 }
 
 manage_rating_playlist(){
 	rating_playlist_id=`"${3}" "${1}" "select id from Playlists where name like '${4}'"`
 	"${3}" "${1}" "delete from Playlists where name like '${4}'"
 	"${3}" "${1}" "delete from PlaylistTracks where playlist_id like '${rating_playlist_id}'"
 	playlist_eintraege=`"${3}" "${sqlite_database_path}" "select max(id) from Playlists"` && \
 	playlist_eintraege=$((playlist_eintraege+1))
  
 	"${3}" "${1}" "insert into Playlists (id,name) values ('${playlist_eintraege}','${4}');" && \
 	select_rating_files "${playlist_eintraege}" "${4}" "${3}" "${1}"
 }
 
 manage_cue_playlist(){
 	cue_playlist_id=`"${3}" "${1}" "select id from Playlists where name like '${4}'"`
 	"${3}" "${1}" "delete from Playlists where name like '${4}'"
 	"${3}" "${1}" "delete from PlaylistTracks where playlist_id like '${cue_playlist_id}'"
 	cue_playlist_id=`"${3}" "${1}" "select max(id) from Playlists"` && \
 	cue_playlist_id=$((cue_playlist_id+1))
 
 	"${3}" "${1}" "insert into Playlists (id,name) values ('${cue_playlist_id}','${4}');" 
 	"${3}" "${1}"  "select track_id from cues where hotcue is not '0'" | uniq | sort  | while read cue_tracks; do
 		insert_cue_tracks "${3}" "${4}" "${cue_tracks}" "${1}" "${cue_playlist_id}"
 	done
 }
 
 find_double_comments(){
 	find "${6}" -type f -iname "*${5}*" | while read in_file; do 
 		
 		"${1}" "${2}${3}" "${4}"
 
 		sed -n '/=Bpm-Manual-Check/!p' "${3}" >> "${3}tmp"
 		mv "${3}tmp" "${3}"
 		cut -d= -f1 "${3}" | sort -u | while read x; do grep "^$x" "${3}" | tail -n 1; done > "${3}tmp"
 		mv "${3}tmp" "${3}"
 	done
 }
 
 mp3_replace_tags (){
 	echo -e "${red}${artist}${ende1}----${blue}${title}${ende1}"
 	"${id3_app}" -a "${artist}" -t "${title}" "${1}"
 }
 
 
 flac_replace_tags(){
 	basedir_name_basen "${2}"
 	metatext_datei="${basedir}${basen}.txt"
 	metatext_datei_tmp="${basedir}${basen}.txt_tmp"
 
 	wav_datei="${basedir}${basen}.wav"
 	[ -f "${metatext_datei}" ] && rm "${metatext_datei}"
 	
 	"${1}" --export-tags-to="${metatext_datei}" "${2}" && \
 	cat "${metatext_datei}" | grep -iv "Comment" > "${metatext_datei_tmp}" && \
 	mv "${metatext_datei_tmp}" "${metatext_datei}" && \
 	cat "${metatext_datei}"
 
 	flac -f -d "${2}" -o "${wav_datei}" && rm "${2}" && \
 	flac -5 -f --delete-input-file --replay-gain "${wav_datei}" -o "${2}" && \
 
 	"${1}" --import-tags-from="${metatext_datei}" "${2}" && \
 	[ -f "${metatext_datei}" ] && rm "${metatext_datei}"
 }
 
 edit_bpm_comment(){
 	[ -f "${8}" ] && \
 	pbm_integer=`echo "${9}" | awk -F "." '{ print $1 }'`
 	bpm_comment_tag=`"${5}" --show-tag=Comment "${8}" | awk -F "Comment=" '{print $2}'`
 
 
 	echo "edit_bpm_comment---${bpm_comment_tag}--"
 
 
 
 	# -- Wenn Comment Tag leer --
 #	[ -z "${bpm_comment_tag}" ] && \
 #	echo -e "${yellow_bg}Kein Comment Tag vorhanden${ende1} ${red}${4}${ende1}" "${8}" && \
 #	[ -n "${4}" ] && "${5}" --set-tag=Comment="${4}BPm" "${8}"
 
 	# -- Wenn im Comment Tag der Beat der Database unterschiedlich ist --
 	# -- Flac --
 	
 	[ "${11}"=="flac" ] && \
 	[ -z "`echo ${bpm_comment_tag} | grep "${pbm_integer}"`" ] && \
 	[ -n "${bpm_comment_tag}" ] && \
 	echo -e "Comment-Tag: ${red_bg}${bpm_comment_tag}${ende1} Unterschied DB Bpm: ${red}${4}${ende1}" "${8}" && \
 	flac_replace_tags "${5}" "${8}" && \
 	[ -n "${4}" ] && "${5}" --set-tag=Comment="${4}BPm" "${8}"
 
 
 
 
 
 
 
 
 }
 
 edit_bpm_manual_measure(){
 	"${3}" "${1}" "select id from library where comment like '${4}'" | while read bpm_mod_tracks; do
 		bpm_id=`"${3}" "${1}" "select bpm from library where id like '${bpm_mod_tracks}'"`
 		path_id=`"${3}" "${1}" "select location from track_locations where id like '${bpm_mod_tracks}'"`
 		basedir_name_basen "${path_id}"
 	
 		leadet_zero "${bpm_id}"
 
 		[ -n "`echo "${bpm_id}" | awk '{ if ($1 !=0) print $1 }'`" ] && \
 		edit_bpm_comment "${2}" "${name}" "${5}" "${leadet_zero_9999}" "${6}" "${dirup1}" "${dirup2}" "${path_id}"
 	done
 }
 
 transfer_bpm(){
 	"${3}" "${1}" "select id from library where bpm like '%'" | while read bpm_mod_tracks; do
 		bpm_id=`"${3}" "${1}" "select bpm from library where id like '${bpm_mod_tracks}'"`
 		path_id=`"${3}" "${1}" "select location from track_locations where id like '${bpm_mod_tracks}'"`
 
 		#bla=`"${3}" "${1}" "select location from track_locations where id like '${bpm_mod_tracks}'"`
 		#[ -n "${path_id}" ] && echo "${bpm_id}--${path_id}--${bpm_mod_tracks}"
 
 		basedir_name_basen "${path_id}"
 		leadet_zero "${bpm_id}"
 
 
 		# neu!![ -n "${path_id}" ] &&
 
 		[ -n "${path_id}" ] &&	[ -n "`echo "${bpm_id}" | awk '{ if ($1 !=0) print $1 }'`" ] && \
 		echo "${path_id}" "${bpm_id}" "${ext}" && \
 
 
 		# -- Auf flac datei tesen -- #
 		[ "${ext}"=="flac" ] && [ -f "${path_id}" ] && \
 		edit_bpm_comment "${2}" "${name}" "${5}" "${leadet_zero_9999}" "${6}" "${dirup1}" "${dirup2}" \
 		"${path_id}" "${bpm_id}" "${7}" "${ext}"
 
 
 
 
 
 	done
 }
 
 select_track_name(){
 	"${1}" "${2}" "select artist from library where id like '${3}'" | while read track_name; do
 		tracktitle=`"${1}" "${2}" "select title from library where id like '${3}'"`
 		ziehl_pfad="${5}${4}${6}"
 		track_naem_title="${track_name};${tracktitle}"
 
 		echo "${track_naem_title}" >> "${ziehl_pfad}" 
 	done
 }
 
 select_track_id_from_playlist_tracks(){
 	"${1}" "${2}" "select track_id from PlaylistTracks where playlist_id like '${4}'" | while read track_id; do
 		select_track_name "${1}" "${2}" "${track_id}" "${3}" "${5}" "${6}"
 	done
 }
 
 add_playlist(){
 	"${3}" "${1}" "select name from Playlists" | grep -v "[[:digit:]]\{4\}" | while read playlist_name; do
 		playlist_name=`echo "${playlist_name}" | grep -v -F "${5}"`
 		playlist_id=`"${3}" "${1}" "select id from Playlists where name like '${playlist_name}'"`
 		select_track_id_from_playlist_tracks "${3}" "${1}" "${playlist_name}" "${playlist_id}" "${4}" "${6}"
 
 		playlist_path_n="${4}${playlist_name}${6}"
 
 		[ -f "${playlist_path_n}" ] && echo -e "${blue_bg}${playlist_path_n}${ende1}" && \
 		sort_txt_lists "${playlist_path_n}"
 
 	done
 }
  
 file_backup "${sqlite_database_path}" 
 
 #[ "${2}" = "" ] && \
 #manage_rating_playlist "${sqlite_database_path}" "${library_eintraege}" "${sqlite3}" "${rating_playlist}" "${playlist_eintraege}" 
 
 #[ "${2}" = "c" ] && \
 #manage_cue_playlist "${sqlite_database_path}" "" "${sqlite3}" "${cue_playlist}" "" && \
 #fs_deleted_needs_verification_reset "${sqlite3}" "${sqlite_database_path}"
 
 transfer_bpm "${sqlite_database_path}" "${1}" "${sqlite3}" "Bpm-Manual-Check" "${filesammlung}" "metaflac" ".flac"
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 # -- Export Playlists from sqlite-Database --
 #
 #add_playlist "${sqlite_database_path}" "${1}" "${sqlite3}" "${playlist_path}" "${grep_list}" "-flac-dateien.txt" 
 
 
 
 
 
 
 
 #sync
 
 
 #sqlite3 .mixxx/mixxxdb.sqlite "update library set bpm=''"
 #"${sqlite3}" "${sqlite_database_path}" "update track_locations set fs_deleted='0'"
 #"${sqlite3}" "${sqlite_database_path}" "update track_locations set needs_verification='0'"
 #sqlite3 /home/karl/.mixxx/mixxxdb.sqlite "update track_locations set fs_deleted='0'" && \
 #sqlite3 /home/karl/.mixxx/mixxxdb.sqlite "update track_locations set needs_verification='0'"