package OpsviewImportRuntime;

use warnings;
use strict;
use Log::Log4perl qw(:no_extra_logdie_message);
use Getopt::Std;
use DateTime;
use Odw;
use Odw::Performancelabel;
use Odw::Dataload;
use Runtime;
use Opsview;
use Opsview::Config;
use Opsview::Host;
use Opsview::Performanceparsing;
use Opsview::Systempreference;
Opsview::Performanceparsing->init;
use Time::Interval;
use List::Util qw(min max reduce);
use Statistics::Lite qw(count);
use Fcntl;
use Opsview::Schema;
use Runtime::Schema;
use Try::Tiny;

# See TODO below
#use Opsview::ServerInfo::Monitoringservers;
use Data::Dump qw(dump);
use Digest::CRC qw(crc16);

no warnings 'uninitialized';

use PAR qw( /opt/opsview/corelibs/lib/opsview-core-libs.lib );
use OpsviewLicensing { required => 'ov-odw' };

my $opsview_schema;
my $runtime_schema;
my $reloadtimes_rs;
my $systempreferences_rs;

sub usage;

# Use a cache for service objects to save continually hits on db
# Saves about 85% of time
my $cache                     = {};
my $cache_host                = {};
my $cache_perflabels          = {};
my $downtimes                 = {};
my $notifications             = {};
my $import_host_cache         = {};
my $import_servicecheck_cache = {};
my $initial_states_cache;
my $initial_perf_cache;

my $opts = {};

my $rootdir  = "/opt/opsview/coreutils";
my $log4perl = "$rootdir/etc/Log4perl.conf";
my $pidfile  = "$rootdir/var/import_runtime.pid";

my $logger;

my $remove_pidfile = 1;

END {
    $logger->info("Finished") if $logger;
    unlink $pidfile           if $remove_pidfile;
}

# database objects
my $runtimedb;
my $opsviewdb;
my $odwdb;

# database names
my $runtime_dbname;
my $opsview_dbname;

# Multi-master instance information
my $opsview_instance_id;
my $max_object_id;
my $instance_addition;
my $object_id_range_start;
my $object_id_range_end;

# Constants
my $future_datetime = "2037-01-01 00:00:00";

# Prepared statements
my $list_keywords_for_sid_sth;

my $dataload;

my $start_dt;
my $start_timev;
my $end_timev;
my $extra_runs     = 0;
my $first_ever_run = 0;
my $earliest_time;
my $start_datetime;
my $previous_datetime;

my $import_all_odw_hosts;
my $import_all_odw_servicechecks;

my $timeperiods_imported      = 0;
my $service_saved_state_cache = {};

my $deleted_host;
my $deleted_servicecheck;

my @host_data_columns = sort( qw(
      alias
      hostgroup
      monitored_by
      nagios_object_id
      opsview_instance_id
    ),
    map {"hostgroup$_"} 1 .. 9 );
my @sc_data_columns = sort( qw(
      description
      servicegroup
      nagios_object_id
      host
      keywords
    )
);

