#################### main pod documentation begin ###################

=head1 NAME

Zymonic::Toolkit::Editor - Implements methods needed for 
building/editing parts of Zymonic via Toolkit.

=head1 SYNOPSIS

Implements methods needed for building/editing parts of Zymonic via Toolkit.

=head1 DESCRIPTION

Implements methods needed for building/editing parts of Zymonic via Toolkit..

=head1 USAGE

TODO

=head1 BUGS

NONE

=head1 SUPPORT

As in the license, Zymonic is provided without warranty or support
unless purchased separately, however... If you email
zymonic-support@zednax.com your issue will   be noted and may
receive a response.

For security issues, please contact zymonic-security@zednax.com and
someone will respond within 8 working hours.

=head1 AUTHOR

    Alex Masidlover et al.
    CPAN ID: MODAUTHOR
    Zednax Limited
    alex.masidlover@zednax.com
    http://www.zednax.com

=head1 COPYRIGHT

This program is free software licensed under the...

    Zymonic Public License 1.0

The full text of the license can be found in the
LICENSE file included with this module.

Other licenses may be acceptable if including
parts of Zymonic in larger projects, please
contact Zednax for details. 


=head1 SEE ALSO

Zymonic
perl(1).

=cut

#################### main pod documentation end ###################

package Zymonic::Toolkit::Editor;

use strict;

BEGIN
{
    use Exporter ();
    use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
    $VERSION = '0.01';
    @ISA     = qw(Exporter);

    #Give a hoot don't pollute, do not export more than needed by default
    @EXPORT      = qw();
    @EXPORT_OK   = qw();
    %EXPORT_TAGS = ();
}

use base "Zymonic::Toolkit";

use Zymonic::Utils qw(debug get_array list_to_hash);
use Zymonic::ZymonicXMLSimple;

use Data::Dumper;

use Zymonic;
use Exception::Class (
    'Zymonic::Exception::Toolkit::Editor' => {
        isa         => 'Zymonic::Exception::Toolkit',
        fields      => [],
        description => 'Editor Toolkit related exceptions'
    },
);

