# rddb.pm
#
#  rhrdlibs
#
#  Copyright (C) 2015 Christian Pointner <equinox@helsinki.at>
#
#  This file is part of rhrdlibs.
#
#  rhrdlibs is free software: you can redistribute it and/or modify
#  it under the terms of the GNU Affero General Public License as published by
#  the Free Software Foundation, either version 3 of the License, or
#  any later version.
#
#  rhrdlibs is distributed in the hope that it will be useful,
#  but WITHOUT ANY WARRANTY; without even the implied warranty of
#  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#  GNU Affero General Public License for more details.
#
#  You should have received a copy of the GNU Affero General Public License
#  along with rhrdlibs. If not, see <http://www.gnu.org/licenses/>.
#

package RHRD::rddb;

use strict;
use Config::IniFiles;
use DBI;

###########################  constants  ###########################

use constant {
  DB_VERSION => 245,
  RD_CONFIG_FILE => '/etc/rd.conf',
  RD_MIN_CART => 1,
  RD_MAX_CART => 999999,
  RD_MIN_CUT => 1,
  RD_MAX_CUT => 999,
  RD_FADE_DEPTH => -3000,

  RHRD_CONFIG_FILE => '/etc/rhrd.conf',
};

###########################  context handling  ###########################

sub init
{
  my %ctx;

  my $cfg = Config::IniFiles->new(-file => RHRD_CONFIG_FILE)
    or return (undef , 'ERROR', "RHRD Config File Error: " . join("\n", @Config::IniFiles::errors));

  $ctx{'config'}{'specialgroups'}{'system'} = $cfg->val('specialgroups', 'system', 'SYSTEM');
  $ctx{'config'}{'specialgroups'}{'shows'} = $cfg->val('specialgroups', 'shows', 'SHOWS');
  $ctx{'config'}{'specialgroups'}{'allshows'} = $cfg->val('specialgroups', 'allshows', 'ALL_SHOWS');
  $ctx{'config'}{'specialgroups'}{'allpools'} = $cfg->val('specialgroups', 'allpools', 'ALL_POOLS');
  $ctx{'config'}{'specialgroups'}{'alljingles'} = $cfg->val('specialgroups', 'alljingles', 'ALL_JINGLE');

  @{$ctx{'config'}{'specialusers'}{'no-update-token'}} = split(' ', $cfg->val('specialusers', 'no-update-token', ''));
  @{$ctx{'config'}{'specialusers'}{'admins'}} = split(' ', $cfg->val('specialusers', 'admins', 'admin'));
  @{$ctx{'config'}{'specialusers'}{'allshows'}} = split(' ', $cfg->val('specialusers', 'allshows', ''));
  @{$ctx{'config'}{'specialusers'}{'allpools'}} = split(' ', $cfg->val('specialusers', 'allpools', ''));

  $ctx{'config'}{'dropboxes'}{'dropbox-pseudo-station'} = $cfg->val('dropboxes', 'dropbox-pseudo-station', 'import-dropbox');

  $ctx{'config'}{'shows'}{'service'} = $cfg->val('shows', 'service', '');
  $ctx{'config'}{'shows'}{'defaultuser'} = $cfg->val('shows', 'defaultuser', '');
  @{$ctx{'config'}{'shows'}{'logprefix'}} = split(' ', $cfg->val('shows', 'logprefix', ''));
  @{$ctx{'config'}{'shows'}{'logsuffix'}} = split(' ', $cfg->val('shows', 'logsuffix', ''));

  my ($dbh, $status, $errorstring) = opendb();
  unless(defined $dbh) {
    return ($dbh, $status, $errorstring);
  }
  $ctx{'dbh'} = $dbh;

  return \%ctx;
}

sub destroy
{
  my ($ctx) = @_;

  closedb($ctx->{'dbh'});
}

sub opendb
{
  my $cfg = Config::IniFiles->new(-file => RD_CONFIG_FILE)
    or return (undef , 'ERROR', "RD Config File Error: " . join("\n", @Config::IniFiles::errors));

  my $dbhost = $cfg->val('mySQL', 'Hostname');
  my $dbname = $cfg->val('mySQL', 'Database');
  my $dbuser = $cfg->val('mySQL', 'Loginname');
  my $dbpasswd = $cfg->val('mySQL', 'Password');

  my $dbh = DBI->connect("DBI:mysql:$dbname:$dbhost","$dbuser","$dbpasswd")
    or return (undef, 'ERROR', "Database Error: " . $DBI::errstr);


  $dbh->do(qq{SET CHARACTER SET utf8;})
    or return (undef, 'ERROR', "Database Error: " . $dbh->errstr);


  my $sth = $dbh->prepare(qq{select DB from VERSION;})
    or return (undef, 'ERROR', "Database Error: " . $dbh->errstr);

  $sth->execute()
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  my ($dbver) = $sth->fetchrow_array();
  $sth->finish();

  if($dbver != DB_VERSION) {
    return (undef, 'ERROR', "Wrong Database Version " . $dbver . " (should be " . DB_VERSION . ")");
  }

  return ($dbh, 'OK', 'success');
}

sub closedb
{
  my $dbh = shift;
  $dbh->disconnect();
}

