module OpenStudio
  module Analysis
    module Translator
      class Excel
        attr_reader :version
        attr_reader :settings
        attr_reader :variables
        attr_reader :outputs
        attr_reader :models
        attr_reader :weather_files
        attr_reader :measure_paths
        attr_reader :weather_paths
        attr_reader :worker_inits
        attr_reader :worker_finals
        attr_reader :export_path
        attr_reader :cluster_name
        attr_reader :variables
        attr_reader :algorithm
        attr_reader :problem
        attr_reader :run_setup
        attr_reader :aws_tags

        # remove these once we have classes to construct the JSON file
        attr_accessor :name
        attr_reader :analysis_name

        # methods to override instance variables

        # pass in the filename to read
        def initialize(xls_filename)
          @xls_filename = xls_filename
          @root_path = File.expand_path(File.dirname(@xls_filename))

          @xls = nil
          # try to read the spreadsheet as a roo object
          if File.exist?(@xls_filename)
            @xls = Roo::Spreadsheet.open(@xls_filename)
          else
            fail "File #{@xls_filename} does not exist"
          end

          # Initialize some other instance variables
          @version = '0.0.1'
          @analyses = [] # Array o OpenStudio::Analysis. Use method to access
          @name = nil
          @analysis_name = nil
          @cluster_name = nil
          @settings = {}
          @weather_files = [] # remove this from excel!
          @weather_paths = []
          @models = []
          @other_files = []
          @worker_inits = []
          @worker_finals = []
          @export_path = './export'
          @measure_paths = []
          @number_of_samples = 0 # TODO: remove this
          @problem = {}
          @algorithm = {}
          @outputs = {}
          @run_setup = {}
          @aws_tags = []
        end

        def process
          @setup = parse_setup

          @version = Semantic::Version.new @version
          fail "Spreadsheet version #{@version} is no longer supported.  Please upgrade your spreadsheet to at least 0.1.9" if @version < '0.1.9'

          @variables = parse_variables

          @outputs = parse_outputs

          # call validate to make sure everything that is needed exists (i.e. directories)
          validate_analysis
        end

        # Helper methods to remove models and add new ones programatically. Note that these should
        # be moved into a general analysis class
        def delete_models
          @models = []
        end

        def add_model(name, display_name, type, path)
          @models << {
            name: name,
            display_name: display_name,
            type: type,
            path: path
          }
        end

        def validate_analysis
          # Setup the paths and do some error checking
          @measure_paths.each do |mp|
            fail "Measures directory '#{mp}' does not exist" unless Dir.exist?(mp)
          end

          @models.uniq!
          fail 'No seed models defined in spreadsheet' if @models.empty?

          @models.each do |model|
            fail "Seed model does not exist: #{model[:path]}" unless File.exist?(model[:path])
          end

          @weather_files.uniq!
          fail 'No weather files found based on what is in the spreadsheet' if @weather_files.empty?

          @weather_files.each do |wf|
            fail "Weather file does not exist: #{wf}" unless File.exist?(wf)
          end

          # This can be a directory as well
          @other_files.each do |f|
            fail "Other files do not exist for: #{f[:path]}" unless File.exist?(f[:path])
          end

          @worker_inits.each do |f|
            fail "Worker initialization file does not exist for: #{f[:path]}" unless File.exist?(f[:path])
          end

          @worker_finals.each do |f|
            fail "Worker finalization file does not exist for: #{f[:path]}" unless File.exist?(f[:path])
          end

          FileUtils.mkdir_p(@export_path)

          # verify that the measure display names are unique
          # puts @variables.inspect
          measure_display_names = @variables['data'].map { |m| m['enabled'] ? m['display_name'] : nil }.compact
          measure_display_names_mult = measure_display_names.select { |m| measure_display_names.count(m) > 1 }.uniq
          if measure_display_names_mult && !measure_display_names_mult.empty?
            fail "Measure Display Names are not unique for '#{measure_display_names_mult.join('\', \'')}'"
          end

          # verify that all continuous variables have all the data needed and create a name map
          variable_names = []
          @variables['data'].each do |measure|
            if measure['enabled']
              measure['variables'].each do |variable|
                # Determine if row is suppose to be an argument or a variable to be perturbed.
                if variable['variable_type'] == 'variable'
                  variable_names << variable['display_name']

                  # make sure that variables have static values
                  if variable['distribution']['static_value'].nil? || variable['distribution']['static_value'] == ''
                    fail "Variable #{measure['name']}:#{variable['name']} needs a static value"
                  end

                  if variable['type'] == 'enum' || variable['type'] == 'Choice'
                    # check something
                  else # must be an integer or double
                    if variable['distribution']['type'] == 'discrete_uncertain'
                      if variable['distribution']['discrete_values'].nil? || variable['distribution']['discrete_values'] == ''
                        fail "Variable #{measure['name']}:#{variable['name']} needs discrete values"
                      end
                    else
                      if variable['distribution']['mean'].nil? || variable['distribution']['mean'] == ''
                        fail "Variable #{measure['name']}:#{variable['name']} must have a mean"
                      end
                      if variable['distribution']['stddev'].nil? || variable['distribution']['stddev'] == ''
                        fail "Variable #{measure['name']}:#{variable['name']} must have a stddev"
                      end
                    end

                    if variable['distribution']['mean'].nil? || variable['distribution']['mean'] == ''
                      fail "Variable #{measure['name']}:#{variable['name']} must have a mean/mode"
                    end
                    if variable['distribution']['min'].nil? || variable['distribution']['min'] == ''
                      fail "Variable #{measure['name']}:#{variable['name']} must have a minimum"
                    end
                    if variable['distribution']['max'].nil? || variable['distribution']['max'] == ''
                      fail "Variable #{measure['name']}:#{variable['name']} must have a maximum"
                    end
                    unless variable['type'] == 'string' || variable['type'] =~ /bool/
                      if variable['distribution']['min'] > variable['distribution']['max']
                        fail "Variable min is greater than variable max for #{measure['name']}:#{variable['name']}"
                      end
                    end

                  end
                end
              end
            end
          end

          dupes = variable_names.select { |e| variable_names.count(e) > 1 }.uniq
          if dupes.count > 0
            fail "duplicate variable names found in list #{dupes.inspect}"
          end

          # most of the checks will raise a runtime exception, so this true will never be called
          true
        end

        # convert the data in excel's parsed data into an OpenStudio Analysis Object
        # @seed_model [Hash] Seed model to set the new analysis to
        # @append_model_name [Boolean] Append the name of the seed model to the display name
        # @return [Object] An OpenStudio::Analysis
        def analysis(seed_model = nil, append_model_name = false)
          fail 'There are no seed models defined in the excel file. Please add one.' if @models.size == 0
          fail "There are more than one seed models defined in the excel file. Call 'analyses' to return the array" if @models.size > 1 && seed_model.nil?

          seed_model = @models.first if seed_model.nil?

          # Use the programmatic interface to make the analysis
          # append the model name to the analysis name if requested (normally if there are more than 1 models in the spreadsheet)
          display_name = append_model_name ? @name + ' ' + seed_model[:display_name] : @name

          a = OpenStudio::Analysis.create(display_name)

          @variables['data'].each do |measure|
            next unless measure['enabled']

            @measure_paths.each do |measure_path|
              measure_dir_to_add = "#{measure_path}/#{measure['measure_file_name_directory']}"
              if Dir.exist? measure_dir_to_add
                if File.exist? "#{measure_dir_to_add}/measure.rb"
                  measure['local_path_to_measure'] = "#{measure_dir_to_add}/measure.rb"
                  break
                else
                  fail "Measure in directory '#{measure_dir_to_add}' did not contain a measure.rb file"
                end
              end
            end

            fail "Could not find measure '#{measure['name']}' in directory named '#{measure['measure_file_name_directory']}' in the measure paths '#{@measure_paths.join(', ')}'" unless measure['local_path_to_measure']

            a.workflow.add_measure_from_excel(measure)
          end

          @other_files.each do |library|
            a.libraries.add(library[:path], library_name: library[:lib_zip_name])
          end

          @worker_inits.each do |w|
            a.worker_inits.add(w[:path], args: w[:args])
          end

          @worker_finals.each do |w|
            a.worker_finalizes.add(w[:path], args: w[:args])
          end

          # Add in the outputs
          @outputs['output_variables'].each do |o|
            o = Hash[o.map { |k, v| [k.to_sym, v] }]
            a.add_output(o)
          end

          a.analysis_type = @problem['analysis_type']
          @algorithm.each do |k, v|
            a.algorithm.set_attribute(k, v)
          end

          # clear out the seed files before adding new ones
          a.seed_model = seed_model[:path]

          # clear out the weather files before adding new ones
          a.weather_files.clear
          @weather_paths.each do |wp|
            a.weather_files.add_files(wp)
          end

          a
        end

        # Return an array of analyses objects of OpenStudio::Analysis::Formulation
        def analyses
          as = []
          @models.map do |model|
            as << analysis(model, @models.count > 1)
          end

          as
        end

        # save_analysis will iterate over each model that is defined in the spreadsheet and save the
        # zip and json file.
        def save_analysis
          analyses.each do |a|
            puts "Saving JSON and ZIP file for #{@name}:#{a.display_name}"
            json_file_name = "#{@export_path}/#{a.name}.json"
            FileUtils.rm_f(json_file_name) if File.exist?(json_file_name)
            # File.open(json_file_name, 'w') { |f| f << JSON.pretty_generate(new_analysis_json) }

            a.save json_file_name
            a.save_zip "#{File.dirname(json_file_name)}/#{File.basename(json_file_name, '.*')}.zip"
          end
        end

        protected

        # parse_setup will pull out the data on the "setup" tab and store it in memory for later use
        def parse_setup
          rows = @xls.sheet('Setup').parse
          b_settings = false
          b_run_setup = false
          b_problem_setup = false
          b_algorithm_setup = false
          b_weather_files = false
          b_models = false
          b_other_libs = false
          b_worker_init = false
          b_worker_final = false

          rows.each do |row|
            if row[0] == 'Settings'
              b_settings = true
              b_run_setup = false
              b_problem_setup = false
              b_algorithm_setup = false
              b_weather_files = false
              b_models = false
              b_other_libs = false
              b_worker_init = false
              b_worker_final = false
              next
            elsif row[0] == 'Running Setup'
              b_settings = false
              b_run_setup = true
              b_problem_setup = false
              b_algorithm_setup = false
              b_weather_files = false
              b_models = false
              b_other_libs = false
              b_worker_init = false
              b_worker_final = false
              next
            elsif row[0] == 'Problem Definition'
              b_settings = false
              b_run_setup = false
              b_problem_setup = true
              b_algorithm_setup = false
              b_weather_files = false
              b_models = false
              b_other_libs = false
              b_worker_init = false
              b_worker_final = false
              next
            elsif row[0] == 'Algorithm Setup'
              b_settings = false
              b_run_setup = false
              b_problem_setup = false
              b_algorithm_setup = true
              b_weather_files = false
              b_models = false
              b_other_libs = false
              b_worker_init = false
              b_worker_final = false
              next
            elsif row[0] == 'Weather Files'
              b_settings = false
              b_run_setup = false
              b_problem_setup = false
              b_algorithm_setup = false
              b_weather_files = true
              b_models = false
              b_other_libs = false
              b_worker_init = false
              b_worker_final = false
              next
            elsif row[0] == 'Models'
              b_settings = false
              b_run_setup = false
              b_problem_setup = false
              b_algorithm_setup = false
              b_weather_files = false
              b_models = true
              b_other_libs = false
              b_worker_init = false
              b_worker_final = false
              next
            elsif row[0] == 'Other Library Files'
              b_settings = false
              b_run_setup = false
              b_problem_setup = false
              b_algorithm_setup = false
              b_weather_files = false
              b_models = false
              b_other_libs = true
              b_worker_init = false
              b_worker_final = false
              next
            elsif row[0] =~ /Worker Initialization Scripts/
              b_settings = false
              b_run_setup = false
              b_problem_setup = false
              b_algorithm_setup = false
              b_weather_files = false
              b_models = false
              b_other_libs = false
              b_worker_init = true
              b_worker_final = false
              next
            elsif row[0] =~ /Worker Finalization Scripts/
              b_settings = false
              b_run_setup = false
              b_problem_setup = false
              b_algorithm_setup = false
              b_weather_files = false
              b_models = false
              b_other_libs = false
              b_worker_init = false
              b_worker_final = true
              next
            end

            next if row[0].nil?

            if b_settings
              @version = row[1].chomp if row[0] == 'Spreadsheet Version'
              @settings["#{row[0].snake_case}"] = row[1] if row[0]
              @cluster_name = @settings['cluster_name'].snake_case if @settings['cluster_name']

              if row[0] == 'AWS Tag'
                @aws_tags << row[1].strip
              end

              # type some of the values that we know
              @settings['proxy_port'] = @settings['proxy_port'].to_i if @settings['proxy_port']

            elsif b_run_setup
              if row[0] == 'Analysis Name'
                if row[1]
                  @name = row[1]
                else
                  @name = SecureRandom.uuid
                end
                @analysis_name = @name.snake_case
              end
              if row[0] == 'Export Directory'
                tmp_filepath = row[1]
                if (Pathname.new tmp_filepath).absolute?
                  @export_path = tmp_filepath
                else
                  @export_path = File.expand_path(File.join(@root_path, tmp_filepath))
                end
              end
              if row[0] == 'Measure Directory'
                tmp_filepath = row[1]
                if (Pathname.new tmp_filepath).absolute?
                  @measure_paths << tmp_filepath
                else
                  @measure_paths << File.expand_path(File.join(@root_path, tmp_filepath))
                end
              end
              @run_setup["#{row[0].snake_case}"] = row[1] if row[0]

              # type cast
              @run_setup['allow_multiple_jobs'] = @run_setup['allow_multiple_jobs'].to_s.to_bool if @run_setup['allow_multiple_jobs']
              @run_setup['use_server_as_worker'] = @run_setup['use_server_as_worker'].to_s.to_bool if @run_setup['use_server_as_worker']
            elsif b_problem_setup
              if row[0]
                v = row[1]
                v.to_i if v % 1 == 0
                @problem["#{row[0].snake_case}"] = v
              end

            elsif b_algorithm_setup
              if row[0] && !row[0].empty?
                v = row[1]
                v = v.to_i if v % 1 == 0
                @algorithm["#{row[0].snake_case}"] = v
              end
            elsif b_weather_files
              if row[0] == 'Weather File'
                weather_path = row[1]
                unless (Pathname.new weather_path).absolute?
                  weather_path = File.expand_path(File.join(@root_path, weather_path))
                end
                @weather_paths << weather_path
                @weather_files += Dir.glob(weather_path)
              end
            elsif b_models
              if row[1]
                tmp_m_name = row[1]
              else
                tmp_m_name = SecureRandom.uuid
              end
              # Only add models if the row is flagged
              if row[0] && row[0].downcase == 'model'
                model_path = row[3]
                unless (Pathname.new model_path).absolute?
                  model_path = File.expand_path(File.join(@root_path, model_path))
                end
                @models << { name: tmp_m_name.snake_case, display_name: tmp_m_name, type: row[2], path: model_path }
              end
            elsif b_other_libs
              # determine if the path is relative
              other_path = row[2]
              unless (Pathname.new other_path).absolute?
                other_path = File.expand_path(File.join(@root_path, other_path))
              end

              @other_files << { lib_zip_name: row[1], path: other_path }
            elsif b_worker_init
              worker_init_path = row[1]
              unless (Pathname.new worker_init_path).absolute?
                worker_init_path = File.expand_path(File.join(@root_path, worker_init_path))
              end

              @worker_inits << { name: row[0], path: worker_init_path, args: row[2] }
            elsif b_worker_final
              worker_final_path = row[1]
              unless (Pathname.new worker_final_path).absolute?
                worker_final_path = File.expand_path(File.join(@root_path, worker_final_path))
              end

              @worker_finals << { name: row[0], path: worker_final_path, args: row[2] }
            end

            next
          end

          # do some last checks
          @measure_paths = ['./measures'] if @measure_paths.empty?
        end

        # parse_variables will parse the XLS spreadsheet and save the data into
        # a higher level JSON file.  The JSON file is historic and it should really
        # be omitted as an intermediate step
        def parse_variables
          # clean remove whitespace and unicode chars
          # The parse is a unique format (https://github.com/Empact/roo/blob/master/lib/roo/base.rb#L444)
          # If you add a new column and you want that variable in the hash, then you must add it here.
          # rows = @xls.sheet('Variables').parse(:enabled => "# variable")
          # puts rows.inspect

          rows = nil
          begin
            if @version >= '0.3.3'.to_version
              rows = @xls.sheet('Variables').parse(enabled: '# variable',
                                                   measure_name_or_var_type: 'type',
                                                   measure_file_name_or_var_display_name: 'parameter display name*',
                                                   measure_file_name_directory: 'measure directory',
                                                   measure_type_or_parameter_name_in_measure: 'parameter name in measure',
                                                   display_name_short: 'parameter short display name',
                                                   # sampling_method: 'sampling method',
                                                   variable_type: 'Variable Type',
                                                   units: 'units',
                                                   default_value: 'static.default value',
                                                   enums: 'enumerations',
                                                   min: 'min',
                                                   max: 'max',
                                                   mode: 'mean|mode',
                                                   stddev: 'std dev',
                                                   delta_x: 'delta.x',
                                                   discrete_values: 'discrete values',
                                                   discrete_weights: 'discrete weights',
                                                   distribution: 'distribution',
                                                   source: 'data source',
                                                   notes: 'notes',
                                                   relation_to_eui: 'typical var to eui relationship',
                                                   clean: true)
            elsif @version >= '0.3.0'.to_version
              rows = @xls.sheet('Variables').parse(enabled: '# variable',
                                                   measure_name_or_var_type: 'type',
                                                   measure_file_name_or_var_display_name: 'parameter display name*',
                                                   measure_file_name_directory: 'measure directory',
                                                   measure_type_or_parameter_name_in_measure: 'parameter name in measure',
                                                   # sampling_method: 'sampling method',
                                                   variable_type: 'Variable Type',
                                                   units: 'units',
                                                   default_value: 'static.default value',
                                                   enums: 'enumerations',
                                                   min: 'min',
                                                   max: 'max',
                                                   mode: 'mean|mode',
                                                   stddev: 'std dev',
                                                   delta_x: 'delta.x',
                                                   discrete_values: 'discrete values',
                                                   discrete_weights: 'discrete weights',
                                                   distribution: 'distribution',
                                                   source: 'data source',
                                                   notes: 'notes',
                                                   relation_to_eui: 'typical var to eui relationship',
                                                   clean: true)
            elsif @version >= '0.2.0'.to_version
              rows = @xls.sheet('Variables').parse(enabled: '# variable',
                                                   measure_name_or_var_type: 'type',
                                                   measure_file_name_or_var_display_name: 'parameter display name*',
                                                   measure_file_name_directory: 'measure directory',
                                                   measure_type_or_parameter_name_in_measure: 'parameter name in measure',
                                                   sampling_method: 'sampling method',
                                                   variable_type: 'Variable Type',
                                                   units: 'units',
                                                   default_value: 'static.default value',
                                                   enums: 'enumerations',
                                                   min: 'min',
                                                   max: 'max',
                                                   mode: 'mean|mode',
                                                   stddev: 'std dev',
                                                   delta_x: 'delta.x',
                                                   discrete_values: 'discrete values',
                                                   discrete_weights: 'discrete weights',
                                                   distribution: 'distribution',
                                                   source: 'data source',
                                                   notes: 'notes',
                                                   relation_to_eui: 'typical var to eui relationship',
                                                   clean: true)
            elsif @version >= '0.1.12'.to_version
              rows = @xls.sheet('Variables').parse(enabled: '# variable',
                                                   measure_name_or_var_type: 'type',
                                                   measure_file_name_or_var_display_name: 'parameter display name*',
                                                   measure_type_or_parameter_name_in_measure: 'parameter name in measure',
                                                   sampling_method: 'sampling method',
                                                   variable_type: 'Variable Type',
                                                   units: 'units',
                                                   default_value: 'static.default value',
                                                   enums: 'enumerations',
                                                   min: 'min',
                                                   max: 'max',
                                                   mode: 'mean|mode',
                                                   stddev: 'std dev',
                                                   delta_x: 'delta.x',
                                                   discrete_values: 'discrete values',
                                                   discrete_weights: 'discrete weights',
                                                   distribution: 'distribution',
                                                   source: 'data source',
                                                   notes: 'notes',
                                                   relation_to_eui: 'typical var to eui relationship',
                                                   clean: true)
            elsif @version >= '0.1.11'.to_version
              rows = @xls.sheet('Variables').parse(enabled: '# variable',
                                                   measure_name_or_var_type: 'type',
                                                   measure_file_name_or_var_display_name: 'parameter display name*',
                                                   measure_type_or_parameter_name_in_measure: 'parameter name in measure',
                                                   sampling_method: 'sampling method',
                                                   variable_type: 'Variable Type',
                                                   units: 'units',
                                                   default_value: 'static.default value',
                                                   enums: 'enumerations',
                                                   min: 'min',
                                                   max: 'max',
                                                   mode: 'mean|mode',
                                                   stddev: 'std dev',
                                                   #:delta_x => 'delta.x',
                                                   discrete_values: 'discrete values',
                                                   discrete_weights: 'discrete weights',
                                                   distribution: 'distribution',
                                                   source: 'data source',
                                                   notes: 'notes',
                                                   relation_to_eui: 'typical var to eui relationship',
                                                   clean: true)
            else
              rows = @xls.sheet('Variables').parse(enabled: '# variable',
                                                   measure_name_or_var_type: 'type',
                                                   measure_file_name_or_var_display_name: 'parameter display name*',
                                                   measure_type_or_parameter_name_in_measure: 'parameter name in measure',
                                                   sampling_method: 'sampling method',
                                                   variable_type: 'Variable Type',
                                                   units: 'units',
                                                   default_value: 'static.default value',
                                                   enums: 'enumerations',
                                                   min: 'min',
                                                   max: 'max',
                                                   mode: 'mean|mode',
                                                   stddev: 'std dev',
                                                   #:delta_x => 'delta.x',
                                                   #:discrete_values => 'discrete values',
                                                   #:discrete_weights => 'discrete weights',
                                                   distribution: 'distribution',
                                                   source: 'data source',
                                                   notes: 'notes',
                                                   relation_to_eui: 'typical var to eui relationship',
                                                   clean: true)
            end
          rescue => e
            raise "Unable to parse spreadsheet #{@xls_filename} with version #{@version} due to error: #{e.message}"
          end

          fail "Could not find the sheet name 'Variables' in excel file #{@root_path}" unless rows

          # map the data to another hash that is more easily processed
          data = {}
          data['data'] = []

          measure_index = -1
          variable_index = -1
          measure_name = nil
          rows.each_with_index do |row, icnt|
            next if icnt < 1 # skip the first line after the header
            # puts "Parsing line: #{icnt}:#{row}"

            # check if we are a measure - nil means that the cell was blank
            if row[:enabled].nil?
              if measure_name && data['data'][measure_index]['enabled']
                variable_index += 1

                var = {}
                var['variable_type'] = row[:measure_name_or_var_type]
                var['display_name'] = row[:measure_file_name_or_var_display_name]
                var['display_name_short'] = row[:display_name_short] ? row[:display_name_short] : var['display_name']
                var['name'] = row[:measure_type_or_parameter_name_in_measure]
                var['index'] = variable_index # order of the variable (not sure of its need)
                var['type'] = row[:variable_type].downcase
                var['units'] = row[:units]
                var['distribution'] = {}

                # parse the choices/enums
                if var['type'] == 'enum' || var['type'] == 'choice' # this is now a choice
                  if row[:enums]
                    var['distribution']['enumerations'] = row[:enums].delete('|').split(',').map(&:strip)
                  end
                elsif var['type'] == 'bool'
                  var['distribution']['enumerations'] = []
                  var['distribution']['enumerations'] << 'true' # TODO: should this be a real bool?
                  var['distribution']['enumerations'] << 'false'
                end

                var['distribution']['min'] = row[:min]
                var['distribution']['max'] = row[:max]
                var['distribution']['mean'] = row[:mode]
                var['distribution']['stddev'] = row[:stddev]
                var['distribution']['discrete_values'] = row[:discrete_values]
                var['distribution']['discrete_weights'] = row[:discrete_weights]
                var['distribution']['type'] = row[:distribution]
                var['distribution']['static_value'] = row[:default_value]
                var['distribution']['delta_x'] = row[:delta_x]

                # type various values correctly
                var['distribution']['min'] = typecast_value(var['type'], var['distribution']['min'])
                var['distribution']['max'] = typecast_value(var['type'], var['distribution']['max'])
                var['distribution']['mean'] = typecast_value(var['type'], var['distribution']['mean'])
                var['distribution']['stddev'] = typecast_value(var['type'], var['distribution']['stddev'])
                var['distribution']['static_value'] = typecast_value(var['type'], var['distribution']['static_value'])

                # eval the discrete value and weight arrays
                case var['type']
                  when 'bool', 'boolean'
                    if var['distribution']['discrete_values']
                      var['distribution']['discrete_values'] = eval(var['distribution']['discrete_values']).map { |v| v.to_s == 'true' }
                    end
                    if var['distribution']['discrete_weights'] && var['distribution']['discrete_weights'] != ''
                      var['distribution']['discrete_weights'] = eval(var['distribution']['discrete_weights'])
                    end
                  else
                    if var['distribution']['discrete_values']
                      var['distribution']['discrete_values'] = eval(var['distribution']['discrete_values'])
                    end
                    if var['distribution']['discrete_weights'] && var['distribution']['discrete_weights'] != ''
                      var['distribution']['discrete_weights'] = eval(var['distribution']['discrete_weights'])
                    end
                end

                var['distribution']['source'] = row[:source]
                var['notes'] = row[:notes]
                var['relation_to_eui'] = row[:relation_to_eui]

                data['data'][measure_index]['variables'] << var
              end
            else
              measure_index += 1
              variable_index = 0
              data['data'][measure_index] = {}

              # generate name id
              # TODO: put this into a logger. puts "Parsing measure #{row[1]}"
              display_name = row[:measure_name_or_var_type]
              measure_name = display_name.downcase.strip.tr('-', '_').tr(' ', '_').gsub('__', '_')
              data['data'][measure_index]['display_name'] = display_name
              data['data'][measure_index]['name'] = measure_name
              data['data'][measure_index]['enabled'] = row[:enabled] == 'TRUE' ? true : false
              data['data'][measure_index]['measure_file_name'] = row[:measure_file_name_or_var_display_name]
              if row[:measure_file_name_directory]
                data['data'][measure_index]['measure_file_name_directory'] = row[:measure_file_name_directory]
              else
                data['data'][measure_index]['measure_file_name_directory'] = row[:measure_file_name_or_var_display_name].underscore
              end
              data['data'][measure_index]['measure_type'] = row[:measure_type_or_parameter_name_in_measure]
              data['data'][measure_index]['version'] = @version_id

              data['data'][measure_index]['variables'] = []
            end
          end

          # puts data.inspect
          data
        end

        def parse_outputs
          rows = nil
          if @version >= '0.3.3'.to_version
            rows = @xls.sheet('Outputs').parse(display_name: 'Variable Display Name',
                                               display_name_short: 'Short Display Name',
                                               metadata_id: 'Taxonomy Identifier',
                                               name: '^Name$',
                                               units: 'Units',
                                               visualize: 'Visualize',
                                               export: 'Export',
                                               variable_type: 'Variable Type',
                                               objective_function: 'Objective Function',
                                               objective_function_target: 'Objective Function Target',
                                               scaling_factor: 'Scale',
                                               objective_function_group: 'Objective Function Group')
          elsif @version >= '0.3.0'.to_version
            rows = @xls.sheet('Outputs').parse(display_name: 'Variable Display Name',
                                               metadata_id: 'Taxonomy Identifier',
                                               name: '^Name$',
                                               units: 'Units',
                                               visualize: 'Visualize',
                                               export: 'Export',
                                               variable_type: 'Variable Type',
                                               objective_function: 'Objective Function',
                                               objective_function_target: 'Objective Function Target',
                                               scaling_factor: 'Scale',
                                               objective_function_group: 'Objective Function Group')
          else
            rows = @xls.sheet('Outputs').parse(display_name: 'Variable Display Name',
                                               name: '^Name$',
                                               units: 'units',
                                               objective_function: 'objective function',
                                               objective_function_target: 'objective function target',
                                               scaling_factor: 'scale',
                                               objective_function_group: 'objective')
          end

          unless rows
            fail "Could not find the sheet name 'Outputs' in excel file #{@root_path}"
          end

          data = {}
          data['output_variables'] = []

          variable_index = -1
          group_index = 1

          rows.each_with_index do |row, icnt|
            next if icnt < 2 # skip the first 3 lines of the file

            var = {}
            var['display_name'] = row[:display_name]
            var['display_name_short'] = row[:display_name_short] ? row[:display_name_short] : row[:display_name]
            var['metadata_id'] = row[:metadata_id]
            var['name'] = row[:name]
            var['units'] = row[:units]
            var['visualize'] = row[:visualize].downcase == 'true' ? true : false if row[:visualize]
            var['export'] = row[:export].downcase == 'true' ? true : false if row[:export]
            var['variable_type'] = row[:variable_type].downcase if row[:variable_type]
            var['objective_function'] = row[:objective_function].downcase == 'true' ? true : false
            var['objective_function_target'] = row[:objective_function_target]
            var['scaling_factor'] = row[:scaling_factor]

            if var['objective_function']
              if row[:objective_function_group].nil?
                var['objective_function_group'] = group_index
                group_index += 1
              else
                var['objective_function_group'] = row[:objective_function_group]
              end
            end
            data['output_variables'] << var
          end

          data
        end
      end
    end
  end
end