package Orchard::DB::Connect;

use strict;
use warnings;

use DBI;
use FileHandle;
use Net::OpenSSH;
use Sys::Hostname;

use Orchard::YamlConfig;

sub dsn {
    my $self = shift;
    my $conn = shift;

    my $config;
    my $dsncnf;
    my $_dbconn = "$ENV{'HOME'}/.dbconn.yml";
    if ( -e $_dbconn ) {
        $config = Orchard::YamlConfig::get_config_from_file($_dbconn);
        $dsncnf = $config->dbhost->{$conn};
    } else {
        die "$_dbconn file required for db connections";
    }
    die "$conn is not in " . $config->file if not defined $dsncnf;

    my $host = defined $dsncnf->{'host'}   ? $dsncnf->{'host'}   : $config->default_host;
    my $port = defined $dsncnf->{'port'}   ? $dsncnf->{'port'}   : $config->default_port;
    my $drvr = defined $dsncnf->{'driver'} ? $dsncnf->{'driver'} : $config->default_driver;
    my $user = defined $dsncnf->{'user'}   ? $dsncnf->{'user'}   : $config->default_user;
    my $pass = defined $dsncnf->{'pass'}   ? $dsncnf->{'pass'}   : $config->default_pass;
    my $scma = defined $dsncnf->{'schema'} ? $dsncnf->{'schema'} : '';

    my $ssh;
    if ( defined $dsncnf->{'tunnel'} and $dsncnf->{'tunnel'} ) {
        my $conn = $dsncnf->{'sshuser'} . '@' . $dsncnf->{'sshhost'};
        my $key  = $dsncnf->{'sshkey'};
        my $net  = Net::OpenSSH->new( $conn, 'key_path' => $key, 'master_opts' => [qw/-q -N -S none/] );
        die $net->error if $net->error;
        $ssh = {
            'tunnel' => { 'ssh_opts' => [ '-O', 'forward', '-L 6603:' . $host . ':3306' ] },
            'net'    => $net
        };
        $host = '127.0.0.1';
        $port = '6603';
    }

    $host = '127.0.0.1' if $host eq Sys::Hostname::hostname;

    my $dsn;
    if ( $drvr ne 'dbi:ODBC' ) {
        $dsn = "${drvr}:dbname=${scma}:host=$host:port=$port;";
        if ( not $drvr eq 'dbi:Pg' ) {
            $dsn .= "mysql_local_infile=1";
        }
    } else {
        my $pkey    = $dsncnf->{'priv_key_file'};
        my $pkeypw  = $dsncnf->{'priv_key_file_pwd'};
        # my $keyopts = "authenticator=snowflake_jwt;priv_key_file=$pkey;priv_key_file_pwd=$pkeypw";
        my $keyopts = "authenticator=snowflake_jwt;priv_key_file=$pkey";
        $dsn = "${drvr}:dsn=snowflake;uid=$user;pwd=$pass;$keyopts";
    }

    { 'conn' => [ $dsn, $user, $pass ], 'ssh' => $ssh };

}

sub connect {
    my $self = shift;
    my $host = shift;
    my $dsn  = $self->dsn($host);
    my $dbh  = DBI->connect( @{ $dsn->{'conn'} } ) || die "Conn: $DBI::errstr";
    if ( $host eq 'snowflake' ) {
        $dbh = $self->snowflake_session($dbh);
    }
    $dbh;
}

sub snowflake_session {
    my $self = shift;
    my $dbh  = shift;
    my $arg  = shift;

    if ( defined $arg and exists $arg->{'schema'} and $arg->{'schema'} eq 'prod' ) {
        $arg->{'role'}      = 'ROYALTYACCOUNTING_DB_PROD_SCHEMA_READWRITE';
        $arg->{'warehouse'} = 'prod_abacus_wh';
    } elsif ( defined $arg and exists $arg->{'schema'} and $arg->{'schema'} eq 'qa' ) {
        $arg->{'role'}      = 'ROYALTYACCOUNTING_DB_QA_SCHEMA_READWRITE';
        $arg->{'warehouse'} = 'qa_abacus_wh';
    }

    my $role      = $arg->{'role'}      || 'dev_engineering';
    my $schema    = $arg->{'schema'}    || 'qa';
    my $database  = $arg->{'database'}  || 'royalty_accounting';
    my $warehouse = $arg->{'warehouse'} || 'qa_abacus_wh';

    $dbh->do("use $database")            or die "Error: $DBI::errstr\n\n";
    $dbh->do("use role $role")           or die "Error: $DBI::errstr\n\n";
    $dbh->do("use schema ${schema}")     or die "Error: $DBI::errstr\n\n";
    $dbh->do("use warehouse $warehouse") or die "Error: $DBI::errstr\n\n";

    $dbh;

}

sub prepare_file {
    my $self = shift;
    my $dbh  = shift;
    my $file = shift;

    my $contents;
    my $fh = FileHandle->new( $file, 'r' );
    read $fh, $contents, -s $fh;
    $fh->close;

    $dbh->prepare($contents);
}

1;