ALTER TABLE host_state DROP PRIMARY KEY;
ALTER TABLE host_state ADD COLUMN id binary(20) NOT NULL COMMENT 'host.id' FIRST;
UPDATE host_state SET id = host_id;
ALTER TABLE host_state ADD PRIMARY KEY (id);
ALTER TABLE host_state ADD COLUMN properties_checksum binary(20) AFTER environment_id;
UPDATE host_state SET properties_checksum = 0;
ALTER TABLE host_state MODIFY COLUMN properties_checksum binary(20) COMMENT 'sha1(all properties)' NOT NULL;
ALTER TABLE host_state ADD UNIQUE INDEX idx_host_state_host_id (host_id);

ALTER TABLE service_state DROP PRIMARY KEY;
ALTER TABLE service_state ADD COLUMN id binary(20) NOT NULL COMMENT 'service.id' FIRST;
UPDATE service_state SET id = service_id;
ALTER TABLE service_state ADD PRIMARY KEY (id);
ALTER TABLE service_state ADD COLUMN properties_checksum binary(20) AFTER environment_id;
UPDATE service_state SET properties_checksum = 0;
ALTER TABLE service_state MODIFY COLUMN properties_checksum binary(20) COMMENT 'sha1(all properties)' NOT NULL;
ALTER TABLE service_state ADD UNIQUE INDEX idx_service_state_service_id (service_id);

ALTER TABLE downtime
    ADD COLUMN parent_id binary(20) COMMENT 'For service downtimes, the ID of the host downtime that created this downtime by using the "all_services" flag of the schedule-downtime API.' AFTER triggered_by_id,
    MODIFY COLUMN triggered_by_id binary(20) COMMENT 'The ID of the downtime that triggered this downtime. This is set when creating downtimes on a host or service higher up in the dependency chain using the "child_option" "DowntimeTriggeredChildren" and can also be set manually via the API.';
ALTER TABLE downtime_history
    ADD COLUMN parent_id binary(20) COMMENT 'For service downtimes, the ID of the host downtime that created this downtime by using the "all_services" flag of the schedule-downtime API.' AFTER triggered_by_id,
    MODIFY COLUMN triggered_by_id binary(20) COMMENT 'The ID of the downtime that triggered this downtime. This is set when creating downtimes on a host or service higher up in the dependency chain using the "child_option" "DowntimeTriggeredChildren" and can also be set manually via the API.';

ALTER TABLE checkcommand_argument MODIFY COLUMN argument_order smallint DEFAULT NULL;
ALTER TABLE eventcommand_argument MODIFY COLUMN argument_order smallint DEFAULT NULL;
ALTER TABLE notificationcommand_argument MODIFY COLUMN argument_order smallint DEFAULT NULL;

ALTER TABLE acknowledgement_history MODIFY COLUMN id binary(20) NOT NULL COMMENT 'sha1(environment.name + "Host"|"Service" + host|service.name + set_time)';
ALTER TABLE flapping_history MODIFY COLUMN id binary(20) NOT NULL COMMENT 'sha1(environment.name + "Host"|"Service" + host|service.name + start_time)';

ALTER TABLE host ADD INDEX idx_host_name_ci (name_ci) COMMENT 'Host list filtered using quick search';
ALTER TABLE service ADD INDEX idx_service_name_ci (name_ci) COMMENT 'Service list filtered using quick search';

ALTER TABLE user ADD INDEX idx_user_name_ci (name_ci) COMMENT 'User list filtered using quick search';
ALTER TABLE user ADD INDEX idx_user_name (name) COMMENT 'User list filtered/ordered by name; User detail filter';

ALTER TABLE usergroup ADD INDEX `idx_usergroup_display_name` (`display_name`) COMMENT 'Usergroup list filtered/ordered by display_name';
ALTER TABLE usergroup ADD INDEX idx_usergroup_name_ci (name_ci) COMMENT 'Usergroup list filtered using quick search';
ALTER TABLE usergroup ADD INDEX idx_usergroup_name (name) COMMENT 'Usergroup list filtered/ordered by name; Usergroup detail filter';

