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

=head1 NAME

Zymonic::Toolkit::Superset - Implements methods needed for integrating Superset.

=head1 SYNOPSIS

Tools to allow Superset and Zymonic to function together

=head1 DESCRIPTION

See manual/long descriptions below.

=head1 USAGE

See manual/long descriptions below.

=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::Superset;

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::Auth;
use Zymonic::Config;
use Zymonic::AppServer;
use Zymonic::Utils qw(random_string death_handler random_base64_string);
use Zymonic::Filter;

use IPC::SysV qw(IPC_CREAT S_IRWXU);
use IPC::SharedMem;
use Digest::MD5 qw(md5_hex);
use Data::Dumper;
use POSIX qw(strftime);
use Time::HiRes qw(time);

use Zymonic;
use Exception::Class (
    'Zymonic::Exception::Toolkit::Superset' => {
        isa         => 'Zymonic::Exception::Toolkit',
        fields      => [],
        description => 'Superset Toolkit related exceptions'
    },
    'Zymonic::Exception::Toolkit::Superset::file_error' => {
        isa         => 'Zymonic::Exception::Toolkit',
        fields      => [ "file", "error" ],
        description => 'Superset Toolkit related exceptions'
    },
    'Zymonic::Exception::Toolkit::Superset::seteuid' => {
        isa         => 'Zymonic::Exception::Toolkit',
        fields      => [ "error", "user" ],
        description => 'seteuid failed'
    }
);

our $DEFINITION = {
    ShortDescription => {
        content => qq(
The Superset module contains the various commands which provide functionality for integrating Superset and Zymonic.
)
    },
    LongDescription => {
        div => [ { p => { content => qq(TODO) } }, ]
    },
    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' },
        },
        {
            'ZName'            => { 'content' => 'hostname' },
            'DisplayName'      => { 'content' => 'Hostname' },
            'ShortDescription' => {
                'content' =>
                  "The hostname that the Superset server should use to connect to the Zymonic system's OAuth service."
            },
            'RequiredField' => { content => 'true' },
        },
        {
            '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' => '/.,-_' },
        },
        {
            'ZName'            => { 'content' => 'username' },
            'DisplayName'      => { 'content' => 'Username' },
            'ShortDescription' => { 'content' => 'The username of the person that the command is being run for.' },
            'RequiredField'    => { content   => 'true' },

        },
        {
            'ZName'            => { 'content' => 'password' },
            'DisplayName'      => { 'content' => 'Password' },
            'NoLog'            => { 'content' => 'true' },
            'ShortDescription' => {
                    'content' => 'The username of the person that the command is being run for. '
                  . 'Entering !, instead of the password will cause a password prompt to appear once the command has been started.'
            },
        },
        {
            'ZName'            => { 'content' => 'nodebugger' },
            'DisplayName'      => { 'content' => 'Disable debug logging by superset' },
            'ShortDescription' => {
                'content' =>
                  'Disables the extended logging generated by superset - set to true, yes or y to have no debugs'
            },
        },
        {
            'ZName'            => { 'content' => 'clean' },
            'DisplayName'      => { 'content' => 'Clean update' },
            'ShortDescription' => {
                'content' => 'If set will update all sources cleanly, i.e. from scratch.'
            },
        },
    ],
    commands => {
        install_superset => {
            ShortDescription => { content => 'Installs superset.' },
            fields           => [],
            LongDescription  => {
                div => [
                    {
                        p => {
                            content =>
qq(The install_superset command will attempt to use Python's PIP system to install Apache Superset.)
                        }
                    },
                    { p => { content => qq(The install_superset command will add the following lines to .bashrc:) } },
                    {
                        pre => {
                            content => qq(PATH=\${PATH}:/home/superset/.local/bin
export LC_ALL=C.UTF-8
export LANG=C.UTF-8 )
                        }
                    },
                    { p => { content => qq(It will then attempt to run the following sequence of commands:) } },
                    {
                        pre => {
                            content => qq(bash bashrc 
pip install --user apache-superset 
pip install --user mysqlclient 
pip install --user sysv_ipc 
superset db upgrade 
superset fab create-admin 
superset init)
                        }
                    },
                    {
                        p => {
                            content =>
qq(Note that the 'create-admin' command will create an admin that will only work until the OAuth connections are established.)
                        }
                    },
                ]
            },
        },
        connect_superset => {
            ShortDescription => { content => 'Connects superset to a Zymonic System.' },
            fields          => [ 'system', 'configdir', 'hostname', 'username', 'password' ],
            LongDescription => {
                div => [
                    {
                        p => {
                            content =>
qq(The connect_superset command will configure an existing superset install to connect to a Zymonic System. The
                specific steps are: )
                        }
                    },
                    {
                        ul => {
                            li => [
                                { content => qq(Adds Superset system options to the Zymonic system) },
                                {
                                    content =>
qq(Creates a Superset configuration file in superset_config.py in the Superset bin dir)
                                },
                                {
                                    content =>
qq(Adds or updates an OAuth client connection to the Zymonic System and adds the details to the Superset config file)
                                },
                                {
                                    content =>
                                      qq(Adds DB, role and permission entries to the SQLite Superset config database)
                                },
                            ]
                        }
                    }
                ]
            },
        },
        start_superset => {
            ShortDescription => {
                content =>
                  'Starts superset (after first storing the DB password in memory using the specified Zymonic system).'
            },
            fields          => [ 'system', 'configdir', 'nodebugger' ],
            LongDescription => {
                div => [
                    {
                        p => {
                            content =>
qq(The start_superset command can be used to start Superset, or its output can be copied to add to a start up command. 
                However, bear in mind if using a separate script that the 'store_db_password' will still need running to put the DB password into shared memory.)
                        }
                    },
                ]
            },
        },
        store_db_password => {
            ShortDescription => { content => 'Stores the DB password in memory for python to retrieve.' },
            fields          => [ 'system', 'configdir', ],
            LongDescription => {
                div => [
                    {
                        p => {
                            content =>
qq(The store_db_password command puts the DB password into shared memory so it does not need to be stored in the
                Superset configuration in plain text.)
                        }
                    },
                ]
            },
        },
        update_sources => {
            ShortDescription => { content => 'Updates all superset data sources.' },
            fields => [ 'system', 'configdir', 'clean', ],

            # this can be run from the gui, from AddSupersetSource, so needs explicit permissions
            RolePermission => [
                { ZName => { content => 'zz_superset_source_admin_autorolepermission' } },
                { ZName => { content => 'zz_superset_source_write_autorolepermission' } },
            ],
            LongDescription => {
                div => [
                    {
                        p => {
                            content =>
qq(update_sources searches the zz_superset_source table and runs the 'denormalise' method on every filter that has
                not been denormalised within the 'update frequency'.)
                        }
                    },
                ]
            },
        },
        update_roles => {
            ShortDescription => { content => 'Updates the Superset roles to match Zymonic configured roles.' },
            fields          => [ 'system', 'configdir', 'username', 'password' ],
            LongDescription => {
                div => [ { p => { content => qq(Adds or updates the Superset roles to include the Zymonic roles) } }, ]
            },
        },
    },
};

