class RecordxSqlite

# File lib/recordx_sqlite.rb, line 12
def initialize(dbfile, table: nil, primary_key: :id, pk: primary_key, 
               sql: nil, pagesize: 10, debug: false)

  @debug = debug
  sqlite = dbfile =~ /^sqlite:\/\// ? DRbSQLite : SQLite3::Database    
  @db = sqlite.new dbfile

  @db.results_as_hash = true
  
  if table.is_a? String then
    
    @table, @primary_key = table, pk.to_sym
    
  elsif table.is_a? Hash 
    
    h = table
    @table = h.keys.first
    @primary_key = h[@table].keys.first
    
    create_table(@table, h[@table]) if @db.table_info(@table).empty?
    
  else
    
    if @db.respond_to? :tables then
      @table = @db.tables.first
      @primary_key = @db.fields(@table).first
    else
      
      sql = "SELECT name FROM sqlite_master WHERE type='table';"
      r = @db.execute(sql)
      puts 'r: ' + r.inspect if @debug
      
      a = r.map {|x| x['name']}
      puts 'a: ' + a.inspect if @debug
      
      sys = a.grep /^sqlite_/
      @table = (a - sys).first
      puts '@table: ' + @table.inspect if @debug
      
      @primary_key = @db.table_info(@table).map {|x| x['name'].to_sym }.first
      puts '@primary_key: ' + @primary_key.inspect if @debug
      
    end
          
  end
  
  @sql =  sql = 'select * from ' + @table.to_s
  @default_sql = @sql
  @pagesize = pagesize
  @a = nil
  
  # create the fhe find_by methods for each fields
  @db.table_info(@table).each do |cols|
    #puts 'cols: ' + cols.inspect
    define_singleton_method ('find_by_' + cols['name']).to_sym do |val|
      find_by(cols['name'], val)
    end
    
    define_singleton_method ('find_all_by_' + cols['name']).to_sym do |val|
      find_all_by(cols['name'], val)
    end      
  end
  
end

# File lib/recordx_sqlite.rb, line 80
def all()    
  @sql = @default_sql
  query(@sql) unless @a
  @a
end

# File lib/recordx_sqlite.rb, line 86
def create(h={})
  
  fields = h.keys
  values = h.values

  sql = "INSERT INTO #{@table} (#{fields.join(', ')})
  VALUES (#{(['?'] * fields.length).join(', ')})"

  @db.execute(sql, values)
  
  :create
end

# File lib/recordx_sqlite.rb, line 99
def delete(id)
  
  sql = "DELETE FROM #{@table} WHERE #{@primary_key}='#{id}'"
  @db.execute sql
  
  :delete
end

# File lib/recordx_sqlite.rb, line 107
def find(id)

  query(@sql) unless @a
  @a.find {|x| x.method(@primary_key).call == id}
  
end

# File lib/recordx_sqlite.rb, line 121
def find_all_by(field, val)

  query(@sql) unless @a
  @a.select {|x| x.method(field).call.downcase == val.downcase}
  
end

# File lib/recordx_sqlite.rb, line 114
def find_by(field, val)

  query(@sql) unless @a
  @a.find {|x| x.method(field).call == val}
  
end

# File lib/recordx_sqlite.rb, line 130
def first(n=1)
 query(@sql + ' LIMIT ' + n.to_s, cache: false)
end

# File lib/recordx_sqlite.rb, line 134
def order(dir=:asc)
  @sql += " ORDER BY #{@primary_key} #{dir.to_s.upcase}"
  self
end

# File lib/recordx_sqlite.rb, line 139
def page(n=1)
  
  query(@sql + " ORDER BY %s DESC LIMIT %s OFFSET %s" % 
        [@primary_key, @pagesize, @pagesize*(n-1)], cache: false)

end

# File lib/recordx_sqlite.rb, line 146
def query(sql=@sql, cache: true, heading: true)
      
  if heading then
    
    rs = @db.query sql
    
  else
    
    @db.results_as_hash = false
    rs = @db.query(sql).to_a
    @db.results_as_hash = true
    
    return rs 
  end
  
  a = rs.map do |h| 
    h2 = h.inject({}) {|r, x| k, v = x; r.merge(k.to_sym => v)}
    RecordX.new(h2, self, h2[@primary_key]) 
  end    
  
  @a = a if cache
  
  return a
  
end

# File lib/recordx_sqlite.rb, line 172
def refresh()
  query(@sql)
  'refreshed'
end

# File lib/recordx_sqlite.rb, line 177
  def update(id, h={})

    col, value = h.to_a.first
    return if col == @primary_key

s = "
UPDATE #{@table}
SET #{col}='#{value}'
WHERE #{@primary_key.to_s}='#{id}';"

    @db.execute(s)

  end

# File lib/recordx_sqlite.rb, line 193
def create_table(name, cols)
  
  fields = cols.map do |k,v|

    types = { string: :text, integer: :int, float: :real,   date: :date }
    type = types[v.class.to_s.downcase.to_sym].to_s.upcase
    "%s %s" % [k.to_s, type]

  end

  sql = "CREATE TABLE %s (\n  %s PRIMARY KEY NOT NULL,\n  %s\n);" % 
    [name, fields.first, fields[1..-1].join(",\n  ")]

  @db.execute sql
  
end