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

=head1 NAME

Zymonic::Action::AddSupersetSource - Zymonic Workflow System Action module

=head1 SYNOPSIS

Adds a Superset Source to the Superset Configuration and runs the 
initial denormalisation of the data.

=head1 DESCRIPTION

Adds a Superset Source to the Superset Configuration and runs the 
initial denormalisation of the data.

=head1 USAGE

It is not expected that this class would be used directly - only called from
the ZymonicSuperset.xml integration.

The following ClassOption Schema should be used:
  <xs:element name="ClassOptions">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="SourceID" type="FieldOrStatic"
        minOccurs="0" maxOccurs="1" />
        <xs:element name="Filter" type="FieldOrStatic"
        minOccurs="0" maxOccurs="1" />
        <xs:element name="DateTimeField" type="FieldOrStatic"
        minOccurs="0" maxOccurs="1" />
      </xs:sequence>
    </xs:complexType>
  </xs:element>
  <xs:complexType name="FieldOrStatic">
    <xs:choice>
     <xs:element ref="Field">
     <xs:element name="StaticValue" type="xs:string">
    </xs:choice>
  </xs:complexType>

=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::Action::AddSupersetSource;

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::Action";
use Zymonic;
use Zymonic::Utils qw(get_array debug xml_escape debug_function_start debug_function_stop);

use Zymonic;
use Exception::Class (
    'Zymonic::Exception::Action::AddSupersetSource' => {
        isa         => 'Zymonic::Exception::Action',
        fields      => [],
        description => 'Problem with AddSupersetSource Action.'
    },

);

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

=head2 init

 Usage     : N/A
 Purpose   : The init method is called by the object constructor
 to initialise the object fields.
 Returns   : nothing
 Argument  : nothing
 Throws    : Zymonic::Exception::Action
 Comment   :

See Also   :

=cut

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

sub init
{
    my $self = shift;

    $self->SUPER::init();

}

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

=head2 filter

 Usage     : $action->filter
 Purpose   : Returns the filter
 Returns   : See purpose
 Argument  : nothing
 Throws    : 
 Comment   : nothing
 
=cut

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

sub filter
{
    my $self = shift;

    unless ( $self->{filter} )
    {
        # Load the filter
        $self->{filter} = Zymonic::Filter->new(
            parent => $self,
            zname  => $self->{filter_field_zname},
            config => $self->{config},
            DB     => $self->{DB},
            auth   => $self->{auth},
        );
    }

    return $self->{filter};
}

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

=head2 do

 Usage     : $action->do
 Purpose   : Performs the action, i.e. sends a HTTP request
 Returns   : nothing
 Argument  : nothing
 Throws    : 
 Comment   : nothing
 
=cut

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

sub do
{
    my $self                  = shift;
    my $fake                  = shift || '';
    my $no_display_attributes = shift || 0;

    my $result = $self->SUPER::do( $fake, $no_display_attributes );

    $self->{filter_field_zname} = $self->static_or_field_value('Filter');
    $self->{date_time_field} = $self->static_or_field_value( 'DateTimeField', 'optional' );

    if ( $self->{success} && $self->{success} eq 'true' )
    {
        $result->{success} = $self->{success} = 'false';

        # call toolkit to update the source now
        my $toolkit_result = $self->run_toolkit(
            'Superset', 'update_sources', [ $self->static_or_field_value('SourceID') ],

            # no locking, as calling transition will handle locks
            { no_locking => 'true', }
        ) || '';
        $self->add_message_to_result( $result, $toolkit_result );

        my $table_id =
          $self->create_superset_table( $self->{config}->sys_opt('superset_db_id'), $self->{date_time_field} );

        # Update the source table with the correct id
        $self->{DB}->run_statement(
            {
                string => "UPDATE zz_superset_source SET table_id = ? WHERE source_filter = ?",
                params => [ $table_id, $self->{filter_field_zname} ]
            }
        );

        $self->{success} = 'true';

        my $src =
            "https://"
          . $self->{config}->sys_opt('superset_host') . ":"
          . $self->{config}->sys_opt('superset_port')
          . "/login/"
          . $self->{config}->{system_name}
          . "?next=";

        # Do this manually to not encode the entities.
        unless ( $result->{MessageWithLink} )
        {
            $result->{MessageWithLink} = [];
        }
        push(
            @{ $result->{MessageWithLink} },
            {
                content => $self->ll("Superset Source added - Visit to open Superset."),
                path    => $src,
            }
        );
    }

    $result->{success} = $self->{success};
    return $result;
}

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