#################### 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 config

 Usage     : $self->config()
 Purpose   : Returns (and loads if necessary) the config
 Returns   : See purpose.
 Argument  : system definition object (if already loaded)
 Throws    : nothing
 Comment   : 

See Also   :

=cut

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

sub config
{
    my $self = shift;

    # set system if not set
    unless ($Zymonic::system)
    {
        my $system_name = $Zymonic::session->get_field('system');
        $Zymonic::system = $system_name->value();
    }

    # load config if not present
    unless ( $Zymonic::ZCONFIG{$Zymonic::system} )
    {
        my $config_dir = $Zymonic::session->get_field('configdir')->value() || '/etc/zymonic';

        $Zymonic::ZCONFIG{$Zymonic::system} = Zymonic::Config->new(
            parent      => $self,
            system_name => $Zymonic::system,
            config_dir  => $config_dir,
            ip_address  => '127.0.0.1',
        );

        # ensure auth is setup
        $Zymonic::ZCONFIG{$Zymonic::system}->{auth} = Zymonic::Auth->new(
            parent     => $Zymonic::ZCONFIG{$Zymonic::system},
            config     => $Zymonic::ZCONFIG{$Zymonic::system},
            DB         => $Zymonic::ZCONFIG{$Zymonic::system}->{DB},
            ip_address => $Zymonic::ZCONFIG{$Zymonic::system}->{ip_address} || '',
        );

        # set details on self for base functionality to work
        # e.g. for get_table to find these things
        $self->{config} = $Zymonic::ZCONFIG{$Zymonic::system};
        $self->{auth}   = $Zymonic::ZCONFIG{$Zymonic::system}->{auth};
        $self->{DB}     = $Zymonic::ZCONFIG{$Zymonic::system}->{DB};

        # set values on session as sometimes get_table call will come from session and need those values
        if ($Zymonic::session)
        {
            $Zymonic::session->{config} = $Zymonic::ZCONFIG{$Zymonic::system};
            $Zymonic::session->{auth}   = $Zymonic::ZCONFIG{$Zymonic::system}->{auth};
            $Zymonic::session->{DB}     = $Zymonic::ZCONFIG{$Zymonic::system}->{DB};
        }

        # if incoming credentials then login
        my $username = $self->get_param('username');
        my $password = $self->get_param('password') || '';
        if ($username)
        {
            my $auth_result =
              $self->{auth}->inline_auth( '', { username => $username, password => $password } );
            unless ( $auth_result && $auth_result->{result} eq 'PASS' )
            {
                $self->log( "Authentication Failed: " . Dumper($auth_result) );
                exit(1);
            }
        }
    }

    return $Zymonic::ZCONFIG{$Zymonic::system};
}

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

=head2 install_superset

 Usage     : Called from Toolkit
 Purpose   : 
 Returns   : 
 Argument  : 
 Throws    : nothing
 Comment   : 

See Also   : 

=cut

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

sub install_superset
{
    my $self = shift;

    my $home_dir = $ENV{"HOME"};

    open( BRC, ">>", $home_dir . "/.profile" );
    print BRC "# Superset/PIP config - added by Zymonic\n";
    print BRC "PATH=\${PATH}:$home_dir/.local/bin\n";
    print BRC "export LC_ALL=C.UTF-8\n";
    print BRC "export LANG=C.UTF-8\n";
    print BRC "export FLASK_APP=superset\n";
    close(BRC);

    $self->log( "Updated " . $home_dir . "/.profile with LANG and PATH changes" );

    my @pip_modules = q(wheel apache-superset[cors] mysqlclient sysv_ipc dataclasses authlib pymysql jmespath);

    my @commands = (
        ( map { "source $home_dir/.profile && pip install --user " . $_ } @pip_modules ),
        "source $home_dir/.profile && superset db upgrade",
        "source $home_dir/.profile && superset init",
    );

    $self->log( "About to run: " . join( "\n", @commands ) );

    foreach my $command (@commands)
    {
        system($command);
    }

}

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

