package events;

use strict;
use warnings;
no warnings 'redefine';

use Data::Dumper;
$Data::Dumper::Sortkeys=1;
use MIME::Base64();
use Encode();
use Storable 'dclone';
use DBI();
use template();
use config();
use time();
use db();

use markup();
use log();
use project();
use studios();

our @EXPORT_OK = qw(
  init
  get_cached_or_render
  get
  modify_results
  get_query
  render
  get_running_event_id
  delete
  check_params
  configure_cache
  get_duration
  calc_dates
  get_keys
  add_recordings
);

sub init {
}

sub get_cached_or_render($$$) {
    my ($response, $config, $request) = @_;

    my $params = $request->{params}->{checked};
    my $results = events::get( $config, $request );
    events::render( $response, $config, $request, $results );
    return $response;
}

sub get_prev{
    my ($config, $event) = @_;
    my $params = {
        till_date => time::check_date($event->{start}),
        till_time => time::check_time($event->{start}),
        archive   => 'all',
        order     => 'desc',
        limit     => 1,
        exclude_locations => 1,
        exclude_projects  => 1,
    };
    my $request = {
        url    => $ENV{QUERY_STRING},
        params => {
            original  => $params,
            checked  => events::check_params( $config, $params ),
        }
    };
    $request->{params}->{checked}->{stop_nav} = 1;
    my $results = events::get( $config, $request );
    return $results->[0];
}

sub get_next{
    my ($config, $event) = @_;

    my $params = {
        from_date => time::check_date($event->{end}),
        from_time => time::check_time($event->{end}),
        archive   => 'all',
        order     => 'asc',
        limit     => 1,
        exclude_locations => 1,
        exclude_projects  => 1,
    };
    my $request = {
        url    => $ENV{QUERY_STRING},
        params => {
            original => $params,
            checked  => events::check_params( $config, $params ),
        },
    };
    $request->{params}->{checked}->{stop_nav} = 1;
    my $results = events::get( $config, $request );
    return $results->[0];
}

