CREATE TABLE IF NOT EXISTS `#__djc2_messages` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `user_from` int(10) NOT NULL,
  `sender_name` varchar(400) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `sender_email` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `user_to` int(10) NOT NULL,
  `recipient_name` varchar(400) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `recipient_email` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `subject` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `message` mediumtext COLLATE utf8mb4_unicode_ci,
  `sent_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `additional_data` mediumtext COLLATE utf8mb4_unicode_ci,
  `attachments` mediumtext COLLATE utf8mb4_unicode_ci,
  `is_opened` tinyint(4) NOT NULL DEFAULT '0',
  `template_type` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_from_to` (`user_from`,`user_to`)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `#__djc2_message_templates` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `type` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `state` tinyint(3) NOT NULL DEFAULT '0',
  `subject` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `body` mediumtext COLLATE utf8mb4_unicode_ci,
  `attachments` mediumtext COLLATE utf8mb4_unicode_ci,
  `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `created_by` int(10) NOT NULL DEFAULT '0',
  `checked_out` int(10) NOT NULL DEFAULT '0',
  `checked_out_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `params` mediumtext COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_type` (`type`)
) AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `#__djc2_order_statuses` ADD `message_template` VARCHAR(100) NULL AFTER `status_code`;

INSERT INTO `#__djc2_message_templates` (`id`, `type`, `name`, `state`, `subject`, `body`, `attachments`, `created`, `created_by`, `checked_out`, `checked_out_time`, `params`) VALUES
(1, 'order', 'Order - customer notification', 1, 'We have received your order no. [[order_number]]', '<h1 style=\"text-align: center;\">Order no. [[order_number]]</h1>\r\n<p>Dear [[recipient_name]],</p>\r\n<p>Thank you for placing your order no [[order_number]]</p>\r\n<div>[[order_customer_data]]</div>\r\n<div>[[order_items]]</div>\r\n<p>Click <a href=\"[[order_link]]\">here</a> in order to check the order details on our website.</p>', '[]', '0000-00-00 00:00:00', 0, 0, '0000-00-00 00:00:00', NULL),
(2, 'order_admin', 'Order - admin notification', 1, 'Customer placed the order no. [[order_number]]', '<h1 style=\"text-align: center;\">Order no. [[order_number]]</h1>\r\n<p>Hello, </p>\r\n<p>[[recipient_name]] has just placed a new order.</p>\r\n<div>[[order_customer_data]]</div>\r\n<div>[[order_items]]</div>\r\n<p>Click <a href=\"[[order_link]]\">here</a> in order to check the order details on the website.</p>', '[]', '0000-00-00 00:00:00', 0, 0, '0000-00-00 00:00:00', NULL),
(3, 'order_status', 'Order status - customer notification', 1, 'The status of the order no. [[order_number]] has changed to [[order_status]]', '<p>Hi [[recipient_name]],</p>\r\n<p>The status of your order no. [[order_number]] has changed to [[order_status]].</p>\r\n<p>[[order_status_description]]</p>\r\n<p>Click <a href=\"[[order_link]]\">here</a> in order to check the order details on our website.</p>', '[]', '0000-00-00 00:00:00', 0, 0, '0000-00-00 00:00:00', NULL),
(4, 'order_status_admin', 'Order status changed - admin notification', 1, 'The status of the order no. [[order_number]] has changed to [[order_status]]', '<p>Hi,</p>\r\n<p>The status of an order no. [[order_number]] made by [[recipient_name]] has changed to [[order_status]].</p>\r\n<p>[[order_status_description]]</p>\r\n<p>Click <a href=\"[[order_link]]\">here</a> in order to check the order details on the website.</p>', '[]', '0000-00-00 00:00:00', 0, 0, '0000-00-00 00:00:00', NULL),
(5, 'query', 'Query - customer notification', 1, 'We have received your query', '<h1 style=\"text-align: center;\">Inquiry</h1>\r\n<p>Dear [[recipient_name]],</p>\r\n<p>Thank you for your inquiry.</p>\r\n<div>[[query_customer_data]]</div>\r\n<div>[[query_items]]</div>\r\n<p>Click <a href=\"[[order_link]]\">here</a> in order to check the order details on our website.</p>', '[]', '0000-00-00 00:00:00', 0, 0, '0000-00-00 00:00:00', NULL),
(6, 'query_admin', 'Query admin - admin notification', 1, 'Customer sent an inquiry', '<h1 style=\"text-align: center;\">Inquiry</h1>\r\n<p>Hello, </p>\r\n<p>[[recipient_name]] has just placed a new order.</p>\r\n<div>[[query_customer_data]]</div>\r\n<div>[[query_items]]</div>', '[]', '0000-00-00 00:00:00', 0, 0, '0000-00-00 00:00:00', NULL),
(7, 'subscription_new', 'New subscription - customer notification', 1, 'Subscription activation', '<p>Dear [[recipient_name]],</p>\r\n<p>Your subscription has been activated. Below you fill find necessary details regarading type of subscription(s) and their expiration date. Thank you.</p>\r\n<div>[[subscription_items]]</div>', '[]', '0000-00-00 00:00:00', 0, 0, '0000-00-00 00:00:00', NULL),
(8, 'subscription_expire_soon', 'Expiring subscription - customer notification', 1, 'Your subscription will soon expire', '<p>Dear [[recipient_name]],<br /><br />Your \"[[subscription_item]]\" subscription is about to expire on [[expiration_date]].<br /><br />Should you have any questions, don\'t hesitate to contact us.</p>', '[]', '0000-00-00 00:00:00', 0, 0, '0000-00-00 00:00:00', NULL),
(9, 'subscription_expired', 'Expired subscription - customer notification', 1, 'Your subscription has expired', '<p>Dear [[recipient_name]],<br /><br />Your \"[[subscription_item]]\" subscription has expired.<br /><br />Should you have any questions, don\'t hesitate to contact us.</p>', '[]', '0000-00-00 00:00:00', 0, 0, '0000-00-00 00:00:00', NULL);