our $DEFINITION = {
    fields => [
        {
            ZName            => { content => 'system' },
            DisplayName      => { content => 'System' },
            ShortDescription => {
                content => "The name of the sub-directory in which the system's definition is stored."
            },
            RequiredField => { content => 'true' },
            sequence      => 20,
            FieldGroup    => {
                ZName       => { content => 'zz_tk_editor_system_fieldgroup' },
                DisplayName => { content => 'Toolkit Editor - System Options' },
                sequence    => 10,
            }
        },
        {
            ZName            => { content => 'configdir' },
            DisplayName      => { content => 'Configuration Directory' },
            ShortDescription => {
                content =>
                  'The name of the directory in which Zymonic definitions are stored; defaults to "/etc/zymonic".'
            },
            ExtraCharacters => { content => '/.,-_' },
            sequence        => 10,
            FieldGroup      => {
                ZName => { content => 'zz_tk_editor_system_fieldgroup' },
            }
        },
        {
            ZName            => { content => 'table_fields' },
            DisplayName      => { content => 'Table Fields' },
            ShortDescription => {
                content => 'List of tables and fields to populate the filter. '
                  . 'Format is [table1name]:[field1name],[field2name],...|[table1name]:[field1name],[field2name],... etc.'
            },
            ExtraCharacters => { content => ':,|' },
            RequiredField   => { content => 'true' },
            sequence        => 10,
            FieldGroup      => {
                ZName       => { content => 'zz_tk_editor_fieldgroup' },
                DisplayName => { content => 'Toolkit Editor Options' },
                sequence    => 10,
            }
        },
        {
            ZName            => { content => 'order_by_fields' },
            DisplayName      => { content => 'Order By Fields' },
            ShortDescription => {
                content => 'List of tables and fields to order the filter by. '
                  . 'Format is [table1name]:[field1name],[field2name],...|[table1name]:[field1name],[field2name],... etc.'
            },
            ExtraCharacters => { content => ':,|' },
            sequence        => 15,
            FieldGroup      => {
                ZName => { content => 'zz_tk_editor_fieldgroup' },
            }
        },
        {
            ZName            => { content => 'outputfile' },
            DisplayName      => { content => 'Output File' },
            ShortDescription => { content => 'Will write output to this file, if not set writes to STDOUT.' },
            ExtraCharacters  => { content => '/.,-_' },
            sequence         => 20,
            FieldGroup       => {
                ZName => { content => 'zz_tk_editor_fieldgroup' },
            }
        },
        {
            ZName            => { content => 'filter_name' },
            DisplayName      => { content => 'Filter Name' },
            ShortDescription => {
                content => 'DisplayName of the filter, also used to build ZNames.'
            },
            RequiredField => { content => 'true' },
            sequence      => 30,
            FieldGroup    => {
                ZName => { content => 'zz_tk_editor_fieldgroup' },
            }
        },
        {
            ZName            => { content => 'zname_prefix_preference' },
            DisplayName      => { content => 'ZName Prefix Preference' },
            ShortDescription => {
                content =>
                  'When multiple matches are found, will use this list to check start of znames and pick which to use. '
                  . 'Will then only prompt if multiple znames have same preference. '
                  . 'For example, if znames matched are ztsm_orders, oc_orders and orders, setting this to "oc,ztsm" will choose oc_orders automatically.'
            },
            sequence   => 40,
            FieldGroup => {
                ZName => { content => 'zz_tk_editor_fieldgroup' },
            }
        },
        {
            ZName            => { content => 'create_table_include_stubs' },
            DisplayName      => { content => 'Create TableInclude Stubs' },
            ShortDescription => {
                content =>
'When TableIncludes cannot be found in the system will create a stub definition that will need to be filled in. '
            },
            sequence   => 50,
            FieldGroup => {
                ZName => { content => 'zz_tk_editor_fieldgroup' },
            }
        },
        {
            ZName            => { content => 'only_table_include_stubs' },
            DisplayName      => { content => 'Only TableInclude Stubs' },
            ShortDescription => {
                content =>
'Rather than lookup TableIncludes in the system, simply include stubs of the joins needed that can be filled in. '
            },
            sequence   => 5,
            FieldGroup => {
                ZName => { content => 'zz_tk_editor_fieldgroup' },
            }
        },
        {
            ZName            => { content => 'verbose' },
            DisplayName      => { content => 'Verbose' },
            ShortDescription => {
                content => 'Enable more verbose output. '
            },
            sequence   => 60,
            FieldGroup => {
                ZName => { content => 'zz_tk_editor_fieldgroup' },
            }
        },
    ],
    commands => {
        build_filter => {
            ShortDescription => {
                content => 'Generates usable filter XML from the given list of fields.'
            },
            fields => [
                'system',                   'configdir',
                'table_fields',             'order_by_fields',
                'outputfile',               'filter_name',
                'zname_prefix_preference',  'create_table_include_stubs',
                'only_table_include_stubs', 'verbose'
            ],

            # TODO: add role permission so can be used in GUI

            # TODO: docs
            LongDescription => {
                div => []
            },
        },
    }
};

#################### subroutine header begin ####################

=head2 definition 

 Usage     : $definition = $mo->definition;
 Purpose   : Returns the module definition hash.
 Returns   : nothing
 Argument  : nothing
 Throws    : nothing
 Comment   : 

See Also   :

=cut

#################### subroutine header end ####################

sub definition
{
    my $self = shift;
    return $DEFINITION;
}

#################### subroutine header begin ####################

=head2 build_filter 

 Usage     : Called from toolkit
 Purpose   : Builds filter xml from incoming list of tables and fields
 Returns   : nothing
 Argument  : nothing
 Throws    : nothing
 Comment   : 

See Also   :

=cut

#################### subroutine header end ####################

