require 'active_record'

module PwQuery
  class Service
    DEFAULT_LIMIT = 50

    def initialize(model, params, options = {}, associations = nil)
      @model = model
      @params = params
      @options = options
      @associations = associations
      @total = @model.count
      @limit = @params.fetch(:limit, DEFAULT_LIMIT).to_i
      @meta = {
        total: @total,
        limit: @limit,
        page: @params.fetch(:page, 1).to_i,
        page_size: @total,
        total_pages: (@total.to_f / @limit).ceil
      }
    end

    def perform
      query = @model.all
      query = include_associations(query, @associations)
      query = apply_filters(query, @params[:filters])
      query = apply_sorting(query, @params)
      query = apply_field_selection(query, @params)
      query = apply_date_range(query, @params[:date_range]) if @options[:date_scope]
      query = apply_time_range(query, @params[:time_range])
      query = apply_search(query, @params[:search])
      query = apply_range(query, @params[:range])
      query = apply_pagination(query, @params)
      query = apply_aggregation(query, @params[:aggregation])

      handle_query(query)

      { meta: @meta, data: query }
    end

    def include_associations(query, associations)
      query.includes(associations)
    end

    def apply_filters(query, filters)
      return query if filters.blank?

      filters.each do |key, value|
        next if value.blank?

        values = value.to_s.split(',').map(&:strip)
        condition = values.size > 1 ? values : value
        query = query.where(key => condition)
      end

      query
    end

    def apply_sorting(query, params)
      if params[:sort_by]
        apply_multi_sort(query, params[:sort_by])
      else
        apply_sort(query, params[:sort] || 'created_at:DESC')
      end
    end

    def apply_sort(query, value)
      sort_field, sort_order = value.split(':')
      query.order("#{sort_field} #{sort_order}")
    end

    def apply_multi_sort(query, value)
      sort_params = value.split(',')
      sorting_criteria = sort_params.map { |param| param.split(':').join(' ') }
      query.order(sorting_criteria.join(', '))
    end

    def apply_field_selection(query, params)
      if params[:projection] || params[:fields]
        fields = params[:projection] || params[:fields]
        query.select(fields.split(','))
      else
        query
      end
    end

    def apply_date_range(query, value)
      start_date = value ? parse_date(value['start']) : 1.month.ago.beginning_of_day
      end_date = value ? parse_date(value['end']) : Time.current
      query.where(created_at: start_date...end_date)
    end

    def apply_time_range(query, value)
      return query if value.blank?

      start_time = parse_time(value['start'] || '00:00:00')
      end_time = parse_time(value['end'] || '23:59:59')

      table_name = query.model.table_name
      if start_time <= end_time
        query.where("CAST(#{table_name}.created_at AS time) BETWEEN ? AND ?", start_time, end_time)
      else
        query.where("CAST(#{table_name}.created_at AS time) >= ? OR CAST(#{table_name}.created_at AS time) <= ?",
                    start_time, end_time)
      end
    end

    def apply_search(query, filters)
      return query if filters.blank?

      filters = filters.to_unsafe_h
      conditions = build_filter_conditions(query, filters)
      apply_conditions(query, conditions)
    end

    def build_filter_conditions(query, filters)
      filters.map do |key, value|
        next if value.blank?

        key.include?('.') ? association_condition(query, key, value) : direct_condition(query, key, value)
      end.compact
    end

    def association_condition(query, key, value)
      association_chain = key.split('.')
      attribute = association_chain.pop
      current_table = build_join_chain(query, association_chain)
      create_condition(current_table, attribute, value)
    end

    def build_join_chain(query, chain)
      current_model = query.klass
      current_table = current_model.table_name
      join_chain = []

      chain.each do |assoc|
        reflection = current_model.reflect_on_association(assoc.to_sym) or raise "Unknown association: #{assoc} for model #{current_model}"
        table_alias = "#{reflection.klass.table_name}_as_#{current_table}"
        join_chain << { table: reflection.klass.table_name, alias: table_alias, foreign_key: reflection.foreign_key,
                        parent_table: current_table }
        current_table = table_alias
        current_model = reflection.klass
      end

      query.joins(join_chain.map do |join|
        "LEFT JOIN #{join[:table]} AS #{join[:alias]} ON #{join[:alias]}.id = #{join[:parent_table]}.#{join[:foreign_key]}"
      end.join(' '))
    end

    def direct_condition(query, key, value)
      create_condition(query.klass.table_name, key, value)
    end

    def create_condition(table, attribute, value)
      values = value.to_s.split(',').map(&:strip)
      column = get_column_type(table, attribute)

      case column&.type
      when :string, :text
        condition = if values.size > 1
                      values.map do |_v|
                        "LOWER(#{table}.#{attribute}::text) LIKE LOWER(?)"
                      end.join(' OR ')
                    else
                      "LOWER(#{table}.#{attribute}::text) LIKE LOWER(?)"
                    end
        values = values.size > 1 ? values.map { |v| "%#{escape_like(v)}%" } : ["%#{escape_like(value)}%"]
        [condition, *values]
      else
        values.size > 1 ? ["#{table}.#{attribute} IN (?)", values] : ["#{table}.#{attribute} = ?", value]
      end
    end

    def apply_conditions(query, conditions)
      return query unless conditions.any?

      where_clause = conditions.map(&:first).join(' OR ')
      where_values = conditions.map(&:last).flatten
      query.where(where_clause, *where_values)
    end

    def apply_range(query, ranges)
      return query if ranges.blank?

      ranges.each do |key, value|
        range_values = value.split(',')
        min_value = range_values[0].to_f if range_values[0].present?
        max_value = range_values[1].to_f if range_values[1].present?

        if min_value.present? && max_value.present?
          query = query.where("#{key} BETWEEN ? AND ?", min_value, max_value)
        elsif min_value.present?
          query = query.where("#{key} >= ?", min_value)
        elsif max_value.present?
          query = query.where("#{key} <= ?", max_value)
        end
      end

      query
    end

    def apply_pagination(query, params)
      page = params.fetch(:page, 1).to_i
      offset_value = page == 1 ? 0 : (page - 1) * @limit
      total_pages = (@total.to_f / @limit).ceil

      page <= total_pages ? query.offset(offset_value).limit(@limit) : query.none
    end

    def apply_aggregation(query, value)
      return query if value.nil? || value.empty?

      aggregation_type, aggregation_field = value.split(':')

      case aggregation_type
      when 'count'
        query.count
      when 'sum'
        query.sum(aggregation_field)
      when 'average'
        query.average(aggregation_field)
      when 'max'
        query.maximum(aggregation_field)
      when 'min'
        query.minimum(aggregation_field)
      else
        query
      end
    end

    def get_column_type(table, attribute)
      klass = table.classify.constantize
      klass.columns_hash[attribute.to_s]
    rescue StandardError
      nil
    end

    def escape_like(string)
      string.gsub(/[\\%_]/) { |m| "\\#{m}" }
    end

    def parse_time(time_string)
      Time.parse(time_string).strftime('%H:%M:%S')
    rescue ArgumentError
      '00:00:00'
    end

    def parse_date(date_string)
      Date.parse(date_string) if date_string.present?
    rescue ArgumentError
      nil
    end

    private

    def handle_query(query)
      @meta['page_size'] = query.count
      query
    end
  end
end