sub get($$);
sub get($$) {
    my ($config, $request) = @_;

    my $dbh = db::connect( $config, $request );

    ( my $query, my $bind_values ) = events::get_query( $dbh, $config, $request );
    my $results = db::get( $dbh, $$query, $bind_values );
    $results = events::add_recordings($dbh, $config, $request, $results) if $request->{params}->{checked}->{all_recordings};
    $results = events::modify_results( $dbh, $config, $request, $results );

    # get prev and next event
    if ( @$results==1
        and !$request->{params}->{checked}->{stop_nav}  #< prevent recursion!
        and  my $event_id = $request->{params}->{original}->{event_id}
    ){
        my $event = $results->[0];
        $event->{prev_event_id} = (get_prev($config, $event)//{})->{event_id};
        $event->{next_event_id} = (get_next($config, $event)//{})->{event_id};
    }

    return $results;
}

sub modify_results ($$$$) {
    my ($dbh, $config, $request, $results) = @_;

    my $params = $request->{params}->{checked};

    my $running_event_id = 0;
    my $projects         = {};
    my $studios          = {};

    my $time_diff = '';
    if ( scalar @$results > 0 ) {
        $results->[0]->{__first__} = 1;
        $results->[-1]->{__last__} = 1;
        $running_event_id          = events::get_running_event_id($dbh);
    }

    if ( ( defined $params->{template} ) && ( $params->{template} =~ /\.xml/ ) ) {
        $time_diff = time::utc_offset( $config->{date}->{time_zone} );
        $time_diff =~ s/(\d\d)(\d\d)/$1\:$2/g;
    }

    my $previous_result = { start_date => '' };
    my $counter = 1;
    for my $result (@$results) {
        if ( defined $params->{template} ) {
            if ( $params->{template} =~ /\.ics$/ ) {
                $result->{content_ical} =
                  markup::plain_to_ical( $result->{content} );
                $result->{title_ical} =
                  markup::plain_to_ical( $result->{title} );
                $result->{user_title_ical} =
                  markup::plain_to_ical( $result->{user_title} );
                $result->{excerpt_ical} =
                  markup::plain_to_ical( $result->{excerpt} );
                $result->{user_excerpt_ical} =
                  markup::plain_to_ical( $result->{user_excerpt} );
                $result->{series_name} =
                  markup::plain_to_ical( $result->{series_name} );
                $result->{created_at} =~ s/ /T/gi;
                $result->{created_at} =~ s/[\:\-]//gi;
                $result->{modified_at} =~ s/ /T/gi;
                $result->{modified_at} =~ s/[\:\-]//gi;

            } elsif ( $params->{template} =~ /\.atom\.xml/ ) {
                $result->{excerpt} = '' unless defined( $result->{excerpt} );
                $result->{excerpt} = "lass dich ueberraschen"
                  if ( $result->{excerpt} eq '' );

                #                $result->{excerpt}    =markup::plain_to_xml($result->{excerpt});
                #                $result->{title}    =markup::plain_to_xml($result->{title});
                #                $result->{series_name}    =markup::plain_to_xml($result->{series_name});
                #                $result->{program}    =markup::plain_to_xml($result->{program});

                $result->{created_at} =~ s/ /T/gi;
                $result->{created_at} .= $time_diff;
                $result->{modified_at} =~ s/ /T/gi;
                $result->{modified_at} .= $time_diff;
            } elsif ( $params->{template} =~ /\.rss\.xml/ ) {
                $result->{excerpt} = '' unless defined( $result->{excerpt} );
                $result->{excerpt} = "lass dich ueberraschen"
                  if ( $result->{excerpt} eq '' );
                $result->{modified_at} =
                  time::datetime_to_rfc822( $result->{modified_at} );
                if ( $result->{created_at} =~ /[1-9]/ ) {
                    $result->{created_at} =
                      time::datetime_to_rfc822( $result->{created_at} );
                } else {
                    $result->{created_at} = $result->{modified_at};
                }

            }
        }
        $result->{series_name} ||= '';
        $result->{series_name} = '' if $result->{series_name} eq '_single_';

        $result->{rerun} = '' unless defined $result->{rerun};

        $result->{title} = '' unless defined $result->{title};
        if ( $result->{title} =~ /\#(\d+)([a-z])?\s*$/ ) {
            $result->{episode} = $1 unless defined $result->{episode};
            $result->{rerun} = $2 || '' unless ( $result->{rerun} =~ /\d/ );
            $result->{title} =~ s/\#\d+[a-z]?\s*$//;
            $result->{title} =~ s/\s+$//;
        }
        $result->{rerun} = '' if ( $result->{rerun} eq '0' );

        if (   ( defined $result->{recurrence_count} )
            && ( $result->{recurrence_count} > 0 ) )
        {
            $result->{recurrence_count_alpha} =
              markup::base26( $result->{recurrence_count} + 1 );
            $result->{recurrence_id} = $result->{recurrence};
        } else {
            $result->{recurrence_count_alpha} = '';
            $result->{recurrence_count}       = '';
        }

        # set title keys
        my $keys = get_keys($result);
        for my $key ( keys %$keys ) {
            $result->{$key} = $keys->{$key};
        }

        $result = calc_dates( $config, $result, $params, $previous_result, $time_diff );
        get_listen_key($config, $result) unless $params->{set_no_listen_keys};

        $result->{event_uri} = '';
        if ( ( defined $result->{program} ) && ( $result->{program} ne '' ) ) {
            $result->{event_uri} .= $result->{program};
            $result->{event_uri} .= '-'
              if ( ( $result->{series_name} ne '' )
                || ( $result->{title} ne '' ) );
        }
        if ( ($result->{series_name}//'') ne '' ) {
            $result->{event_uri} .= $result->{series_name};
            $result->{event_uri} .= '-' if ( $result->{title} ne '' );
        }
        $result->{event_uri} .= $result->{title} if length $result->{title};
        $result->{event_uri} =~ s/\#/Nr./g;
        $result->{event_uri} =~ s/\&/und/g;
        $result->{event_uri} =~ s/\//\%2f/g;
        $result->{event_uri} =~ s/[?]//g;

        $result->{rds_title} = $result->{event_uri};
        $result->{rds_title} =~ s/[^a-zA-Z0-9\-]/\_/gi;
        $result->{rds_title} =~ s/\_{2,99}/\_/gi;
        $result->{rds_title} = substr( $result->{rds_title}, 0, 63 );

        #$result->{event_id}=$result->{id};

        $result->{base_url}         = $request->{base_url};
        $result->{base_domain}      = $config->{locations}->{base_domain};
        $result->{static_files_url} = $config->{locations}->{static_files_url};
        $result->{source_base_url}  = $config->{locations}->{source_base_url};
        $result->{local_base_url}   = $config->{locations}->{local_base_url};
        $result->{widget_render_url}= $config->{locations}->{widget_render_url};

        $result->{is_running} = 1 if $running_event_id
            && $result->{event_id}
            && $running_event_id eq $result->{event_id};

        if (defined $result->{comment_count}){
            $result->{one_comment} = 1 if ( $result->{comment_count} == 1 );
            $result->{no_comment}  = 1 if ( $result->{comment_count} == 0 );
        }

        {
            my $url   = $config->{locations}->{local_media_url} // '';
            my $image = $result->{image};
            my $conf  = $config->{locations};
            my $basic_url = "$url/images/";

            if ( defined $result->{image} ) {
                $result->{thumb_url} = ($conf->{thumbs_url} // $basic_url) . $image;
                $result->{icon_url}  = ($conf->{icons_url}  // $basic_url) . $image;
                $result->{image_url} = ($conf->{images_url} // $basic_url) . $image;
            }

            if ( defined $result->{series_image} ) {
                $result->{series_thumb_url} = ($conf->{thumbs_url} // $basic_url) . $image;
                $result->{series_icon_url}  = ($conf->{icons_url}  // $basic_url) . $image;
                $result->{series_image_url} = ($conf->{images_url} // $basic_url) . $image;
            }
        }

        $result->{location_css} = $result->{location} || '';
        $result->{location_css} = lc( $result->{location_css} );
        $result->{location_css} =~ s/\.//g;
        $result->{location_css} =~ s/\s//g;
        $result->{ 'location_label_' . $result->{location_css} } = 1;

        # add project by name
        my $project_name = $result->{project};
        if ( defined $project_name ) {

            #print STDERR "found project:$project_name\n";
            unless ( defined $projects->{$project_name} ) {
                my $results = project::get( $config, { name => $project_name } );
                $projects->{$project_name} = $results->[0] || {};
            }
            my $project = $projects->{$project_name};
            for my $key ( keys %$project ) {
                $result->{ 'project_' . $key } = $project->{$key};
            }
        } else {
            printf STDERR "events::get - unknown project for event %s\n", $result->{id} // "undef";
        }

        #if project_id is set add columns from project (cached)
        my $project_id = $result->{project_id};
        if ( defined $project_id ) {
            unless ( defined $projects->{$project_id} ) {
                my $results = project::get( $config, { project_id => $project_id } );
                $projects->{$project_id} = $results->[0] || {};
            }
            my $project = $projects->{$project_id};
            for my $key ( keys %$project ) {
                $result->{ 'project_' . $key } = $project->{$key};
            }
        }

        #if studio_id is set add columns from studio (cached)
        my $studio_id = $result->{studio_id};
        if ( defined $studio_id ) {
            unless ( defined $studios->{$studio_id} ) {
                my $results = studios::get( $config, { studio_id => $studio_id } );
                $studios->{$studio_id} = $results->[0] || {};
            }
            my $studio = $studios->{$studio_id};
            for my $key ( keys %$studio ) {
                $result->{ 'studio_' . $key } = $studio->{$key};
            }
        }

        for my $name ( keys %{ $config->{mapping}->{events} } ) {
            my $val = '';
            if (   ( defined $name )
                && ( defined $config->{mapping}->{events}->{$name} )
                && ( defined $result->{$name} ) )
            {
                $val = $config->{mapping}->{events}->{$name}->{ $result->{$name} }
                  || '';
                $result->{ $name . '_mapped' } = $val if ( $val ne '' );
            }
        }

        $previous_result = $result;

        $result->{ 'counter_' . $counter } = 1;
        $counter++;

        if (   ( defined $params->{template} )
            && ( $params->{template} =~ /(list|details)/ ) )
        {
            if ( ( defined $result->{excerpt} ) && ( length( $result->{excerpt} ) > 250 ) ) {
                $result->{excerpt} = substr( $result->{excerpt}, 0, 250 ) . '...';
            }

            if ( ( defined $result->{user_excerpt} ) && ( length( $result->{user_excerpt} ) > 250 ) ) {
                $result->{user_excerpt} = substr( $result->{user_excerpt}, 0, 250 ) . '...';
            }
        }

        #build content
        if ( ( defined $params->{template} ) && ( $params->{template} =~ /\.html/ ) ) {
            $result->{html_content} = events::format($result, 'content') if defined $result->{content};
            $result->{html_topic}   = events::format($result, 'topic') if defined $result->{topic};
        }

        #detect if images are in content or topic field
        my $image_in_text = 0;
        $image_in_text = 1
          if ( defined $result->{content} )
          && ( $result->{content} =~ /<img / );
        $image_in_text = 1
          if ( defined $result->{topic} )
          && ( $result->{topic} =~ /<img / );
        $result->{no_image_in_text} = 1 if $image_in_text == 0;

        if (
            ( defined $params->{template} )
            && (   ( $params->{template} =~ /event_perl\.txt$/ )
                || ( $params->{template} =~ /event_file_export\.txt$/ ) )

          )
        {
            for my $key ( keys %$result ) {
                $result->{$key} =~ s/\|/\\\|/g if defined $result->{$key};
            }

            #            $result->{content}='no';
        }

    }    # end for results
    add_recurrence_dates( $config, $results );
    return $results;
}

sub format {
    my ($event, $field) = @_;
    if (($event->{content_format}//'') eq 'markdown'){
        $event->{$field} =  markup::markdown_to_html( $event->{$field} );
    } else {
        $event->{$field} = markup::fix_line_ends( $event->{$field} );
        $event->{$field} = markup::creole_to_html( $event->{$field} );
    }
    return $event->{$field};
}

sub add_recurrence_dates {
    my ($config, $results) = @_;

    # get unique list of recurrence ids from results
    my $recurrence_dates = {};
    for my $result (@$results) {
        next unless defined $result->{recurrence};
        next if $result->{recurrence} == 0;
        $recurrence_dates->{ $result->{recurrence} } = 0;
    }

    my @event_ids = keys %$recurrence_dates;
    return if @event_ids == 0;

    # query start date of recurrences
    my $conditions  = [];
    my $bind_values = [];
    for my $id (@event_ids) {
        push @$conditions,  '?';
        push @$bind_values, $id;
    }
    $conditions = join( ',', @$conditions );

    my $query = qq{
        select id event_id, start
        from   calcms_events
        where  id in ($conditions)
    };

    my $dbh = db::connect($config);
    my $events = db::get( $dbh, $query, $bind_values );

    # store start dates by recurrence id
    for my $event (@$events) {
        $recurrence_dates->{ $event->{event_id} } = $event->{start};
    }

    # set start dates to results
    my $language = $config->{date}->{language} || 'en';
    for my $result (@$results) {
        next unless defined $result->{recurrence};
        next if $result->{recurrence} == 0;
        my $rdate = $recurrence_dates->{ $result->{recurrence} };
        if ($rdate){
            $result->{recurrence_date} = $rdate;
            $result->{recurrence_date_name} = time::date_format( $config, $rdate, $language );
            ( $result->{recurrence_time_name} ) = $rdate =~ m/(\d\d\:\d\d)\:\d\d/ ;
            my $ymd = time::date_to_array($rdate);
            my $weekdayIndex = time::weekday( $ymd->[0], $ymd->[1], $ymd->[2] );
            $result->{recurrence_weekday_name}       = time::getWeekdayNames($language)->[$weekdayIndex];
            $result->{recurrence_weekday_short_name} = time::getWeekdayNamesShort($language)->[$weekdayIndex];
        }

    }

}

sub calc_dates {
    my ($config, $result, $params, $previous_result, $time_diff) = @_;

    $params ||= {};
    $previous_result ||= {};
    $time_diff ||= '';

    my $language = $config->{date}->{language} || 'en';
    $result->{utc_offset} = $time_diff;
    $result->{time_zone} = $config->{date}->{time_zone};
    $result->{start_datetime} = $result->{start} =~ s/ /T/r;
    $result->{end_datetime} = $result->{end} =~ s/ /T/r;
    $result->{dtstart} = $result->{start_datetime} =~ s/[:\-]//rg;
    $result->{dtend} = $result->{end_datetime} =~ s/[:\-]//gr;

    if ($result->{start_datetime} =~ /(\d{4})-(\d{2})-(\d{2})T(\d{2}):(\d{2})/) {
        @{$result}{qw(start_year start_month start_day start_hour start_minute)} = ($1, $2, $3, $4, $5);
    }

    $result->{day} = time::datetime_to_array($result->{start})->[3] < 6
        ? time::add_days_to_date($result->{start}, -1)
        : time::datetime_to_date($result->{start})
        unless defined $result->{day};

    $result->{start_date} ||= time::datetime_to_date($result->{start});
    $result->{end_date} ||= time::datetime_to_date($result->{end});

    $result->{start_utc_epoch} = time::datetime_to_utc($result->{start_datetime}, $config->{date}->{time_zone});
    $result->{start_datetime_utc} = time::datetime_to_utc_datetime($result->{start_datetime}, $config->{date}->{time_zone});
    $result->{end_utc_epoch} = time::datetime_to_utc($result->{end_datetime}, $config->{date}->{time_zone});
    $result->{end_datetime_utc} = time::datetime_to_utc_datetime($result->{end_datetime}, $config->{date}->{time_zone});

    if (defined $previous_result && defined $previous_result->{start_date}
      && $result->{start_date} ne $previous_result->{start_date}) {
        $result->{is_first_of_day} = 1;
        $previous_result->{is_last_of_day} = 1;
    }

    $result->{start_date_name} = time::date_format($config, $result->{start_date}, $language);
    $result->{end_date_name} = time::date_format($config, $result->{end_date}, $language);

    $result->{start_time} = $result->{start_time_name} = $1 if $result->{start} =~ /(\d{2}:\d{2}):\d{2}/;
    $result->{end_time} = $result->{end_time_name} = $1 if $result->{end} =~ /(\d{2}:\d{2}):\d{2}/;

    if (defined $result->{weekday}) {
        my $weekdayIndex = time::getWeekdayIndex($result->{weekday}) || 0;
        $result->{weekday_name} = time::getWeekdayNames($language)->[$weekdayIndex];
        $result->{weekday_short_name} = time::getWeekdayNamesShort($language)->[$weekdayIndex];
    }

    return $result;
}

sub get_listen_key($$){
    my ($config, $event) =@_;

    my $time_zone = $config->{date}->{time_zone};
    my $start = time::datetime_to_utc( $event->{start_datetime}, $time_zone );
    my $now = time::datetime_to_utc( time::time_to_datetime( time() ), $time_zone);
    my $over_since = $now-$start;

    return if $over_since < 0;
    return if $over_since > 7*24*60*60;

    my $archive_dir = $config->{locations}->{local_archive_dir};
    my $archive_url = $config->{locations}->{listen_url};
    return $event->{listen_url} = $archive_url . '/' . $event->{listen_key} if 
        defined $event->{listen_key} && -l $archive_dir .'/'. $event->{listen_key};
    set_listen_key($config, $event) unless $event->{listen_key};
}

sub set_listen_key{
    my ($config, $event) =@_;
    my $archive_dir = $config->{locations}->{local_archive_dir};
    my $archive_url = $config->{locations}->{listen_url};
    my $datetime = $event->{start_datetime};
    if ( $datetime =~ /(\d\d\d\d\-\d\d\-\d\d)[ T](\d\d)\:(\d\d)/ ) {
        $datetime = $1 . '\ ' . $2 . '_' . $3;
    } else {
        print STDERR "update_recording_link: no valid datetime found $datetime\n";
        return;
    }
    my @files = glob( $archive_dir . '/' . $datetime . '*.mp3' );
    return if @files <= 0;

    my $key  = int( rand(99999999999999999) );
    $key = MIME::Base64::encode_base64($key);
    $key =~ s/[^a-zA-Z0-9]//g;
    $key .='.mp3';

    my $audio_file = Encode::decode( "UTF-8", $files[0] );
    my $link = $archive_dir . '/' . $key;
    symlink $audio_file, $link or die "cannot create $link, $!";
    $event->{listen_url} = $archive_url . '/' . $key;
    $event->{listen_key} = $key;
                    
    return undef unless defined $event->{event_id};
    return undef unless defined $event->{listen_key};
    my $bindValues = [ $event->{listen_key}, $event->{event_id} ];

    my $query = qq{
        update calcms_events
        set listen_key=? 
        where id=?;
    };
    my $dbh = db::connect($config);
    my $recordings = db::put( $dbh, $query, $bindValues );
}

sub set_upload_status($$){
    my ($config, $event) = @_; 

    for ('event_id', 'upload_status') {
        return undef unless defined $event->{$_}
    };

    my $bindValues = [ $event->{upload_status}, $event->{event_id}, $event->{upload_status} ];

    my $query = qq{
        update calcms_events
        set upload_status=? 
        where id=? and upload_status!=?;
    };
    my $dbh = db::connect($config);
    my $recordings = db::put( $dbh, $query, $bindValues );
}

sub add_recordings($$$$) {
    my ($dbh, $config, $request, $events) = @_;

    return $events unless defined $events;

    my $eventsById = { map { $_->{event_id} => $_ } @$events };
    my $qms        = join ', ', map { '?' } @$events;
    my $bindValues = [map {$_->{event_id}} @$events];

    my $query = qq{
        select  *
        from    calcms_audio_recordings
        where   event_id in ($qms)
        order by created_at;
    };

    $dbh = db::connect($config) unless defined $dbh;
    my $recordings = db::get( $dbh, $query, $bindValues );
    push @{ $eventsById->{$_->{event_id}}->{recordings} }, $_ for @$recordings;

    return $events;
}

sub getDateQueryConditions ($$$) {
    my ($config, $params, $bind_values) = @_;

    # conditions by date
    my $date_conds = [];

    #date, today, tomorrow, yesterday
    my $date = '';
    $date = time::date_cond( $params->{date} ) if $params->{date} ne '';

    my $from_date = '';
    $from_date = time::date_cond( $params->{from_date} ) if $params->{from_date} ne '';

    my $till_date = '';
    $till_date = time::date_cond( $params->{till_date} ) if $params->{till_date} ne '';

    my $from_time = '';
    $from_time = time::time_cond( $params->{from_time} ) if $params->{from_time} ne '';

    my $till_time = '';
    $till_time = time::time_cond( $params->{till_time} ) if $params->{till_time} ne '';

    my $time = $params->{time};
    $time = '' unless defined $time;

    my $date_range_include = $params->{date_range_include};
    my $day_starting_hour  = $config->{date}->{day_starting_hour};

    if ( $date eq 'today' ) {
        my $date = time::get_event_date($config);
        push @$date_conds,  ' ( start_date = ? ) ';
        push @$bind_values, $date;
        return $date_conds;
    }

    # given date
    my $start = time::datetime_cond( $date . 'T00:00:00' );
    if ( $start ne '' ) {
        $start = time::add_hours_to_datetime( $start, $day_starting_hour );
        my $end = time::add_hours_to_datetime( $start, 24 );

        if ( $date_range_include eq '1' ) {
            push @$date_conds,  ' end > ? ';
            push @$bind_values, $start;
        } else {
            push @$date_conds,  ' start >= ? ';
            push @$bind_values, $start;
        }

        push @$date_conds,  ' start < ? ';
        push @$bind_values, $end;
        return $date_conds;
    }

    if ( $time eq 'now' ) {
        push @$date_conds, qq{
                 (
                 ( unix_timestamp(end)   >  unix_timestamp(now() ) )
                 and
                 ( unix_timestamp(start) <= unix_timestamp(now() ) )
                 )
             };
        return $date_conds;
    }

    if ( $time eq 'future' ) {
        push @$date_conds, qq{
                    (
                    ( unix_timestamp(end)   >  unix_timestamp(now() ) )
                    and
                    ( unix_timestamp(end) - unix_timestamp(now() ) ) < 7*24*3600
                    )
                };
        return $date_conds;
    }

    #from_date and from_time is defined
    if ( ( $from_date ne '' ) && ( $from_time ne '' ) ) {
        my $datetime = time::datetime_cond( $from_date . 'T' . $from_time );
        if ( $datetime ne '' ) {
            if ( $date_range_include eq '1' ) {
                push @$date_conds,  ' end > ? ';
                push @$bind_values, $datetime;
                $from_date = '';
            } else {
                push @$date_conds,  ' start >= ? ';
                push @$bind_values, $datetime;
                $from_date = '';
            }
        }
    }

    #till_date and till_time is defined
    if ( ( $till_date ne '' ) && ( $till_time ne '' ) ) {
        my $datetime = time::datetime_cond( $till_date . 'T' . $till_time );
        if ( $datetime ne '' ) {
            push @$date_conds,  ' start < ? ';
            push @$bind_values, $datetime;
            $till_date = '';
        }
    }

    # after start of daily broadcast
    if ( ( $from_date ne '' ) && ( $from_time eq '' ) ) {
        my $start = time::datetime_cond( $from_date . 'T00:00:00' );
        $start = time::add_hours_to_datetime( $start, $day_starting_hour );

        if ( $date_range_include eq '1' ) {

            # end is after start
            push @$date_conds,  ' ( end >= ? )';
            push @$bind_values, $start;
        } else {
            push @$date_conds,  ' ( start >= ? ) ';
            push @$bind_values, $start;
        }
    }

    # before end of daily broadcast
    if ( ( $till_date ne '' ) && ( $till_time eq '' ) ) {
        my $end = time::datetime_cond( $till_date . 'T00:00:00' );
        $end = time::add_hours_to_datetime( $end, $day_starting_hour );
        if ( $date_range_include eq '1' ) {

            # start is before end
            push @$date_conds,  ' ( start <= ? )';
            push @$bind_values, $end;
        } else {
            push @$date_conds,  ' ( end <= ? ) ';
            push @$bind_values, $end;
        }
    }

    if ( $params->{weekday} ne '' ) {
        my $weekday = $params->{weekday};
        $weekday += 1;
        $weekday -= 7 if ( $weekday > 7 );
        push @$date_conds,  ' (dayofweek(start)= ?) ';
        push @$bind_values, $weekday;
    }

    if ( $params->{last_days} ) {
        my $d = int $params->{last_days};
        push @$date_conds,  qq{ ( end between date_sub(now(),INTERVAL $d DAY) and now() ) };
    } elsif ( $params->{archive} eq 'past' ) {
        my $date = time::get_event_date($config);
        if ( $date ne '' ) {
            push @$date_conds,  ' ( start < ? ) ';
            push @$bind_values, $date;
        }

    } elsif ( $params->{archive} eq 'future' ) {
        my $date = time::get_event_date($config);
        if ( $date ne '' ) {
            push @$date_conds,  ' ( end >= ? ) ';
            push @$bind_values, $date;
        }
    }
    return $date_conds;

}

# if "all_recordings" is set in params, all event recordings will be included
# if "active_recording" is set in params, recordings date and path will be included
sub get_query($$$) {
    my ($dbh, $config, $request) = @_;

    my $params = $request->{params}->{checked};
    $params->{all_recordings}   //= '';
    $params->{active_recording} //= '';
    $params->{only_active_recording} //= '';

    my $bind_values = [];
    my $where_cond  = [];
    my $order_cond  = '';
    my $limit_cond  = '';

    if ( $params->{event_id} ne '' ) {

        # conditions by event id
        push @$where_cond, 'e.id=?';
        $bind_values = [ $params->{event_id} ];

        #filter by published, default=1 to see published only, set published='all' to see all
        my $published = $params->{published} || '1';
        if ( ( $published eq '0' ) || ( $published eq '1' ) ) {
            push @$where_cond,  'published=?';
            push @$bind_values, $published;
        }

        my $draft = $params->{draft} || '0';
        if ( ( $draft eq '0' ) || ( $draft eq '1' ) ) {
            push @$where_cond,  'draft=?';
            push @$bind_values, $draft;
        }

    } else {

        my $date_conds = getDateQueryConditions( $config, $params, $bind_values );
        my $date_cond = join " and ", @$date_conds;

        push @$where_cond, $date_cond if ( $date_cond ne '' );
    }

    # location
    my $location_cond = '';
    if ( $params->{location} ne '' ) {
        my $location = ( split( /\,/, $params->{location} ) )[0];
        $location =~ s/[^a-zA-Z0-9\-\_]/%/g;
        $location =~ s/%{2,99}/%/g;
        if ( $location ne '' ) {
            $location_cond = ' location like ? ';
            push @$bind_values, $location;
        }
    }

    # exclude location
    my $exclude_location_cond = '';
    if ( $params->{exclude_locations} eq '1' ) {
        if ( $params->{locations_to_exclude} ne '' ) {
            my @locations_to_exclude = split( /,/, $params->{locations_to_exclude} );
            $exclude_location_cond = 'location not in (' . join( ",", map { '?' } @locations_to_exclude ) . ')';
            for my $location (@locations_to_exclude) {
                $location =~ s/^\s+//g;
                $location =~ s/\s+$//g;
                push @$bind_values, $location;
            }
        }
    }

    # exclude project
    my $exclude_project_cond = '';
    if ( $params->{exclude_projects} eq '1' ) {
        if ( $params->{projects_to_exclude} ne '' ) {
            my @projects_to_exclude = split( /,/, $params->{projects_to_exclude} );
            $exclude_project_cond = 'project not in (' . join( ",", map { '?' } @projects_to_exclude ) . ')';
            for my $project (@projects_to_exclude) {
                $project =~ s/^\s+//g;
                $project =~ s/\s+$//g;
                push @$bind_values, $project;
            }
        }
    }

    my $series_name_cond = '';
    if (   ( defined $params->{series_name} )
        && ( $params->{series_name} ne '' ) )
    {
        my $series_name = ( split( /\,/, $params->{series_name} ) )[0];
        $series_name =~ s/[^a-zA-Z0-9]/%/g;
        $series_name =~ s/%{2,99}/%/g;
        if ( $series_name ne '' ) {
            $series_name_cond = ' series_name like ? ';
            push @$bind_values, $series_name;
        }
    }

    #filter for tags
    my $tag_cond = '';
    if ( ( defined $params->{tag} ) && ( $params->{tag} ne '' ) ) {
        my @tags = ( split( /\,/, $params->{tag} ) );
        if ( scalar @tags > 0 ) {
            my $tags = join ",", ( map { '?' } @tags );
            for my $tag (@tags) {
                push @$bind_values, $tag;
            }
            $tag_cond = qq{
                id in(
                    select event_id from calcms_tags
                    where name in($tags)
                )
            };
        }
    }
    $tag_cond = '';

    my $title_cond = '';
    if ( ( defined $params->{title} ) && ( $params->{title} ne '' ) ) {
        my $title = ( split( /\,/, $params->{title} ) )[0];
        $title =~ s/[^a-zA-Z0-9]/%/g;
        $title =~ s/%{2,99}/%/g;
        $title =~ s/^\%//;
        $title =~ s/\%$//;
        $title = '%' . $title . '%';
        if ( $title ne '' ) {
            $title_cond = ' title like ? ';
            push @$bind_values, $title;
        }
    }

    my $search_cond = '';
    if ( ( defined $params->{search} ) && ( $params->{search} ne '' ) ) {
        my $search = lc $params->{search};
        $search =~ s/(?=[\\%_])/\\/g;
        $search =~ s/^[\%\s]+//;
        $search =~ s/[\%\s]+$//;
        if ( $search ne '' ) {
            $search = '%' . $search . '%';
            my @attr = ( 'title', 'series_name', 'excerpt', 'content', 'topic' );
            $search_cond = "(" . join( " or ", map { 'lower(' . $_ . ') like ?' } @attr ) . ")";
            for my $attr (@attr) {
                push @$bind_values, $search;
            }
        }
    }

    my $project_cond = '';

    my $project = undef;
    $project = $params->{project}
      if ( defined $params->{project} ) && ( $params->{project} ne '' );

    my $project_name = '';
    $project_name = $project->{name}
      if ( defined $project )
      && ( defined $project->{name} )
      && ( $project->{name} ne '' );

    if ( ( $project_name ne '' ) && ( $project_name ne 'all' ) ) {
        $project_cond = '(project=?)';
        push @$bind_values, $project_name;
    }

    #filter by published, default =1, set to 'all' to see all
    my $published_cond = '';
    my $published = $params->{published} || '1';
    if ( ( $published eq '0' ) || ( $published eq '1' ) ) {
        $published_cond = 'published=?';
        push @$bind_values, $published;
    }

    #filter by draft, default =1, set to 'all' to see all
    my $draft_cond = '';
    my $draft = $params->{draft} || '0';
    if ( ( $draft eq '0' ) || ( $draft eq '1' ) ) {
        $draft_cond = 'draft=?';
        push @$bind_values, $draft;
    }

    my $disable_event_sync_cond = '';
    my $disable_event_sync = $params->{disable_event_sync} || '';
    if ( ( $disable_event_sync eq '0' ) || ( $disable_event_sync eq '1' ) ) {
        $disable_event_sync_cond = 'disable_event_sync=?';
        push @$bind_values, $disable_event_sync;
    }

    #print STDERR $disable_event_sync_cond." ".$bind_values->[-1]."\n";

    #combine date, location, series_name, tag, search and project

    push @$where_cond, $location_cond if ( $location_cond =~ /\S/ );
    push @$where_cond, $exclude_location_cond
      if ( $exclude_location_cond =~ /\S/ );
    push @$where_cond, $exclude_project_cond
      if ( $exclude_project_cond =~ /\S/ );
    push @$where_cond, $series_name_cond if ( $series_name_cond =~ /\S/ );
    push @$where_cond, $tag_cond         if ( $tag_cond =~ /\S/ );
    push @$where_cond, $title_cond       if ( $title_cond =~ /\S/ );
    push @$where_cond, $search_cond      if ( $search_cond =~ /\S/ );
    push @$where_cond, $project_cond     if ( $project_cond =~ /\S/ );
    push @$where_cond, $published_cond   if ( $published_cond =~ /\S/ );
    push @$where_cond, $draft_cond       if ( $draft_cond =~ /\S/ );
    push @$where_cond, $disable_event_sync_cond
      if ( $disable_event_sync_cond ne '' );

    #order is forced
    if ( $params->{order} eq 'asc' ) {
        $order_cond = 'order by start';
    } elsif ( $params->{order} eq 'desc' ) {
        $order_cond = 'order by start desc';
    } else {

        #derivate order from archive flag
        if ( $params->{archive} eq 'past' ) {
            $order_cond = 'order by start desc';
        } else {
            $order_cond = 'order by start';
        }
    }

    if ( ( defined $params->{limit} ne '' ) && ( $params->{limit} ne '' ) ) {
        $limit_cond = 'limit ' . $params->{limit};
    }

    my $query = qq{
        select
             date(e.start)        start_date
            ,date(e.end)          end_date
            ,weekday(e.start)     weekday
            ,weekofyear(e.start)  week_of_year
            ,dayofyear(e.start)   day_of_year
            ,e.start_date         day
            ,e.id                 event_id
            ,e.start
            ,e.end
            ,TIMEDIFF(e.end,e.start)      duration
            ,e.program
            ,e.series_name
            ,e.title
            ,e.modified_at
            ,e.created_at
            ,e.modified_by
            ,e.comment_count
            ,e.image
            ,e.image_label
            ,e.series_image
            ,e.series_image_label
            ,e.reference
            ,e.recurrence
            ,e.recurrence_count
            ,e.podcast_url
            ,e.archive_url
            ,e.media_url
            ,e.status
            ,e.location
            ,e.project
            ,e.user_title
            ,e.user_excerpt
            ,e.published
            ,e.draft
            ,e.playout
            ,e.archived
            ,e.rerun
            ,e.live
            ,e.disable_event_sync
            ,e.episode
            ,e.listen_key
            ,e.upload_status
            ,e.content_format
    };
    my $template = $params->{template} || '';

    $query .= ',e.excerpt' unless ( $template =~ /menu/ );

    #    $query.=',e.project'                 unless ($template=~/menu/ || $template=~/list/);

    my $get = $params->{get} || '';
    unless ( $get eq 'no_content' ) {
        unless ( $template =~ /menu/ || $template =~ /list/ ) {
            $query .= ', e.content, e.topic, e.html_content, e.html_topic';
        }
    }

    # add project id and series id
    if ( ( $params->{project_id} ne '' ) || ( $params->{studio_id} ne '' ) ) {
        if ( $params->{project_id} ne '' ) {
            push @$where_cond,  'se.project_id=?';
            push @$bind_values, $params->{project_id};
            $query .= ', se.project_id';
        }
        if ( $params->{studio_id} ne '' ) {
            push @$where_cond,  'se.studio_id=?';
            push @$bind_values, $params->{studio_id};
            $query .= ', se.studio_id';
        }

        #push @$where_cond, 'se.event_id=e.id';
    }

    # add recordings field and conditions
    if ( $params->{active_recording} || $params->{only_active_recording} ) {
        $query .= ', ar.path';
        $query .= ', ar.size';
        $query .= ', ar.created_by uploaded_by';
        $query .= ', ar.modified_at uploaded_at';
    }

    $query .= "\n from";

    # add tables
    if ( ( $params->{project_id} ne '' ) || ( $params->{studio_id} ne '' ) ) {

        # prepent series_events
        $query .= "\n calcms_series_events se inner join calcms_events e on se.event_id=e.id";
    } else {
        $query .= "\n calcms_events e";
    }

    # add recordings table
    if ( $params->{active_recording} || $params->{only_active_recording}) {
        my $type = $params->{only_active_recording} ? 'inner' : 'left';
        $query .= "\n $type join calcms_audio_recordings ar on e.id=ar.event_id and ar.active=1";
    }

    if ( scalar @$where_cond > 0 ) {
        $query .= "\nwhere " . join( ' and ', @$where_cond );
    }

    $query .= "\n" . $order_cond if ( $order_cond ne '' );
    $query .= "\n" . $limit_cond if ( $limit_cond ne '' );

    return ( \$query, $bind_values );
}

sub render($$$$;$) {
    my ($response, $config, $request, $results, $root_params) = @_; 

    my $params = $request->{params}->{checked};
    if ( ref($root_params) eq 'HASH' ) {
        for my $param ( keys %$root_params ) {
            $params->{$param} = $root_params->{$param};
        }
    }
    my %tparams = %$params;
    my $tparams = \%tparams;
    $tparams->{events}       = $results;

    if ( scalar @$results > 0 ) {
        my $result = $results->[0];
        $tparams->{event_id}      = $result->{event_id};
        $tparams->{event_dtstart} = $result->{dtstart};
        $tparams->{first_date}    = $results->[0]->{start_date};
        $tparams->{last_date}     = $results->[-1]->{start_date};
    }

    #    $tparams->{print}            =1 if ($params->{print} eq '1');
    $tparams->{base_url}       = $config->{locations}->{base_url};
    $tparams->{base_domain}    = $config->{locations}->{base_domain};
    $tparams->{local_base_url} = $config->{locations}->{local_base_url};
    $tparams->{widget_render_url} = $config->{locations}->{widget_render_url};
    $tparams->{modified_at}    = time::time_to_datetime( time() );
    if (   ( defined $params->{template} )
        && ( $params->{template} =~ /(\.xml)/ ) )
    {
        $tparams->{modified_at_datetime_utc} =
          time::datetime_to_utc_datetime( $tparams->{modified_at}, $config->{date}->{time_zone} );
    }

    #$tparams->{tags}        = $tags;

    if ( scalar @$results == 0 ) {
        if (   ( $params->{search} ne '' )
            || ( $params->{series_name} ne '' ) )
        {
            $tparams->{no_search_result} = '1';
        } else {
            $tparams->{no_result} = '1';
        }
    } else {
        if ( ( !defined $params->{event_id} ) || ( $params->{event_id} eq '' ) ) {
            $tparams->{event_count}   = scalar @$results . '';
            $tparams->{first_of_list} = $results->[0]->{event_id};
        }
        my $start = $results->[0]->{start_datetime} || '';
        if ( $start =~ /(\d{4}\-\d{2})/ ) {
            $tparams->{month} = $1;
        }
    }

    my $time_diff = time::utc_offset( $config->{date}->{time_zone} );
    $time_diff =~ s/(\d\d)(\d\d)/$1\:$2/g;
    $tparams->{time_zone}  = $config->{date}->{time_zone};
    $tparams->{utc_offset} = $time_diff;

    if ( $params->{template} =~ /\.atom\.xml/ ) {
        $tparams->{modified_at} =~ s/ /T/gi;
        $tparams->{modified_at} .= $time_diff;
    } elsif ( $params->{template} =~ /\.rss\.xml/ ) {
        $tparams->{modified_at} =
          time::datetime_to_rfc822( $tparams->{modified_at} );
    } elsif ( $params->{template} =~ /\.txt/ ) {
        $tparams->{modified_at_utc} =
          time::datetime_to_utc( $tparams->{modified_at}, $config->{date}->{time_zone} );
    }

    my $project = $params->{default_project};
    foreach my $key ( keys %$project ) {
        $tparams->{ 'project_' . $key } = $project->{$key};
    }
    $tparams->{ 'project_' . $project->{name} } = 1
      if ( $project->{name} ne '' );

    $tparams->{controllers}       = $config->{controllers};
    $tparams->{hide_event_images} = 1
      if ( defined $config->{permissions}->{hide_event_images} )
      && ( $config->{permissions}->{hide_event_images} == 1 );

    for my $attr (qw(no_result events_title events_description)){
        $tparams->{$attr} = $config->{$attr};
    }

    template::process( $config, $_[0], $params->{template}, $tparams );

    return $_[0];
}

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

    my $query = qq{
        select id event_id, start, title
        from calcms_events
        where
    (
        ( unix_timestamp(start) <= unix_timestamp(now() ) )
        and
        ( unix_timestamp(end) > unix_timestamp(now() ) )
        and
        ( unix_timestamp(end) - unix_timestamp(now() ) ) < 24*3600
    )

        order by start
        limit 1
    };

    my $running_events = db::get( $dbh, $query );
    my @running_events = @$running_events;

    return $running_events->[0]->{event_id} if ( scalar @running_events > 0 );
    return 0;
}

# add filters to query
sub setDefaultEventConditions ($$$$) {
    my ($config, $conditions, $bind_values, $options) = @_;

    $options = {} unless defined $options;

    # exclude projects
    if (   ( defined $options->{exclude_projects} )
        && ( $options->{exclude_projects} == 1 )
        && ( defined $config->{filter} )
        && ( defined $config->{filter}->{projects_to_exclude} ) )
    {
        my @projects_to_exclude =
          split( /,/, $config->{filter}->{projects_to_exclude} );
        push @$conditions, 'project not in (' . join( ",", map { '?' } @projects_to_exclude ) . ')';
        for my $project (@projects_to_exclude) {
            push @$bind_values, $project;
        }
    }

    # exclude locations
    if (   ( defined $options->{exclude_locations} )
        && ( $options->{exclude_locations} == 1 )
        && ( defined $config->{filter} )
        && ( defined $config->{filter}->{locations_to_exclude} ) )
    {
        my @locations_to_exclude =
          split( /,/, $config->{filter}->{locations_to_exclude} );
        push @$conditions, 'location not in (' . join( ",", map { '?' } @locations_to_exclude ) . ')';
        for my $location (@locations_to_exclude) {
            push @$bind_values, $location;
        }
    }

}

# for local use only or add support for exclude_projects and exclude_locations
sub getEventById ($$$$) {
    my ($dbh, $config, $event_id, $options) = @_;

    $dbh = db::connect($config) unless defined $dbh;

    my $conditions  = [];
    my $bind_values = [];

    push @$conditions,  "id=?";
    push @$bind_values, $event_id;

    setDefaultEventConditions( $config, $conditions, $bind_values, $options );
    $conditions = join( ' and ', @$conditions );

    my $query = qq{
        select  *
        from    calcms_events
        where   $conditions
    };

    my $events = db::get( $dbh, $query, $bind_values );
    return $events;
}

sub get_next_event_of_series ($$$) {
    my ($dbh, $config, $options) = @_;

    my $eventId = $options->{event_id};
    return undef unless defined $eventId;

    $dbh = db::connect($config) unless defined $dbh;

    my $events = getEventById( $dbh, $config, $eventId, $options );
    return undef unless scalar(@$events) == 1;
    my $event = $events->[0];

    my $conditions  = [];
    my $bind_values = [];

    push @$conditions,  "start>?";
    push @$bind_values, $event->{start};

    push @$conditions,  "series_name=?";
    push @$bind_values, $event->{series_name};

    setDefaultEventConditions( $config, $conditions, $bind_values, $options );
    $conditions = join( ' and ', @$conditions );

    my $query = qq{
        select  id 
        from    calcms_events
        where   $conditions
        order by start
        limit 1
    };

    $events = db::get( $dbh, $query, $bind_values );
    return undef unless scalar @$events == 1;

    return $events->[0]->{id};
}

sub get_previous_event_of_series($$$) {
    my ($dbh, $config, $options) = @_;

    my $eventId = $options->{event_id};
    return undef unless defined $eventId;

    $dbh = db::connect($config) unless defined $dbh;
    my $events = getEventById( $dbh, $config, $eventId, $options );
    return undef unless scalar(@$events) == 1;
    my $event = $events->[0];

    my $conditions  = [];
    my $bind_values = [];

    push @$conditions,  "start<?";
    push @$bind_values, $event->{start};

    push @$conditions,  "series_name=?";
    push @$bind_values, $event->{series_name};

    setDefaultEventConditions( $config, $conditions, $bind_values, $options );
    $conditions = join( ' and ', @$conditions );

    my $query = qq{
        select id from calcms_events
        where     $conditions
        order by  start desc
        limit 1
    };
    $events = db::get( $dbh, $query, $bind_values );

    return undef unless scalar(@$events) == 1;
    return $events->[0]->{id};
}

# used by calendar
sub get_by_date_range ($$$$$) {
    my ($dbh, $config, $start_date, $end_date, $options) = @_;

    my $day_starting_hour = $config->{date}->{day_starting_hour};

    my $start = time::datetime_cond( $start_date . 'T00:00:00' );
    $start = time::add_hours_to_datetime( $start, $day_starting_hour );

    my $end = time::datetime_cond( $end_date . 'T00:00:00' );
    $end = time::add_hours_to_datetime( $end, $day_starting_hour );

    my $conditions = [];
    push @$conditions, 'published = 1';
    push @$conditions, 'start between ? and ?';
    my $bind_values = [ $start, $end ];

    setDefaultEventConditions( $config, $conditions, $bind_values, $options );

    $conditions = join( ' and ', @$conditions );

    my $select = qq{distinct date(start) 'start_date'};
    $select = qq{distinct date(DATE_SUB(start, INTERVAL $day_starting_hour HOUR)) 'start_date'}
      if defined $day_starting_hour;

    my $query = qq{
        select   $select
        from     calcms_events 
        where    $conditions
    };

    my $events = db::get( $dbh, $query, $bind_values );

    return $events;
}

sub get_by_image ($$$) {
    my ($dbh, $config, $filename) = @_;

    my $query = qq{
        select * from calcms_events
        where content like ?
        order by start desc
        limit 1
    };
    my $bind_values = [ '%' . $filename . '%' ];

    my $events = db::get( $dbh, $query, $bind_values );

    return undef if scalar @$events == 0;
    return $events->[0];
}

# deleting an event is currently disabled
sub delete ($$$) {
    return;
    my ($request, $config, $event_id) = @_;

    my $params = $request->{params}->{checked};
    my $dbh = db::connect($config);

    my $query = 'delete from calcms_events where id=?';
    db::put( $dbh, $query, [$event_id] );

    $query = 'delete from calcms_categories where id=?';
    db::put( $dbh, $query, [$event_id] );

    $query = 'delete from calcms_tags where id=?';
    db::put( $dbh, $query, [$event_id] );

    $query = 'delete from calcms_series_events where event_id=?';
    db::put( $dbh, $query, [$event_id] );

}

sub get_duration ($$) {
    my ($config, $event) = @_;

    my $timezone = $config->{date}->{time_zone};
    my $start    = time::get_datetime( $event->{start}, $timezone );
    my $end      = time::get_datetime( $event->{end}, $timezone );

    #my $seconds  = $end->subtract($start)->in_units("minutes");
    #return $seconds;
    return undef unless defined $start;
    return undef unless defined $end;
    my $duration = $end->epoch() - $start->epoch();

    #print STDERR "duration=$duration, end=".$end->datetime()." start=".$start->datetime()."\n";
    return $duration / 60;
}

sub check_params ($$) {
    my ($config, $params) = @_;

    #define running at
    my $running_at = $params->{running_at} || '';
    if ( ( defined $running_at ) && ( $running_at ne '' ) ) {
        my $run_date = time::check_date($running_at);
        my $run_time = time::check_time($running_at);
        if ( ( $run_date ne '' ) && ( $run_time ne '' ) ) {
            $params->{till_date} = $run_date;
            $params->{till_time} = $run_time;
            $params->{order}     = 'asc';
            $params->{limit}     = 1;
            $params->{archive}   = 'all';
        }
    }

    #set time
    my $time      = time::check_time( $params->{time} );
    my $from_time = time::check_time( $params->{from_time} );
    my $till_time = time::check_time( $params->{till_time} );

    #set date
    my $date      = '';
    my $from_date = time::check_date( $params->{from_date} );
    my $till_date = time::check_date( $params->{till_date} );
    if ( ( $from_date eq '' ) && ( $till_date eq '' ) ) {
        $date = time::check_date( $params->{date} );
    }

    #set date interval (including)
    my $date_range_include = 0;
    $date_range_include = 1
      if ( defined $params->{date_range_include} )
      && ( $params->{date_range_include} eq '1' );

    my $order = '';
    if ( defined $params->{order} ) {
        $order = 'desc' if ( $params->{order} eq 'desc' );
        $order = 'asc'  if ( $params->{order} eq 'asc' );
    }

    my $weekday = $params->{weekday} || '';

    if ( ( defined $weekday ) && ( $weekday ne '' ) ) {
        if ( $weekday =~ /\d/ ) {
            $weekday = int($weekday);
            log::error( $config, 'invalid weekday' )
              if ( $weekday < 1 || $weekday > 7 );
        } else {
            log::error( $config, 'invalid weekday' );
        }
    }

    my $tag = $params->{tag} || '';
    if ( ( defined $tag ) && ( $tag ne '' ) ) {
        log::error( $config, "invalid tag" ) if ( $tag =~ /\s/ );
        log::error( $config, "invalid tag" ) if ( $tag =~ /\;/ );
        $tag =~ s/\'//gi;
    }

    my $series_name = $params->{series_name} || '';
    if ( ( defined $series_name ) && ( $series_name ne '' ) ) {
        log::error( $config, "invalid series_name" )
          if ( $series_name =~ /\;/ );
        $series_name =~ s/^\s+//gi;
        $series_name =~ s/\s+$//gi;
        $series_name =~ s/\'//gi;
    }

    my $title = $params->{title} || '';
    if ( ( defined $title ) && ( $title ne '' ) ) {
        log::error( $config, "invalid title" ) if ( $title =~ /\;/ );
        $title =~ s/^\s+//gi;
        $title =~ s/\s+$//gi;
        $title =~ s/\'//gi;
    }

    my $location = $params->{location} || '';
    if ( ( defined $location ) && ( $location ne '' ) ) {
        log::error( $config, "invalid location" ) if ( $location =~ /\;/ );
        $location =~ s/^\s+//gi;
        $location =~ s/\s+$//gi;
        $location =~ s/\'//gi;
    }

    #if no location is set, use exclude location filter from default config
    my $locations_to_exclude = '';
    if (   ( $location eq '' )
        && ( defined $config->{filter} )
        && ( defined $config->{filter}->{locations_to_exclude} ) )
    {
        $locations_to_exclude = $config->{filter}->{locations_to_exclude} || '';
        $locations_to_exclude =~ s/\s+/ /g;
    }

    my $projects_to_exclude = '';
    if (   ( defined $config->{filter} )
        && ( defined $config->{filter}->{projects_to_exclude} ) )
    {
        $projects_to_exclude = $config->{filter}->{projects_to_exclude} || '';
        $projects_to_exclude =~ s/\s+/ /g;
    }

    #enable exclude locations filter
    my $exclude_locations = 0;
    $exclude_locations = 1 if ( defined $params->{exclude_locations} ) && ( $params->{exclude_locations} eq '1' );

    my $exclude_projects = 0;
    $exclude_projects = 1 if ( defined $params->{exclude_projects} ) && ( $params->{exclude_projects} eq '1' );

    my $exclude_event_images = 0;
    $exclude_event_images = 1
      if ( defined $params->{exclude_event_images} ) && ( $params->{exclude_event_images} eq '1' );

    #show future events by default
    my $archive = 'future';
    if ( defined $params->{archive} ) {
        $archive = 'all'    if ( $params->{archive} eq 'all' );
        $archive = 'past'   if ( $params->{archive} eq 'gone' );
        $archive = 'future' if ( $params->{archive} eq 'coming' );
    }

    my $last_days = defined $params->{last_days} ? int($params->{last_days}) : 0;

    my $disable_event_sync = '';
    if (   ( defined $params->{disable_event_sync} )
        && ( $params->{disable_event_sync} =~ /([01])/ ) )
    {
        $disable_event_sync = $1;
    }

    #show all on defined timespans
    if ( ( $from_date ne '' ) && ( $till_date ne '' ) ) {
        $archive = 'all';
    }

    my $event_id = $params->{event_id} || '';
    if ( ( defined $event_id ) && ( $event_id ne '' ) ) {
        if ( $event_id =~ /(\d+)/ ) {
            $event_id = $1;
        } else {
            log::error( $config, "invalid event_id" );
        }
    }

    my $get = 'all';
    $get = 'no_content'
      if ( defined $params->{get} ) && ( $params->{get} eq 'no_content' );

    my $search = $params->{search} || '';
    if ( ( defined $search ) && ( $search ne '' ) ) {
        $search = substr( $search, 0, 100 );
        $search =~ s/^\s+//gi;
        $search =~ s/\s+$//gi;
    }

    #print STDERR $params->{template}."\n";
    my $template = '.html';
    if ( ($params->{template}//'') eq 'no' ) {
        $template = 'no';
    } elsif ( ($params->{template}//'') eq 'html' ) {
        $template = 'html';
    } else {
        $template = template::check( $config, $params->{template}, 'event_list.html' );
    }

    my $limit_config = $config->{permissions}->{result_limit} || 100;
    my $limit = $params->{limit} || $limit_config;
    log::error( $config, 'invalid limit!' ) if ( $limit =~ /\D/ );
    $limit = $limit_config if ( $limit_config < $limit );

    #read project from configuration file
    my $project_name = $config->{project} || '';
    log::error( $config, 'no default project configured' )
      if ( $project_name eq '' );

    #get default project
    my $default_project = undef;
    my $projects = project::get( $config, { name => $project_name } );
    log::error( $config, "no configuration found for project '$project_name'" )
      unless ( scalar(@$projects) == 1 );
    $default_project = $projects->[0];

    # get project from parameter (by name)
    my $project = '';
    if (   ( defined $params->{project} )
        && ( $params->{project} =~ /\w+/ )
        && ( $params->{project} ne 'all' ) )
    {
        my $project_name = $params->{project};
        my $projects = project::get( $config, { name => $project_name } );
        log::error( $config, 'invalid project ' . $project_name )
          unless scalar(@$projects) == 1;
        $project = $projects->[0];
    }

    $project_name = $params->{project_name} || '';
    my $studio_name = $params->{studio_name} || '';

    my $project_id = $params->{project_id} || '';
    my $studio_id  = $params->{studio_id}  || '';

    my $json_callback = $params->{json_callback} || '';
    if ( $json_callback ne '' ) {
        $json_callback =~ s/[^a-zA-Z0-9\_]//g;
    }

    # use relative links
    my $extern = 0;
    $extern = 1 if ( defined $params->{extern} ) && ( $params->{extern} eq '1' );

    my $all_recordings = $params->{all_recordings};
    my $active_recording = $params->{active_recording} // '';
    my $only_active_recording = $params->{only_active_recording} // '';
    my $set_no_listen_keys = !($active_recording or $only_active_recording);

    my $checked = {
        date                 => $date,
        time                 => $time,
        from_date            => $from_date,
        till_date            => $till_date,
        date_range_include   => $date_range_include,
        from_time            => $from_time,
        till_time            => $till_time,
        weekday              => $weekday,
        limit                => $limit,
        template             => $template,
        location             => $location,
        series_name          => $series_name,
        tag                  => $tag,
        title                => $title,
        event_id             => $event_id,
        search               => $search,
        archive              => $archive,
        last_days            => $last_days,
        order                => $order,
        project              => $project,
        default_project      => $default_project,
        project_name         => $project_name,
        project_id           => $project_id,
        studio_name          => $studio_name,
        studio_id            => $studio_id,
        json_callback        => $json_callback,
        get                  => $get,
        locations_to_exclude => $locations_to_exclude,
        projects_to_exclude  => $projects_to_exclude,
        exclude_locations    => $exclude_locations,
        exclude_projects     => $exclude_projects,
        exclude_event_images => $exclude_event_images,
        disable_event_sync   => $disable_event_sync,
        extern               => $extern,
        all_recordings       => $all_recordings,
        active_recording     => $active_recording,
        only_active_recordings => $only_active_recording,
        set_no_listen_keys   => $set_no_listen_keys,
        ro                   => ($params->{ro}//'') ? 1 : 0
    };
    return $checked;
}

sub l($){
    my ($word) = @_;
    return length $word ? $word : ();
}

sub get_keys($) {
    my ($event) = @_;

    #my $program                = $event->{program}                || '';
    my $series_name            = $event->{series_name}            || '';
    my $title                  = $event->{title}                  || '';
    my $user_title             = $event->{user_title}             || '';
    my $episode                = $event->{episode}                || '';
    my $recurrence_count_alpha = $event->{recurrence_count_alpha} || '';

    # "<title>: <user-title>"
    my $tkey = join (': ', (l($title), l($user_title)));

    # episode "#123c"
    my $ekey = join '', (
        (length $episode) ? '#'.$episode : '',
        $recurrence_count_alpha
    );

    # "<title> <episode>"
    my $te = join " ", (l($tkey), l($ekey));

    # separation between <series> and <title>
    my $stkey = ( length($series_name) and length($te) ) ? ' - ' : '';
    
    return {
        skey                            => $series_name,
        stkey                           => $stkey,
        tkey                            => $tkey,
        ekey                            => $ekey,
        full_title                      => $series_name . $stkey . $te,
        full_title_no_series            => $te,
    };
}

#do not delete last line!
1;