###########################  utils  ###########################

sub get_cart_range
{
  my ($ctx, $groupname) = @_;

  my $sql = qq{select DEFAULT_LOW_CART, DEFAULT_HIGH_CART, DEFAULT_TITLE from GROUPS where NAME = ?;};
  my $sth = $ctx->{'dbh'}->prepare($sql)
    or return (undef, 'ERROR', "Database Error: " . $ctx->{'dbh'}->errstr);

  $sth->execute($groupname)
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  my ($low_cart, $high_cart, $chunk_size) = $sth->fetchrow_array;
  $sth->finish();

  unless(defined $low_cart) { return (undef, 'ERROR', "group '" . $groupname . "' does not exist"); }

  return ($low_cart, $high_cart, $chunk_size);
}

sub get_next_free_slot
{
  my ($ctx, $groupname) = @_;

  my ($group_low_cart, $group_high_cart, $group_chunksize) = get_cart_range($ctx, $groupname);

  my $sql = qq{select NAME, DEFAULT_LOW_CART, DEFAULT_HIGH_CART from GROUPS where NAME != ? and DEFAULT_LOW_CART >= ? and DEFAULT_HIGH_CART <= ? order by DEFAULT_LOW_CART;};
  my $sth = $ctx->{'dbh'}->prepare($sql)
    or return (undef, 'ERROR', "Database Error: " . $ctx->{'dbh'}->errstr);

  $sth->execute($groupname, $group_low_cart, $group_high_cart)
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  my ($low_cart, $high_cart) = ($group_low_cart, $group_low_cart + $group_chunksize - 1);
  while(my ($slot_name, $slot_low_cart, $slot_high_cart) = $sth->fetchrow_array) {
    my $slot_chunksize = $slot_high_cart - $slot_low_cart + 1;
    # print " --> " . $slot_name . ": " . $slot_low_cart . " - " . $slot_high_cart . " (" . $slot_chunksize . ")\n";;
    if($slot_chunksize != $group_chunksize) {
      $sth->finish();
      return (undef, 'ERROR', "show group " . $slot_name . " has wrong chunksize " . $slot_chunksize . " != " . $group_chunksize);
    }

    if($high_cart < $slot_low_cart) {
      last; # found a hole...
    }
    ($low_cart, $high_cart) = ($slot_high_cart + 1, $slot_high_cart + $group_chunksize);
  }
  $sth->finish();

  return ($low_cart, $high_cart);
}

sub check_log_exists
{
  my ($ctx, $logname) = @_;

  my $sql = qq{select LOG_EXISTS from LOGS where NAME = ?;};
  my $sth = $ctx->{'dbh'}->prepare($sql)
    or return (undef, 'ERROR', "Database Error: " . $ctx->{'dbh'}->errstr);

  $sth->execute($logname)
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  my $log_exists = $sth->fetchrow_array();
  $sth->finish();

  if(!defined $log_exists || $log_exists ne 'Y') {
    return (0, 'OK', 'log does not exist');
  }
  return (1, 'OK', 'log exists');
}

sub create_log_table
{
  my ($ctx, $logname) = @_;

  $logname=~s/ /_/g;
  $logname = $ctx->{'dbh'}->quote_identifier($logname . '_LOG');
  my $sql = qq{
    create table if not exists $logname
    (ID INT NOT NULL PRIMARY KEY,
    COUNT INT NOT NULL,
    TYPE INT DEFAULT 0,
    SOURCE INT NOT NULL,
    START_TIME int,
    GRACE_TIME int default 0,
    CART_NUMBER INT UNSIGNED NOT NULL,
    TIME_TYPE INT NOT NULL,
    POST_POINT enum('N','Y') default 'N',
    TRANS_TYPE INT NOT NULL,
    START_POINT INT NOT NULL DEFAULT -1,
    END_POINT INT NOT NULL DEFAULT -1,
    FADEUP_POINT int default -1,
    FADEUP_GAIN int default ?,
    FADEDOWN_POINT int default -1,
    FADEDOWN_GAIN int default ?,
    SEGUE_START_POINT INT NOT NULL DEFAULT -1,
    SEGUE_END_POINT INT NOT NULL DEFAULT -1,
    SEGUE_GAIN int default ?,
    DUCK_UP_GAIN int default 0,
    DUCK_DOWN_GAIN int default 0,
    COMMENT CHAR(255),
    LABEL CHAR(64),
    ORIGIN_USER char(255),
    ORIGIN_DATETIME datetime,
    EVENT_LENGTH int default -1,
    LINK_EVENT_NAME char(64),
    LINK_START_TIME int,
    LINK_LENGTH int default 0,
    LINK_START_SLOP int default 0,
    LINK_END_SLOP int default 0,
    LINK_ID int default -1,
    LINK_EMBEDDED enum('N','Y') default 'N',
    EXT_START_TIME time,
    EXT_LENGTH int,
    EXT_CART_NAME char(32),
    EXT_DATA char(32),
    EXT_EVENT_ID char(32),
    EXT_ANNC_TYPE char(8),
    index COUNT_IDX (COUNT),
    index CART_NUMBER_IDX (CART_NUMBER),
    index LABEL_IDX (LABEL));
  };

  my $sth = $ctx->{'dbh'}->prepare($sql)
    or return (undef, 'ERROR', "Database Error: " . $ctx->{'dbh'}->errstr);

  $sth->execute(RD_FADE_DEPTH, RD_FADE_DEPTH, RD_FADE_DEPTH)
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  return (1, 'OK', 'success');
}

