# rddb.pm
#
#  rhrdlibs
#
#  Copyright (C) 2015-2016 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;
use RHRD::utils;
use Date::Calc;

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

use constant {
  DB_VERSION => 250,
  RD_CONFIG_FILE => '/etc/rd.conf',
  RD_INVALID_CART => 0,
  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'}{'dropboxes'}{'norm-level'} = $cfg->val('dropboxes', 'norm-level', -1200);
  $ctx{'config'}{'dropboxes'}{'trim-level'} = $cfg->val('dropboxes', 'trim-level', 0);

  $ctx{'config'}{'shows'}{'service'} = $cfg->val('shows', 'service', '');
  $ctx{'config'}{'shows'}{'defaultuser'} = $cfg->val('shows', 'defaultuser', '');
  $ctx{'config'}{'shows'}{'schedule-lifetime'} = $cfg->val('shows', 'schedule-lifetime', 35);
  @{$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 get_log_table_name
{
  my ($logname) = @_;
  $logname=~s/ /_/g;
  return $logname . '_LOG';
}

sub get_log_table_name_escaped
{
  my ($ctx, $logname) = @_;
  return $ctx->{'dbh'}->quote_identifier(get_log_table_name($logname));
}

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

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

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

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

  unless($cnt) {
    return ($cnt, 'OK', 'log table does not exist');
  }
  return ($cnt, 'OK', 'log table exists');
}

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

  $logname = get_log_table_name_escaped($ctx, $logname);
  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);

  $sth->finish();

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

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

  $logname = get_log_table_name_escaped($ctx, $logname);
  my $sql_log = qq{insert into $logname (ID, COUNT, TYPE, START_TIME, CART_NUMBER, TRANS_TYPE, TIME_TYPE) values (?, ?, ?, ?, ?, ?, ?);};
  my $sql_cart_type = qq{select TYPE from CART where NUMBER = ?;};

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

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

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

    my ($type) = $sth_cart_type->fetchrow_array;
    $type = 0 unless(defined($type) && $type == 2);

    $sth_log->execute($cnt + 1, $cnt, $type, $cart->{'START_TIME'}, $cart->{'NUMBER'}, $cart->{'TRANS_TYPE'}, $cart->{'TIME_TYPE'})
      or return (undef, 'ERROR', "Database Error: " . $sth_log->errstr);
    $sth_log->finish();

    $cnt++;
  }

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

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

  $logname = get_log_table_name_escaped($ctx, $logname);
  my $sql = qq{drop table $logname;};

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

  return (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 Cuts
      sql => qq{delete from CUTS where CART_NUMBER IN (select NUMBER from CART where GROUP_NAME = ?);},
      name => 'member cuts',
      cnt => 0
    }, {
      # 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_cart_range
{
  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_cart_range
{
  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;
  if(defined $username && !defined $groupname) {
    $sql = qq{select USER_PERMS.GROUP_NAME,DROPBOXES.TO_CART,DROPBOXES.NORMALIZATION_LEVEL,DROPBOXES.AUTOTRIM_LEVEL,DROPBOXES.SET_USER_DEFINED,DROPBOXES.PATH,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 = ?;};
  }
  elsif(defined $username && defined $groupname) {
    $sql = qq{select USER_PERMS.GROUP_NAME,DROPBOXES.TO_CART,DROPBOXES.NORMALIZATION_LEVEL,DROPBOXES.AUTOTRIM_LEVEL,DROPBOXES.SET_USER_DEFINED,DROPBOXES.PATH,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 = ?;};
  }
  elsif(!defined $username && defined $groupname) {
    $sql = qq{select GROUPS.NAME,DROPBOXES.TO_CART,DROPBOXES.NORMALIZATION_LEVEL,DROPBOXES.AUTOTRIM_LEVEL,DROPBOXES.SET_USER_DEFINED,DROPBOXES.PATH,GROUPS.DEFAULT_LOW_CART,GROUPS.DEFAULT_HIGH_CART,GROUPS.DESCRIPTION from DROPBOXES, GROUPS where DROPBOXES.GROUP_NAME=GROUPS.NAME and DROPBOXES.STATION_NAME = ? and GROUPS.NAME = ?;};
  }
  else {
    $sql = qq{select GROUPS.NAME,DROPBOXES.TO_CART,DROPBOXES.NORMALIZATION_LEVEL,DROPBOXES.AUTOTRIM_LEVEL,DROPBOXES.SET_USER_DEFINED,DROPBOXES.PATH,GROUPS.DEFAULT_LOW_CART,GROUPS.DEFAULT_HIGH_CART,GROUPS.DESCRIPTION from DROPBOXES, GROUPS where DROPBOXES.GROUP_NAME=GROUPS.NAME and DROPBOXES.STATION_NAME = ?;};
  }

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

  if(defined $username && !defined $groupname) {
    $sth->execute($username, $ctx->{'config'}{'dropboxes'}{'dropbox-pseudo-station'})
      or return (undef, 'ERROR', "Database Error: " . $sth->errstr);
  }
  elsif(defined $username && defined $groupname) {
    $sth->execute($username, $ctx->{'config'}{'dropboxes'}{'dropbox-pseudo-station'}, $groupname)
      or return (undef, 'ERROR', "Database Error: " . $sth->errstr);
  }
  elsif(!defined $username && defined $groupname) {
    $sth->execute($ctx->{'config'}{'dropboxes'}{'dropbox-pseudo-station'}, $groupname)
      or return (undef, 'ERROR', "Database Error: " . $sth->errstr);
  }
  else {
    $sth->execute($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, $path, $lowcart, $highcart, $groupdesc) = $sth->fetchrow_array()) {
    my @p = split(';', $params);
    if (defined $type) {
      if(!($type eq 'show' && $p[0] eq "S") && !($type eq 'jingle' && $p[0] eq "J") && !($type eq 'musicpool' && $p[0] eq "M")) {
        next
      }
    }

    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(defined($p[0]) && $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) {
        $sth->finish();
        return (undef, $status, $errorstring);
      }
      $entry->{'SHOWTITLE'} = $title;
      $entry->{'SHOWLOG'} = $log;

      $entry->{'SHOWRHYTHM'} = $p[1];
      $entry->{'SHOWRHYTHM'} = '????' unless((RHRD::utils::dropbox_param_rhythm_ok($entry->{'SHOWRHYTHM'}))[0]);
      $entry->{'SHOWDOW'} = $p[2];
      $entry->{'SHOWDOW'} = 0 unless((RHRD::utils::dropbox_param_dow_ok($entry->{'SHOWDOW'}))[0]);
      $entry->{'SHOWSTARTTIME'} = $p[3];
      $entry->{'SHOWSTARTTIME'} = '????' unless((RHRD::utils::dropbox_param_starttime_ok($entry->{'SHOWSTARTTIME'}))[0]);
      $entry->{'SHOWLEN'} = $p[4];
      $entry->{'SHOWLEN'} = 0 unless((RHRD::utils::dropbox_param_len_ok($entry->{'SHOWLEN'}))[0]);
    } elsif(defined($p[0]) && $p[0] eq "J") {
      $entry->{'TYPE'} = 'jingle';
      $entry->{'JINGLETITLE'} = $groupdesc;
    } elsif(defined($p[0]) && $p[0] eq "M") {
      $entry->{'TYPE'} = 'musicpool';
      $entry->{'MUSICPOOLTITLE'} = $groupdesc;
      $entry->{'MUSICPOOLCLOCK'} = $path;
    } else {
      $entry->{'TYPE'} = 'unknown';
    }

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

  return @allowed_dbs;
}

###########################  Log handling  ######################

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

  my $sql = qq{select NAME from LOGS;};
  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 @logs;
  while(my ($log) = $sth->fetchrow_array()) {
    push @logs, $log;
  }
  $sth->finish();

  return @logs;
}

sub create_schedule_log
{
  my $ctx = shift;
  my $year = shift;
  my $month = shift;
  my $day = shift;
  my @shows = @_;

  my $logname = sprintf("%04d_%02d_%02d", $year, $month, $day);
  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 (NAME, LOG_EXISTS, TYPE, DESCRIPTION, ORIGIN_USER, ORIGIN_DATETIME, LINK_DATETIME, SERVICE) values (?, 'N', 0, ?, ?, NOW(), NOW(), ?)};

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

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

  $sth->finish();

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

  my @carts = ();
  for my $show (@shows) {
    push @carts, { NUMBER => $show->{'ID'}, START_TIME => $show->{'START_TIME'}*1000, TRANS_TYPE => 2, TIME_TYPE => 1 };
  }

  (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 = ?, PURGE_DATE = ? where NAME = ?};

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

  my @purge_date = Date::Calc::Add_Delta_Days($year, $month, $day, $ctx->{'config'}{'shows'}{'schedule-lifetime'});
  $sth->execute($next_id, sprintf("%04d-%02d-%02d", $purge_date[0], $purge_date[1], $purge_date[2]), $logname)
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  $sth->finish();

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

###########################  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, $groupname) = @_;

  my $sql = qq{select TO_CART,SET_USER_DEFINED from DROPBOXES where STATION_NAME = ? order by TO_CART;};
  if(defined($groupname)) {
    $sql = qq{select TO_CART,SET_USER_DEFINED from DROPBOXES where STATION_NAME = ? and GROUP_NAME = ? order by TO_CART;};
  }

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

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

  my @show_dbs;
  while(my ($to_cart, $params) = $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) {
      $sth->finish();
      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 list_showids
{
  my ($ctx) = @_;

  my $sql = qq{select NUMBER from CART where GROUP_NAME = ? order by NUMBER};

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

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

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

  return @showids;
}

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

  my $sql = qq{select count(*) from CART where NUMBER = ?};

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

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

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

  return ($result, 'OK', "successfully fetched show status");
}

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

  my ($cnt, $status, $errorstring) = add_group($ctx, $groupname);
  unless(defined $cnt) {
    return (undef, $status, $errorstring);
  }

  (my $low_cart, my $high_cart, $errorstring) = get_shows_next_free_slot($ctx);
  if(!$low_cart) {
    return (undef, $high_cart, $errorstring);
  }

  ($cnt, $status, $errorstring) = set_group_cart_range($ctx, $groupname, $low_cart, $high_cart, 1, 'Y');
  unless(defined $cnt) {
    return (undef, $status, $errorstring);
  }

  ($cnt, $status, $errorstring) = RHRD::rddb::set_group_reports($ctx, $groupname, 'Y', 'N', 'N');
  unless(defined $cnt) {
    return (undef, $status, $errorstring);
  }

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

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

  for my $user (@{$ctx->{'config'}{'specialusers'}{'allshows'}}) {
    ($cnt, $status, $errorstring) = RHRD::rddb::add_group_member($ctx, $groupname, $user);
    return (undef, $status, $errorstring) unless(defined $cnt);
  }

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

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

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

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

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

  unless(defined($groupname)) {
    return (undef, 'ERROR', "Show not found");
  }
  return ($groupname, 'OK', 'success');
}