sub build_filter
{
    my $self = shift;

    # setup and load config
    $Zymonic::system = $self->get_param('system');
    $self->{config_dir} = $self->get_param('configdir');
    my $config = $self->_config();

    my %table_fields = $self->get_table_fields( $self->get_param('table_fields'), $config );
    unless ( keys %table_fields )
    {
        $self->log("Unable to build filter, no tables found");
        return;
    }

    # only need joins if there are multiple tables
    my %table_includes = ();
    if ( ( keys %table_fields ) > 1 )
    {
        %table_includes = $self->get_table_includes( [ keys %table_fields ] );
        unless ( keys %table_includes )
        {
            $self->log("Unable to find joins needed");
            return;
        }
    }

    my %order_by_fields = $self->get_table_fields( $self->get_param('order_by_fields'), $config );

    # build the filter
    my $filter_def =
      $self->build_filter_def( $self->get_param('filter_name'), \%table_fields, \%table_includes, \%order_by_fields );

    # convert to zymonic xml
    my $xmlsimple = Zymonic::ZymonicXMLSimple->new(
        KeyAttr       => [],
        RootName      => "Zymonic",
        SuppressEmpty => 1
    );
    my $filter_xml = $xmlsimple->XMLout( { Filter => $filter_def } );

    # output to file or screen
    my $output_file = $self->get_param('outputfile');
    if ($output_file)
    {
        open my $fh, '>', $output_file or die $!;
        print $fh $filter_xml;
        close $fh;
        $self->log("Filter XML written to: $output_file");
    }
    else
    {
        # if output then prevent completed message and notime on the log
        $self->{no_completed_message} = 'true';
        $self->log( $filter_xml, 'notime' );
    }
}

#################### subroutine header begin ####################

=head2 get_table_fields 

 Usage     : $self->get_table_fields("[table1name]:[field1name],[field2name],...|[table1name]:[field1name],[field2name],...")
 Purpose   : parsing incoming list of table fields into znames form the system
 Returns   : hashref keyed on table zname containing field znames
 Argument  : list of table fields
 Throws    : nothing
 Comment   : 

See Also   :

=cut

#################### subroutine header end ####################

sub get_table_fields
{
    my $self = shift;
    my $table_fields = shift || '';

    my $verbose = $self->get_param('verbose');

    my $config  = $self->_config();
    my %tables  = ();
    my $errored = '';

    # split on | to get per table data
    my $sequence = 0;
    foreach my $table_data ( split( '\|', $table_fields ) )
    {

        # split out table name from list of fields, using :
        my ( $table_name, $field_names ) = split( ':', $table_data, 2 );

        # split apart the fields on ,
        my @field_names = split( ',', $field_names );

        # lookup table definition
        my $table_def = $self->get_definition_from_sqlname( 'Table', $table_name );
        if ($table_def)
        {

            # add table def to hash to return and setup list of fields
            my $table_zname = $table_def->{ZName}->{content};
            $tables{$table_zname} = {
                xmldef       => $table_def,
                min_sequence => $sequence + 1,
                fields       => [],
              }
              unless $tables{$table_zname};

            # lookup fields in table def
            my %table_fields = map { $_->{ZName}->{content} => 1 } get_array( $table_def->{Field} );

            # lookup field definitions
            foreach my $field_name (@field_names)
            {
                my $field_def = $self->get_definition_from_sqlname( 'Field', $field_name, \%table_fields );
                if ($field_def)
                {
                    # check field is in this table
                    my $field_zname = $field_def->{ZName}->{content};
                    if ( $table_fields{$field_zname} )
                    {
                        push( @{ $tables{$table_zname}->{fields} }, { xmldef => $field_def, sequence => ++$sequence } );
                    }
                    else
                    {
                        $self->log("Field $field_name ($field_zname) not in Table $table_name ($table_zname)")
                          if $verbose;
                        $errored = 'true';
                    }
                }
                else
                {
                    $self->log("Unable to find definition for Field $field_name") if $verbose;
                    $errored = 'true';
                }
            }
        }
        else
        {
            $self->log("Unable to find definition for Table $table_name") if $verbose;
            $errored = 'true';
        }
    }

    # if errored return nothing
    return () if $errored;
    return %tables;
}

#################### subroutine header begin ####################

=head2 get_table_includes 

 Usage     : $self->get_table_includes($table_Znames)
 Purpose   : determins minimial tableincludes needed to connect all incoming tables
 Returns   : hashref of table includes, keys are "base" for base table, then 
 each table zname with value of a hashref containing the table_include to use
 Argument  : list of table znames
 Throws    : nothing
 Comment   : 