ALTER TABLE hostgroup_customvar
    ADD INDEX idx_hostgroup_customvar_hostgroup_id (hostgroup_id, customvar_id),
    ADD INDEX idx_hostgroup_customvar_customvar_id (customvar_id, hostgroup_id);
ALTER TABLE servicegroup_customvar
    ADD INDEX idx_servicegroup_customvar_servicegroup_id (servicegroup_id, customvar_id),
    ADD INDEX idx_servicegroup_customvar_customvar_id (customvar_id, servicegroup_id);
ALTER TABLE checkcommand_customvar
    ADD INDEX idx_checkcommand_customvar_command_id (command_id, customvar_id),
    ADD INDEX idx_checkcommand_customvar_customvar_id (customvar_id, command_id);
ALTER TABLE eventcommand_customvar
    ADD INDEX idx_eventcommand_customvar_command_id (command_id, customvar_id),
    ADD INDEX idx_eventcommand_customvar_customvar_id (customvar_id, command_id);
ALTER TABLE notificationcommand_customvar
    ADD INDEX idx_notificationcommand_customvar_command_id (command_id, customvar_id),
    ADD INDEX idx_notificationcommand_customvar_customvar_id (customvar_id, command_id);
ALTER TABLE notification_customvar
    ADD INDEX idx_notification_customvar_notification_id (notification_id, customvar_id),
    ADD INDEX idx_notification_customvar_customvar_id (customvar_id, notification_id);
ALTER TABLE timeperiod_customvar
    ADD INDEX idx_timeperiod_customvar_timeperiod_id (timeperiod_id, customvar_id),
    ADD INDEX idx_timeperiod_customvar_customvar_id (customvar_id, timeperiod_id);
ALTER TABLE user_customvar
    ADD INDEX idx_user_customvar_user_id (user_id, customvar_id),
    ADD INDEX idx_user_customvar_customvar_id (customvar_id, user_id);
ALTER TABLE usergroup_customvar
    ADD INDEX idx_usergroup_customvar_usergroup_id (usergroup_id, customvar_id),
    ADD INDEX idx_usergroup_customvar_customvar_id (customvar_id, usergroup_id);

ALTER TABLE host
    MODIFY active_checks_enabled enum('n','y') NOT NULL,
    MODIFY passive_checks_enabled enum('n','y') NOT NULL,
    MODIFY event_handler_enabled enum('n','y') NOT NULL,
    MODIFY notifications_enabled enum('n','y') NOT NULL,
    MODIFY flapping_enabled enum('n','y') NOT NULL,
    MODIFY perfdata_enabled enum('n','y') NOT NULL,
    MODIFY is_volatile enum('n','y') NOT NULL;
ALTER TABLE host_state
    ADD COLUMN normalized_performance_data longtext DEFAULT NULL AFTER performance_data,
    ADD COLUMN last_comment_id binary(20) DEFAULT NULL COMMENT 'comment.id' AFTER acknowledgement_comment_id,
    ADD COLUMN scheduling_source text DEFAULT NULL AFTER check_source,
    MODIFY is_problem enum('n','y') NOT NULL,
    MODIFY is_handled enum('n','y') NOT NULL,
    MODIFY is_reachable enum('n','y') NOT NULL,
    MODIFY is_flapping enum('n','y') NOT NULL,
    MODIFY is_overdue enum('n','y') NOT NULL,
    MODIFY is_acknowledged enum('n','y','sticky') NOT NULL,
    MODIFY in_downtime enum('n','y') NOT NULL;
ALTER TABLE service
    MODIFY active_checks_enabled enum('n','y') NOT NULL,
    MODIFY passive_checks_enabled enum('n','y') NOT NULL,
    MODIFY event_handler_enabled enum('n','y') NOT NULL,
    MODIFY notifications_enabled enum('n','y') NOT NULL,
    MODIFY flapping_enabled enum('n','y') NOT NULL,
    MODIFY perfdata_enabled enum('n','y') NOT NULL,
    MODIFY is_volatile enum('n','y') NOT NULL;
