# encoding: UTF-8
###
# A task filter is used to find tasks matching the filters set up
# in session.
###
class TaskFilter < ActiveRecord::Base
  # column `name` is of type `VARCHAR(255)`.
  # Note that MySQL 5.x+ and PostgreSQL both track length of "characters" and not "bytes"
  MAXIMUM_NAME_LENGTH = 255

  belongs_to :user
  belongs_to :company
  has_many(:qualifiers, :dependent => :destroy, :class_name => 'TaskFilterQualifier')
  has_many :keywords, :dependent => :destroy
  has_many :task_filter_users, :dependent => :delete_all

  accepts_nested_attributes_for :keywords
  accepts_nested_attributes_for :qualifiers

  validates :user, :presence => true
  validates :name, :presence => true, :length => {:maximum => MAXIMUM_NAME_LENGTH}

  scope :shared, -> { where(:shared => true) }
  scope :visible, -> { where(:system => false, :recent_for_user_id => nil) }
  scope :recent_for, lambda { |user| where(:recent_for_user_id => user.id).order('id DESC') }

  before_create :set_company_from_user
  after_create :set_task_filter_status, :if => Proc.new { |x| x.recent_for_user_id.blank? && !x.system }

  # Returns the system filter for the given user. If none is found,
  # create and saves a new one and returns that.
  def self.system_filter(user)
    filter = user.task_filters.where(:system => true).first
    if filter.nil?
      filter = user.task_filters.build(:name => "System filter for #{ user }",
                                       :user_id => user.id, :system => true)
      filter.save!
    end

    return filter
  end

  # Returns an array of all tasks matching the conditions from this filter.
  # If extra_conditions is passed, that will be ANDed to the conditions
  # If limit is false, no limit will be set on the tasks returned (otherwise
  # a default limit will be applied)
  def tasks(extra_conditions = nil)
    return TaskRecord.all_accessed_by(user).where(conditions(extra_conditions)).includes(to_include).references(to_include).limit(500).uniq
  end

  # Returns an array of all tasks matching the conditions from this filter.
  def tasks_for_fullcalendar(parameters)
    tasks(parse_fullcalendar_params(parameters)).joins(:milestone)
  end

  def tasks_for_gantt(parameters)
    tasks.includes(:milestone).order('projects.name, milestones.name')
  end

  def projects_for_fullcalendar(parameters)
    projects = tasks(parse_fullcalendar_params(parameters)).includes(:project).collect { |t| t.project }.uniq
  end

  # Returns the count of tasks matching the conditions of this filter.
  # if extra_conditions is passed, that will be ANDed to the conditions
  def count(extra_conditions = nil)
    TaskRecord.all_accessed_by(user).includes(to_include).references(to_include).where(conditions(extra_conditions)).uniq.count
  end

  # Returns a count to display for this filter. The count represents the
  # number of tasks that look they need attention for the given user -
  # unassigned tasks and unread tasks are counted.
  # The value will be cached and re-used unless force_recount is passed.
  def display_count(user, force_recount = false)
    @display_count = nil if force_recount
    @display_count ||= count(unread_conditions(user, true))
  end

  # Returns an array of the conditions to use for a sql lookup
  # of tasks for this filter
  def conditions(extra_conditions = nil)
    time_qualifiers = qualifiers.select { |q| q.qualifiable_type == 'TimeRange' }
    status_qualifiers = qualifiers.select { |q| q.qualifiable_type == 'Status' }
    property_qualifiers = qualifiers.select { |q| q.qualifiable_type == 'PropertyValue' }
    customer_qualifiers = qualifiers.select { |q| q.qualifiable_type == 'Customer' }
    standard_qualifiers = (qualifiers - property_qualifiers - status_qualifiers -
        customer_qualifiers - time_qualifiers)

    res = conditions_for_standard_qualifiers(standard_qualifiers)
    res += conditions_for_property_qualifiers(property_qualifiers)
    res << conditions_for_status_qualifiers(status_qualifiers)
    res << conditions_for_customer_qualifiers(customer_qualifiers)
    res << conditions_for_time_qualifiers(time_qualifiers)
    res << conditions_for_keywords
    res << extra_conditions if extra_conditions
    res << unread_conditions(user) if unread_only?
    res << '(task_users.id is null)' if unassigned?

    res = res.select { |c| !c.blank? }
    res = res.join(' AND ')

    return res
  end

  def cache_key
    key = super

    if unread_only?
      # we can't cache the whole filter when unread_only set
      "#{ key }/Time.now.to_i/#{ user.id }/#{ rand }/"
    else
      last_task_update = user.company.tasks.where(conditions).joins(:task_users).includes(to_include).maximum(:updated_at)
      "#{ key }/#{ last_task_update.to_i }/#{ user.id }"
    end
  end

  def copy_from(filter)
    self.unread_only = filter.unread_only
    filter.qualifiers.each do |q|
      self.qualifiers.build(:task_filter_id => q.task_filter_id,
                            :qualifiable_type => q.qualifiable_type,
                            :qualifiable_id => q.qualifiable_id,
                            :qualifiable_column => q.qualifiable_column,
                            :reversed => q.reversed)
    end

    filter.keywords.each do |kw|
      # N.B Shouldn't have to pass in all these values, but it
      # doesn't work when we don't, so...
      self.keywords.build(:task_filter => self,
                          :company => filter.company,
                          :word => kw.word)
    end
  end

  def select_filter(filter)
    TaskFilter.transaction do
      self.qualifiers.all.delete_all
      self.keywords.all.delete_all
      self.copy_from(filter)
      self.save!
    end
  end

  def store_for(user)
    ActiveRecord::Base.transaction do
      if TaskFilter.recent_for(user).count >= 10
        TaskFilter.recent_for(user).last.destroy
      end
      filter=TaskFilter.new(:recent_for_user_id => user.id, :user => user, :company => self.company)
      filter.name= generate_name
      filter.name= self.name if filter.name.blank?
      filter.copy_from(self)
      filter.save!
    end
  end

  def update_filter(params)
    ActiveRecord::Base.transaction do
      self.keywords.all.delete_all
      self.qualifiers.all.delete_all
      self.unread_only = false
      self.attributes = params
      self.save!
    end
  end

  def show?(user)
    task_filter_users.where(:user_id => user.id).count != 0
  end

  private
  ###
  # This method generate filter name based on qualifiers and keywords
  # this name will include all projects, milestones, statuses, clients, users qualifiers in this order
  # then all keywords, then other qualifiers
  ###
  def generate_name
    counter = 0
    arr=[]
    types=['Project', 'Milestone', 'Status', 'Client', 'User']
    types.each do |type|
      qualifiers.select { |q| q.qualifiable_type == type }.each do |qualifier|
        arr<< (qualifier.reversed? ? 'not ' : '') + qualifier.qualifiable.to_s
      end
    end
    keywords.each do |kw|
      arr<< (kw.reversed? ? 'not ' : '') + kw.word;
    end
    qualifiers.select { |q| !types.include?(q.qualifiable_type) }.each do |qualifier|
      arr<< (qualifier.reversed? ? 'not ' : '') + qualifier.qualifiable.to_s
    end
    arr<< 'Unread only' if unread_only?
    return arr.join(', ').truncate(MAXIMUM_NAME_LENGTH)
  end

  def to_include
    to_include = [:project, :task_users]
    to_include = :task_owners if unassigned?

    to_include << :tags if qualifiers.for('Tag').any?
    to_include << :task_property_values if qualifiers.for('PropertyValue').any?
    to_include << :customers if qualifiers.for('Customer').any?

    return to_include
  end

  def set_company_from_user
    self.company = user.company
  end

  # Returns a conditions hash the will filter tasks based on the
  # given property value qualifiers
  def conditions_for_property_qualifiers(property_qualifiers)
    property_qualifiers = property_qualifiers.group_by { |qualifier| qualifier.reversed? }
    simple_conditions_for_property_qualifiers(property_qualifiers[false], false) + simple_conditions_for_property_qualifiers(property_qualifiers[true], true)
  end

  def simple_conditions_for_property_qualifiers(property_qualifiers, reverse)
    return [] if property_qualifiers.nil?
    name = 'task_property_values.property_value_id'
    grouped = property_qualifiers.group_by { |q| q.qualifiable.property }

    res = []
    grouped.each do |property, qualifiers|
      ids = qualifiers.map { |q| q.qualifiable.id }
      if reverse
        res << "(#{ name } NOT IN (#{ ids.join(', ') }) AND task_property_values.property_id = #{property.id})"
      else
        res << "(#{ name } IN (#{ ids.join(', ') }) AND task_property_values.property_id = #{property.id})"
      end
    end

    return res
  end


  # Returns an array of conditions that will filter tasks based on the
  # given standard qualifiers.
  # Standard qualifiers are things like project, milestone, user, where
  # a filter will OR the different users, but and between different types
  def conditions_for_standard_qualifiers(standard_qualifiers)
    standard_qualifiers = standard_qualifiers.group_by { |qualifier| qualifier.reversed? }
    simple_conditions_for_standard_qualifiers(standard_qualifiers[false])+ simple_conditions_for_standard_qualifiers(standard_qualifiers[true]).map { |sql| 'not ' + sql }
  end

  def simple_conditions_for_standard_qualifiers(standard_qualifiers)
    return [] if standard_qualifiers.nil?
    res = []

    grouped_conditions = standard_qualifiers.group_by { |q| q.qualifiable_type }
    grouped_conditions.each do |type, values|
      name = column_name_for(type)
      ids = values.map { |v| v.qualifiable_id }
      res << "#{ name } in (#{ ids.join(',') })"
    end

    return res
  end

  # Returns a string sql fragment that will limit tasks to
  # those that match the set keywords
  def conditions_for_keywords
    kws = keywords.group_by { |keyword| keyword.reversed? }
    compose_sql(simple_conditions_for_keywords(kws[false]), simple_conditions_for_keywords(kws[true]))
  end

  def simple_conditions_for_keywords(keywords_arg)
    return if keywords_arg.nil?
    sql = []
    params = []

    keywords_arg.each do |kw|
      str = 'lower(tasks.name) like ?'
      str += ' or lower(tasks.description) like ?'
      sql << "coalesce((#{str}), FALSE)"
      2.times { params << "%#{ kw.word.downcase }%" }
    end

    sql = sql.join(' or ')
    res = TaskFilter.send(:sanitize_sql_array, [sql] + params)
    return "(#{ res })" unless res.blank?
  end

  # Returns a sql string fragment that will limit tasks to only
  # status set by the status qualifiers.
  # Status qualifiers have to be handled especially until the
  # migration from an array in code to db backed statuses is complete
  def conditions_for_status_qualifiers(status_qualifiers)
    status_qualifiers = status_qualifiers.group_by { |qualifier| qualifier.reversed? }
    compose_sql(simple_conditions_for_status_qualifiers(status_qualifiers[false]), simple_conditions_for_status_qualifiers(status_qualifiers[true]))
  end

  def simple_conditions_for_status_qualifiers(status_qualifiers)
    return if status_qualifiers.nil?
    old_status_ids = []
    c = company || user.company

    status_qualifiers.each do |q|
      status = q.qualifiable
      old_status = c.statuses.index(status)
      old_status_ids << old_status
    end

    old_status_ids = old_status_ids.compact.join(',')
    return "tasks.status in (#{ old_status_ids })" unless old_status_ids.blank?
  end

  # Returns a sql string fragment that will limit tasks to only
  # those in a project belonging to customers, or linked directly
  # to the customer
  def conditions_for_customer_qualifiers(customer_qualifiers)
    customer_qualifiers = customer_qualifiers.group_by { |qualifier| qualifier.reversed? }
    compose_sql(simple_conditions_for_customer_qualifiers(customer_qualifiers[false]), simple_conditions_for_customer_qualifiers(customer_qualifiers[true]))
  end

  def simple_conditions_for_customer_qualifiers(customer_qualifiers)
    return if customer_qualifiers.nil?
    ids = customer_qualifiers.map { |q| q.qualifiable.id }
    ids = ids.join(',')

    unless ids.blank?
      res = "projects.customer_id in (#{ ids })"
      res += " or coalesce(task_customers.customer_id in (#{ ids }),0)"
      return "(#{ res })"
    end
  end

  # Returns a sql string fragment that will limit tasks to only those
  # which match the given time qualifiers
  def conditions_for_time_qualifiers(time_qualifiers)
    time_qualifiers = time_qualifiers.group_by { |qualifier| qualifier.reversed? }
    compose_sql(simple_conditions_for_time_qualifiers(time_qualifiers[false]), simple_conditions_for_time_qualifiers(time_qualifiers[true]))
  end

  def simple_conditions_for_time_qualifiers(time_qualifiers)
    return if time_qualifiers.nil? or time_qualifiers.empty?

    res = []
    time_qualifiers.each do |tq|
      start_time = tq.qualifiable.start_time
      end_time = tq.qualifiable.end_time
      column = tq.qualifiable_column
      column = TaskRecord.connection.quote_column_name(column)

      sql = "tasks.#{ column } >= '#{ start_time.to_formatted_s(:db) }'"
      sql += " and tasks.#{ column } < '#{ end_time.to_formatted_s(:db) }'"
      res << "coalesce((#{sql}),0)"
    end

    res = res.join(' or ')
    return "(#{ res })"
  end

  # Returns the column name to use for lookup for the given
  # class_type
  def column_name_for(class_type)
    if class_type == 'User'
      return "task_users.type= 'TaskOwner' AND task_users.user_id"
    elsif class_type == 'Project'
      return 'tasks.project_id'
    elsif class_type == 'Task'
      return 'tasks.id'
    elsif class_type == 'Customer'
      return 'projects.customer_id'
    elsif class_type == 'Company'
      return 'tasks.company_id'
    elsif class_type == 'Milestone'
      return 'tasks.milestone_id'
    elsif class_type == 'Tag'
      return 'task_tags.tag_id'
    else
      return "#{ class_type.downcase }_id"
    end
  end

  def unread_conditions(user, include_orphaned = false)
    count_conditions = []
    count_conditions << "(task_users.unread = ? AND task_users.user_id = #{ user.id })"
    count_conditions << '(task_users.id IS NULL)' if include_orphaned
    sql = count_conditions.join(' OR ')

    params = [true]
    sql = TaskFilter.send(:sanitize_sql_array, [sql] + params)
    "(#{ sql })"
  end

  def compose_sql(arg1, arg2)
    if arg1.blank?
      if arg2.blank?
        ''
      else
        "( not #{arg2} )"
      end
    else
      if arg2.blank?
        arg1
      else
        "(#{arg1} and not #{arg2})"
      end
    end
  end

  #This function parse fullCalendar `start` and `end` date(in Unix format) from  params
  #return conditions for TaskFilter#tasks, unfortunately TaskFilter#task does not support active record :conditions, only plain sql:(
  def parse_fullcalendar_params(calendar_params)
    if !calendar_params[:end].blank? and !calendar_params[:start].blank?

      #return TaskFilter.send(:sanitize_sql_array, ["if(isnull(tasks.estimate_date), (milestones.due_at < ? and milestones.due_at > ?),(tasks.estimate_date < ? and tasks.estimate_date > ?))", Time.at(calendar_params[:end].to_i), Time.at(calendar_params[:start].to_i), Time.at(calendar_params[:end].to_i), Time.at(calendar_params[:start].to_i)])
      return TaskFilter.send(:sanitize_sql_array, [
          '((tasks.estimate_date IS NULL AND milestones.due_at < ? AND milestones.due_at > ?) OR (tasks.estimate_date IS NOT NULL AND tasks.estimate_date < ? AND tasks.estimate_date > ?))',
          Time.at(calendar_params[:end].to_i), Time.at(calendar_params[:start].to_i), Time.at(calendar_params[:end].to_i), Time.at(calendar_params[:start].to_i)
      ]
      )
    else
      return nil
    end
  end

  def set_task_filter_status
    # make owner can see his/her own filter, can be changed later on manage filter
    self.task_filter_users.create(:user_id => self.user_id)
  end
end


# == Schema Information
#
# Table name: task_filters
#
#  id                 :integer(4)      not null, primary key
#  name               :string(255)
#  company_id         :integer(4)
#  user_id            :integer(4)
#  shared             :boolean(1)
#  created_at         :datetime
#  updated_at         :datetime
#  system             :boolean(1)      default(FALSE)
#  unread_only        :boolean(1)      default(FALSE)
#  recent_for_user_id :integer(4)
#
# Indexes
#
#  fk_task_filters_company_id  (company_id)
#  fk_task_filters_user_id     (user_id)
#