See Also   :

=cut

#################### subroutine header end ####################

sub get_table_includes
{
    my $self         = shift;
    my $table_znames = shift;

    # get all table includes and convert into a lookup on join to
    my $config               = $self->_config();
    my @all_table_includes   = $config->get_def( 'TableInclude', '*' );
    my %table_include_lookup = ();
    map {
        $table_include_lookup{ $_->{JoinTo}->{Table}->{ZName}->{content} } = []
          unless $table_include_lookup{ $_->{JoinTo}->{Table}->{ZName}->{content} };
        push( @{ $table_include_lookup{ $_->{JoinTo}->{Table}->{ZName}->{content} } }, $_ );
    } @all_table_includes;

    # try and build a join list use each table as the base
    my @solutions = ();
    foreach my $base_table ( @{$table_znames} )
    {
        my $solution =
          $self->get_all_table_includes_for_base_table( $base_table, $table_znames, \%table_include_lookup );
        push( @solutions, $solution ) if $solution;
    }

    # now need to pick a solution

    # see if there are any without new table includes, prefer those
    my @preferred_solutions = ();
    foreach my $solution (@solutions)
    {
        my @new_table_includes =
          grep { $_ ne $solution->{base} && $solution->{$_}->{new_table_include} } @{$table_znames};
        push( @preferred_solutions, $solution ) unless @new_table_includes;
    }

    # if no preferred found then need to pick from all
    @preferred_solutions = @solutions unless @preferred_solutions;

    # sort the list on base table name, for consistancy on multiple runs
    @preferred_solutions = sort { $a->{base} cmp $b->{base} } @preferred_solutions;

    # TODO: might be able to limit further here byt picking "simplest" solution
    # problem is how to determine that, instinct is to pick the one with most fields on base table
    # but for now just prompt the user to pick

    # build up list of joins in each for user to pick from
    my $solution = $self->get_user_choice_from_list(
        \@preferred_solutions,
        "Multiple possible solutions found, which one should be used",
        sub {
            my $solution = shift;
            return $solution->{base} . ': ' . join(
                ', ',
                map {
                    my $table_include = $solution->{$_}->{new_table_include} || $solution->{$_}->{table_include};
                    ( $table_include ? $table_include->{ZName}->{content} : 'ERROR:NOTABLEINCLUDE' );
                } grep { $_ ne $solution->{base} } @{$table_znames}
            );
        }
    );
    return %{ $solution || {} };
}

#################### subroutine header begin ####################

=head2 get_all_table_includes_for_base_table

 Usage     : $self->get_all_table_includes_for_base_table($base_table, $tables, $table_include_lookup)
 Purpose   : attempts to get all table includes needed for list of tables for the given base table
 Returns   : undef, or hashref solution keys are "base" for base table, then 
 each table zname with value of a hashref containing the table_include to use 
 Argument  : base table, join to table zname, hashref of all table includes, keyed on jointo
 Throws    : nothing
 Comment   : nothing

See Also   :

=cut

#################### subroutine header end ####################