ALTER TABLE service_state
    ADD COLUMN normalized_performance_data longtext DEFAULT NULL AFTER performance_data,
    ADD COLUMN last_comment_id binary(20) DEFAULT NULL COMMENT 'comment.id' AFTER acknowledgement_comment_id,
    ADD COLUMN scheduling_source text DEFAULT NULL AFTER check_source,
    MODIFY is_problem enum('n','y') NOT NULL,
    MODIFY is_handled enum('n','y') NOT NULL,
    MODIFY is_reachable enum('n','y') NOT NULL,
    MODIFY is_flapping enum('n','y') NOT NULL,
    MODIFY is_overdue enum('n','y') NOT NULL,
    MODIFY is_acknowledged enum('n','y','sticky') NOT NULL,
    MODIFY in_downtime enum('n','y') NOT NULL;
ALTER TABLE icingadb_instance
    MODIFY responsible enum('n','y') NOT NULL,
    MODIFY icinga2_notifications_enabled enum('n','y') NOT NULL,
    MODIFY icinga2_active_service_checks_enabled enum('n','y') NOT NULL,
    MODIFY icinga2_active_host_checks_enabled enum('n','y') NOT NULL,
    MODIFY icinga2_event_handlers_enabled enum('n','y') NOT NULL,
    MODIFY icinga2_flap_detection_enabled enum('n','y') NOT NULL,
    MODIFY icinga2_performance_data_enabled enum('n','y') NOT NULL;
ALTER TABLE checkcommand_argument
    MODIFY repeat_key enum('n','y') NOT NULL,
    MODIFY required enum('n','y') NOT NULL,
    MODIFY skip_key enum('n','y') NOT NULL;
ALTER TABLE eventcommand_argument
    MODIFY repeat_key enum('n','y') NOT NULL,
    MODIFY required enum('n','y') NOT NULL,
    MODIFY skip_key enum('n','y') NOT NULL;
ALTER TABLE notificationcommand_argument
    MODIFY repeat_key enum('n','y') NOT NULL,
    MODIFY required enum('n','y') NOT NULL,
    MODIFY skip_key enum('n','y') NOT NULL;
ALTER TABLE comment
    MODIFY name varchar(548) NOT NULL COMMENT '255+1+255+1+36, i.e. "host.name!service.name!UUID"',
    MODIFY is_persistent enum('n','y') NOT NULL,
    MODIFY is_sticky enum('n','y') NOT NULL;
ALTER TABLE downtime
    ADD COLUMN scheduled_by varchar(767) DEFAULT NULL COMMENT 'Name of the ScheduledDowntime which created this Downtime. 255+1+255+1+255, i.e. "host.name!service.name!scheduled-downtime-name"' AFTER end_time,
    MODIFY name varchar(548) NOT NULL COMMENT '255+1+255+1+36, i.e. "host.name!service.name!UUID"',
    MODIFY is_flexible enum('n','y') NOT NULL,
    MODIFY is_in_effect enum('n','y') NOT NULL;
ALTER TABLE timeperiod
    MODIFY prefer_includes enum('n','y') NOT NULL;
ALTER TABLE user
    MODIFY notifications_enabled enum('n','y') NOT NULL;
ALTER TABLE zone
    MODIFY is_global enum('n','y') NOT NULL;
ALTER TABLE downtime_history
    ADD COLUMN scheduled_by varchar(767) DEFAULT NULL COMMENT 'Name of the ScheduledDowntime which created this Downtime. 255+1+255+1+255, i.e. "host.name!service.name!scheduled-downtime-name"' AFTER end_time,
    MODIFY is_flexible enum('n','y') NOT NULL,
    MODIFY has_been_cancelled enum('n','y') NOT NULL;
ALTER TABLE comment_history
    MODIFY is_persistent enum('n','y') NOT NULL,
    MODIFY is_sticky enum('n','y') NOT NULL,
    MODIFY has_been_removed enum('n','y') NOT NULL;