sub get_show_group_cart_range
{
  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();
  $sth->finish();

  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_group_carts_used
{
  my ($ctx, $groupname) = @_;

  my @dropboxes = RHRD::rddb::get_dropboxes($ctx, undef, $groupname, 'show');
  if(!defined $dropboxes[0]) {
    if(defined $dropboxes[1]) {
      return (undef, $dropboxes[1], $dropboxes[2]);
    }
    return ();
  }

  my @carts = ();
  for my $db (@dropboxes) {
    my @show_carts = RHRD::rddb::get_show_carts($ctx, $db->{'SHOWID'});
    if(!defined $show_carts[0] && defined $show_carts[1]) {
      return (undef, $show_carts[1], $show_carts[2]);
    }
    @carts = (@carts, @show_carts) if defined($show_carts[0])
  }

  return sort(@carts);
}

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

  my ($low_cart, $high_cart, $type, undef) = get_group_cart_range($ctx, $groupname);
  unless(defined $low_cart) {
    return (undef, $high_cart, $type);
  }

  my @carts = get_show_group_carts_used($ctx, $groupname);
  if(!defined $carts[0] && defined $carts[1]) {
    return (undef, $carts[1], $carts[2]);
  }

  for my $cart (@carts) {
    last if(($low_cart + $num_carts - 1) < $cart);
    $low_cart = $cart + 1;
  }
  if(($low_cart + $num_carts -1) > $high_cart) {
    return (undef, 'ERROR', 'not enough free carts left in group');
  }

  return ($low_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_info
{
  my ($ctx, $showid) = @_;

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

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

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

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

  my @p = split(';', $params);
  return (undef, 'ERROR', "show with $showid does not exist") unless ('S' eq $p[0]);

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

  $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];

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

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

  my ($group_low_cart, $group_high_cart, $status, $errorstring) = get_show_group_cart_range($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 = get_log_table_name_escaped($ctx, $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 (NAME, LOG_EXISTS, TYPE, DESCRIPTION, ORIGIN_USER, ORIGIN_DATETIME, LINK_DATETIME, SERVICE) values (?, 'N', 0, ?, ?, NOW(), NOW(), ?)};

  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);

  $sth->finish();

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

  my @carts = ();
  for my $cart (@{$ctx->{'config'}{'shows'}{'logprefix'}}) {
    push @carts, { NUMBER => $cart, START_TIME => 0, TRANS_TYPE => 1, TIME_TYPE => 0 };
  }
  for my $cart ($low_cart .. $high_cart) {
    push @carts, { NUMBER => $cart, START_TIME => 0, TRANS_TYPE => 0, TIME_TYPE => 0 };
  }
  for my $cart (@{$ctx->{'config'}{'shows'}{'logsuffix'}}) {
    push @carts, { NUMBER => $cart, START_TIME => 0, TRANS_TYPE => 0, TIME_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);

  $sth->finish();

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

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

  my ($low, $high, $type, undef) = RHRD::rddb::get_group_cart_range($ctx, $ctx->{'config'}{'specialgroups'}{'shows'});
  unless(defined $low) {
    return (undef, $high, $type);
  }

  my $sql = qq{select NUMBER from CART where GROUP_NAME = ? order by NUMBER};

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

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

  my $show_id = $low;
  while(my ($cart) = $sth->fetchrow_array()) {
    last if($show_id < $cart);
    $show_id += 1;
  }
  $sth->finish();

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

sub create_show_macro_cart
{
  my ($ctx, $logname, $title) = @_;
  my $macro = 'LL 1 ' . $logname . ' 0!';

  my ($number, $status, $errorstring) = get_next_free_showid($ctx);

  my $sql = qq{insert into CART (NUMBER, TYPE, GROUP_NAME, TITLE, MACROS, VALIDITY, METADATA_DATETIME) values (?, 2, ?, ?, ?, 3, NOW())};

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

  $sth->execute($number, $ctx->{'config'}{'specialgroups'}{'shows'}, $title, $macro)
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  $sth->finish();

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

sub create_show_dropbox
{
  my ($ctx, $groupname, $show_id, $rhythm, $dow, $starttime, $len) = @_;
  my $param = join(';', ('S', $rhythm, $dow, $starttime, $len));

  my $sql = qq{insert into DROPBOXES (STATION_NAME, GROUP_NAME, NORMALIZATION_LEVEL, AUTOTRIM_LEVEL, TO_CART, FIX_BROKEN_FORMATS, SET_USER_DEFINED) values (?, ?, ?, ?, ?, 'Y', ?)};

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

  my $cnt = $sth->execute($ctx->{'config'}{'dropboxes'}{'dropbox-pseudo-station'}, $groupname, $ctx->{'config'}{'dropboxes'}{'norm-level'}, $ctx->{'config'}{'dropboxes'}{'trim-level'}, $show_id, $param)
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  $sth->finish();

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

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

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

  my @show_carts = get_show_carts($ctx, $showid);
  if(!defined $show_carts[0] && defined $show_carts[1]) {
    return (undef, $show_carts[1], $show_carts[2]);
  }

  my @actions = ({
      # Delete Dropbox
      sql => qq{delete from DROPBOXES where TO_CART = ?;},
      param => $showid,
      name => 'dropboxes',
      cnt => 0
    }, {
      # Delete Macro Carts
      sql => qq{delete from CART where NUMBER = ?;},
      param => $showid,
      name => 'macro carts',
      cnt => 0
    }, {
      # Delete Log Entry
      sql => qq{delete from LOGS where NAME = ?;},
      param => $logname,
      name => 'log 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($href->{param})
      or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

    $sth->finish();
  }

  if(!defined $show_carts[0]) {
    push @actions, { name => 'audio carts', cnt => 0 };
  } else {
    my $sth_cut = $ctx->{'dbh'}->prepare(qq{delete from CUTS where CART_NUMBER = ?;})
      or return (undef, 'ERROR', "Database Error: " . $ctx->{'dbh'}->errstr);
    my $sth_cart = $ctx->{'dbh'}->prepare(qq{delete from CART where NUMBER = ?;})
      or return (undef, 'ERROR', "Database Error: " . $ctx->{'dbh'}->errstr);

    my $cnt = 0;
    for my $cart (@show_carts) {
      $sth_cut->execute($cart) or return (undef, 'ERROR', "Database Error: " . $sth_cut->errstr);
      $sth_cut->finish();
      $cnt += ($sth_cart->execute($cart) or return (undef, 'ERROR', "Database Error: " . $sth_cart->errstr));
      $sth_cart->finish();
    }
    push @actions, { name => 'audio carts', cnt => $cnt };
  }

  (my $cnt, $status, $errorstring) = drop_log_table($ctx, $logname);
  unless (defined $cnt) {
    return (undef, $status, $errorstring);
  }
  push @actions, { name => 'log tables', cnt => $cnt };

  return @actions;
}

###########################  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'});
}

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

  return 0 if $username eq '';

  for my $groupuser (@{$ctx->{'config'}{'specialusers'}{'allpools'}}) {
    if ($username eq $groupuser) {
      return 1;
    }
  }
  return 0;
}

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

  my $sql = 'select SVC_CLOCKS.HOUR,CLOCKS.SHORT_NAME,CLOCKS.COLOR,GROUPS.DESCRIPTION from DROPBOXES,GROUPS,CLOCKS,SVC_CLOCKS where SVC_CLOCKS.SERVICE_NAME = ? and SVC_CLOCKS.CLOCK_NAME = CLOCKS.NAME and CLOCKS.SHORT_NAME = DROPBOXES.PATH and DROPBOXES.GROUP_NAME = GROUPS.NAME order by SVC_CLOCKS.HOUR;';

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

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

  my @clocks;
  while(my ($idx, $shortname, $color, $title) = $sth->fetchrow_array()) {
    my $dow = int($idx / 24) + 1;
    $dow = 0 if $dow > 6;
    my $hour = $idx % 24;

    my $clock = {};
    $clock->{'DOW'} = $dow;
    $clock->{'HOUR'} = $hour;
    $clock->{'SHORTNAME'} = $shortname;
    $clock->{'COLOR'} = $color;
    $clock->{'TITLE'} = $title;
    push @clocks, $clock;
  }

  $sth->finish();

  return @clocks;
}

sub set_musicpools_clock
{
  my ($ctx, $dow, $hour, $shortname) = @_;

  my $sql = qq{select NAME from CLOCKS where SHORT_NAME = ?;};

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

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

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

  if(!defined $name) {
    return (undef, 'ERROR', "clock '" . $shortname . "' does not exist");
  }

  my $day = $dow - 1;
  $day = 6 if $dow == 0;
  my $idx = (($day*24) + $hour);
  $sql = 'update SVC_CLOCKS set CLOCK_NAME = ? where SERVICE_NAME = ? and HOUR = ?';

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

  my $cnt = $sth->execute($name, $ctx->{'config'}{'shows'}{'service'}, $idx)
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  unless($cnt == 1) {
    return (undef, 'ERROR', "clock does not exist")
  }
  return (1, 'OK', 'success');
}

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