class EasyTimeEntryBaseQuery < EasyQuery

  def self.permission_view_entities
    :view_time_entries
  end

  def query_after_initialize
    super
    self.groups_opened = false if self.new_record?

    self.display_project_column_if_project_missing = false
  end

  def default_additional_statement
    self.project.project_condition(Setting.display_subprojects_issues?) if self.project
  end

  def available_filters
    return @available_filters unless @available_filters.blank?

    group = l(:label_filter_group_easy_time_entry_query)
    group_issue = l(:label_filter_group_easy_issue_query)

    @available_filters = {
        'spent_on' => {:type => :date_period, :order => 1, :group => group},
        'activity_id' => {:type => :list, :order => 10, :values => (project ? Proc.new { project.activities.collect { |i| [i.name, i.id.to_s] } } : Proc.new { TimeEntryActivity.shared.sorted.collect { |i| [i.name, i.id.to_s] } }), :group => group},
        'tracker_id' => {:type => :list, :order => 11, :values => (project ? Proc.new { project.trackers.collect { |t| [t.name, t.id.to_s] } } : Proc.new { Tracker.all.collect { |t| [t.name, t.id.to_s] } }), :group => group},
        'created_on' => {:type => :date_period, :order => 12, :group => group},
        'updated_on' => {:type => :date_period, :order => 17, :group => group},
        'issue_created_on' => {:type => :date_period, :order => 13, :group => group_issue},
        'issue_updated_on' => {:type => :date_period, :order => 14, :group => group_issue},
        'issue_closed_on' => {:type => :date_period, :order => 15, :group => group_issue},
        'issue_open_duration_in_hours' => {:type => :float, :order => 16, :group => group_issue},
        'issue_assigned_to_id' => { :type => :list, :values => Proc.new { all_users_values(:include_me => true) }, :group => group_issue, :name => l(:field_assigned_to)},
        'xentity_type' => {
            :type => :list,
            :values => (
            proc { TimeEntry.available_entity_types.map{|t| [l('label_'+t.underscore), t]}.sort_by{|m| m[0] } }
            ), :order => 17, :group => group, :name => l(:field_entity_type)
        }
    }

    if project
      versions = Proc.new {
        Version.values_for_select_with_project(project.shared_versions)
      }
    else
      versions = Proc.new {
        Version.values_for_select_with_project(Version.visible.where(projects: { easy_is_easy_template: false }).joins(:project))
      }
    end

    @available_filters['fixed_version_id'] = {:type => :list_optional, :order => 17, :values => versions, :group => group_issue}

    if TimeEntry.easy_locking_enabled?
      @available_filters['easy_locked'] = {:type => :boolean, :order => 20, :group => group, :name => l(:field_locked)}
      @available_filters['easy_locked_at'] = {:type => :date_period, :order => 21, :group => group, :name => l(:field_locked_at)}
      @available_filters['easy_locked_by_id'] = {:type => :list, :order => 22, :name => l(:field_locked_by), :values => Proc.new do
        all_users_values(:include_me => true)
      end, :group => group
      }
    end
    unless project
      @available_filters['xproject_id'] = {:type => :list_optional, :order => 5, :values => Proc.new { self.projects_for_select(Project.visible(User.current, :include_archived => true).non_templates.sorted) }, :group => group, :name => l(:field_project)}
      @available_filters['xproject_name'] = {:type => :text, :order => 6, :group => group, :name => l(:label_project_name)}
      @available_filters['parent_id'] = {:type => :list, :order => 7, :values => Proc.new { self.projects_for_select(Project.visible(User.current, :include_archived => true).non_templates.sorted) }, :group => group}
      @available_filters['project_root_id'] = {:type => :list, :order => 8, :values => Proc.new { self.projects_for_select(Project.visible(User.current, :include_archived => true).non_templates.sorted.roots.select([:id, :name, :easy_level, :parent_id]).to_a, false) }, :group => group}
    end
    if User.current.allowed_to_globally_view_all_time_entries?
      @available_filters['user_id'] = {:type => :list, :order => 15, :values => Proc.new { all_users_values(:include_me => true, :include_inferiors => true) }, :group => group, :includes => [:user]}
      @available_filters['user_group'] = {:type => :list, :order => 15, name: l(:label_group), :values => Proc.new do
        principal_values = []
        principal_values.concat(all_groups_values)
        principal_values
      end, :group => group #, :includes => [:user]
      }
    end

    @available_filters['user_roles'] = {:type => :list, :order => 9, :values => Proc.new { Role.givable.collect { |r| [r.name, r.id.to_s] } }, :group => group}

    @available_filters['updated_on'] = {:type => :date_period, :order => 23, :group => group, :name => l(:field_updated_on)}

    add_custom_fields_filters(TimeEntryCustomField)
    add_custom_fields_filters(UserCustomField.visible.sorted, :user)

    add_associations_custom_fields_filters :project
    add_custom_fields_filters(issue_custom_fields, :issue)

    @available_filters
  end

  def available_columns
    unless @available_columns_added
      group = l(:label_filter_group_easy_time_entry_query)
      group_issue = l(:label_filter_group_easy_issue_query)
      group_user = l(:label_user_plural)
      @available_columns = [
          EasyQueryDateColumn.new(:spent_on, :sortable => "#{TimeEntry.table_name}.spent_on", :groupable => true, :group => group),
          EasyQueryColumn.new(:tweek, :sortable => "#{TimeEntry.table_name}.tweek", :groupable => true, :group => group),
          EasyQueryColumn.new(:tmonth, :sortable => "#{TimeEntry.table_name}.tmonth", :groupable => true, :group => group),
          EasyQueryColumn.new(:tyear, :sortable => "#{TimeEntry.table_name}.tyear", :groupable => true, :group => group),
          EasyQueryColumn.new(:user, :groupable => "#{TimeEntry.table_name}.user_id", :sortable => lambda { User.fields_for_order_statement }, :includes => [:user => :easy_avatar], :group => group_user),
          EasyQueryColumn.new(:activity, :groupable => true, :sortable => "#{TimeEntryActivity.table_name}.name", :group => group),
          EasyQueryColumn.new(:issue, :sortable => "#{Issue.table_name}.subject", :groupable => "#{Issue.table_name}.id", :preload => [:issue => [:priority, :status, :tracker, :project, :assigned_to]], :group => group_issue),
          EasyQueryColumn.new(:tracker, :sortable => "#{Tracker.table_name}.position", :groupable => "#{Tracker.table_name}.id", :includes => [{:issue => :tracker}], :group => group),
          EasyQueryColumn.new(:fixed_version, :sortable => lambda { Version.fields_for_order_statement }, :includes => [:issue => :fixed_version], :group => group),
          EasyQueryColumn.new(:project, :sortable => "#{Project.table_name}.name", :groupable => "#{Project.table_name}.id", :group => group),
          EasyQueryColumn.new(:parent_project, :sortable => 'join_parent.name', :groupable => "join_parent.id", :joins => joins_for_parent_project_field, :group => group),
          EasyQueryColumn.new(:project_root,
                              :sortable => "(SELECT p.id FROM #{Project.table_name} p WHERE p.lft <= #{Project.table_name}.lft AND p.rgt >= #{Project.table_name}.rgt AND p.parent_id IS NULL)",
                              :sumable_sql => "(SELECT p.id FROM #{Project.table_name} p WHERE p.lft <= #{Project.table_name}.lft AND p.rgt >= #{Project.table_name}.rgt AND p.parent_id IS NULL)",
                              :groupable => true, :group => group),
          # :sumable_sql => "(SELECT r.role_id FROM member_roles r INNER JOIN members m ON m.id = r.member_id WHERE m.project_id = #{TimeEntry.table_name}.project_id AND m.user_id = #{TimeEntry.table_name}.user_id)"
          EasyQueryColumn.new(:user_roles, :groupable => false, :group => group),
          EasyQueryColumn.new(:comments, :group => group),
          EasyQueryColumn.new(:hours, :sortable => "#{TimeEntry.table_name}.hours", :sumable => :bottom, :caption => 'label_spent_time', :group => group),
          EasyQueryColumn.new(:easy_range_from, :group => group),
          EasyQueryColumn.new(:easy_range_to, :group => group),
          EasyQueryDateColumn.new(:created_on, :sortable => "#{TimeEntry.table_name}.created_on", :group => group),
          EasyQueryDateColumn.new(:updated_on, :sortable => "#{TimeEntry.table_name}.updated_on", :group => group),
          EasyQueryColumn.new(:issue_assigned_to, :sortable => lambda { User.fields_for_order_statement('join_assignee') }, :groupable => true, :sumable_sql => "#{Issue.table_name}.assigned_to_id", :preload => [:project => [:enabled_modules], :issue => [:assigned_to => Setting.gravatar_enabled? ? :email_addresses : :easy_avatar]], :caption => :field_assigned_to, :group => group_issue),
          EasyQueryDateColumn.new(:issue_created_on, :sortable => "#{Issue.table_name}.created_on", :group => group_issue),
          EasyQueryDateColumn.new(:issue_updated_on, :sortable => "#{Issue.table_name}.updated_on", :group => group_issue),
          EasyQueryDateColumn.new(:issue_closed_on, :sortable => "#{Issue.table_name}.closed_on", :group => group_issue),
          EasyQueryColumn.new(:issue_open_duration_in_hours, :sortable => self.sql_time_diff("#{Issue.table_name}.created_on", "#{Issue.table_name}.closed_on"), :group => group_issue),
          EasyQueryColumn.new(:entity_type, :groupable => true, :sortable => "#{TimeEntry.table_name}.entity_type", :caption => :field_entity_type, :group => group),
          EasyQueryColumn.new(:entity, :caption => :field_entity, :group => group, :preload => [:entity]),
          EasyQueryColumn.new(:category, :sortable => "#{IssueCategory.table_name}.name", :groupable => "#{IssueCategory.table_name}.id",  :includes => [{:issue => :category}], :group => group_issue)
      ]
      if TimeEntry.easy_locking_enabled?
        @available_columns << EasyQueryColumn.new(:easy_locked, :groupable => true, :sortable => "#{TimeEntry.table_name}.easy_locked", :caption => :field_locked, :group => group)
        @available_columns << EasyQueryDateColumn.new(:easy_locked_at, :sortable => "#{TimeEntry.table_name}.easy_locked_at", :caption => :field_locked_at, :group => group)
        @available_columns << EasyQueryColumn.new(:easy_locked_by, :sortable => lambda { User.fields_for_order_statement('locked_by_user') }, :caption => :field_locked_by, :group => group, :preload => [:easy_locked_by])
      end
      if User.current.allowed_to?(:view_estimated_hours, project, {:global => true})
        @available_columns << EasyQueryColumn.new(:estimated_hours, :sortable => "#{Issue.table_name}.estimated_hours", :sumable => :bottom, :sumable_sql => "(SELECT i.estimated_hours FROM #{Issue.table_name} i WHERE i.id = #{TimeEntry.table_name}.issue_id)", :sumable_options => {model: 'Issue', column: 'estimated_hours', distinct_columns: [["#{Issue.table_name}.id", :issue]]}, :group => group)
      end
      @available_columns << EasyQueryColumn.new(:issue_id, :sortable => "#{Issue.table_name}.id", :group => group_issue) if EasySetting.value('show_issue_id', project)

      @available_columns.concat(TimeEntryCustomField.visible.sorted.collect { |cf| EasyQueryCustomFieldColumn.new(cf) })
      @available_columns.concat(issue_custom_fields.visible.sorted.to_a.collect { |cf| EasyQueryCustomFieldColumn.new(cf, group: l(:xml_data_issue_custom_fields), assoc: :issue) })
      @available_columns.concat(ProjectCustomField.visible.sorted.to_a.collect { |cf| EasyQueryCustomFieldColumn.new(cf, group: l(:label_project_custom_fields),assoc: :project) })
      @available_columns.concat(UserCustomField.visible.sorted.to_a.collect { |cf| EasyQueryCustomFieldColumn.new(cf, group: l(:label_user_custom_fields), assoc: :user) })

      @available_columns_added = true
    end
    @available_columns
  end

  def joins_for_issue_assignee
    issue = Issue.arel_table
    assignee = User.arel_table.alias('join_assignee')
    join_assignees = issue.create_on(issue[:assigned_to_id].eq(assignee[:id]))

    issue.create_join(assignee, join_assignees, Arel::Nodes::OuterJoin).to_sql
  end

  def columns_with_me
    super + ['user_id', 'issue_assigned_to_id', 'easy_locked_by_id']
  end

  def entity
    TimeEntry
  end

  def entity_scope
    @entity_scope ||= TimeEntry.non_templates.visible_with_archived
  end

  def self.chart_support?
    true
  end

  def default_find_include
    [:project, :user, :issue, :activity]
  end

  def default_sort_criteria
    @default_sort_criteria ||= super.presence || ['spent_on desc']
  end

  def additional_group_attributes(group, attributes, options={})
    @total_hours ||= self.entity_sum("#{TimeEntry.table_name}.hours")
    @column_for_additional_attributes ||= available_columns.detect { |col| col.name == :hours }
    sum = attributes[:sums][:bottom][@column_for_additional_attributes]
    attributes[:percent] = ((sum / @total_hours) * 100.0).round(2) if sum && !@total_hours.zero?
  end

  def add_additional_order_statement_joins(order_options)
    joins = []
    if order_options
      if order_options.include?('join_parent') && col = available_columns.detect { |col| (col.name == :parent_project) }
        joins << col.joins
      end
      if order_options.include?('locked_by_user')
        joins << "LEFT OUTER JOIN #{User.table_name} locked_by_user ON locked_by_user.id = #{TimeEntry.table_name}.easy_locked_by_id"
      end
      if order_options.include?('join_assignee') && col = available_columns.detect { |col| (col.name == :issue_assigned_to) }
        joins << joins_for_issue_assignee
      end
    end
    joins
  end

  def add_custom_fields_filters(scope, assoc = nil)
    super unless scope.name == 'TimeEntryCustomField'

    scope.where(:is_filter => true).sorted.each do |field|
      add_custom_field_filter(field, assoc)
    end
  end

  def issue_custom_fields
    if project
      project.all_issue_custom_fields
    else
      IssueCustomField.where(:is_for_all => true)
    end
  end

  def get_custom_sql_for_field(field, operator, value)
    case field
      when 'activity_id'
        db_table = TimeEntry.table_name
        db_field = 'activity_id'
        sql = "#{db_table}.activity_id #{ operator == '=' ? 'IN' : 'NOT IN' } (SELECT #{TimeEntryActivity.table_name}.id FROM #{TimeEntryActivity.table_name} WHERE "
        sql << sql_for_field(field, '=', value, TimeEntryActivity.table_name, 'parent_id')
        sql << ' OR '
        sql << sql_for_field(field, '=', value, db_table, db_field) + ')'
        return sql
      when 'user_roles'
        v = value.is_a?(Array) ? value.join(',') : value
        o = (operator == '=') ? 'IN' : 'NOT IN'
        sql = "EXISTS (SELECT r.id FROM member_roles r INNER JOIN members m ON m.id = r.member_id WHERE m.user_id = #{User.table_name}.id AND m.project_id = #{TimeEntry.table_name}.project_id AND r.role_id #{o} (#{v}))"
        return sql
      when 'user_group'
        o = (operator == '=') ? '' : 'NOT'
        return "#{o} 0 = 1" if value.blank?
        v = value.is_a?(Array) ? value.join(',') : value
        sql = "#{o} EXISTS (SELECT r.user_id FROM groups_users r WHERE #{User.table_name}.id = r.user_id AND group_id IN (#{v}))"
      when 'project_root_id'
        db_table = TimeEntry.arel_table
        db_field = 'project_id'
        v = Project.select('children.id').where(:id => value).joins('INNER JOIN projects as children ON children.lft >= projects.lft AND children.rgt <= projects.rgt').where(Project.allowed_to_condition(User.current, :view_time_entries, {:table_name => 'children', :include_archived => true})).to_sql

        if operator == '='
          db_table[db_field.to_sym].in(Arel.sql(v)).to_sql
        else
          db_table[db_field.to_sym].not_in(Arel.sql(v)).to_sql
        end
      when 'parent_id'
        ids = Array(values_for('parent_id')).map(&:to_i)
        return '' if ids.blank?
        op_not = (operator_for('parent_id') == '!')

        return "#{Project.table_name}.id #{op_not ? 'NOT IN' : 'IN'} (SELECT p_parent_id.id FROM #{Project.table_name} p_parent_id WHERE p_parent_id.parent_id IN (#{ids.join(',')}))"
      when 'issue_created_on', 'issue_updated_on', 'issue_closed_on', 'created_on'
        if field.start_with?('issue_')
          db_table = Issue.table_name
          db_field = field.sub('issue_', '')
        else
          db_table = TimeEntry.table_name
          db_field = field
        end

        if operator =~ /date_period_([12])/
          if $1 == '1' && value[:period].to_sym == :all
            '(1=1)'
          else
            period_dates = self.get_date_range($1, value[:period], value[:from], value[:to], value[:period_days])
            self.date_clause(db_table, db_field, (period_dates[:from].nil? ? nil : period_dates[:from].beginning_of_day), (period_dates[:to].nil? ? nil : period_dates[:to].end_of_day)).presence || '(1=1)'
          end
        else
          '(1=1)'
        end
    end
  end

  def sql_for_xproject_id_field(field, operator, v)
    db_table = self.entity.table_name
    db_field = 'project_id'
    returned_sql_for_field = self.sql_for_field(db_field, operator, v, db_table, db_field)
    return ('(' + returned_sql_for_field + ')') unless returned_sql_for_field.blank?
  end

  def sql_for_xproject_name_field(field, operator, v)
    db_table = Project.table_name
    db_field = 'name'
    returned_sql_for_field = self.sql_for_field(db_field, operator, v, db_table, db_field)
    return ('(' + returned_sql_for_field + ')') unless returned_sql_for_field.blank?
  end

  def sql_for_tracker_id_field(field, operator, v)
    db_table = Issue.table_name
    db_field = field
    returned_sql_for_field = self.sql_for_field(field, operator, v, db_table, db_field)
    return ('(' + returned_sql_for_field + ')') unless returned_sql_for_field.blank?
  end

  def sql_for_fixed_version_id_field(field, operator, v)
    db_table = Issue.table_name
    db_field = field
    returned_sql_for_field = self.sql_for_field(field, operator, v, db_table, db_field)
    return ('(' + returned_sql_for_field + ')') unless returned_sql_for_field.blank?
  end

  def sql_for_issue_open_duration_in_hours_field(field, operator, value)
    sql_for_field(field, operator, value, nil, self.sql_time_diff("#{Issue.table_name}.created_on", "#{Issue.table_name}.closed_on"))
  end

  def sql_for_issue_assigned_to_id_field(field, operator, value)
    sql_for_field(field, operator, value, Issue.table_name, 'assigned_to_id')
  end

  def sql_for_xentity_type_field(field, operator, value)
    db_table = self.entity.table_name
    db_field = 'entity_type'
    returned_sql_for_field = self.sql_for_field(field, operator, value, db_table, db_field)
    return ('(' + returned_sql_for_field + ')') unless returned_sql_for_field.blank?
  end
end