sub fill_log_table
{
  my $ctx = shift;
  my $logname = shift;
  my @carts = @_;

  $logname=~s/ /_/g;
  $logname = $ctx->{'dbh'}->quote_identifier($logname . '_LOG');
  my $sql = qq{insert into $logname (ID, COUNT, START_TIME, CART_NUMBER, TRANS_TYPE) values (?, ?, ?, ?, ?);};

  my $sth = $ctx->{'dbh'}->prepare($sql)
    or return (undef, 'ERROR', "Database Error: " . $ctx->{'dbh'}->errstr);

  my $cnt = 0;
  for my $cart (@carts) {
    $sth->execute($cnt + 1, $cnt, $cart->{'START_TIME'}, $cart->{'NUMBER'}, $cart->{'TRANS_TYPE'})
      or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

    $cnt++;
  }

  return ($cnt+1, 'OK', 'success');
}

###########################  TOKEN handling  ###########################

sub get_token
{
  my ($ctx, $username) = @_;

  my $sql = qq{select PASSWORD from USERS where LOGIN_NAME = ?;};
  my $sth = $ctx->{'dbh'}->prepare($sql)
    or return (undef, 'ERROR', "Database Error: " . $ctx->{'dbh'}->errstr);

  $sth->execute($username)
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  my ($token) = $sth->fetchrow_array;
  $sth->finish();

  unless(defined $token) {
    return (undef, 'ERROR', "user '" . $username . "' not known by rivendell")
  }
  return ($token, 'OK', 'success');
}

sub set_token
{
  my ($ctx, $username, $token) = @_;
  if(!defined $token || $token eq '') {
    return (undef, 'ERROR', "empty token is not allowed")
  }

  my $sql = qq{update USERS set PASSWORD = ? where LOGIN_NAME = ?;};
  my $rows = $ctx->{'dbh'}->do($sql, undef, $token, $username)
    or return (undef, 'ERROR', "Database Error: " . $ctx->{'dbh'}->errstr);

  unless($rows == 1) {
    return (undef, 'ERROR', "user '" . $username . "' not known by rivendell")
  }
  return ($token, 'OK', 'success');
}

sub check_token
{
  my ($ctx, $username, $token) = @_;
  if(!defined $token || $token eq '') {
    return (undef, 'ERROR', "empty token is not allowed")
  }

  my $sql = qq{select PASSWORD from USERS where LOGIN_NAME = ?;};
  my $sth = $ctx->{'dbh'}->prepare($sql)
    or return (undef, 'ERROR', "Database Error: " . $ctx->{'dbh'}->errstr);

  $sth->execute($username)
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  my ($token_result) = $sth->fetchrow_array;
  $sth->finish();

  unless(defined $token_result) {
    return (undef, 'ERROR', "user '" . $username . "' not known by rivendell")
  }

  if($token_result eq $token) {
    return (1, 'OK', 'success');
  }
  return (0, 'ERROR', "wrong password");
}

###########################  USER handling  ###########################

sub add_user
{
  my ($ctx, $username, $token, $fullname) = @_;
  if(!defined $token || $token eq '') {
    return (undef, 'ERROR', "empty token is not allowed")
  }
  if(!defined $fullname) {
    $fullname = '';
  }

  my $sql = qq{insert into USERS (LOGIN_NAME, FULL_NAME, PHONE_NUMBER, DESCRIPTION, PASSWORD, ENABLE_WEB, ADMIN_USERS_PRIV, ADMIN_CONFIG_PRIV, CREATE_CARTS_PRIV, DELETE_CARTS_PRIV, MODIFY_CARTS_PRIV, EDIT_AUDIO_PRIV, ASSIGN_CART_PRIV, CREATE_LOG_PRIV, DELETE_LOG_PRIV, DELETE_REC_PRIV, PLAYOUT_LOG_PRIV, ARRANGE_LOG_PRIV, MODIFY_TEMPLATE_PRIV, ADDTO_LOG_PRIV, REMOVEFROM_LOG_PRIV, CONFIG_PANELS_PRIV, VOICETRACK_LOG_PRIV, EDIT_CATCHES_PRIV, ADD_PODCAST_PRIV, EDIT_PODCAST_PRIV, DELETE_PODCAST_PRIV) values ( ?, ?, "", "", ? , "N", "N", "N", "Y", "Y", "Y", "Y", "N", "N", "N", "N", "N", "N", "N", "N", "N", "N", "N", "N", "N", "N", "N");};
  my $sth = $ctx->{'dbh'}->prepare($sql)
    or return (undef, 'ERROR', "Database Error: " . $ctx->{'dbh'}->errstr);

  my $cnt = $sth->execute($username, $fullname, $token)
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  $sth->finish();
  return ($cnt, 'OK', "success");
}

