#!/usr/bin/env ruby

# Use this script to search for a string in all columns and tables in MYSQL
# INSTALLATION
#   gem install mysql2
#   gem install awesome_print
#   gem install colorize
# USAGE
#   ./dbsearch.rb 'find me in db'

require 'mysql2'
require 'awesome_print'
require 'colorize'

# Set your general variables here
exclude_tables = %w(_pantheon_heartbeat)
db_name     = 'database_name'
db_host     = 'database_host'
db_username = 'database_username'
db_password = 'database_password'

search = ARGV[0]

abort("ERROR: MISSING SEARCH ARG    ---    dbsearch \"[search text]\"") if search.nil?

client = Mysql2::Client.new(host: db_host, username: db_username, password: db_password, database: db_name)

table_data = []
tables = client.query("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='#{db_name}'")
tables.each do |table|
  next if exclude_tables.include? table['TABLE_NAME']

  table_indexes = client.query("SHOW INDEX FROM #{table['TABLE_NAME']} FROM #{db_name}")
  table_index = table_indexes.first
  table_index_column_name = table_index['Column_name'] if table_index

  column_data = []
  columns = client.query("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='#{db_name}' AND TABLE_NAME='#{table['TABLE_NAME']}'")

  columns.each do |column|
    column_data << column['COLUMN_NAME']
  end
  table_data << {
    name: table['TABLE_NAME'],
    columns: column_data,
    index: table_index_column_name,
  }
end

table_data.each do |table|
  column_query = table[:columns].collect do |column_name|
    "#{column_name} LIKE '%#{search}%'"
  end
  found_in_table = client.query("SELECT * FROM #{table[:name]} WHERE #{column_query.join(' OR ')}")
  next if found_in_table.size == 0
  puts '====================================================================================='
  puts "FOUND IN TABLE: #{table[:name]}".red

  table[:columns].each do |column_name|
    find_in_column = client.query("SELECT * FROM #{table[:name]} WHERE #{column_name} LIKE '%#{search}%'")
    next if find_in_column.size == 0
    puts "    #{find_in_column.size} TIME(S) IN COLUMN: #{column_name}"
    puts "        SELECT * FROM #{table[:name]} WHERE #{column_name} LIKE '%#{search}%'".blue
  end
end