#!/usr/bin/env ruby

require 'rubygems'
require 'open-uri'
require 'active_record'
require 'optparse'
require File.dirname(__FILE__) + '/../lib/swivel2'

SWIVEL_URL = "http://inviteonly.swivel.com"

SWIVELRC = <<-EOS

Your API key is read from ~/.swivelrc:

    You need a ~/.swivelrc file before you can use this program!  The contents
    of your swivelrc file should look like this:

    --- !ruby/struct:Swivel2::Config 
    site: https://x:<YOUR_API_KEY>@api.swivel.com
    timeout_read: 500
    timeout_write: 1_000
    extra_params: { noviews: true }
EOS

EXAMPLES = <<-EOS

Examples:

    Upload a data set
    $ cat data.csv | #{$0} upload 1234567

    Append data to data set with id 1234567
    $ cat more_data.csv | #{$0} append 1234567

    Replace underlying data for data set with id 1234567
    $ cat new_data.csv | #{$0} replace 1234567
EOS

# todo: allow users to input their api instead of editing a resource file but let them know api can be changed in the resource file
options = Hash.new
config =
  begin
    Swivel2::Config.load
  rescue Errno::ENOENT
    puts SWIVELRC
    exit
  end
  
class SwivelHelper
  def initialize config
    @swivel = Swivel2::Connection.new config
  end

  def upload name, data, options = Hash.new
    opts = { 'data_set[name]' => name,
      'data_set[citation]' => $0,
      'data_set[public]' => 0,
      'file[data]' => data }
    opts.merge! 'file[types]' => options[:types] if options[:types]
    opts.merge! 'file[headings]' => options[:headings] if options[:headings]
    opts.merge! 'file[sep]' => options[:sep] if options[:sep]
    data_set = @swivel.post '/data_sets', opts
    puts "uploaded #{data_set.id}"
    data_set.id
  end

  def append id, options = Hash.new
    opts = { 'file[mode]' => 'append',
      'file[data]' => read(options[:filename]) }
    data_set = update id, opts
    puts "appended #{data_set.id}"
  end

  def replace id, data, options = Hash.new
    opts = { 'file[mode]' => 'replace',
      'file[data]' => data }
    data_set = update id, opts
    puts "replaced #{data_set.id}"
  end

  private
    def update id, options
      @swivel.put "/data_sets/#{id}", options
    end

    def read filename = nil
      if filename
        open filename do |f| f.readlines.join end
      else
        readlines.join
      end
    end
end

class SqlTable
  attr_accessor :server, :port, :socket, :username, :password, :database, :table, :sql, :random_table, :data, :data_set_name, :data_set_id, :last_sync_time

  INPUT_CONSTS = {
    :server => ['Enter Mysql Server Host Address','192.168.0.1','localhost'],
    :port => ['Enter Mysql Port','12345','3306'],
    :socket => ['Enter Mysql Socket Path','/tmp/mysql.socket','/var/lib/mysql/mysql.sock'],
    :username => ['Enter Mysql Username',"'bob'@'app01.xyz.com'","'root'@'localhost'"],
    :password => ['Enter Mysql Password If Needed',"mypassword123",""],
    :database => ['Enter Mysql Database Name',"db01","test"],
    :table => ['The above are the tables in the database you specified\nEnter the name of the table to sync to Swivel',"users",""],
    :sql => ['Customize your sql statement if needed (or press enter for default)','select name, address, date from users_table',''],
    :data_set_name => ['Enter the data set name to use in Swivel for this data','weather forecast year 2008 data','']
  }

  MYSQL_TO_SWIVEL_TYPE = {}
  
  def input var, default_val = nil
    sym = var.to_sym
    default_val ||= INPUT_CONSTS[sym][2]
    puts ''
    puts INPUT_CONSTS[sym][0]
    puts "      example:  #{INPUT_CONSTS[sym][1]}"
    puts "      default:  #{default_val}"
    print '>'
    val = gets.chomp 
    val = default_val if val.blank?
    self.send("#{sym.to_s}=", val)
  end
  
  def connect
	  ActiveRecord::Base.establish_connection(
	    :adapter  => "mysql",
	    :host     => @server.chomp,
	    :username => @username.chomp,
	    :password => @password.chomp,
	    :port => @port.chomp.to_i,
	    :socket => @socket.chomp,
	    :database => @database.chomp
	  )
  end
  
  def list_tables
    r = ActiveRecord::Base.connection.execute('show tables')
    h = r.all_hashes
    puts ''
    puts 'Listing all Tables in the Database Specified'
    h.each do |t|
      puts "   #{t.values.first}"
    end
    @random_table = h.last.values.first
  end

  def list_fields
    r = ActiveRecord::Base.connection.execute("show create table #{@table}")
    h = r.all_hashes
    puts ''
    puts "Listing structure of table #{@table}"
    puts "#{h.first['Create Table']}"
  end  

  def extract_data
    r = ActiveRecord::Base.connection.execute(@sql)
    fs = r.fetch_fields
    # do field mapping here so no autoestimation
    buffer = StringIO.new
    
    #titles
    buffer << fs.map(&:name).join("\t")
    buffer << "\n"
    
    #data
    r.each do |row|
      next  if row.all? &:blank?
      buffer << row.join("\t").gsub('"','').gsub("'",'').gsub("\n",'')
      buffer << "\n"
    end    
    
    @data = buffer.string
    
    puts ''
    puts "#{r.num_rows} rows of data ready to upload to Swivel"
  end
      
