require "sqlite3"

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

          FROM sqlite_master

         WHERE type = 'table'
           AND name = ?
      ",

      pragma_foreign_keys: "
        PRAGMA foreign_keys = true
      ",
    }

    def initialize(path)
      super(path)
      query(SQL[:pragma_foreign_keys])
    end

    def initialize(path, &block)
      super(path)
      query(SQL[:pragma_foreign_keys])

      begin
        block.call(self)
      ensure
        close unless closed?
      end
    end

    def table_exists?(table : String) : Bool
      one(SQL[:table_exists], [table]) == table
    end

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

      run(sql, args) do |rs|
        if rs.next
          r = rs[0].to_s
        end
      end

      # return result
      r
    end

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

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

      # return result
      r
    end

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

      nil
    end

    def query(
      sql     : String
    )
      run(sql, nil) do |rs|
        # make sure query executes
        rs.next
        nil
      end
    end

    #
    # NOTE: if you pass a block, be sure to call rs.next at least once,
    # or the query will _not_ execute!!!
    #
    def query(
      sql         : String,
      args = nil  : Array(String) | Hash(String, String) | Nil,
    )
      run(sql, args) do |rs|
        # make sure query executes
        rs.next
        nil
      end
    end

    #
    # NOTE: if you pass a block, be sure to call rs.next at least once,
    # or the query will _not_ execute!!!
    #
    def query(
      sql         : String,
      args = nil  : Array(String) | Hash(String, String) | Nil,
      &block      : Proc(::SQLite3::ResultSet, Nil) \
    )
      run(sql, args, &block)
    end

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

    private def run(
      sql     : String,
      args    : Hash(String, String),
      &block  : Proc(::SQLite3::ResultSet, Nil) \
    )
      run(sql, [args], &block)
    end

    private def run(
      sql         : String,
      args = nil  : Array(String | Hash(String, String))?,
      &block      : Proc(::SQLite3::ResultSet, Nil) \
    )
      # build statement
      puts "sql = %s" % [sql]
      st = prepare(sql)

      # exec and close statement
      if args && args.size > 0
        st.execute(args, &block)
      else
        st.execute(&block)
      end

      # return result
      nil
    end

    private def to_row(rs)
      Hash(String, ::SQLite3::Value).zip(rs.columns, rs.to_a)
    end
  end
end