ALTER TABLE acknowledgement_history
    MODIFY author varchar(255) DEFAULT NULL COLLATE utf8mb4_unicode_ci COMMENT 'NULL if ack_set event happened before Icinga DB history recording',
    MODIFY comment text DEFAULT NULL COMMENT 'NULL if ack_set event happened before Icinga DB history recording',
    MODIFY is_sticky enum('n','y') DEFAULT NULL COMMENT 'NULL if ack_set event happened before Icinga DB history recording',
    MODIFY is_persistent enum('n','y') DEFAULT NULL COMMENT 'NULL if ack_set event happened before Icinga DB history recording';

INSERT INTO icingadb_schema (version, timestamp)
  VALUES (2, CURRENT_TIMESTAMP() * 1000);

ALTER TABLE host_state
    MODIFY output longtext DEFAULT NULL,
    MODIFY long_output longtext DEFAULT NULL,
    MODIFY performance_data longtext DEFAULT NULL;

ALTER TABLE state_history
    ADD COLUMN scheduling_source text DEFAULT NULL AFTER check_source,
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.name + host|service.name + event_time)',
    MODIFY output longtext DEFAULT NULL,
    MODIFY long_output longtext DEFAULT NULL;

ALTER TABLE service_state
    MODIFY output longtext DEFAULT NULL,
    MODIFY long_output longtext DEFAULT NULL,
    MODIFY performance_data longtext DEFAULT NULL;

ALTER TABLE notification_history
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.name + notification.name + type + send_time)';

ALTER TABLE user_notification_history
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(notification_history_id + user_id)',
    MODIFY notification_history_id binary(20) NOT NULL COMMENT 'UUID notification_history.id';

ALTER TABLE history
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.name + event_type + x...) given that sha1(environment.name + x...) = *_history_id',
    MODIFY notification_history_id binary(20) DEFAULT NULL COMMENT 'notification_history.id',
    MODIFY state_history_id binary(20) DEFAULT NULL COMMENT 'state_history.id',
    ADD CONSTRAINT fk_history_acknowledgement_history FOREIGN KEY (acknowledgement_history_id) REFERENCES acknowledgement_history (id) ON DELETE CASCADE,
    ADD CONSTRAINT fk_history_comment_history FOREIGN KEY (comment_history_id) REFERENCES comment_history (comment_id) ON DELETE CASCADE,
    ADD CONSTRAINT fk_history_downtime_history FOREIGN KEY (downtime_history_id) REFERENCES downtime_history (downtime_id) ON DELETE CASCADE,
    ADD CONSTRAINT fk_history_flapping_history FOREIGN KEY (flapping_history_id) REFERENCES flapping_history (id) ON DELETE CASCADE,
    ADD CONSTRAINT fk_history_notification_history FOREIGN KEY (notification_history_id) REFERENCES notification_history (id) ON DELETE CASCADE,
    ADD CONSTRAINT fk_history_state_history FOREIGN KEY (state_history_id) REFERENCES state_history (id) ON DELETE CASCADE;

ALTER TABLE user_notification_history
    ADD CONSTRAINT fk_user_notification_history_notification_history FOREIGN KEY (notification_history_id) REFERENCES notification_history (id) ON DELETE CASCADE;

ALTER TABLE host
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + name)',
    MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
ALTER TABLE hostgroup
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + name)',
    MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
ALTER TABLE hostgroup_member
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + host_id + hostgroup_id)',
    MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
ALTER TABLE host_customvar
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + host_id + customvar_id)',
    MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
ALTER TABLE hostgroup_customvar
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + hostgroup_id + customvar_id)',
    MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
ALTER TABLE host_state
    MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
ALTER TABLE service
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + name)',
    MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
ALTER TABLE servicegroup
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + name)',
    MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
ALTER TABLE servicegroup_member
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + servicegroup_id + service_id)',
    MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
