module PgsqlStats
  class Analysis
    @@analysis_hint_proc = lambda do |template|
      template.image_tag("exclamation.gif", :alt => "database performance suggestion", :size => "12x12")
    end
    cattr_accessor :analysis_hint_proc
    
    TABLE_MIN_SEQ_SCANS = 1000
    
    def self.analyze_tables(tables)
#      median_scans = tables.collect { |t| t.sequential_scans }.median
    end
    
    def self.analyze_indexes(indexes)
      table_oids = indexes.collect { |index| index.table_oid }.uniq
      table_oids.each do |table_oid|
        # Gather all of the indexes for a table, and exclude the primary key index.
        table_indexes = indexes.find_all { |index| index.table_oid == table_oid }
        table_indexes.reject! { |index| index.name =~ /_pkey$/ }
        
        next if table_indexes.empty?
        
        # For any table with enough sequential scans, see if the index
        # factored into enough of the queries.
        next unless table_has_enough_activity(table_indexes.first.table)
        
        table_indexes.each do |index|
          if index_infrequently_used(index)
            index.analysis ||= Analysis.new
            index.analysis.consider_dropping_index = true
          end
        end
      end

      nil
    end
    
    attr_writer :consider_adding_index
    attr_writer :consider_dropping_index
    
    def initialize
      @consider_adding_index = false
      @consider_dropping_index = false
    end
    
    def consider_adding_index?
      @consider_adding_index
    end
    
    def consider_dropping_index?
      @consider_dropping_index
    end
    
    private
    
    def self.table_has_enough_activity(table)
      table.sequential_scans >= TABLE_MIN_SEQ_SCANS
    end
    
    def self.index_infrequently_used(index)
      index.scans.to_f / index.table.sequential_scans < 0.25
    end
  end
end