#!/bin/sh
# This line continues for Tcl, but is a single line for 'sh' \
exec tclsh "$0" ${1+"$@"}

if {[array get env HOST_TYPE] != ""} {
    set sqliteLib tclsqlite.so
} else {
    set sqliteLib tclsqlite3.dll
    set env(HOST_TYPE) win32
}

set filename [lindex $argv 0]
if {[catch {open $filename r} {fh}]} {
    puts "Couldn't open $filename"
    exit
}
set file [split [string trim [read $fh]] \n]
close $fh

proc __exit {} {
    puts goodbye
    ::db close
    exit
}

proc commit {cmds} {
    foreach cmd $cmds {
        eval $cmd
    }
    __exit
}

# CREATE TABLE medias (bootlegId, mediaId, mediaTypeId)
# CREATE TABLE trackArtists (bootlegId, mediaId, trackNum, artistId)
# CREATE TABLE tracks (bootlegId, mediaId, trackNum, songId, recordingDate, venueId);
# CREATE TABLE artists (artistId INTEGER PRIMARY KEY, lastName, firstName, birthdate, deathDate, url)
# CREATE TABLE bootlegs (bootlegId INTEGER PRIMARY KEY, name, quality)
# CREATE TABLE mediaTypes (mediaTypeId INTEGER PRIMARY KEY, type)
# CREATE TABLE songs (songId INTEGER PRIMARY KEY, title, url)
# CREATE TABLE venues (venueId INTEGER PRIMARY KEY,venue,location)

load $sqliteLib Sqlite3
sqlite3 ::db bldb.sqlite

# empty list of artists
set artists [list]

# set the media number to 0, which will get incr'ed to 1 at the first :m found
set mediaNumber 0

set trackList [list]
set trackArtistsList [list]
set mediaList [list]
set quality NULL

# go through every line in the file
for {set j 0} {$j < [llength $file]} {incr j} {

    # if the first character is a : then it is a database command
    if {[string index [lindex $file $j] 0] == ":"} {
        switch [string index [lindex $file $j] 1] {
            q {
                set quality \\\"[lindex $file [incr j]]\\\"
            }
            a {
                if {[string index [lindex $file $j] 2] == "r"} {
                    set artists [lrange $artists 0 [expr {[llength $artists] - 2}]]
                } else {
                    set artist [split [lindex $file [incr j]] " "]
                    set artistId [::db eval "select artistId from artists where artists.lastName = \"[lindex $artist end]\" and artists.firstName = \"[lrange $artist 0 [expr {[llength $artist] - 2}]]\""]
                    if {$artistId != ""} {
                        lappend artists $artistId
                    } else {
                        puts "$artist not found in the database\nline $j : [lindex $file $j]"
                        __exit
                    }
                }
            }
            d {
                set date [lindex $file [incr j]]
            }
            m {
                set mediaTypeId [::db eval "select mediaTypeId from mediaTypes where mediaTypes.type = \"[lindex $file [incr j]]\""]
                if {$mediaTypeId == ""} {
                    puts "media not found in the database\nline $j : [lindex $file $j]"
                    __exit
                }
                incr mediaNumber
                lappend mediaList [list $mediaNumber $mediaTypeId]
                set trackNumber 1
            }
            t {
                set title [lindex $file [incr j]]
                if {[::db eval "select name from bootlegs where bootlegs.name = \"$title\""] != ""} {
                    puts "title already in the database\nline $j : [lindex $file $j]"
                    __exit
                }
            }
            v {
                set venue [lindex $file [incr j]]
                set location [lindex $file [incr j]]
                if {[set venueId [::db eval "select venueId from venues where venues.venue = \"[set venue]\" and venues.location = \"[set location]\""]] == ""} {
                    puts -nonewline "Going to add this venue to the database\n$venue\n$location\nOK (Y/N)? : "
                    flush stdout
                    if {[string tolower [gets stdin]] != "y"} {
                        __exit
                    }
                    ::db eval "INSERT INTO venues VALUES (NULL, \"[set venue]\", \"[set location]\")"
                    set venueId [::db last_insert_rowid]
                }
            }
        }
    } else {
        # otherwise it is a song title
        # so see if it is in the database
        if {[set songId [::db eval "select songId from songs where songs.title = \"[lindex $file $j]\""]] == ""} {
            # find the longest word in the song
            set longest ""
            foreach word [split [lindex $file $j] " "] {
                if {[string length $word] > [string length $longest]} {
                    set longest $word
                }
            }

            # lop off the first and last letters of the word
            set longest [string range $longest 1 end-1]

            # ask the user to pick one of them
            puts "\nChoices for -->[lindex $file $j]<--"
            set choices [lsort [::db eval "select songs.title from songs where songs.title like \"%[set longest]%\""]]
            set i 0
            foreach choice $choices {
                puts "$i -->[set choice]<--"
                incr i
            }
            puts -nonewline "Selection for -->[lindex $file $j]<-- (x to quit) : "
            flush stdout
            if {[set choice [gets stdin]] != "x"} {
                set songId [::db eval "select songId from songs where songs.title = \"[lindex $choices $choice]\""]
            } else {

                # the user can quit here and add the song to the database
                __exit
            }
        }
        lappend trackList [list $mediaNumber $trackNumber $songId $date $venueId]
        lappend trackArtistsList [list $mediaNumber $trackNumber $artists]
        incr trackNumber


        # add onto the list of lists that contains the data to add to tracks and trackArists tables
        # tracks        (bootlegId, mediaId, trackNum, songId, recordingDate, venueId)
        # trackArtists  (bootlegId, mediaId, trackNum, artistId)

    }
}

package require Tk
package require Iwidgets
iwidgets::scrolledtext .t
button .commit -text commit -command {commit [split [.t  get 0.0 end] \n];__exit}
button .exit -text exit -command {__exit}

grid .t -columnspan 2 -column 0 -row 0 -sticky news
grid .commit -column 0 -row 1 -sticky news
grid .exit -column 1 -row 1 -sticky news
grid columnconfigure . 0 -weight 1
grid columnconfigure . 1 -weight 1
grid rowconfigure . 0 -weight 1
grid rowconfigure . 1 -weight 0

# so now we should have all the data to populate all the tables
# first add the bootleg title to the bootleg table and get the new bootleg id
.t insert end "::db eval \"INSERT INTO bootlegs VALUES (NULL,\\\"[set title]\\\",$quality)\"\n"
.t insert end "set bootlegId \[::db last_insert_rowid\]\n"

foreach i $mediaList {
    .t insert end "::db eval \"INSERT INTO medias VALUES (\\\"\$bootlegId\\\",\\\"[lindex $i 0]\\\",\\\"[lindex $i 1]\\\")\"\n"
}

foreach i $trackList {
    .t insert end "::db eval \"INSERT INTO tracks VALUES (\\\"\$bootlegId\\\",\\\"[lindex $i 0]\\\",\\\"[lindex $i 1]\\\",\\\"[lindex $i 2]\\\",\\\"[lindex $i 3]\\\",\\\"[lindex $i 4]\\\")\"\n"
}

foreach i $trackArtistsList {
    foreach a [lindex $i 2] {
        .t insert end "::db eval \"INSERT INTO trackArtists VALUES (\\\"\$bootlegId\\\",\\\"[lindex $i 0]\\\",\\\"[lindex $i 1]\\\",\\\"$a\\\")\"\n"
    }
}