ALTER TABLE service_customvar
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + service_id + customvar_id)',
    MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
ALTER TABLE servicegroup_customvar
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + servicegroup_id + customvar_id)',
    MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
ALTER TABLE service_state
    MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
ALTER TABLE endpoint
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + name)',
    MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
ALTER TABLE environment
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(Icinga CA public key)';
ALTER TABLE checkcommand
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + type + name)';
ALTER TABLE checkcommand_argument
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + command_id + argument_key)';
ALTER TABLE checkcommand_envvar
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + command_id + envvar_key)';
ALTER TABLE checkcommand_customvar
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + command_id + customvar_id)',
    MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
ALTER TABLE eventcommand
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + type + name)';
ALTER TABLE eventcommand_argument
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + command_id + argument_key)';
ALTER TABLE eventcommand_envvar
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + command_id + envvar_key)';
ALTER TABLE eventcommand_customvar
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + command_id + customvar_id)',
    MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
ALTER TABLE notificationcommand
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + type + name)';
ALTER TABLE notificationcommand_argument
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + command_id + argument_key)';
ALTER TABLE notificationcommand_envvar
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + command_id + envvar_key)';
ALTER TABLE notificationcommand_customvar
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + command_id + customvar_id)',
    MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
ALTER TABLE comment
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + name)';
ALTER TABLE downtime
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + name)';
ALTER TABLE notification
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + name)',
    MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
ALTER TABLE notification_user
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + notification_id + user_id)';
ALTER TABLE notification_usergroup
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + notification_id + usergroup_id)';
ALTER TABLE notification_recipient
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + notification_id + (user_id | usergroup_id))';
ALTER TABLE notification_customvar
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + notification_id + customvar_id)',
    MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
ALTER TABLE icon_image
    MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
ALTER TABLE action_url
    MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
ALTER TABLE notes_url
    MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
ALTER TABLE timeperiod
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + name)';
ALTER TABLE timeperiod_range
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + range_id + timeperiod_id)';
ALTER TABLE timeperiod_override_include
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + include_id + timeperiod_id)';
ALTER TABLE timeperiod_override_exclude
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + exclude_id + timeperiod_id)';
ALTER TABLE timeperiod_customvar
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + timeperiod_id + customvar_id)',
    MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
ALTER TABLE customvar
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + name + value)',
    MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
ALTER TABLE customvar_flat
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + flatname + flatvalue)',
    MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
ALTER TABLE user
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + name)',
    MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
ALTER TABLE usergroup
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + name)',
    MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
ALTER TABLE usergroup_member
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + usergroup_id + user_id)',
    MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
ALTER TABLE user_customvar
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + user_id + customvar_id)',
    MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
ALTER TABLE usergroup_customvar
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + usergroup_id + customvar_id)',
    MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
ALTER TABLE zone
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + name)',
    MODIFY environment_id binary(20) NOT NULL COMMENT 'environment.id';
ALTER TABLE flapping_history
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + "Host"|"Service" + host|service.name + start_time)';
ALTER TABLE acknowledgement_history
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + "Host"|"Service" + host|service.name + set_time)';

/*
 * Schema changes after https://github.com/Icinga/icingadb/pull/403:
 */

ALTER TABLE checkcommand_customvar
    DROP INDEX idx_checkcommand_customvar_command_id,
    DROP INDEX idx_checkcommand_customvar_customvar_id;

ALTER TABLE eventcommand_customvar
    DROP INDEX idx_eventcommand_customvar_command_id,
    DROP INDEX idx_eventcommand_customvar_customvar_id;

ALTER TABLE notificationcommand_customvar
    DROP INDEX idx_notificationcommand_customvar_command_id,
    DROP INDEX idx_notificationcommand_customvar_customvar_id;

ALTER TABLE notification
    RENAME COLUMN command_id TO notificationcommand_id;
ALTER TABLE notification
    MODIFY notificationcommand_id binary(20) NOT NULL COMMENT 'notificationcommand.id';

