-- 31-07-2021 
ALTER TABLE `sip` CHANGE `created_at` `created_at` DATE NOT NULL;
ALTER TABLE `jsf` CHANGE `created_at` `created_at` DATE NOT NULL;

-- 03-07-2021 
ALTER TABLE `invoice` ADD `region_id` INT NOT NULL AFTER `company_uuid`, ADD `created_at` DATE NOT NULL AFTER `region_id`, ADD `updated_at` DATETIME NOT NULL AFTER `created_at`, ADD `deleted_at` DATETIME NULL DEFAULT NULL AFTER `updated_at`;
ALTER TABLE `invoice` ADD `general_status_id` INT NOT NULL AFTER `deleted_at`;
ALTER TABLE `jsf` CHANGE `billing _company` `billing_company` VARCHAR(500) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL;

-- 05-08-2021 
ALTER TABLE `loa` ADD `region_id` INT NOT NULL AFTER `id`;
ALTER TABLE `loa` ADD `created_at` DATE NOT NULL AFTER `address`, ADD `updated_at` DATETIME NOT NULL AFTER `created_at`, ADD `deleted_at` DATETIME NULL DEFAULT NULL AFTER `updated_at`;
ALTER TABLE `loa` CHANGE `c_name` `c_name` VARCHAR(1000) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL;


-- 08-08-2021 --
ALTER TABLE `loa` ADD `data` LONGTEXT NOT NULL AFTER `region_id`;
ALTER TABLE `loa` CHANGE `data` `data` LONGTEXT CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL;

-- 10-08-2021 --
CREATE TABLE `token_sent_log` ( `id` int(11) NOT NULL AUTO_INCREMENT, `userName` varchar(150) COLLATE utf8_bin NOT NULL, `cas_no` varchar(250) COLLATE utf8_bin NOT NULL, `uuid` varchar(500) COLLATE utf8_bin NOT NULL, `entry_date` varchar(500) COLLATE utf8_bin NOT NULL, `operation` varchar(50) COLLATE utf8_bin NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

-- 12-08-2021 --
ALTER TABLE `admin_panel` CHANGE `status` `general_status_id` INT(11) NOT NULL DEFAULT '1';
-- ALTER TABLE `admin_panel` CHANGE `mail_status` `status` LONGTEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;
ALTER TABLE `admin_panel` CHANGE `updated_at` `updated_at` DATE NOT NULL;

-- 28-08-2021 
ALTER TABLE `email_log` ADD `created_at` DATE NOT NULL AFTER `status`, ADD `updated_at` DATETIME NOT NULL AFTER `created_at`;
ALTER TABLE `token_sent_log` ADD `created_at` DATE NOT NULL AFTER `operation`, ADD `updated_at` DATETIME NOT NULL AFTER `created_at`;

-- 30-08-2021 
ALTER TABLE `company_uuid` ADD `pdf_flag` VARCHAR(10) NOT NULL DEFAULT TRUE AFTER `invoice_pdf`;

-- 1-09-2021 
ALTER TABLE `users` CHANGE `allow` `allow` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'No';
CREATE TABLE `payment_received_log` (
  `id` int(11) NOT NULL,
  `userName` varchar(150) COLLATE utf8_bin NOT NULL,
  `cas_no` varchar(250) COLLATE utf8_bin NOT NULL,
  `uuid` varchar(500) COLLATE utf8_bin NOT NULL,
  `entry_date` varchar(500) COLLATE utf8_bin NOT NULL,
  `operation` varchar(50) COLLATE utf8_bin NOT NULL,
  `created_at` date NOT NULL,
  `updated_at` datetime NOT NULL,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE `payment_received_log` ADD PRIMARY KEY (`id`);


-- 03-09-2021 
ALTER TABLE `registered_substance` ADD `year` VARCHAR(50) NOT NULL AFTER `cas_no`, ADD `tonnage` VARCHAR(100) NOT NULL AFTER `year`, ADD `reg_no` VARCHAR(255) NOT NULL AFTER `tonnage`;

-- 27-09-2021 
INSERT INTO `roles` (`id`, `role`, `active_for_user`, `created_at`, `updated_at`, `deleted_at`) VALUES (NULL, 'Importer', '1', '2021-09-27', '2021-09-27 00:00:00', NULL);
INSERT INTO `roles` (`id`, `role`, `active_for_user`, `created_at`, `updated_at`, `deleted_at`) VALUES (NULL, 'EU - Manufacturer', '1', '2021-09-27', '2021-09-27 00:00:00', NULL);
INSERT INTO `roles` (`id`, `role`, `active_for_user`, `created_at`, `updated_at`, `deleted_at`) VALUES (NULL, 'non-EU Manufacturer', '1', '2021-09-27', '2021-09-27 00:00:00', NULL);
INSERT INTO `roles` (`id`, `role`, `active_for_user`, `created_at`, `updated_at`, `deleted_at`) VALUES (NULL, 'EU-trader', '1', '2021-09-27', '2021-09-27 00:00:00', NULL);
INSERT INTO `roles` (`id`, `role`, `active_for_user`, `created_at`, `updated_at`, `deleted_at`) VALUES (NULL, 'non-EU trader', '1', '2021-09-27', '2021-09-27 00:00:00', NULL);
-- 28-09-2021
ALTER TABLE `token_sent_log` ADD `region_id` INT NULL DEFAULT NULL AFTER `id`;
ALTER TABLE `email_log` ADD `region_id` INT NULL DEFAULT NULL AFTER `id`;
ALTER TABLE `payment_received_log` ADD `region_id` INT NULL DEFAULT NULL AFTER `id`;
ALTER TABLE `email_log` ADD `company_id` INT NOT NULL AFTER `region_id`;
ALTER TABLE `token_sent_log` ADD `company_id` INT NOT NULL AFTER `id`;
ALTER TABLE `payment_received_log` ADD `company_id` INT NOT NULL AFTER `id`;

-- 09-10-2021 
INSERT INTO `email_template` (`id`, `type`, `subject`, `message`, `created_at`, `updated_at`, `general_status_id`, `deleted_at`) VALUES
(16, 'user_credential', 'User Login Credential of GPC LR Portal', '<p>Dear {USERNAME}</p><br><p>Your Login Credential for GPC LR Portal is :</p>\r\n<p>Company Name:{USERNAME} / Email: {EMAIL}</p>\r\n<p>UUID: {UUID}</p>\r\n<p>Password: {PASSWORD}</p>\r\n<p>Reset: {RESET}</p>\r\n\r\n', '2021-10-09', '2021-10-09 00:00:00', 1, NULL);
CREATE TABLE `password_resets` (
  `id` int(11) NOT NULL,
  `uuid` varchar(255) NOT NULL,
  `reset_token` varchar(255) NOT NULL,
  `reset_time` varchar(255) NOT NULL,
  `created_at` date NOT NULL,
  `updated_at` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `password_resets`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `reset_token` (`reset_token`);
-- 21-10-2021 
ALTER TABLE `jsf` CHANGE `non_eu_signing_person` `non_eu_signing_person` VARCHAR(500) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL, CHANGE `non_eu_title` `non_eu_title` VARCHAR(500) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL;
-- 23-10-2021 
ALTER TABLE `request_for_update` CHANGE `other` `other` LONGTEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL;
-- 26-10-21
UPDATE `email_template` SET `message` = '\r\n\r\n<p>Dear {name},</p>\r\n<br><br>\r\n<p>\r\nYour OTP is {otp}. Use this Passcode to Login LR Portal.</p>\r\n\r\n<p>If you didn\'t request this, you can ignore this email or let us know.</p>\r\n\r\n<br><br><br>\r\n\r\nThanks & Regards\r\n\r\nIT Team' WHERE `email_template`.`id` = 12;
-- 27-10-21
UPDATE `email_template` SET `message` = '\r\n\r\n<p>Dear {name},</p>\r\n<br><br>\r\n<p>\r\nYour OTP is {otp}. Use this Passcode to Login LR Portal.</p>\r\n\r\n<p>If you didn\'t request this, you can ignore this email or let us know.</p>\r\n\r\n<br><br><br>\r\n\r\nThanks & Regards<br>\r\n\r\nIT Team' WHERE `email_template`.`id` = 12;
UPDATE `email_template` SET `message` = '<p style=\'font-family:calibri;\'>Dear {NAME},<br />\r\n Hello and Greetings!<br /><br />\r\n Thank you for the your interest in registering the substance with CAS No. {CAS}.<br /><br />\r\n We request you to kindly click on the given link to gain access to our online LR Portal and then follow the below given instructions to move ahead step-wise towards Final Registration of your substance: <br />\r\n Link to access online portal::::<br><a href=\'ssslr.reach-support.org\'>ssslr.reach-support.org</a><br /><br />\r\n <strong>Welcome to online GPC LR portal! </strong><br />\r\n Please follow the following procedure for getting the substance identification profile (SIP), Joint submission scope, SIEF agreement\r\n </p><p style=\'font-family:calibri;\'>\r\n<ul style=\'font-family:calibri;\'>\r\n <li>Please \'Register\' your company with company name and company UUID along with contact person details<br /></li>\r\n <li>You will receive th[...]';

-- 28-10
INSERT INTO `email_template` (`id`, `type`, `subject`, `message`, `created_at`, `updated_at`, `general_status_id`, `deleted_at`) VALUES (NULL, 'user_password_change_by_admin', 'Password Changed Successfully', '<p>Dear {USERNAME},</p>\r\n<P>Your password has changed successfully by <b>Admin</b>.</P>\r\n', '', '', '1', NULL);
INSERT INTO `email_template` (`id`, `type`, `subject`, `message`, `created_at`, `updated_at`, `general_status_id`, `deleted_at`) VALUES (NULL, 'inactive_user_password_change', 'User Password Changed ', '<p>You changed user password of uuid {UUID} </p>\r\n', '', '', '1', NULL);
-- 30-10
ALTER TABLE `sip_pdf` ADD `region_id` INT NOT NULL AFTER `id`;
ALTER TABLE `sip` CHANGE `check1` `check1` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL;
ALTER TABLE `sip_pdf` ADD `ec_no` VARCHAR(50) NOT NULL AFTER `cas_no`;
ALTER TABLE `sip_pdf` ADD `status` INT NOT NULL DEFAULT '1' AFTER `pdf`;

-- 01-11-2021
ALTER TABLE `company_uuid` ADD `scope` VARCHAR(50) NULL DEFAULT NULL AFTER `jsf_pdf`;
ALTER TABLE `jsf` CHANGE `address` `address` VARCHAR(1000) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL, CHANGE `code` `code` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL, CHANGE `city` `city` VARCHAR(500) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL, CHANGE `country` `country` VARCHAR(500) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL, CHANGE `contact_person` `contact_person` VARCHAR(500) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL, CHANGE `telephone` `telephone` VARCHAR(500) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL, CHANGE `vat` `vat` VARCHAR(500) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL, CHANGE `billing_company` `billing_company` VARCHAR(500) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL, CHANGE `sign` `sign` VARCHAR(500) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL, CHANGE `title` `title` VARCHAR(500) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL;

-- 16-11
ALTER TABLE `loa` CHANGE `ref_no` `ref_no` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL;
ALTER TABLE `loa` ADD UNIQUE(`ref_no`);
ALTER TABLE `loa` CHANGE `cas_name` `cas_name` VARCHAR(500) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL;

-- 17
ALTER TABLE `classification` CHANGE `cas_name` `cas_name` VARCHAR(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL;
ALTER TABLE `request_for_update` ADD `region_id` INT NULL DEFAULT NULL AFTER `company_id`;
-- 18
ALTER TABLE `companies`
  ADD PRIMARY KEY (`id`);
CREATE TABLE `companies` (
  `id` int(11) NOT NULL,
  `e_mail` varchar(50) NOT NULL,
  `company_name` varchar(50) NOT NULL,
  `contact_name` varchar(255) NOT NULL,
  `uuid` varchar(100) NOT NULL,
  `mobile_number` varchar(50) NOT NULL,
  `address` varchar(255) NOT NULL,
  `status` int(11) NOT NULL DEFAULT '1',
  `created_at` date NOT NULL,
  `updated_at` datetime NOT NULL,
  `deleted_at` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `companies`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
-- 19
  ALTER TABLE `companies` ADD UNIQUE(`uuid`);
-- 29-11-2021
CREATE TABLE `blacklists` (

  `id` int(11) NOT NULL,

  `ipaddress` varchar(255) NOT NULL,

  `attempt` varchar(255) NOT NULL,

  `status` int(11) NOT NULL,

  `created_at` datetime NOT NULL,

  `updated_at` datetime NOT NULL,

  `deleted_at` datetime DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1; 

ALTER TABLE `blacklists`

  ADD PRIMARY KEY (`id`);
-- 30-11-2021

CREATE TABLE `use_profile_survey` (
  `id` int(11) NOT NULL, `company_id` int(11) NOT NULL, `substance_name` varchar(255) NOT NULL, `cas_no` varchar(100) NOT NULL, `company_name` varchar(255) NOT NULL,  `buyer_name` varchar(100) NOT NULL, `created_at` date NOT NULL, `updated_at` datetime NOT NULL, `deleted_at` datetime DEFAULT NULL, `status` int(11) NOT NULL DEFAULT '1') ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `use_profile_survey` ADD PRIMARY KEY (`id`);
ALTER TABLE `use_profile_survey` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; 
CREATE TABLE `use_profile_survey_details` (
  `id` int(11) NOT NULL, `use_profile_survey_id` int(11) NOT NULL, `sector_use_id` int(11) NOT NULL, `sector_use_code` text NOT NULL,  `sector_remark` longtext, `product_category_use_code` text NOT NULL, `product_category_remark` longtext, `process_category_use_code` text NOT NULL, `process_category_remark` longtext, `sub_article_use_code` text NOT NULL, `sub_article_remark` longtext, `env_use_code` text NOT NULL, `env_remark` longtext, `tech_use_code` text NOT NULL, `tech_remark` longtext, `desc_use_code` text NOT NULL, `desc_remark` longtext, `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL, `deleted_at` datetime DEFAULT NULL, `status` int(11) NOT NULL DEFAULT '1') ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `use_profile_survey_details` ADD PRIMARY KEY(`id`);
ALTER TABLE `use_profile_survey_details` CHANGE `id` `id` INT(11) NOT NULL AUTO_INCREMENT;
-- 07-12-2021
ALTER TABLE `use_profile_survey` ADD COLUMN `region_id` INT NOT NULL AFTER `company_id`; 
ALTER TABLE `use_profile_survey` ADD COLUMN `uuid` VARCHAR(255) NULL AFTER `region_id`; 
-- 08-12-2021
ALTER TABLE `jsf` ADD COLUMN `tax_office` VARCHAR(255) NULL AFTER `vat`; 
ALTER TABLE `use_profile_survey` CHANGE `substance_name` `substance_name` VARCHAR(255) CHARSET latin1 COLLATE latin1_swedish_ci NULL; 
ALTER TABLE `use_profile_survey_details` CHANGE `sector_use_id` `sector_names_id` INT(11) NOT NULL; 
 ALTER TABLE `use_profile_survey_details` ADD COLUMN `otherusecodes` LONGTEXT NULL AFTER `desc_use_code`; 
ALTER TABLE `use_profile_survey_details` CHANGE `desc_use_code` `desc_use` TEXT CHARSET latin1 COLLATE latin1_swedish_ci NOT NULL; 
ALTER TABLE `use_profile_survey_details` CHANGE `desc_use` `desc_use` TEXT CHARSET latin1 COLLATE latin1_swedish_ci NULL; 

-- 13-12-2021
CREATE TABLE `manual_email_template` ( `id` INT NOT NULL AUTO_INCREMENT, `uuid` VARCHAR(255) NOT NULL, `region_id` INT, `cas_no` VARCHAR(255) NOT NULL, `to` VARCHAR(255) NOT NULL, `cc` VARCHAR(255), `content` LONGTEXT NOT NULL, `status` INT NOT NULL, `general_status` INT NOT NULL DEFAULT 1, `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL, `deleted_at` DATETIME, PRIMARY KEY (`id`) ); 
CREATE TABLE `manual_email_send` ( `id` INT NOT NULL AUTO_INCREMENT, `manual_email_template_id` INT NOT NULL, `email` VARCHAR(255) NOT NULL, `company_name` VARCHAR(255) NOT NULL, `uuid` VARCHAR(255) NOT NULL, `is_sent` INT NOT NULL COMMENT '1:Yes 0:NO', `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL, `deleted_at` DATETIME, PRIMARY KEY (`id`) ); 


INSERT INTO `general_manual_mail_status` (`name`, `status`, `created_at`, `updated_at`) VALUES ('draft', '1', '2021-12-13 15:43:03', '2021-12-13 15:43:09'); 
INSERT INTO `general_manual_mail_status` (`name`, `status`, `created_at`, `updated_at`) VALUES ('scheduled list', '1', '2021-12-13 15:43:33', '2021-12-13 15:43:37'); 
INSERT INTO `general_manual_mail_status` (`name`, `status`, `created_at`) VALUES ('scheduled mail', '1', '2021-12-13 15:44:03'); 
UPDATE `general_manual_mail_status` SET `updated_at` = '2021-12-13 15:44:06' WHERE `id` = '3'; 
INSERT INTO `general_manual_mail_status` (`name`, `status`, `created_at`, `updated_at`) VALUES ('completed', '1', '2021-12-13 15:44:20', '2021-12-13 15:44:23'); 
INSERT INTO `general_manual_mail_status` (`name`, `status`, `created_at`, `updated_at`) VALUES ('deleted', '1', '2021-12-13 15:44:38', '2021-12-13 15:44:40'); 

-- 14-12
ALTER TABLE `manual_email_template` DROP COLUMN `to`; 

--29-09-2023 db changes by Manisha –

INSERT INTO `ssslr`.`roles`(`id`, `role`, `active_for_user`, `created_at`, `updated_at`, `deleted_at`) VALUES (9, 'Cost-Viewer', 0, '2023-09-27', '2023-09-27 13:49:47', NULL);
INSERT INTO `ssslr`.`admins`(`id`, `role_id`, `name`, `email`, `password`, `token_2fa`, `token_2fa_expiry`, `remember_token`, `status`, `created_at`, `updated_at`) VALUES (7, 9, 'Barbara', 'barbara@kr.gpcregulatory.com', '$2y$10$Z0OeeV5w3Y5pKtjeHSC6hOU0diHoXjCS1EywijBtL7CBO0PP18JDO', '219295', NULL, NULL, 1, NULL, '2023-09-29 06:21:38');
