ALTER TABLE proll_client_expense_type
DROP PRIMARY KEY;

ALTER TABLE proll_client_expense_type
MODIFY COLUMN cid INT(9) UNSIGNED;

ALTER TABLE proll_client_expense_type
  ADD CONSTRAINT fk_cid
  FOREIGN KEY (cid) REFERENCES proll_client(id)
  ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE proll_expense_type
MODIFY COLUMN id INT(9) UNSIGNED AUTO_INCREMENT;

ALTER TABLE proll_client_expense_type
MODIFY COLUMN expense_type_id INT(9) UNSIGNED;

SET foreign_key_checks = 0;

ALTER TABLE proll_client_expense_type
  ADD CONSTRAINT fk_expense_type_id
  FOREIGN KEY (expense_type_id) REFERENCES proll_expense_type(id)
  ON DELETE CASCADE ON UPDATE CASCADE;
  
SET foreign_key_checks = 1;
 
ALTER TABLE proll_client_expense_type

  ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY FIRST,
  
  ADD COLUMN title VARCHAR(255) AFTER expense_type_id,
  
  ADD COLUMN status BOOLEAN DEFAULT 0 AFTER title,

  ADD COLUMN created_by INT AFTER status,

  ADD COLUMN updated_by INT AFTER created_by,

  ADD COLUMN deleted_by INT AFTER updated_by,

  ADD COLUMN created_at DATETIME DEFAULT CURRENT_TIMESTAMP AFTER deleted_by,

  ADD COLUMN updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER created_at,

  ADD COLUMN deleted_at DATETIME NULL AFTER updated_at;
  
UPDATE proll_expense_type
SET active = 0;

INSERT INTO proll_expense_type (type, active) 
VALUES 
('Medical Expense', 1),
('Travel Expense', 1),
('Business-Related Expense', 1),
('Entertainment Expense', 1),
('Miscellaneous Expenses', 1);

UPDATE proll_client_expense_type
SET `status` = 0;

-- Insert records for expense_type_id 70 with cid = 48
INSERT INTO proll_client_expense_type (cid, expense_type_id, title, `status`) 
VALUES 
(48, (SELECT id FROM proll_expense_type where `type` = "Medical Expense"), 'OPD', 1),
(48, (SELECT id FROM proll_expense_type where `type` = "Medical Expense"), 'IPD', 1),
(48, (SELECT id FROM proll_expense_type where `type` = "Medical Expense"), 'Maternity', 1),
(48, (SELECT id FROM proll_expense_type where `type` = "Medical Expense"), 'Dental Care', 1),
(48, (SELECT id FROM proll_expense_type where `type` = "Medical Expense"), 'Vision Care', 1),
(48, (SELECT id FROM proll_expense_type where `type` = "Medical Expense"), 'Medical Equipment', 1);

-- Insert records for expense_type_id 71 with cid = 48
INSERT INTO proll_client_expense_type (cid, expense_type_id, title, status) 
VALUES 
(48, (SELECT id FROM proll_expense_type where `type` = "Travel Expense"), 'Travel Tickets', 1),
(48, (SELECT id FROM proll_expense_type where `type` = "Travel Expense"), 'Car Rental', 1),
(48, (SELECT id FROM proll_expense_type where `type` = "Travel Expense"), 'Local Transport', 1),
(48, (SELECT id FROM proll_expense_type where `type` = "Travel Expense"), 'Mileage', 1),
(48, (SELECT id FROM proll_expense_type where `type` = "Travel Expense"), 'Toll & Parking', 1),
(48, (SELECT id FROM proll_expense_type where `type` = "Travel Expense"), 'Hotel Stay', 1),
(48, (SELECT id FROM proll_expense_type where `type` = "Travel Expense"), 'Boarding & Lodging', 1),
(48, (SELECT id FROM proll_expense_type where `type` = "Travel Expense"), 'Daily Allowance', 1);

-- Insert records for expense_type_id 72 with cid = 48
INSERT INTO proll_client_expense_type (cid, expense_type_id, title, status) 
VALUES 
(48, (SELECT id FROM proll_expense_type where `type` = "Business-Related Expense"), 'Conference or seminar fees', 1),
(48, (SELECT id FROM proll_expense_type where `type` = "Business-Related Expense"), 'Internet and phone charges', 1),
(48, (SELECT id FROM proll_expense_type where `type` = "Business-Related Expense"), 'Printing, Copying and Mailing expenses', 1);

