#!/usr/bin/perl

#use utf8;
use strict;
use CGI;

use Opals::Context; 
use Opals::Template qw(
    tmpl_read
    tmpl_write
);


my $dbh = Opals::Context->dbh();
END { $dbh->disconnect(); }

my $cgi = CGI->new;
my $input = $cgi->Vars();
my $op = lc($input->{'op'});
my ($permission, $cookieList, $template) = tmpl_read(
    {
        dbh             => $dbh,
        cgi             => $cgi,
        tmplFile        => 'util/db.tmpl',
        reqPermission   => 'pref_edit',
    }
);

# See User.pm for the list of permissions
#if ($permission && $permission->{'reqPermission1'}) {
my $op_result;
if ($op eq 'add') {
    $op = 'edit';
    $template->param(
        id => -1,
    );
}
elsif ($op eq 'edit') {
    my $db_param = get_db_param($dbh, $input->{'id'});
    $template->param($db_param);
}
else {
    if ($op eq 'save') {
        $op_result = save_db($dbh, $input);
    }
    elsif ($op eq 'delete') {
        $op_result = delete_db($dbh, $input);
    }
    $op = 'list';
    my @dbList = list_database($dbh);
    $template->param(
        dbList => \@dbList,
    );
}

$template->param(
    'op_'.$op => 1,
    $op.'_ok'    => ($op =~ m/^(save|delete)$/ && $op_result),
);

#}
#else { # input for login form
#    $template->param(
#        input => [
#            {name => '', value => $,},
#            {name => '', value => $,},
#        ],
#    );
#}

# Turn on/off search box
#$template->param(
#    search_off      => 1,
#);
$template->param(hlpUrl     => Opals::Constant->getHlpUrl('db') );

tmpl_write($dbh, $cgi, $cookieList, $template);
#$dbh->disconnect();
################################################################################

sub list_database {
    my ($dbh) = @_;

    my @dbs = @{$dbh->selectall_arrayref(<<_SQL_, {Slice => {}})};
select  *
from    opl_database
order by id asc
_SQL_

    foreach my $db (@dbs) {
      $db->{'recommended'} = ($db->{'recommended'} eq 'true');
    }

    return @dbs;
}
########################################

sub get_db_param {
    my ($dbh, $id) = @_;

    my $sql = <<_SQL_;
select  *
from    opl_database
where   id = ?
_SQL_

    my @value = ($id);
    my $db_param = $dbh->selectrow_hashref($sql, undef, @value) || return;

    $db_param->{'recommended'} = ($db_param->{'recommended'} eq 'true');

    return $db_param;
}
########################################

sub save_db {
    my ($dbh, $in) = @_;
    my $sql;
    my @value;

    my $op = ($in->{'id'} == -1) ? 'insert into' : 'update';

    my $sql = "$op opl_database set ";
    foreach my $col qw(host name type recommended) {
        $sql .= "$col = ?,";
        push @value, $in->{$col};
    }
    $sql =~ s/,$//; # remove the last comma
    if ($op eq 'update') {
        $sql .= " where id = $in->{'id'}";
    }

    return $dbh->do($sql, undef, @value);
}
########################################

sub delete_db {
    my ($dbh, $in) = @_;
    if (!$in->{'id'}) {
        return;
    }

    return $dbh->do("delete from opl_database where id = $in->{'id'}");
}