module Voyager
  class OracleConnection

    attr_reader :connection
    attr_accessor :results 

    def initialize(args = {})
      args.symbolize_keys!
      @connection = args[:connection] 
    

      unless args.has_key?(:connection)
        app_config_filename = File.join(File.dirname(__FILE__), "..",  "config" ,"app_config.yml")

        args = YAML.load_file(app_config_filename)['oracle_connection_details'] if args == {} and File.exists?(app_config_filename)
        args.symbolize_keys!

        raise "Need argument 'user'" unless args.has_key?(:user) 
        raise "Need argument 'password'" unless args.has_key?(:password)
        raise "Need argument 'service'" unless args.has_key?(:service)

        @connection = OCI8.new(args[:user], args[:password], args[:service])
        @connection.prefetch_rows = 1000
      end

      @results = {}

    end

    
    def retrieve_holdings(*bibids)
      bibids = Array.wrap(bibids).flatten

      query = <<-HERE
        select a.bib_id, a.mfhd_id, c.item_id, item_status
        from bib_mfhd a, mfhd_master b, mfhd_item c, item_status d
        where a.bib_id IN (~bibid~) and
        b.mfhd_id = a.mfhd_id and
        suppress_in_opac = 'N' and
        c.mfhd_id (+) = b.mfhd_id and
        d.item_id (+) = c.item_id
        order by c.mfhd_id, c.item_id, item_status
      HERE

      unless bibids.empty?
        raw_results = execute_select_command(query, bibid: bibids)
        parse_results(raw_results, name: 'retrieve_holdings', hash_by: 'BIB_ID')
      else
        @results['retrieve_holdings'] ||= {}
      end
    end


    private

    def parse_results(results, *args)
      options = args.extract_options!
      @results[options[:name]] ||= {}
      result_hash = @results[options[:name]]

      results.each do |row|
        if (key = row[options[:hash_by]].to_s)
          
          result_hash[key] ||= []
          result_hash[key] << row
        end
      end

      return result_hash
    end




    def execute_select_command(query, *args)
      options = args.extract_options!
      options.each do |name, value|
        value = [value] unless value.kind_of?(Array)
        formatted_value = value.collect { |item| "'#{item.to_s.gsub("'","''")}'"}.join(',')
        query.gsub!("~#{name.to_s}~",formatted_value)
      end


      cursor = @connection.parse(query)

      results = []
      cursor.exec
      
      cursor.fetch_hash do |row|
        results << row
      end

      cursor.close

      return results

    
    end


  end
end