=head2 connect_superset

 Usage     : Called from Toolkit
 Purpose   : 
 Returns   : 
 Argument  : 
 Throws    : nothing
 Comment   : 

See Also   : 

=cut

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

sub connect_superset
{
    my $self = shift;

    if ( $self->config->get_def( 'Table', 'zz_superset_charts', 'no error' ) )
    {

        # Add superset host options to system
        $self->add_superset_options;

        # Check for presence of supersetconfig.py and create if
        # missing
        my $supersetconfig_file = $self->_app_server()->get_opts->{superset_bin} . "/superset_config.py";
        $self->create_supersetconfig($supersetconfig_file) unless ( -e $supersetconfig_file );

        # add an oauth source
        $self->add_oauth($supersetconfig_file);

    # Add the DB connection (will do directly in SQLite to avoid complication of getting 'master' superuser credentials)
        $self->add_superset_db;

        # Create roles for the system (can be done with direct SQL)
        # and SQL inserts on the superset SQLite DB
        # https://apache-superset.readthedocs.io/en/0.35.2/security.html
        $self->update_roles;

        # Print the Apache reverse pass proxy config
        $self->print_apache_config;

    }
    else
    {
        $self->log(
"Superset charts table not detected - link in ZymonicSuperset.xml, config_build and rerun the connect_superset command"
        );
    }
}

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

=head2 print_apache_config

 Usage     : $self->print_apache_config
 Purpose   : Prints the apache reverse pass proxy configuration
 Returns   : see purpose
 Argument  : nothing
 Throws    : nothing
 Comment   : This is less than ideal due to superset not having
 a mechanism to set a base url - it looks like its coming slowly
 but not there yet - once its done then it should only need one
 ProxyPass and one ProxyPassReverse line.

See Also   : 

=cut

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

sub print_apache_config
{
    my $self = shift;

    my $proxypath = $self->_app_server()->get_opts->{superset_proxy_path};
    my $port      = $self->_app_server()->get_opts->{superset_port};
    my $host      = $self->_app_server()->get_opts->{superset_host};

    my $config = <<END
  # https://github.com/komoot/superset-reverse-nginx-example/blob/master/nginx/nginx.conf
    SSLProxyEngine on 
    SSLProxyVerify none 
    SSLProxyCheckPeerCN off
    SSLProxyCheckPeerName off
    SSLProxyCheckPeerExpire off
    ProxyPass /$proxypath https://$host:$port
    ProxyPassReverse /$proxypath https://$host:$port
    ProxyPass /superset https://$host:$port/superset
    ProxyPassReverse /superset https://$host:$port/superset
    ProxyPass /static https://$host:$port/static
    ProxyPassReverse /static https://$host:$port/static
    ProxyPass /api https://$host:$port/api
    ProxyPassReverse /api https://$host:$port/api
    ProxyPass /tablemodelview https://$host:$port/tablemodelview
    ProxyPassReverse /tablemodelview https://$host:$port/tablemodelview
    RequestHeader add X-Script-Name /analytics
    ProxyPreserveHost On
  
END
      ;

    $self->log("Apache Proxy Config:\n$config");

}

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

=head2 add_superset_options

 Usage     : $self->add_superset_options
 Purpose   : Adds superset options to the system being connected
 Returns   : see purpose
 Argument  : nothing
 Throws    : nothing
 Comment   : 

See Also   : 

=cut

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

sub add_superset_options
{
    my $self = shift;

    $self->config->sys_opt( 'superset_port',       $self->_app_server()->get_opts->{superset_port} );
    $self->config->sys_opt( 'superset_host',       $self->_app_server()->get_opts->{superset_host} );
    $self->config->sys_opt( 'superset_proxy_path', $self->_app_server()->get_opts->{superset_proxy_path} );
    $self->config->sys_opt( 'superset_sqlite',     $self->_app_server()->get_opts->{superset_sqlite} );
    $self->config->sys_opt( 'zz_oauth_enabled',    'true' );

    $self->log("Added/Updated Superset and OAuth system options in Zymonic.");
}

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

=head2 sqlalchemy_uri

 Usage     : $self->sqlalchemy_uri
 Purpose   : Returns the sqlalchemy URI
 Returns   : see purpose
 Argument  : nothing
 Throws    : nothing
 Comment   : 

See Also   : 

=cut

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

sub sqlalchemy_uri
{
    my $self               = shift;
    my %driver_translation = (
        "MariaDB" => "mysql+pymysql",
        "mariadb" => "mysql+pymysql",
        "MySQL"   => "mysql+pymysql",
        "mysql"   => "mysql+pymysql",
    );

    my $sqlalchemy_uri =
        $driver_translation{ $self->config->denormalised_db_details->{dbdriver} } . "://"
      . $self->config->denormalised_db_details->{dbusername}
      . ":XXXXXXXXXX@"
      . $self->config->denormalised_db_details->{dbhost} . "/"
      . $self->config->denormalised_db_details->{dbname};
}

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