-- Insert records for expense_type_id 73 with cid = 48
INSERT INTO proll_client_expense_type (cid, expense_type_id, title, status) 
VALUES 
(48, (SELECT id FROM proll_expense_type where `type` = "Entertainment Expense"), 'Daily Meal Allowance', 1),
(48, (SELECT id FROM proll_expense_type where `type` = "Entertainment Expense"), 'Meal Expenses (breakfast, lunch, dinner)', 1),
(48, (SELECT id FROM proll_expense_type where `type` = "Entertainment Expense"), 'Tips and gratuities', 1),
(48, (SELECT id FROM proll_expense_type where `type` = "Entertainment Expense"), 'Mini Bar/Beverages', 1),
(48, (SELECT id FROM proll_expense_type where `type` = "Entertainment Expense"), 'Event Tickets', 1),
(48, (SELECT id FROM proll_expense_type where `type` = "Entertainment Expense"), 'Recreational Activities', 1),
(48, (SELECT id FROM proll_expense_type where `type` = "Entertainment Expense"), 'Gifts', 1),
(48, (SELECT id FROM proll_expense_type where `type` = "Entertainment Expense"), 'Decorations or Promotional Items', 1);

-- Insert records for expense_type_id 74 with cid = 48
INSERT INTO proll_client_expense_type (cid, expense_type_id, title, status) 
VALUES 
(48, (SELECT id FROM proll_expense_type where `type` = "Miscellaneous Expenses"), 'Laundry and Dry-cleaning services', 1),
(48, (SELECT id FROM proll_expense_type where `type` = "Miscellaneous Expenses"), 'Visa and passport fees', 1),
(48, (SELECT id FROM proll_expense_type where `type` = "Miscellaneous Expenses"), 'Travel Insurance', 1),
(48, (SELECT id FROM proll_expense_type where `type` = "Miscellaneous Expenses"), 'Currency exchange fees', 1),
(48, (SELECT id FROM proll_expense_type where `type` = "Miscellaneous Expenses"), 'Mobile Load/Card', 1),
(48, (SELECT id FROM proll_expense_type where `type` = "Miscellaneous Expenses"), 'Courier Charges', 1),
(48, (SELECT id FROM proll_expense_type where `type` = "Miscellaneous Expenses"), 'Stationary Purchase', 1),
(48, (SELECT id FROM proll_expense_type where `type` = "Miscellaneous Expenses"), 'Any Other Expense', 1);

ALTER TABLE hr_expense_setup
ADD COLUMN expense_title_id INT AFTER expense_type_id;

ALTER TABLE hr_expense_setup
ADD CONSTRAINT fk_expense_title_id
FOREIGN KEY (expense_title_id) REFERENCES proll_client_expense_type(id)
ON DELETE CASCADE
ON UPDATE CASCADE;

ALTER TABLE proll_expense_detail
ADD COLUMN expense_title_id INT AFTER type_id;

ALTER TABLE proll_expense_detail
ADD CONSTRAINT fk_proll_expense_detail_expense_title_id
FOREIGN KEY (expense_title_id) REFERENCES proll_client_expense_type(id)
ON DELETE CASCADE
ON UPDATE CASCADE;

ALTER TABLE proll_expense_detail
MODIFY COLUMN type_id INT(9) UNSIGNED;

SET foreign_key_checks = 0;

ALTER TABLE proll_expense_detail
  ADD CONSTRAINT fk_eproll_expense_type_id
  FOREIGN KEY (type_id) REFERENCES proll_expense_type(id)
  ON DELETE CASCADE ON UPDATE CASCADE;
  
SET foreign_key_checks = 1;



#add Expense Menus
INSERT INTO `acm_menus` (`menu_title`, `parent_id`, `menu_order`, `menu_type_id`, `level`, `client_id`, `icon`, `img`, `page_menu_title`, `created_by`, `updated_at`, `created_at`) VALUES ('Expense Claim Report', 139, 2, 3, 4, 48, 'Expense Analytics_Expenses Report.svg', 'Expense Analytics_Expenses Report.svg', 'Expense Claim Report', 58472, '2022-06-02 15:57:49', '2022-06-02 15:57:49');


