package SL::Controller::CustomDataExport;

use strict;
use utf8;

use parent qw(SL::Controller::Base);

use DBI qw(:sql_types);
use File::Temp ();
use List::UtilsBy qw(sort_by);
use POSIX qw(strftime);
use Text::CSV_XS;

use SL::DB::CustomDataExportQuery;
use SL::Controller::Helper::ReportGenerator;
use SL::Locale::String qw(t8);

use Rose::Object::MakeMethods::Generic
(
  scalar                  => [ qw(rows) ],
  'scalar --get_set_init' => [ qw(query queries parameters) ],
);

__PACKAGE__->run_before('check_auth');
__PACKAGE__->run_before('setup_javascripts');

#
# actions
#

sub action_list {
  my ($self) = @_;

  $self->render('custom_data_export/list', title => $::locale->text('Execute a custom report query'));
}

sub action_export {
  my ($self) = @_;

  if (!$::form->{parameters_set}) {
    $self->setup_export_action_bar;
    return $self->render('custom_data_export/export', title => t8("Execute custom report '#1'", $self->query->name));
  }

  $self->execute_query;

  if (scalar(@{ $self->rows // [] }) == 1) {
    $self->setup_empty_result_set_action_bar;
    return $self->render('custom_data_export/empty_result_set', title => t8("Execute custom report '#1'", $self->query->name));
  }

  my $report = SL::ReportGenerator->new(\%::myconfig, $::form);

  my $report_name =  $self->query->name;
  $report_name    =~ s{[^[:word:]]+}{_}ig;
  $report_name   .=  strftime('_%Y-%m-%d_%H-%M-%S', localtime());

  $report->set_options(
    std_column_visibility => 1,
    controller_class      => 'CustomDataExport',
    output_format         => 'HTML',
    top_info_text         => $self->query->name,
    title                 => $self->query->name,
    allow_pdf_export      => 1,
    allow_csv_export      => 1,
    allow_chart_export    => 1,
    attachment_basename   => $report_name,
  );

  my %column_defs;
  foreach my $key (@{ $self->rows->[0] }) {
    $column_defs{$key} = { text => $key, sub => sub { $_[0]->{$key} } };
  }

  $report->set_columns(%column_defs);
  $report->set_column_order(@{ $self->rows->[0] });

  $report->set_export_options(qw(export id parameters_set parameters));
  $report->set_options_from_form;

  # Setup data objects (which in this case is an array of hashes).
  my @objects;
  foreach my $set_idx (1..$#{ $self->rows }) {
    my %row_set;
    foreach my $key_idx (0..$#{ $self->rows->[0] }) {
      my $key   = $self->rows->[0]->[$key_idx];
      my $value = $self->rows->[$set_idx]->[$key_idx];
      $row_set{$key} = $value;
    }
    push @objects, \%row_set;
  }

  $self->report_generator_list_objects(report  => $report,
                                       objects => \@objects,
                                       options => {
                                         action_bar_additional_submit_values => { id => $::form->{id}, },
                                       },
  );
}

#
# filters
#

sub check_auth {
  my ($self) = @_;
  $::auth->assert($self->query->access_right) if $self->query->access_right;
}

sub setup_javascripts {
  $::request->layout->add_javascripts('kivi.Validator.js');
}

#
# helpers
#

sub init_query      { $::form->{id} ? SL::DB::CustomDataExportQuery->new(id => $::form->{id})->load : SL::DB::CustomDataExportQuery->new }
sub init_parameters { [ sort_by { lc $_->name } @{ $_[0]->query->parameters // [] } ] }

sub init_queries {
  my %rights_map     = %{ $::auth->load_rights_for_user($::form->{login}) };
  my @granted_rights = grep { $rights_map{$_} } keys %rights_map;

  return scalar SL::DB::Manager::CustomDataExportQuery->get_all_sorted(
    where => [
      or => [
        access_right => undef,
        access_right => '',
        (access_right => \@granted_rights) x !!@granted_rights,
      ],
    ],
  )
}

sub setup_export_action_bar {
  my ($self) = @_;

  for my $bar ($::request->layout->get('actionbar')) {
    $bar->add(
      action => [
        t8('Execute'),
        submit    => [ '#form', { action => 'CustomDataExport/export' } ],
        checks    => [ 'kivi.validate_form' ],
        accesskey => 'enter',
      ],
      action => [
        t8('Back'),
        call => [ 'kivi.history_back' ],
      ],
    );
  }
}

sub setup_empty_result_set_action_bar {
  my ($self) = @_;

  for my $bar ($::request->layout->get('actionbar')) {
    $bar->add(
      action => [
        t8('Back'),
        call => [ 'kivi.history_back' ],
      ],
    );
  }
}

sub prepare_query {
  my ($self) = @_;

  my $sql_query = $self->query->sql_query;
  my @values;

  my %values_by_name;

  foreach my $parameter (@{ $self->query->parameters // [] }) {
    my $value                           = ($::form->{parameters} // {})->{ $parameter->name };
    $values_by_name{ $parameter->name } = $parameter->parameter_type eq 'number' ? $::form->parse_amount(\%::myconfig, $value) : $value;
  }

  while ($sql_query =~ m{<\%(.+?)\%>}) {
    push @values, $values_by_name{$1};
    substr($sql_query, $-[0], $+[0] - $-[0], '?');
  }

  return ($sql_query, @values);
}

sub execute_query {
  my ($self) = @_;

  my ($sql_query, @values) = $self->prepare_query;
  my $sth                  = $self->query->db->dbh->prepare($sql_query) || $::form->dberror;
  $sth->execute(@values)                                                || $::form->dberror;

  my @names = @{ $sth->{NAME} };
  my @types = @{ $sth->{TYPE} };
  my @data  = @{ $sth->fetchall_arrayref };

  $sth->finish;

  foreach my $row (@data) {
    foreach my $col (0..$#types) {
      my $type = $types[$col];

      if ($type == SQL_NUMERIC) {
        $row->[$col] = $::form->format_amount(\%::myconfig, $row->[$col]);
      }
    }
  }

  $self->rows([
    \@names,
    @data,
  ]);
}

1;
