ALTER TABLE `smartrisk_dev`.`mst_client`
ADD COLUMN `add_on_subscription` varchar(50) NULL DEFAULT NULL COMMENT '' AFTER `phone`;

ALTER TABLE `client_supplier_country_category_mapping` ADD `mapped_user_emails` VARCHAR(2000) NULL DEFAULT NULL AFTER `BVD_ID`;
ALTER TABLE `client_supplier_country_category_mapping` ADD `is_pull` INT NOT NULL DEFAULT '0' AFTER `is_active`;

ALTER TABLE `user_category_access` ADD `sourcing_country_id` INT NOT NULL AFTER `client_supplier_id`;
ALTER TABLE `user_category_access_history` ADD `sourcing_country_id` INT NOT NULL AFTER `client_supplier_id`;

ALTER TABLE `user_category_access` ADD `is_pull` INT NOT NULL DEFAULT '0' AFTER `sourcing_country_id`;
ALTER TABLE `user_category_access_history` ADD `is_pull` INT NOT NULL DEFAULT '0' AFTER `sourcing_country_id`;

DROP TABLE IF EXISTS `user_subscription_category`;
CREATE TABLE `user_subscription_category` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `user_id` INT UNSIGNED NOT NULL ,
  `client_id` INT UNSIGNED NOT NULL ,
  `client_category_id` INT UNSIGNED NOT NULL ,
  `frequency` INT NOT NULL DEFAULT 0 ,
  `risk_rating` VARCHAR(11) NOT NULL ,
  `is_active` BIT(1) NOT NULL DEFAULT b'1' ,
  `created_by` INT NOT NULL ,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  `updated_by` INT NULL ,
  `updated_at` TIMESTAMP NULL ,
  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `logistics_news`;
CREATE TABLE `logistics_news` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `client_id` int(10) unsigned NOT NULL,
  `news_type` varchar(25) NOT NULL,
  `mode` int(11) NOT NULL,
  `region`int(11) NOT NULL,
  `risk_score` int(11) NOT NULL,

  `news_date` date NOT NULL,
  `news_title` varchar(2000) NOT NULL,
  `content` varchar(8000) NOT NULL,
  `link` varchar(8000) NOT NULL,
  `send_risk_alert` tinyint(1) NOT NULL DEFAULT 1,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,

  `created_by` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int(11) DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
)  ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `mst_logistic_news_mode`;
CREATE TABLE `mst_logistic_news_mode` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `mode` varchar(100) NOT NULL,
  `ordering` int(11) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `mst_logistic_news_mode` (`mode`) VALUES ('Road');
INSERT INTO `mst_logistic_news_mode` (`mode`) VALUES ('Rail');
INSERT INTO `mst_logistic_news_mode` (`mode`) VALUES ('Air');
INSERT INTO `mst_logistic_news_mode` (`mode`) VALUES ('Ocean');
INSERT INTO `mst_logistic_news_mode` (`mode`) VALUES ('Warehousing');
INSERT INTO `mst_logistic_news_mode` (`mode`) VALUES ('Intermodal');
INSERT INTO `mst_logistic_news_mode` (`mode`) VALUES ('3PL/4PL');




DROP TABLE IF EXISTS `category_news`;
CREATE TABLE `category_news` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `client_id` int(10) unsigned NOT NULL DEFAULT 0,
  `news_type` varchar(25) NOT NULL,
  `client_category_id` int(10) unsigned NULL,
  `tsc_category_id` int(10) unsigned NULL,

  `news_date` date NOT NULL,
  `news_title` varchar(2000) NOT NULL,
  `content` varchar(8000) NULL,
  `link` varchar(8000) NOT NULL,
  `risk_id` int(11) NOT NULL,
  `sub_risk_id` int(11) NOT NULL,

  `theme_id` int(11) NOT NULL,
  `topic` varchar(2000) NOT NULL,
  `score` int(11) NOT NULL,
  `send_alert` tinyint(1) NOT NULL DEFAULT 1,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,

  `created_by` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int(11) DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
)  ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `user_subscription_logistic`;
CREATE TABLE `user_subscription_logistic` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `client_id` int(10)  NOT NULL DEFAULT '0',
  `mode` int(11)  NOT NULL,
  `region` int(11)  NOT NULL,
  `frequency` int(11) NOT NULL DEFAULT '0',
  `risk_rating` varchar(11) NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_by` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int(11) DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `mst_theme`;
CREATE TABLE `mst_theme` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `theme` varchar(100) NOT NULL,
  `ordering` int(11) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `mst_theme` (`theme`) VALUES ('Material price & supply');
INSERT INTO `mst_theme` (`theme`) VALUES ('Labour rate & supply');
INSERT INTO `mst_theme` (`theme`) VALUES ('Trade events');
INSERT INTO `mst_theme` (`theme`) VALUES ('Supplier operations');
INSERT INTO `mst_theme` (`theme`) VALUES ('Market competition');
INSERT INTO `mst_theme` (`theme`) VALUES ('Sustainability');
INSERT INTO `mst_theme` (`theme`) VALUES ('End-markets');
INSERT INTO `mst_theme` (`theme`) VALUES ('Others');