sub remove_user
{
  my ($ctx, $username) = @_;

  my @actions = ({
      # Delete RSS Feed Perms
      sql => qq{delete from FEED_PERMS where USER_NAME = ?;},
      name => 'podcast feed assignments',
      cnt => 0
    }, {
      # Delete Member User Perms
      sql => qq{delete from USER_PERMS where USER_NAME = ?;},
      name => 'group assignments',
      cnt => 0
    }, {
      # Delete from User List
      sql => qq{delete from USERS where LOGIN_NAME = ?;},
      name => 'user entries',
      cnt => 0
    }, {
      # Delete from Cached Web Connections
      sql => qq{delete from WEB_CONNECTIONS where LOGIN_NAME = ?;},
      name => 'cached web connections',
      cnt => 0
    });

  for my $href (@actions) {
    my $sth = $ctx->{'dbh'}->prepare($href->{sql})
      or return (undef, 'ERROR', "Database Error: " . $ctx->{'dbh'}->errstr);
    delete($href->{sql});

    $href->{cnt} = $sth->execute($username)
      or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

    $sth->finish();
  }

  return @actions;
}

sub check_user
{
  my ($ctx, $username) = @_;

  my $sql = qq{select count(*) from USERS where LOGIN_NAME = ?;};
  my $sth = $ctx->{'dbh'}->prepare($sql)
    or return (undef, 'ERROR', "Database Error: " . $ctx->{'dbh'}->errstr);

  $sth->execute($username)
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  my ($cnt) = $sth->fetchrow_array();
  $sth->finish();

  if ($cnt != 0) {
    $sql = qq{select count(*) from STATIONS where DEFAULT_NAME = ?;};
    my $sth = $ctx->{'dbh'}->prepare($sql)
      or return (undef, 'ERROR', "Database Error: " . $ctx->{'dbh'}->errstr);

    $sth->execute($username)
      or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

    ($cnt) = $sth->fetchrow_array();
    $sth->finish();

    if($cnt) { return (2, 'OK', "user '" . $username . "' is known by rivendell and is the default user of at least one station"); }
    else { return (1, 'OK', "user '" . $username . "' is known by rivendell and isn't the default user of any station"); }
  }

  return (0, 'OK', "user '" . $username . "' not known by rivendell");
}

sub get_fullname
{
  my ($ctx, $username) = @_;

  my $sql = qq{select FULL_NAME from USERS where LOGIN_NAME = ?;};
  my $sth = $ctx->{'dbh'}->prepare($sql)
    or return (undef, 'ERROR', "Database Error: " . $ctx->{'dbh'}->errstr);

  $sth->execute($username)
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  my ($fullname) = $sth->fetchrow_array;
  $sth->finish();

  unless(defined $fullname) {
    return (undef, 'ERROR', "user '" . $username . "' not known by rivendell")
  }
  return ($fullname, 'OK', 'success');
}

sub list_users
{
  my ($ctx) = @_;

  my $sql = qq{select LOGIN_NAME from USERS order by LOGIN_NAME;};
  my $sth = $ctx->{'dbh'}->prepare($sql)
    or return (undef, 'ERROR', "Database Error: " . $ctx->{'dbh'}->errstr);

  $sth->execute()
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  my @users;
  while(my ($user) = $sth->fetchrow_array()) {
    push @users, $user;
  }
  $sth->finish();

  return @users;
}

###########################  GROUP handling  ###########################

sub add_group
{
  my ($ctx, $groupname, $description) = @_;
  if(!defined $description) {
    $description = '';
  }

  my $sql = qq{insert into GROUPS (NAME, DESCRIPTION) values (?, ?);};
  my $sth = $ctx->{'dbh'}->prepare($sql)
    or return (undef, 'ERROR', "Database Error: " . $ctx->{'dbh'}->errstr);

  my $cnt = $sth->execute($groupname, $description)
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  $sth->finish();
  return ($cnt, 'OK', "success");
}

sub remove_group
{
  my ($ctx, $groupname) = @_;

  my @actions = ({
      # Delete Member Carts
      sql => qq{delete from CART where GROUP_NAME = ?;},
      name => 'member carts',
      cnt => 0
    }, {
      # Delete Dropboxes
      sql => qq{delete from DROPBOXES where GROUP_NAME = ?;},
      name => 'dropboxes',
      cnt => 0
    }, {
      # Delete Audio Perms
      sql => qq{delete from AUDIO_PERMS where GROUP_NAME = ?;},
      name => 'service permissions',
      cnt => 0
    }, {
      # Delete Member User Perms
      sql => qq{delete from USER_PERMS where GROUP_NAME = ?;},
      name => 'user assignments',
      cnt => 0
    }, {
      # Delete Replicator Map Records
      sql => qq{delete from REPLICATOR_MAP where GROUP_NAME = ?;},
      name => 'replicator map records',
      cnt => 0
    }, {
      # Delete from Group List
      sql => qq{delete from GROUPS where NAME = ?;},
      name => 'group entries',
      cnt => 0
    });

  for my $href (@actions) {
    my $sth = $ctx->{'dbh'}->prepare($href->{sql})
      or return (undef, 'ERROR', "Database Error: " . $ctx->{'dbh'}->errstr);
    delete($href->{sql});

    $href->{cnt} = $sth->execute($groupname)
      or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

    $sth->finish();
  }

  return @actions;
}

