[user@ec2-use1]$ pv user_service.dump | mysql --defaults-file=mysqlroot.cnf -h rds-apse2 -u sp user_service

[user@ec2-use1]$ pv user_service.dump | ssh ec2-user@ec2-apse2 mysql --defaults-file=mysqlroot.cnf -h rds-apse2 -u sp user_service

[user@ec2-use1]$ ssh -L 3306:rds-apse2:3306 ec2-user@ec2-apse2
[user@ec2-use1]$ pv user_service.dump | mysql --defaults-file=mysqlroot.cnf -h localhost -u sp user_service

# pv user_service.dump | LD_PRELOAD=/home/ec2-user/tcp_nodelay.so mysql --defaults-file=mysqlroot.cnf -h rds-apse2 -u sp user_service
^C.5MiB 0:00:23 [2.99MiB/s] [==============>

# pv user_service.dump | ssh -o Compression=no ec2-user@ec2-apse2 mysql --defaults-file=mysqlroot.cnf -h rds-apse2 -u sp user_service
    390MiB 0:01:14 [5.20MiB/s]

CREATE VIEW conversations_search AS (
    SELECT a.id, a.organization_id, a.status, a.way, a.agent_id, a.subject, a.snooze_until, a.channel_id, a.source, a.contact_id, a.name, a.email, a.created, a.message, a.category, a.is_personal, a.is_customer, a.last_update,
        CONCAT(DATE_FORMAT(a.last_update, '%Y%m%d'), a.is_customer, DATE_FORMAT(a.last_update, '%H%i%S'), RIGHT(LPAD(a.id, 6, '0'), 6)) AS priority_sort,
        CONCAT(DATE_FORMAT(a.last_update, '%Y%m%d%H%i%S'), RIGHT(LPAD(a.id, 6, '0'), 6)) AS date_sort,
        CONCAT(RIGHT(LPAD(CAST((UTC_TIMESTAMP() - a.last_update) AS UNSIGNED) * 100, 12, '0'), 12), RIGHT(LPAD(a.id, 6, '0'), 6)) AS longest_sort
    FROM (
        SELECT c.id, c.organization_id, c.status, e.way, c.agent_id, c.subject AS subject, c.snooze_until, c.channel_id, c.source, c.contact_id, cn.name, cn.email, e.created, e.message, e.category, c.is_personal,
            IF(pc.customer_id IS NULL, 0, 1) AS is_customer,
            (
                IFNULL(
                    (SELECT created FROM messages WHERE conversation_id = c.id AND way = 'IN' ORDER BY created DESC LIMIT 1),
                    IFNULL(
                        (SELECT created FROM messages WHERE conversation_id = c.id AND way = 'OUT' ORDER BY created DESC LIMIT 1),
                        (SELECT created FROM message_drafts WHERE conversation_id = c.id)
                    )
                )
            ) AS last_update
        FROM (
            SELECT m.way, m.created, m.message, m.conversation_id, 'MESSAGE' AS category FROM messages m WHERE `status` != 'DRAFT'
            UNION
            SELECT NULL AS way, n.created AS created, n.message, n.conversation_id, 'NOTE' AS category FROM conversation_notes n WHERE `status` != 'DRAFT'
        ) AS e LEFT JOIN conversations c ON c.id = e.conversation_id LEFT JOIN contacts cn ON cn.id = c.contact_id
            LEFT JOIN processor_customers pc ON pc.contact_id = c.contact_id
    ) AS a
);

* the ticket id
* the organization_id to which the ticket is affected
* The status (closed, opened, etc)
* the way the last message was sent (received or sent)
* The assigned agent id
* The subject
* The snooze time (if any)
* the channel id (like support@xxx.com)
* The contact id (From email)
* The contact's name
* The contact's email
* The ticket's creation date
* the last message content
* the category (if the last entry ss a message or a note)
* If the received message is personal (the channel where the email was sent is personal, like you@email.com, or open to anyone in the team, like support@..)
* If the contact is a customer
* The last time the ticket was updated
* A column that is used to sort by priority
* A column to sort by date
* A column to sort by waiting longer

CREATE TABLE `contacts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(250) DEFAULT NULL,
  `email` varchar(250) NOT NULL,
  `created` datetime NOT NULL,
  `organization_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`,`organization_id`),
  KEY `organization_id` (`organization_id`),
  CONSTRAINT `contacts_ibfk_2` FOREIGN KEY (`organization_id`) REFERENCES `organizations` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11181 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `conversations` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `subject` varchar(250) DEFAULT NULL,
  `status` varchar(15) NOT NULL,
  `source` varchar(15) NOT NULL,
  `created` datetime(6) NOT NULL,
  `snooze_until` datetime DEFAULT NULL,
  `channel_id` bigint(20) unsigned DEFAULT NULL,
  `organization_id` int(11) NOT NULL,
  `contact_id` bigint(20) unsigned NOT NULL,
  `agent_id` bigint(20) unsigned DEFAULT NULL,
  `is_personal` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `agent_id` (`agent_id`),
  KEY `channel_id` (`channel_id`),
  KEY `contact_id` (`contact_id`),
  KEY `organization_id` (`organization_id`),
  CONSTRAINT `conversations_ibfk_1` FOREIGN KEY (`agent_id`) REFERENCES `agents` (`id`),
  CONSTRAINT `conversations_ibfk_2` FOREIGN KEY (`channel_id`) REFERENCES `channels` (`id`),
  CONSTRAINT `conversations_ibfk_3` FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`id`),
  CONSTRAINT `conversations_ibfk_4` FOREIGN KEY (`organization_id`) REFERENCES `organizations` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=17502 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `messages` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `message` mediumtext DEFAULT NULL,
  `way` varchar(3) NOT NULL,
  `created` datetime(6) NOT NULL,
  `last_update` datetime NOT NULL,
  `status` varchar(20) NOT NULL,
  `conversation_id` bigint(20) unsigned NOT NULL,
  `contact_id` bigint(20) unsigned DEFAULT NULL,
  `agent_id` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `agent_id` (`agent_id`),
  KEY `contact_id` (`contact_id`),
  KEY `conversation_id` (`conversation_id`),
  CONSTRAINT `messages_ibfk_1` FOREIGN KEY (`agent_id`) REFERENCES `agents` (`id`),
  CONSTRAINT `messages_ibfk_2` FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`id`),
  CONSTRAINT `messages_ibfk_3` FOREIGN KEY (`conversation_id`) REFERENCES `conversations` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=27845 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `conversation_notes` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `agent_id` bigint(20) unsigned DEFAULT NULL,
  `message` mediumtext DEFAULT NULL,
  `status` varchar(20) NOT NULL,
  `created` datetime(6) NOT NULL,
  `conversation_id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `agent_id` (`agent_id`),
  KEY `conversation_id` (`conversation_id`),
  CONSTRAINT `conversation_notes_ibfk_1` FOREIGN KEY (`agent_id`) REFERENCES `agents` (`id`),
  CONSTRAINT `conversation_notes_ibfk_2` FOREIGN KEY (`conversation_id`) REFERENCES `conversations` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1452 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `message_drafts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `content` mediumtext DEFAULT NULL,
  `created` datetime(6) NOT NULL,
  `last_updated` datetime NOT NULL,
  `conversation_id` bigint(20) unsigned NOT NULL,
  `agent_id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `agent_id` (`agent_id`),
  KEY `conversation_id` (`conversation_id`),
  CONSTRAINT `message_drafts_ibfk_1` FOREIGN KEY (`agent_id`) REFERENCES `agents` (`id`),
  CONSTRAINT `message_drafts_ibfk_2` FOREIGN KEY (`conversation_id`) REFERENCES `conversations` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=653 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `agents` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(250) DEFAULT NULL,
  `email` varchar(250) NOT NULL,
  `organization_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ix_agents_email` (`email`),
  UNIQUE KEY `email` (`email`,`organization_id`),
  KEY `organization_id` (`organization_id`),
  CONSTRAINT `agents_ibfk_1` FOREIGN KEY (`organization_id`) REFERENCES `organizations` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=679 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `organizations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(250) NOT NULL,
  `slug` varchar(250) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ix_organizations_slug` (`slug`),
) ENGINE=InnoDB AUTO_INCREMENT=647 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;