sub run {

    # We have to set Opsview::Performanceparsing to use the special ODW mode
    # This needs to be done in case modules further up change the behaviour of
    # Opsview::Performanceparsing for their own purposes
    Opsview::Performanceparsing->normalise_for_odw(1);

    $opsview_schema = Opsview::Schema->my_connect;
    $runtime_schema = Runtime::Schema->my_connect;
    $reloadtimes_rs = $opsview_schema->resultset( "Reloadtimes" );
    $systempreferences_rs =
      $opsview_schema->resultset("Systempreferences")->find(1);

    getopts( "hqd:r:i:N:vB:", $opts ) or usage($!);

    usage if ( $opts->{h} );

    Log::Log4perl::init($log4perl);
    $logger = Log::Log4perl->get_logger( "import_runtime" );
    $logger->info( "Starting" );
    $logger->info( "USING PATCHED " . __PACKAGE__ );

    $SIG{__DIE__} = sub {
        if ($^S) {

            # We're in an eval {} and don't want log
            # this message but catch it later
            return;
        }
        local $Log::Log4perl::caller_depth = $Log::Log4perl::caller_depth + 1;
        $logger->fatal(@_) if $logger;
        die @_; # Now terminate really
    };

    # database objects
    $runtimedb = Runtime->db_Main;
    $opsviewdb = Opsview->db_Main;
    $odwdb     = Odw->db_Main;

    $deleted_host         = Odw::Host->construct( { id => 1 } );
    $deleted_servicecheck = Odw::Servicecheck->construct( { id => 1 } );

    # database names
    $runtime_dbname = Opsview::Config->runtime_db;
    $opsview_dbname = Opsview::Config->db;

    # Multi-master instance information
    $opsview_instance_id   = $opts->{N} || Opsview::Config->opsview_instance_id;
    $max_object_id         = 100000000;
    $instance_addition     = ( $max_object_id * ( $opsview_instance_id - 1 ) );
    $object_id_range_start = $instance_addition;
    $object_id_range_end   = $object_id_range_start + $max_object_id - 1;

    # Prepared statements
    $list_keywords_for_sid_sth = $runtimedb->prepare( "
    SELECT keyword
    FROM opsview_viewports
    WHERE object_id = ?
    ORDER BY keyword
    " );

    unless ( $systempreferences_rs->enable_odw_import ) {

        # Quietly exit if not ODW imports not enabled
        unless ( $opts->{q} ) {
            print "ODW importing needs to be enabled in System Preferences\n";
        }
        exit;
    }

    my $upgrade_lock_file = Opsview::Config->upgrade_lock_file;
    if ( -e $upgrade_lock_file ) {
        my $message =
          "Upgrade seems to still be in progress - check why $upgrade_lock_file still exists";
        if ( $opts->{v} ) {
            print $message, $/;
        }
        $logger->logdie($message);
    }

    # Check for lock file
    # If exists, read pid. If pid does not exist, assume finished and run cleanup. If pid does exist, print message and exit
    my $fh;
    my $c = 0;
    unless ( sysopen( $fh, $pidfile, O_WRONLY | O_EXCL | O_CREAT ) ) {
        $logger->error( "Lock file already exists" );
        if ( open $fh, $pidfile ) {
            my $pid = <$fh>;
            $logger->error( "Pid file found with pid $pid" );
            if ( kill 0, $pid ) {
                $logger->error(
                    "Process $pid currently running - exiting quietly"
                );
                $remove_pidfile = 0;
                exit;
            }
            else {
                $logger->error( "Process $pid does not exist - cleaning up" );
                unlink $pidfile;
                system( "/opt/opsview/coreutils/bin/cleanup_import" );
                unless ( sysopen( $fh, $pidfile, O_WRONLY | O_EXCL | O_CREAT ) )
                {
                    $logger->logdie( "Still cannot get lock after cleanup" );
                }
                $logger->info( "Cleanup successful - continuing" );
            }
        }
        else {
            $logger->logdie( "Lock file exists, but cannot read it: $!" );
        }
    }
    print $fh $$;
    close $fh;

    if (
        $odwdb->selectrow_array(
            "SELECT 1 FROM dataloads WHERE opsview_instance_id=? AND status='running'",
            {},
            $opsview_instance_id
        )
      )
    {
        $logger->logdie( "There are running dataloads - exiting" );
    }
    if (
        $odwdb->selectrow_array(
            "SELECT 1 FROM dataloads WHERE opsview_instance_id=? AND status='failed'",
            {},
            $opsview_instance_id
        )
      )
    {
        $logger->logdie( "There are failed dataloads - exiting" );
    }

    if ( $opts->{d} ) {
        $logger->logdie(
            "-d is no longer supported - running import_runtime for the first time will automatically calculate from the earliest possible time"
        );
    }

    # enable transactions and keep track of them to apply a commit every X rows whenever called
    {
        our $batch_max   = $opts->{B} || 5000;
        our $batch_count = 0;

        sub batch_begin {
            $logger->debug( "Transaction start" );
            $odwdb->begin_work;
        }

        sub batch_commit {
            $logger->debug( "Transaction commit" );
            $odwdb->commit;
        }

        sub batch_check {
            $batch_count += ( $_[0] || 1 );

            $logger->trace( "Transaction batch check ($batch_count/$batch_max)"
            );

            if ( $batch_count >= $batch_max ) {
                $logger->debug(
                    "Transaction batch commit (count $batch_count/$batch_max limit)"
                );
                batch_commit();
                batch_begin();
                $batch_count = 0;
            }
        }
    }

    my $iterations = $opts->{i} || 0; # 0 means keep going

    my $full_odw_import = $systempreferences_rs->enable_full_odw_import;

    # Set hour that we are looking at
    $earliest_time = $opts->{r};
    unless ( defined $earliest_time ) {
        my $last_timev =
          Odw::Dataload->maximum_period_end_timev($opsview_instance_id);
        if ($last_timev) {
            $start_dt = DateTime->from_epoch(
                epoch     => $last_timev + 1,
                time_zone => "UTC"
            );
        }
        else {
            $earliest_time = $runtimedb->selectrow_array(
                "SELECT DATE_FORMAT(CONVERT_TZ(MIN(start_time) + INTERVAL 1 HOUR, '+00:00', 'SYSTEM'), '%Y-%m-%d %H') FROM nagios_servicechecks"
            );
            unless ($earliest_time) {
                $logger->logdie( "No data in runtime - is Opsview running yet?"
                );
            }
            $first_ever_run = 1;
        }
    }

    if ($earliest_time) {
        my ( $year, $mon, $day, $hour ) =
          ( $earliest_time =~ /^(\d\d\d\d)-(\d\d)-(\d\d) (\d\d)$/ );
        unless ( defined $hour ) {
            $logger->logdie(
                "Must specify a starting hour of format 'YYYY-MM-DD HH'\n"
            );
        }

        # TODO: Need some processing here to select dates
        # This input is expecting local times, so convert from local to UTC afterwards
        $start_dt = DateTime->new(
            year      => $year,
            month     => $mon,
            day       => $day,
            hour      => $hour,
            minute    => 0,
            second    => 0,
            time_zone => "local",
        );

        # The y/m/d/h fields above are in local time, so we must create the object
        # with TZ set to 'local' and then switch it to UTC after
        $start_dt->set_time_zone( "UTC" );
    }

    # Check for external lock
    if (
        $odwdb->selectrow_array(
            "SELECT value FROM locks WHERE name='import_disabled'")
      )
    {
        print "Import ignored for now\n";
        exit;
    }

    $import_all_odw_hosts = $systempreferences_rs->import_all_odw_hosts;
    $import_all_odw_servicechecks =
      $systempreferences_rs->import_all_odw_servicechecks;

    $logger->info( "import_all_odw_hosts set to $import_all_odw_hosts" );
    $logger->info(
        "import_all_odw_servicechecks set to $import_all_odw_servicechecks"
    );

    if ( !$import_all_odw_hosts ) {

        # Not set to import all hosts data so set up cache of
        # what should be imported
        my $sth =
          $opsviewdb->prepare( "SELECT name FROM hosts WHERE import_to_odw = 1"
          );
        $sth->execute;
        while ( my ($host_name) = $sth->fetchrow_array ) {
            $import_host_cache->{$host_name} = 1;
            $logger->debug( 'Host set for ODW import: ' . $host_name );
        }
    }

    if ( !$import_all_odw_servicechecks ) {

        # Not set to import all servicecheck data so set up cache of
        # what should be imported
        my $sth = $opsviewdb->prepare(
            "SELECT name FROM servicechecks WHERE import_to_odw = 1"
        );
        $sth->execute;
        while ( my ($servicecheck_name) = $sth->fetchrow_array ) {
            $import_servicecheck_cache->{$servicecheck_name} = 1;
            $logger->debug(
                'Service set for ODW import: ' . $servicecheck_name );
        }
    }

    $logger->info( "Building global cache" );
    build_cache( $start_dt->clone(), $iterations );
    $logger->info( "Cache created" );

    # Main loop
    MAIN_LOOP:
    while (1) {

        my $end_dt      = get_hour_end( $start_dt->clone );
        my $previous_dt = $start_dt->clone->subtract( seconds => 1 );
        my $next_dt     = $end_dt->clone->add( seconds => 1 );
        $start_datetime = $start_dt->strftime( "%F %T" );
        my $start_datetime_local = $start_dt->clone->set_time_zone( "local" );
        $start_datetime_local = $start_datetime_local->strftime( "%F %T" );
        $previous_datetime    = $previous_dt->strftime( "%F %T" );
        my $next_datetime = $next_dt->strftime( "%F %T" );
        my $end_datetime  = $end_dt->strftime( "%F %T" );
        $end_timev   = $end_dt->epoch;
        $start_timev = $start_dt->epoch;

        my $runtime_update_timev;

        my $retry_timeout = 60; # number of seconds to keep trying this check
        while (
            !(
                $runtime_update_timev = $runtimedb->selectrow_array(
                    "SELECT UNIX_TIMESTAMP(MAX(status_update_time)) FROM nagios_servicestatus"
                )
            )
          )
        {

            $logger->logwarn(
                'Waiting on status_update_time to be populated in nagios_servicestatus (timeout in ',
                $retry_timeout, ' seconds)', $/
            );

            # This value could be NULL sometimes, usually if caught during a reload
            sleep 10;
            $retry_timeout -= 10;
            if ( $retry_timeout <= 0 ) {
                $logger->logwarn(
                    'Problem trying to access status_update_time in nagios_servicestatus; will retry later',
                    $/
                );
                last MAIN_LOOP;
            }
        }

        # TODO: Do we need this functionality anymore?
        if ( 0 && Opsview::Config->detect_slave_status_on_import ) {
            my $srvinfo_monitoringservers =
              Opsview::ServerInfo::Monitoringservers->new(
                schema         => $opsview_schema,
                runtime_schema => $runtime_schema,
                user           => '',
              );
            for my $server ( @{ $srvinfo_monitoringservers->status_data() } ) {
                next unless $server->{activated};
                for my $node ( @{ $server->{nodes} } ) {
                    unless ( $node->{status} == 0 ) {
                        $logger->logwarn(
                            "Node $node->{name} not OK; will retry later", $/ );
                        last MAIN_LOOP;
                    }
                    if ( $node->{state_duration} < 300 ) { # 5 minutes
                        $logger->logwarn(
                            "Node $node->{name} is up for less then 5 minutes; will retry later",
                            $/
                        );
                        last MAIN_LOOP;
                    }
                }
            }
        }

        if ( $runtime_update_timev < $end_timev ) {
            if ($extra_runs) {

                # Normal exit
                last MAIN_LOOP;
            }
            print "Last update to DB is "
              . DateTime->from_epoch(
                epoch     => $runtime_update_timev,
                time_zone => "local"
            )->strftime("%F %T")
              . ". Cannot run until after "
              . $end_dt->set_time_zone("local")->strftime("%F %T"), $/;
            exit 1;
        }
        if ( $runtime_update_timev - $end_timev < 60 ) {
            if ($extra_runs) {

                # Normal exit
                last MAIN_LOOP;
            }
            else {

                # Need to allow time for NDO to update rows in nagios_servicechecks
                print "Cannot run within 60 seconds of end time", $/;
                exit 1;
            }
        }

        if ( !$opts->{q} ) {
            print "Running for $start_datetime_local";
            if ( $opts->{v} ) {
                print ' - started at ', scalar(localtime);
            }
            print $/ ;
        }

        $logger->info( "Importing for $start_datetime_local" );

        my $num_hosts          = {};
        my $num_services       = {};
        my $num_serviceresults = 0;
        my $num_perfdata       = 0;

        my $load_start_timev = time();

        my $dataload = Odw::Dataload->insert(
            {
                opsview_instance_id => $opsview_instance_id,
                period_start_timev  => $start_timev,
                period_end_timev    => $end_timev,
                load_start_timev    => $load_start_timev,
                status              => "running",
            }
        );
        $logger->logdie("Error creating dataload") unless $dataload;

        import_timeperiods();

        build_saved_state_cache($start_timev);

        my $sth;

        my @values;
        my $do_large_perfdata_insert = sub {
            my $force = shift;
            if ( ( scalar @values >= 1000 ) || ( $force && @values ) ) {

                my $error;
                my $count = 1;
                do {
                    $error = 0;
                    try {
                        $odwdb->do(
                            "INSERT INTO performance_data (datetime, performance_label, value) VALUES "
                              . join( ",", @values )
                        );
                    }
                    catch {
                        if ( $_ =~ m/Lock wait timeout exceeded/ ) {
                            $logger->warn(
                                "Attempt $count: Insert into performance_data failed due to lock wait timeout"
                            );
                            $error++;
                        }
                        else {
                            $logger->error_die(
                                "Insert into performance_data failed: $_"
                            );
                        }
                    };

                } until $count++ > 2 || $error == 0;

                $logger->error_die(
                    "Insert into odw.performance_data failed: 3 attempts failed with 'Lock wait timeout exceeded'"
                ) if $error;

                batch_check( scalar @values );
                @values = ();
            }
        };

        my @sc_results;
        my $do_large_sc_insert = sub {
            my $force = shift;

            # Choose 50 because don't want to have more than 1 megabyte of data in one query
            if (   ( ( my $vals = scalar @sc_results / 8 ) >= 50 )
                || ( $force && @sc_results ) )
            {
                $odwdb->do(
                    "INSERT INTO servicecheck_results
                (start_datetime, start_datetime_usec, servicecheck, check_type, status, status_type, duration, output)
                VALUES " . ( join ',', ("(?,?,?,?,?,?,?,?)") x $vals ), {},
                    @sc_results
                );
                batch_check( scalar @sc_results );
                @sc_results = ();
            }
        };

        # Read in servicechecks and grab performance data
        my $perfdata_cache           = {};
        my $last_odw_servicecheck_id = 0;
        $logger->info( "Importing all results and performance data" );
        $sth = $runtimedb->prepare( "
    SELECT service_object_id, state, state_type, perfdata, output, start_time, start_time_usec, check_type, execution_time
    FROM nagios_servicechecks
    WHERE start_time BETWEEN '$start_datetime' AND '$end_datetime'
    ORDER BY service_object_id, start_time
    " );
        $sth->execute;
        batch_begin();
        while (
            my ( $sid, $state, $state_type, $perfdata, $output,
                $service_start_time, $service_start_time_usec, $check_type,
                $duration )
            = $sth->fetchrow_array
          )
        {

            $_ = get_odw_objects($sid);
            my $odw_host         = $_->{odw_host_data};
            my $odw_servicecheck = $_->{odw_service_data};

            #use Data::Dump qw(dump);
            #warn dump $import_host_cache;
            #warn dump $import_servicecheck_cache;

            # Check to see if we are only importing specific hosts
            next if block_host_import( $odw_host->{name} );
            next if block_servicecheck_import( $odw_servicecheck->{name} );

            $num_hosts->{ $odw_host->{id} }            = 1;
            $num_services->{ $odw_servicecheck->{id} } = 1;

            # For every new service object, reset cache
            if ( $last_odw_servicecheck_id != $odw_servicecheck->{id} ) {
                if ($last_odw_servicecheck_id) {
                    insert_summarised_perfdata( $start_datetime,
                        $perfdata_cache );
                }
                $perfdata_cache           = {};
                $last_odw_servicecheck_id = $odw_servicecheck->{id};
            }

            if ($full_odw_import) {
                push @sc_results, $service_start_time,
                  $service_start_time_usec, $odw_servicecheck->{id},
                  convert_check_type_to_text($check_type),
                  convert_state_to_text($state),
                  convert_state_type_to_text($state_type), $duration, $output;
            }

            #
            # Parse perfdata using Opsview::Performanceparsing
            # which is a wrapper to Nagios::Plugin::Performance and nagiosgraph's map file logic
            #

            my $_perfdata = $perfdata;
            my $perfs     = Opsview::Performanceparsing->parseperfdata(
                servicename => $odw_servicecheck->{name},
                output      => $output,
                perfdata    => $_perfdata,
            );
            foreach my $p (@$perfs) {
                if ( !defined $p->value ) {
                    $logger->warn(
                        'No value in perfdata for check "',
                        $odw_servicecheck->{name},
                        '" on host "',
                        $odw_host->{name},
                        '" (',
                        $perfdata || 'using map.local',
                        ')'
                    );
                    next;
                }
                next if ( $p->value eq "U" ); # Ignore unknown values

                if ( !$odw_host->{id} ) {
                    $logger->warn( "No ODW host data for sid $sid" );
                    next;
                }
                my $odw_perflabel_id =
                  get_odw_perf_label( $odw_host->{id}, $odw_servicecheck->{id},
                    $p->label, $p->uom );

                push @{ $perfdata_cache->{$odw_perflabel_id} }, $p->value;

                if ($full_odw_import) {
                    push @values, "('$service_start_time',$odw_perflabel_id,"
                      . $p->value . ")";
                }

                $num_perfdata++;
            }
            $do_large_sc_insert->();
            $do_large_perfdata_insert->();
            $num_serviceresults++;
        }
        $do_large_sc_insert->(1);
        $do_large_perfdata_insert->(1);
        insert_summarised_perfdata( $start_datetime, $perfdata_cache );
        batch_commit();

        # Only required on a first ever run
        # For each activated servicecheck, set an initial state_history of the last state
        # seen in nagios_statehistory/nagios_servicechecks at import start time.
        # The prior state is the same, with a time of that point
        if ($first_ever_run) {
            $logger->info( "Calculating initial states" );
            $sth = $runtimedb->prepare( "
        SELECT object_id
        FROM nagios_objects
        WHERE is_active = 1
        AND objecttype_id = 2
        " );
            $sth->execute;
            batch_begin();
            while ( my $object_id = $sth->fetchrow_array ) {

                my ( $initial_status_datetime, $initial_status,
                    $initial_status_type )
                  = find_initial_service_state( $object_id, $start_datetime );
                unless ( defined $initial_status ) {

                    # There is no data for this service at start_datetime so can't have been running at start_time
                    # This could happen with a service that starts in the middle of an hour
                    next;
                }
                $_ = get_odw_objects($object_id);
                my $odw_servicecheck = $_->{odw_service_data};

                # Check to see if we are only importing specific hosts
                next if block_servicecheck_import( $odw_servicecheck->{name} );

                $odwdb->do(
                    "INSERT INTO state_history
                (datetime, datetime_usec,
                servicecheck,
                status, status_type,
                prior_status_datetime, prior_status, output)
                VALUES
                (?, ?, ?, ?, ?, ?, ?, ?)",
                    {},
                    $start_datetime, 0,
                    $odw_servicecheck->{id},
                    convert_state_to_text($initial_status),
                    convert_state_type_to_text($initial_status_type),
                    $initial_status_datetime,
                    convert_state_to_text($initial_status),
                    "First state on initial import",
                );

                # Assume all failed things are acknowledged on first start
                my $is_acknowledged = $initial_status == 0 ? 0 : 1;

                # skip deleted
                unless ( $odw_servicecheck->{id} == 1 ) {
                    $odwdb->do(
                        "INSERT INTO service_saved_state
                    (start_timev, hostname, servicename, nagios_object_id, last_state, last_hard_state, acknowledged, scheduled_downtime_depth, host_state_num, opsview_instance_id)
                    VALUES
                    (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
                        {},
                        $start_timev,
                        $odw_servicecheck->{hostname},
                        $odw_servicecheck->{name},
                        $object_id,
                        convert_state_to_text($initial_status),
                        convert_state_to_text($initial_status)
                        ,  # TODO: Not always true, but close enough
                        $is_acknowledged,
                        0, # Assume not in downtime
                        0, # Assume host is UP
                        $opsview_instance_id,
                    );
                }
            }
            batch_commit();
        }

        # Save downtime start data into ODW
        # Separate hosts and services tables
        # Only save downtimes that have an actual_start_time beginning in this hour
        # NOTE: internal_downtime_id from 6.4 will be NULL and we should use downtimehistory_id
        # instead
        $logger->info( "Importing downtime starts" );
        $sth = $runtimedb->prepare( "
    SELECT downtime_type, object_id, entry_time, author_name, comment_data, scheduled_start_time,
     scheduled_end_time, was_started, actual_start_time, actual_end_time, was_cancelled,
     IF(ISNULL(internal_downtime_id), downtimehistory_id, internal_downtime_id) AS internal_downtime_id
    FROM nagios_downtimehistory
    WHERE actual_start_time BETWEEN '$start_datetime' AND '$end_datetime'
    ORDER BY object_id
    " );
        $sth->execute;
        batch_begin();
        while ( my $row = $sth->fetchrow_hashref ) {
            my ( $odw_object_id, $tablename );

            # For services
            if ( $row->{downtime_type} == 1 ) {
                $tablename = "downtime_service_history";

                # Even though $odw_object_id is not used, let this go
                # because will confirm object has been created
                $_             = get_odw_objects( $row->{object_id} );
                $odw_object_id = $_->{odw_service_data};
                next if block_servicecheck_import( $odw_object_id->{name} );
            }
            else {
                $tablename     = "downtime_host_history";
                $odw_object_id = get_odw_host_data( $row->{object_id} );
                next if block_host_import( $odw_object_id->{name} );
            }

            # If the object is not found in ODW, we ignore this downtime. This is possible if
            # an object is created and renamed before import_runtime runs
            next if ( $odw_object_id->{id} == 1 );

            my $actual_end_datetime;

            # If the end time has not been set, we set it to a date in the future
            # This will be reduced down to the actual_end_datetime when downtime finishes
            if ( !defined $row->{actual_end_time} ) {

                # Can't be higher because of perl's intepretation of date
                $actual_end_datetime = $future_datetime;
            }
            else {
                $actual_end_datetime = $row->{actual_end_time};
            }
            my $nagios_object_id_multi_master =
              $row->{object_id} + $instance_addition;

            # We check to see if there already is a downtime of this value set
            my $found = $odwdb->selectrow_array(
                "SELECT COUNT(*) FROM $tablename
            WHERE actual_start_datetime=? AND nagios_object_id = ?",
                {},
                $row->{actual_start_time},
                $nagios_object_id_multi_master,
            );
            if ( !$found ) {

                # NOTE: is_fixed is always 1 in Runtime. We haven't changed ODW's schema to reflect
                $odwdb->do(
                    "INSERT IGNORE INTO $tablename
                (actual_start_datetime, actual_end_datetime, nagios_object_id,
                author_name, comment_data, entry_datetime,
                scheduled_start_datetime, scheduled_end_datetime,
                is_fixed, duration, was_cancelled, nagios_internal_downtime_id)
                VALUES
                (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
                    {},
                    $row->{actual_start_time}, $actual_end_datetime,
                    $nagios_object_id_multi_master,
                    $row->{author_name}, $row->{comment_data},
                    $row->{entry_time},
                    $row->{scheduled_start_time}, $row->{scheduled_end_time},
                    1, 0, $row->{was_cancelled},
                    $row->{internal_downtime_id},
                );
            }
        }
        batch_commit();

        # Save downtime end data
        # Can ignore actual_start_times before $start_datetime because will be caught by above
        # Some rows will be copied again, eg, if import_runtime is run over two hours where start
        # is in one hour and end in another. This is not a problem
        $logger->info( "Importing downtime ends" );
        $sth = $runtimedb->prepare( "
    SELECT actual_start_time, actual_end_time, downtime_type, object_id, was_cancelled, IF(ISNULL(internal_downtime_id), downtimehistory_id, internal_downtime_id) AS internal_downtime_id
    FROM nagios_downtimehistory
    WHERE actual_end_time BETWEEN '$start_datetime' AND '$end_datetime' AND actual_start_time < '$start_datetime'
    ORDER BY object_id
    " );
        $sth->execute;
        batch_begin();
        while ( my $row = $sth->fetchrow_hashref ) {
            my ( $odw_object_id, $tablename );

            if ( $row->{downtime_type} == 1 ) {
                $tablename     = "downtime_service_history";
                $_             = get_odw_objects( $row->{object_id} );
                $odw_object_id = $_->{odw_service_data};
                next if block_servicecheck_import( $odw_object_id->{name} );
            }
            else {
                $tablename     = "downtime_host_history";
                $odw_object_id = get_odw_host_data( $row->{object_id} );
                next if block_host_import( $odw_object_id->{name} );
            }

            my $nagios_object_id_multi_master =
              $row->{object_id} + $instance_addition;

            # As nagios_internal_downtime_id could be duplicated across multi-masters, also use the (multi-master) nagios_object_id
            # Also limit based on the actual_start_time because have seen instances where NDO puts a nagios_internal_downtime_id as 1
            # multiple times. This means that some downtime is incorrectly set
            $odwdb->do(
                "UPDATE $tablename
            SET actual_end_datetime=?, was_cancelled=?
            WHERE nagios_internal_downtime_id = ?
            AND actual_start_datetime = ?
            AND nagios_object_id = ?",
                {},
                $row->{actual_end_time},
                $row->{was_cancelled},
                $row->{internal_downtime_id},
                $row->{actual_start_time},
                $nagios_object_id_multi_master,
            );
        }
        batch_commit();

        # Fix possible issue where actual_downtime_end is not set correctly
        $logger->info( "Checking for incorrect downtimes" );
        batch_begin();
        fix_nonending_downtimes( $end_datetime, "downtime_service_history" );
        fix_nonending_downtimes( $end_datetime, "downtime_host_history" );
        batch_commit();

        # Get a hash of all downtimes that could be relevant for this period
        # $downtimes is a hash, indexed by the (multi-master) nagios_object_id. Need to use this, and not the odw ids because
        # the host/service configuration could change over the duration of the downtime, but the nagios_object_id will remain constant
        $logger->info( "Calculating relevant downtimes" );
        $downtimes = {};

        # We have to order this by actual_start_datetime so that we can check for overlapping downtimes
        $sth = $odwdb->prepare( "
    SELECT nagios_object_id, UNIX_TIMESTAMP(actual_start_datetime), UNIX_TIMESTAMP(actual_end_datetime)
    FROM downtime_service_history
    WHERE
    (
     (actual_start_datetime <= '$start_datetime' AND actual_end_datetime >= '$end_datetime')
     OR
     (actual_start_datetime <= '$start_datetime' AND actual_end_datetime BETWEEN '$start_datetime' AND '$end_datetime')
     OR
     (actual_start_datetime BETWEEN '$start_datetime' AND '$end_datetime' AND actual_end_datetime BETWEEN '$start_datetime' AND '$end_datetime')
     OR
     (actual_start_datetime BETWEEN '$start_datetime' AND '$end_datetime' AND actual_end_datetime >= '$end_datetime')
    )
    ORDER BY actual_start_datetime
    " );
        $sth->execute;
        while (
            my ( $nagios_object_id, $actual_start_timev, $actual_end_timev ) =
            $sth->fetchrow_array )
        {

            # It seems that Nagios 4 could put a downtime entry in and then delete it straight away
            # As this is useless, we don't bother importing this into ODW
            if ( $actual_start_timev == $actual_end_timev ) {
                next;
            }

            push @{ $downtimes->{$nagios_object_id} },
              {
                actual_start_timev => $actual_start_timev,
                actual_end_timev   => $actual_end_timev
              };
        }

        $sth = $odwdb->prepare( "
    SELECT nagios_object_id, UNIX_TIMESTAMP(actual_start_datetime), UNIX_TIMESTAMP(actual_end_datetime)
    FROM downtime_host_history
    WHERE
    (
     (actual_start_datetime <= '$start_datetime' AND actual_end_datetime >= '$end_datetime')
     OR
     (actual_start_datetime <= '$start_datetime' AND actual_end_datetime BETWEEN '$start_datetime' AND '$end_datetime')
     OR
     (actual_start_datetime BETWEEN '$start_datetime' AND '$end_datetime' AND actual_end_datetime BETWEEN '$start_datetime' AND '$end_datetime')
     OR
     (actual_start_datetime BETWEEN '$start_datetime' AND '$end_datetime' AND actual_end_datetime >= '$end_datetime')
    )
    " );
        $sth->execute;
        while (
            my ( $nagios_object_id, $actual_start_timev, $actual_end_timev ) =
            $sth->fetchrow_array )
        {

            # It seems that Nagios 4 could put a downtime entry in and then delete it straight away
            # As this is useless, we don't bother importing this into ODW
            if ( $actual_start_timev == $actual_end_timev ) {
                next;
            }

            push @{ $downtimes->{$nagios_object_id} },
              {
                actual_start_timev => $actual_start_timev,
                actual_end_timev   => $actual_end_timev
              };
        }

        # Save notification data
        # Seperate hosts and services tables
        # Also saved into a hash for later use if required
        $logger->info( "Importing notifications" );
        $notifications = {};
        $sth           = $runtimedb->prepare( "
    SELECT
        nn.start_time as time,
        UNIX_TIMESTAMP(nn.start_time) AS timev,
        notification_type,
        nn.object_id,
        nn.state as state,
        nn.output as output,
        notification_reason,
        notification_number,
        nc.alias as contact_name,
        no.name1 as command_name
    FROM
        nagios_notifications nn,
        nagios_contactnotifications ncn,
        nagios_contacts nc,
        nagios_contactnotificationmethods ncnm,
        nagios_objects no
    WHERE nn.notification_id = ncn.notification_id
    AND ncn.contact_object_id = nc.contact_object_id
    AND ncn.contactnotification_id = ncnm.contactnotification_id
    AND ncnm.command_object_id = no.object_id
    AND nn.start_time BETWEEN '$start_datetime' AND '$end_datetime'
    ORDER BY nn.notification_id
    " );
        $sth->execute;
        batch_begin();
        while ( my $row = $sth->fetchrow_hashref ) {
            my ( $odw_object_id, $tablename, $columnname, $status );

            if ( $row->{notification_type} == 1 ) {
                $tablename     = "notification_service_history";
                $columnname    = "service";
                $_             = get_odw_objects( $row->{object_id} );
                $odw_object_id = $_->{odw_service_data};
                next if block_servicecheck_import( $odw_object_id->{name} );
                $status = convert_state_to_text( $row->{state} );
            }
            else {
                $tablename     = "notification_host_history";
                $columnname    = "host";
                $odw_object_id = get_odw_host_data( $row->{object_id} );
                next if block_host_import( $odw_object_id->{name} );
                $status = convert_host_state_to_text( $row->{state} );
            }

            push @{ $notifications->{$columnname}->{ $odw_object_id->{id} } },
              { entry_timev => $row->{entry_timev} };

            my $methodname = $row->{command_name};
            $methodname =~ s/^((service|host)-)?notify-by-//;

            my $reason =
              convert_notification_reason_to_text( $row->{notification_reason}
              );

            $odwdb->do(
                "INSERT INTO $tablename
            (entry_datetime, $columnname, status, output,
            notification_reason, notification_number,
            contactname, methodname)
            VALUES
            (?, ?, ?, ?, ?, ?, ?, ?)",
                {},
                $row->{time},
                $odw_object_id->{id},
                $status,
                $row->{output},
                $reason,
                $row->{notification_number},
                $row->{contact_name},
                $methodname,
            );
        }
        batch_commit();

        # Save acknowledgement data
        # Separate hosts and services tables
        $logger->info( "Importing acknowledgements" );
        $sth = $runtimedb->prepare( "
    SELECT entry_time, UNIX_TIMESTAMP(entry_time) AS entry_timev,
    acknowledgement_type, object_id, author_name, comment_data, is_sticky, notify_contacts
    FROM nagios_acknowledgements
    WHERE entry_time BETWEEN '$start_datetime' AND '$end_datetime'
    ORDER BY object_id
    " );
        $sth->execute;
        batch_begin();
        while ( my $row = $sth->fetchrow_hashref ) {
            my ( $odw_object_id, $tablename, $columnname );

            if ( $row->{acknowledgement_type} == 1 ) {
                $tablename     = "acknowledgement_service";
                $columnname    = "service";
                $_             = get_odw_objects( $row->{object_id} );
                $odw_object_id = $_->{odw_service_data};
                next if block_servicecheck_import( $odw_object_id->{name} );
            }
            else {
                $tablename     = "acknowledgement_host";
                $columnname    = "host";
                $odw_object_id = get_odw_host_data( $row->{object_id} );
                next if block_host_import( $odw_object_id->{name} );
            }

            $odwdb->do(
                "INSERT INTO $tablename
            (entry_datetime, $columnname,
            author_name, comment_data,
            is_sticky, persistent_comment, notify_contacts)
            VALUES
            (?, ?, ?, ?, ?, ?, ?)",
                {},
                $row->{entry_time},
                $odw_object_id->{id},
                $row->{author_name},
                $row->{comment_data},
                $row->{is_sticky},

                # NOTE: persistent_comment is always set in the Opsview UI as 0
                # and is no longer in runtime DB so we always set this to 0
                0,
                $row->{notify_contacts},
            );
        }
        batch_commit();

        # Save state_history - only for services
        # Add two extra columns to store the prior states. This helps speed up queries for availability
        # If a prior state is not found, then assumes OK - this would happen with new services
        $logger->info( "Importing state history staging table" );
        my (
            $prior_status_datetime, $prior_status,
            $last_object_id,        $odw_servicecheck
        );

        # We use a staging table here because it saves having to search the potentially very large
        # state_history table later on when calculating availability stats
        # Also, we do a mass insert at once later, which should make this quicker too
        # This must be a temporary table so that it doesn't clash on a sharedodw system
        # Also, temporary tables cannot be innodb because of the foreign key constraint - this will be picked up
        # in the mass insert later, but it shouldn't happen
        $odwdb->do( "DROP TABLE IF EXISTS state_history_staging_table" );
        $odwdb->do(
            qq{
        CREATE TEMPORARY TABLE state_history_staging_table (
            datetime DATETIME NOT NULL,
            datetime_usec int NOT NULL,
            servicecheck int NOT NULL,
            status ENUM("OK", "WARNING", "CRITICAL", "UNKNOWN") NOT NULL,
            status_type ENUM("SOFT","HARD") NOT NULL,
            prior_status_datetime DATETIME NOT NULL,        # This column is redundant - remove in future
            prior_status ENUM("OK", "WARNING", "CRITICAL", "UNKNOWN", "INDETERMINATE") NOT NULL,
            problem_has_been_acknowledged BOOLEAN NOT NULL DEFAULT 0,
            scheduled_downtime_depth SMALLINT NOT NULL DEFAULT 0,
            host_state SMALLINT NOT NULL DEFAULT 0,
            eventtype SMALLINT NOT NULL DEFAULT 0,
            output MEDIUMTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
            INDEX (servicecheck, datetime, datetime_usec)
        ) ENGINE=InnoDB
        }
        );

        #my $prior_status_cache_by_scid = get_prior_status_cache_by_scid();

        # Must order by object_id, state_time for the last_object_id bit to work
        # We also find hosts as well, as they are required for calculation of the seconds_unhandled of the service
        $sth = $runtimedb->prepare( "
    SELECT sh.object_id as object_id, state, state_type, state_time, state_time_usec,
     scheduled_downtime_depth,
     problem_has_been_acknowledged,
     host_state,
     eventtype,
     output
    FROM nagios_statehistory sh, nagios_objects o
    WHERE sh.object_id = o.object_id
    AND o.objecttype_id IN (2)
    AND state_time BETWEEN '$start_datetime' AND '$end_datetime'
    ORDER BY object_id, state_time
    " );

        $sth->execute;
        batch_begin();
        while (
            my (
                $object_id,                     $state,
                $state_type,                    $service_start_time,
                $service_start_time_usec,       $in_downtime,
                $problem_has_been_acknowledged, $host_state_num,
                $eventtype,                     $output
            )
            = $sth->fetchrow_array
          )
        {
            if ( !defined $last_object_id || $last_object_id != $object_id ) {
                $_                = get_odw_objects($object_id);
                $odw_servicecheck = $_->{odw_service_data};
                next if block_servicecheck_import( $odw_servicecheck->{name} );

                #if ( $_ =
                #    $prior_status_cache_by_scid->{ $odw_servicecheck->{id} } )
                #{
                #    ( $prior_status_datetime, $prior_status ) = @{$_};
                #}

                ( $prior_status_datetime, $prior_status ) =
                  $odwdb->selectrow_array( "
                SELECT datetime,status FROM state_history
                WHERE datetime <= '$start_datetime' AND servicecheck="
                      . $odw_servicecheck->{id} . "
                ORDER BY datetime DESC LIMIT 1
            " );

                # This service could have changed its primary key due to configuration changes
                unless ($prior_status) {
                    my $hostname    = $odw_servicecheck->{hostname};
                    my $servicename = $odw_servicecheck->{name};

                    my $count = $odwdb->selectrow_array(
                        "SELECT COUNT(*) FROM servicechecks WHERE hostname = ? AND name = ? AND id < ?",
                        {},
                        $hostname,
                        $servicename,
                        $odw_servicecheck->{id}
                    );
                    if ( $count > 0 ) {
                        ( $prior_status_datetime, $prior_status ) =
                          $odwdb->selectrow_array( "
    SELECT datetime, status
    FROM state_history, servicechecks
    WHERE state_history.servicecheck=servicechecks.id
     AND servicechecks.hostname=?
     AND servicechecks.name=?
     AND servicechecks.id < ?
     AND state_history.datetime < ?
    ORDER BY datetime DESC
    LIMIT 1
    ", {}, $hostname, $servicename, $odw_servicecheck->{id},
                            $service_start_time );
                    }
                }

                # This is a new service that has just appeared
                # Set prior state to current data
                unless ($prior_status) {
                    $prior_status          = convert_state_to_text($state);
                    $prior_status_datetime = $service_start_time;
                }
            }

            my $current_state      = convert_state_to_text($state);
            my $current_state_type = convert_state_type_to_text($state_type);

            $odwdb->do(
                "INSERT INTO state_history_staging_table
            (datetime, datetime_usec, servicecheck,
            status, status_type,
            problem_has_been_acknowledged, scheduled_downtime_depth, host_state,
            prior_status_datetime, prior_status,
            eventtype,
            output)
            VALUES
            (?, ?, ?,
             ?, ?,
             ?, ?, ?,
             ?, ?,
             ?,
             ?)",
                {},
                $service_start_time, $service_start_time_usec,
                $odw_servicecheck->{id},
                $current_state,                 $current_state_type,
                $problem_has_been_acknowledged, $in_downtime, $host_state_num,
                $prior_status_datetime,         $prior_status,
                $eventtype,
                $output,
            );

            $prior_status          = $current_state; # Save timings for next run
            $prior_status_datetime = $service_start_time;
            $last_object_id        = $object_id;
        }
        batch_commit();

        # For each servicecheck, find the state at the top of the hour
        # by backtracking through state_history table.
        # Then progressively see what state changes have occurred, keeping a running total of
        # seconds in each state
        # Only uses state_history to gather information
        # I wonder if this is easier to do with DateTime::Sets? Could create sets of time intervals
        # and work out intersections and unions to calculate timings of things.
        $logger->info( "Calculating hourly availability" );
        $sth = $runtimedb->prepare( "
    SELECT object_id
    FROM nagios_objects
    WHERE is_active = 1
    AND objecttype_id = 2
    " );
        $sth->execute;
        batch_begin();
        while ( my $object_id = $sth->fetchrow_array ) {

            $_ = get_odw_objects($object_id);
            my $odw_servicecheck    = $_->{odw_service_data};
            my $odw_host            = $_->{odw_host_data};
            my $odw_host_id         = $odw_host->{id};
            my $odw_servicecheck_id = $odw_servicecheck->{id};

            next if block_host_import( $odw_host->{name} );
            next if block_servicecheck_import( $odw_servicecheck->{name} );

            my %state_seconds = (
                INDETERMINATE => 0,
                OK            => 0,
                WARNING       => 0,
                CRITICAL      => 0,
                UNKNOWN       => 0
            );
            my %state_hard_seconds = (
                INDETERMINATE => 0,
                OK            => 0,
                WARNING       => 0,
                CRITICAL      => 0,
                UNKNOWN       => 0
            );
            my $seconds_not_ok_scheduled    = 0;
            my $seconds_warning_scheduled   = 0;
            my $seconds_critical_scheduled  = 0;
            my $seconds_unknown_scheduled   = 0;
            my $seconds_unacknowledged      = 0;
            my $seconds_unacknowledged_hard = 0;
            my $seconds_unhandled           = 0;
            my $seconds_unhandled_hard      = 0;

            # We put the main calculations into this anonymous sub. This is because it needs to be called
            # within the loop as well as outside. This keeps the calculations consistent
            # This takes the previous state information and calculates stats, eg:
            # 10:45:00 - 10:47:00, WARN, OK, acked, in_downtime, UP
            my $do_calculations = sub {
                my (
                    $last_timev,                         $new_state_timev,
                    $last_state,                         $last_hard_state,
                    $last_problem_has_been_acknowledged, $last_in_downtime,
                    $last_host_state_num
                ) = @_;

                my $seconds_elapsed = $new_state_timev - $last_timev;
                $state_seconds{$last_state}           += $seconds_elapsed;
                $state_hard_seconds{$last_hard_state} += $seconds_elapsed;

                if ( $last_state ne "OK" ) {
                    my ( $seconds_elapsed_not_ok_scheduled, $end_downtime ) =
                      calculate_downtime_seconds(
                        $last_timev, $new_state_timev,
                        $odw_servicecheck->{nagios_object_id},
                        $odw_host->{nagios_object_id}
                      );
                    $seconds_not_ok_scheduled
                      += $seconds_elapsed_not_ok_scheduled;
                    if ( $last_state eq "WARNING" ) {
                        $seconds_warning_scheduled
                          += $seconds_elapsed_not_ok_scheduled;
                    }
                    elsif ( $last_state eq "CRITICAL" ) {
                        $seconds_critical_scheduled
                          += $seconds_elapsed_not_ok_scheduled;
                    }
                    elsif ( $last_state eq "UNKNOWN" ) {
                        $seconds_unknown_scheduled
                          += $seconds_elapsed_not_ok_scheduled;
                    }

                    # Unhandled logic
                    if (   $last_problem_has_been_acknowledged == 0
                        && $last_in_downtime == 0
                        && $last_host_state_num == 0 )
                    {
                        $seconds_unhandled += $seconds_elapsed;

                        if ( $last_hard_state ne "OK" ) {
                            $seconds_unhandled_hard += $seconds_elapsed;
                        }
                    }

                    # Unacknowledged timer
                    if ( $last_problem_has_been_acknowledged == 0 ) {
                        $seconds_unacknowledged += $seconds_elapsed;

                        if ( $last_hard_state ne "OK" ) {
                            $seconds_unacknowledged_hard += $seconds_elapsed;
                        }
                    }

                }
            };

            # my ( $last_state, $last_hard_state,
            #     $last_problem_has_been_acknowledged,
            #     $last_in_downtime, $last_host_state_num )
            #   = $odwdb->selectrow_array( "
            # SELECT last_state, last_hard_state, acknowledged, scheduled_downtime_depth, host_state_num
            # FROM service_saved_state
            # WHERE start_timev = ? AND hostname = ? AND servicename = ?",
            #     {},
            #     $start_timev,
            #     $odw_servicecheck->hostname,
            #     $odw_servicecheck->name,
            #   );
            my ( $last_state, $last_hard_state,
                $last_problem_has_been_acknowledged,
                $last_in_downtime, $last_host_state_num )
              = @{ $service_saved_state_cache->{
                    lc $odw_servicecheck->{hostname} }
                  ->{ lc $odw_servicecheck->{name} }->{$object_id} }{
                qw(last_state last_hard_state acknowledged scheduled_downtime_depth host_state_num)
                  };

            unless ( defined $last_state ) {

                # For things that have not had saved statuses (like new hosts, services, or an upgrade from an older ODW),
                # try and query Runtime for info
                # Most of the time, the service_saved_state should have the information required
                my ( $initial_status_datetime, $initial_status,
                    $initial_status_type )
                  = find_initial_service_state( $object_id, $start_datetime );

                if ( defined $initial_status ) {
                    $last_state = convert_state_to_text($initial_status),
                      $last_hard_state =
                      convert_state_to_text($initial_status),;
                }
                else {

                    # Set default values
                    $last_state      = "INDETERMINATE";
                    $last_hard_state = "INDETERMINATE";
                }

                # Set default values
                $last_problem_has_been_acknowledged = 0;
                $last_in_downtime                   = 0;
                $last_host_state_num                = 0;
            }

            # We need to order by datetime_usec because of possibly multiple results in the same second
            my $last_timev = $start_timev;
            my $changes    = $odwdb->prepare( "
            SELECT UNIX_TIMESTAMP(datetime) as state_timev, status, status_type,
                problem_has_been_acknowledged, scheduled_downtime_depth, host_state
            FROM state_history_staging_table
            WHERE servicecheck = $odw_servicecheck_id
            ORDER BY datetime, datetime_usec
            " );
            $changes->execute;
            while (
                my (
                    $new_state_timev, $state,
                    $state_type,      $problem_has_been_acknowledged,
                    $in_downtime,     $host_state_num
                )
                = $changes->fetchrow_array
              )
            {

                $do_calculations->(
                    $last_timev,                         $new_state_timev,
                    $last_state,                         $last_hard_state,
                    $last_problem_has_been_acknowledged, $last_in_downtime,
                    $last_host_state_num
                );

                if ( $state_type eq "HARD" ) {
                    $last_hard_state = $state;
                }

                # Save info for next stage change
                $last_state = $state;
                $last_timev = $new_state_timev;
                $last_problem_has_been_acknowledged =
                  $problem_has_been_acknowledged;
                $last_in_downtime    = $in_downtime;
                $last_host_state_num = $host_state_num;
            }

            $do_calculations->(
                $last_timev,                         $end_timev + 1,
                $last_state,                         $last_hard_state,
                $last_problem_has_been_acknowledged, $last_in_downtime,
                $last_host_state_num
            );

            # Ignore services that have not had a result at all
            next if ( $state_seconds{INDETERMINATE} == 3600 );

            $odwdb->do(
                "INSERT INTO service_availability_hourly_summary
            (start_datetime, servicecheck, seconds_ok, seconds_not_ok, seconds_warning, seconds_critical, seconds_unknown,
            seconds_not_ok_hard, seconds_warning_hard, seconds_critical_hard, seconds_unknown_hard,
            seconds_not_ok_scheduled,
            seconds_warning_scheduled, seconds_critical_scheduled, seconds_unknown_scheduled,
            seconds_unhandled, seconds_unhandled_hard,
            seconds_unacknowledged, seconds_unacknowledged_hard)
            VALUES
            (?, ?, ?, ?, ?, ?, ?,
             ?, ?, ?, ?,
             ?,
             ?, ?, ?,
             ?, ?,
             ?, ?)",
                {},
                $start_datetime,
                $odw_servicecheck_id,
                $state_seconds{OK} || 0,
                $state_seconds{WARNING}
                  + $state_seconds{CRITICAL}
                  + $state_seconds{UNKNOWN} || 0,
                $state_seconds{WARNING}  || 0,
                $state_seconds{CRITICAL} || 0,
                $state_seconds{UNKNOWN}  || 0,
                $state_hard_seconds{WARNING}
                  + $state_hard_seconds{CRITICAL}
                  + $state_hard_seconds{UNKNOWN} || 0,
                $state_hard_seconds{WARNING}  || 0,
                $state_hard_seconds{CRITICAL} || 0,
                $state_hard_seconds{UNKNOWN}  || 0,
                $seconds_not_ok_scheduled,
                $seconds_warning_scheduled,
                $seconds_critical_scheduled,
                $seconds_unknown_scheduled,
                $seconds_unhandled,
                $seconds_unhandled_hard,
                $seconds_unacknowledged,
                $seconds_unacknowledged_hard,
            );

            # skip deleted
            unless ( $odw_servicecheck->{id} == 1 ) {
                $odwdb->do(
                    "INSERT INTO service_saved_state
                (start_timev, hostname, servicename, nagios_object_id, last_state, last_hard_state, acknowledged,
                scheduled_downtime_depth, host_state_num,
                opsview_instance_id)
                VALUES
                (?, ?, ?, ?, ?, ?, ?,
                ?, ?,
                ?)",
                    {},
                    $start_timev + 3600,
                    $odw_servicecheck->{hostname},
                    $odw_servicecheck->{name},
                    $object_id,
                    $last_state,
                    $last_hard_state,
                    $last_problem_has_been_acknowledged,
                    $last_in_downtime,
                    $last_host_state_num,
                    $opsview_instance_id,
                );
            }
        }

        # Insert via the staging table to do all at once, ignoring downtimes/acks
        $odwdb->do(
            "INSERT INTO state_history
                (datetime, datetime_usec, servicecheck,
                   status, status_type,
                   prior_status_datetime, prior_status, output
                )
            SELECT datetime, datetime_usec, servicecheck,
                   status, status_type,
                   prior_status_datetime, prior_status, output
            FROM state_history_staging_table
            WHERE eventtype=0
        "
        );
        $odwdb->do( "DROP TABLE IF EXISTS state_history_staging_table" );
        batch_commit();

        batch_begin();
        my $cache_components = cache_all_business_components($cache);
        my $cache_business_services =
          cache_all_business_services($cache_components);
        copy_business_service_history($cache_business_services);
        copy_business_component_history($cache_components);
        copy_business_host_history($cache_components);
        batch_commit();

        my $now           = time();
        my $seconds_taken = $now - $load_start_timev;

        my $last_reload = $reloadtimes_rs->search(
            { duration => \"IS NOT NULL" },
            { order_by => { -desc => "id" } }
        )->first;
        my $last_reload_duration;
        if ($last_reload) {
            $last_reload_duration = $last_reload->duration;
        }

        my $reloads = $reloadtimes_rs->search(
            { end_config => { -between => [ $start_timev, $end_timev ] } } )
          ->count;

        # Mark successful dataload
        $logger->info( "Finished import for hour" );
        $dataload->load_end_timev($now);
        $dataload->status( "success" );
        $dataload->num_hosts( scalar keys %$num_hosts );
        $dataload->num_services( scalar keys %$num_services );
        $dataload->num_serviceresults($num_serviceresults);
        $dataload->num_perfdata($num_perfdata);
        $dataload->duration($seconds_taken);
        $dataload->last_reload_duration($last_reload_duration);
        $dataload->reloads($reloads);
        $dataload->update;

        my $time_taken = parseInterval(
            seconds => $seconds_taken,
            String  => 1,
        );
        print '- took ', $time_taken, $/ if ( $opts->{v} );
        $logger->info( "Importing for $start_datetime_local took " . $time_taken );

        $start_dt->add( hours => 1 );
        $extra_runs++;
        $first_ever_run = 0;

        $iterations--;
        last if $iterations == 0;

    } # main loop

    # Cleanup only after all successful imports
    # Keep the last 1 days worth, for debugging purposes
    # NOTE: housekeeping trims this to 4 hours, but keep this here as a fall-back
    my $cleanup_timev = $start_timev - ( 60 * 60 * 24 );
    batch_begin();
    $odwdb->do(
        "DELETE FROM service_saved_state WHERE start_timev <= $cleanup_timev AND opsview_instance_id = $opsview_instance_id"
    );
    batch_commit();
}

sub cleanup_cache {
    for my $o ( values %$cache ) {
        delete $o->{odw_host_object}->{_class_trigger_results};
        delete $o->{odw_host_object}->{__triggers};

        delete $o->{odw_service_object}->{_class_trigger_results};
        delete $o->{odw_service_object}->{__triggers};
    }
}

# Cleanup on error
END {
    if ( $? != 0 ) {
        if ( $dataload && $dataload->status eq "running" ) {
            print "Cleanup...", $/;
            $dataload->load_end_timev( time() );
            $dataload->status( "failed" );
            $dataload->update;
        }
    }
}

sub cache_all_business_components {
    my ($cache_servicechecks) = @_;

    $logger->info( "Caching all business components" );

    my $cache     = {};
    my $to_delete = {};

    # Get all most_recent components in Odw, indexed by opsview_business_component_id
    # and add to cache
    my $sth = $odwdb->prepare( "
SELECT
 bc.id AS id,
 bc.opsview_business_component_id AS opsview_business_component_id,
 bc.name AS name,
 bc.host_template_name AS host_template_name,
 bc.quorum_pct AS quorum_pct,
 bridge.servicecheck AS servicecheck
FROM
 business_components bc
LEFT JOIN business_components_to_servicechecks_bridge bridge
 ON bc.id = bridge.business_component_id
WHERE
 bridge.end_datetime IS NULL
 AND bc.most_recent = 1
 AND bc.opsview_instance_id = ?
ORDER BY
 opsview_business_component_id, servicecheck
" );
    $sth->execute($opsview_instance_id);

    my $last_bcid = "";
    while ( my $row = $sth->fetchrow_hashref ) {
        my $this_bcid = $row->{opsview_business_component_id};
        if ( $last_bcid ne $this_bcid ) {
            $cache->{$this_bcid} = {
                odw_id             => $row->{id},
                name               => $row->{name},
                host_template_name => $row->{host_template_name},
                quorum_pct         => $row->{quorum_pct},
                servicechecks      => [],
            };
            $to_delete->{$this_bcid} = $cache->{$this_bcid};
            $last_bcid = $this_bcid;
        }
        if ( $row->{servicecheck} ) {
            push @{ $cache->{$this_bcid}->{servicechecks} },
              $row->{servicecheck};
        }
    }

    my $unset_most_recent_sth = $odwdb->prepare_cached(
        "UPDATE business_components SET most_recent=0 WHERE id=?"
    );
    my $unset_bridge_sth = $odwdb->prepare_cached( "
        UPDATE business_components_to_servicechecks_bridge
        SET end_datetime=?
        WHERE business_component_id=? AND end_datetime IS NULL" );
    my $insert_sth = $odwdb->prepare_cached(
        "INSERT INTO business_components SET
        opsview_business_component_id=?,
        name=?,
        host_template_name=?,
        quorum_pct=?,
        active_date=?,
        most_recent=1,
        opsview_instance_id=?"
    );
    my $insert_bridge_sth = $odwdb->prepare_cached( "
        INSERT INTO business_components_to_servicechecks_bridge SET
        business_component_id=?,
        servicecheck=?,
        start_datetime=?,
        end_datetime=NULL" );

    my $do_insert_if_changed = sub {
        my ( $cache, $last_bcid, $data ) = @_;

        return unless $last_bcid;

        my $do_insert;
        my $do_insert_bridge;

        # If exists, delete from $seen
        if ( my $c = $cache->{$last_bcid} ) {
            delete $to_delete->{$last_bcid};

            # if a check has been removed replace the ID using time() so it changes on every invocation
            # which then forces the cache to be updated within ODW
            my $current_servicechecks = join(
                "-",
                sort
                  map {
                    $cache_servicechecks->{$_}->{odw_service_data}->{id}
                      || time()
                  } @{ $data->{service_object_ids} }
            );
            my $old = join( "-", sort @{ $c->{servicechecks} } );

            # Crazy bug where a name in Opsview database with trailing space is considered different from ODW
            my $opsview_name = $data->{name};
            $opsview_name =~ s/\s+$//;

            # If changed, update in Odw. Update cache
            if (   $opsview_name ne $c->{name}
                || $data->{quorum_pct} ne $c->{quorum_pct}
                || $current_servicechecks ne $old
                || $data->{host_template_name} ne $c->{host_template_name} )
            {

                $logger->warn(
                        "Difference - |$opsview_name|v|"
                      . $c->{name} . "|, |"
                      . $data->{quorum_pct} . "|v|"
                      . $c->{quorum_pct}
                      . "|, |$current_servicechecks|v|$old|, |"
                      . $data->{host_template_name} . "|v|"
                      . $c->{host_template_name} . "|"
                );
                $unset_most_recent_sth->execute( $c->{odw_id} );
                $unset_bridge_sth->execute( $previous_datetime, $c->{odw_id} );
                $do_insert++;
            }
        }
        else {
            $do_insert++;
        }

        if ($do_insert) {
            $logger->info( "Updating ODW stored business components" );
            $insert_sth->execute( $last_bcid, $data->{name},
                $data->{host_template_name},
                $data->{quorum_pct}, $start_timev, $opsview_instance_id );

            my $odw_servicechecks =
              [ map { $cache_servicechecks->{$_}->{odw_service_data}->{id} }
                  @{ $data->{service_object_ids} } ];

            for my $scid ( @{ $data->{service_object_ids} } ) {
                if ( !$cache_servicechecks->{$scid}->{odw_service_data}->{id} )
                {
                    #$logger->warn("Empty scid encountered for runtime service_object_ids $scid for bcid ".$last_bcid->{name}." on HT ".$data->{host_template_name});
                    $logger->warn(
                        "Empty scid encountered for runtime service_object_ids $scid on HT "
                          . $data->{host_template_name}
                    );
                }
            }

            #print dump($odw_servicechecks);

            $cache->{$last_bcid} = {
                odw_id             => $insert_sth->{mysql_insertid},
                name               => $data->{name},
                host_template_name => $data->{host_template_name},
                quorum_pct         => $data->{quorum_pct},
                servicechecks      => $odw_servicechecks,
            };

            #print dump($cache->{$last_bcid});

            foreach my $scid (@$odw_servicechecks) {
                if ( !$scid ) {
                    $logger->warn( "Empty scid encountered" );
                    next;
                }
                $insert_bridge_sth->execute( $cache->{$last_bcid}->{odw_id},
                    $scid, $start_datetime, );
            }
        }
    };

    # Custom query here. Seems using DBIx::Class doesn't cover the extra joins required
    my $bc_sth = $runtimedb->prepare( "
SELECT
    me.id AS id,
    me.name AS name,
    host_template.name AS host_template_name,
    me.quorum_pct AS quorum_pct,
    component_host_services.service_object_id
FROM
    $opsview_dbname.business_components me
        INNER JOIN
    $opsview_dbname.hosttemplates host_template ON host_template.id = me.host_template_id
        INNER JOIN
    $opsview_dbname.business_components_host business_components_hosts ON business_components_hosts.business_component_id = me.id
        INNER JOIN
    $runtime_dbname.opsview_host_template_services component_host_services ON component_host_services.opsview_host_id = business_components_hosts.host_id
AND  component_host_services.opsview_host_template_id = me.host_template_id
ORDER BY me.id
" );
    $bc_sth->execute();

    $last_bcid = "";
    my $last_data = {};
    while ( my $row = $bc_sth->fetchrow_hashref ) {

        if ( $last_bcid ne $row->{id} ) {
            $do_insert_if_changed->( $cache, $last_bcid, $last_data );
            $last_data = {
                name               => $row->{name},
                host_template_name => $row->{host_template_name},
                quorum_pct         => $row->{quorum_pct},
                service_object_ids => [],
            };
            $last_bcid = $row->{id};
        }

        if ( $row->{service_object_id} ) {
            push @{ $last_data->{service_object_ids} },
              $row->{service_object_id};
        }

    }
    $do_insert_if_changed->( $cache, $last_bcid, $last_data );

    # Set most_recent to 0 for deleted components
    foreach my $id ( keys %$to_delete ) {
        $unset_most_recent_sth->execute( $to_delete->{$id}->{odw_id} );
        $unset_bridge_sth->execute( $previous_datetime,
            $to_delete->{$id}->{odw_id}
        );
    }

    return $cache;
}

sub cache_all_business_services {
    my ($cache_components) = @_;

    $logger->info( "Caching all business services" );

    my $cache     = {};
    my $to_delete = {};

    # Get all most_recent business services in Odw, indexed by opsview_business_service_id
    # and add to cache
    my $sth = $odwdb->prepare( "
      SELECT
       bs.id AS id,
       bs.opsview_business_service_id AS opsview_business_service_id,
       bs.name AS name,
       bridge.business_component_id AS bcid
      FROM
       business_services bs
      LEFT JOIN business_services_to_components_bridge bridge
        ON bs.id = bridge.business_service_id
      WHERE
       bridge.end_datetime IS NULL
       AND bs.most_recent = 1
       AND bs.opsview_instance_id = ?
      ORDER BY
       bs.opsview_business_service_id, bcid" );
    $sth->execute($opsview_instance_id);

    my $last_bsid = "";
    while ( my $row = $sth->fetchrow_hashref ) {
        if ( $last_bsid ne $row->{opsview_business_service_id} ) {
            $cache->{ $row->{opsview_business_service_id} } = {
                odw_id => $row->{id},
                name   => $row->{name},
                bcids  => [],
            };
            if ( $row->{bcid} ) {
                push
                  @{ $cache->{ $row->{opsview_business_service_id} }->{bcids} },
                  $row->{bcid};
            }
            $to_delete->{ $row->{opsview_business_service_id} } =
              $cache->{ $row->{opsview_business_service_id} };
            $last_bsid = $row->{opsview_business_service_id};
        }
        else {
            push @{ $cache->{ $row->{opsview_business_service_id} }->{bcids} },
              $row->{bcid};
        }
    }

    my $unset_most_recent_sth = $odwdb->prepare_cached(
        "UPDATE business_services SET most_recent=0 WHERE id=?"
    );
    my $unset_bridge_sth = $odwdb->prepare_cached( "
        UPDATE business_services_to_components_bridge
        SET end_datetime=?
        WHERE business_service_id=? AND end_datetime IS NULL" );
    my $insert_sth = $odwdb->prepare_cached(
        "INSERT INTO business_services SET
        opsview_business_service_id=?,
        name=?,
        active_date=?,
        most_recent=1,
        opsview_instance_id=?"
    );
    my $insert_bridge_sth = $odwdb->prepare_cached( "
        INSERT INTO business_services_to_components_bridge SET
        business_service_id=?,
        business_component_id=?,
        start_datetime=?,
        end_datetime=NULL" );

    # Get all business services in Opsview, including components relationships
    my $rs = $opsview_schema->resultset("BusinessService")->search(
        {},
        {
            result_class => "DBIx::Class::ResultClass::HashRefInflator",
            "select"     => [
                "me.id", "me.name",
                "business_service_components.business_component_id",
            ],
            "as"       => [ "id", "name", "bcid", ],
            "join"     => ["business_service_components"],
            "order_by" => "me.id",
        }
    );

    # If any change to bs columns, update in Odw. Add to changed_business_services->{id}
    # Use a saved function as called within loop and at the end
    my $do_insert_if_changed = sub {
        my ( $cache, $last_bsid, $data ) = @_;

        #warn("do_insert: last_bsid=$last_bsid");
        return unless $last_bsid;

        my $do_insert;
        my $do_insert_bridge;

        # If exists, delete from $seen
        if ( my $c = $cache->{$last_bsid} ) {
            delete $to_delete->{$last_bsid};

            # If changed, update in Odw - use sort to guarantee same order
            # Note that sort is alphabetical, not numerical, but that is fine for this purpose
            # NOTE: Need the "|| 1", otherwise $current != $old causing services to be recreated
            my $current = join( "-",
                sort map { $cache_components->{$_}->{odw_id} || 1 }
                  @{ $data->{bcids} }
            );
            my $old = join( "-", sort @{ $c->{bcids} } );

            #warn("current=$current old=$old");

            # Again, bizarre error where name could have trailing spaces
            my $name = $data->{name};
            $name =~ s/\s+$//;

            #TODO: Should check bridge data individually
            if (   $name ne $c->{name}
                || $current ne $old )
            {

                #warn("Service difference: |$name|v|$c->{name}|, |$current|v|$old|");
                $unset_most_recent_sth->execute( $c->{odw_id} );
                $unset_bridge_sth->execute( $previous_datetime, $c->{odw_id} );
                $do_insert++;
            }
        }
        else {
            $do_insert++;
        }

        if ($do_insert) {
            $insert_sth->execute(
                $last_bsid,   $data->{name},
                $start_timev, $opsview_instance_id
            );
            $cache->{$last_bsid} = {
                odw_id => $insert_sth->{mysql_insertid},
                name   => $data->{name},
                bcids  => $data->{bcids},
            };

            #warn(Data::Dump::dump($data->{bcids}));
            foreach my $bcid ( @{ $data->{bcids} } ) {
                $insert_bridge_sth->execute(
                    $cache->{$last_bsid}->{odw_id},
                    $cache_components->{$bcid}->{odw_id} || 1,
                    $start_datetime,
                );
            }
        }
    };

    # Create lookup hash of relationships with {opsview_bsid} = { bcids => [opsview_bcid1, opsview_bcid2, ... ] }
    $last_bsid = "";
    my $last_data = {};
    while ( my $row = $rs->next ) {

        #warn("id=$row->{id} last_bsid=$last_bsid");
        if ( $last_bsid ne $row->{id} ) {
            $do_insert_if_changed->( $cache, $last_bsid, $last_data );
            $last_data = {
                name  => $row->{name},
                bcids => [],
            };

            # Need this as a business service could have no components
            if ( $row->{bcid} ) {
                push @{ $last_data->{bcids} }, $row->{bcid};
            }
            $last_bsid = $row->{id};
        }
        else {
            push @{ $last_data->{bcids} }, $row->{bcid};
        }
    }
    $do_insert_if_changed->( $cache, $last_bsid, $last_data );

    # Set most_recent to 0 for deleted business services
    foreach my $id ( keys %$to_delete ) {

        #warn("Deleting bsid=$id");
        $unset_most_recent_sth->execute( $to_delete->{$id}->{odw_id} );
        $unset_bridge_sth->execute( $previous_datetime,
            $to_delete->{$id}->{odw_id}
        );
    }

    return $cache;
}

sub copy_business_service_history {
    my ($cache_bs) = @_;
    $logger->info( "Copying business service history" );

    # For each row in runtime where updated_at >= import_start_time OR seconds_duration==NULL ordered by business_service_id, updated_at ASC
    my $source_sth = $runtimedb->prepare( "
        SELECT
          updated_at, business_service_id, seconds_duration, status,
          components_total, components_failed, components_impacted, components_acknowledged, components_downtime
        FROM
          opsview_business_service_history
        WHERE
          updated_at BETWEEN ? AND ?
          OR (seconds_duration IS NULL AND updated_at <= ?)
          OR (updated_at+seconds_duration >= ? AND updated_at <=?)
        ORDER BY business_service_id, updated_at
    " );
    my $insert_sth = $odwdb->prepare( "
        INSERT INTO business_service_history
        SET updated_at=FROM_UNIXTIME(?), business_service_id=?, seconds_duration=?, status=?,
         components_total=?, components_failed=?, components_impacted=?, components_acknowledged=?, components_downtime=?
    " );
    my $insert_summary_sth = $odwdb->prepare( "
        INSERT INTO business_service_30min_summary
        SET start_datetime=FROM_UNIXTIME(?), business_service_id=?,
         seconds_operational=?, seconds_offline=?, seconds_downtime=?, seconds_impacted=?, seconds_acknowledged=?,
         seconds_operational_unacknowledged=?,
         seconds_offline_unacknowledged=?,
         seconds_impacted_unacknowledged=?,
         seconds_total=?
    " );

    # Setup
    my $last_bsid = "";
    my $counters  = {
        start_timev                => 0,
        bsid                       => 0,
        operational                => 0,
        offline                    => 0,
        downtime                   => 0,
        impacted                   => 0,
        acknowledged               => 0,
        operational_unacknowledged => 0,
        offline_unacknowledged     => 0,
        impacted_unacknowledged    => 0,
        total                      => 0,
    };
    my $reset_counters = sub {
        my ( $bsid, $new_start_timev ) = @_;
        foreach my $key ( keys %$counters ) {
            $counters->{$key} = 0;
        }
        $counters->{bsid}        = $bsid;
        $counters->{start_timev} = $new_start_timev;
    };
    my $do_calculations = sub {
        my ( $row, $seconds_duration ) = @_;

        #warn("Got duration=$seconds_duration");
        $counters->{total} += $seconds_duration;

        my ( $impacted, $ack );
        if ( $row->{components_impacted} > 0 ) {
            $counters->{impacted} += $seconds_duration;
            $impacted = 1;
        }

        if (
            ( $row->{status} eq "OPERATIONAL" || $row->{status} eq "OFFLINE" )
            && $row->{components_acknowledged} > 0
            && ( $row->{components_acknowledged}
                == ( $row->{components_failed} + $row->{components_impacted} ) )
          )
        {
            $counters->{acknowledged} += $seconds_duration;
            $ack = 1;
        }
        if ( $row->{status} eq "OPERATIONAL" && $impacted && !$ack ) {
            $counters->{impacted_unacknowledged} += $seconds_duration;
        }

        # Work out counters
        if ( $row->{status} eq "OPERATIONAL" ) {
            $counters->{operational} += $seconds_duration;

            # Need $impacted check as otherwise the counter is incremented even though everything is okay
            if ( $impacted && !$ack ) {
                $counters->{operational_unacknowledged} += $seconds_duration;
            }
        }
        elsif ( $row->{status} eq "OFFLINE" ) {
            $counters->{offline} += $seconds_duration;

            if ( !$ack ) {
                $counters->{offline_unacknowledged} += $seconds_duration;
            }
        }
        elsif ( $row->{status} eq "DOWNTIME" ) {
            $counters->{downtime} += $seconds_duration;
        }
    };
    my $insert_new_summary_value = sub {

        #warn("Inserting for $counters->{start_timev}");
        $insert_summary_sth->execute(
            $counters->{start_timev},
            $counters->{bsid},
            $counters->{operational},
            $counters->{offline},
            $counters->{downtime},
            $counters->{impacted},
            $counters->{acknowledged},
            $counters->{operational_unacknowledged},
            $counters->{offline_unacknowledged},
            $counters->{impacted_unacknowledged},
            $counters->{total},
        );
    };

    my $boundary_30m;

    # First row for each business service will give the last recorded state
    $source_sth->execute( $start_timev, $end_timev, $end_timev, $start_timev,
        $end_timev );
    while ( my $row = $source_sth->fetchrow_hashref ) {

        #warn(Data::Dump::dump($row));
        #warn("updated_at=".scalar gmtime $row->{updated_at});
        # If bs changes, insert last counters
        if ( $last_bsid ne $row->{business_service_id} ) {

            #warn("In here with last_bsid=$last_bsid");
            if ($last_bsid) {

                #warn("Inserting old bsid=$last_bsid");
                $insert_new_summary_value->();
            }
            $reset_counters->(
                $cache_bs->{ $row->{business_service_id} }->{odw_id} || 1,
                $start_timev,
            );
            $boundary_30m = $start_timev + 30 * 60;
            $last_bsid    = $row->{business_service_id};
        }

        # Counter of state information, indexed by odw_business_service_id, broken into 30 min chunks

        # If seconds_duration is NULL
        # If updated_at < start_time, set start_time to this hour
        # Else work out duration till the current end_time
        my $updated_at      = $row->{updated_at};
        my $this_start_time = $updated_at;
        my $this_end_time;
        if ( $this_start_time < $start_timev ) {
            $this_start_time = $start_timev;
        }
        if ( !defined $row->{seconds_duration} ) {
            $this_end_time = $end_timev + 1;
        }
        else {
            $this_end_time = $updated_at + $row->{seconds_duration};
            if ( $this_end_time > $end_timev ) {
                $this_end_time = $end_timev + 1;
            }
        }

        #warn("start=".scalar gmtime $this_start_time);
        #warn("end=".scalar gmtime $this_end_time);
        my $seconds_duration = $this_end_time - $this_start_time;

        #warn("seconds_duration=$seconds_duration");

        # If this goes beyond the 30 mins boundary, work out the cut off point and use that as the duration
        my $end_point_timev = $updated_at + $seconds_duration;
        if ( $this_end_time > $boundary_30m ) {

            # Need this IF, so that things that start in the 2nd half don't store a record in the first half
            if ( $this_start_time < $boundary_30m ) {
                $do_calculations->( $row, $boundary_30m - $this_start_time );
                if ( $boundary_30m == $end_timev + 1 ) {

                    # Break out of this if a record is found that straddles the 30min into the next hour
                    # The insert below is also not required, as it will get picked up in the next dataload
                    die( "Should not get here!" );
                    next;
                }
                $insert_new_summary_value->();
                $reset_counters->(
                    $cache_bs->{ $row->{business_service_id} }->{odw_id} || 1,
                    $boundary_30m,
                );
            }
            else {
                $reset_counters->(
                    $cache_bs->{ $row->{business_service_id} }->{odw_id} || 1,
                    $boundary_30m,
                );
                $boundary_30m = $this_start_time;
            }
            $do_calculations->( $row, $this_end_time - $boundary_30m );
            $boundary_30m = $end_timev + 1;
        }
        else {
            $do_calculations->( $row, $seconds_duration );
        }

        # Insert row
        # Change the updated_at to DATETIME, change the business_service_id to ODW's
        $insert_sth->execute(
            $this_start_time,
            $cache_bs->{ $row->{business_service_id} }->{odw_id} || 1,
            $seconds_duration,
            $row->{status},
            $row->{components_total},
            $row->{components_failed},
            $row->{components_impacted},
            $row->{components_acknowledged},
            $row->{components_downtime},
        );

    }
    if ($last_bsid) {
        $insert_new_summary_value->();
    }
}

sub copy_business_component_history {
    my ($cache_bc) = @_;
    $logger->info( "Copying business component history" );

    # For each row in runtime where updated_at >= import_start_time OR seconds_duration==NULL.
    my $source_sth = $runtimedb->prepare( "
        SELECT
          updated_at, business_component_id, seconds_duration, status,
          hosts_total, hosts_failed, hosts_acknowledged, hosts_downtime
        FROM
          opsview_business_component_history
        WHERE
          updated_at BETWEEN ? AND ?
          OR (seconds_duration IS NULL AND updated_at <= ?)
          OR (updated_at+seconds_duration >= ? AND updated_at <=?)
        ORDER BY business_component_id, updated_at
    " );
    my $insert_sth = $odwdb->prepare( "
        INSERT INTO business_component_history
        SET updated_at=FROM_UNIXTIME(?), business_component_id=?, seconds_duration=?, status=?,
         hosts_total=?, hosts_failed=?, hosts_acknowledged=?, hosts_downtime=?
    " );
    my $insert_summary_sth = $odwdb->prepare( "
        INSERT INTO business_component_30min_summary
        SET start_datetime=FROM_UNIXTIME(?), business_component_id=?,
         seconds_operational=?, seconds_failed=?, seconds_downtime=?, seconds_impacted=?, seconds_acknowledged=?,
         seconds_operational_unacknowledged=?,
         seconds_failed_unacknowledged=?,
         seconds_impacted_unacknowledged=?,
         seconds_total=?
    " );

    # Setup
    my $last_bcid = "";
    my $counters  = {
        start_timev                => 0,
        bcid                       => 0,
        operational                => 0,
        failed                     => 0,
        downtime                   => 0,
        impacted                   => 0,
        acknowledged               => 0,
        operational_unacknowledged => 0,
        failed_unacknowledged      => 0,
        impacted_unacknowledged    => 0,
        total                      => 0,
    };
    my $reset_counters = sub {
        my ( $bcid, $new_start_timev ) = @_;
        foreach my $key ( keys %$counters ) {
            $counters->{$key} = 0;
        }
        $counters->{bcid}        = $bcid;
        $counters->{start_timev} = $new_start_timev;
    };
    my $do_calculations = sub {
        my ( $row, $seconds_duration ) = @_;

        #warn("Got duration=$seconds_duration");
        $counters->{total} += $seconds_duration;

        my ( $impacted, $ack );
        if ( $row->{status} eq 'OPERATIONAL' && $row->{hosts_failed} > 0 ) {
            $counters->{impacted} += $seconds_duration;
            $impacted = 1;
        }
        if (   ( $row->{status} eq "OPERATIONAL" || $row->{status} eq "FAILED" )
            && $row->{hosts_acknowledged} > 0
            && $row->{hosts_acknowledged} == $row->{hosts_failed} )
        {
            $counters->{acknowledged} += $seconds_duration;
            $ack = 1;
        }
        if ( $impacted && !$ack ) {
            $counters->{impacted_unacknowledged} += $seconds_duration;
        }

        # Work out counters
        if ( $row->{status} eq "OPERATIONAL" ) {
            $counters->{operational} += $seconds_duration;
            if ( $impacted && !$ack ) {
                $counters->{operational_unacknowledged} += $seconds_duration;
            }
        }
        elsif ( $row->{status} eq "FAILED" ) {
            $counters->{failed} += $seconds_duration;
            if ( !$ack ) {
                $counters->{failed_unacknowledged} += $seconds_duration;
            }
        }
        elsif ( $row->{status} eq "DOWNTIME" ) {
            $counters->{downtime} += $seconds_duration;
        }
    };
    my $insert_new_summary_value = sub {

        #warn("Inserting for $counters->{start_timev}");
        $insert_summary_sth->execute(
            $counters->{start_timev},
            $counters->{bcid},
            $counters->{operational},
            $counters->{failed},
            $counters->{downtime},
            $counters->{impacted},
            $counters->{acknowledged},
            $counters->{operational_unacknowledged},
            $counters->{failed_unacknowledged},
            $counters->{impacted_unacknowledged},
            $counters->{total},
        );
    };

    my $boundary_30m;

    # First row for each business component will give the last recorded state
    $source_sth->execute( $start_timev, $end_timev, $end_timev, $start_timev,
        $end_timev );
    while ( my $row = $source_sth->fetchrow_hashref ) {

        #warn(Data::Dump::dump($row));
        #warn("updated_at=".scalar gmtime $row->{updated_at});
        # If bs changes, insert last counters
        if ( $last_bcid ne $row->{business_component_id} ) {

            #warn("In here with last_bcid=$last_bcid");
            if ($last_bcid) {

                #warn("Inserting old bcid=$last_bcid");
                $insert_new_summary_value->();
            }
            $reset_counters->(
                $cache_bc->{ $row->{business_component_id} }->{odw_id} || 1,
                $start_timev,
            );
            $boundary_30m = $start_timev + 30 * 60;
            $last_bcid    = $row->{business_component_id};
        }

        # Counter of state information, indexed by odw_business_component_id, broken into 30 min chunks

        # If seconds_duration is NULL
        # If updated_at < start_time, set start_time to this hour
        # Else work out duration till the current end_time
        my $updated_at      = $row->{updated_at};
        my $this_start_time = $updated_at;
        my $this_end_time;
        if ( $this_start_time < $start_timev ) {
            $this_start_time = $start_timev;
        }
        if ( !defined $row->{seconds_duration} ) {
            $this_end_time = $end_timev + 1;
        }
        else {
            $this_end_time = $updated_at + $row->{seconds_duration};
            if ( $this_end_time > $end_timev ) {
                $this_end_time = $end_timev + 1;
            }
        }

        #warn("start=".scalar gmtime $this_start_time);
        #warn("end=".scalar gmtime $this_end_time);
        my $seconds_duration = $this_end_time - $this_start_time;

        #warn("seconds_duration=$seconds_duration");

        # If this goes beyond the 30 mins boundary, work out the cut off point and use that as the duration
        my $end_point_timev = $updated_at + $seconds_duration;
        if ( $this_end_time > $boundary_30m ) {

            # Need this IF, so that things that start in the 2nd half don't store a record in the first half
            if ( $this_start_time < $boundary_30m ) {
                $do_calculations->( $row, $boundary_30m - $this_start_time );
                if ( $boundary_30m == $end_timev + 1 ) {

                    # Break out of this if a record is found that straddles the 30min into the next hour
                    # The insert below is also not required, as it will get picked up in the next dataload
                    die( "Should not get here!" );
                    next;
                }
                $insert_new_summary_value->();
                $reset_counters->(
                    $cache_bc->{ $row->{business_component_id} }->{odw_id} || 1,
                    $boundary_30m,
                );
            }
            else {
                $reset_counters->(
                    $cache_bc->{ $row->{business_component_id} }->{odw_id} || 1,
                    $boundary_30m,
                );
                $boundary_30m = $this_start_time;
            }
            $do_calculations->( $row, $this_end_time - $boundary_30m );
            $boundary_30m = $end_timev + 1;
        }
        else {
            $do_calculations->( $row, $seconds_duration );
        }

        # Insert row
        # Change the updated_at to DATETIME, change the business_component_id to ODW's
        $insert_sth->execute(
            $this_start_time,
            $cache_bc->{ $row->{business_component_id} }->{odw_id} || 1,
            $seconds_duration,
            $row->{status},
            $row->{hosts_total},
            $row->{hosts_failed},
            $row->{hosts_acknowledged},
            $row->{hosts_downtime},
        );

    }
    if ($last_bcid) {
        $insert_new_summary_value->();
    }
}

sub copy_business_host_history {
    my ($cache_bc) = @_;
    $logger->info( "Copying business host history" );

    # For each row in runtime where updated_at >= import_start_time OR seconds_duration==NULL.
    my $source_sth = $runtimedb->prepare( "
        SELECT
          updated_at, business_component_id, host_object_id, seconds_duration, status,
          services_total, services_failed, services_acknowledged, services_downtime
        FROM
          opsview_business_host_history
        WHERE
          updated_at BETWEEN ? AND ?
          OR (seconds_duration IS NULL AND updated_at <= ?)
          OR (updated_at+seconds_duration >= ? AND updated_at <=?)
        ORDER BY business_component_id, host_object_id, updated_at
    " );
    my $insert_sth = $odwdb->prepare( "
        INSERT INTO business_host_history
        SET updated_at=FROM_UNIXTIME(?), business_component_id=?, host=?, seconds_duration=?, status=?,
         services_total=?, services_failed=?, services_acknowledged=?, services_downtime=?
    " );
    my $insert_summary_sth = $odwdb->prepare( "
        INSERT INTO business_host_30min_summary
        SET start_datetime=FROM_UNIXTIME(?), business_component_id=?, host=?,
         seconds_operational=?, seconds_failed=?, seconds_downtime=?, seconds_acknowledged=?,
         seconds_failed_unacknowledged=?,
         seconds_total=?, seconds_impacted=0
    " );

    # Setup
    my $last_bc_h_id =
      [ '', '' ]
      ; # Need to track both component & host IDs, so [component, host]
    my $counters = {
        start_timev           => 0,
        bcid                  => 0,
        host                  => 0,
        operational           => 0,
        failed                => 0,
        downtime              => 0,
        acknowledged          => 0,
        failed_unacknowledged => 0,
        total                 => 0,
    };
    my $reset_counters = sub {
        my ( $bcid, $host, $new_start_timev ) = @_;
        foreach my $key ( keys %$counters ) {
            $counters->{$key} = 0;
        }
        $counters->{bcid}        = $bcid;
        $counters->{host}        = $host;
        $counters->{start_timev} = $new_start_timev;
    };
    my $do_calculations = sub {
        my ( $row, $seconds_duration ) = @_;

        #warn("Got duration=$seconds_duration");
        $counters->{total} += $seconds_duration;

        my $ack;
        if (   ( $row->{status} eq "OPERATIONAL" || $row->{status} eq "FAILED" )
            && $row->{services_acknowledged} > 0
            && $row->{services_acknowledged} == $row->{services_failed} )
        {
            $counters->{acknowledged} += $seconds_duration;
            $ack = 1;
        }

        # Work out counters
        if ( $row->{status} eq "OPERATIONAL" ) {
            $counters->{operational} += $seconds_duration;

            # Note: there is no operational_unacknowledged because it is
            # impossible to have operational with a failed service (will be FAILED)
        }
        elsif ( $row->{status} eq "FAILED" ) {
            $counters->{failed} += $seconds_duration;
            if ( !$ack ) {
                $counters->{failed_unacknowledged} += $seconds_duration;
            }
        }
        elsif ( $row->{status} eq "DOWNTIME" ) {
            $counters->{downtime} += $seconds_duration;
        }
    };
    my $insert_new_summary_value = sub {

        #warn("Inserting for $counters->{start_timev}");
        $insert_summary_sth->execute(
            $counters->{start_timev},  $counters->{bcid},
            $counters->{host},         $counters->{operational},
            $counters->{failed},       $counters->{downtime},
            $counters->{acknowledged}, $counters->{failed_unacknowledged},
            $counters->{total},
        );
    };

    my $boundary_30m;

    # First row for each business component will give the last recorded state
    $source_sth->execute( $start_timev, $end_timev, $end_timev, $start_timev,
        $end_timev );
    while ( my $row = $source_sth->fetchrow_hashref ) {

        #warn(Data::Dump::dump($row));
        #warn("updated_at=".scalar gmtime $row->{updated_at});
        # If bc/host changes, insert last counters
        if (   $last_bc_h_id->[0] ne $row->{business_component_id}
            || $last_bc_h_id->[1] ne $row->{host_object_id} )
        {

            #warn("In here with last_bc_h_id=$last_bc_h_id");
            if ( $last_bc_h_id->[0] && $last_bc_h_id->[1] ) {

                #warn("Inserting old bcid/hid=@$last_bc_h_id");
                $insert_new_summary_value->();
            }
            $reset_counters->(
                $cache_bc->{ $row->{business_component_id} }->{odw_id} || 1,
                get_odw_host_data( $row->{host_object_id} )->{id} || 1,
                $start_timev,
            );
            $boundary_30m      = $start_timev + 30 * 60;
            $last_bc_h_id->[0] = $row->{business_component_id};
            $last_bc_h_id->[1] = $row->{host_object_id};
        }

        # Counter of state information, indexed by odw_business_component_id, broken into 30 min chunks

        # If seconds_duration is NULL
        # If updated_at < start_time, set start_time to this hour
        # Else work out duration till the current end_time
        my $updated_at      = $row->{updated_at};
        my $this_start_time = $updated_at;
        my $this_end_time;
        if ( $this_start_time < $start_timev ) {
            $this_start_time = $start_timev;
        }
        if ( !defined $row->{seconds_duration} ) {
            $this_end_time = $end_timev + 1;
        }
        else {
            $this_end_time = $updated_at + $row->{seconds_duration};
            if ( $this_end_time > $end_timev ) {
                $this_end_time = $end_timev + 1;
            }
        }

        #warn("start=".scalar gmtime $this_start_time);
        #warn("end=".scalar gmtime $this_end_time);
        my $seconds_duration = $this_end_time - $this_start_time;

        #warn("seconds_duration=$seconds_duration");

        # If this goes beyond the 30 mins boundary, work out the cut off point and use that as the duration
        my $end_point_timev = $updated_at + $seconds_duration;
        if ( $this_end_time > $boundary_30m ) {

            # Need this IF, so that things that start in the 2nd half don't store a record in the first half
            if ( $this_start_time < $boundary_30m ) {
                $do_calculations->( $row, $boundary_30m - $this_start_time );
                if ( $boundary_30m == $end_timev + 1 ) {

                    # Break out of this if a record is found that straddles the 30min into the next hour
                    # The insert below is also not required, as it will get picked up in the next dataload
                    die( "Should not get here!" );
                    next;
                }
                $insert_new_summary_value->();
                $reset_counters->(
                    $cache_bc->{ $row->{business_component_id} }->{odw_id} || 1,
                    get_odw_host_data( $row->{host_object_id} )->{id} || 1,
                    $boundary_30m,
                );
            }
            else {
                $reset_counters->(
                    $cache_bc->{ $row->{business_component_id} }->{odw_id} || 1,
                    get_odw_host_data( $row->{host_object_id} )->{id} || 1,
                    $boundary_30m,
                );
                $boundary_30m = $this_start_time;
            }
            $do_calculations->( $row, $this_end_time - $boundary_30m );
            $boundary_30m = $end_timev + 1;
        }
        else {
            $do_calculations->( $row, $seconds_duration );
        }

        # Insert row
        # Change the updated_at to DATETIME, change the business_component_id to ODW's
        $insert_sth->execute(
            $this_start_time,
            $cache_bc->{ $row->{business_component_id} }->{odw_id} || 1,
            get_odw_host_data( $row->{host_object_id} )->{id} || 1,
            $seconds_duration,
            $row->{status},
            $row->{services_total},
            $row->{services_failed},
            $row->{services_acknowledged},
            $row->{services_downtime},
        );

    }
    if ( $last_bc_h_id->[0] && $last_bc_h_id->[1] ) {
        $insert_new_summary_value->();
    }
}

sub import_timeperiods {

    # Only import timeperiods once per import invocation
    # Needs to be done within the loop because of the start time
    return if $timeperiods_imported;

    my $cache     = {};
    my $to_delete = {};

    # Get ODW timeperiods, indexed by opsview_timeperiod_id
    my $sth = $odwdb->prepare(
        "SELECT id, opsview_timeperiod_id, crc
      FROM timeperiods
      WHERE
       most_recent = 1
       AND opsview_instance_id = ?"
    );
    $sth->execute($opsview_instance_id);
    while ( my $row = $sth->fetchrow_hashref ) {
        $cache->{ $row->{opsview_timeperiod_id} } = {
            odw_id => $row->{id},
            crc    => $row->{crc},
        };
        $to_delete->{ $row->{opsview_timeperiod_id} } =
          $cache->{ $row->{opsview_timeperiod_id} };
    }

    my $unset_most_recent_sth =
      $odwdb->prepare_cached( "UPDATE timeperiods SET most_recent=0 WHERE id=?"
      );
    my $insert_sth = $odwdb->prepare_cached(
        "INSERT INTO timeperiods SET
        opsview_timeperiod_id=?,
        name=?,
        alias=?,
        active_date=?,
        most_recent=1,
        crc=?,
        opsview_instance_id=?"
    );
    my $insert_bridge_sth = $odwdb->prepare_cached( "
        INSERT INTO timeperiods_time_segments_30min_key SET
        timeperiod_id=?,
        time_segment_30min_key=?
    " );

    # Get Opsview timeperiods
    my $current_timeperiods_rs =
      $opsview_schema->resultset("Timeperiods")->search(
        {},
        {
            columns => [
                qw(id name alias sunday monday tuesday wednesday thursday friday saturday)
            ]
        }
      );

    while ( my $tp = $current_timeperiods_rs->next ) {

        # Ignore invalid timeperiods
        my $segments_30m = $tp->get_30m_segments();

        next unless $segments_30m;

        my $do_insert;
        my $crc;

        # If not exist, will insert
        if ( !$cache->{ $tp->id } ) {
            $crc = crc16( join( "-", @$segments_30m, $tp->name, $tp->alias ) );
            $do_insert = 1;
        }

        # Else if different, set most_recent = 0 for old row. Will insert
        else {
            delete $to_delete->{ $tp->id };

            $crc = crc16( join( "-", @$segments_30m, $tp->name, $tp->alias ) );

            # if different, set most_recent = 0 for old row. Will insert
            if ( $crc ne $cache->{ $tp->id }->{crc} ) {
                $do_insert = 1;
            }
        }

        # If insert, add row
        if ($do_insert) {
            $unset_most_recent_sth->execute( $cache->{ $tp->id }->{odw_id} );
            $insert_sth->execute( $tp->id, $tp->name, $tp->alias, $start_timev,
                $crc, $opsview_instance_id, );

            my $odw_tp_id = $insert_sth->{mysql_insertid};

            # Add segment bridge with key
            foreach my $k (@$segments_30m) {
                $insert_bridge_sth->execute( $odw_tp_id, $k, );
            }
        }

    }

    # Set most_recent=0 for deleted timeperiods
    foreach my $id ( keys %$to_delete ) {
        $unset_most_recent_sth->execute( $to_delete->{$id}->{odw_id} );
    }

    $timeperiods_imported = 1;
}

sub load_time_segments {
    my $exists =
      $odwdb->selectrow_array( "SELECT 1 FROM time_segments_30m WHERE id=1" );
    if ( !$exists ) {
        my $days =
          [qw(Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sunday)];
        my $sth =
          $odwdb->prepare( "INSERT INTO time_segments_30m VALUES (?, ?)" );

        # Sun Nov  3 00:00:00 2013
        my $start = 1383436800;

        my $c = 1;
        while ( $c <= 336 ) {
            my ( undef, $min, $hour, undef, undef, undef, $wday ) =
              gmtime($start);
            my $day = $days->[$wday];
            $sth->execute(
                get_time_segment_30m_id($start),
                sprintf( "$day %02d:%02d", $hour, $min )
            );

            #$sth->execute( $c, sprintf("$day %02d:%02d", $hour, $min));
            $c++;
            $start += 1800;
        }
    }
}

sub get_time_segment_30m_id {
    my ($time) = @_;
    my ( undef, $min, $hour, undef, undef, undef, $wday ) = gmtime($time);
    return ( 1 + $wday * 48 + $hour * 2 + int( $min / 30 ) );
}

# At this point in time, are we in a downtime? If so, return the highest end downtime
# Returns 0 if no downtime found
sub find_highest_downtime_endtimev {
    my ( $timev, $id, $hid ) = @_;
    my $highest = 0;
    if ( exists $downtimes->{$id} ) {
        foreach my $downtime ( @{ $downtimes->{$id} } ) {

            # There is a test that actual_start_timev != actual_end_timev which could happen due to Nagios 4
            # If this is the case, an infinite loop could occur, so we miss these entries out
            if (   ( $downtime->{actual_start_timev} <= $timev )
                && ( $timev <= $downtime->{actual_end_timev} )
                && $downtime->{actual_start_timev}
                != $downtime->{actual_end_timev} )
            {
                $highest = max( $highest, $downtime->{actual_end_timev} );

                # Set the time to this new end time to check for overlapping downtimes
                $timev = $highest if $highest;
            }
        }
    }
    if ($hid) {
        $highest =
          max( $highest, find_highest_downtime_endtimev( $timev, $hid ) );
    }
    return $highest;
}

# At this point in time, is there a downtime about to start? If so, return the lowest start downtime
# Returns 0 if no downtime in immediate future
sub find_next_start_downtime_timev {
    my ( $timev, $end_timev, $id, $hid ) = @_;
    my $lowest = 0;
    if ( exists $downtimes->{$id} ) {
        foreach my $downtime ( @{ $downtimes->{$id} } ) {
            if (   ( $timev <= $downtime->{actual_start_timev} )
                && ( $downtime->{actual_start_timev} <= $end_timev ) )
            {
                $lowest = my_min( $lowest, $downtime->{actual_start_timev} );
            }
        }
    }
    if ($hid) {
        $lowest =
          my_min( $lowest,
            find_next_start_downtime_timev( $timev, $end_timev, $hid )
          );
    }
    return $lowest;
}

sub my_min {
    return $_[1] if ( $_[0] == 0 );
    return $_[0] if ( $_[1] == 0 );
    return min(@_);
}

# Returns the amount of time that should be added for downtime
# $scid and $hostid are the multi-master nagios_object_ids
sub calculate_downtime_seconds {
    my ( $start_point, $end_point, $scid, $hostid ) = @_;
    my $this_point               = $start_point;
    my $seconds_not_ok_scheduled = 0;
    my $end_of_downtime          = $start_point; # Assume no downtime
    if ( my $end_downtime_timev =
        find_highest_downtime_endtimev( $this_point, $scid, $hostid ) )
    {
        if ( $end_point < $end_downtime_timev ) {
            $seconds_not_ok_scheduled += ( $end_point - $start_point );
            $this_point      = $end_point;
            $end_of_downtime = $end_point;
        }
        else {
            $seconds_not_ok_scheduled += ( $end_downtime_timev - $start_point );
            $this_point      = $end_downtime_timev;
            $end_of_downtime = $end_downtime_timev;
        }
    }
    while ( $this_point < $end_point ) {

        # Track forwards and see if there were any downtimes from this_point onwards
        if (
            my $temp_start_timev = find_next_start_downtime_timev(
                $this_point, $end_point, $scid, $hostid
            )
          )
        {

            my $temp_end_timev =
              find_highest_downtime_endtimev( $temp_start_timev, $scid,
                $hostid );

            # This could be 0 if there is an error in the downtimes where a start time is after an end time
            # In this case, break out and don't check for any more downtimes in this hour
            if ( !$temp_end_timev ) {

                # TODO: log
                $logger->warn( "Got a bad downtime range" );
                last;
            }
            if ( $end_point < $temp_end_timev ) {
                $seconds_not_ok_scheduled += ( $end_point - $temp_start_timev );
                $this_point = $end_point;
            }
            else {
                $seconds_not_ok_scheduled
                  += ( $temp_end_timev - $temp_start_timev );
                $this_point = $temp_end_timev;
            }
        }
        else {
            last;
        }
    }
    return ( $seconds_not_ok_scheduled, $end_of_downtime );
}

sub find_initial_service_state {
    my ( $nagios_object_id, $start_datetime ) = @_;
    my ( $initial_status_datetime, $initial_status, $initial_status_type );

    unless ( exists $initial_states_cache->{$start_datetime} ) {
        $initial_states_cache->{$start_datetime} =
          $runtimedb->selectall_hashref(
            qq{
            SELECT a.object_id, a.state_time, a.state, a.state_type
            FROM nagios_statehistory a
            INNER JOIN (
                SELECT object_id, MAX(state_time) as max_state_time
                FROM nagios_statehistory
                WHERE
                    ( state_time BETWEEN DATE_SUB('$start_datetime', INTERVAL 14 DAY) AND '$start_datetime' )
                    AND
                state_change = 1
                GROUP BY
                    object_id
            ) AS b
            ON a.object_id=b.object_id AND a.state_time=b.max_state_time
            }, 'object_id'
          );
    }
    if ( my $data =
        $initial_states_cache->{$start_datetime}->{$nagios_object_id} )
    {
        ( $initial_status_datetime, $initial_status, $initial_status_type ) =
          @$data{qw(state_time state state_type)};
    }
    unless ( defined $initial_status ) {
        ( $initial_status_datetime, $initial_status, $initial_status_type ) =
          $runtimedb->selectrow_array(
            qq{
                SELECT start_time, state, state_type
                FROM nagios_servicechecks
                WHERE service_object_id=$nagios_object_id AND start_time = (
                    SELECT MAX(start_time)
                    FROM nagios_servicechecks
                    WHERE service_object_id=$nagios_object_id
                    AND
                    ( start_time BETWEEN DATE_SUB('$start_datetime', INTERVAL 12 HOUR) AND '$start_datetime' )
                )
                ORDER BY start_time DESC
                LIMIT 1
            }
          );
        $initial_states_cache->{$start_datetime}->{$nagios_object_id} = {
            state_time => $initial_status_datetime,
            state      => $initial_status,
            state_type => $initial_status_type,
        };
    }
    return ( $initial_status_datetime, $initial_status, $initial_status_type );
}

# This is different from Nagios::Plugins' max_state - we set UNKNOWN to be a
# higher failure state than OK
sub max_state_text {
    my ( $a, $b ) = @_;
    return "CRITICAL" if ( $a eq "CRITICAL" || $b eq "CRITICAL" );
    return "WARNING"  if ( $a eq "WARNING"  || $b eq "WARNING" );
    return "UNKNOWN"  if ( $a eq "UNKNOWN"  || $b eq "UNKNOWN" );
    return "OK"       if ( $a eq "OK"       || $b eq "OK" );
}

sub convert_state_to_text {
    my $s = shift;
    if    ( $s == 0 ) { return "OK" }
    elsif ( $s == 1 ) { return "WARNING" }
    elsif ( $s == 2 ) { return "CRITICAL" }
    elsif ( $s == 3 ) { return "UNKNOWN" }
    elsif ( $s == 4 ) { return "INDETERMINATE" }
    $logger->logdie( "Invalid service state: $s" );
}

sub convert_host_state_to_text {
    my $s = shift;
    if    ( $s == 0 ) { return "UP" }
    elsif ( $s == 1 ) { return "DOWN" }
    elsif ( $s == 2 ) { return "UNREACHABLE" }
    $logger->logdie( "Invalid host state: $s" );
}

sub convert_notification_reason_to_text {
    my $s = shift;
    if    ( $s == 0 )  { return "NORMAL" }
    elsif ( $s == 1 )  { return "ACKNOWLEDGEMENT" }
    elsif ( $s == 2 )  { return "FLAPPING STARTED" }
    elsif ( $s == 3 )  { return "FLAPPING STOPPED" }
    elsif ( $s == 4 )  { return "FLAPPING DISABLED" }
    elsif ( $s == 5 )  { return "DOWNTIME STARTED" }
    elsif ( $s == 6 )  { return "DOWNTIME STOPPED" }
    elsif ( $s == 7 )  { return "DOWNTIME CANCELLED" }
    elsif ( $s == 8 )  { return "CUSTOM" }
    elsif ( $s == 99 ) { return "CUSTOM" }
    $logger->logdie( "Invalid state: $s" );
}

sub convert_check_type_to_text {
    return "ACTIVE" if ( $_[0] == 0 );
    return "PASSIVE";
}

sub convert_state_type_to_text {
    return "SOFT" if ( $_[0] == 0 );
    return "HARD";
}

# Uses $start_timev
sub duration_within_hour {
    if ( $_[0]->{start} < $start_timev ) {
        return ( $_[0]->{end} - $start_timev );
    }
    else {
        return ( $_[0]->{end} - $_[0]->{start} );
    }
}

#sub get_prior_status_cache_by_scid {
#
#    $logger->info( "Building prior_status cache" );
#
#    my $cache_by_scid = {};
#
#    my $sth = $odwdb->prepare( "
#        SELECT
#            servicecheck, datetime, status
#        FROM
#            state_history
#        ORDER BY
#            servicecheck, datetime DESC" );
#    $sth->execute;
#    my $last_sc;
#    while ( my ( $scid, $prior_status_datatime, $prior_status ) =
#        $sth->fetchrow_array )
#    {
#        if ( !$last_sc || $scid != $last_sc ) {
#            $cache_by_scid->{$scid} = [ $prior_status_datatime, $prior_status ];
#        }
#    }
#
#    $logger->info( "Finished prior_status cache" );
#
#    return $cache_by_scid;
#}

sub get_odw_perf_label {
    my ( $odw_host_id, $odw_service_id, $name, $units ) = @_;

    my $lcname  = substr( lc($name),  0, 64 );
    my $lcunits = substr( lc($units), 0, 16 );

    if ( my $data =
        $initial_perf_cache->{$odw_service_id}->{$lcname}->{$lcunits} )
    {
        $cache_perflabels->{$odw_service_id}->{$lcname}->{$lcunits} =
          $data->{id};
    }
    elsif (
        !exists $cache_perflabels->{$odw_service_id}->{$lcname}->{$lcunits} )
    {
        # Create perf object in ODW
        #
        my ($perflabel_id) = $odwdb->selectrow_array(
            "SELECT id FROM performance_labels WHERE host = ? AND servicecheck = ? AND name = ? AND units = ?",
            {}, $odw_host_id, $odw_service_id, $name, $units
        );
        if ($perflabel_id) {
            $cache_perflabels->{$odw_service_id}->{$lcname}->{$lcunits} =
              $perflabel_id;
        }
        else {
            my $sth = $odwdb->prepare_cached(
                q{INSERT INTO performance_labels (host, servicecheck, name, units) VALUES (?,?,?,?)}
            );
            $sth->execute(
                $odw_host_id, $odw_service_id,
                substr( $name,  0, 64 ),
                substr( $units, 0, 16 )
            );
            $cache_perflabels->{$odw_service_id}->{$lcname}->{$lcunits} =
              $sth->{mysql_insertid};
        }
    }
    return $cache_perflabels->{$odw_service_id}->{$lcname}->{$lcunits};
}

sub build_saved_state_cache {
    my $start_timev = shift;

    $service_saved_state_cache = $odwdb->selectall_hashref(
        qq{
            SELECT LOWER(hostname) AS hostname, LOWER(servicename) AS servicename, nagios_object_id, last_state, last_hard_state, acknowledged, scheduled_downtime_depth, host_state_num
            FROM service_saved_state
            WHERE start_timev = $start_timev
        },
        [qw(hostname servicename nagios_object_id)]
    );
}

sub build_cache {
    my ( $start_dt, $iterations ) = @_;

    $iterations = 0 unless $iterations > 0;

    my $start_datetime = $start_dt->strftime( "%F %T" );
    my $end_dt         = get_hour_end( $start_dt->clone );
    my $start_end_range;
    if ($iterations) {
        while ( $iterations-- ) {
            $end_dt = get_hour_end( $end_dt->clone->add( seconds => 1 ) );
        }
        my $end_datetime = $end_dt->strftime( "%F %T" );
        $start_end_range = "BETWEEN '$start_datetime' AND '$end_datetime'";
    }
    else {
        $start_end_range = ">= '$start_datetime'";
    }

    $logger->info( "Taking snapshot of all objects" );
    my $objects_query = qq{
        SELECT
            sc.object_id AS sc_id, sc.name1 AS sc_hostname, sc.name2 AS sc_name,
            hs.object_id AS hs_id, hs.name1 AS hs_name
        FROM
            nagios_objects sc
            INNER JOIN (
                (
                    SELECT service_object_id AS object_id
                    FROM nagios_servicechecks
                    WHERE start_time $start_end_range
                )
                UNION DISTINCT
                (
                    SELECT object_id AS object_id
                    FROM nagios_downtimehistory
                    WHERE actual_start_time $start_end_range
                )
                UNION DISTINCT
                (
                    SELECT object_id AS object_id
                    FROM nagios_downtimehistory
                    WHERE actual_end_time $start_end_range AND actual_start_time < '$start_datetime'
                )
                UNION DISTINCT
                (
                    SELECT
                        nn.object_id AS object_id
                    FROM
                        nagios_notifications nn,
                        nagios_contactnotifications ncn,
                        nagios_contacts nc,
                        nagios_contactnotificationmethods ncnm,
                        nagios_objects no
                    WHERE nn.notification_id = ncn.notification_id
                    AND ncn.contact_object_id = nc.contact_object_id
                    AND ncn.contactnotification_id = ncnm.contactnotification_id
                    AND ncnm.command_object_id = no.object_id
                    AND nn.start_time $start_end_range
                )
                UNION DISTINCT
                (
                    SELECT object_id AS object_id
                    FROM nagios_acknowledgements
                    WHERE entry_time $start_end_range
                )
                UNION DISTINCT
                (
                    SELECT sh.object_id as object_id
                    FROM nagios_statehistory sh, nagios_objects o
                    WHERE sh.object_id = o.object_id
                    AND o.objecttype_id = 2
                    AND state_time $start_end_range
                )
                UNION DISTINCT
                (
                    SELECT object_id AS object_id
                    FROM nagios_objects
                    WHERE is_active = 1
                    AND objecttype_id = 2
                )
                UNION DISTINCT
                (
                    SELECT host_object_id AS object_id
                    FROM opsview_business_host_history
                    WHERE
                      updated_at BETWEEN ? AND ?
                      OR (seconds_duration IS NULL AND updated_at <= ?)
                      OR (updated_at+seconds_duration >= ? AND updated_at <=?)
                )
            ) AS ob
                ON sc.object_id=ob.object_id
            LEFT JOIN nagios_objects hs
                ON sc.name1=hs.name1 AND hs.name2 IS NULL AND hs.objecttype_id = 1
        WHERE
            sc.objecttype_id = 2
        GROUP BY sc_id, hs.object_id};

    my $nagios_objects =
      $runtimedb->selectall_hashref( $objects_query, 'sc_id', {},
        $start_dt->epoch, $end_dt->epoch, $end_dt->epoch, $start_dt->epoch,
        $end_dt->epoch );
    $logger->info( "Taking snapshot of all hosts data" );
    my $hosts_data = $opsviewdb->selectall_hashref(
        q{SELECT
                LOWER(h.name) AS lcname, h.name AS name, h.alias AS alias,
                hg.name AS hostgroup, hg.matpath AS hostgroup_matpath,
                ms.name AS monitored_by
                FROM hosts h
                INNER JOIN hostgroups hg
                    ON h.hostgroup=hg.id
                INNER JOIN monitoringclusters ms
                    ON h.monitored_by=ms.id
            },
        'lcname'
    );

    $logger->info( "Taking snapshot of all keywords" );
    $runtimedb->do( "SET SESSION group_concat_max_len = 25165824" );
    my $sc_keywords = $runtimedb->selectall_hashref(
        q{SELECT
            object_id, CONCAT(",", GROUP_CONCAT(DISTINCT keyword ORDER BY keyword ASC), ",") AS keywords
        FROM
            opsview_viewports
        WHERE
            host_object_id != object_id
        GROUP BY
            object_id
        },
        'object_id'
    );

    $logger->info( "Taking snapshot of all servicechecks data" );
    my $sc_data = $opsviewdb->selectall_hashref(
        q{SELECT LOWER(sc.name) as lcname, sc.name, sc.description, sg.name AS servicegroup
        FROM servicechecks sc
        INNER JOIN servicegroups sg
            ON sc.servicegroup=sg.id
        },
        'lcname'
    );

    $logger->info( "Taking snapshot of all ODW hosts" );
    my $odw_current_hosts = $odwdb->selectall_hashref(
        q{SELECT LOWER(name) as lcname, id, name, nagios_object_id, crc FROM hosts WHERE most_recent = 1},
        'lcname'
    );

    $logger->info( "Taking snapshot of all ODW servicechecks" );
    my $odw_current_scs = $odwdb->selectall_hashref(
        q{SELECT LOWER(hostname) AS lchostname, LOWER(name) AS lcname, id, hostname, name, nagios_object_id, crc FROM servicechecks WHERE most_recent = 1},
        [qw(lchostname lcname)]
    );

    $logger->info( "Taking snapshot of all performance labels" );
    $initial_perf_cache = $odwdb->selectall_hashref(
        qq{
        SELECT 
            performance_labels.id,
            performance_labels.servicecheck,
            LOWER(performance_labels.name) AS lcname,
            LOWER(performance_labels.units) AS lcunits,
            performance_labels.name,
            performance_labels.units
        FROM performance_labels
        JOIN servicechecks
            ON performance_labels.servicecheck=servicechecks.id
        WHERE
            servicechecks.most_recent=1
        }, [qw(servicecheck lcname lcunits)]
    );

    my %nagios_sc2opsview;

    my %stats;

    $logger->info( "Caching services and hosts" );
    for my $sid ( sort { $a <=> $b } keys %{ $nagios_objects || {} } ) {
        my $sc_name       = $nagios_objects->{$sid}->{sc_name};
        my $o             = $nagios_objects->{$sid};
        my $used_hostname = $o->{hs_name} || $o->{sc_hostname};
        my $used_hid      = $o->{hs_id}   || 0;
        my $sid           = $o->{sc_id};
        my $lc_hostname   = lc($used_hostname);
        my $sc_odw_host;
        my $sc_odw_sc;

        my ($sc_opsview_name) = $sc_name =~ /^([^:]+?):/;
        my $sc_used_name = lc( $sc_opsview_name || $sc_name );

        $nagios_sc2opsview{$sc_used_name} = $sc_data->{$sc_used_name};

        # The theory here is that the cache is a list of host ids already seen
        # There are three states for the cache:
        #   * not exists (not found yet)
        #   * found, and object is the same as before
        #   * found, but object is different or doesn't exist
        # For the latter, we set the cache value to be 0
        # This is removed at the end of build_cache() so that get_odw_host()
        # and others will create the ODW object as usual
        unless ( exists $cache_host->{$used_hid} ) {

            if ( my $h = $hosts_data->{$lc_hostname} ) {

                my $odw_host = $odw_current_hosts->{ $h->{lcname} };

                $h->{nagios_object_id}    = $used_hid + $instance_addition;
                $h->{opsview_instance_id} = $opsview_instance_id;
                mathpath2hg1_9( $h,
                    [ split( /,/, $h->{hostgroup_matpath} ) ] );

                $h->{crc} = host_crc16($h);

                if ($odw_host) {
                    if ( $odw_host->{crc} eq $h->{crc} ) {
                        $stats{HOSTS}->{kept}++;
                        $sc_odw_host = $odw_host;
                    }
                }
            }

            # deleted host
            else {
                $stats{HOSTS}->{deleted}++;
                $sc_odw_host = {
                    id               => $deleted_host->id,
                    name             => $deleted_host->name,
                    nagios_object_id => $deleted_host->nagios_object_id,
                };
            }

            $cache_host->{$used_hid} = $sc_odw_host || 0;
        }

        # These hosts don't exist, so we ignore the service check part
        unless ( $sc_odw_host = $cache_host->{$used_hid} ) {
            next;
        }

        if ( my $sc = { %{ $nagios_sc2opsview{$sc_used_name} || {} } } ) {
            $sc->{host}     = $sc_odw_host->{id};
            $sc->{hostname} = $sc_odw_host->{name};
            $sc->{keywords} =
              ( $sc_keywords->{$sid} || {} )->{keywords} || ',,';
            $sc->{name} = $o->{sc_name};

            my $odw_sc = $odw_current_scs->{ lc( $sc_odw_host->{name} || '' ) }
              ->{ lc $sc->{name} };

            $sc->{nagios_object_id} = $sid + $instance_addition;
            $sc->{crc}              = sc_crc16($sc);

            if ($odw_sc) {
                if ( $odw_sc->{crc} eq $sc->{crc} ) {
                    $stats{SERVICES}->{kept}++;
                    $sc_odw_sc = $odw_sc;
                }
            }

        }

        # missing servicecheck for $sid
        else {
            $stats{SERVICES}->{deleted}++;
            $sc_odw_sc = {
                id               => $deleted_servicecheck->id,
                name             => $deleted_servicecheck->name,
                hostname         => $deleted_servicecheck->hostname,
                nagios_object_id => $deleted_servicecheck->nagios_object_id,
            };
        }

        if ($sc_odw_sc) {
            $cache->{$sid}->{odw_service_data} = $sc_odw_sc;
            $cache->{$sid}->{odw_host_data}    = $sc_odw_host;
        }
    }

    # Delete all the temporary host cache items
    foreach $_ ( keys %$cache_host ) {
        if ( $cache_host->{$_} == 0 ) {
            delete $cache_host->{$_};
        }
    }

    $logger->info(
        "Cache stats for hosts: kept($stats{HOSTS}->{kept}) deleted($stats{HOSTS}->{deleted})"
    );
    $logger->info(
        "Cache stats for services: kept($stats{SERVICES}->{kept}) deleted($stats{SERVICES}->{deleted})"
    );
}

sub sc_crc16 {
    my ($sc) = @_;
    return crc16( join( "", map { $sc->{$_} } @sc_data_columns ) );
}

sub host_crc16 {
    my ($h) = @_;
    return crc16( join( "", map { $h->{$_} } @host_data_columns ) );
}

sub mathpath2hg1_9 {
    my ( $host, $hostgroups ) = @_;
    my $i = 1;
    for my $hg (@$hostgroups) {
        $host->{"hostgroup${i}"} = $hg;
        last if ++$i >= 10;
    }
}

sub get_odw_objects {
    my ($sid) = @_;
    unless ( exists $cache->{$sid} ) {

        #
        # Create host object in ODW
        #
        my ( $host_name, $servicecheck_name ) = $runtimedb->selectrow_array(
            "SELECT name1, name2 FROM nagios_objects WHERE object_id = ? AND is_active = 1",
            {}, $sid );

        # If host_name is blank, get_odw_host_object will return the deletedhost object
        my $hid = $runtimedb->selectrow_array(
            "SELECT object_id FROM nagios_objects WHERE objecttype_id = 1 AND name1 = ? AND name2 IS NULL AND is_active = 1",
            {}, $host_name
        );
        my $odw_host = get_odw_host_object($hid);

        my $opsview_servicecheck;
        if ($servicecheck_name) {
            my $sth = $opsviewdb->prepare_cached(
                q{SELECT sc.description AS description, sg.name AS servicegroup_name
                    FROM servicechecks sc
                    INNER JOIN servicegroups sg
                        ON sc.servicegroup=sg.id
                    WHERE sc.name=?}
            );
            my ($sc_name) = $servicecheck_name =~ /^([^:]+?):/;
            $sth->execute( $sc_name || $servicecheck_name );
            $opsview_servicecheck = $sth->fetchrow_hashref();
            $sth->finish;
        }
        else {
            $logger->warn( "Missing servicecheck_name for $sid" );
        }

        #
        # Create servicecheck object in ODW
        #
        my $odw_servicecheck;
        unless ($opsview_servicecheck) {
            $odw_servicecheck = Odw::Servicecheck->construct( { id => 1 } );
        }
        else {

            # Get keywords for this object_id
            my $keywords =
              $runtimedb->selectcol_arrayref( $list_keywords_for_sid_sth, {},
                $sid )
              || [];

            #print "service=".$opsview_servicecheck->name." servicegroup=".$opsview_servicecheck->servicegroup,$/;
            # Use $servicecheck_name because autogenerated servicechecks do not have an object associated
            $odw_servicecheck = Odw::Servicecheck->find_or_create_with_crc(
                {
                    hostname => $odw_host->{name},
                    name     => $servicecheck_name
                },
                {
                    description  => $opsview_servicecheck->{description},
                    servicegroup => $opsview_servicecheck->{servicegroup_name},
                    nagios_object_id => ( $sid + $instance_addition ),
                    host             => $odw_host->{id},
                    keywords         => "," . join( ",", @$keywords ) . ",",
                },
            );
        }
        $cache->{$sid}->{odw_host_data}      = $odw_host;
        $cache->{$sid}->{odw_service_object} = $odw_servicecheck;
        $cache->{$sid}->{odw_service_data}   = {
            id               => $odw_servicecheck->id,
            name             => $odw_servicecheck->name,
            hostname         => $odw_servicecheck->hostname,
            nagios_object_id => $odw_servicecheck->nagios_object_id,
        };
    }
    return $cache->{$sid};
}

sub get_odw_host_data {
    return get_odw_host_object(@_);
}

sub get_odw_host_object {
    my ($hid) = @_;

    # Can be seen that $hid is empty - don't understand how
    unless ( defined $hid ) {
        my $odw_host = Odw::Host->construct( { id => 1 } );
        return {
            id               => $odw_host->id,
            name             => $odw_host->name,
            nagios_object_id => $odw_host->nagios_object_id,
        };
    }
    unless ( exists $cache_host->{$hid} ) {

        #
        # Create host object in ODW
        #
        my ($host_name) = $runtimedb->selectrow_array(
            "SELECT name1 FROM nagios_objects WHERE object_id = ? AND objecttype_id = 1 AND is_active = 1",
            {}, $hid
        );

        my $odw_host;
        my $opsview_host;

        if ($host_name) {
            my $sth = $opsviewdb->prepare_cached(
                q{SELECT
                    h.name AS host_name, h.alias AS host_alias,
                    hg.name AS hostgroup_name, hg.matpath AS hostgroup_matpath,
                    ms.name AS monitored_by_name
                    FROM hosts h
                    INNER JOIN hostgroups hg
                        ON h.hostgroup=hg.id
                    INNER JOIN monitoringclusters ms
                        ON h.monitored_by=ms.id
                    WHERE h.name=?}
            );
            $sth->execute($host_name);
            if ( $opsview_host = $sth->fetchrow_hashref() ) {
                $opsview_host->{hostgroup_1_to_9} =
                  [ split( /,/, $opsview_host->{hostgroup_matpath} ) ];
            }
            $sth->finish;
        }
        else {

            # Warn, but use deletedhost object
            $logger->warn( "Missing host_name for $hid" );
        }

        unless ($opsview_host) {
            $odw_host = Odw::Host->construct( { id => 1 } );
        }
        else {

            #print "name=".$opsview_host->name," hostgroup=".$opsview_host->hostgroup,$/;
            # NOTE: We use $host_name as this will preserve the case of the hostname used
            $odw_host = Odw::Host->find_or_create_with_crc(
                { name => $host_name },
                {
                    hostgroup           => $opsview_host->{hostgroup_name},
                    alias               => $opsview_host->{host_alias},
                    monitored_by        => $opsview_host->{monitored_by_name},
                    hostgroups          => $opsview_host->{hostgroup_1_to_9},
                    nagios_object_id    => ( $hid + $instance_addition ),
                    opsview_instance_id => $opsview_instance_id,
                },
            );
        }
        $cache_host->{$hid} = {
            id               => $odw_host->id,
            name             => $odw_host->name,
            nagios_object_id => $odw_host->nagios_object_id,
        };
    }
    return $cache_host->{$hid};
}

sub usage {
    my $message = shift;

    print $message, $/ if ( defined($message) );
    print <<"!EOF!";
Usage: $0 [-h] [-q] [-d "YYYY-MM-DD HH" ]
Where:
    -h                  This help text
    -q                  Quiet
    -v                  Show current time when beginning each iteration
    -i X                Number of iterations to import instead of all available
    -r "YYYY-MM-DD HH"  Date to restart importing - only use
                        if a whole period of data has been lost
!EOF!

    exit defined($message) ? 1 : 0;
}

# This is required because sometimes Nagios doesn't send a "downtime end" to runtime db - not sure why
# This sets actual_end_datetime to scheduled_end_datetime if scheduled_end_datetime has passed the hour period
# Only does this for the current instance id
sub fix_nonending_downtimes {
    my ( $end_datetime, $tablename ) = @_;
    my $sth;
    $sth = $odwdb->prepare_cached( "
SELECT actual_start_datetime, actual_end_datetime, nagios_object_id, nagios_internal_downtime_id, scheduled_end_datetime
FROM $tablename
WHERE
 scheduled_end_datetime <= ?
 AND actual_end_datetime = ?
 AND nagios_object_id BETWEEN ? AND ?
" );
    $sth->execute(
        $end_datetime,          $future_datetime,
        $object_id_range_start, $object_id_range_end
    );
    while ( my $row = $sth->fetchrow_hashref ) {
        $logger->info(
                "$tablename: Setting actual_end_datetime to "
              . $row->{scheduled_end_datetime}
              . " for object_id="
              . $row->{nagios_object_id}
              . " and start time="
              . $row->{actual_start_datetime}
              . ". Was set to "
              . $row->{actual_end_datetime}
        );
        my $rows_changed = $odwdb->do(
            "UPDATE $tablename
                SET actual_end_datetime=?
                WHERE nagios_internal_downtime_id = ?
                AND actual_start_datetime = ?
                AND nagios_object_id = ?",
            {},
            $row->{scheduled_end_datetime},
            $row->{nagios_internal_downtime_id},
            $row->{actual_start_datetime},
            $row->{nagios_object_id},
        );
        if ( $rows_changed != 1 ) {
            $logger->warn( "Rows changed: $rows_changed" );
        }
    }
}

sub insert_summarised_perfdata {
    my ( $start_datetime, $perfdata_list ) = @_;
    my $sql =
      qq{INSERT INTO performance_hourly_summary (start_datetime, performance_label, average, max, min, count, stddev, stddevp, first, sum) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)};
    my $sth = $odwdb->prepare_cached($sql);
    foreach my $perflabelid ( keys %$perfdata_list ) {
        my $all_vals = $perfdata_list->{$perflabelid};

        my $vals = [ grep {defined} @{ $all_vals || [] } ];

        my $count = count(@$vals);
        next unless $count;

        my $mean    = _mean( $vals, $count );
        my $min     = min(@$vals);
        my $max     = max(@$vals);
        my $stddev  = _stddev( $mean, $vals, $count );
        my $stddevp = _stddevp( $mean, $vals, $count );
        my $first   = $vals->[0];
        my $sum     = reduce( sub { $a + $b }, @$vals );
        my @bind    = (
            $start_datetime, $perflabelid, $mean,  $max, $min, $count,
            $stddev,         $stddevp,     $first, $sum,
        );
        $sth->execute(@bind) or die $sth->errstr;
    }
}

sub block_host_import {
    return ( !$import_all_odw_hosts && !$import_host_cache->{ $_[0] } );
}

sub block_servicecheck_import {

    # Optimisation
    return 0 if $import_all_odw_servicechecks;

    # Need to do this for multi-servicechecks
    my $scname = $_[0];
    $scname =~ s/:.*//g;
    return !$import_servicecheck_cache->{$scname};
}

sub get_hour_end {
    my ($start_dt) = @_;

    return $start_dt->add(
        minutes => 59,
        seconds => 59
    );
}

sub _variance {
    my ( $mean, $vals, $count ) = @_;
    return reduce( sub { $a + $b }, map { ( $_ - $mean )**2 } @{ $vals || [] } )
      / ( $count - 1 );
}

sub _variancep {
    my ( $mean, $vals, $count ) = @_;
    return reduce( sub { $a + $b }, map { ( $_ - $mean )**2 } @{ $vals || [] } )
      / $count;
}

sub _stddev {
    my ( $mean, $vals, $count ) = @_;
    return 0 unless $count > 1;

    return sqrt _variance( $mean, $vals, $count );
}

sub _stddevp {
    my ( $mean, $vals, $count ) = @_;
    return 0 unless $count > 1;

    return sqrt _variancep( $mean, $vals, $count );
}

sub _mean {
    return reduce( sub { $a + $b }, @{ $_[0] } ) / $_[1];
}

1;
