#!/usr/bin/tclsh8.5
# Logs to base converter utility for "Bldjid2" plugin.
# 2010-06-27 v. 0.9.11.12
# by Serge Yudin xmpp:bigote@jabber.ru

package require sqlite3
package require md5
package require base64

proc convert_ts {ts} {
    set ts [string trim $ts \]\[]
    if {[string match *\ * $ts]} {
	if {[string match "Dec" $ts]} {
	    append ts " 2009"
	} else {
	    append ts " 2010"
	}
	return [clock scan $ts -format "%a %b %e %H:%M %Y"]
    } else {
	return [clock scan $ts -format "%Y-%m-%e_%H:%M:%S"]
    }
}

proc hash {string} {
    set binstr [encoding convertto utf-8 $string]<
    if {[catch {::md5::md5 -hex $binstr} hex]} {
	set hex [::md5::md5 $binstr]
    }
    set binhash [binary format H32 $hex]
    return [base64::encode $binhash]
}

puts [clock format [clock seconds] -format "%a %b %e %H:%M:%S"]
set log [lindex $argv 0]
set base [lindex $argv 1]
if { $log eq "" } {
    puts "Please specify a path to your log file. \
    Usage:\n./log2base.tcl /path/to/log /path/to/base"
    exit
}
if { $base eq "" } {
    puts "Please specify a path to your future base file. \
    Usage:\n./log2base.tcl /path/to/log /path/to/base"
    exit
}
if { [catch {open $log} fd] } {
    puts "ERROR: cannot open log file."
    exit
}

if {[file exists $base]} {
    file copy $base $base.[clock seconds]
    sqlite3 db $base
} else {
    sqlite3 db $base
    db eval {
	CREATE TABLE client(
	    id INTEGER PRIMARY KEY,
	    cli TEXT
	);
    }
    db eval {
	CREATE TABLE version(
	    id INTEGER PRIMARY KEY,
	    ver TEXT
	);
    }
    db eval {
	CREATE TABLE osys(
	    id INTEGER PRIMARY KEY,
	    os TEXT
	);
    }
    db eval {
	CREATE TABLE timezone(
	    id INTEGER PRIMARY KEY,
	    tz TEXT
	);
    }
    db eval {
	CREATE TABLE visitors(
	    id INTEGER PRIMARY KEY,
	    ts INTEGER,
	    grp TEXT,
	    nick TEXT,
	    jid TEXT,
	    res TEXT,
	    role TEXT,
	    aff TEXT,
	    status_h TEXT,
	    caps_h TEXT,
	    avatar_h TEXT,
	    photo_h TEXT,
	    cli_id INTEGER,
	    ver_id INTEGER,
	    os_id INTEGER,
	    tz_id INTEGER,
	    bmod_ver TEXT,
	    pres_lang TEXT,
	    pres_id TEXT,
	    pres_h TEXT,
	    prty INTEGER,
	    unavail INTEGER,
	    part_vis_h TEXT,
	    full_vis_h TEXT
	);
    }
    db eval {
	CREATE TABLE alias(
	    id INTEGER,
	    alias TEXT,
	    userids TEXT,
	    comment TEXT
	);
    }
}

db eval {
    CREATE UNIQUE INDEX counter ON visitors (id);
}

set data [read $fd]
close $fd
set lines [split $data \n]
set counter 1
set old_perc 0
set ll [llength $lines]
puts "0% complete"
foreach line $lines {
    if {$line == "" || [string match *Наблюдаемый* $line] \
    || [string match *Watched* $line]} {
    } else {
	set e [split $line \t]
	if {[string is integer [lindex $e 0]]} {
	    set ts [convert_ts [lindex $e 1]]
	    set group [lindex $e 2]
	    set nick [lindex $e 3]
	    set rjid [split [lindex $e 4] /]
	    set jid [lindex $rjid 0]
	    if {[llength $rjid] > 1} {
		set resource [lindex $rjid 1]
	    } else {
		set resource ""
	    }
	    if {[llength $e] >= 6} {
		set r_aff [split [lindex $e 5] /]
		set role [lindex $r_aff 0]
		set aff [lindex $r_aff 1]
	    } else {
		set role ""
		set aff ""
	    }
	    if {[llength $e] >= 7} {
		set v_os [lindex $e 6]
		set vos [regexp -inline \
		{(Клиент|Client):\ (.+\ )(Версия|Version):\ (.+\ )(ОС|OS):\ (.+)} $v_os]
		set cli [string trim [lindex $vos 2]]
		set ver [string trim [lindex $vos 4]]
		set os [string trim [lindex $vos 6]]
	    } else {
		set cli ""
		set ver ""
		set os ""
	    }
	    if {[llength $e] == 8} {
		set caps_h [lindex $e 7]
	    } else {
		set caps_h ""
	    }
	    db eval {
		INSERT INTO visitors
		VALUES(NULL, $ts, $group, $nick, $jid, \
		$resource, $role, $aff, NULL, $caps_h, NULL, \
		NULL, NULL, NULL, NULL, NULL, NULL, NULL, \
		NULL, NULL, NULL, NULL, NULL, NULL)
	    }
	    set id [db eval {SELECT last_insert_rowid();}]
	    if {$cli != ""} {
		set cli_id [db eval {SELECT id FROM client WHERE cli=$cli}]
		if {[string equal $cli_id ""]} {
		    db eval {
			INSERT INTO client
			VALUES(NULL, $cli)
		    }
		    set cli_id [db eval {SELECT last_insert_rowid();}]
		}
		db eval {
		    UPDATE visitors
		    SET cli_id=$cli_id
		    WHERE id = $id
		}
	    }
	    if {$ver != ""} {
		set ver_id [db eval {SELECT id FROM version WHERE ver=$ver}]
		if {[string equal $ver_id ""]} {
		    db eval {
			INSERT INTO version
			VALUES(NULL, $ver)
		    }
		    set ver_id [db eval {SELECT last_insert_rowid();}]
		}
		db eval {
		    UPDATE visitors
		    SET ver_id=$ver_id
		    WHERE id = $id
		}
	    }
	    if {$os != ""} {
		set os_id [db eval {SELECT id FROM osys WHERE os=$os}]
		if {[string equal $os_id ""]} {
		    db eval {
			INSERT INTO osys
			VALUES(NULL, $os)
		    }
		    set os_id [db eval {SELECT last_insert_rowid();}]
		}
		db eval {
		    UPDATE visitors
		    SET os_id=$os_id
		    WHERE id = $id
		}
	    }
	}
    }
	set new_perc [expr ($counter * 100) / $ll]
	if {$new_perc > $old_perc} {
	    puts "$new_perc% complete"
	    set old_perc $new_perc
	}
    incr counter
}
puts [clock format [clock seconds] -format "%a %b %e %H:%M:%S"]


# vim:ts=8:sw=4:sts=4:noet
