from operator import or_


def _apply_query_filter(table, key, values, query):
    """Apply a filter to the given ``query`` based on the ``table``,
    ``key``, and ``value``.

    Parameters
    ----------
    table : obj
        The ``SQLAlchemy`` table object associated with the table to
        apply the filter to.
    key : str
        The keyword for the column to filter on.
    values : obj
        The requested values of the ``key``.
    query : obj
        The ``SQLAlchemy`` ``query`` objecgt to perform the filtering
        on.

    Returns
    -------
    query : obj
        The ``SQLAlchemy`` ``query`` object with filter applied.
    """

    # Fields that accpet comma-separated lists and wildcards
    csv_keys = ['rootname', 'targname', 'pr_inv_l', 'pr_inv_f']

    # Fields that allow operators
    operator_keys = ['date_obs', 'exptime']

    # Parse the key/value pairs for comma-separated values
    if key in csv_keys:
        parsed_value = values[0].replace(' ', '').split(',')
        parsed_value = [item.replace('*', '%') for item in parsed_value]
        conditions = [getattr(table, key).like(val) for val in parsed_value]
        query = query.filter(or_(*conditions))

    # Parse the key/value pairs for operator keys
    elif key in operator_keys:
        if values['op'] == 'between':
            if len(values) == 3:
                if float(values['val1'].replace('-', '')) < float(values['val2'].replace('-', '')):
                    query = query.filter(getattr(table, key).between(values['val1'], values['val2']))
                elif float(values['val1'].replace('-', '')) > float(values['val2'].replace('-', '')):
                    query = query.filter(getattr(table, key).between(values['val2'], values['val1']))
                elif float(values['val1'].replace('-', '')) == float(values['val2'].replace('-', '')):
                    raise ValueError('Values submitted for field "{}" must be different.'.format(key))
                else:
                    raise ValueError('Invalid values submitted for field "{}".'.format(key))
            else:
                query = query.filter(getattr(table, key).op('=')(values['val1']))
        else:
            query = query.filter(getattr(table, key).op(values['op'])(values['val1']))

    # Else the filtering is straightforward
    query = query.filter(getattr(table, key).in_(values))

    return query