package CohortExplorer::Datasource;

use strict;
use warnings;

our $VERSION = 0.01;

use CLI::Framework::Exceptions qw (:all);
use Exception::Class::TryCatch;
use Config::General;
use Carp;
use DBI;

my $CONFIG_FILE = '/home/abhishek/Desktop/datasource-config.properties';

#-------

sub initialise {

	my ( $class, $opts ) = @_;

	# --- VALIDATE DATASOURCE NAME USING datasource-config.properties ---

	my $param;

	# Get the configuration for the specified datasource
	eval {

		$param = {
			Config::General->new(
				-ConfigFile            => $CONFIG_FILE,
				-LowerCaseNames        => 1,
				-MergeDuplicateBlocks  => 1,
				-MergeDuplicateOptions => 1
			  )->getall()
		  }->{datasource}{ $opts->{datasource} }

	};

	if ( catch my $e ) {
		throw_app_init_exception($e);
	}

	throw_app_init_exception( error => "Invalid datasource '$opts->{datasource}'" ) unless ($param);

	throw_app_init_exception( error => "Parameter 'storage' is missing from datasource '$opts->{datasource}' configuration" ) unless ( $param->{storage} );

	eval {
		$param->{dbh} =
		  DBI->connect( $param->{dsn}, $param->{username}, $param->{password},
			{ PrintError => 0, RaiseError => 1 } );
	};

	if ( catch my $e ) {
		throw_app_init_exception( error => $e );
	}

	for (qw(dsn username password)) {

		# Remove DSN, username and password
		delete $param->{$_};
	}

	$param->{name} ||= $opts->{datasource};
	$param->{alias}   = $opts->{datasource};
	$param->{storage} = ucfirst lc $param->{storage};

	# Instantiate datasource
	my $target_pkg = 'CohortExplorer::Application::' . $param->{storage} . '::Datasource';

	eval "require $target_pkg";    # May or may not be preloaded

	my $obj = $target_pkg->new($param) or croak "Failed to instantiate datasource package '$target_pkg' via new(): $!";

	$obj->_process( $opts, );

	return $obj;
}

sub _process {

	my ( $datasource, $opts ) = @_;

	print STDERR "\n" . "Authenticating '$opts->{username}\@$opts->{datasource}' ..." . "\n\n" if ( $opts->{verbose} );

	my $response = $datasource->authenticate( $opts, );

	throw_app_init_exception( error => "Failed to authenticate '$opts->{username}\@$opts->{datasource}'" ) unless ($response);

	print STDERR
	  "Initializing application for '$opts->{username}\@$opts->{datasource}' "
	  . "\n"
	  if ( $opts->{verbose} );

	my $default = $datasource->get_default_param( $opts, $response );

	for ( keys %$default ) {
		$datasource->{$_} = $default->{$_};
	}

	# --- SET ENTITY_COUNT, VISIT_MAX, TABLES AND VARIABLES --- #

	require SQL::Abstract::More;    # May or may not be preloaded

	my $sqla = SQL::Abstract::More->new();

	my @attrib =
	  $datasource->type() eq 'standard'
	  ? qw(entity_count tables variables)
	  : qw(entity_count visit_max tables variables);

	my ( $stmt, @bind );

	for (@attrib) {
		eval {
			my $method = 'get_' . $_ . '_sql';
			( $stmt, @bind ) =
			  $sqla->select( %{ $datasource->$method($opts) } );
		};

		if ( catch my $e ) {
			throw_app_init_exception( error => $e );
		}

		if (/entity_count|visit_max/) {
			$datasource->set_single_value_attrib( $_, $stmt, @bind );
		}

		else {
			$datasource->set_multi_value_attrib( $_, $stmt, @bind );
		}

	}

	# ---SET VISIT VARIABLES--- #

	$datasource->set_visit_variables($opts) if ( $datasource->type() eq 'longitudinal' );
}

