CREATE TABLE `dlv_xx` (
  `pk` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `id` bigint(20) NOT NULL DEFAULT 0 COMMENT 'delivery pk',
  `deleted_status` tinyint(4) NOT NULL DEFAULT 0 COMMENT '0=no, 1=yes, 2=recycled',
  `state` int(11) NOT NULL DEFAULT 0,
  `state_string` varchar(100) GENERATED ALWAYS AS (case when `state` = 0 then 'Being edited' when `state` = 11 then 'Targeting pending' when `state` = 12 then 'Counting in progress' when `state` = 13 then 'Arbitration in progress' when `state` = 15 then 'Target ready' when `state` = 21 then 'Personalization pending' when `state` = 22 then 'Personalization in progress' when `state` = 25 then 'Message finalized' when `state` = 37 then 'Personalization or count failed' when `state` = 45 then 'Ready to be delivered' when `state` = 51 then 'Start pending' when `state` = 55 then 'In progress' when `state` = 61 then 'Retry pending' when `state` = 62 then 'Retry in progress' when `state` = 71 then 'Pause requested' when `state` = 72 then 'Pause in progress' when `state` = 75 then 'Paused' when `state` = 81 then 'Stop requested' when `state` = 82 then 'Stop in progress' when `state` = 85 then 'Stopped' when `state` = 87 then 'Failed' when `state` = 95 then 'Finished' when `state` = 100 then 'Deleted' else '??' end) VIRTUAL,
  `contactDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `analysisEndDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `internal_name` varchar(150) NOT NULL DEFAULT '',
  `label` varchar(255) NOT NULL DEFAULT '',
  `sender_address` varchar(200) NOT NULL DEFAULT '',
  `subject` varchar(5000) NOT NULL,
  `delivery_code` varchar(255) NOT NULL DEFAULT '',
  `category` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci GENERATED ALWAYS AS (case when `delivery_code` like 'SE%' or `delivery_code` like 'EHQ\\_%' then 'Triggers' when `delivery_code` like 'W%' then 'Adhoc' else '' end) STORED,
  `country` varchar(50) NOT NULL DEFAULT '?',
  `to_send` int(10) unsigned NOT NULL DEFAULT 0,
  `initial_target_population` int(10) unsigned NOT NULL DEFAULT 0,
  `rejected_at_import` int(10) unsigned NOT NULL DEFAULT 0,
  `rejected_by_rule` int(10) unsigned NOT NULL DEFAULT 0,
  `processed` int(10) unsigned NOT NULL DEFAULT 0,
  `success` int(10) unsigned NOT NULL DEFAULT 0,
  `success_without_seeds` int(10) unsigned NOT NULL DEFAULT 0,
  `open` int(10) unsigned NOT NULL DEFAULT 0,
  `open_ratio` float unsigned GENERATED ALWAYS AS (case when `success_without_seeds` = 0 then 0 else 100 * `open` / `success_without_seeds` end) STORED,
  `reactivity_ratio` float unsigned NOT NULL DEFAULT 0,
  `directory_path` varchar(500) NOT NULL DEFAULT '',
  `typo_ip_affinity` varchar(200) NOT NULL DEFAULT '',
  `ctrl_group_exclusions` int(10) unsigned NOT NULL DEFAULT 0,
  `delivery_provider` varchar(255) NOT NULL DEFAULT '',
  `waves_split_size` varchar(20) NOT NULL DEFAULT '',
  `waves_split_delay` int(11) unsigned NOT NULL DEFAULT 0,
  `waves_mode` tinyint(3) unsigned NOT NULL DEFAULT 0 COMMENT 'split=0, calendar=1, smooth=2',
  `waves_enabled` tinyint(3) unsigned NOT NULL DEFAULT 0,
  `validity_duration` int(11) unsigned NOT NULL DEFAULT 0,
  `retry_period` int(11) unsigned NOT NULL DEFAULT 0,
  `retry_nb_max` tinyint(3) unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`pk`),
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `id_contactDate` (`contactDate`,`id`) USING BTREE,
  KEY `category` (`category`(1)),
  KEY `country` (`country`(1)) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=87947094 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC `PAGE_COMPRESSED`='ON' 


