package SL::DB::Helpers::Sorted;

use strict;

require Exporter;
our @ISA    = qw(Exporter);
our @EXPORT = qw(get_all_sorted make_sort_string);

my %sort_spec;

sub make_sort_string {
  my ($class, %params) = @_;

  %sort_spec           = $class->_sort_spec unless %sort_spec;

  my $sort_dir         = defined($params{sort_dir}) ? $params{sort_dir} * 1 : $sort_spec{default}->[1];
  my $sort_dir_str     = $sort_dir ? 'ASC' : 'DESC';

  my $sort_by          = $params{sort_by};
  $sort_by             = $sort_spec{default}->[0] unless $sort_spec{columns}->{$sort_by};

  my $nulls_str        = '';
  if ($sort_spec{nulls}) {
    $nulls_str = ref($sort_spec{nulls}) ? ($sort_spec{nulls}->{$sort_by} || $sort_spec{nulls}->{default}) : $sort_spec{nulls};
    $nulls_str = " NULLS ${nulls_str}" if $nulls_str;
  }

  my $sort_by_str = $sort_spec{columns}->{$sort_by};
  $sort_by_str    = [ $sort_by_str ] unless ref($sort_by_str) eq 'ARRAY';
  $sort_by_str    = join(', ', map { "${_} ${sort_dir_str}${nulls_str}" } @{ $sort_by_str });

  return wantarray ? ($sort_by, $sort_dir, $sort_by_str) : $sort_by_str;
}

sub get_all_sorted {
  my ($class, %params) = @_;
  my $sort_str         = $class->make_sort_string(sort_by => delete($params{sort_by}), sort_dir => delete($params{sort_dir}));

  return $class->get_all(sort_by => $sort_str, %params);
}

1;

__END__

=encoding utf8

=head1 NAME

SL::DB::Helpers::Sorted - Mixin for a manager class that handles
sorting of database records

=head1 SYNOPSIS

  package SL::DB::Manager::Message;

  use SL::DB::Helpers::Sorted;

  sub _sort_spec {
    return ( columns => { recipient_id => [ 'CASE
                                             WHEN recipient_group_id IS NULL THEN lower(recipient.name)
                                             ELSE                                 lower(recipient_group.name)
                                             END',                                      ],
                          sender_id    => [ 'lower(sender.name)',                       ],
                          created_at   => [ 'created_at',                               ],
                          subject      => [ 'lower(subject)',                           ],
                          status       => [ 'NOT COALESCE(unread, FALSE)', 'created_at' ],
                        },
             default => [ 'status', 1 ],
             nulls   => { default => 'LAST',
                          subject => 'FIRST',
                        }
           );
  }

  package SL::Controller::Message;

  sub action_list {
    my $messages = SL::DB::Manager::Message->get_all_sorted(sort_by  => $::form->{sort_by},
                                                            sort_dir => $::form->{sort_dir});
  }

=head1 CLASS FUNCTIONS

=over 4

=item C<make_sort_string %params>

Evaluates C<$params{sort_by}> and C<$params{sort_dir}> and returns an
SQL string suitable for sorting. The package this package is mixed
into has to provide a method L</_sort_spec> that returns a hash whose
structure is explained below. That hash is authoritive in which
columns may be sorted, which column to sort by by default and how to
handle C<NULL> values.

Returns the SQL string in scalar context. In array context it returns
three values: the actual column it sorts by (suitable for another call
to L</make_sort_string>), the actual sort direction (either 0 or 1)
and the SQL string.

=item C<get_all_sorted %params>

Returns C<< $class->get_all >> with C<sort_by> set to the value
returned by c<< $class->make_sort_string(%params) >>.

=back

=head1 CLASS FUNCTIONS PROVIDED BY THE MIXING PACKAGE

=over 4

=item C<_sort_spec>

This method is actually not part of this package but must be provided
by the package this helper is mixed into.

Returns a has with the following keys:

=over 2

=item C<default>

A two-element array containing the name and direction by which to sort
in default cases. Example:

  default => [ 'name', 1 ],

=item C<columns>

A hash reference. Its keys are column names, and its values are SQL
strings by which to sort. Example:

  columns => { transaction_description => 'oe.transaction_description',
               customer_name           => 'lower(customer.name)',
             },

If sorting by a column is requested that is not a key in this hash
then the default column name will be used.

The value can be either a scalar or an array reference. If it's the
latter then both the sort direction as well as the null handling will
be appended to each of its members.

=item C<nulls>

Either a scalar or a hash reference determining where C<NULL> values
will be sorted. If undefined then the decision is left to the
database.

If it is a scalar then all the same value will be used for all
classes. The value is either C<FIRST> or C<LAST>.

If it is a hash reference then its keys are column names (not SQL
names). The values are either C<FIRST> or C<LAST>. If a column name is
not found in this hash then the special keu C<default> will be looked
up and used if it is found.

Example:

  nulls => { transaction_description => 'FIRST',
             customer_name           => 'FIRST',
             default                 => 'LAST',
           },

=back

=back

=head1 BUGS

Nothing here yet.

=head1 AUTHOR

Moritz Bunkus E<lt>m.bunkus@linet-services.deE<gt>

=cut