class EasyProjectQuery < EasyQuery

  def self.entity_css_classes(project, options={})
    project.css_classes(project.easy_level, options)
  end

  def self.permission_view_entities
    :view_project
  end

  def entity_easy_query_path(options)
    options = options.dup
    polymorphic_path(self.entity, options)
  end

  def query_after_initialize
    super
    self.display_filter_sort_on_edit = true
  end

  def filter_groups_ordering
    super + [
      EasyQuery.column_filter_group_name(nil)
    ]
  end

  def column_groups_ordering
    super + [
      EasyQuery.column_filter_group_name(nil),
      l(:label_filter_group_easy_time_entry_query),
      l(:label_user_plural)
    ]
  end

  def initialize_available_filters
    group = l('label_filter_group_easy_project_query')

    if !self.project && self.entity == Project && self.class.name != 'EasyProjectQuery'
      add_available_filter 'project_id', {
        :type => :list_optional, :order => 1, :values => Proc.new { self.all_projects_values(:include_mine => true) },
        :group => group
      }
    end

    add_available_filter('parent_id', {:type => :list, :label => :field_is_subproject_of, :order => 1, :values => Proc.new { self.all_projects_parents_values }, :group => group})
    add_available_filter('role_id', {
      :type => :list,
      :order => 6,
      :values => Proc.new { Role.all.collect { |r| [r.name, r.id.to_s] } },
      :group => group
    })
    add_available_filter('name', {:type => :text, :order => 8, :group => group})
    add_available_filter('is_planned', {
      :type => :boolean,
      :order => 9,
      :group => group
    })
    add_available_filter('is_closed', {
      :type => :boolean,
      :order => 10,
      :group => group,
      :name => l(:field_is_project_closed)
    })
    add_available_filter('is_public', {
      :type => :boolean,
      :order => 11,
      :group => group
    })
    add_available_filter('created_on', {:type => :date_period, :order => 12, :group => group})
    add_available_filter('updated_on', {:type => :date_period, :order => 13, :group => group})
    add_available_filter('not_updated_on', {:type => :date_period, :time_column => true, :order => 14, :name => l(:label_not_updated_on), :group => group})
    add_available_filter('easy_start_date', {:type => :date_period, :order => 15, :group => group}) unless EasySetting.value('project_calculate_start_date')
    add_available_filter('easy_due_date', {:type => :date_period, :order => 16, :group => group}) unless EasySetting.value('project_calculate_due_date')
    add_available_filter('favorited', {:type => :boolean, :order => 17, :group => group})

    add_custom_fields_filters(ProjectCustomField)

    add_available_filter('member_id', {
      :type => :list,
      :order => 5,
      :values =>
        Proc.new do
          user_values = []
          user_values << ["<< #{l(:label_me)} >>", 'me'] if User.current.logged?
          user_values.concat(User.active.non_system_flag.sorted.collect { |s| [s.name, s.id.to_s] })
          user_values
        end,
      :group => group
    })

    add_available_filter 'author_id', {:type => :list, :order => 18, :values => Proc.new do
      all_users_values(:include_me => true)
    end,
                                       :group => group
    }

    add_available_filter 'easy_indicator', {:type => :list, :order => 19, :values => Proc.new do
      easy_indicator_values = []
      easy_indicator_values << [l(:ok, scope: :easy_indicator), Project::EASY_INDICATOR_OK.to_s]
      easy_indicator_values << [l(:warning, scope: :easy_indicator), Project::EASY_INDICATOR_WARNING.to_s]
      easy_indicator_values << [l(:alert, scope: :easy_indicator), Project::EASY_INDICATOR_ALERT.to_s]
      easy_indicator_values
    end,
                                            :group => group
    }

    add_available_filter 'tags', {:type => :list, :values => Proc.new { all_tags_values }, :includes => [:tags], :label => :label_easy_tags, :group => group}


    add_available_filter 'easy_priority_id', {type: :list,
                                              group: group,
                                              label: :field_priority,
                                              values: proc { EasyProjectPriority.active.sorted.pluck(:name, :id) }
    }
  end

  def initialize_available_columns
    sortable_due_date = EasySetting.value('project_calculate_due_date') ? nil :"#{Project.table_name}.easy_due_date"
    sortable_star_date = EasySetting.value('project_calculate_start_date') ? nil : "#{Project.table_name}.easy_start_date"

    group = l(:label_filter_group_easy_project_query)

    @available_columns ||= []
    @available_columns.concat([
                                EasyQueryColumn.new(:name, :sortable => "#{Project.table_name}.name", :group => group),
                                EasyQueryColumn.new(:parent, :group => group, :preload => [:parent]),
                                EasyQueryColumn.new(:root, :group => group),
                                EasyQueryColumn.new(:description, :sortable => "#{Project.table_name}.description", :group => group, :inline => true),
                                EasyQueryColumn.new(:status, :group => group),
                                EasyQueryDateColumn.new(:start_date, group: group, sortable: sortable_star_date),
                                EasyQueryDateColumn.new(:due_date, group: group, sortable: sortable_due_date),
                                EasyQueryDateColumn.new(:created_on, :sortable => "#{Project.table_name}.created_on", :default_order => 'desc', :group => group),
                                EasyQueryDateColumn.new(:updated_on, :sortable => "#{Project.table_name}.updated_on", :default_order => 'desc', :group => group),
                                EasyQueryColumn.new(:journal_comments, :inline => true, :caption => :field_project_journal_comments, :group => group),
                                EasyQueryColumn.new(:last_journal_comment, :inline => true, :caption => :field_project_last_journal_comment, :group => group),
                                EasyQueryColumn.new(:easy_indicator, :group => group),
                                EasyQueryColumn.new(:identifier, :sortable => "#{Project.table_name}.identifier", :group => group),
                                EasyQueryColumn.new(:completed_percent, :group => group),
                                EasyQueryColumn.new(:tags, :preload => [:tags], :caption => :label_easy_tags, :group => group),
                                EasyQueryColumn.new(:priority, caption: :field_priority, groupable: "#{Project.table_name}.easy_priority_id", preload: [:priority], sortable: 'easy_project_priority.position', group: group)
                              ])

    @available_columns << EasyQueryColumn.new(:id, :sortable => "#{Project.table_name}.id", :group => group) if EasySetting.value('project_display_identifiers')

    group = l('label_filter_group_easy_time_entry_query')
    if User.current.allowed_to?(:view_estimated_hours, project, {:global => true})
      @available_columns << EasyQueryColumn.new(:sum_estimated_hours, :caption => :field_estimated_hours, :numeric => true, :group => group)
      @available_columns << EasyQueryColumn.new(:total_sum_estimated_hours, :caption => :field_sum_estimated_hours, :numeric => true, :group => group)
    end

    if User.current.allowed_to?(:view_time_entries, project, {:global => true})
      @available_columns << EasyQueryColumn.new(:sum_of_timeentries, :numeric => true, :group => group)
      @available_columns << EasyQueryColumn.new(:remaining_timeentries, :numeric => true, :group => group)
      @available_columns << EasyQueryColumn.new(:total_remaining_timeentries, :group => group)
      @available_columns << EasyQueryColumn.new(:total_spent_hours,
                                                :default_order => 'desc',
                                                :caption => :label_total_spent_time, :group => group
      )
    end

    @available_columns.concat(ProjectCustomField.sorted.collect { |cf| EasyQueryCustomFieldColumn.new(cf) })

    group = l('label_user_plural')
    @available_columns << EasyQueryColumn.new(:author, :groupable => true, :sortable => lambda { User.fields_for_order_statement('authors') }, :group => group, :preload => [{:author => :easy_avatar}])
    @available_columns << EasyQueryColumn.new(:users, :caption => :field_member, :group => group)
  end

  def additional_statement
    unless @additional_statement_added
      @additional_statement = super
      @additional_statement << ' AND ' unless @additional_statement.blank?
      @additional_statement << Project.arel_table[:easy_is_easy_template].eq(false).to_sql
      @additional_statement_added = true
    end
    @additional_statement
  end

  def searchable_columns
    return ["#{Project.table_name}.name"]
  end

  def calendar_support?
    true
  end

  def self.chart_support?
    true
  end

  def entity
    Project
  end

  def columns_with_me
    super + ['member_id']
  end

  def extended_period_options
    {
      :extended_options => [:to_today],
      :option_limit => {
        :is_null => ['easy_due_date', 'easy_start_date'],
        :is_not_null => ['easy_due_date', 'easy_start_date'],
        :after_due_date => ['easy_due_date'],
        :next_week => ['easy_due_date'],
        :tomorrow => ['easy_due_date'],
        :next_5_days => ['easy_due_date'],
        :next_7_days => ['easy_due_date'],
        :next_10_days => ['easy_due_date'],
        :next_30_days => ['easy_due_date'],
        :next_90_days => ['easy_due_date'],
        :next_month => ['easy_due_date'],
        :next_year => ['easy_due_date']
      },
      :field_disabled_options => {
        'not_updated_on' => [:is_null, :is_not_null]
      }
    }
  end

  def default_sort_criteria
    @default_sort_criteria ||= super.presence || [['lft', 'asc']]
  end

  def default_find_preload
    preloads = [:enabled_modules]
    preloads << :project_custom_fields if has_custom_field_column?
    preloads
  end

  def sortable_columns
    {'lft' => "#{Project.table_name}.lft"}.merge(super)
  end

  def roots(options={})
    projects = Project.arel_table
    root_statement = statement
    children = Arel::Table.new(:projects, :as => :children)
    children_statement = root_statement.gsub(/^projects\.|(?<=[^_])projects\./, 'children.')
    children_statement.gsub!(/FROM projects (AS)?/i) { |m|
      if $1
        'FROM projects AS'
      else
        'FROM projects AS children '
      end
    }

    join_sources = projects.join(children, Arel::Nodes::OuterJoin).on(
      projects[:lft].lteq(children[:lft]).and(
        children[:parent_id].not_eq(nil).and(
          projects[:rgt].gteq(children[:rgt])
        )
      )
    ).join_sources

    root_info = merge_scope(Project, options)
      .select('projects.id AS root_id, Count(children.id) AS children_count')
      .joins(join_sources)
      .where("( (#{root_statement}) OR (#{children_statement}) ) AND projects.parent_id IS NULL")
      .reorder(:lft)
      .group('projects.id')

    root_hash = Hash[root_info.map { |r| [r.root_id.to_i, r.children_count.to_i] }]
    return root_hash, Project.where(:id => root_hash.keys).reorder(:lft)
  end

  def find_projects_for_root(root_id = nil, options = {})
    projects = children_scope(root_id).
      select(Project.arel_table[Arel.star]).select("MIN(children.lft) - #{entity_table_name}.lft AS diff").
      select("(COUNT(children.id) - CASE WHEN MIN(children.lft) > #{entity_table_name}.lft THEN 0 ELSE 1 END) AS visible_children").
      group(:id).limit(options[:limit]).offset(options[:offset]).order(options[:order])
    ids_with_children = projects.map{|i| [i.id, i.visible_children]}.to_h
    @projects_for_root_scope = new_entity_scope(projects)
    @projects_for_root_scope.to_a.each do |p|
      p.has_visible_children = ids_with_children[p.id] > 0
      p.nofilter = ' nofilter' if p.attributes['diff'].to_i > 0
    end
    @projects_for_root_scope
  end

  def children_scope(root_id = nil)
    @entity_table_name = 'children'
    children_statement = self.statement
    @entity_table_name = nil
    Project.joins('INNER JOIN projects as children ON children.lft >= projects.lft AND children.rgt <= projects.rgt').
      where(children_statement).where(Project.visible_condition(User.current, table_name: 'children')).where(parent_id: root_id)
  end

  def only_favorited?
    filters.include?('favorited')
  end

  def display_as_tree?
    if !(sort_criteria.blank? || sort_criteria == [['lft', 'asc']]) || grouped? || free_search_question
      false
    else
      true
    end
  end

  def generate_custom_formatting_entities_hash
    return if self.custom_formatting.blank?
    self.custom_formatting_entities = {}

    self.custom_formatting.each do |scheme, filters|
      ids_for_scheme = []
      ids_for_scheme |= self.new_entity_scope.where(self.statement(filters)).pluck(:id)
      ids_for_scheme |= @projects_for_root_scope.where(self.statement(filters)).pluck(:id) if @projects_for_root_scope
      ids_for_scheme.each do |project_id|
        self.custom_formatting_entities[project_id] = scheme
      end
    end
  end

  def sql_for_not_updated_on_field(field, operator, value)
    db_field = 'updated_on'
    db_table = entity_table_name

    if operator =~ /date_period_([12])/
      if $1 == '1' && value[:period].to_sym == :all
        "#{db_table}.#{db_field} = #{db_table}.created_on"
      else
        period_dates = self.get_date_range($1, value[:period], value[:from], value[:to], value[:period_days])
        self.reversed_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))
      end
    else
      nil
    end
  end

  def statement_skip_fields
    ['member_id', 'role_id', 'parent_id']
  end

  def add_statement_sql_before_filters
    my_fields = ['member_id', 'role_id', 'parent_id'] & filters.keys

    if my_fields.present?
      if my_fields.include?('parent_id')
        parent_id_where = Array.new
        op_not = (operator_for('parent_id') == '!')
        Project.where(:id => values_for('parent_id')).each do |p|
          if op_not
            parent_id_where << "#{entity_table_name}.id NOT IN (SELECT p_parent_id.id FROM #{Project.table_name} AS p_parent_id WHERE p_parent_id.lft > #{p.lft} AND p_parent_id.rgt < #{p.rgt})"
          else
            parent_id_where << "#{entity_table_name}.id IN (SELECT p_parent_id.id FROM #{Project.table_name} AS p_parent_id WHERE p_parent_id.lft > #{p.lft} AND p_parent_id.rgt < #{p.rgt})"
          end
        end

        if parent_id_where.any?
          if op_not
            sql_parent_id = parent_id_where.join(' AND ')
          else
            sql_parent_id = parent_id_where.join(' OR ')
          end
          sql_parent_id = "(#{sql_parent_id})"
        end
      end

      if my_fields.include?('member_id')
        mv = values_for('member_id').dup
        mv.push(User.current.logged? ? User.current.id.to_s : '0') if mv.delete('me')
        sql_member_id = "#{entity_table_name}.id #{operator_for('member_id') == '=' ? 'IN' : 'NOT IN'} (SELECT DISTINCT pm1.project_id FROM #{Member.table_name} pm1 WHERE "
        sql_member_id << sql_for_field('member_id', '=', mv, 'pm1', 'user_id', true)
        sql_member_id << ')'

        if my_fields.include?('role_id')
          sql_member_id << " AND #{entity_table_name}.id #{operator_for('role_id') == '=' ? 'IN' : 'NOT IN'} (SELECT DISTINCT pm1.project_id FROM #{Member.table_name} pm1 INNER JOIN #{MemberRole.table_name} pmr1 ON pmr1.member_id = pm1.id WHERE "
          sql_member_id << sql_for_field('member_id', '=', mv, 'pm1', 'user_id', true)
          sql_member_id << (' AND ' + sql_for_field('role_id', '=', values_for('role_id').dup, 'pmr1', 'role_id', true))
          sql_member_id << ')'
        end
      elsif my_fields.include?('role_id')
        sql_role_id = "#{entity_table_name}.id #{operator_for('role_id') == '=' ? 'IN' : 'NOT IN'} (SELECT DISTINCT pm1.project_id FROM #{Member.table_name} pm1 INNER JOIN #{MemberRole.table_name} pmr1 ON pmr1.member_id = pm1.id WHERE "
        sql_role_id << sql_for_field('role_id', '=', values_for('role_id').dup, 'pmr1', 'role_id', true)
        sql_role_id << ')'
      end

      sql = [sql_parent_id, sql_member_id, sql_role_id].compact.join(' AND ')

      return sql
    end
  end

  def sql_for_is_planned_field(field, operator, value)
    o = value.first == '1' ? '=' : '<>'
    "(#{entity_table_name}.status #{o} #{Project::STATUS_PLANNED})"
  end

  def sql_for_is_closed_field(field, operator, value)
    o = value.first == '1' ? '=' : '<>'
    "(#{entity_table_name}.status #{o} #{Project::STATUS_CLOSED})"
  end

  def sql_for_project_id_field(field, operator, value)
    sql_for_field(field, operator, value, entity_table_name, 'id')
  end

  def sql_for_easy_indicator_field(field, operator, value)
    sql = []
    op = (operator == '!') ? 'NOT ' : ''
    return ("#{op}(1=0)") unless value.present?
    ids_in_warning_scope = Project.joins(issues: :status).where.not(issues: {due_date: nil}, issue_statuses: {is_closed: true}).where("#{Issue.table_name}.due_date < ?", Date.today)
    ids_in_alert_scope = Project.where.not(easy_due_date: nil).where("#{Project.table_name}.easy_due_date < ?", Date.today)

    if Setting.display_subprojects_issues?
      ids_in_warning = ids_in_warning_scope.joins('INNER JOIN projects as ancestors ON ancestors.lft <= projects.lft AND ancestors.rgt >= projects.rgt').uniq.pluck('ancestors.id')
      ids_in_alert = ids_in_alert_scope.joins('INNER JOIN projects as ancestors ON ancestors.lft <= projects.lft AND ancestors.rgt >= projects.rgt').uniq.pluck('ancestors.id')
    else
      ids_in_warning = ids_in_warning_scope.uniq.pluck(:id)
      ids_in_alert = ids_in_alert_scope.uniq.pluck(:id)
    end

    ids_not_ok = ids_in_alert + ids_in_warning
    ids_in_warning_only = ids_in_warning - ids_in_alert

    if value.include?(Project::EASY_INDICATOR_OK.to_s)
      is_easy_due_date_nil_ok = EasySetting.value(:default_project_indicator).to_i == Project::EASY_INDICATOR_OK
      due_date_condition = (is_easy_due_date_nil_ok || op.present?) ? '' : "#{entity_table_name}.easy_due_date IS NOT NULL AND "
      planned_condition = "#{op.blank? ? ' OR' : ' AND'} #{entity_table_name}.status #{op}IN (#{Project::STATUS_PLANNED})"
      sql << due_date_condition + (ids_not_ok.any? ? "(#{entity_table_name}.id #{'NOT ' if op.blank?}IN (#{ids_not_ok.join(', ')}))" : '(1=1)') + planned_condition
    end
    if value.include?(Project::EASY_INDICATOR_WARNING.to_s) && ids_in_warning_only.any?
      sql << "(#{entity_table_name}.id #{op}IN (#{ids_in_warning_only.join(', ')}))"
    end
    if value.include?(Project::EASY_INDICATOR_ALERT.to_s) && ids_in_alert.any?
      sql << "(#{entity_table_name}.id #{op}IN (#{ids_in_alert.join(', ')}))"
    end

    sql.any? ? sql.join(' OR ') : '1=0'
  end

  def joins_for_order_statement(order_options, return_type = :sql, uniq = false)
    joins = []

    if order_options
      if order_options.include?('authors')
        joins << "LEFT OUTER JOIN #{User.table_name} authors ON authors.id = #{entity_table_name}.author_id"
      end
      if order_options.include?('easy_project_priority')
        joins << "LEFT OUTER JOIN #{EasyProjectPriority.table_name} easy_project_priority ON easy_project_priority.id = #{entity_table_name}.easy_priority_id"
      end
      joins.concat(super(order_options, :array, uniq))
    end

    case return_type
    when :sql
      joins.any? ? joins.join(' ') : nil
    when :array
      joins
    else
      raise ArgumentError, 'return_type has to be either :sql or :array'
    end
  end

end