CREATE TABLE `dlv_kpi_xx` (
  `pk` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `id` bigint(20) NOT NULL DEFAULT 0 COMMENT 'delivery pk',
  `email_domain` varchar(60) NOT NULL DEFAULT '',
  `to_send` int(11) unsigned NOT NULL DEFAULT 0,
  `processed` int(11) unsigned NOT NULL DEFAULT 0,
  `success` int(11) unsigned NOT NULL DEFAULT 0,
  `success_without_seeds` int(11) unsigned NOT NULL DEFAULT 0,
  `open` int(11) unsigned NOT NULL DEFAULT 0,
  `open_mpp` int(11) unsigned NOT NULL DEFAULT 0,
  `click` int(11) unsigned NOT NULL DEFAULT 0,
  `click_raw` int(11) unsigned NOT NULL DEFAULT 0,
  `opt_out` int(11) unsigned NOT NULL DEFAULT 0,
  `complaint` int(11) unsigned NOT NULL DEFAULT 0,
  `soft_bounce` int(11) unsigned NOT NULL DEFAULT 0 COMMENT '@unreachable + @mailBoxFull + @invalidDomain + @disabled + @notConnected + @refused',
  `unknown_user` int(11) unsigned NOT NULL DEFAULT 0,
  `error` int(11) unsigned NOT NULL DEFAULT 0,
  `delivered_ratio` float unsigned GENERATED ALWAYS AS (case when `processed` = 0 then 0 else 100 * `success` / `processed` end) VIRTUAL,
  `open_ratio` float unsigned GENERATED ALWAYS AS (case when `success_without_seeds` = 0 then 0 else 100 * `open` / `success_without_seeds` end) VIRTUAL,
  `reactivity_ratio` float unsigned GENERATED ALWAYS AS (case when `open` = 0 then 0 else 100 * `click` / `open` end) VIRTUAL,
  `click_ratio` float unsigned GENERATED ALWAYS AS (case when `success_without_seeds` = 0 then 0 else 100 * `click` / `success_without_seeds` end) VIRTUAL,
  `opt_out_ratio` float unsigned GENERATED ALWAYS AS (case when `success_without_seeds` = 0 then 0 else 100 * `opt_out` / `success_without_seeds` end) VIRTUAL,
  `complaint_ratio` float unsigned GENERATED ALWAYS AS (case when `success_without_seeds` = 0 then 0 else 100 * `complaint` / `success_without_seeds` end) VIRTUAL,
  `unknown_user_ratio` float unsigned GENERATED ALWAYS AS (case when `processed` = 0 then 0 else 100 * `unknown_user` / `processed` end) VIRTUAL,
  `soft_bounce_ratio` float unsigned GENERATED ALWAYS AS (case when `processed` = 0 then 0 else 100 * `soft_bounce` / `processed` end) VIRTUAL,
  `error_ratio` float unsigned GENERATED ALWAYS AS (case when `processed` = 0 then 0 else 100 * `error` / `processed` end) VIRTUAL,
  `hard_bounce` int(11) unsigned NOT NULL DEFAULT 0,
  `unreachable` int(11) unsigned NOT NULL DEFAULT 0,
  `rejected` int(11) unsigned NOT NULL DEFAULT 0,
  `invalid_domain` int(11) unsigned NOT NULL DEFAULT 0,
  `account_disabled` int(11) unsigned NOT NULL DEFAULT 0,
  `mailbox_full` int(11) unsigned NOT NULL DEFAULT 0,
  `hard_bounce_ratio` float unsigned GENERATED ALWAYS AS (case when `processed` = 0 then 0 else 100 * `hard_bounce` / `processed` end) STORED,
  `unreachable_ratio` float unsigned GENERATED ALWAYS AS (case when `processed` = 0 then 0 else 100 * `unreachable` / `processed` end) STORED,
  `rejected_ratio` float unsigned GENERATED ALWAYS AS (case when `processed` = 0 then 0 else 100 * `rejected` / `processed` end) STORED,
  `invalid_domain_ratio` float unsigned GENERATED ALWAYS AS (case when `processed` = 0 then 0 else 100 * `invalid_domain` / `processed` end) STORED,
  `account_disabled_ratio` float unsigned GENERATED ALWAYS AS (case when `processed` = 0 then 0 else 100 * `account_disabled` / `processed` end) STORED,
  `mailbox_full_ratio` float unsigned GENERATED ALWAYS AS (case when `processed` = 0 then 0 else 100 * `mailbox_full` / `processed` end) STORED,
  `cancelled` int(11) unsigned NOT NULL DEFAULT 0,
  `quarantine` int(11) unsigned NOT NULL DEFAULT 0,
  `cancelled_ratio` float GENERATED ALWAYS AS (case when `to_send` = 0 then 0 else 100 * `cancelled` / `to_send` end) STORED,
  `quarantine_ratio` float GENERATED ALWAYS AS (case when `processed` = 0 then 0 else 100 * `quarantine` / `processed` end) STORED,
  PRIMARY KEY (`pk`),
  UNIQUE KEY `unicity2` (`email_domain`,`id`),
  KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=265353589 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC `PAGE_COMPRESSED`='ON'