##########################################################################
#
# QuoteDB v1.0 by ccr/TNSP <ccr@tnsp.org>
# (C) Copyright 2003-2011 Tecnic Software productions (TNSP)
#
# Not for public use or distribution. If you happen to find this,
# send your questions and/or problems to /dev/null, thank you.
#
##########################################################################

###
### General options
###
# Path and filename of the SQLite database
set qdb_file "quotedb.sqlite"

# 1 = Verbose: Say messages PUBLIC when SPEDE is OK, bad, etc.
# 0 = Quiet  : Say privately
set qdb_verbose 0

# 1 = Put some info to bot's Logfile when doing stuff...
# 0 = Don't.
set qdb_logmsg 1

# What IRC "command" should we use to send messages:
# (Valid alternatives are "PRIVMSG" and "NOTICE")
set qdb_preferredmsg "PRIVMSG"


# Foo.
set qdb_max_deltime 15

###
### Search related settings
###

# How many SPEDE's should the !spedefind command show (maximum limit)
set qdb_showmax_pub 3

# For private-search, this is the default limit (user can change it)
set qdb_showmax_priv 5


##########################################################################
# No need to look below this line
##########################################################################
#-------------------------------------------------------------------------
set qdb_name "QuoteDB"
set qdb_version "1.0"


#-------------------------------------------------------------------------
### Binding initializations
bind pub - !spedefind spede_pub_find
bind pub - !spedeadd spede_pub_add
bind pub - !spededel spede_pub_del
bind pub - !spede spede_pub_get
bind msg - spedefind spede_msg_find
bind msg - spede spede_msg_get

proc spede_pub_get  {unick uhost uhand uchan utext} { qdb_msg 0 $uchan "" [qdb_get "spededb" $unick $uhand $utext] }
proc spede_msg_get  {unick uhost uhand utext}       { qdb_msg 0 $unick "" [qdb_get "spededb" $unick $uhand $utext] }
proc spede_pub_add  {unick uhost uhand uchan utext} { qdb_add "spededb" $utext $uhand $unick $uhost $uchan 1 }
proc spede_pub_del  {unick uhost uhand uchan utext} { qdb_del "spededb" $utext $uhand $unick $uchan 1 }
proc spede_pub_find {unick uhost uhand uchan utext} { qdb_find "spededb" $unick $uhand $uchan $utext 1 }
proc spede_msg_find {unick uhost uhand utext}       { qdb_find "spededb" $unick $uhand "" $utext 0 }

bind pub - !mnfind mn_pub_find
bind pub - !mnadd mn_pub_add
bind pub - !mndel mn_pub_del
bind pub - !mn mn_pub_get
bind msg - mnfind mn_msg_find
bind msg - mn mn_msg_get

proc mn_pub_get  {unick uhost uhand uchan utext} { qdb_msg 0 $uchan "" [qdb_get "mndb" $unick $uhand $utext] }
proc mn_msg_get  {unick uhost uhand utext}       { qdb_msg 0 $unick "" [qdb_get "mndb" $unick $uhand $utext] }
proc mn_pub_add  {unick uhost uhand uchan utext} { qdb_add "mndb" $utext $uhand $unick $uhost $uchan 1 }
proc mn_pub_del  {unick uhost uhand uchan utext} { qdb_del "mndb" $utext $uhand $unick $uchan 1 }
proc mn_pub_find {unick uhost uhand uchan utext} { qdb_find "mndb" $unick $uhand $uchan $utext 1 }
proc mn_msg_find {unick uhost uhand utext}       { qdb_find "mndb" $unick $uhand "" $utext 0 }


bind pub - !tuksufind tuksu_pub_find
bind pub - !tuksuadd tuksu_pub_add
bind pub - !tuksudel tuksu_pub_del
bind pub - !tuksu tuksu_pub_get
bind msg - tuksufind tuksu_msg_find
bind msg - tuksu tuksu_msg_get

proc tuksu_pub_get  {unick uhost uhand uchan utext} { qdb_msg 0 $uchan "" [qdb_get "tuksudb" $unick $uhand $utext] }
proc tuksu_msg_get  {unick uhost uhand utext}       { qdb_msg 0 $unick "" [qdb_get "tuksudb" $unick $uhand $utext] }
proc tuksu_pub_add  {unick uhost uhand uchan utext} { qdb_add "tuksudb" $utext $uhand $unick $uhost $uchan 1 }
proc tuksu_pub_del  {unick uhost uhand uchan utext} { qdb_del "tuksudb" $utext $uhand $unick $uchan 1 }
proc tuksu_pub_find {unick uhost uhand uchan utext} { qdb_find "tuksudb" $unick $uhand $uchan $utext 1 }
proc tuksu_msg_find {unick uhost uhand utext}       { qdb_find "tuksudb" $unick $uhand "" $utext 0 }


#-------------------------------------------------------------------------
### Initialization messages
set qdb_message "$qdb_name v$qdb_version by ccr/TNSP"
putlog "$qdb_message"

### Require packages
package require sqlite3

### SQLite database initialization
if {[catch {sqlite3 quotedb $qdb_file} uerrmsg]} {
  putlog " Could not open SQLite3 database '$qdb_file': $uerrmsg"
  exit 2
}


#-------------------------------------------------------------------------
### Utility functions
proc qdb_log {jarg} {
  global qdb_logmsg qdb_name

  if {$qdb_logmsg != 0} {
    putlog "$qdb_name: $jarg"
  }
}

proc qdb_ctime {utime} {
  if {$utime == "" || $utime == "*"} {
    set utime 0
  }

  return [clock format $utime -format "%d.%m.%Y %H:%M"]
}