ALTER TABLE checkcommand_argument
    RENAME COLUMN command_id TO checkcommand_id;
ALTER TABLE checkcommand_argument
    MODIFY checkcommand_id binary(20) NOT NULL COMMENT 'checkcommand.id',
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + checkcommand_id + argument_key)';

ALTER TABLE checkcommand_envvar
    RENAME COLUMN command_id TO checkcommand_id;
ALTER TABLE checkcommand_envvar
    MODIFY checkcommand_id binary(20) NOT NULL COMMENT 'checkcommand.id',
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + checkcommand_id + envvar_key)';

ALTER TABLE checkcommand_customvar
    RENAME COLUMN command_id TO checkcommand_id;
ALTER TABLE checkcommand_customvar
    MODIFY checkcommand_id binary(20) NOT NULL COMMENT 'checkcommand.id',
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + checkcommand_id + customvar_id)';

ALTER TABLE eventcommand_argument
    RENAME COLUMN command_id TO eventcommand_id;
ALTER TABLE eventcommand_argument
    MODIFY eventcommand_id binary(20) NOT NULL COMMENT 'eventcommand.id',
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + eventcommand_id + argument_key)';

ALTER TABLE eventcommand_envvar
    RENAME COLUMN command_id TO eventcommand_id;
ALTER TABLE eventcommand_envvar
    MODIFY eventcommand_id binary(20) NOT NULL COMMENT 'eventcommand.id',
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + eventcommand_id + envvar_key)';

ALTER TABLE eventcommand_customvar
    RENAME COLUMN command_id TO eventcommand_id;
ALTER TABLE eventcommand_customvar
    MODIFY eventcommand_id binary(20) NOT NULL COMMENT 'eventcommand.id',
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + eventcommand_id + customvar_id)';

ALTER TABLE notificationcommand_argument
    RENAME COLUMN command_id TO notificationcommand_id;
ALTER TABLE notificationcommand_argument
    MODIFY notificationcommand_id binary(20) NOT NULL COMMENT 'notificationcommand.id',
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + notificationcommand_id + argument_key)';

ALTER TABLE notificationcommand_envvar
    RENAME COLUMN command_id TO notificationcommand_id;
ALTER TABLE notificationcommand_envvar
    MODIFY notificationcommand_id binary(20) NOT NULL COMMENT 'notificationcommand.id',
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + notificationcommand_id + envvar_key)';

ALTER TABLE notificationcommand_customvar
    RENAME COLUMN command_id TO notificationcommand_id;
ALTER TABLE notificationcommand_customvar
    MODIFY notificationcommand_id binary(20) NOT NULL COMMENT 'notificationcommand.id',
    MODIFY id binary(20) NOT NULL COMMENT 'sha1(environment.id + notificationcommand_id + customvar_id)';

ALTER TABLE checkcommand_customvar
    ADD INDEX idx_checkcommand_customvar_checkcommand_id (checkcommand_id, customvar_id),
    ADD INDEX idx_checkcommand_customvar_customvar_id (customvar_id, checkcommand_id);

ALTER TABLE eventcommand_customvar
    ADD INDEX idx_eventcommand_customvar_eventcommand_id (eventcommand_id, customvar_id),
    ADD INDEX idx_eventcommand_customvar_customvar_id (customvar_id, eventcommand_id);

ALTER TABLE notificationcommand_customvar
    ADD INDEX idx_notificationcommand_customvar_notificationcommand_id (notificationcommand_id, customvar_id),
    ADD INDEX idx_notificationcommand_customvar_customvar_id (customvar_id, notificationcommand_id);

ALTER TABLE downtime
    ADD COLUMN scheduled_duration bigint unsigned NOT NULL AFTER scheduled_end_time,
    ADD COLUMN duration bigint unsigned NOT NULL COMMENT 'Duration of the downtime: When the downtime is flexible, this is the same as flexible_duration otherwise scheduled_duration' AFTER end_time,
    MODIFY COLUMN flexible_duration bigint unsigned NOT NULL AFTER is_flexible;
