--
-- Menu
--
INSERT INTO `sps_menu` (`menu_item`, `menu_url`, `menu_class`, `parent_menu_id`,
    `action`, `order`, `status`, `location_id`, `area_id`, `created_by`, `created_at`, `updated_by`)
    VALUES ('alert_messages_configuration', 'admin/alert_msg_configuration', 'menu', '11', '1,2', '2.11', '1', '0', '0', '0', 'CURRENT_TIMESTAMP', '0');

--
-- Menu action mapping
--
INSERT INTO  `sps_menu_action_mapping`
    (`menu_id`, `action_id`, `area_id`, `location_id`, `created_by`, `updated_by`, `created_at`, `deleted_at`)
    VALUES ((SELECT id FROM sps_menu WHERE sps_menu.menu_item = "alert_messages_configuration"), '1', '0', '0', '0', '0', 'CURRENT_TIMESTAMP',  NULL);

INSERT INTO  `sps_menu_action_mapping`
    (`menu_id`, `action_id`, `area_id`, `location_id`, `created_by`, `updated_by`, `created_at`, `deleted_at`)
    VALUES ((SELECT id FROM sps_menu WHERE sps_menu.menu_item = "alert_messages_configuration"), '3', '0', '0', '0', '0', 'CURRENT_TIMESTAMP',  NULL);

--
-- Table structure for table `sps_tariff_list`
--
CREATE TABLE `sps_tariff_list` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tariff_name` varchar(150) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `sps_reports_list`
--
CREATE TABLE `sps_reports_list` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `report_name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data in table `sps_tariff_list`
--
INSERT INTO `sps_tariff_list` (`tariff_name`) VALUES('casual_tariff'),('overnight_tariff'),('additional_tariff');

--
-- Dumping data in table `sps_reports_list`
--
INSERT INTO `sps_reports_list` (report_name) 
SELECT `menu_item`
FROM `sps_menu` WHERE `parent_menu_id` = (SELECT `id` FROM `sps_menu` WHERE `menu_item` = 'reports') 
    AND `menu_item` NOT IN ('configure_statistic_list', 'overnight_vehicle','tariff_report') AND `status` = '1'; 

--
-- Table structure for table `sps_alert_msg_config`
--
CREATE TABLE `sps_alert_msg_config` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `reference_id` INT(11) NOT NULL,
  `on_email` ENUM('0','1') NOT NULL DEFAULT '0',
  `on_sms` ENUM('0','1') NOT NULL DEFAULT '0',
  `screen_type` VARCHAR(50) NOT NULL,
  `created_by` VARCHAR(50) DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `reference_id` (`reference_id`),
  KEY `screen_type` (`screen_type`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

--
-- Table structure for table `sps_alert_msg_config_user`
--
CREATE TABLE `sps_alert_msg_config_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `config_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `config_id` (`config_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `email_log`
--
CREATE TABLE `sps_email_log` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `date` datetime NOT NULL,
  `to` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `from` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `cc` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `bcc` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `subject` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `body` longtext COLLATE utf8_unicode_ci NOT NULL,
  `email_status` enum('0','1') COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `attachment_path` longtext COLLATE utf8_unicode_ci,
  `remark` longtext COLLATE utf8_unicode_ci,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

/*sps_access_rights table query*/

INSERT INTO `sps_access_rights`
	(`id`, `role_id`, `menu_id`, `action_id`, `created_by`, `updated_at`, `deleted_at`, `location_id`, `area_id`, `updated_by`, `created_at`)
	VALUES ('0', '1', (SELECT id FROM sps_menu WHERE sps_menu.menu_item = "alert_messages_configuration"), '1', '0', CURRENT_TIMESTAMP, NULL, '0', '0', '0', CURRENT_TIMESTAMP);

INSERT INTO `sps_access_rights`
	(`id`, `role_id`, `menu_id`, `action_id`, `created_by`, `updated_at`, `deleted_at`, `location_id`, `area_id`, `updated_by`, `created_at`)
	VALUES ('0', '1', (SELECT id FROM sps_menu WHERE sps_menu.menu_item = "alert_messages_configuration"), '3', '0', CURRENT_TIMESTAMP, NULL, '0', '0', '0', CURRENT_TIMESTAMP);