INSERT INTO `acm_group_role_menu` (`menus_id`, `client_portal_id`, `description`, `platform`, `status`, `cid`, `created_by`, `updated_by`, `on_created`, `on_updated`) VALUES ((SELECT id FROM acm_menus WHERE menu_title = "Expense Claim Report" AND parent_id = 139), 149, 'View Expense Claim Report', 0, 1, 48, 56193, 56193, '2023-02-03 14:51:32', '2023-02-03 14:51:32');
INSERT INTO `acm_group_role_menu` (`menus_id`, `client_portal_id`, `description`, `platform`, `status`, `cid`, `created_by`, `updated_by`, `on_created`, `on_updated`) VALUES ((SELECT id FROM acm_menus WHERE menu_title = "Expense Claim Report" AND parent_id = 139), 142, 'View Expense Claim Report', 0, 1, 48, 56193, 56193, '2023-02-03 14:51:32', '2023-02-03 14:51:32');
INSERT INTO `acm_group_role_menu` (`menus_id`, `client_portal_id`, `description`, `platform`, `status`, `cid`, `created_by`, `updated_by`, `on_created`, `on_updated`) VALUES ((SELECT id FROM acm_menus WHERE menu_title = "Expense Claim Report" AND parent_id = 139), 152, 'View Expense Claim Report', 0, 1, 48, 56193, 56193, '2023-02-03 14:51:32', '2023-02-03 14:51:32');
INSERT INTO `acm_group_role_menu` (`menus_id`, `client_portal_id`, `description`, `platform`, `status`, `cid`, `created_by`, `updated_by`, `on_created`, `on_updated`) VALUES ((SELECT id FROM acm_menus WHERE menu_title = "Expense Claim Report" AND parent_id = 139), 147, 'View Expense Claim Report', 0, 1, 48, 56193, 56193, '2023-02-03 14:51:32', '2023-02-03 14:51:32');


INSERT INTO `acm_group_role_association` (`menu_id`, `group_role_id`, `on_created`, `on_updated`, `component_name`, `description`, `status`) VALUES ((SELECT id FROM acm_menus WHERE menu_title = "Expense Claim Report" AND parent_id = 139), 17, '2023-02-03 14:51:52', '2023-02-03 14:51:52', '/reports/expense-claim', 'View a complete record of all expenses Calom applied through People-i in a given period of time', 1);
INSERT INTO `acm_group_role_association` (`menu_id`, `group_role_id`, `on_created`, `on_updated`, `component_name`, `description`, `status`) VALUES ((SELECT id FROM acm_menus WHERE menu_title = "Expense Claim Report" AND parent_id = 139), 19, '2023-02-03 14:51:52', '2023-02-03 14:51:52', '/reports/expense-claim', 'View a complete record of all expenses Calom applied through People-i in a given period of time', 1);
INSERT INTO `acm_group_role_association` (`menu_id`, `group_role_id`, `on_created`, `on_updated`, `component_name`, `description`, `status`) VALUES ((SELECT id FROM acm_menus WHERE menu_title = "Expense Claim Report" AND parent_id = 139), 12, '2023-02-03 14:51:52', '2023-02-03 14:51:52', '/reports/expense-claim', 'View a complete record of all expenses Calom applied through People-i in a given period of time', 1);
INSERT INTO `acm_group_role_association` (`menu_id`, `group_role_id`, `on_created`, `on_updated`, `component_name`, `description`, `status`) VALUES ((SELECT id FROM acm_menus WHERE menu_title = "Expense Claim Report" AND parent_id = 139), 10, '2023-02-03 14:51:52', '2023-02-03 14:51:52', '/reports/expense-claim', 'View a complete record of all expenses Calom applied through People-i in a given period of time', 1);

#update line manager expense landing page
UPDATE `acm_group_role_association` SET `component_name`='lm_expense' WHERE  menu_id = 39 AND group_role_id = 16;


INSERT INTO `acm_group_role_menu` (`menus_id`, `client_portal_id`, `description`, `platform`, `status`, `cid`, `created_by`, `updated_by`, `on_created`, `on_updated`, `pre_approved_status`) VALUES (139, 148, '', 0, 1, 48, 56193, 56193, '2022-06-02 16:26:08', '2022-06-02 16:26:08', 0);
INSERT INTO `acm_group_role_menu` (`menus_id`, `client_portal_id`, `description`, `platform`, `status`, `cid`, `created_by`, `updated_by`, `on_created`, `on_updated`, `pre_approved_status`) VALUES (138, 148, '', 0, 1, 48, 56193, 56193, '2022-06-02 16:26:08', '2022-06-02 16:26:08', 0);

