CREATE TABLE `#__cmgiftcard_cards` (
  `id` int(10) UNSIGNED NOT NULL,
  `order_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `card_code` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `design_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `amount` decimal(10,2) UNSIGNED NOT NULL DEFAULT '0.00',
  `message` varchar(1024) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `from_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `to_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `to_email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `to_address_1` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `to_address_2` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `to_address_3` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `to_postal_box` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `to_city` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `to_state` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `to_postal_code` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `to_country` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `deliver_date` date NULL DEFAULT NULL,
  `delivered` datetime NULL DEFAULT NULL,
  `redeemed` DATETIME NULL DEFAULT NULL,
  `modified` datetime NULL DEFAULT NULL,
  `modified_by` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `checked_out` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `checked_out_time` datetime NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `#__cmgiftcard_codes` (
  `id` int(10) UNSIGNED NOT NULL,
  `code` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `gift_card_id` int(10) NOT NULL DEFAULT '0',
  `published` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
  `ordering` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `created` datetime NULL DEFAULT NULL,
  `created_by` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `modified` datetime NULL DEFAULT NULL,
  `modified_by` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `checked_out` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `checked_out_time` datetime NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `#__cmgiftcard_designs` (
  `id` int(10) UNSIGNED NOT NULL,
  `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `card_type` enum('physical','electronic') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'electronic',
  `image` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `published` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
  `ordering` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `created` datetime NULL DEFAULT NULL,
  `created_by` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `modified` datetime NULL DEFAULT NULL,
  `modified_by` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `checked_out` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `checked_out_time` datetime NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `#__cmgiftcard_email_templates` (
  `id` int(10) UNSIGNED NOT NULL,
  `template_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `subject` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `body` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `modified` datetime NULL DEFAULT NULL,
  `modified_by` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `checked_out` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `checked_out_time` datetime NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `#__cmgiftcard_email_templates` (`id`, `template_id`, `subject`, `body`, `modified`, `modified_by`, `checked_out`, `checked_out_time`) VALUES
(1, 'order_success', 'Thank you for your order {order_number}', '<p><strong>This is a sample email template.</strong></p>\r\n<p>Dear {last_name},</p>\r\n<p>Thank you for completing your order {order_number}!</p>\r\n<p>Please find your invoice in the attachment.</p>\r\n<p>Best regards,<br />Company Name</p>\r\n<p><strong>This is a sample email template.</strong></p>\r\n<p>Available tags:</p>\r\n<ul>\r\n<li>{first_name}: Sender\'s first name</li>\r\n<li>{last_name}: Sender\'s last name</li>\r\n<li>{completed_date}: Order\'s completed date</li>\r\n<li>{order_amount}: Order\'s total amount</li>\r\n<li>{order_number}: Order\'s number</li>\r\n<li>{payment_method}: Payment method</li>\r\n<li>{transaction_id}: Transaction ID</li>\r\n</ul>', NULL, 0, 0, NULL),
(2, 'gift_card_sender', 'Your gift card for {recipient_email} was delivered', '<p><strong>This is a sample email template.</strong></p>\r\n<p>Dear {sender_last_name},</p>\r\n<p>On {order_date}, you purchase a {amount} gift card for {recipient_name}. We just sent the gift card to {recipient_name} as you requested.</p>\r\n<p>Best regards,<br />Company Name</p>\r\n<p><strong>This is a sample email template.</strong></p>\r\n<ul>\r\n<li>{sender_name}: Sender\'s name</li>\r\n<li>{recipient_name}: Recipient\'s name</li>\r\n<li>{completed_date}: Order\'s completed date</li>\r\n<li>{gift_card_amount}: Gift card\'s amount</li>\r\n<li>{order_amount}: Order\'s total amount</li>\r\n<li>{order_number}: Order\'s number</li>\r\n</ul>', NULL, 0, 0, NULL),
(3, 'gift_card_recipient', '{sender_name} sent you a gift card!', '<p><strong>This is a sample email template.</strong></p>\r\n<p>{sender_name} purchased a gift card for you.</p>\r\n<table style="width: 100%;" border="0" cellspacing="0" cellpadding="2">\r\n<tbody>\r\n<tr>\r\n<td style="width: 50%; text-align: center;">{gift_card_image}</td>\r\n<td style="width: 50%;">\r\n<p>{gift_card_message}</p>\r\n<p>Gift Card Code: {gift_card_code}</p>\r\n<p>Gift Card Amount: {gift_card_amount}</p>\r\n</td>\r\n</tr>\r\n</tbody>\r\n</table>\r\n<p>Best regards,<br />Company Name</p>\r\n<p><strong>This is a sample email template.</strong></p>', NULL, 0, 0, NULL),
(4, 'physical_admin', 'New physical gift card {gift_card_code} from order {order_number}', '<p><strong>This is a sample email template.</strong></p>\r\n<p>Dear administrator,</p>\r\n<p>There is a new physical gift card from order {order_number}.</p>\r\n<ul>\r\n<li>Gift card\'s code: {gift_card_code}</li>\r\n<li>Gift card\'s amount: {gift_card_amount}</li>\r\n<li>Order\'s number: {order_number}</li>\r\n<li>Order\'s completed date: {completed_date}</li>\r\n<li>Order\'s total amount: {order_amount}</li>\r\n<li>Payment method: {payment_method}</li>\r\n<li>Transaction ID: {transaction_id}</li>\r\n<li>Sender\'s name: {sender_name}</li>\r\n<li>Recipient\'s name: {recipient_name}</li>\r\n<li>Recipient\'s email: {recipient_email}</li>\r\n<li>Recipient\'s address 1: {recipient_address_1}</li>\r\n<li>Recipient\'s address 2: {recipient_address_2}</li>\r\n<li>Recipient\'s address 3: {recipient_address_3}</li>\r\n<li>Recipient\'s postal box: {recipient_postal_box}</li>\r\n<li>Recipient\'s city: {recipient_city}</li>\r\n<li>Recipient\'s state: {recipient_state}</li>\r\n<li>Recipient\'s postal_code: {recipient_postal_code}</li>\r\n<li>Recipient\'s country: {recipient_country}</li>\r\n<li>Message to recipient: {gift_card_message}</li>\r\n</ul>\r\n<p>Best regards,<br />Company Name</p>', NULL, '0', '0', NULL);

CREATE TABLE `#__cmgiftcard_orders` (
  `id` int(10) UNSIGNED NOT NULL,
  `order_number` int(10) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `first_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `last_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `address_1` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `address_2` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `address_3` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `postal_box` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `city` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `state` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `postal_code` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `country` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `payment_method` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `transaction_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `order_status` enum('unpaid','paid','refunded','') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'unpaid',
  `created` datetime NULL DEFAULT NULL,
  `completed` datetime NULL DEFAULT NULL,
  `modified` datetime NULL DEFAULT NULL,
  `modified_by` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `checked_out` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `checked_out_time` datetime NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `#__cmgiftcard_cards`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_orderid` (`order_id`);

ALTER TABLE `#__cmgiftcard_codes`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_giftcardid` (`gift_card_id`);

ALTER TABLE `#__cmgiftcard_designs`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `#__cmgiftcard_email_templates`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `#__cmgiftcard_orders`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `idx_ordernumber` (`order_number`) USING BTREE,
  ADD KEY `idx_userid` (`user_id`);


ALTER TABLE `#__cmgiftcard_cards`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
ALTER TABLE `#__cmgiftcard_codes`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
ALTER TABLE `#__cmgiftcard_designs`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
ALTER TABLE `#__cmgiftcard_email_templates`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
ALTER TABLE `#__cmgiftcard_orders`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;