# FIXME: we should patch the connection adapters instead of having 'case, when' evaluated each time
# For example:
# module ActiveRecord
#   module ConnectionAdapters
#     class MysqlAdapter
#       include Zena::Db::MysqlAdditions
#     end
#   end
# end


module Zena
  module Db
    extend self

    # constants
    case ActiveRecord::Base.configurations[RAILS_ENV]['adapter']
    when 'mysql'
      NOW = 'now()'
    when 'sqlite3'
      NOW = "datetime('now')"
    end

    def set_attribute(obj, key, value)
      obj.send("#{key}=", value)
      execute "UPDATE #{obj.class.table_name} SET #{key}=#{quote(value)} WHERE id=#{obj[:id]}"
    end

    def quote(value)
      connection.quote(value)
    end

    def adapter
      ActiveRecord::Base.configurations[RAILS_ENV]['adapter']
    end

    def execute(*args)
      ActiveRecord::Base.connection.execute(*args)
    end

    def update(*args)
      ActiveRecord::Base.connection.update(*args)
    end

    def connection
      ActiveRecord::Base.connection
    end

    def table_options
      case adapter
      when 'mysql'
        'type=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci'
      else
        ''
      end
    end

    def update_value(name, opts)
      tbl1, fld1 = name.split('.')
      tbl2, fld2 = opts[:from].split('.')
      case adapter
      when 'mysql', 'postgresql'
        execute "UPDATE #{tbl1},#{tbl2} SET #{tbl1}.#{fld1}=#{tbl2}.#{fld2} WHERE #{opts[:where]}"
      when 'sqlite3'
        execute "UPDATE #{tbl1} SET #{fld1} = (SELECT #{fld2} FROM #{tbl2} WHERE #{opts[:where]})"
      else
        raise Exception.new("Database Adapter #{adapter.inspect} not supported yet (you can probably fix this).")
      end
    end

    def change_engine(table, engine)
      case adapter
      when 'mysql'
        execute "ALTER TABLE #{table} ENGINE = #{engine}"
      else
        # do nothing
      end
    end

    def add_unique_key(table, keys)
      case adapter
      when 'mysql', 'postgresql'
        execute "ALTER IGNORE TABLE #{table} ADD UNIQUE KEY(#{keys})"
      when 'sqlite3'
        execute "CREATE UNIQUE INDEX IF NOT EXISTS #{(table + keys).gsub(/[^\w]/,'')} ON #{table} (#{keys})"
      else
        raise Exception.new("Database Adapter #{adapter.inspect} not supported yet (you can probably fix this).")
      end
    end

    # 'DELETE' depending on a two table query.
    def delete(table, opts)
      tbl1, tbl2 = opts[:from]
      fld1, fld2 = opts[:fields]
      case adapter
      when 'mysql', 'postgresql'
        execute "DELETE #{table} FROM #{opts[:from].join(',')} WHERE #{tbl1}.#{fld1} = #{tbl2}.#{fld2} AND #{opts[:where]}"
      when 'sqlite3'
        execute "DELETE FROM #{table} WHERE #{fld1} = (SELECT #{fld2} FROM #{tbl2} WHERE #{opts[:where]})"
      else
        raise Exception.new("Database Adapter #{adapter.inspect} not supported yet (you can probably fix this).")
      end
    end

    # Escape a list of values for multicolumn insert.
    def insert_many(table, columns, values)
      values = values.compact.uniq
      case adapter
      when 'sqlite3'
        pre_query = "INSERT INTO #{table} (#{columns.join(',')}) VALUES "
        values.each do |v|
          execute pre_query + "(#{v.join(',')})"
        end
      else
        values = values.map {|v| "(#{v.join(',')})"}.join(', ')
        execute "INSERT INTO #{table} (#{columns.map{|c| "`#{c}`"}.join(',')}) VALUES #{values}"
      end
    end

    # Fetch a single row of raw data from db
    def fetch_row(sql)
      case adapter
      when 'sqlite3'
        res = execute(sql)
        res.empty? ? nil : res.first[0]
      when 'mysql'
        res = execute(sql).fetch_row
        res ? res[0] : nil
      else
        raise Exception.new("Database Adapter #{adapter.inspect} not supported yet (you can probably fix this).")
      end
    end

    def fetch_ids(sql, attr_name='id')
      connection.select_all(sql, "#{name} Load").map! do |record|
        record[attr_name]
      end
    end

    def fetch_attribute(attribute, sql)
      unless sql =~ /SELECT/i
        sql = "SELECT `#{attribute}` FROM #{table_name} WHERE #{sql}"
      end
      Zena::Db.fetch_row(sql)
    end

    def next_zip(site_id)
      case adapter
      when 'mysql'
        res = update "UPDATE zips SET zip=@zip:=zip+1 WHERE site_id = '#{site_id}'"
        if res == 0
          # error
          raise Zena::BadConfiguration, "no zip entry for (#{site_id})"
        end
        rows = execute "SELECT @zip"
        rows.fetch_row[0].to_i
      when 'sqlite3'
        # FIXME: is there a way to make this thread safe and atomic (like it is with mysql) ?
        update "UPDATE zips SET zip=zip+1 WHERE site_id = '#{site_id}'"
        fetch_row("SELECT zip FROM zips WHERE site_id = '#{site_id}'").to_i
      else
        raise Exception.new("Database Adapter #{adapter.inspect} not supported yet (you can probably fix this).")
      end
    end

    # Return a string matching the pseudo sql function.
    def sql_function(function, key)
      return key unless function
      res = case adapter
      when 'mysql'
        case function
        when 'year'
          "year(#{key})"
        end
      when 'sqlite3'
        case function
        when 'year'
          "strftime('%Y', #{key})*1"
        end
      end
      raise Exception.new("Database Adapter #{adapter.inspect} does not support function #{function.inspect}.") unless res
      res
    end

    # This is used by zafu and it's a mess.
    # ref_date can be a string ('2005-05-03') or ruby ('Time.now'). It should not come uncleaned from evil web.
    def date_condition(date_cond, field, ref_date)
      case adapter
      when 'mysql'
        case date_cond
        when 'today', 'current', 'same'
          "DATE(#{field}) = DATE(#{ref_date})"
        when 'week'
          "date_format(#{ref_date},'%Y-%v') = date_format(#{field}, '%Y-%v')"
        when 'month'
          "date_format(#{ref_date},'%Y-%m') = date_format(#{field}, '%Y-%m')"
        when 'year'
          "date_format(#{ref_date},'%Y') = date_format(#{field}, '%Y')"
        when 'upcoming'
          "#{field} >= #{ref_date}"
        else
          # '2008-01-31 23:50' + INTERVAL 1 hour
          if date_cond =~ /^(\+|-|)\s*(\d+)\s*(second|minute|hour|day|week|month|year)/
            count = $2.to_i
            if $1 == ''
              # +/-
              "#{field} > #{ref_date} - INTERVAL #{count} #{$3.upcase} AND #{field} < #{ref_date} + INTERVAL #{count} #{$3.upcase}"
            elsif $1 == '+'
              # x upcoming days
              "#{field} > #{ref_date} AND #{field} < #{ref_date} + INTERVAL #{count} #{$3.upcase}"
            else
              # x days in the past
              "#{field} < #{ref_date} AND #{field} > #{ref_date} - INTERVAL #{count} #{$3.upcase}"
            end
          end
        end
      when 'sqlite3'
        case date_cond
        when 'today', 'current', 'same'
          "DATE(#{field}) = DATE(#{ref_date})"
        when 'week'
          "strftime('%Y-%W', #{ref_date}) = strftime('%Y-%W', #{field})"
        when 'month'
          "strftime('%Y-%m', #{ref_date}) = strftime('%Y-%m', #{field})"
        when 'year'
          # we multiply by '1' to force a cast to INTEGER so that comparaison against
          # numbers works.
          "strftime('%Y', #{ref_date}) = strftime('%Y', #{field})"
        when 'upcoming'
          "#{field} >= #{ref_date}"
        else
          # date('2008-01-31 23:50','+1 hour')
          if date_cond =~ /^(\+|-|)\s*(\d+)\s*(second|minute|hour|day|week|month|year)/
            count = $2.to_i
            if $1 == ''
              # +/-
              "#{field} > DATE(#{ref_date}, '-#{count} #{$3.upcase}') AND #{field} < DATE(#{ref_date}, '+#{count} #{$3.upcase}')"
            elsif $1 == '+'
              # x upcoming days
              "#{field} > #{ref_date} AND #{field} < DATE(#{ref_date}, '+#{count} #{$3.upcase}')"
            else
              # x days in the past
              "#{field} < #{ref_date} AND #{field} > DATE(#{ref_date}, '-#{count} #{$3.upcase}')"
            end
          end
        end
      else
        raise Exception.new("Database Adapter #{adapter.inspect} not supported yet (you can probably fix this).")
      end
    end
  end # Db
end # Zena