require "sqlite3"

module Guff
  class Database < ::SQLite3::Database
    SQL = {
      table_exists: "
        SELECT tbl_name

          FROM sqlite_master

         WHERE type = 'table'
           AND tbl_name = ?
      ",
    }

    # TODO (add table_exists?)
    def table_exists?(table_name : String)
      one(SQL[:table_exists], [table_name]) == table_name
    end

    def one(
      sql     : String
    )
      r = nil

      bind(sql).execute do |rs|
        if rs.next
          # FIXME
          r = rs[0]
        end
      end

      # return result
      r
    end

    def one(
      sql     : String,
      args    : Array(String)
    )
      r = nil

      bind(sql, args).execute do |rs|
        if rs.next
          # FIXME
          r = rs[0]
        end
      end

      # return result
      r
    end

    def one(
      sql     : String,
      args    : Hash(String, String),
    )
      r = nil

      bind(sql, args).execute do |rs|
        if rs.next
          # FIXME
          r = rs[0]
        end
      end

      # return result
      r
    end

    def row(sql : String)
      r = nil

      # exec query
      bind(sql).execute do |rs|
        if rs.next
          r = to_row(rs)
        end
      end

      # return result
      r
    end

    def row(
      sql   : String, 
      args  : Array(String)
    )
      r = nil

      # exec query
      bind(sql, args).execute do |rs|
        if rs.next
          r = to_row(rs)
        end
      end

      # return result
      r
    end

    def row(
      sql   : String, 
      args  : Hash(String, String)
    )
      r = nil

      # exec query
      bind(sql, args).execute do |rs|
        if rs.next
          r = to_row(rs)
        end
      end

      # return result
      r
    end

    def all(
      sql     : String,
      args    : Array(String),
      &block  : Proc(Hash(String, ::SQLite3::Value), Nil) \
    )
      # build statement
      bind(sql, args).execute do |rs|
        # walk results
        while rs.next
          # build row and pass it to callback
          block.call(to_row(rs))
        end
      end

      nil
    end

    def all(
      sql     : String,
      args    : Hash(String, String),
      &block  : Proc(Hash(String, ::SQLite3::Value), Nil) \
    )
      bind(sql, args).execute do |rs|
        # walk results
        while rs.next
          # build row and pass it to callback
          block.call(to_row(rs))
        end
      end

      # close statement, return nil
      nil
    end

    def transaction(&block)
      query("BEGIN")
      block.call(self)
      query("COMMIT")
    end

    private def bind(sql : String)
      prepare(sql)
    end

    private def bind(
      sql     : String,
      args    : Array(String)?
    )
      # build statement
      st = prepare(sql)

      # build args
      if args.size > 0
        args.each_with_index do |val, i|
          st[i + 1] = val
        end
      end

      # return result
      st
    end

    private def bind(
      sql     : String,
      args    : Hash(String, String)?
    )
      # build statement
      if args.size > 0
        st = prepare(sql)
      end

      # bind args
      st[0] = args

      # return result
      st
    end

    private def to_row(rs : SQLite3::ResultSet) : Hash(String, ::SQLite3::Value)
      0.upto(rs.column_count).inject(
        {} of String => ::SQLite3::Value
      ) do |r, i|
        r[rs.columns[i]] = r[i]
        r
      end
    end
  end
end