UPDATE downtime SET scheduled_duration = scheduled_end_time - scheduled_start_time, duration = (CASE WHEN is_flexible = 'y' THEN flexible_duration ELSE scheduled_end_time - scheduled_start_time END) WHERE scheduled_duration = 0;

ALTER TABLE service_state ADD COLUMN host_id binary(20) NOT NULL COMMENT 'host.id' AFTER id;
UPDATE service_state INNER JOIN service ON service.id = service_state.service_id SET service_state.host_id = service.host_id WHERE service_state.host_id = REPEAT('\0', 20);

ALTER TABLE comment
    ADD INDEX idx_comment_author (author) COMMENT 'Comment list filtered/ordered by author',
    ADD INDEX idx_comment_expire_time (expire_time) COMMENT 'Comment list filtered/ordered by expire_time';

ALTER TABLE downtime
    ADD INDEX idx_downtime_entry_time (entry_time) COMMENT 'Downtime list filtered/ordered by entry_time',
    ADD INDEX idx_downtime_start_time (start_time) COMMENT 'Downtime list filtered/ordered by start_time',
    ADD INDEX idx_downtime_end_time (end_time) COMMENT 'Downtime list filtered/ordered by end_time',
    ADD INDEX idx_downtime_scheduled_start_time (scheduled_start_time) COMMENT 'Downtime list filtered/ordered by scheduled_start_time',
    ADD INDEX idx_downtime_scheduled_end_time (scheduled_end_time) COMMENT 'Downtime list filtered/ordered by scheduled_end_time',
    ADD INDEX idx_downtime_author (author) COMMENT 'Downtime list filtered/ordered by author',
    ADD INDEX idx_downtime_duration (duration) COMMENT 'Downtime list filtered/ordered by duration';

ALTER TABLE service_state
    ADD INDEX idx_service_state_is_problem (is_problem, severity) COMMENT 'Service list filtered by is_problem ordered by severity',
    ADD INDEX idx_service_state_severity (severity) COMMENT 'Service list filtered/ordered by severity',
    ADD INDEX idx_service_state_soft_state (soft_state, last_state_change) COMMENT 'Service list filtered/ordered by soft_state; recently recovered filter',
    ADD INDEX idx_service_state_last_state_change (last_state_change) COMMENT 'Service list filtered/ordered by last_state_change';

ALTER TABLE host_state
    ADD INDEX idx_host_state_is_problem (is_problem, severity) COMMENT 'Host list filtered by is_problem ordered by severity',
    ADD INDEX idx_host_state_severity (severity) COMMENT 'Host list filtered/ordered by severity',
    ADD INDEX idx_host_state_soft_state (soft_state, last_state_change) COMMENT 'Host list filtered/ordered by soft_state; recently recovered filter',
    ADD INDEX idx_host_state_last_state_change (last_state_change) COMMENT 'Host list filtered/ordered by last_state_change';

ALTER TABLE hostgroup
    ADD INDEX idx_hostroup_name (name) COMMENT 'Host/service/host group list filtered by host group name';

ALTER TABLE servicegroup
    ADD INDEX idx_servicegroup_name (name) COMMENT 'Host/service/service group list filtered by service group name';

ALTER TABLE notification
    DROP INDEX idx_host_id,
    DROP INDEX idx_service_id,
    ADD INDEX idx_notification_host_id (host_id),
    ADD INDEX idx_notification_service_id (service_id);

ALTER TABLE zone
    DROP INDEX idx_parent_id,
    ADD INDEX idx_zone_parent_id (parent_id);

ALTER TABLE history
    ADD INDEX idx_history_host_service_id (host_id, service_id, event_time) COMMENT 'Host/service history detail filter';

ALTER TABLE notification_history
    DROP INDEX idx_notification_history_event_time,
    ADD INDEX idx_notification_history_send_time (send_time) COMMENT 'Notification list filtered/ordered by send_time';