=head2 add_superset_db

 Usage     : $self->add_superset_db
 Purpose   : Adds an entry to the superset DB for the system
 Returns   : nothing
 Argument  : nothing
 Throws    : nothing
 Comment   : Creates and entry in the db table of Superset's 
 SQLite database. It also creates entries in -
  ab_view_menu - the list of 'objects' that can be seen
  ab_permission_view - the permissions associated with 
   each thing that can be viewed.
  It uses the ab_permission table to get the ids of the required
   permissions.

See Also   : 

=cut

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

sub add_superset_db
{
    my $self = shift;

    my $dbrec = $self->superset_db->run_query(
        {
            string => "SELECT * FROM dbs WHERE database_name = ?",
            params => [ $self->config->{dbname} ]
        }
    );

    my $db_id;

    # create DB entry code
    my %dbparams = (

        #	created_on DATETIME NOT NULL, - same format as MySQL - maybe use NOW() ?
        created_on => $self->superset_db->timestamp,

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

        #	id INTEGER NOT NULL,
        #	database_name VARCHAR(250) NOT NULL,
        database_name => $self->config->denormalised_db_details->{dbname},

        #	sqlalchemy_uri VARCHAR(1024) NOT NULL,  mysql+pymysql://md5:XXXXXXXXXX@dbserver/md5_ztsm
        sqlalchemy_uri => $self->sqlalchemy_uri,

        #	created_by_fk INTEGER,  1 - entry in ab_user - may need to leave null.
        #	changed_by_fk INTEGER,  1 - entry in ab_user - may need to leave null.
        #	password BLOB, - leave null (or try and recreate SQLAlchemy EncryptedText method)
        #	cache_timeout INTEGER, - empty
        #	extra TEXT,
        extra => '
{
    "metadata_params": {},
    "engine_params": {},
    "metadata_cache_timeout": {},
    "schemas_allowed_for_csv_upload": []
}',

        #	select_as_create_table_as BOOLEAN, 0
        select_as_create_table_as => 0,

        #	allow_ctas BOOLEAN, 0
        allow_ctas => 0,

        #	expose_in_sqllab BOOLEAN, 1
        expose_in_sqllab => 1,

        #	force_ctas_schema VARCHAR(250), EMPTY
        #	allow_run_async BOOLEAN, 0
        allow_run_async => 0,

        #	allow_dml BOOLEAN, 0
        allow_dml => 0,

        #	verbose_name VARCHAR(250), EMPTY
        #	impersonate_user BOOLEAN, 0
        impersonate_user => 0,

        #	allow_multi_schema_metadata_fetch BOOLEAN, 0
        # field now deprecated in v2.1 of Superset
        # allow_multi_schema_metadata_fetch => 0,

        #	allow_csv_upload BOOLEAN DEFAULT 1 NOT NULL, 0
        # allow_file_upload => 0,
        # It appears that superset are changing the name of this
        # field in the latest version - will leave it unset for
        # the moment.

        #	encrypted_extra BLOB, EMPTY
        #	server_cert BLOB, EMPTY
        #	allow_cvas BOOLEAN, EMPTY
    );
    if ( scalar( @{$dbrec} ) > 0 )
    {
        $db_id = $dbrec->[0]->{id};
        $self->log("Superset entry for Zymonic DB already exists - updating");

        $self->superset_db->run_statement(
            {
                string => "UPDATE dbs SET " . join( ", ", map { $_ . " = ? " } keys(%dbparams) ) . " WHERE id = ?",
                params => [ values(%dbparams),            $db_id ]
            }
        );
    }
    else
    {

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

        $db_id = $self->superset_db->last_insert_id();

        # add permission entries to ab_view_menu
        # [db].(id:[id]) - DB access needed
        my $db_perm             = "[" . $self->config->denormalised_db_details->{dbname} . "].(id:" . $db_id . ")";
        my $database_access_rec = $self->superset_db->run_query(
            { string => "SELECT id FROM ab_permission WHERE name = ?", params => ["database_access"] } );
        $self->superset_db->run_statement(
            { string => "INSERT INTO ab_view_menu (name) VALUES (?)", params => [$db_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 => [ $database_access_rec->[0]->{id}, $abvm_id ]
            }
        );

        # [db].[db] - schema access needed
        my $schema_perm = "["
          . $self->config->denormalised_db_details->{dbname} . "].["
          . $self->config->denormalised_db_details->{dbname} . "]";
        my $schema_access_rec = $self->superset_db->run_query(
            { string => "SELECT id FROM ab_permission WHERE name = ?", params => ["schema_access"] } );
        $self->superset_db->run_statement(
            { string => "INSERT INTO ab_view_menu (name) VALUES (?)", params => [$schema_perm] } );
        $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 => [ $schema_access_rec->[0]->{id}, $abvm_id ]
            }
        );

        $self->log("Superset entry for Zymonic DB created and permissions added.");

    }

    $self->config->sys_opt( 'superset_db_id', $db_id );

}

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

=head2 update_roles

 Usage     : $self->update_roles
 Purpose   : Adds an entry to the superset DB for the roles
 Returns   : nothing
 Argument  : nothing
 Throws    : nothing
 Comment   : TODO Consider adding a save extra (with an XML default?)
 and an update roles action to do this whenever a role is updated in 
 Zymonic.