sub get_all_table_includes_for_base_table
{
    my $self                 = shift;
    my $base_table           = shift;
    my $tables               = shift;
    my $table_include_lookup = shift;

    my $verbose                    = $self->get_param('verbose');
    my $create_table_include_stubs = $self->get_param('create_table_include_stubs');
    my $only_table_include_stubs   = $self->get_param('only_table_include_stubs');

    my $solution = { base => $base_table };

    # need to find joins for all other tables
    my $config        = $self->_config();
    my @joins_to_find = grep { $_ ne $base_table } @{$tables};
    my %joins_found   = ();
    my %joins_used    = ();
    my $count         = 0;
    while ( @joins_to_find && !$only_table_include_stubs )
    {
        # get all joins to this table
        my $table_to_check = shift @joins_to_find;
        my @table_includes = @{ $table_include_lookup->{$table_to_check} || [] };

        # get joins to use
        my @joins_to_use = $self->pick_best_table_includes( $base_table, $table_to_check, \@table_includes,
            { %joins_found, %joins_used } );
        if (@joins_to_use)
        {
            # add to solution
            $solution->{$table_to_check} = { all_table_includes => \@joins_to_use };

            # track what we've used and link it back to that part of the solution
            foreach my $table_include (@joins_to_use)
            {
                my $table_include_zname = $table_include->{ZName}->{content};
                $joins_found{$table_include_zname} = $solution->{$table_to_check};

                # also mark it as the table include to use on the solution that uses it
                if ( $table_include->{JoinFrom}->{TableInclude} )
                {
                    my $join_from = $table_include->{JoinFrom}->{TableInclude}->{ZName}->{content};
                    my $join_from_solution = $joins_used{$join_from} || $joins_found{$join_from};
                    $join_from_solution->{table_include} = $config->get_def( 'TableInclude', $join_from );
                    $joins_used{$join_from} = $join_from_solution;
                }
            }
        }
        else
        {
            # unable to find anything suitable, add this table to the end of the list
            # to let more potential joins get added and check it later
            push( @joins_to_find, $table_to_check );
        }

        # stop if we try too many times
        if ( $count++ > ( @{$tables} * 10 ) )
        {
            last;
        }
    }

    # if we broke out early and didn't find anything then give up on this base table
    if (@joins_to_find)
    {
        # if flag is set then create stubs for what is missing
        if ( $create_table_include_stubs || $only_table_include_stubs )
        {
            foreach my $table (@joins_to_find)
            {
                $solution->{$table}->{new_table_include} = {
                    ZName         => { content => "STUB_${base_table}_TO_${table}" },
                    JoinFrom      => { Table   => { ZName => { content => $base_table } } },
                    JoinTo        => { Table   => { ZName => { content => $table } } },
                    JoinType      => { content => 'TODO_TYPE' },
                    JoinCondition => { content => 'TODO_CONDITION' },
                };
            }
        }
        else
        {
            $self->log( "Unable to identify joins from base table $base_table for these tables: "
                  . join( ', ', @joins_to_find ) )
              if $verbose;
            return undef;
        }
    }

    # ensure each item has a table include set
    my @tables_missing_joins =
      grep { $_ ne $base_table && !( $solution->{$_}->{table_include} || $solution->{$_}->{new_table_include} ) }
      @{$tables};
    if (@tables_missing_joins)
    {
        # pick joins to use in same manner as above
        foreach my $table (@tables_missing_joins)
        {
            my @all_table_includes = @{ $solution->{$table}->{all_table_includes} || [] };
            my @joins_to_use = $self->pick_best_table_includes( $base_table, $table, \@all_table_includes,
                { %joins_found, %joins_used } );
            if (@joins_to_use)
            {
                # let user have final choice
                my $table_include = $self->get_user_choice_from_list(
                    \@joins_to_use,
                    "Multiple possible TableIncludes found, which one should be used",
                    sub { return $_[0]->{ZName}->{content}; }
                );
                if ($table_include)
                {
                    $solution->{$table}->{table_include} = $table_include;
                }
                else
                {
                    $self->log("Invalid choice");
                    return undef;
                }
            }
            else
            {
                $self->log("No valid joins found to table $table");
                return undef;
            }
        }
    }

    # all joins found and explicitly set, add as a solution
    return $solution;
}

#################### subroutine header begin ####################

=head2 pick_best_table_includes

 Usage     : $self->pick_best_table_includes($base_table, $table, $table_includes, $table_includes)
 Purpose   : picks the best table include to use for given table in the incoming list
 Returns   : list table include to use
 Argument  : base table, join to table zname, list of table includes and lookup of table includes used so far
 Throws    : nothing
 Comment   : assumes joins to base table are preferrable

See Also   :

=cut

#################### subroutine header end ####################

sub pick_best_table_includes
{
    my $self                = shift;
    my $base_table          = shift;
    my $table               = shift;
    my $table_includes      = shift;
    my $table_includes_used = shift;

    # check if any come from the base table and if found, use that join
    my @from_base =
      grep { $_->{JoinFrom}->{Table} && $_->{JoinFrom}->{Table}->{ZName}->{content} eq $base_table } @{$table_includes};
    if (@from_base)
    {
        return @from_base;
    }
    else
    {
        # check if any come from any joins we've used/found so far
        my @from_joins = grep {
                 $_->{JoinFrom}->{TableInclude}
              && $table_includes_used->{ $_->{JoinFrom}->{TableInclude}->{ZName}->{content} }
        } @{$table_includes};
        if (@from_joins)
        {
            return @from_joins;
        }
    }

    # couldn't find anything
    return ();
}

