module MysqlDoubleIndex

# File lib/mysql_double_index.rb, line 123
def byte2mb(byte)
  byte = byte.to_f
  if byte < 1024
    return "#{byte}Byte"
  elsif byte/1024 < 1024
    return "#{(byte/1024).round(2)}KB"
  elsif byte/(1024**2) < 1024
    return "#{(byte/(1024**2)).round(2)}MB"
  elsif byte/(1024**3) < 1024
    return "#{(byte/(1024**3)).round(2)}GB"
  elsif byte/(1024**4) < 1024
    return "#{(byte/(1024**4)).round(2)}TB"
  end
end

# File lib/mysql_double_index/db_conn.rb, line 12
def db_close
  if ActiveRecord::Base.connection && ActiveRecord::Base.connection.active?
    ActiveRecord::Base.connection.close
  end
end

# File lib/mysql_double_index/db_conn.rb, line 8
def db_connection
  dbconfig = YAML::load(MysqlDoubleIndex.load_config)
  ActiveRecord::Base.establish_connection(dbconfig[Rails::env.to_s])
end

# File lib/mysql_double_index.rb, line 13
def db_double_index(table = nil)
  begin
    MysqlDoubleIndex.db_connection #连接数据库
    result = {}
    double_index = []
    redundancy_index = []
    if table.nil?
      sql="show tables"
      tables=ActiveRecord::Base.connection.execute(sql)
    else
      tables = []
      tables << [table]
    end
    tables.each do |item|
      tmp_hash = {}
      keys = ActiveRecord::Base.connection.execute("show index from #{item[0]}")
      keys.each do |item|
        tmp_hash["#{item[2]}"] = {} if tmp_hash["#{item[2]}"].nil?
        item[4] += "(#{item[7]})" if !item[7].nil?
        if tmp_hash["#{item[2]}"]['columns'].nil?
          tmp_hash["#{item[2]}"]['columns'] = [item[4]]
        else
          tmp_hash["#{item[2]}"]['columns'] << item[4]
        end
        tmp_hash["#{item[2]}"]['index_type'] = item[10]
      end
      result.merge!({item[0] => tmp_hash})
    end
    result.each do |table,indexs|
      handled_index = []
      indexs.each do |index_name_outer,index_columns_outer|
        indexs.each do |index_name_inner,index_columns_inner|
          next if index_name_inner == index_name_outer || index_columns_inner['index_type'] != index_columns_outer['index_type'] || handled_index.include?(index_name_inner.to_s + index_name_outer.to_s) || handled_index.include?(index_name_outer.to_s + index_name_inner.to_s)
          #重复索引
          if get_index_columns_sorted(index_columns_inner['columns']) == get_index_columns_sorted(index_columns_outer['columns'])
            double_index << "#{table}上存在重复的索引:【#{index_name_outer.to_s + get_index_columns_sorted(index_columns_outer['columns'],true)}】&【#{index_name_inner.to_s + get_index_columns_sorted(index_columns_inner['columns'],true)}】"
          elsif has_redundancy_index?(index_columns_inner['columns'],index_columns_outer['columns'])
            #冗余索引
            redundancy_index << "#{table}上存在冗余的索引:【#{index_name_outer.to_s + get_index_columns_sorted(index_columns_outer['columns'],true)}】&【#{index_name_inner.to_s + get_index_columns_sorted(index_columns_inner['columns'],true)}】"
          end
          handled_index << index_name_inner.to_s + index_name_outer.to_s
        end
      end
      handled_index = []
    end
    print_arr = []
    double_index.each do |item|
      print_arr << [item]
    end
    table = Terminal::Table.new :title => "重复索引", :rows => print_arr
    puts table if double_index.size > 0
    if double_index.size < 1
      table = Terminal::Table.new :title => "暂无检索到重复索引"
      puts table
    end

    print_arr = []
    redundancy_index.each do |item|
      print_arr << [item]
    end
    table = Terminal::Table.new :title => "冗余索引", :rows => print_arr
    puts table if redundancy_index.size > 0
    if redundancy_index.size < 1
      table = Terminal::Table.new :title => "暂无检索到冗余索引"
      puts table
    end
  rescue Exception => e
    puts e.backtrace
  ensure
    MysqlDoubleIndex.db_close #释放链接
  end
end

# File lib/mysql_double_index.rb, line 86
def db_table_size(table = nil)
  begin
    MysqlDoubleIndex.db_connection #连接数据库
    print_arr = []
    sql = "select database()"
    database = ActiveRecord::Base.connection.execute(sql).first[0]
    sql="use information_schema"
    ActiveRecord::Base.connection.execute(sql)
    if table.nil?
      sql = "select TABLE_NAME,ENGINE,TABLE_ROWS,AVG_ROW_LENGTH,DATA_LENGTH,INDEX_LENGTH,(DATA_LENGTH+INDEX_LENGTH) as TABLE_LENGTH,CREATE_TIME from information_schema.tables where table_schema = '#{database}'"
    else
      sql = "select TABLE_NAME,ENGINE,TABLE_ROWS,AVG_ROW_LENGTH,DATA_LENGTH,INDEX_LENGTH,(DATA_LENGTH+INDEX_LENGTH) as TABLE_LENGTH,CREATE_TIME from information_schema.tables where table_schema = '#{database}' and table_name = '#{table}'"
    end
    tables = ActiveRecord::Base.connection.execute(sql)
    head = ["table name","engine","table rows","average row length","data length","index length","sum lemgth","create time"]
    tables.each do |item|
      item.each_with_index do |data,index|
        if [4,5,6].include?(index)
          item[index] = byte2mb(data)
        end
        if 7 == index
          item[index] = item[index].to_s[0...20]
        end
      end
      print_arr << item
    end
    table = Terminal::Table.new :title => "表占用磁盘详情", :headings => head, :rows => print_arr
    puts table
  rescue Exception => e
  ensure
    sql="use #{database}"
    ActiveRecord::Base.connection.execute(sql)
    MysqlDoubleIndex.db_close #释放链接
  end
end

# File lib/mysql_double_index.rb, line 151
def get_index_columns_join(columns)
  columns.map do |item|
    if item.include?('(')
      item[0...item.index('(')]
    else
      item
    end
   end.join(',')
end

# File lib/mysql_double_index.rb, line 138
def get_index_columns_sorted(columns, sub_part = false)
  if sub_part
    '(' + columns.join(',') + ')'
  else
    '(' + columns.map do |item|
      if item.include?('(')
        item[0...item.index('(')]
      else
        item
      end
     end.join(',') + ')'
  end
end

# File lib/mysql_double_index.rb, line 160
def has_redundancy_index?(columns1, columns2)
  columns1 = columns1.map do |item|
    if item.include?('(')
      item[0...item.index('(')]
    else
      item
    end
  end
  columns2 = columns2.map do |item|
    if item.include?('(')
      item[0...item.index('(')]
    else
      item
    end
  end
  if columns1.size > columns2.size
    columns1,columns2 = columns2,columns1
  end
  columns1.each_with_index do |item,index|
    return false if item != columns2[index]
  end
  return true
end

# File lib/mysql_double_index/db_conn.rb, line 3
def load_config
  config_path = Rails.root ? File.join(Rails.root, "config", "database.yml") : './config/database.yml'
  File.open(config_path)
end

# File lib/mysql_double_index.rb, line 10
def test
end