See Also   : 

=cut

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

sub update_roles
{
    my $self = shift;

    # create role in DB
    # https://gist.github.com/pajachiet/62eb85805cee55053d208521e0bdaf13#file-init_superset_user_role-py

    my $roles = $self->config->{DB}->run_query(
        {
            string =>
"SELECT DISTINCT role FROM zz_role_permissions WHERE role_permission = 'zz_superset_charts_read_autorolepermission' AND allowed = 'Y'",
            params => [],
        }
    );

    my $master_system  = $self->_app_server()->get_opts->{superset_master_system};
    my $current_system = $self->config->{system_name};

    foreach my $role ( @{$roles} )
    {
        $self->add_superset_role(
            $self->config()->{system_name} . "_" . $role->{role},
            $master_system eq $current_system ? 'Admin' : 'Gamma'
        );
    }

}

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

=head2 add_superset_role

 Usage     : $self->add_superset_role($rolename)
 Purpose   : Adds an entry to the superset DB for a specific
 role
 Returns   : nothing
 Argument  : nothing
 Throws    : nothing
 Comment   : Creates or updates entries in the following Superset 
 SQLit database tables:
  ab_role - the list of roles
  ab_permission_view_role - which 'permission/view' combinations
   each role has access to.

See Also   : 

=cut

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

sub add_superset_role
{
    my $self      = shift;
    my $rolename  = shift;
    my $base_role = shift || 'Gamma';

    # lookup the role first in order to remove the permission_view_role entries
    my $role_rec =
      $self->superset_db->run_query( { string => "SELECT id FROM ab_role WHERE NAME = ?", params => [$rolename] } );

    if ( scalar( @{$role_rec} ) )
    {
        $self->superset_db->run_statement(
            {
                string => "DELETE FROM ab_role WHERE name = ?;",
                params => [$rolename],
            }
        );

        $self->superset_db->run_statement(
            { string => "DELETE FROM ab_permission_view_role WHERE role_id = ?", params => [ $role_rec->[0]->{id} ] } );

    }

    $self->superset_db->run_statement(
        {
            string => "INSERT INTO ab_role (name) VALUES (?);",
            params => [$rolename],
        }
    );

    $self->superset_db->run_statement(
        {
            string => "INSERT INTO ab_permission_view_role (role_id, permission_view_id)
        SELECT
            ab_role.id, ab_permission_view.id
        FROM
            ab_role,
            ab_permission_view
            LEFT JOIN ab_permission on ab_permission.id = ab_permission_view.permission_id
            LEFT JOIN ab_view_menu on ab_view_menu.id = ab_permission_view.view_menu_id
        WHERE
            ab_role.name = ? AND
            ab_permission.name = 'datasource_access' AND
            ab_view_menu.name LIKE ?
            
        UNION
        SELECT
            ab_role.id, ab_permission_view.id
        FROM
            ab_role,
            ab_permission_view
            LEFT JOIN ab_permission on ab_permission.id = ab_permission_view.permission_id
            LEFT JOIN ab_view_menu on ab_view_menu.id = ab_permission_view.view_menu_id
        WHERE
            ab_role.name = ? AND
            ab_permission.name = 'database_access' AND
            ab_view_menu.name LIKE ?;",
            params =>
              [ $rolename, "[" . $self->config->{dbname} . "]%%", $rolename, "[" . $self->config->{dbname} . "]%%" ],
        }
    );

    # Copy the base role
    $self->superset_db->run_statement(
        {
            string => "INSERT OR IGNORE INTO ab_permission_view_role (role_id, permission_view_id)
        SELECT
            ab_role.id, ab_permission_view_role.permission_view_id
        FROM
            ab_permission_view_role, ab_role
        JOIN
            ab_role AS ab_base_role ON ab_base_role.id = ab_permission_view_role.role_id
        WHERE 
            ab_role.name = ? AND ab_base_role.name = ?",
            params => [ $rolename, $base_role ],
        }
    );

    $self->log("Added superset role for $rolename");
}

#################### 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
    my $sqlitefile = $self->_app_server()->get_opts->{superset_sqlite};

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

    return $self->{superset_db};

}

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

=head2 add_oauth

 Usage     : $self->add_oauth($file)
 Purpose   : adds oauth configuration to a superset config file
 Returns   : nothing
 Argument  : superset config filename
 Throws    : nothing
 Comment   : 

See Also   : 

=cut

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

