DROP TABLE IF EXISTS employee_additional_details;

DROP TABLE IF EXISTS proll_employee_additional;

CREATE TABLE `employee_additional_details` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`emp_id` INT(11) UNSIGNED NULL DEFAULT NULL,
	`emirates_id` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`labour_card_number` INT(11) NULL DEFAULT NULL,
	`labour_card_expiry` DATE NULL DEFAULT NULL,
	`on_ppa_visa` VARCHAR(50) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
	`joining_date_mol` DATE NULL DEFAULT NULL,
	`mol_id` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`contract_expiry_date` DATE NULL DEFAULT NULL,
	`kashf_status` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`wps_card_number` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`new_salary` DOUBLE(11,2) NULL DEFAULT NULL,
	`increment_effective_date` DATE NULL DEFAULT NULL,
	`allowance_type` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`driving_license_status` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`personal_file_status` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`application_form_status` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`cnic_copies_status` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`employment_letter_status` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`joining_report_status` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`photographs_status` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`reference1_status` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`reference2_status` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`satatement_of_ethics_status` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`confirmation_of_bank_account_status` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`cv_status` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`education_documents_status` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`resign_release_letter_status` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`exit_clearance_form_status` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`employee_card_status` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`experience_letter_status` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`police_verification_form_status` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`surity_bond_status` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`gratuity_acknowledge_form_status` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`telephonic_reference_check_status` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`new_employment_city` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`new_employment_department` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`new_employment_designation` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`letter_type` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`letter_issuance_date` DATE NULL DEFAULT NULL,
	`letter_dispatch_date` DATE NULL DEFAULT NULL,
	`letter_issued_by_name` VARCHAR(150) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`letter_issued_by_designation` VARCHAR(150) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`show_cause_reply_status` VARCHAR(150) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`reason_of_issuance` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`letter_duplication_status` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`penalty_amount` FLOAT(11,2) NULL DEFAULT NULL,
	`new_Branch` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`embassy_addressee` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`embassy_city` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`country_of_visit` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`visit_from` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`visit_to` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`Market` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`cluster` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`sub_cluster` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`continent` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`deployment_onshore_offshore` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`domestic_international` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`business_class_fbs_nongbs` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`sector_division` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`hrbp_name` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`global_function_code` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`global_function_name` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`company_code` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`wps_number` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`local_id_no` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`pin_no` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`nssf_no` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`nhif_no` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`pay_frequency` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`channel` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`internal_order` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`internal_order_code` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`rotation` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`project_name` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`currency_rate` FLOAT(11,2) NULL DEFAULT NULL,
	`work_permit_no` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`ntn_type_status` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`department_group1` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`department_group2` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`department_group3` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`place_of_posting_location` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`place_of_posting_code` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`district` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`purchase_order_no` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`po_start_date` DATE NULL DEFAULT NULL,
	`purchase_order_expire_date` DATE NULL DEFAULT NULL,
	`purchase_order_expense_amount` FLOAT(11,2) NULL DEFAULT NULL,
	`ss_directorate` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`sepration_type` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`weekend_day` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`compensation_remarks` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`payment_mod` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`created_at` DATE NULL DEFAULT NULL,
	`updated_at` DATE NULL DEFAULT NULL,
	`deleted_at` DATE NULL DEFAULT NULL,
	PRIMARY KEY (`id`) USING BTREE,
	INDEX `FK_proll_employee_additional_proll_employee` (`emp_id`) USING BTREE,
	CONSTRAINT `FK_proll_employee_additional_proll_employee` FOREIGN KEY (`emp_id`) REFERENCES `bata_300_7007`.`proll_employee` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=175
;

CREATE TABLE `salary_category` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`contract_type` VARCHAR(45) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`status` TINYINT(1) NULL DEFAULT '1',
	PRIMARY KEY (`id`) USING BTREE
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=4
;


CREATE TABLE `salary_category_detail` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`salary_type` VARCHAR(45) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`salary_type_short` VARCHAR(45) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`contract_category_id` INT(11) NULL DEFAULT NULL,
	`status` TINYINT(1) NULL DEFAULT '1',
	PRIMARY KEY (`id`) USING BTREE,
	INDEX `FK1_salary_category_detail` (`contract_category_id`) USING BTREE,
	CONSTRAINT `FK1_salary_category_detail` FOREIGN KEY (`contract_category_id`) REFERENCES `bata_300_7007`.`salary_category` (`id`) ON UPDATE CASCADE ON DELETE SET NULL
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=20
;

CREATE TABLE `salary_category_setting` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`start_date` DATE NOT NULL,
	`end_date` DATE NOT NULL,
	`salary_category_id` INT(11) NULL DEFAULT NULL,
	`label` VARCHAR(45) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`status` TINYINT(1) NULL DEFAULT '1',
	PRIMARY KEY (`id`) USING BTREE,
	INDEX `FK2_salary_category_detail` (`salary_category_id`) USING BTREE,
	CONSTRAINT `FK2_salary_category_detail` FOREIGN KEY (`salary_category_id`) REFERENCES `bata_300_7007`.`salary_category` (`id`) ON UPDATE CASCADE ON DELETE SET NULL
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=8
;

ALTER TABLE `banks`
	ADD COLUMN `created_date` DATETIME NULL DEFAULT NULL AFTER `deleted_at`;
ALTER TABLE `banks`
	ADD COLUMN `updated_date` DATETIME NULL DEFAULT NULL AFTER `created_date`;

ALTER TABLE `proll_department_managers`
DROP INDEX `cid_department_region_id_department_department_group3_id`,
DROP INDEX `cid_dep_code_department_region_id_department_group3_id`;