#################### subroutine header begin ####################

=head2 get_definition_from_sqlname

 Usage     : $self->get_definition_from_sqlname($type, $sqlname)
 Purpose   : Looks up zname and then definition from given sqlanme
 Returns   : an xmldef for the given sqlname, or undef if couldn't be found
 Argument  : type and sqlname and optional lookup hash of znames to limit choices to
 Throws    : nothing
 Comment   : prompts the user

See Also   :

=cut

#################### subroutine header end ####################

sub get_definition_from_sqlname
{
    my $self            = shift;
    my $type            = shift;
    my $sqlname         = shift;
    my $limit_to_znames = shift;

    my $config = $self->_config();

    # lookup znames, error if none found
    my @znames = $config->get_znames_from_sqlname($sqlname);
    unless (@znames)
    {
        $self->log("Unable to find zname for $type $sqlname");
        return undef;
    }

    # limit zname choice
    if ($limit_to_znames)
    {
        @znames = grep { $limit_to_znames->{$_} } @znames;
        unless (@znames)
        {
            $self->log("Unable to find zname for $type $sqlname in the requested table");
            return undef;
        }
    }

    # if zname prefix preferences set, check if can use that to limit list
    my $zname_prefix_preference = $self->get_param('zname_prefix_preference');
    if ($zname_prefix_preference)
    {
        foreach my $zname_prefix ( split( ',', $zname_prefix_preference ) )
        {
            my @preferred_znames = grep { $_ =~ /^$zname_prefix/ } @znames;
            if (@preferred_znames)
            {
                @znames = @preferred_znames;
                last;
            }
        }
    }

    # get user to choose which one
    my $zname = $self->get_user_choice_from_list( \@znames, "$type ZName to use for $sqlname" );
    if ($zname)
    {
        # grab the def, caller will handle it if undef
        return $config->get_def( $type, $zname, 'no_error' );
    }

    return undef;
}

#################### subroutine header begin ####################

=head2 get_user_choice_from_list 

 Usage     : $self->get_user_choice_from_list(\@options)
 Purpose   : Called by toolkit
 Returns   : list of options as chosen by the user
 Argument  : list of options to choose from
 Throws    : nothing
 Comment   : if 1 choice then doesn't prompt just uses it,
 otherwise prompts the user if multiple choice, 

See Also   :

=cut

#################### subroutine header end ####################