sub check_group
{
  my ($ctx, $groupname) = @_;

  my $sql = qq{select count(*) from GROUPS where NAME = ?;};
  my $sth = $ctx->{'dbh'}->prepare($sql)
    or return (undef, 'ERROR', "Database Error: " . $ctx->{'dbh'}->errstr);

  $sth->execute($groupname)
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  my ($cnt) = $sth->fetchrow_array();
  $sth->finish();

  if ($cnt != 0) {
    $sql = qq{select count(*) from CART where GROUP_NAME = ?;};
    my $sth = $ctx->{'dbh'}->prepare($sql)
      or return (undef, 'ERROR', "Database Error: " . $ctx->{'dbh'}->errstr);

    $sth->execute($groupname)
      or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

    ($cnt) = $sth->fetchrow_array();
    $sth->finish();

    if($cnt) { return (2, 'OK', "group '" . $groupname . "' exists and has carts assigned to it"); }
    else { return (1, 'OK', "group '" . $groupname . "' exists but no cart is assigned to it"); }
  }

  return (0, 'OK', "group '" . $groupname . "' does not exist");
}

sub get_group_carts
{
  my ($ctx, $groupname) = @_;

  my $sql = qq{select DEFAULT_LOW_CART, DEFAULT_HIGH_CART, DEFAULT_CART_TYPE, ENFORCE_CART_RANGE from GROUPS where NAME = ?;};
  my $sth = $ctx->{'dbh'}->prepare($sql)
    or return (undef, 'ERROR', "Database Error: " . $ctx->{'dbh'}->errstr);

  $sth->execute($groupname)
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  my ($low_cart, $high_cart, $cart_type, $enforce_cart_range) = $sth->fetchrow_array;
  $sth->finish();

  unless(defined $low_cart) { return (undef, 'ERROR', "group '" . $groupname . "' does not exist"); }

  return ($low_cart, $high_cart, $cart_type, $enforce_cart_range);
}

sub set_group_carts
{
  my ($ctx, $groupname, $low_cart, $high_cart, $cart_type, $enforce_cart_range) = @_;
  if(!defined $low_cart) {
    $low_cart = 0;
  }
  if(!defined $high_cart) {
    $high_cart = 0;
  }
  if(!defined $cart_type) {
    $cart_type = 1;
  }
  if(!defined $enforce_cart_range) {
    $enforce_cart_range = 'N';
  }

  my $sql = qq{update GROUPS set DEFAULT_LOW_CART = ?, DEFAULT_HIGH_CART = ?, DEFAULT_CART_TYPE  = ? , ENFORCE_CART_RANGE = ? where NAME = ?;};
  my $sth = $ctx->{'dbh'}->prepare($sql)
    or return (undef, 'ERROR', "Database Error: " . $ctx->{'dbh'}->errstr);

  my $cnt = $sth->execute($low_cart, $high_cart, $cart_type, $enforce_cart_range, $groupname)
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  $sth->finish();

  return ($cnt, 'OK', "Success");
}

sub get_group_reports
{
  my ($ctx, $groupname) = @_;

  my $sql = qq{select ENABLE_NOW_NEXT, REPORT_TFC, REPORT_MUS from GROUPS where NAME = ?;};
  my $sth = $ctx->{'dbh'}->prepare($sql)
    or return (undef, 'ERROR', "Database Error: " . $ctx->{'dbh'}->errstr);

  $sth->execute($groupname)
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  my ($now_next, $traffic, $music) = $sth->fetchrow_array;
  $sth->finish();

  unless(defined $now_next) { return (undef, 'ERROR', "group '" . $groupname . "' does not exist"); }

  return ($now_next, $traffic, $music);
}

sub set_group_reports
{
  my ($ctx, $groupname, $now_next, $traffic, $music) = @_;
  if(!defined $now_next) {
    $now_next = 'N';
  }
  if(!defined $traffic) {
    $traffic = 'Y';
  }
  if(!defined $music) {
    $music = 'Y';
  }

  my $sql = qq{update GROUPS set ENABLE_NOW_NEXT = ?, REPORT_TFC = ?, REPORT_MUS = ? where NAME = ?;};
  my $sth = $ctx->{'dbh'}->prepare($sql)
    or return (undef, 'ERROR', "Database Error: " . $ctx->{'dbh'}->errstr);

  my $cnt = $sth->execute($now_next, $traffic, $music, $groupname)
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  $sth->finish();

  return ($cnt, 'OK', "Success");
}

sub get_group_members
{
  my ($ctx, $groupname) = @_;

  my $sql = qq{select USER_NAME from USER_PERMS where GROUP_NAME = ?;};
  my $sth = $ctx->{'dbh'}->prepare($sql)
    or return (undef, 'ERROR', "Database Error: " . $ctx->{'dbh'}->errstr);

  $sth->execute($groupname)
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  my @users;
  while(my ($user) = $sth->fetchrow_array()) {
    push @users, $user;
  }
  $sth->finish();

  return @users;
}