=head2 create_superset_column

 Usage     : $action->create_superset_column
 Purpose   : Creates a superset column
 Returns   : nothing
 Argument  : superset table id, field zname
 Throws    : 
 Comment   : nothing
 
=cut

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

sub create_superset_column
{
    my $self              = shift;
    my $table_id          = shift;
    my $field_ref         = shift;
    my $field_column_name = shift;

    # table_columns

    my %values = (

        #	created_on DATETIME NOT NULL,
        created_on => $self->superset_db->timestamp,

        #	changed_on DATETIME NOT NULL,
        changed_on => $self->superset_db->timestamp,

        #	table_id INTEGER,
        table_id => $table_id,

        #	column_name VARCHAR(255) NOT NULL,
        column_name => $field_ref->{zname},

        #	is_dttm BOOLEAN, TODO - add a mechanism to DateTime to add a flag to its ref
        is_dttm => ( $field_ref->{class} eq 'DateTime' ? 1 : 0 ),

        #	is_active BOOLEAN,
        is_active => 1,

        #	type VARCHAR(32),
        type => uc( $field_ref->{field_type} )
          . (
            ( $field_ref->{field_type} =~ /timestamp|datetime|text|double|decimal|float/i ) ? ''
            : '('
              . (
                ( $field_ref->{max_length} ) ? $field_ref->{max_length}
                : $self->{DB}->default_max_length( $field_ref->{field_type} )
              )
              . ')'
          ),

        #	groupby BOOLEAN,
        groupby => 1,

        #	filterable BOOLEAN,
        filterable => 1,

        #	description TEXT,
        #	created_by_fk INTEGER,
        #	changed_by_fk INTEGER,
        #	expression TEXT,
        #	verbose_name VARCHAR(1024),
        verbose_name => $field_column_name,

        #	python_date_format VARCHAR(255),
    );
    my $column_rec = $self->superset_db->run_query(
        {
            string => 'SELECT id FROM table_columns WHERE column_name = ? AND table_id = ?',
            params => [ $field_ref->{zname}, $table_id ]
        }
    );

    if ( scalar( @{$column_rec} ) )
    {
        # Update the table record
        $self->superset_db->run_statement(
            {
                string => "UPDATE table_columns SET "
                  . join( ", ", map { $_ . " = ? " } keys(%values) )
                  . " WHERE id = ?",
                params => [ values(%values), $column_rec->[0]->{id} ]
            }
        );
    }
    else
    {

        $self->superset_db->run_statement(
            {
                string => "INSERT INTO table_columns ("
                  . join( ",", keys(%values) )
                  . ") VALUES ("
                  . join( ", ", map { "?" } keys(%values) ) . ")",
                params => [ values(%values) ]
            }
        );
    }

    $column_rec = $self->superset_db->run_query(
        {
            string => 'SELECT id FROM table_columns WHERE column_name = ? AND table_id = ?',
            params => [ $field_ref->{zname}, $table_id ]
        }
    );

    return $column_rec->[0]->{id};
}

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

=head2 create_superset_table

 Usage     : $action->create_superset_table
 Purpose   : Creates a superset table
 Returns   : nothing
 Argument  : database id and datetime column name
 Throws    : 
 Comment   : nothing
 
=cut

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