sub get_user_choice_from_list
{
    my $self       = shift;
    my $options    = shift || [];
    my $label      = shift || 'Options';
    my $item_label = shift || sub { return $_[0]; };

    # if only one item just use that
    if ( @{$options} == 1 )
    {
        return $options->[0];
    }

    # sort the list so re-running always shows in the same order
    my @options = sort @{$options};

    print "$label:\n" . join( "\n", map { ( $_ + 1 ) . ': ' . $item_label->( $options[$_] ); } ( 0 .. $#options ) );
    print "\nEnter choice: ";
    my $index = <STDIN>;
    chomp($index);
    print "\n";

    if ( $index !~ /[0-9]+/ )
    {
        $self->log("Invalid option picked: $index");
        return undef;
    }

    my $selected = $options[ $index - 1 ];
    return $selected;
}

#################### subroutine header begin ####################

=head2 build_filter_def 

 Usage     : $self->build_filter_def($name, $table_fields, $table_includes, $order_by_fields)
 Purpose   : Build a filter definition from incoming tables and fields
 Returns   : filter def
 Argument  : filter name, hashref of tables and fields, hashref of table include,
 hashref of order by fields
 Throws    : nothing
 Comment   :

See Also   :

=cut

#################### subroutine header end ####################

sub build_filter_def
{
    my $self            = shift;
    my $filter_name     = shift;
    my $table_fields    = shift;
    my $table_includes  = shift;
    my $order_by_fields = shift;

    # strip chars to build a zname
    my $filter_zname = lc( $Zymonic::system . '_' . $filter_name );
    $filter_zname =~ s/[^a-zA-Z0-9_]//g;

    # build the basic def
    my $filter_def = {
        ZName       => { content => $filter_zname },
        DisplayName => { content => $filter_name },
        BaseTable   => { ZName   => { content => $table_includes->{base} } },
        ReportField => [],
        SearchField => [],
    };

    # track TableIncludes so we only add the def once
    my %table_includes_added = ();

    # starting from base table if joins
    my @table_order = keys %{$table_fields};
    if ( keys %{$table_includes} )
    {
        @table_order = ( $table_includes->{base}, grep { $_ ne 'base' } keys %{$table_includes} );
    }

    # add the report/search fields
    foreach my $table_zname (@table_order)
    {
        my $table     = $table_fields->{$table_zname};
        my $table_def = $table->{xmldef};

        foreach my $field ( sort { $a->{sequence} <=> $b->{sequence} } @{ $table->{fields} } )
        {
            my $field_def   = $field->{xmldef};
            my $field_zname = $field_def->{ZName}->{content};

            # TODO: lookup existing ReportFields/SearchFields to reuse?

            my $table_include;
            if ( $table_includes->{$table_zname} )
            {
                if ( $table_includes->{$table_zname}->{new_table_include} )
                {
                    # if new table include add the full def first time, then references other times
                    my $new_table_include = $table_includes->{$table_zname}->{new_table_include};
                    if ( $table_includes_added{ $new_table_include->{ZName}->{content} } )
                    {
                        $table_include = { ZName => { content => $new_table_include->{ZName}->{content} } };
                    }
                    else
                    {
                        $table_include = $new_table_include;
                        $table_includes_added{ $new_table_include->{ZName}->{content} } = 'true';
                    }
                }
                elsif ( $table_includes->{$table_zname}->{table_include} )
                {
                    # existing table include, just zname reference needed
                    my $existing_table_include = $table_includes->{$table_zname}->{table_include};
                    $table_include = { ZName => { content => $existing_table_include->{ZName}->{content} } };
                }
            }

            push(
                @{ $filter_def->{ReportField} },
                {
                    ZName => { content => $filter_zname . '_rf_' . $field_zname },
                    Field => {
                        ZName    => { content => $filter_zname . '_f_' . $field_zname },
                        Base     => { content => $field_zname },
                        sequence => $field->{sequence},
                        (
                            $table_include
                            ? ( TableInclude => $table_include )
                            : ()
                        ),
                    },
                    SearchMap => {
                        ZName           => { content => $filter_zname . '_sm_' . $field_zname },
                        SearchFieldName => { content => $filter_zname . '_sf_' . $field_zname },
                        Condition       => { content => 'ZZRF LIKE ?' },
                        PercentBefore   => { content => 'true' },
                        PercentAfter    => { content => 'true' },

                    }
                }
            );

            push(
                @{ $filter_def->{SearchField} },
                {
                    ZName => { content => $filter_zname . '_sf_' . $field_zname },
                    Field => { ZName   => { content => $filter_zname . '_f_' . $field_zname } },
                }
            );
        }
    }

    # add any order by
    if ( keys %{$order_by_fields} )
    {
        $filter_def->{OrderBy} = [];
        foreach my $order_by_table ( sort { $a->{min_sequence} <=> $b->{min_sequence} } values %{$order_by_fields} )
        {
            foreach my $order_by_field ( sort { $a->{sequence} <=> $b->{sequence} } @{ $order_by_table->{fields} } )
            {
                my $field_def   = $order_by_field->{xmldef};
                my $field_zname = $field_def->{ZName}->{content};

                push(
                    @{ $filter_def->{OrderBy} },
                    {
                        ZName       => { content => $filter_zname . '_orderby_' . $field_zname },
                        TargetField => { ZName   => { content => $filter_zname . '_rf_' . $field_zname } },
                        sequence => $order_by_field->{sequence},
                    }
                );
            }
        }
    }

    return $filter_def;
}

1;