sub is_group_member
{
  my ($ctx, $groupname, $username) = @_;

  my $sql = qq{select count(*) from USER_PERMS where GROUP_NAME = ? and USER_NAME = ?;};
  my $sth = $ctx->{'dbh'}->prepare($sql)
    or return (undef, 'ERROR', "Database Error: " . $ctx->{'dbh'}->errstr);

  $sth->execute($groupname, $username)
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  my ($cnt) = $sth->fetchrow_array();
  $sth->finish();

  return ($cnt, 'OK', "success");
}

sub add_group_member
{
  my ($ctx, $groupname, $username) = @_;

  my $sql = qq{select count(*) from USERS where LOGIN_NAME = ?;};
  my $sth = $ctx->{'dbh'}->prepare($sql)
    or return (undef, 'ERROR', "Database Error: " . $ctx->{'dbh'}->errstr);

  $sth->execute($username)
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  my ($cnt) = $sth->fetchrow_array();
  $sth->finish();
  if($cnt < 1) {
    return (undef, 'ERROR', "user '" . $username . "' does not exist");
  }

  ($cnt, my $result, my $errostring) = is_group_member($ctx, $groupname, $username);
  if($cnt > 0) {
    return (undef, 'ERROR', "already a member");
  }

  $sql = qq{insert into USER_PERMS (GROUP_NAME, USER_NAME) values (?, ?);};
  $sth = $ctx->{'dbh'}->prepare($sql)
    or return (undef, 'ERROR', "Database Error: " . $ctx->{'dbh'}->errstr);

  $cnt = $sth->execute($groupname, $username)
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  $sth->finish();
  return ($cnt, 'OK', "success");
}

sub remove_group_member
{
  my ($ctx, $groupname, $username) = @_;

  my $sql = qq{delete from USER_PERMS where GROUP_NAME = ? and USER_NAME = ?;};
  my $sth = $ctx->{'dbh'}->prepare($sql)
    or return (undef, 'ERROR', "Database Error: " . $ctx->{'dbh'}->errstr);

  my $cnt = $sth->execute($groupname, $username)
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  $sth->finish();
  return ($cnt, 'OK', "success");
}

sub list_groups
{
  my ($ctx) = @_;

  my $sql = qq{select NAME from GROUPS order by DEFAULT_LOW_CART;};
  my $sth = $ctx->{'dbh'}->prepare($sql)
    or return (undef, 'ERROR', "Database Error: " . $ctx->{'dbh'}->errstr);

  $sth->execute()
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  my @groups;
  while(my ($group) = $sth->fetchrow_array()) {
    push @groups, $group;
  }
  $sth->finish();

  return @groups;
}

###########################  Dropboxes handling  ######################

sub get_dropboxes
{
  my ($ctx, $username, $groupname, $type) = @_;

  my $sql = qq{select USER_PERMS.GROUP_NAME,DROPBOXES.TO_CART,DROPBOXES.NORMALIZATION_LEVEL,DROPBOXES.AUTOTRIM_LEVEL,DROPBOXES.SET_USER_DEFINED,GROUPS.DEFAULT_LOW_CART,GROUPS.DEFAULT_HIGH_CART,GROUPS.DESCRIPTION from USER_PERMS, DROPBOXES, GROUPS where USER_PERMS.USER_NAME = ? and DROPBOXES.GROUP_NAME=USER_PERMS.GROUP_NAME and DROPBOXES.GROUP_NAME=GROUPS.NAME and DROPBOXES.STATION_NAME = ?;};
  if(defined $groupname) {
    $sql = qq{select USER_PERMS.GROUP_NAME,DROPBOXES.TO_CART,DROPBOXES.NORMALIZATION_LEVEL,DROPBOXES.AUTOTRIM_LEVEL,DROPBOXES.SET_USER_DEFINED,GROUPS.DEFAULT_LOW_CART,GROUPS.DEFAULT_HIGH_CART,GROUPS.DESCRIPTION from USER_PERMS, DROPBOXES, GROUPS where USER_PERMS.USER_NAME = ? and DROPBOXES.GROUP_NAME=USER_PERMS.GROUP_NAME and DROPBOXES.GROUP_NAME=GROUPS.NAME and DROPBOXES.STATION_NAME = ? and GROUPS.NAME = ?;};
  }

  my $sth = $ctx->{'dbh'}->prepare($sql)
    or return (undef, 'ERROR', "Database Error: " . $ctx->{'dbh'}->errstr);

  if(defined $groupname) {
    $sth->execute($username, $ctx->{'config'}{'dropboxes'}{'dropbox-pseudo-station'}, $groupname)
      or return (undef, 'ERROR', "Database Error: " . $sth->errstr);
  } else {
    $sth->execute($username, $ctx->{'config'}{'dropboxes'}{'dropbox-pseudo-station'})
      or return (undef, 'ERROR', "Database Error: " . $sth->errstr);
  }

  my @allowed_dbs;
  while(my ($group, $to_cart, $normlevel, $trimlevel, $params, $lowcart, $highcart, $groupdesc) = $sth->fetchrow_array()) {
    my @p = split(';', $params);
    next if (defined $type && $type ne $p[0]);

    my $entry = {};
    $entry->{'GROUP'} = $group;
    $entry->{'GROUPDESC'} = $groupdesc;
    $entry->{'GROUPLOWCART'} = int $lowcart;
    $entry->{'GROUPHIGHCART'} = int $highcart;
    $entry->{'NORMLEVEL'} = int $normlevel;
    $entry->{'TRIMLEVEL'} = int $trimlevel;
    $entry->{'PARAM'} = $params;
    if($p[0] eq "S") {
      $entry->{'TYPE'} = 'show';
      $entry->{'SHOWID'} = $to_cart;

      my ($title, $log, $status, $errorstring) = get_show_title_and_log($ctx, $to_cart);
      unless (defined $title && defined $log) {
        return (undef, $status, $errorstring);
      }
      $entry->{'SHOWTITLE'} = $title;
      $entry->{'SHOWLOG'} = $log;

      $entry->{'SHOWRHYTHM'} = $p[1];
      $entry->{'SHOWDOW'} = int $p[2];
      $entry->{'SHOWDOW'} = 0 unless $entry->{'SHOWDOW'} < 7;
      substr($p[3], 2, 0) = ':';
      $entry->{'SHOWSTARTTIME'} = $p[3];
      $entry->{'SHOWLEN'} = int $p[4];
    } elsif($p[0] eq "J") {
      $entry->{'TYPE'} = 'jingle';
      $entry->{'JINGLETITLE'} = $groupdesc;
    } elsif($p[0] eq "M") {
      $entry->{'TYPE'} = 'musicpool';
      $entry->{'MUSICPOOLTITLE'} = $groupdesc;
    }

    push @allowed_dbs, $entry;
  }
  $sth->finish();

  return @allowed_dbs;
}

