CREATE TABLE `mst_email_marketing_contact` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `email_id` varchar(255) NOT NULL,
  `is_subscribe` tinyint(2) NOT NULL DEFAULT '1' COMMENT '(1 => Subscribe User, 0 => Unsubscribe User)',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modified_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `mst_marketing_campaign` (
  `cm_id` int(11) NOT NULL AUTO_INCREMENT,
  `campaign_title` varchar(250) DEFAULT NULL,
  `from_mail` varchar(250) DEFAULT NULL,
  `subject_line` varchar(250) DEFAULT NULL,
  `schedule_sent_date` date DEFAULT NULL,
  `schedule_sent_time` time DEFAULT NULL,
  `content_template` longtext,
  `template_id` int(10) DEFAULT NULL,
  `total_open_count` int(11) DEFAULT NULL,
  `total_clicked_count` int(11) DEFAULT NULL,
  `bounced_count` int(11) DEFAULT NULL,
  `total_unsubscribed` int(11) DEFAULT NULL,
  `total_delivered` int(11) DEFAULT NULL,
  `last_open_date` date DEFAULT NULL,
  `last_clicked_date` date DEFAULT NULL,
  `clickable_url` varchar(250) DEFAULT NULL,
  `delivery_date_time` datetime DEFAULT NULL,
  `total_recipients` int(11) DEFAULT NULL,
  `status` int(10) unsigned NOT NULL,
  `created_by` int(11) DEFAULT NULL,
  `modified_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modified_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`cm_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Email Marketing'

CREATE TABLE `mst_marketing_campaigns_contacts` (
  `cc_id` int(11) NOT NULL AUTO_INCREMENT,
  `campaigns_id` int(11) DEFAULT NULL,
  `email_address` varchar(250) DEFAULT NULL,
  `is_sendable` tinyint(2) NOT NULL DEFAULT '1' COMMENT '(1 => Subscribe, 0 => Unsubscribe)',
  `first_name` varchar(250) DEFAULT NULL,
  `last_name` varchar(250) DEFAULT NULL,
  `address` text,
  `phone_number` varchar(250) DEFAULT NULL,
  `status` int(10) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `tracklink` text,
  `ip_address` varchar(255) DEFAULT NULL,
  `user_agent` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`cc_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Campaign Email List'

CREATE TABLE `trn_email_tracking_history` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `campaigns_id` bigint(20) unsigned NOT NULL,
  `cc_id` bigint(20) unsigned NOT NULL,
  `status` int(10) unsigned NOT NULL,
  `ip_address` varchar(255) NOT NULL,
  `user_agent` varchar(255) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