sub add_oauth
{
    my $self = shift;
    my $file = shift;

    my $system = $self->config()->{system_name};

    #  update the file
    open( SCOUT, ">", $file . ".new" )
      or Zymonic::Exception::Toolkit::Superset::file_error->throw(
        file  => $file,
        error => $@,
      );
    open( SCIN, "<", $file ) or Zymonic::Exception::Toolkit::Superset::file_error->throw(
        file  => $file,
        error => $@,
    );

    my $providers_detected     = 0;
    my $currentconfig_detected = 0;
    my $first_provider         = 1;
    my $current_provider       = '';

    while (<SCIN>)
    {
        if ( $_ =~ /OAUTH_PROVIDERS/ )
        {
            $providers_detected = 1;
            print SCOUT $_;
        }
        elsif ( $_ =~ /# PROVIDER: (.*)$/ )
        {
            $current_provider = $1;
            $first_provider   = 0;
            print SCOUT $_;
            if ( $current_provider eq $system )
            {
                print SCOUT $self->oauth_providerconfig;
                $currentconfig_detected = 1;
            }
        }
        elsif ( $_ =~ /# END PROVIDER/ )
        {
            $current_provider = '';
            print SCOUT $_;
        }
        elsif ( $current_provider eq $system )
        {
            # print nothing
        }
        elsif ( $_ =~ /]; # END OF PROVIDERS/ )
        {
            unless ($currentconfig_detected)
            {
                print SCOUT ",\n" unless $first_provider;
                $first_provider = 0;
                print SCOUT "# PROVIDER: $system\n";
                print SCOUT $self->oauth_providerconfig;
                print SCOUT "# END PROVIDER\n";
            }
            print SCOUT $_;
        }
        else
        {
            print SCOUT $_;

        }
    }

    # remove the original file, move the new file and change the permissions
    unlink($file);
    rename( $file . ".new", $file );
    my $uid = getpwnam( $self->_app_server()->get_opts->{superset_user} )  || -1;
    my $gid = getgrnam( $self->_app_server()->get_opts->{superset_group} ) || -1;

    my $count = chown( $uid, $gid, $file );
}

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

=head2 oauth_providerconfig

 Usage     : $self->oauth_providerconfig($file)
 Purpose   : Returns the oauth provider configuration for the 
  current system.
 Returns   : see purpose
 Argument  : nothing
 Throws    : nothing
 Comment   : 

See Also   : 

=cut

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

sub oauth_providerconfig
{
    my $self = shift;

    my $system = $self->config()->{system_name};

    # update/create the client record (we hash the password
    # so we need to at least set a new password each time this
    # runs)
    my $client_secret = random_string( 20, ( 'A' .. 'Z' ), ( 'a' .. 'z' ), ( 0 .. 9 ) );
    my $oauth_client_table = $self->config->get_table( 'zz_oa_t_clients', { config => $self->{config} } );

    my @results = $oauth_client_table->save_records(
        [
            {
                zz_oauth_client_name      => 'Superset',
                zz_oauth_client_secret_tc => $client_secret,
                zz_oauth_client_id_tc     => undef,
            }
        ],
        { zz_oauth_client_name => 1 }
    );

    my $client_id = $results[0]->{client_id};

    my $hostname_f = $Zymonic::session->get_field('hostname');
    my $hostname   = $hostname_f->value;

    $self->log("Adding OAUth connectivity for $system into Superset");

    return <<END
    {
       'name': '$system',
       'icon': 'sign-in-alt',
       'token_key': 'access_token', 
       'remote_app': {
            'api_base_url': 'https://$hostname/zymonicmp/$system/oauth/',
            'request_token_params': {
              'scope': ''
            },
            'request_token_url': None,
            'access_token_url': 'https://$hostname/zymonicmp/$system/oauth/token',
            'authorize_url': 'https://$hostname/zymonicmp/$system/oauth/authorize',
            'client_id': '$client_id',
            'client_secret': '$client_secret'
        }
    }
END
}

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

=head2 create_supersetconfig

 Usage     : $self->create_supersetconfig($file)
 Purpose   : Creates a superset config file
 Returns   : nothing
 Argument  : superset config filename
 Throws    : nothing
 Comment   : 

See Also   : 

=cut

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

sub create_supersetconfig
{
    my $self = shift;
    my $file = shift;

    my $master_system = $self->_app_server()->get_opts->{superset_master_system};
    my $superset_log  = $self->_app_server()->get_opts->{superset_log} . "/superset.log";

    # create the file
    open( SC, ">", $file ) or Zymonic::Exception::Toolkit::Superset::file_error->throw(
        file  => $file,
        error => $@,
    );

    my $secret_key = random_base64_string(32);

    print SC<<END
import os
import logging
import sysv_ipc
import hashlib
from flask_appbuilder.security.manager import AUTH_OID, AUTH_REMOTE_USER, AUTH_DB, AUTH_LDAP, AUTH_OAUTH
basedir = os.path.abspath(os.path.dirname(__file__))

SECRET_KEY = '$secret_key'

PREVENT_UNSAFE_DB_CONNECTIONS = False

ROW_LIMIT = 5000

AUTH_TYPE = AUTH_OAUTH

AUTH_USER_REGISTRATION = True

AUTH_USER_REGISTRATION_ROLE_JMESPATH = 'role_name'

ENABLE_CORS = True

OAUTH_PROVIDERS = [
]; # END OF PROVIDERS

from superset.security import SupersetSecurityManager

class CustomSecurityManager(SupersetSecurityManager):

    def __init__(self, appbuilder):
         super(CustomSecurityManager, self).__init__(appbuilder)

    def get_oauth_user_info(self, provider, resp):
        me = self.appbuilder.sm.oauth_remotes[provider].get("userinfo")
        data = me.json()
        return {
            "username": data.get("username", ""),
            "first_name": data.get("given_name", ""),
            "last_name": data.get("family_name", ""),
            "email": data.get("email", ""),
            "role_name": provider + '_' + data.get("role", ""),
        }


CUSTOM_SECURITY_MANAGER = CustomSecurityManager

def zymonic_db_password(url):
    if url.drivername == 'sqlite':
        logging.debug("SQLite detected, returning");
        return ''

    # Convert url to string
    urlstring = url.__to_string__(False)

    # Calculate the key
    hashed = hashlib.md5(urlstring.encode('utf-8')).hexdigest()
    hashstart = hashed[0:6]
    key = int(hashstart,16)

    # Create shared memory object
    memory = sysv_ipc.SharedMemory(key)

    # Read value from shared memory
    memory_value = memory.read()

    # Find the 'end' of the string and strip
    a = memory_value.find(bytes('\\0','utf-8'))
    if a != -1:
        memory_value = memory_value[:a]

    memory_string = memory_value.decode("utf-8")

    return memory_string

SQLALCHEMY_CUSTOM_PASSWORD_STORE = zymonic_db_password

# https://github.com/apache/incubator-superset/issues/8169
LOG_FORMAT = "%(asctime)s:%(levelname)s:%(name)s%(message)s"
LOG_LEVEL = "DEBUG"

ENABLE_TIME_ROTATE = True
TIME_ROTATE_LOG_LEVEL = "DEBUG"
FILENAME = "$superset_log"
ROLLOVER = "midnight"
INTERVAL = 1 
BACKUP_COUNT = 10

# source: https://github.com/apache/incubator-superset/pull/1866#issuecomment-347310860
class ReverseProxied(object):

    def __init__(self, app):
        self.app = app

    def __call__(self, environ, start_response):
        script_name = environ.get('HTTP_X_SCRIPT_NAME', '')
        if script_name:
            environ['SCRIPT_NAME'] = script_name
            path_info = environ['PATH_INFO']
            if path_info.startswith(script_name):
                environ['PATH_INFO'] = path_info[len(script_name):]

        scheme = environ.get('HTTP_X_SCHEME', '')
        if scheme:
            environ['wsgi.url_scheme'] = scheme
        return self.app(environ, start_response)


ADDITIONAL_MIDDLEWARE = [ReverseProxied, ]

END
      ;
    close(SC);

    # set correct ownership and permissions on the file
    my $uid = getpwnam( $self->_app_server()->get_opts->{superset_user} )  || -1;
    my $gid = getgrnam( $self->_app_server()->get_opts->{superset_group} ) || -1;

    my $count = chown( $uid, $gid, $file );

    $self->log("Created Superset config file");
}

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

=head2 store_db_password

 Usage     : Called from Toolkit
 Purpose   : 
 Returns   : 
 Argument  : 
 Throws    : nothing
 Comment   : 

See Also   : 

=cut

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

sub store_db_password
{
    my $self = shift;

    # get password
    my $dbpass = $self->config->denormalised_db_details->{dbpassword};

    # generate a key
    my $url   = $self->sqlalchemy_uri;
    my $md5   = md5_hex($url);
    my $start = substr( $md5, 0, 6 );
    my $int   = hex($start);

    # Store the password in memory as superset user
    my $uid = getpwnam( $self->_app_server()->get_opts->{superset_user} ) || -1;
    $> = $uid;

    if ( $> != $uid )
    {
        Zymonic::Exception::Toolkit::Superset::seteuid->throw(
            error => $!,
            user  => $self->_app_server()->get_opts->{superset_user}
        );
    }
    my $shm = IPC::SharedMem->new( $int, 50, IPC_CREAT | S_IRWXU );

    $shm->write( $dbpass, 0, length($dbpass) );

}

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

=head2 start_superset

 Usage     : Called from Toolkit
 Purpose   : 
 Returns   : 
 Argument  : 
 Throws    : nothing
 Comment   : 

See Also   : 

=cut

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

sub start_superset
{
    my $self = shift;

    my $debug = ( ( $self->get_param('nodebugger') || '' ) =~ /^(true|yes|y)/i ? '' : ' --debugger' );

    # Write DB password memory share key
    $self->store_db_password;

    my $superset_cert       = $self->_app_server()->get_opts->{superset_ssl_cert};
    my $superset_key        = $self->_app_server()->get_opts->{superset_ssl_key};
    my $superset_port       = $self->_app_server()->get_opts->{superset_port};
    my $superset_ip         = $self->_app_server()->get_opts->{superset_ip};
    my $superset_user       = $self->_app_server()->get_opts->{superset_user};
    my $superset_group      = $self->_app_server()->get_opts->{superset_group};
    my $superset_executable = $self->_app_server()->get_opts->{superset_bin} . "/superset";
    my $superset_log        = $self->_app_server()->get_opts->{superset_log};

    # create log dir if missing
    $self->_app_server()->_OS->make_dir( $superset_log, $superset_user, $superset_group );

    # generate a command line to run
    my $command_line =
        "sudo su - "
      . $superset_user
      . " -c 'export FLASK_APP=superset; export LC_ALL=C.UTF-8; export LANG=C.UTF-8; PATH=\${PATH}:/home/superset/.local/bin; "
      . $superset_executable . " run "
      . ( $superset_cert ? " --cert \"$superset_cert\" " : "" )
      . ( $superset_key  ? " --key \"$superset_key\" "   : "" )
      . " -h $superset_ip -p $superset_port --with-threads --reload $debug'";

    # run it - use a mode which detaches (exec?)
    $self->log( "Running: " . $command_line );
    exec($command_line);

}

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

=head2 update_sources

 Usage     : Called from Toolkit
 Purpose   : 
 Returns   : 
 Argument  : pass in source id if running directly
 Throws    : nothing
 Comment   : can also be called directly by passing in the source record to update

See Also   : 

=cut

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

sub update_sources
{
    my $self      = shift;
    my $source_id = shift;

    # get all the created sources in the Zymonic DB, limit by id if incoming
    my $config       = $self->config();
    my $source_table = $self->get_table('zz_superset_source');
    $source_table->get_all_records(
        (
            $source_id
            ? ( { where_clause => $source_table->{tablename} . ".id = ?", where_params => [$source_id] } )
            : ()
        )
    );

    # lock the source record before updating, to ensure nothing else is updating at the same time
    unless ( $self->{no_locking} )
    {
        $self->setup_locking();
        unless ( $source_table->lock_records( '', 'force' ) )
        {
            $self->log("Unable to lock source records, not updating");
            return;
        }
    }

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

    # grab max length of a source name to better format the output
    my $max_name_length = 1;
    map { $max_name_length = length( $_->{source_name} ) if length( $_->{source_name} ) > $max_name_length; }
      @{ $source_table->{records} };

    # run the update on them
    my @sources = @{ $source_table->{records} };
    while (@sources)
    {
        my $source = pop @sources;
        next if ( $source->{autocreated} || 0 ) == -1;
        my $update_date_field = $source->{update_dt_field} || $source->{primary_dt_field} || '';
        my $filter_zname      = $source->{source_filter};
        my $filter            = Zymonic::Filter->new(
            parent         => $self,
            zname          => $filter_zname,
            config         => $config,
            DB             => $self->{DB} || $config->{DB},
            auth           => $self->{auth} || $config->{auth},
            dnd_date_field => $update_date_field,
            ( $clean ? ( dnd_skip_cache => 'true' ) : () )
        );
        eval {
            my $since_update = 0;
            $since_update = time - $self->config->{DB}->db_time_to_epoch( $source->{source_last_updated} )
              if $source->{source_last_updated};

            if ( !$since_update or $since_update > ( $source->{update_freq} || 0 ) )
            {
                if (  !$clean
                    && $update_date_field
                    && ( $source->{incremental} || '' ) eq 'Y'
                    && !$source->{skip_incremental} )
                {
                    # search on everything since last update, within delay range
                    $filter->auto_search_report_field(
                        $update_date_field,
                        [ $source->{source_last_updated}, $source->{update_delay} || 0 ],
                        "ZZRF > ? AND TIME_TO_SEC(TIMEDIFF(NOW(), ZZRF)) > ?",
                        '', 'raw_value',
                    );
                    $filter->{dnd_incremental} = 'true';
                }

                my $start = time();
                $filter->denormalise();
                my $end = time();
                $self->log(
                    sprintf(
                        "Updated %*s - %6d record(s) in %.2fs",
                        $max_name_length, $source->{source_name},
                        ( $filter->{dnd_updated} || 0 ), ( $end - $start ),
                      )
                      . (
                        $filter->{dnd_incremental}
                        ? (
                            $filter->{dnd_last_updated}
                            ? " ($source->{source_last_updated} - $filter->{dnd_last_updated})"
                            : " (checked from $source->{source_last_updated})"
                          )
                        : ''
                      )
                );
            }
            else
            {
                $self->log( $source->{source_name}
                      . " not updated - last update was "
                      . $source->{source_last_updated}
                      . ", minimum seconds between updates is "
                      . $source->{update_freq} );
            }
            1;
        }
          or do
        {
            my $exception = $@;
            if (   $exception
                && ref($exception)
                && $exception->isa('Zymonic::Exception::QueryBuilder::DNDTableStructureChanged') )
            {
                $self->log( $source->{source_name}
                      . " not updated - Unable to do an incremental update, data structure has changed." );
                if ( ( $source->{allow_full_fallback} || '' ) eq 'Y' )
                {
                    $source->{skip_incremental} = 'true';
                    push( @sources, $source );
                }
                else
                {
                    $self->log( $source->{source_name}
                          . " not allowed to do a full update as fallback. Will need data structure manually correcting to retain data."
                    );
                }
            }
            else
            {
                my $error = death_handler( $exception, '', 'return' );
                $self->log(
                    "Failed to update " . $source->{source_name} . ", see system error " . $error->{reference} );
            }
        };
    }

    # unlock records after updates
    unless ( $self->{no_locking} )
    {
        $source_table->unlock_records();
    }
}

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

=head2 _app_server

 Usage     : $self->_app_server()
 Purpose   : returns the app server object
 Returns   : the Zymonic::AppServer object
 Argument  : nothing
 Throws    : nothing
 Comment   : 

See Also   :

=cut

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

sub _app_server
{
    my $self = shift;
    my $conf_dir = shift || '/etc/zymonic/';

    $self->{_app_server} = Zymonic::AppServer->new( config_dir => $conf_dir, )
      unless $self->{_app_server};
    return $self->{_app_server};

}

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

=head2 gui_commands

 Usage     : @commands = $mo->gui_commands;
 Purpose   : Returns available GUI commands 
 Returns   : nothing
 Argument  : nothing
 Throws    : nothing
 Comment   : 

See Also   :

=cut

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

sub gui_commands
{
    my $self = shift;
    return ( $self->SUPER::gui_commands(), qw(update_sources) );
}

1;