sub create_superset_table
{
    my $self = shift;

    my $database_id  = shift;
    my $datetime_col = shift;

    #tables

    my %values = (

        #	created_on DATETIME NOT NULL,
        created_on => $self->superset_db->timestamp,

        #	changed_on DATETIME NOT NULL,
        changed_on => $self->superset_db->timestamp,

        #	table_name VARCHAR(250) NOT NULL,
        table_name => $self->{filter_field_zname},

        #	main_dttm_col VARCHAR(250),
        main_dttm_col => $datetime_col,

        #	default_endpoint TEXT,
        #	database_id INTEGER NOT NULL,
        database_id => $database_id,

#	created_by_fk INTEGER,
#	changed_by_fk INTEGER,
#	"offset" INTEGER,
#	description TEXT,
#	is_featured BOOLEAN,
#	cache_timeout INTEGER,
#	schema VARCHAR(255),
#	sql TEXT,
#	params TEXT,
#	perm VARCHAR(1000), - TODO this is populated with [db name].[table name](id:[id]) - not sure why or what it relates to.
#	filter_select_enabled BOOLEAN,
#	fetch_values_predicate VARCHAR(1000),
#	is_sqllab_view BOOLEAN DEFAULT 0,
#	template_params TEXT, schema_perm VARCHAR(1000),
    );

    my $table_rec = $self->superset_db->run_query(
        {
            string => 'SELECT id FROM tables WHERE table_name = ?',
            params => [ $self->{filter_field_zname} ]
        }
    );

    my $table_id;

    if ( scalar( @{$table_rec} ) )
    {
        $table_id = $table_rec->[0]->{id};

        # Update the table record
        $self->superset_db->run_statement(
            {
                string => "UPDATE tables SET "
                  . join( ", ", map { $_ . " = ? " } keys(%values) )
                  . " WHERE table_name = ?",
                params => [ values(%values), $self->{filter_field_zname} ]
            }
        );
    }
    else
    {
        # do the insert
        $self->superset_db->run_statement(
            {
                string => "INSERT INTO tables ("
                  . join( ",", keys(%values) )
                  . ") VALUES ("
                  . join( ", ", map { "?" } keys(%values) ) . ")",
                params => [ values(%values) ]
            }
        );

        # get the table id NOTE last_insert_id should take a table object
        # but currently nothing actually uses it.
        $table_id = $self->superset_db->last_insert_id();
        my $source_perm =
            "["
          . $self->{config}->denormalised_db_details->{dbname} . "].["
          . $self->{filter_field_zname}
          . "].(id:"
          . $table_id . ")";

        # update the source with the perm
        $self->superset_db->run_statement(
            {
                string => "UPDATE tables SET perm = ? WHERE id = ?",
                params => [ $source_perm, $table_id ]
            }
        );

        # add the permissions to permission_view and the role
        # [perm] - needs datasource_access
        my $datasource_access_rec = $self->superset_db->run_query(
            { string => "SELECT id FROM ab_permission WHERE name = ?", params => ["datasource_access"] } );
        $self->superset_db->run_statement(
            { string => "INSERT INTO ab_view_menu (name) VALUES (?)", params => [$source_perm] } );
        my $abvm_id = $self->superset_db->last_insert_id();
        $self->superset_db->run_statement(
            {
                string => "INSERT INTO ab_permission_view (permission_id, view_menu_id) VALUES (?,?)",
                params => [ $datasource_access_rec->[0]->{id}, $abvm_id ]
            }
        );
        my $abpv_id = $self->superset_db->last_insert_id();

        $self->superset_db->run_statement(
            {
                string => "INSERT INTO ab_permission_view_role (role_id, permission_view_id) "
                  . "SELECT id, ? FROM ab_role WHERE name LIKE ?",
                params => [ $abpv_id, $self->{config}->{system_name} . "_%" ],
            }
        );

    }

    my @report_fields = $self->filter->get_report_fields('');

    my $qb = $self->filter->build_query_builder( "", \@report_fields, "denormalise" );

    my @column_ids = ();
    foreach my $denorm_field ( grep { !$_->{skip_in_sql} } values %{ $qb->{fields} } )
    {
        # skip hidden fields
        next if ( $denorm_field->{hidden} || '' ) eq 'true';
        my $display_name = $qb->denormalisedAS($denorm_field);

        my $column_id = $self->create_superset_column( $table_id, $denorm_field, $display_name );
        push( @column_ids, $column_id );
    }

    # Clear unused columns
    $self->superset_db->run_statement(
        {
            string => 'DELETE FROM table_columns WHERE table_id = ? AND id NOT IN('
              . join( ", ", map { "?" } @column_ids ) . ')',
            params => [ $table_id, @column_ids ]
        }
    );

    return $table_id;

}

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

=head2 superset_db

 Usage     : $self->superset_db->run_query(...)
 Purpose   : returns a Zymonic DB object connected to the superset
 sqlite DB
 Returns   : see purpose
 Argument  : nothing
 Throws    : nothing
 Comment   : 

See Also   : 

=cut

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

sub superset_db
{
    my $self = shift;

    # get option
    # TODO permissions on SQLite DB file need to allow for apache access
    my $sqlitefile = $self->{config}->sys_opt('superset_sqlite');

    $self->{superset_db} = Zymonic::DB->new(
        dbdriver      => 'SQLite',
        driver_string => 'dbi:SQLite:dbname=' . $sqlitefile,
    ) unless $self->{superset_db};

    return $self->{superset_db};

}

1;