require 'dbiu'
require 'singleton'
require 'div/div'
require 'parsedate'

class SaifuLoginDB
  include Singleton

  def self.pool=(pool)
    @@pool = pool
  end

  def regist(user, username, phrase)
    @@pool.transaction do |conn|
      sql = "insert into pw (userid, username, phrase) values (?,?,?)"
      conn.do(sql, user, username, phrase)
    end
  end

  def username(user)
    @@pool.transaction do |conn|
      name ,= conn.select_one("select username from pw where userid=?", user)
      return name
    end
  end
  
  def get_user(user, pw)
    @@pool.transaction do |conn|
      sql = "select username, phrase from pw where userid=?"
      username, phrase = conn.select_one(sql, user)
      return nil unless pw == phrase
      return {:id => user, :name => username }
    end
  end
  
  def change_passphrase(user, oldphrase, newphrase)
    @@pool.transaction do |conn|
      check_sign(user, oldphrase)
      sql = "update pw set phrase=? where userid=?"
      conn.do(sql, newphrase, user)
    end
  end
end

class SaifuDB
  include Singleton
  
  Item = Struct.new(:key, :name, :date, :value, :memo)

  def self.pool=(pool)
    @@pool = pool
  end

  def add(name, date, value, memo)
    @@pool.transaction do |conn|
      conn.do("update seq set value=value+1 where name='history'")
      key = conn.select_one("select value from seq where name='history'")
      sql = <<EOQ
insert into history (key, name, date, value, memo) values (?,?,?,?,?)
EOQ
      conn.do(sql, key, name, date, value, memo)
    end
  end

  def update(key, name, date, value, memo)
    @@pool.transaction do |conn|
      sql = "update history set date=?, value=?, memo=? where key=? and name=?"
      conn.do(sql, date, value, memo, key, name)
    end
  end

  def make_empty(name)
    @@pool.transaction do |conn|
      conn.do("delete from history where name=?", name)
    end
  end
  
  def delete(key, name)
    @@pool.transaction do |conn|
      conn.do("delete from history where key=? and name=?", key, name)
    end
  end

  def retrieve(name, limit=20, start=0)
    @@pool.transaction do |conn|
      sql = "select count(*), sum(value) from history where name=?"
      count, total = conn.select_one(sql, name)

      sql = "select key, name, date, value, memo from history where name=? order by date desc limit ?, ?"
      conn.select_all(sql, name, limit, start) do |r|
	yield(Item.new(*r))
      end
      return count, total
    end
  end

  def retrieve_all(name)
    @@pool.transaction do |conn|
      sql = "select count(*), sum(value)  from history where name=?"
      count, total = conn.select_one(sql, name)

      sql = <<EOQ
select key, name, date, value, memo from history where name=? 
  order by date desc
EOQ
      conn.select_all(sql, name) do |r|
	yield(Item.new(*r))
      end
      return count, total      
    end
  end
end

class SaifuDiv < Div::Div
  set_erb('saifu.erb')

  Args = Struct.new(:key, :value, :memo, :date)

  def initialize(session)
    super(session)
    @saifu = SaifuDB.instance
    @edit = nil
    @div_seq = self.id.to_i
  end

  def user
    return nil unless @session.login.login?
    # return nil if @session.login.guest?
    @session.login.user[:id]
  end

  def to_args(param)
    key ,= param['key']
    value ,= param['value']
    memo ,= param['memo']
    date ,= param['date']

    key = key.to_i if key && key.size > 0
    value = value.to_i if value && value.size > 0
    begin
      time = Time.mktime(*(ParseDate.parsedate(date)[0,3]))
      date = time.strftime("%Y-%m-%d")
    rescue
      date =nil
    end
    memo = NKF.nkf('-e', memo) if memo
    Args.new(key, value, memo, date)
  end

  def do_add(context, param)
    args = to_args(param)
    return unless args.value && args.date && args.memo
    @saifu.add(user, args.date, args.value, args.memo)
  rescue
  end
  
  def do_delete(context, param)
    args = to_args(param)
    @saifu.delete(args.key, user)
  rescue
  end

  def do_update(context, param)
    args = to_args(param)
    return unless args.key && args.value && args.date && args.memo
    @saifu.update(args.key, user, args.date, args.value, args.memo)
  rescue
  end

  def do_detail(context, param)
    args = to_args(param)
    @edit = args.key
  end
end


if __FILE__ == $0
  pool = DBIPool.new(5, 'DBI:Pg:saifu', 'seki', 'seki00')
  SaifuDB.pool = pool
  s = SaifuDB.instance
  s.add('seki', '2002-04-03', 2000, 'bento')
  s.add('seki', '2002-04-04', 2000, 'bento')
  s.update(4, 'seki', '2002-04-11', 2000, 'bento')
  p s.retrieve_all('seki') {|it| p it}
end