end

class Mysql2Swivel

  def initialize(config)
		@helper = SwivelHelper.new config
		@sqltables = []
		@sqltables = File.open("mysql2swivel.yml") do |f|
		              YAML.load(f)
		            end if File.exist?('mysql2swivel.yml')
  end
  
  def display_syncs_detailed
    puts ''
    puts ''
    puts "There are currently #{@sqltables.size} mysql datastreams syncing to swivel"
    puts '-----------------------------------------'
    @sqltables.each_with_index do |s,i|
      puts "[#{i}] dataset name:  #{s.data_set_name}"
      puts "       swivel dataset id: #{s.data_set_id}"
      puts "       url: #{SWIVEL_URL}/data_sets/#{s.data_set_id}"  
      puts "       last updated at: #{s.last_sync_time}"
      puts "     data from:"
      puts "       host: #{s.server} db: #{s.database} table: #{s.table}"
      puts "       sql: #{s.sql}"
      puts '-----------------------------------------'
    end
  end
  
  def display_syncs
    puts ''
    puts ''
    puts "There are currently #{@sqltables.size} mysql datastreams syncing to swivel"
    puts '-----------------------------------------'
    @sqltables.each_with_index do |s,i|
      puts "[#{i}] dataset name:  #{s.data_set_name}  last sync: #{s.last_sync_time.to_s}"
      puts '-----------------------------------------'
    end
  end
  
  def cron_info
    puts 'all the datastreams above will update and sync with the dataset in Swivel'
    puts 'when you run this script with the -update option'
    puts "you can add the script to your cron jobs for regular updates"
    puts "  example: to run the updates everyday at 4am add following line to your crontab"
    puts "  0 4 * * * mysql2swivel -update"
  end
  
  def main_menu
    exit = false
    verbose = false
    while !exit
	    verbose ? display_syncs_detailed : display_syncs  
	    cron_info unless @sqltables.blank?
	    puts "You can (A)dd or (D)elete a datastream from mysql, (T)oggle verbose or (Q)uit"
	    print ">"
	    command = gets
	    if command.downcase.starts_with? 'a'
	      add
	    elsif command.downcase.starts_with? 'd'
	      delete
	    elsif command.downcase.starts_with? 'q'
	      exit = true
	    elsif command.downcase.starts_with? 't'
	      verbose = !verbose
	    else
	      puts ''
	      puts "'#{command}' is not a valid action!"
	    end
	    save
	  end
  end
  
  def add
    puts 'Adding a mysql datastream'
		sqltable = SqlTable.new
		sqltable.input :server
		sqltable.input :port
		sqltable.input :socket
		sqltable.input :username
		sqltable.input :password
		sqltable.input :database
		sqltable.connect
		sqltable.list_tables
		sqltable.input :table, sqltable.random_table
		sqltable.list_fields
		sqltable.input :sql, "select * from #{sqltable.table}"
		sqltable.extract_data
		sqltable.input :data_set_name
		puts 'uploading...'
		sqltable.data_set_id = @helper.upload sqltable.data_set_name, sqltable.data
		sqltable.last_sync_time = Time.now
		sqltable.data = nil
		puts "data successfully uploaded to Swivel"
		sleep 2
		puts "the swivel dataset can be found at #{SWIVEL_URL}/data_sets/#{sqltable.data_set_id}"
		sleep 2
		puts "the data source has also been setup to update when you run this script with -update option"
		@sqltables << sqltable
		sleep 5
  end

  def delete
    display_syncs_detailed   
    puts 'give index of the datastream to delete:'
    print '>'
    index = gets.to_i
    s = @sqltables.delete_at index
    puts ''
    puts "#{s.data_set_name} deleted"
    sleep 3
  end
  
  def save
    @sqltables.each{|s|s.data = nil}
		File.open("mysql2swivel.yml",'w') do |f|
		  YAML.dump(@sqltables, f)
		end
  end
  
  def update
    @sqltables.each do |s|
      begin
        s.connect
        s.extract_data        
        @helper.replace s.data_set_id.to_i, s.data
        s.last_sync_time = Time.now
      rescue Exception => e
        puts "dataset #{s.data_set_name} with id #{s.data_set_id} did not update due to error: #{e.message}"
      end
    end
    save
  end
  
end

r = Mysql2Swivel.new config

if ARGV.blank?
  r.main_menu
elsif ARGV.first.downcase.starts_with?('-u')
  r.update
else
  puts 'run script with no options to setup datastreams'
  puts 'use -update option to sync all previously setup datastreams'
end