CREATE TABLE IF NOT EXISTS `sms_settings`(
    `setting_name` VARCHAR(100) COLLATE utf8_unicode_ci NOT NULL,
    `setting_value` MEDIUMTEXT COLLATE utf8_unicode_ci NOT NULL,
    `type` VARCHAR(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'app',
    `deleted` TINYINT(1) NOT NULL DEFAULT '0',
    UNIQUE KEY `setting_name`(`setting_name`)
) ENGINE = INNODB DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_ci; --#


CREATE TABLE IF NOT EXISTS `sms_notification_settings`(
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `event` VARCHAR(250) NOT NULL,
    `category` VARCHAR(50) NOT NULL,
    `enable_sms` INT(1) NOT NULL DEFAULT '0',
    `sort` INT(11) NOT NULL,
    `deleted` INT(1) NOT NULL DEFAULT '0',
    PRIMARY KEY(`id`),
    KEY `event`(`event`)
) ENGINE = INNODB DEFAULT CHARSET = latin1 AUTO_INCREMENT = 1; --#


INSERT INTO `sms_notification_settings` (`id`, `event`, `category`, `enable_sms`, `sort`, `deleted`) VALUES
(1, 'project_created', 'project', 0, 1, 0),
(2, 'project_deleted', 'project', 0, 2, 0),
(3, 'project_task_created', 'project', 0, 3, 0),
(4, 'project_task_updated', 'project', 0, 4, 0),
(5, 'project_task_assigned', 'project', 0, 5, 0),
(7, 'project_task_started', 'project', 0, 7, 0),
(8, 'project_task_finished', 'project', 0, 8, 0),
(9, 'project_task_reopened', 'project', 0, 9, 0),
(10, 'project_task_deleted', 'project', 0, 10, 0),
(11, 'project_task_commented', 'project', 0, 11, 0),
(12, 'project_member_added', 'project', 0, 12, 0),
(13, 'project_member_deleted', 'project', 0, 13, 0),
(14, 'project_file_added', 'project', 0, 14, 0),
(15, 'project_file_deleted', 'project', 0, 15, 0),
(16, 'project_file_commented', 'project', 0, 16, 0),
(17, 'project_comment_added', 'project', 0, 17, 0),
(18, 'project_comment_replied', 'project', 0, 18, 0),
(19, 'project_customer_feedback_added', 'project', 0, 19, 0),
(20, 'project_customer_feedback_replied', 'project', 0, 20, 0),
(21, 'client_signup', 'client', 0, 21, 0),
(22, 'invoice_online_payment_received', 'invoice', 0, 22, 0),
(23, 'leave_application_submitted', 'leave', 0, 23, 0),
(24, 'leave_approved', 'leave', 0, 24, 0),
(25, 'leave_assigned', 'leave', 0, 25, 0),
(26, 'leave_rejected', 'leave', 0, 26, 0),
(27, 'leave_canceled', 'leave', 0, 27, 0),
(28, 'ticket_created', 'ticket', 0, 28, 0),
(29, 'ticket_commented', 'ticket', 0, 29, 0),
(30, 'ticket_closed', 'ticket', 0, 30, 0),
(31, 'ticket_reopened', 'ticket', 0, 31, 0),
(32, 'estimate_request_received', 'estimate', 0, 32, 0),
(34, 'estimate_accepted', 'estimate', 0, 34, 0),
(35, 'estimate_rejected', 'estimate', 0, 35, 0),
(36, 'new_message_sent', 'message', 0, 36, 0),
(37, 'message_reply_sent', 'message', 0, 37, 0),
(38, 'invoice_payment_confirmation', 'invoice', 0, 22, 0),
(39, 'new_event_added_in_calendar', 'event', 0, 39, 0),
(40, 'recurring_invoice_created_vai_cron_job', 'invoice', 0, 22, 0),
(41, 'new_announcement_created', 'announcement', 0, 41, 0),
(42, 'invoice_due_reminder_before_due_date', 'invoice', 0, 22, 0),
(43, 'invoice_overdue_reminder', 'invoice', 0, 22, 0),
(44, 'recurring_invoice_creation_reminder', 'invoice', 0, 22, 0),
(45, 'project_completed', 'project', 0, 2, 0),
(46, 'lead_created', 'lead', 0, 21, 0),
(47, 'client_created_from_lead', 'lead', 0, 21, 0),
(48, 'project_task_deadline_pre_reminder', 'project', 0, 20, 0),
(49, 'project_task_reminder_on_the_day_of_deadline', 'project', 0, 20, 0),
(50, 'project_task_deadline_overdue_reminder', 'project', 0, 20, 0),
(51, 'recurring_task_created_via_cron_job', 'project', 0, 20, 0),
(52, 'calendar_event_modified', 'event', 0, 39, 0),
(53, 'client_contact_requested_account_removal', 'client', 0, 21, 0),
(54, 'bitbucket_push_received', 'project', 0, 45, 0),
(55, 'github_push_received', 'project', 0, 45, 0),
(56, 'invited_client_contact_signed_up', 'client', 0, 21, 0),
(57, 'created_a_new_post', 'timeline', 0, 52, 0),
(58, 'timeline_post_commented', 'timeline', 0, 52, 0),
(59, 'ticket_assigned', 'ticket', 0, 31, 0),
(60, 'new_order_received', 'order', 0, 1, 0),
(61, 'order_status_updated', 'order', 0, 2, 0); --#

CREATE TABLE IF NOT EXISTS `sms_templates` (
  `id` int(11) NOT NULL,
  `template_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `default_message` mediumtext CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `custom_message` mediumtext CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `deleted` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8; --#

INSERT INTO `sms_templates` (`id`, `template_name`, `default_message`, `custom_message`, `deleted`) VALUES
(1, 'general_notification', '{EVENT_TITLE}\r\n{EVENT_DETAILS}\r\n{NOTIFICATION_URL}', NULL, 0),
(2, 'login_info', 'Hello {USER_FIRST_NAME},  {USER_LAST_NAME},\r\nAn account has been created for you.\r\nPlease use the following info to login your dashboard:\r\nDashboard URL: {DASHBOARD_URL}\r\n\r\nEmail: {USER_LOGIN_EMAIL}\r\nPassword: {USER_LOGIN_PASSWORD}\r\n\r\n{SIGNATURE}', NULL, 0),
(4, 'reset_password', 'Hello {ACCOUNT_HOLDER_NAME},\r\nA password reset request has been created for your account. \r\nTo initiate the password reset process, please click on the following link:\r\n{RESET_PASSWORD_URL}\r\n\r\nIf you have received this mail in error, it is likely that another user entered your email address by mistake while trying to reset a password.\r\nIf you did not initiate the request, you do not need to take any further action and can safely disregard this email.\r\n\r\n{SIGNATURE}', NULL, 0),
(5, 'team_member_invitation', 'Hello,\r\n{INVITATION_SENT_BY} has sent you an invitation to join with a team.\r\n{INVITATION_URL}\r\nIf you do not want to accept this invitation, simply ignore this email.\r\n\r\n{SIGNATURE}', NULL, 0),
(6, 'new_client_greetings', 'Hello {CONTACT_FIRST_NAME},\r\nThank you for creating your account.\r\nWe are happy to see you here.\r\nDashboard URL: {DASHBOARD_URL}\r\n\r\nEmail: {CONTACT_LOGIN_EMAIL}\r\nPassword: {CONTACT_LOGIN_PASSWORD}\r\n\r\n{SIGNATURE}', NULL, 0),
(7, 'client_contact_invitation', 'Hello,\r\n{INVITATION_SENT_BY} has sent you an invitation to a client portal.\r\n{INVITATION_URL}\r\nIf you do not want to accept this invitation, simply ignore this email.\r\n\r\n{SIGNATURE}', 'Hello,\r\n{INVITATION_SENT_BY} has sent you an invitation to a client portal.\r\n{INVITATION_URL}\r\nIf you do not want to accept this invitation, simply ignore this email.\r\n\r\n{SIGNATURE}', 0),
(8, 'verify_email', 'To initiate the signup process, please click on the following link:\r\n{VERIFY_EMAIL_URL}\r\n{SIGNATURE}', NULL, 0),
(9, 'project_task_deadline_reminder', 'Hello,\r\nThis is to remind you that there are some tasks which deadline is {DEADLINE}.\r\n\r\n{TASKS_LIST}\r\n\r\n{SIGNATURE}', NULL, 0),
(10, 'send_invoice', 'Hello {CONTACT_FIRST_NAME},\r\nThank you for your business cooperation.\r\nYour invoice for the project {PROJECT_TITLE} has been generated and is attached here.\r\nINVOICE #{INVOICE_ID}\r\n{INVOICE_URL}\r\n\r\nInvoice balance due is {BALANCE_DUE}\r\nPlease pay this invoice within {DUE_DATE}. \r\n\r\n{PUBLIC_PAY_INVOICE_URL}\r\n\r\n{SIGNATURE}', NULL, 0),
(11, 'invoice_payment_confirmation', 'Hello,\r\nWe have received your payment of {PAYMENT_AMOUNT} for {INVOICE_ID}\r\nThank you for your business cooperation.\r\n{INVOICE_URL}\r\n\r\n{SIGNATURE}', NULL, 0),
(12, 'invoice_due_reminder_before_due_date', 'Hello,\r\nWe would like to remind you that invoice {INVOICE_ID} is due on {DUE_DATE}. Please pay the invoice within due date. \r\n\r\nIf you have already submitted the payment, please ignore this email.\r\n{INVOICE_URL}\r\n\r\n{SIGNATURE}', NULL, 0),
(13, 'invoice_overdue_reminder', 'Hello,\r\nWe would like to remind you that you have an unpaid invoice {INVOICE_ID}. We kindly request you to pay the invoice as soon as possible. \r\n\r\nIf you have already submitted the payment, please ignore this email.\r\n{INVOICE_URL}\r\n\r\n{SIGNATURE}', NULL, 0),
(14, 'recurring_invoice_creation_reminder', 'Hello,\r\nWe would like to remind you that a recurring invoice will be created on {NEXT_RECURRING_DATE}.\r\n{INVOICE_URL}\r\n\r\n{SIGNATURE}', NULL, 0),
(15, 'estimate_sent', 'Hello {CONTACT_FIRST_NAME},\r\nHere is the estimate. Please check the attachment.\r\nESTIMATE #{ESTIMATE_ID} : {ESTIMATE_URL}\r\n\r\n{SIGNATURE}', NULL, 0),
(16, 'estimate_accepted', 'The estimate #{ESTIMATE_ID} has been accepted.\r\n{ESTIMATE_URL}\r\n\r\n{SIGNATURE}', NULL, 0),
(17, 'estimate_rejected', 'The estimate #{ESTIMATE_ID} has been rejected.\r\n{ESTIMATE_URL}\r\n\r\n{SIGNATURE}', NULL, 0),
(18, 'estimate_request_received', 'A new estimate request has been received from {CONTACT_FIRST_NAME}.\r\nESTIMATE REQUEST #{ESTIMATE_REQUEST_ID} :  {ESTIMATE_REQUEST_URL}\r\n\r\n{SIGNATURE}', NULL, 0),
(19, 'new_order_received', 'A new order has been received from {CONTACT_FIRST_NAME} and is attached here.\r\nORDER #{ORDER_ID}: {ORDER_URL}\r\n\r\n{SIGNATURE}', NULL, 0),
(20, 'order_status_updated', 'Hello {CONTACT_FIRST_NAME},\r\nThank you for your business cooperation.\r\nYour order has been updated and is attached here.\r\nORDER #{ORDER_ID}: {ORDER_URL}\r\n\r\n{SIGNATURE}', NULL, 0),
(21, 'ticket_created', 'Ticket #{TICKET_ID} Opened\r\nTitle: {TICKET_TITLE}\r\n{TICKET_CONTENT}\r\n{TICKET_URL}\r\n\r\nRegards,\r\n{USER_NAME}', 'Ticket #{TICKET_ID} Opened\r\nTitle: {TICKET_TITLE}\r\nComment: {TICKET_CONTENT}\r\n{TICKET_URL}\r\n\r\nRegards,\r\n{USER_NAME}', 0),
(22, 'ticket_commented', 'Ticket #{TICKET_ID} Replies\r\nTitle: {TICKET_TITLE}\r\n{TICKET_CONTENT}\r\n{TICKET_URL}', 'Ticket #{TICKET_ID} Replies\r\nTitle: {TICKET_TITLE}\r\nComment: {TICKET_CONTENT}\r\n{TICKET_URL}', 0),
(23, 'ticket_closed', 'The Ticket #{TICKET_ID} has been closed by {USER_NAME}\r\n{TICKET_URL}', NULL, 0),
(24, 'ticket_reopened', 'The Ticket #{TICKET_ID} has been reopened by {USER_NAME}\r\n{TICKET_URL}', NULL, 0),
(25, 'message_received', '{USER_NAME} sent you a message.\r\n{MESSAGE_CONTENT}\r\n{MESSAGE_URL}', NULL, 0), 
(26, 'signature', 'Powered By: SketchCode', NULL, 0); --#

CREATE TABLE IF NOT EXISTS `sms_notification_logs`(
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `message` LONGTEXT COLLATE utf8_unicode_ci NOT NULL,
    `created_at` DATETIME NOT NULL,
    `notify_to` MEDIUMTEXT COLLATE utf8_unicode_ci NOT NULL,
    `event` VARCHAR(250) COLLATE utf8_unicode_ci NOT NULL,
    `category` VARCHAR(250) COLLATE utf8_unicode_ci NOT NULL,
    `deleted` INT(1) NOT NULL DEFAULT '0',
    PRIMARY KEY(`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_ci AUTO_INCREMENT = 1;