proc qdb_msg {apublic anick achan amsg} {
  global qdb_preferredmsg

  if {$apublic == 0} {
    putserv "$qdb_preferredmsg $anick :$amsg" 
  } else {
    putserv "$qdb_preferredmsg $achan :$anick, $amsg"
  }
}

proc qdb_escape {str} {
  return [string map {' ''} $str]
}


#-------------------------------------------------------------------------
proc qdb_add {utable utext uhand unick uhost uchan upublic} {
  global quotedb

  if {"x$utext" == "x" || "$uhand" == "x" || $uhand == {}} {
    qdb_msg $upublic $unick $uchan "pyh."
    return 0
  }

  set usql "INSERT INTO $utable (utime,utext,user,host,chan) VALUES ([unixtime], '[qdb_escape $utext]', '[qdb_escape $uhand]', '[qdb_escape $uhost]', '[qdb_escape $uchan]')"
  if {[catch {quotedb eval $usql} uerrmsg]} {
    qdb_log "$uerrmsg on SQL:\n$sql"
    qdb_msg $upublic $unick $uchan "virhe sörkittäessä tietokantaa. uliskaa."
    return 0
  }

  set quoteID [quotedb last_insert_rowid]
  set numQuotes 0
#  set numQuotes [quotedb ]

  ### Log some data
  qdb_log "Added SPEDE #$quoteID ($unick/$uhand@$uchan): $utext"

  ### Report success to user
  qdb_msg $upublic $unick $uchan "tietokantaa sörkitty (#$quoteID / $numQuotes), kiitos."
  return 1
}


#-------------------------------------------------------------------------
proc qdb_del {utable utext uhand unick uchan upublic} {
  global quotedb qdb_max_deltime

  if {"x$utext" == "x" || "$uhand" == "x" || $uhand == {}} {
    qdb_msg $upublic $unick $uchan "pyh."
    return 0
  }

  set maxdiff [expr $qdb_max_deltime * 60]

  if {[regexp "(\[0-9\]+)" $utext umatch unum]} {
    set usql "SELECT id AS quoteID, utime AS utime, utext AS utext, user AS uuser, host AS uhost FROM $utable WHERE id=$unum AND user='[qdb_escape $uhand]'"
  } else {
    qdb_msg $upublic $unick $uchan "ID # annettava."
  }
  
  quotedb eval $usql {
    set udiff [expr [unixtime] - $utime]
    if {$udiff < $maxdiff} {
      set usql "DELETE FROM $utable WHERE id=$quoteID AND user='[qdb_escape $uhand]'"
      if {[catch {quotedb eval $usql} uerrmsg]} {
        qdb_log "$uerrmsg on SQL:\n$sql"
        qdb_msg $upublic $unick $uchan "virhe sörkittäessä tietokantaa. uliskaa."
      } else {
        qdb_msg $upublic $unick $uchan "$utable ID #$quoteID poistettu."
      }
      return 0
    } else {
      qdb_msg $upublic $unick $uchan "$utable quote #$quoteID vanhempi kuin $qdb_max_deltime minuuttia, ei poisteta."
      return 0
    }
  }

  qdb_msg $upublic $unick $uchan "#$quoteID ei löydy, tai se ei kuulu käyttäjälle '$uhand'."
}


#-------------------------------------------------------------------------
proc qdb_get {utable unick uhand uindex} {
  global quotedb 
  
  set usql "SELECT id AS quoteID, utime AS utime, utext AS utext, user AS uuser, host AS uhost FROM $utable"
  if {[regexp "(\[0-9\]+)" $uindex umatch unum]} {
    set usql "$usql WHERE id=$unum"
    qdb_log "$unick/$uhand searched $utable #$unum"
  } else {
    set usql "$usql ORDER BY RANDOM() LIMIT 1"
    qdb_log "$unick/$uhand get random $utable"
  }

  quotedb eval $usql {
    return "#${quoteID}: $utext ($uuser)"
  }

  return "ei löydy."
}


#-------------------------------------------------------------------------
proc qdb_find {utable unick uhand uchan utext upublic} {
  global quotedb qdb_showmax_pub qdb_showmax_priv

  if {$upublic == 0} {
    set ulimit $qdb_showmax_priv
  } else {
    set ulimit $qdb_showmax_pub
  }

  ### Parse the given command
  qdb_log "$unick/$uhand/$uchan searched $utable: $utext"

  set ftokens [split $utext " "]
  foreach ftoken $ftokens {
    set fprefix [string range $ftoken 0 0]
    set fpattern [string range $ftoken 1 end]

    if {$fprefix == "-"} {
      lappend fpatlist "utext NOT LIKE '%[qdb_escape $fpattern]%'"
    } elseif {$fprefix == "%"} {
      lappend fpatlist "user='[qdb_escape $fpattern]'"
    } elseif {$fprefix == "@"} {
      # foo
    } else {
      lappend fpatlist "utext LIKE '%[qdb_escape $ftoken]%'"
    }
  }

  set uresults 0
  set usql "SELECT id AS quoteID, utime AS utime, utext AS utext, user AS uuser, host AS uhost FROM $utable WHERE [join $fpatlist " AND "] ORDER BY utime DESC LIMIT $ulimit"
  quotedb eval $usql {
    incr uresults
    qdb_msg $upublic $unick $uchan "#${quoteID}: $utext ($uuser@[qdb_ctime $utime])"
  }
  
  if {$uresults == 0} {
    qdb_msg $upublic $unick $uchan "ei löydy."
  }
  return 0
}

#-------------------------------------------------------------------------
# end of script