require "rubygems"
require "dbi"
require "/home/ofwd/etc/ofwd-web-interface-conf.rb"
require "digest/md5"

class OfwdDb
  
  def initialize
    @dbh = DBI.connect("DBI:Mysql:" + $dbname + ":" + $dbhost, $dbuser, $dbpass)
    rescue DBI::DatabaseError => e
      puts "An error occurred"
      puts "Error code: #{e.err}"
      puts "Error message: #{e.errstr}"
  end

  def destroy
    # disconnect from server
    @dbh.disconnect if @dbh
  end
  
  def verify_username(username)
    valid_user = false
    sth = @dbh.execute("SELECT username FROM user WHERE username = ?", username)
    rows = sth.fetch_all
    sth.finish
    if rows.size == 1 then
      valid_user = true
    end
    valid_user
    rescue DBI::DatabaseError => e
      puts "An error occurred"
      puts "Error code: #{e.err}"
      puts "Error message: #{e.errstr}"
  end
  
  def check_username_availability(username)
    not self.verify_username(username)
  end
  
  def get_email(username)
    sth = @dbh.execute("SELECT email FROM user WHERE username = ? LIMIT 1", username)
    row = sth.fetch
    sth.finish
    email = row[0]
    rescue DBI::DatabaseError => e
      puts "An error occurred"
      puts "Error code: #{e.err}"
      puts "Error message: #{e.errstr}"
  end
  
  def change_password(username, password)
    sth = @dbh.prepare("UPDATE user SET password=md5(?) WHERE username = ?")
    sth.execute(password, username)
    sth.finish
    @dbh.commit
    rescue DBI::DatabaseError => e
      puts "An error occurred"
      puts "Error code: #{e.err}"
      puts "Error message: #{e.errstr}"
  end
  
  def authenticate_user(username, password)
    authentication_successful = false
    md5password=Digest::MD5.hexdigest(password)
    sth = @dbh.execute("SELECT username FROM user WHERE username = ? AND password = ?", username, md5password)
    rows = sth.fetch_all
    sth.finish
    if rows.size == 1 then
      valid_user = true
    end
    valid_user
    rescue DBI::DatabaseError => e
      puts "An error occurred"
      puts "Error code: #{e.err}"
      puts "Error message: #{e.errstr}"
  end
  
  def create_account(username, email, password, verify_key)
    sth = @dbh.prepare("INSERT INTO user (username, password, email, active, verify) VALUES (?, md5(?), ?, 0, ?)")
    sth.execute(username, password, email, verify_key)
    sth.finish
    @dbh.commit
    rescue DBI::DatabaseError => e
      puts "An error occurred"
      puts "Error code: #{e.err}"
      puts "Error message: #{e.errstr}"
  end
  
  def verify_account(verify_key)
    account_verify = false
    rows = @dbh.do("UPDATE user SET active=1, verify=NULL WHERE verify = ?", verify_key)
    if rows >= 1 then
      account_verify = true
    end
    account_verify
    rescue DBI::DatabaseError => e
      puts "An error occurred"
      puts "Error code: #{e.err}"
      puts "Error message: #{e.errstr}"
  end
  
  def get_user_list
    user_list = Array.new
    sth = @dbh.execute("SELECT username FROM user")
    rows = sth.fetch_all
    sth.finish
    rows.each do |row|
      user_list.push(row[0])
    end
    user_list
    rescue DBI::DatabaseError => e
      puts "An error occurred"
      puts "Error code: #{e.err}"
      puts "Error message: #{e.errstr}"
  end
  
end