require 'mysql_routine'

class MysqlRoutineMigration < ActiveRecord::Migration
  PROC_DIR = "#{RAILS_ROOT}/db/migrate/routines/"
  @script_file_names = []

  protected
  def self.script_file_names=(val)
    @script_file_names = val
  end
  
  def self.script_file_names
    @script_file_names.map { |file_name| PROC_DIR + file_name }
  end
	
  def self.up
    raise 'script_file_names (Array) is required' if @script_file_names.empty?
    
    @script_file_names.each do |file_name|
      say('reading sql file ' + file_name)
      begin
        file = File.open(PROC_DIR + file_name, "rb")
        file.readline # /* Migration params
        name_return = file.readline.chomp.split(',') # routine name, return value for fx's
        if name_return.size == 2 # fx
          routine_name, fx_return_value = name_return
        else # procedure
          routine_name = name_return[0]
        end
        routine_params = file.readline.chomp
        file.readline # */
        file.readline # DELIMITER $$
        
        execute file.readline.chomp.tr('$','') # DROP PROCEDURE/FUNCTION IF EXISTS
        
        routine_type = file.readline.upcase.include?(' FUNCTION ') ? 'FUNCTION' : 'PROCEDURE' # CREATE PROCEDURE/FUNCTION
        
        body = file.readlines.join
        say body
        body = body.tr('$','').sub(/DELIMITER ;/,'').chomp
    
        routine = MysqlRoutine.new
        
        routine.db = ActiveRecord::Base.connection.current_database
        routine.name = routine_name
        routine.type = routine_type
        routine.specific_name = routine.name
        routine.param_list = routine_params
        routine.returns = fx_return_value if fx_return_value
        routine.definer = 'root@localhost'
        routine.sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER'
        routine.body = body
        
        routine.save!
      ensure
        file.close if file
      end
    end
  end
  
  def self.down
    raise 'script_file_names (Array) is required' if @script_file_names.empty?
    
    @script_file_names.each do |file_name|
      say('reading sql file ' + file_name)
      begin
        file = File.open(PROC_DIR + file_name, "rb")
        file.readline # /* Migration params
        routine_name = file.readline.chomp
        routine_params = file.readline.chomp
        file.readline # */
        file.readline # DELIMITER $$
        
        execute file.readline.chomp.tr('$','') # DROP PROCEDURE/FUNCTION IF EXISTS
        
      ensure
        file.close if file
      end
    end
  end
end