#change DB Structure Productline
ALTER TABLE `bank_accounts`
	CHANGE COLUMN `account_number` `account_number` VARCHAR(255) NULL DEFAULT NULL;
    
    

  

#for aft
CREATE TABLE IF NOT EXISTS `bank_client_info` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `bank_id` int(11) unsigned DEFAULT 0,
  `bank_client_id` int(11) DEFAULT NULL,
  `bank_client_name` varchar(200) DEFAULT NULL,
  `bankLogo` varchar(200) DEFAULT NULL,
  `status` enum('1','0') DEFAULT '1',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=UTF8MB4;




ALTER TABLE module_level_policy MODIFY COLUMN value TEXT;








#company setup crf
ALTER TABLE `geo_groups_countries`
	ADD COLUMN `geo_graphical_setup_detail_id` INT(11) UNSIGNED NULL DEFAULT NULL AFTER `geo_group_id`;
DROP TABLE If EXISTS geo_graphical_setup_details;
CREATE TABLE geo_graphical_setup_details (
    id INT AUTO_INCREMENT PRIMARY KEY,
    market_id INT NULL,
    cluster_id INT NULL,
    sub_cluster_id INT NULL,
    `status` INT NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
ALTER TABLE `geo_graphical_setup_details`
	ADD COLUMN `geo_graphical_setup_level` INT(11) NULL DEFAULT NULL AFTER `sub_cluster_id`;







#uet vacant by employee
ALTER TABLE `proll_employee` ADD COLUMN `vacant_position_created_against` INT NULL AFTER `is_employee`;

#alter dms table
ALTER TABLE dms_document_files ADD COLUMN 	`is_active` TINYINT(3) NULL DEFAULT '1';


ALTER TABLE `proll_employee` CHANGE COLUMN `job_status` `job_status` INT(10) UNSIGNED NULL DEFAULT NULL AFTER `status`;


#contract details
ALTER TABLE `proll_employee_contracts` ADD COLUMN `contract_type` INT NULL, ADD COLUMN `contract_milestone` INT NULL, ADD COLUMN `milestone_value` INT NULL,
ADD COLUMN `frequency_rate` INT NULL, ADD COLUMN `milestone_timeline` INT NULL, ADD COLUMN `contract_period` VARCHAR(50) NULL DEFAULT NULL,
ADD COLUMN `extension_no` VARCHAR(50) NULL DEFAULT NULL;

ALTER TABLE `proll_employee_contracts`
	CHANGE COLUMN `contract_milestone` `contract_milestone` VARCHAR(50) NULL DEFAULT NULL,
	CHANGE COLUMN `milestone_timeline` `milestone_timeline` DATE NULL DEFAULT NULL;




#Alter Table Payroll Emf Change and payroll process UAT-06-0094
ALTER TABLE `cb_payroll_emf_change`
ADD COLUMN `taxable_income` FLOAT NULL DEFAULT NULL
AFTER `increment_amount`;

ALTER TABLE `cb_payroll_emf_change`
ADD COLUMN `non_taxable_income` FLOAT NULL DEFAULT NULL
AFTER `taxable_income`;

ALTER TABLE `cb_payroll_process` ADD COLUMN `comments` VARCHAR(50) NULL DEFAULT NULL;


# for taxable certrifcate uploader CRF-07-0113
ALTER TABLE `cb_salary_details`
ADD COLUMN `total_taxable_allowance_amount` FLOAT NULL DEFAULT NULL AFTER `deleted_at`,
ADD COLUMN `total_tax_deducted_amount` FLOAT NULL DEFAULT NULL AFTER `total_taxable_allowance_amount`;



#UAT 0108

DROP TABLE IF EXISTS `employee_details_log`;
CREATE TABLE IF NOT EXISTS `employee_details_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `emp_id` int(11) DEFAULT NULL,
  `band_id` int(11) DEFAULT NULL,
  `department_id` int(11) DEFAULT NULL,
  `designation_id` int(11) DEFAULT NULL,
  `department_lm_id` int(11) DEFAULT NULL,
  `employement_status_id` int(11) DEFAULT NULL,
  `employee_status_id` int(11) DEFAULT NULL,
  `department_joining_date` date DEFAULT NULL,
  `sub_department_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `shift_id` int(11) DEFAULT NULL,
  `external_lm1_id` int(11) DEFAULT NULL,
  `external_lm2_id` int(11) DEFAULT NULL,
  `mobile_attendance` tinyint(2) DEFAULT 0,
  `remote_attendance` tinyint(2) DEFAULT 0,
  `probation_period` varchar(255) DEFAULT NULL,
  `probation_completion_date` date DEFAULT NULL,
  `doj` date DEFAULT NULL,
  `orignal_doj` date DEFAULT NULL,
  `last_working_date` date DEFAULT NULL,
  `dor` date DEFAULT NULL,
  `currency_id` int(11) DEFAULT NULL,
  `gross_annual_salary` float DEFAULT NULL,
  `gross_monthly_salary` float DEFAULT NULL,
  `source` varchar(255) DEFAULT NULL,
  `eobi_applicable` tinyint(11) DEFAULT 0,
  `social_security_applicable` tinyint(11) DEFAULT 0,
  `appraisal_rating_prior_year` varchar(255) DEFAULT NULL,
  `appraisal_rating_last_year` varchar(255) DEFAULT NULL,
  `last_promotion_date` date DEFAULT NULL,
   `created_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
	`updated_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
	`deleted_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
	`created_by` INT(10) NULL DEFAULT NULL,
	`updated_by` INT(10) NULL DEFAULT NULL,
	`deleted_by` INT(10) NULL DEFAULT NULL,
	`inactive_marked_at` DATE NULL DEFAULT NULL,
	`reason_for_leaving` INT(10) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
DROP TABLE IF EXISTS `employee_perfomance_log`;
CREATE TABLE IF NOT EXISTS `employee_perfomance_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(255) DEFAULT NULL,
  `emp_id` int(11) DEFAULT NULL,
  `amount` float DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
DROP TABLE IF EXISTS `employee_person_details_log`;
CREATE TABLE IF NOT EXISTS `employee_person_details_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `emp_id` int(11) DEFAULT NULL,
  `official_email` varchar(255) DEFAULT NULL,
  `official_phone_no` varchar(255) DEFAULT NULL,
  `third_party_id` varchar(255) DEFAULT NULL,
  `is_current_address` tinyint(2) DEFAULT 0,
  `current_email` varchar(50) DEFAULT NULL,
  `current_mobile_number` varchar(20) DEFAULT NULL,
  `current_skype_name` varchar(30) DEFAULT NULL,
  `current_emergency_contact_person_name` varchar(30) DEFAULT NULL,
  `current_emergency_contact_number` varchar(30) DEFAULT NULL,
  `current_address` varchar(250) DEFAULT NULL,
  `current_telephone_no` varchar(20) DEFAULT NULL,
  `current_zip_code` varchar(10) DEFAULT NULL,
  `is_permanent_address` tinyint(2) DEFAULT 0,
  `permanent_email` varchar(50) DEFAULT NULL,
  `permanent_mobile_number` varchar(20) DEFAULT NULL,
  `permanent_skype_name` varchar(30) DEFAULT NULL,
  `permanent_emergency_contact_person_name` varchar(30) DEFAULT NULL,
  `permanent_emergency_contact_number` varchar(30) DEFAULT NULL,
  `permanent_address` varchar(250) DEFAULT NULL,
  `permanent_telephone_no` varchar(20) DEFAULT NULL,
  `permanent_zip_code` varchar(10) DEFAULT NULL,
  `father_husband_occupation` varchar(255) DEFAULT NULL,
  `martial_status` int(11) DEFAULT NULL,
  `emp_code` int(11) DEFAULT NULL,
  `dial_code` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


ALTER TABLE `proll_employee_info` ADD COLUMN `inactive_marked_by` INT NULL DEFAULT NULL;
ALTER TABLE `proll_employee_info` ADD COLUMN `inactive_marked_at` DATE NULL DEFAULT NULL;



#for CRF-04-0039(A)

ALTER TABLE bank_accounts
ADD COLUMN raast_account_number VARCHAR(255) NULL;