###########################  SHOW handling  ###########################

sub get_shows_cart_range
{
  my ($ctx) = @_;
  return get_cart_range($ctx, $ctx->{'config'}{'specialgroups'}{'allshows'})
}

sub get_shows_next_free_slot
{
  my ($ctx) = @_;
  return get_next_free_slot($ctx, $ctx->{'config'}{'specialgroups'}{'allshows'})
}

sub list_shows
{
  my ($ctx) = @_;

  my $sql = qq{select TO_CART,SET_USER_DEFINED from DROPBOXES where STATION_NAME=?;};

  my $sth = $ctx->{'dbh'}->prepare($sql)
    or return (undef, 'ERROR', "Database Error: " . $ctx->{'dbh'}->errstr);

  $sth->execute($ctx->{'config'}{'dropboxes'}{'dropbox-pseudo-station'})
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  my @show_dbs;
  while(my ($to_cart, $params, $lowcart, $highcart) = $sth->fetchrow_array()) {
    my @p = split(';', $params);
    next if ('S' ne $p[0]);

    my $entry = {};
    $entry->{'ID'} = $to_cart;
    my ($title, $log, $status, $errorstring) = get_show_title_and_log($ctx, $to_cart);
    unless (defined $title && defined $log) {
      return (undef, $status, $errorstring);
    }
    $entry->{'TITLE'} = $title;
    $entry->{'LOG'} = $log;
    $entry->{'RHYTHM'} = $p[1];
    $entry->{'DOW'} = int $p[2];
    $entry->{'DOW'} = 0 unless $entry->{'DOW'} < 7;
    substr($p[3], 2, 0) = ':';
    $entry->{'STARTTIME'} = $p[3];
    $entry->{'LEN'} = int $p[4];

    push @show_dbs, $entry;
  }
  $sth->finish();

  return @show_dbs;
}

sub get_show_group_carts
{
  my ($ctx, $showid) = @_;

  my $sql = qq{select GROUPS.DEFAULT_LOW_CART,GROUPS.DEFAULT_HIGH_CART from DROPBOXES, GROUPS where DROPBOXES.TO_CART = ? and DROPBOXES.GROUP_NAME=GROUPS.NAME and DROPBOXES.STATION_NAME = ?;};
  my $sth = $ctx->{'dbh'}->prepare($sql)
    or return (undef, undef, 'ERROR', "Database Error: " . $ctx->{'dbh'}->errstr);

  $sth->execute($showid, $ctx->{'config'}{'dropboxes'}{'dropbox-pseudo-station'})
    or return (undef, undef, 'ERROR', "Database Error: " . $sth->errstr);

  my ($group_low_cart, $group_high_cart) = $sth->fetchrow_array();
  unless(defined($group_low_cart) && defined($group_high_cart)) {
    return (undef, undef, 'ERROR', "Show not found");
  }
  return ($group_low_cart, $group_high_cart, 'OK', 'success');
}