sub set_single_value_attrib {

	my ( $datasource, $attrib, $stmt, @bind ) = @_;

	eval {
		( $datasource->{$attrib} ) = $datasource->dbh()->selectrow_array( $stmt, undef, @bind );
	};
	if ( catch my $e ) {
		throw_app_init_exception( error => $e );
	}

	if ( !$datasource->{$attrib} || $datasource->{$attrib} == 0 ) {
		throw_app_init_exception(
			    error => "$attrib must be > 0 for datasource '"
			  . $datasource->name()
			  . "' with storage "
			  . $datasource->storage() );
	}
}

sub set_multi_value_attrib {

	my ( $datasource,, $attrib, $stmt, @bind ) = @_;

	my $sth;

	eval {
		$sth = $datasource->dbh()->prepare_cached($stmt);
		$sth->execute(@bind);
	};

	if ( catch my $e ) {
		throw_app_init_exception( error => $e );
	}

	my @columns = @{ $sth->{NAME} };
	my @rows = @{ $sth->fetchall_arrayref( [] ) };

	$sth->finish();

	require Tie::IxHash;
	tie %{ $datasource->{$attrib} }, "Tie::IxHash";   # Preserve order of values

	for my $row (@rows) {
		$datasource->{$attrib}{ $row->[0] } =
		  { map { $columns[$_] => $row->[$_] } 0 .. $#columns };
	}
}

sub new {

	return bless $_[1], $_[0];
}

sub set_visit_variables {

	my ( $datasource, $opts ) = @_;

	my @static_tables = @{ $datasource->static_tables() || [] };
	my $visit_max = $datasource->visit_max();
	for my $var ( keys %{ $datasource->variables() } ) {
		$var =~ /^([^\.]+)\..+$/;
		unless ( grep( /^$1/, @static_tables ) ) {
			for ( qw(any last), 1 .. $visit_max ) {
				push @{ $datasource->{visit_variables} }, "V$_.$var";
			}
		}
	}
}

sub DESTROY {

	my ($datasource) = @_;

	$datasource->dbh()->disconnect() if ( $datasource->dbh() );

}

sub AUTOLOAD {

	my ($datasource) = @_;

	our $AUTOLOAD;

	( my $attrib = lc $AUTOLOAD ) =~ s/.*:://;

	return $datasource->{$attrib} || undef;
}

#--------- SUBCLASSES HOOKS --------#

sub authenticate { }

sub get_default_param { }

sub get_entity_count_sql { }

sub get_visit_max_sql { }

sub get_tables_sql { }

sub get_variables_sql { }

#-------
1;

__END__

=pod

=head1 NAME

CohortExplorer::Datasource - CohortExplorer datasource superclass

=head1 SYNOPSIS

    # The code below shows methods your datasource class is likely to override;

    package Opal::Datasource;
    use base qw( CohortExplorer::Datasource );

    sub authenticate { 
    	
    	my ($self, $opts) = @_;
    	
    	# perform authentication using REST URL or database connection
    	
        # successful authentication returns a $response while unsuccessful returns undef
                
        # authentication code ...
    	
    }

    sub get_default_param {
        
         my ($self, $opts, $response) = @_;
          
         # $response being the response obtained upon authentication
         
         return {
         	
                  type        => $self->type() || 'standard',
                  project_id  => $response,
                  entity_type => $self->entity_type() || 'Participant',
                  id_visit_separator => $self->type() eq 'standard' ? undef : $self->id_visit_separator() || '_',
         };
    }

    sub get_entity_count_sql {
    	
        my ($self, $opts) = @_;
        
        # returns a hash reference to construct SQL for entity_count ( see SQL::Abstract::More)
        
        return {
                 -columns => 'COUNT( DISTINCT record)',
                 - from   => 'redcap_data',
                 -where => { 'project_id' => $self->project_id() }
        };
        
    }

    sub get_visit_max_sql {
        
        my ($self, $opts) = @_;
        
        # returns a hash reference to construct SQL for visit_max
        # only valid to longitudinal datasources
        
        return { 
                 -columns => 'COUNT(form_name)|count',
                 -from    => 'redcap_forms',
                 -where   => { 'event_id' => \" IN ( SELECT event_id FROM redcap_data where project_id = $project_id )" },
                -group_by => 'form_name',
                -having   => { 'count' => { '>' => 1 } }
        };
        
    }
    
    sub get_tables_sql {
        
        my ($self, $opts) = @_;
        
        # returns a hash reference to construct SQL to fetch tables

        return {
        	
            -columns => [  "GROUP_CONCAT( DISTINCT form_name )|`Table`",
                           "GROUP_CONCAT( DISTINCT IF( form_menu_description IS NOT NULL, form_menu_description, '' ) SEPARATOR '')|`Label`",
                            "COUNT( field_name )|`Variable_Count`"
                        ],
               -from => 'redcap_metadata',
              -where => { 'project_id' => $self->project_id() },
           -order_by => 'field_order',
           -group_by => 'form_name'
        };

    }
    
    sub get_variables_sql {

        my ( $self, $opts ) = @_;
        
        # returns a hash reference to construct SQL to fetch variables
        
        return {
                -columns => [
                              "CONCAT( form_name, '.', field_name )|`Variable`",
                              "IF( element_enum like '%, %', element_enum, '')|`Type`",
                              "IF( element_validation_type IS NULL, 'text', element_validation_type)|`Category`"
                 ],
                 -from => 'redcap_metadata',
                 -where => { 'project_id' => $self->project_id() },
                 -order_by => 'field_order'
        };
    }

=head1 DESCRIPTION

CohortExplorer::Datasource (datasource class for use with L<CohortExplorer>) is the base class to initialise the datasource.

=head1 CONCEPTS

=over

=head2 Object creation

CohortExplorer::Datasource is an abstract factory; C<initilise()> is the factory method that constructs and returns an object of the 
specific datasource that is supplied as an application option.
CohortExplorer::Datasource uses the datasource configuration from the config file (i.e. /etc/CohortExplorer/datasource-config.properties) 
to instantiate the datasource object. The config file takes the format below,

<datasource Clinical>
  storage=Opal
  type=standard
  id_visit_separator=_
  static_tables=Demographics,FamilyHistory
  url=myhost
  dsn=DBI:mysql:database=opal;host=myhost;port=3306
  username=yourusername
  password=yourpassword
</datasource>

<datasource Clinical1>
  storage=Opal
  type=standard
  id_visit_separator=_
  name=Clinical
  url=myhost
  dsn=DBI:mysql:database=opal;host=myhost;port=3306
  username=yourusername
  password=yourpassword
</datasource>

<datasource Drugs>
  storage=Redcap
  dsn=DBI:mysql:database=opal;host=myhost;port=3306
  username=yourusername
  password=yourpassword
</datasource>

Each blocks holds a unique datasource configuration. Apart from dsn, username and password, it is up to the user to decide 
what paramters they want to include in the configuration file. The user can specify the actual name of the datasource using
the 'name' parameter provided the block name is an alias. If the name parameter is not found then the block name is assumed
to be the actual actual name. For example in the example above, both Clinical and Clinical1 connect to the same datasource 
(i.e. Clinical) but with different configurations. Once CohortExplorer::Datasource class has successfully instantiated the 
datasource object the user can access the parameters by simply the calling the corresponding methods,

For example, database handle can be retrived by $self->dbh(),
             storage can be retreived by $self->storage()

=head 2 Processing

After instantiating the datasource object the CohortExplorer::Datasource class attempts following operations:
=over

=item 1

Loads default parameters (if any).

=item 2

Authenticates the user. The subsequent steps are only performed if the authentication is successful.

=item 3

Constructs and executes the SQL from the hash ref returned by the subclass hook 'get_entity_count_sql'

=item 4

Constructs and executes the SQL from the hash ref returned by the subclass hook 'get_visit_max_sql'.
This step is only performed if the datasource type is longitudunal. The sub classes must make sure that the parameter 
'type' exists with value either standard or logitudinal.

=item 5

Constructs and executes the SQL from the hash ref returned by the subclass hook 'get_tables_sql'. The tables returned are 
stored as hash with table names being keys and values (i.e. hash ref) being the table properties like Variable_Count, Label etc.

=item 6

Constructs and executes the SQL from the hash ref returned by the subclass hook 'get_variables_sql'. The variables returned 
are stored as hash with variable names being keys and values (i.e. hash ref) being the variable properties like Type, 
Category etc.Type parmeter stores the type of the variable i.e. integer, float, text etc. and the Category parameter stores 
the different options a variable of type text can take. The variable names are a combination of table name and variable 
name (i.e. Table.Variable). 

=item 7

If the datasource is of type longitudinal then the list of visit variables are set. Visit variables are valid to dynamic 
tables only and they represent the visit transformation of variables like V1.Var, V2.Var, Vany.Var and Vlast.Var.

=over

=head2 Subclass Hook

THe sub classes must override the following hooks:

=head2 get_default_param($self, $opts)

This method should return a hash ref with paramaters names as keys.
The method can use available parameters from the config file to load new paramters.

=head2 authenticate($self, $opts)

This method should return a string if the authentication is successful otherwise return undef.
 
=head2 get_entity_count_sql( $self, $opts )

This method should return a hash ref with SQL paramaters like columns, from, where (see SQL::Abstract::More)
The hash ref returned is used to construct the SQL to retrive entity_count.

=head2 get_visit_max_sql( $self, $opts )

This method like the get_entity_count_sql should return a hash ref with SQL paramaters.
The hash ref returned is used to construct the SQL to retrive visit_max. The method should only be overridden if the 
datasource type is longitudinal.

=head2 get_tables_sql( $self, $opts )

This method also returns a hash ref with SQL paramaters. The SQL columns must include table name followed by table 
properties like variable count, label etc. The hash ref returned is used to construct the SQL to retrive information on 
tables.

=head2 get_variables_sql( $self, $opts )

This method also returns a hash ref with SQL paramaters. The SQL columns must include variable name followed by variable 
properties like type, category etc. The hash ref returned is used to construct the SQL to retrive information on variables.
Variabe names in the SQL must be referenced as TableName.VariableName.


=head1 DIAGNOSTICS

=over

CohortExplorer::Datasource throws exception of type CLI::Framework::Exception::AppInitException whenever,

=item 1
configuration file is unable to be parsed by Config::General

=item 2 
SQL::Abstract::More is unable to construct a SQL using the supplied hash ref.

=item 3
DBI is unable to execute the SQL
=back

=head1 CONFIGURATION & ENVIRONMENT

No special configuration requirements.

=head1 DEPENDENCIES

Carp

L<CLI::Framework::Exceptions>

L<Config::General>

L<DBI>

L<Exception::Class::TryCatch>

L<SQL::Abstract::More>

L<Tie::IxHash>

=head1 SEE ALSO

L<CohortExplorer>

L<CohortExplorer::Application::Command::Describe>

L<CohortExplorer::Application::Command::Find>

L<CohortExplorer::Application::Command::History>

L<CohortExplorer::Application::Command::Query::Search>

L<CohortExplorer::Application::Command::Query::Compare>

=head1 LICENSE AND COPYRIGHT

Copyright (c) 2013 Abhishek Dixit (abhishekdxt at cpan dot org). All rights reserved.

This program is free software: you can redistribute it and/or modify it under the terms of either:

=over

=item *
the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version, or

=item *
the "Artistic Licence".

=back

=head1 AUTHOR

Abhishek Dixit

=cut