INSERT INTO `acm_group_role_menu` (`menus_id`, `client_portal_id`, `description`, `platform`, `status`, `cid`, `created_by`, `updated_by`, `on_created`, `on_updated`, `pre_approved_status`) VALUES ((SELECT id FROM acm_menus WHERE menu_title = "Expense Claim Report" AND parent_id = 139), 148, 'View Expense Claim Report', 0, 1, 48, 56193, 56193, '2023-02-03 14:51:32', '2023-02-03 14:51:32', 0);



INSERT INTO `acm_group_role_association` (`menu_id`, `group_role_id`, `del`, `vie`, `upd`, `rig`, `on_created`, `on_updated`, `component_name`, `file_name`, `description`, `status`) VALUES (139, 16, 0, 0, 0, 0, '2022-06-02 17:49:43', '2022-06-02 17:49:43', 'lm_expense_analytics', NULL, 'View a complete record of all expenses applied through People-i in a given period of time', 1);

INSERT INTO `acm_group_role_association` (`menu_id`, `group_role_id`, `del`, `vie`, `upd`, `rig`, `on_created`, `on_updated`, `component_name`, `file_name`, `description`, `status`) VALUES ((SELECT id FROM acm_menus WHERE menu_title = "Expense Claim Report" AND parent_id = 139), 16, 0, 0, 0, 0, '2023-02-03 14:51:52', '2023-02-03 14:51:52', '/reports/expense-claim', NULL, 'View a complete record of all expenses Calom applied through People-i in a given period of time', 1);





#update employee expense landing page

INSERT INTO `acm_menus` (`menu_title`, `parent_id`, `menu_order`, `menu_type_id`, `level`, `client_id`, `icon`, `img`, `page_menu_title`, `isDeleted`, `created_by`, `updated_at`, `created_at`, `is_approved`, `pre_approved_status`, `pre_approved_value`, `pre_approved_description`, `approval_request_generated_by`) VALUES ('Apply Expense', 39, 1, 1, 3, 48, 'Expenses_Setup.svg', 'Expenses_Setup.svg', 'Apply Expense', 0, 58472, '2022-06-02 15:57:49', '2022-06-02 15:57:49', 1, 0, 0, NULL, NULL);


UPDATE `acm_group_role_association` SET `component_name`='emp_expense' WHERE  menu_id = 39 AND group_role_id = 10;


INSERT INTO `acm_group_role_menu` (`menus_id`, `client_portal_id`, `description`, `platform`, `status`, `cid`, `created_by`, `updated_by`, `on_created`, `on_updated`, `pre_approved_status`) VALUES (139, 147, '', 0, 1, 48, 56193, 56193, '2022-06-02 16:26:08', '2022-06-02 16:26:08', 0);
INSERT INTO `acm_group_role_menu` (`menus_id`, `client_portal_id`, `description`, `platform`, `status`, `cid`, `created_by`, `updated_by`, `on_created`, `on_updated`, `pre_approved_status`) VALUES (138, 147, '', 0, 1, 48, 56193, 56193, '2022-06-02 16:26:08', '2022-06-02 16:26:08', 0);

INSERT INTO `acm_group_role_menu` (`menus_id`, `client_portal_id`, `description`, `platform`, `status`, `cid`, `created_by`, `updated_by`, `on_created`, `on_updated`, `pre_approved_status`) VALUES ((SELECT id FROM acm_menus WHERE menu_title = "Apply Expense" AND parent_id = 39), 147, '', 0, 1, 48, 56193, 56193, '2022-06-02 16:26:08', '2022-06-02 16:26:08', 0);



INSERT INTO `acm_group_role_association` (`menu_id`, `group_role_id`, `del`, `vie`, `upd`, `rig`, `on_created`, `on_updated`, `component_name`, `file_name`, `description`, `status`) VALUES (139, 10, 0, 0, 0, 0, '2022-06-02 17:49:43', '2022-06-02 17:49:43', 'emp_expense_analytics', NULL, 'View a complete record of all expenses applied through People-i in a given period of time', 1);


INSERT INTO `acm_group_role_association` (`menu_id`, `group_role_id`, `del`, `vie`, `upd`, `rig`, `on_created`, `on_updated`, `component_name`, `file_name`, `description`, `status`) VALUES ((SELECT id FROM acm_menus WHERE menu_title = "Apply Expense" AND parent_id = 39), 10, 0, 0, 0, 0, '2022-06-02 17:49:43', '2022-06-02 17:49:43', 'emp_expense_add', NULL, 'Create and Manage applications for work-related expense reimbursement', 1);