sub get_show_title_and_log
{
  my ($ctx, $showid) = @_;

  my $sql = qq{select TITLE,MACROS from CART where NUMBER = ?;};
  my $sth = $ctx->{'dbh'}->prepare($sql)
    or return (undef, undef, 'ERROR', "Database Error: " . $ctx->{'dbh'}->errstr);

  $sth->execute($showid)
    or return (undef, undef, 'ERROR', "Database Error: " . $sth->errstr);

  my ($title, $macros) = $sth->fetchrow_array;
  $sth->finish();

  unless(defined $title) {
    return (undef, undef, 'ERROR', "Show with ID=" . $showid . " not found!")
  }
  unless(defined $macros) {
    return (undef, undef, 'ERROR', "Show with ID=" . $showid . " has no macro!");
  }

  unless($macros =~ /^LL 1 ([^ ]+) 0\!$/) {
    return (undef, undef, 'ERROR', "Show with ID=" . $showid . " has invalid macro: '" . $macros . "'");
  }
  my $log = $1;

  return ($title, $log, 'OK', 'success');
}

sub get_show_carts
{
  my ($ctx, $showid) = @_;

  my ($group_low_cart, $group_high_cart, $status, $errorstring) = get_show_group_carts($ctx, $showid);
  unless (defined $group_low_cart && defined $group_high_cart) {
    return (undef, $status, $errorstring);
  }

  (undef, my $log, $status, $errorstring) = get_show_title_and_log($ctx, $showid);
  unless (defined $log) {
    return (undef, $status, $errorstring);
  }

  (my $log_exists, $status, $errorstring) = check_log_exists($ctx, $log);
  unless (defined $log_exists) {
    return (undef, $status, $errorstring);
  }
  unless($log_exists) {
    return (undef, 'ERROR', "Log with name '$log' does not exist")
  }

  $log=~s/ /_/g;
  $log = $ctx->{'dbh'}->quote_identifier($log . '_LOG');
  my $sql = qq{select COUNT,CART_NUMBER from $log order by COUNT;};

  my $sth = $ctx->{'dbh'}->prepare($sql)
    or return (undef, 'ERROR', "Database Error: " . $ctx->{'dbh'}->errstr);

  $sth->execute()
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  my @carts;
  while(my ($count, $cart) = $sth->fetchrow_array()) {
    if($cart >= $group_low_cart && $cart <= $group_high_cart) {
      push @carts, $cart;
    }
  }
  $sth->finish();

  return @carts;
}

sub create_show_log
{
  my ($ctx, $logname, $low_cart, $high_cart) = @_;

  my ($log_exists, $status, $errorstring) = check_log_exists($ctx, $logname);
  unless (defined $log_exists) {
    return (undef, $status, $errorstring);
  }
  if($log_exists) {
    return (undef, 'ERROR', "Log with name '" . $logname . "' already exists")
  }

  my $sql = qq{insert into LOGS set NAME = ?, LOG_EXISTS='N', TYPE=0, DESCRIPTION = ?, ORIGIN_USER = ?, ORIGIN_DATETIME=NOW(), LINK_DATETIME=NOW(), SERVICE = ?};

  my $sth = $ctx->{'dbh'}->prepare($sql)
    or return (undef, 'ERROR', "Database Error: " . $ctx->{'dbh'}->errstr);

  $sth->execute($logname, $logname . " log", $ctx->{'config'}{'shows'}{'defaultuser'}, $ctx->{'config'}{'shows'}{'service'})
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  (my $result, $status, $errorstring) = create_log_table($ctx, $logname);
  unless (defined $result && defined $status) {
    return (undef, $status, $errorstring);
  }

  my @carts = ();
  for my $cart (@{$ctx->{'config'}{'shows'}{'logprefix'}}) {
    push @carts, { NUMBER => $cart, START_TIME => 0, TRANS_TYPE => 2 };
  }
  for my $cart ($low_cart .. $high_cart) {
    push @carts, { NUMBER => $cart, START_TIME => 0, TRANS_TYPE => 0 };
  }
  for my $cart (@{$ctx->{'config'}{'shows'}{'logsuffix'}}) {
    push @carts, { NUMBER => $cart, START_TIME => 0, TRANS_TYPE => 0 };
  }

  (my $next_id, $status, $errorstring) = fill_log_table($ctx, $logname, @carts);
  unless (defined $next_id) {
    return (undef, $status, $errorstring);
  }

  $sql = qq{update LOGS set LOG_EXISTS='Y', AUTO_REFRESH='Y', NEXT_ID = ? where NAME = ?};

  $sth = $ctx->{'dbh'}->prepare($sql)
    or return (undef, 'ERROR', "Database Error: " . $ctx->{'dbh'}->errstr);

  $sth->execute($next_id, $logname)
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  return (1, 'OK', 'success');
}

###########################  MUSICPOOL handling  ###########################

sub get_musicpools_cart_range
{
  my ($ctx) = @_;
  return get_cart_range($ctx, $ctx->{'config'}{'specialgroups'}{'allpools'})
}

sub get_musicpools_next_free_slot
{
  my ($ctx) = @_;
  return get_next_free_slot($ctx, $ctx->{'config'}{'specialgroups'}{'allpools'})
}

###########################  JINGLES handling  ###########################

sub get_jingles_cart_range
{
  my ($ctx) = @_;
  return get_cart_range($ctx, $ctx->{'config'}{'specialgroups'}{'alljingles'})
}

sub get_jingles_next_free_slot
{
  my ($ctx) = @_;
  return get_next_free_slot($ctx, $ctx->{'config'}{'specialgroups'}{'alljingles'})
}

#################################  END  ####################################
return 1;