#!/bin/sh
# The next line is executed by /bin/sh, but not tcl \
exec /usr/local/bin/emush $0 ${1+"$@"}
# -*-tcl-*-


proc sql_create_tables {handle template} {

    global sql
    
    ## create the database and select it
    puts $handle "DROP DATABASE IF EXISTS [$template name];"
    puts $handle "CREATE DATABASE [$template name];";
    puts $handle "use [$template name]"
    
    puts $handle "CREATE TABLE levels ("
    puts $handle "id	int NOT NULL PRIMARY KEY,"
    puts $handle "name	char(20) NOT NULL"
    puts $handle ");"

    puts $handle "CREATE TABLE tokens ("

    puts $handle "     id    char(20) NOT NULL PRIMARY KEY, "
    puts $handle "     level int NOT NULL,"
    puts $handle "     stime real NOT NULL,"
    puts $handle "     etime real NOT NULL,"
    puts $handle "     seq   int NOT NULL,"
    puts $handle "     label char(20) NOT NULL,"
    puts $handle "     utt   char(50) NOT NULL,"
    puts $handle "     INDEX labelindex (label)"
    puts $handle ");"


    puts $handle "CREATE TABLE links ("
    puts $handle "	parent	char(20) NOT NULL,"
    puts $handle "	child	char(20) NOT NULL,"
    puts $handle "	INDEX parentindex (parent),"
    puts $handle "	INDEX childindex (child)"
    puts $handle ");"

    puts $handle "LOAD DATA LOCAL INFILE '$sql(tokens,file)' INTO TABLE tokens;"
    puts $handle "LOAD DATA LOCAL INFILE '$sql(levels,file)' INTO TABLE levels;"
    puts $handle "LOAD DATA LOCAL INFILE '$sql(links,file)' INTO TABLE links;"

}

proc make_segid {utt seg} {
    return "$utt/$seg"
}

proc sql_insert_hierarchy {handle template hier} {
    global sql

    set uttname [$hier basename]
    set sql(uttid,$uttname) $uttname
    foreach l [$template getlevels] {
	set seq 0
	set segments [$hier segments $l]
	for {set i 0} {$i < [llength $segments]} {incr i} {
	    sql_insert_segment $handle $template $hier $l \
		[lindex $segments $i] [incr seq]
	}
    }
    sql_insert_links $handle $template $hier
}

proc sql_insert_levels {handle template} {
    global sql
    
    set levelid 0
    foreach l [$template getlevels] {
	set sql(levelid,$l) [incr levelid]
	puts $sql(levels) "$sql(levelid,$l)\t$l"
    }
}

proc sql_insert_segment {handle template hier level segment seq} {
    global sql

    set utt $sql(uttid,[$hier basename])
    set segid [make_segid $utt $segment]
    set label [$hier seginfo $segment label $level]
    set times [$hier seginfo $segment times]

    if {$label == {}} {set label "-"}

    puts -nonewline $sql(tokens) "$segid\t"
    puts -nonewline $sql(tokens) "$sql(levelid,$level)\t"
    puts -nonewline $sql(tokens) "[lindex $times 0]\t"
    puts -nonewline $sql(tokens) "[lindex $times 1]\t"
    puts -nonewline $sql(tokens) "$seq\t"
    puts -nonewline $sql(tokens) "$label\t"
    puts            $sql(tokens) "$utt"
}

## this needs to be done once all of the sql(segid,*) elements are set for 
## this utterance
proc sql_insert_links {handle template hier} {
    global sql
    
    set utt $sql(uttid,[$hier basename])
    foreach level [$template getlevels] {
	foreach parent [$hier segments $level] {
	    ## we go for descendents here to get all transitive links
	    foreach cl [$template descendents $level] {
		foreach child [$hier seginfo $parent children $cl] {
		    puts $sql(links) \
			"[make_segid $utt $parent]\t[make_segid $utt $child]"
		}
	    }
	}
    }
}


if {[lindex $argv 0] == "-append"} {
    set options {WRONLY APPEND CREAT}
    set templatename [lindex $argv 1]
    set pattern      [lindex $argv 2]
} else {
    set options {WRONLY TRUNC CREAT}
    set templatename [lindex $argv 0]
    set pattern      [lindex $argv 1]
}

emutemplate Template $templatename

## create a full pathname 
set dir [file join [pwd] $templatename]

if {![file exists $dir]} {
    file mkdir $dir
}

set sql(tokens,file) [file join $dir tokens.db]
set sql(tokens) [open $sql(tokens,file) $options]

set sql(levels,file) [file join $dir levels.db]
set sql(levels) [open $sql(levels,file) $options]

set sql(links,file) [file join $dir links.db]
set sql(links)  [open $sql(links,file) $options]

set handle [open [file join $dir database] w]

sql_create_tables $handle Template

set utts [Template utterances $pattern]

set utlen [llength $utts]
puts "Got $utlen utterances"

sql_insert_levels $handle Template

foreach u $utts {
    Template hierarchy Hierarchy $u 
    sql_insert_hierarchy $handle Template Hierarchy
}





## Local Variables:
## mode: tcl
## End: