
/**
* 22-01-2022
**/

ALTER TABLE `tbl_users` CHANGE `first_name` `first_name` VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL;
ALTER TABLE `tbl_users` CHANGE `last_name` `last_name` VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL;
ALTER TABLE `tbl_users` CHANGE `email_id` `email_id` VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL;
ALTER TABLE `tbl_users` CHANGE `mobile_number` `mobile_number` BIGINT(12) NULL;
ALTER TABLE `tbl_users` CHANGE `mobile_number_2` `mobile_number_2` BIGINT(20) NULL;
ALTER TABLE `tbl_users` CHANGE `address` `address` VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL;
ALTER TABLE `tbl_users` CHANGE `state_id` `state_id` INT(5) NULL;
ALTER TABLE `tbl_users` CHANGE `city_id` `city_id` INT(8) NULL;
ALTER TABLE `tbl_users` CHANGE `is_deleted` `is_deleted` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '0 = Not Deleted, 1 = Deleted';
ALTER TABLE `tbl_user_installation_info` CHANGE `installation_date` `installation_date` DATE NULL;
ALTER TABLE `tbl_user_installation_info` CHANGE `site_name` `site_name` VARCHAR(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL;
ALTER TABLE `tbl_user_controller_motor_info` CHANGE `controller_capacity` `controller_capacity` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL;
ALTER TABLE `tbl_user_controller_motor_info` CHANGE `controller_type` `controller_type` INT(2) NULL COMMENT '1 = AC, 2 = DC';

ALTER TABLE `tbl_user_device_info` ADD `mqtt_broker_url` VARCHAR(255) NOT NULL AFTER `imei_number`, ADD `mqtt_broker_topic` VARCHAR(255) NOT NULL AFTER `mqtt_broker_url`, ADD `mqtt_broker_username` VARCHAR(255) NOT NULL AFTER `mqtt_broker_topic`, ADD `mqtt_broker_password` VARCHAR(255) NOT NULL AFTER `mqtt_broker_username`;

/**
* 25-01-2022
**/
ALTER TABLE `tbl_user_device_info` ADD `pem_file` VARCHAR(255) NOT NULL AFTER `mqtt_broker_password`, ADD `pem_file_path` VARCHAR(255) NOT NULL AFTER `pemf_file`;

/**
* 28-01-2022
**/
ALTER TABLE `tbl_user_device_info` ADD `mqtt_broker_port` INT(5) NOT NULL AFTER `mqtt_broker_url`;

/**
* 25-02-2022
**/
ALTER TABLE `tbl_user_device_info` ADD `data_interval` INT(11) NULL AFTER `log_interval`, ADD `heartbeat_interval` INT(11) NULL AFTER `data_interval`;
ALTER TABLE `tbl_user_device_info` ADD `is_restart` BOOLEAN NOT NULL DEFAULT FALSE AFTER `ip_address`, ADD `is_device_logs_needed` BOOLEAN NOT NULL DEFAULT FALSE AFTER `is_restart`;
ALTER TABLE `tbl_user_device_info` CHANGE `is_restart` `is_restart` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '1 = Yes, 0 = No';
ALTER TABLE `tbl_user_device_info` CHANGE `is_device_logs_needed` `is_device_logs_needed` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '1 = Yes, 0 = No';

ALTER TABLE `tbl_user_device_info` DROP `pem_file`, DROP `pem_file_path`;

/**
* 27-02-2022
**/

ALTER TABLE `tbl_users` ADD `distributor_code` VARCHAR(155) NULL AFTER `pincode`;

/**
* 28-02-2022
**/
ALTER TABLE `tbl_device_upgrades` ADD CONSTRAINT `fk_device_upgrade_user_id` FOREIGN KEY (`user_id`) REFERENCES `tbl_users`(`user_id`) ON DELETE CASCADE ON UPDATE NO ACTION;

INSERT INTO `tbl_user_types` (`user_type_id`, `user_type_name`) VALUES ('6', 'Site Engineer');