-----------------------------------------------------------------------------------------------
-- Hetal Visaveliya (14th July 2020)
UPDATE `as_steps` SET `as_title` = 'Resume Approval' WHERE `as_steps`.`id` = 3;
UPDATE `as_steps` SET `as_title` = 'Interview' WHERE `as_steps`.`id` = 5;
UPDATE `as_steps` SET `as_title` = 'Agreement' WHERE `as_steps`.`id` = 6;
UPDATE `as_steps` SET `as_desc_before` = 'You will need to sign the Agreement in order to continue our process.' WHERE `as_steps`.`id` = 6;
UPDATE `as_steps` SET `as_desc_current` = 'You will need to sign the Agreement in order to continue our process.' WHERE `as_steps`.`id` = 6;
UPDATE `as_steps` SET `as_desc_after` = 'You will need to sign the Agreement in order to continue our process.' WHERE `as_steps`.`id` = 6;
UPDATE `as_steps` SET `as_desc_before` = 'IMIX agent will review and evaluate your submitted resume and you will be notified within 24 hours.' WHERE `as_steps`.`id` = 3;
UPDATE `as_steps` SET `as_desc_current` = 'IMIX agent will review and evaluate your submitted resume and you will be notified within 24 hours.' WHERE `as_steps`.`id` = 3;
UPDATE `as_steps` SET `as_desc_before` = 'Pre-screening interview with an IMIX agent to evaluate your English level and gather additional information regarding your application.' WHERE `as_steps`.`id` = 5;
UPDATE `as_steps` SET `as_desc_current` = 'Pre-screening interview with an IMIX agent to evaluate your English level and gather additional information regarding your application.' WHERE `as_steps`.`id` = 5;
UPDATE `as_steps` SET `as_desc_after` = 'Pre-screening interview with an IMIX agent to evaluate your English level and gather additional information regarding your application.' WHERE `as_steps`.`id` = 5;
----------------------------------------------------------------------------------------------------------------------------------
--Manisha Odedara (14th July 2020)
INSERT INTO `as_steps` (`id`, `as_order_key`, `as_title`, `as_icon`, `as_desc_before`, `as_desc_current`, `as_desc_after`, `j1_status_id`) VALUES (NULL, '3_predeparture_orientation', 'Pre Departure Orientation', 'fa-file-text-o', 'Pre Departure Orientation', 'Pre Departure Orientation', 'Pre Departure Orientation', '3014'); 
ALTER TABLE `user_details` ADD `pre_departure_orientation_flag` TINYINT(4) NULL DEFAULT NULL COMMENT '0 = \"No\" and 1 = \"Yes\"' AFTER `consecutive_visa_denied_flag`;
INSERT INTO `j1_statuses` (`id`, `status_key`, `status_name`, `category`) VALUES ('3014', 'pre-departure-orientation', 'pre-departure-orientation', '3');
----------------------------------------------------------------------------------------------------------------------
--Hetal Visaveliya (16th July 2020)
UPDATE `as_steps` SET `j1_status_id` = '' WHERE `as_steps`.`as_order_key` = '3_predeparture_orientation';
DELETE FROM `j1_statuses` WHERE `j1_statuses`.`id` = 3014;
----------------------------------------------------------------------------------------------------------------------
--Nidhi Lakhani (22nd July 2020)

CREATE TABLE `admin_log` (
  `id` bigint(20) NOT NULL,
  `admin_id` int(11) NOT NULL,
  `admin_log_text` mediumtext CHARACTER SET utf8 NOT NULL,
  `user_id` int(11) NOT NULL,
  `admin_action_owner` int(11) NOT NULL,
  `admin_log_time` datetime DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `admin_log`
  ADD PRIMARY KEY (`id`),
  ADD KEY `admin_id` (`admin_id`),
  ADD KEY `admin_action_owner` (`user_id`),
  ADD KEY `admin_action_owner_2` (`admin_action_owner`);
ALTER TABLE `admin_log`
  MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
----------------------------------------------------------------------------------------------------------------------
--Manisha Odedara (23 July 2020)
INSERT INTO `system_settings` (`id`, `name`, `field`, `value`, `description`) VALUES (NULL, 'No Replay Email', 'no_reply_email', '', 'No Replay Email');

-- Manisha Odedara (24 July 2020)
UPDATE
    email_notification
SET 
    text =  "<div>
                <p>Hello</p>
                <p>Dear candidate,</p>
                <p>
                You're invited to join  {{company}} </p> 
                <p>{{company}} ({{contact_email}}) sent you this invitation. </p>
                <p>{{url}}</p>
                <p>Please feel free to contact us by replying to this email or at
                {{contact_email}} with any questions.</p>
            </div>"
WHERE 
    en_key = 'send_invitation_to_user'
LIMIT 1;
-------------------------------------------------------------------------------------------------------------------------
--Nidhi lakhani (28 July 2020)
UPDATE `as_steps` SET `j1_status_id` = '4003' WHERE `as_steps`.`as_order_key` = '4_medical_insurance';
UPDATE `as_steps` SET `j1_status_id` = '4004' WHERE `as_steps`.`as_order_key` = '4_arrival_check_in';
UPDATE `as_steps` SET `j1_status_id` = '4005' WHERE `as_steps`.`as_order_key` = '4_monthly_check_in';
UPDATE `as_steps` SET `j1_status_id` = '4006' WHERE `as_steps`.`as_order_key` = '4_mid_super_evaluation';
UPDATE `as_steps` SET `j1_status_id` = '4007' WHERE `as_steps`.`as_order_key` = '4_mid_evaluation';
UPDATE `as_steps` SET `j1_status_id` = '4008' WHERE `as_steps`.`as_order_key` = '4_final_super_evaluation';
UPDATE `as_steps` SET `j1_status_id` = '4009' WHERE `as_steps`.`as_order_key` = '4_final_evaluation';

INSERT INTO `j1_statuses` (`id`, `status_key`, `status_name`, `category`) VALUES ('4003', 'medical-insurance', 'Medical Insurance Activated', '4');
INSERT INTO `j1_statuses` (`id`, `status_key`, `status_name`, `category`) VALUES ('4004', 'arrival-checkin', 'Arrival Check In Done', '4');
INSERT INTO `j1_statuses` (`id`, `status_key`, `status_name`, `category`) VALUES ('4005', 'monthly-checkin', 'Monthly Check In Done', '4');
INSERT INTO `j1_statuses` (`id`, `status_key`, `status_name`, `category`) VALUES ('4006', 'midterm-supervisor-evaluation', 'Midterm Supervisor Evaluation Completed', '4');
INSERT INTO `j1_statuses` (`id`, `status_key`, `status_name`, `category`) VALUES ('4007', 'midterm-participant-evaluation', 'Midterm Participant Evaluation Completed', '4');
INSERT INTO `j1_statuses` (`id`, `status_key`, `status_name`, `category`) VALUES ('4008', 'final-supervisor-evaluation', 'Final Supervisor Evaluation Completed', '4');
INSERT INTO `j1_statuses` (`id`, `status_key`, `status_name`, `category`) VALUES ('4009', 'final-participant-evaluation', 'Final Participant Evaluation Completed', '4');

-------------------------------------------------------------------------------------------------------------------------
-- IMIX BETA SYNC SUCCESSFULLY.
-------------------------------------------------------------------------------------------------------------------------
--Hetal Visaveliya (05 Aug 2020)

ALTER TABLE `portfolio` ADD `sponsor_name` VARCHAR(255) NOT NULL COMMENT 'sponsor name field only updated when candidate is sync with ITN and ITN admin add sponsor' AFTER `enable_special_stage`;

ALTER TABLE `portfolio` CHANGE `sponsor_name` `sponsor_name` VARCHAR(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL COMMENT 'sponsor name field only updated when candidate is sync with ITN and ITN admin add sponsor';

-------------------------------------------------------------------------------------------------------------------------
--Nidhi lakhani (6 August 2020)
ALTER TABLE `j1_interview` ADD `zoom_meeting_id` INT(11) NOT NULL AFTER `portfolio_id`;
ALTER TABLE `j1_interview` ADD INDEX(`zoom_meeting_id`);

CREATE TABLE `zoom_meetings` (
  `id` int(11) NOT NULL,
  `meeting_type` tinyint(4) NOT NULL COMMENT '1 = Pre-screen Interview, 2 = HC Interview, 3 = English Verification',
  `meeting_id` varchar(255) NOT NULL COMMENT 'meeting id resopnse from Zoom',
  `meeting_topic` varchar(1000) DEFAULT NULL,
  `meeting_uuid` varchar(255) DEFAULT NULL,
  `meeting_password` varchar(255) DEFAULT NULL,
  `meeting_host` varchar(255) DEFAULT NULL COMMENT 'id of user in zoom meeting who will be host of meeting',
  `meeting_participants` text COMMENT 'participants who will be join the zoom meeting',
  `meeting_starttime` datetime DEFAULT NULL,
  `meeting_duration` int(11) DEFAULT NULL,
  `calendar_event` text COMMENT 'Calendar links for this meeting event',
  `join_url` varchar(255) DEFAULT NULL COMMENT 'response of join url from Zoom meeting',
  `start_url` text COMMENT 'this url use for the start meeting',
  `host_key` varchar(20) DEFAULT NULL COMMENT 'host key can be used to claim as host during meeting',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `zoom_meetings`
  ADD PRIMARY KEY (`id`),
  ADD KEY `meeting_type` (`meeting_type`);
ALTER TABLE `zoom_meetings`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
INSERT INTO `system_settings` (`id`, `name`, `field`, `value`, `description`) VALUES (NULL, 'Devlopment Zoom Setting', 'devlopment_zoom_setting', '{\"zoom_user_key\":\"wtpdtmL2QHmnoYlnPtnPsQ\",\"zoom_key\":\"aB_XFfH6QLG2Uv03C-hvoA\",\"zoom_secret\":\"y0uvlnnGZoxbcQQ8HQwvL97LdB3XynRB40pB\",\"api_url\":\"https://api.zoom.us/v2/\"}', 'Zoom host key and secret key');

ALTER TABLE `j1_interview` ADD `ev_alternate_datetime` DATETIME NULL COMMENT 'Alternative date-time on schedule rejected by EV' AFTER `graduation_date`, ADD `ev_reject_reason` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL AFTER `ev_alternate_datetime`;
ALTER TABLE `j1_interview` ADD `interview_confirmed_user` INT(1) NOT NULL COMMENT '1 = Yes, 2 = No' AFTER `graduation_date`;


INSERT INTO `email_notification` (`id`, `en_key`, `subject`, `text`, `send_to`, `send_cc`, `status`) VALUES (NULL, 'zoom_pre_interview_schedule_to_ev', 'Confirm your Pre-Screening interview schedule with [{{admin_name}}]', '<div>\r\n<p>Dear {{ev_name}},</p>\r\n\r\n<p>We are pleased to send you the schedule and details for your interview with <strong>{{admin_name}}</strong>.<br />\r\nInterview Scheduled on: <strong>{{interview_datetime}}</strong><br />\r\nTime Zone: <strong>{{time_zone}}</strong><br />\r\nYou can convert time here: <a href=\"http://www.timezoneconverter.com/cgi-bin/tzc.tzc?style=2\" target=\"_blank\">www.timezoneconverter.com</a></p>\r\n\r\n<p>You will be interviewing with the following ITN representative:<br />\r\n- Name: <strong>{{contact_person_name}}</strong><br />\r\n- Title: <strong>{{contact_title}}</strong></p>\r\n\r\n<p>Your interview will be conduct on zoom meeting, Please make sure you have install zoom software in your computer or zoom app on your mobile.</p>\r\n\r\n<p>Following details you need join zoom meeting.<br />\r\n<strong>Join from PC, Mac, Linux, iOS or Android:</strong><br />\r\n<span style=\"color:red\">{{join_link}}</span><br />\r\n<strong>Note:</strong> This link should not be shared with others; it is unique to you.</p>\r\n\r\n<p>Meeting ID: <strong>{{meeting_id}}</strong><br />\r\nPassword: <strong>{{password}}</strong></p>\r\n\r\n<p>Click to add this meeting to your {{calendar_link}} calendar.</p>\r\n\r\n<p>{{confirmation_link}}</p>\r\n\r\n<p>Thank you,</p>\r\n</div>', '1', 'test_cc_qa@yopmail.com', '');

INSERT INTO `email_notification` (`id`, `en_key`, `subject`, `text`, `send_to`, `send_cc`, `status`) VALUES (NULL, 'zoom_pre_interview_schedule_to_admin', 'Confirm your Pre-Screening interview schedule with [{{ev_name}}]', '<div>\r\n<p>Dear {{admin_name}},</p>\r\n\r\n<p>We are pleased to send you the schedule and details for your interview with <strong>{{ev_name}}</strong>.<br />\r\nInterview Scheduled on: <strong>{{interview_datetime}}</strong><br />\r\nTime Zone: <strong>{{time_zone}}</strong></p>\r\n\r\n<p>You can convert time here: <a href=\"http://www.timezoneconverter.com/cgi-bin/tzc.tzc?style=2\" target=\"_blank\">www.timezoneconverter.com</a></p>\r\n\r\n<p>Your interview will be conduct on zoom meeting, Please make sure you have install zoom software in your computer or zoom app on your mobile.</p>\r\n\r\n<p>Following details you need join zoom meeting.<br />\r\n<strong>Join from PC, Mac, Linux, iOS or Android:</strong><br />\r\n<span style=\"color:red\">{{join_link}}</span><br />\r\n<strong>Note:</strong> This link should not be shared with others; it is unique to you.</p>\r\n\r\n<p>Meeting ID: <strong>{{meeting_id}}</strong><br />\r\nPassword: <strong>{{password}}</strong></p>\r\n\r\n<p>{{claim_host}}</p>\r\n\r\n<p>Click to add this meeting to your {{calendar_link}} calendar.</p>\r\n\r\n<p>Thank you,</p>\r\n</div>', '2', 'test_cc_qa@yopmail.com', '');
ALTER TABLE `j1_interview` ADD `notification_sent_on` DATETIME NULL AFTER `ev_reject_reason`;

-------------------------------------------------------------------------------------------------------------------------
--Hetal Visaveliya (07 August 2020)

ALTER TABLE `agency` ADD `agency_logo` VARCHAR(255) NULL AFTER `agency_address`;
ALTER TABLE `agency` ADD `agency_bg_color` VARCHAR(255) NULL AFTER `agency_logo`;
-------------------------------------------------------------------------------------------------------------------------
--Manisha Odedara (07 August 2020)
ALTER TABLE `flight_info` ADD `flight_order` TINYINT NOT NULL DEFAULT '1' AFTER `portfolio_id`;
ALTER TABLE `flight_info` ADD `arrival_method` TINYINT NULL DEFAULT NULL COMMENT '1 = Airplane,2 = Bus,3 = Train,4 = Car' AFTER `arrival_date`, ADD `arrival_state` VARCHAR(50) NULL DEFAULT NULL AFTER `arrival_method`, ADD `arrival_city` VARCHAR(50) NULL DEFAULT NULL AFTER `arrival_state`;

ALTER TABLE `user_details` ADD `eligibility_test_complete` DATE NULL DEFAULT NULL COMMENT 'Eligibility test complete date' AFTER `eligibility_test_output`;

CREATE TABLE `master_pending_action` (
  `id` int(11) NOT NULL,
  `portfolio_id` int(11) NOT NULL,
  `action_message` varchar(255) NOT NULL,
  `action_step_key` varchar(255) NOT NULL,
  `action_stauts` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0 = pending, 1 = done',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `master_pending_action`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `master_pending_action`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `master_pending_action` ADD INDEX(`portfolio_id`);

INSERT INTO `permissions` (`id`, `parent_permission_id`, `permission_group_id`, `permission_name`, `display_name`, `description`, `route_name`, `is_menu_item`) VALUES (NULL, NULL, '14', 'master.pending.action.list', 'Master Pending Action List', 'Master Pending Action List', 'master.pending.action.list', '1');
INSERT INTO `permissions` (`id`, `parent_permission_id`, `permission_group_id`, `permission_name`, `display_name`, `description`, `route_name`, `is_menu_item`) VALUES (NULL, NULL, '14', 'master.pending.action.filter', 'Filter Master Pending Action', 'Filter Master Pending Action', 'master.pending.action.filter', '0');

INSERT INTO `permissions` (`id`, `parent_permission_id`, `permission_group_id`, `permission_name`, `display_name`, `description`, `route_name`, `is_menu_item`) VALUES (NULL, NULL, '8', 'sql.query', 'SQL Query', 'SQL query', 'sql.query', '1');
INSERT INTO `permissions` (`id`, `parent_permission_id`, `permission_group_id`, `permission_name`, `display_name`, `description`, `route_name`, `is_menu_item`) VALUES (NULL, NULL, '8', 'run.sql.query', 'Run SQL Query', 'Run the SQL query', 'run.sql.query', '0');
------------------------------------------------------------------------------------------
--Nidhi lakhani (7 August 2020)
INSERT INTO `email_notification` (`id`, `en_key`, `subject`, `text`, `send_to`, `send_cc`, `status`) VALUES (NULL, 'zoom_pre_screening_interview_rejected', 'Pre Screening Interview Rejected {{ev_name}} with {{admin_name}}', '<div>\r\n<p>Dear admin,</p>\r\n\r\n<p>The ITN Pre Screening interview scheduled for <strong>{{ev_name}}</strong> with <strong>{{admin_name}}</strong> has been rejected by <strong>{{action_by}}</strong>.</p>\r\n\r\n<p>Following is the details of Pre Screening interview schedule.<br />\r\n<br />\r\nInterviewer admin: {{contact_person_name}}<br />\r\nEV Name: {{ev_name}}<br />\r\n<br />\r\n<strong>Pre Screening Interview Schedule On:</strong><br />\r\nDate/Time: {{interview_datetime}}<br />\r\nTimezone: {{timezone}}</p>\r\n\r\n<p><strong>Following is the reason of rejection:</strong><br />\r\n{{reject_reason}}</p>\r\n{{alternate_datetime}}\r\n\r\n<p>Thank You,</p>\r\n</div>', '1', 'test_cc_qa@yopmail.com', '');
-------------------------------------------------------------------------------------------------------------------------
--Manisha Odedara(8 August 2020)
UPDATE `menu_items` SET `permission_group_id` = '' WHERE `menu_items`.`id` = 22;
------------------------------------------------------------------------------------------------------------------------
--Nidhi Lakhani(13 August 2020)

CREATE TABLE `tickets` (
  `id` int(11) NOT NULL,
  `portfolio_id` int(11) NOT NULL,
  `subject` varchar(255) NOT NULL,
  `question` text NOT NULL,
  `answer` text NOT NULL,
  `status` enum('pending','completed') NOT NULL,
  `active_step_key` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `tickets`
  ADD PRIMARY KEY (`id`),
  ADD KEY `portfolio_id` (`portfolio_id`);
ALTER TABLE `tickets`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

INSERT INTO `permissions` (`id`, `parent_permission_id`, `permission_group_id`, `permission_name`, `display_name`, `description`, `route_name`, `is_menu_item`) VALUES (NULL, NULL, '11', 'user.ticket', 'Users Tickets', 'Users Tickets', 'user.ticket', '1');
------------------------------------------------------------------------------------------------------------------------
--Nidhi Lakhani(14 August 2020)
ALTER TABLE `tickets` ADD `admin_id` INT(11) NOT NULL COMMENT 'admins -> id' AFTER `portfolio_id`;
ALTER TABLE `tickets` ADD INDEX(`admin_id`);
------------------------------------------------------------------------------------------------------------------------
--Nidhi Lakhani(18 August 2020)
INSERT INTO `as_steps` (`id`, `as_order_key`, `as_title`, `as_icon`, `as_desc_before`, `as_desc_current`, `as_desc_after`, `j1_status_id`) VALUES (NULL, '4_departure_info', 'Departure Info', 'fa-plane', 'Information about your departure flight.', 'Information about your departure flight.', 'Information about your departure flight.', '4010');

ALTER TABLE `flight_info` ADD `flight_status` ENUM('ARRIVAL', 'DEPARTURE') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'ARRIVAL' AFTER `additional_info`;
INSERT INTO `j1_statuses` (`id`, `status_key`, `status_name`, `category`) VALUES (4010, 'departure-flight-info', 'Departure Flight Info', 4);
-----------------------------------------------------------------------------------------------------------------------
--Manisha Odedara(18 August 2020)

INSERT INTO `document_types` (`id`, `name`, `doc_key`, `description`, `template`, `country_specific`) VALUES (55, 'Placement Agency Agreement', 'placement_agency_agreement', '', '', '0');
INSERT INTO `document_types` (`id`, `name`, `doc_key`, `description`, `template`, `country_specific`) VALUES (56, 'Sponser Agency Agreement', 'sponser_agency_agreement', '', '', '0');
--------------------------------------------------------------------------------------------------------------------
--Nidhi Lakhani(24 August 2020)
ALTER TABLE `j1_interview` ADD `date_interview_hc` DATETIME NOT NULL AFTER `time_zone_user`, ADD `time_zone_hc` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL AFTER `date_interview_hc`;
ALTER TABLE `j1_interview` ADD `interview_confirmed_hc` INT(1) NOT NULL COMMENT '1 = Yes, 2 = No' AFTER `interview_confirmed_user`;
ALTER TABLE `j1_interview` ADD `hc_reject_reason` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL AFTER `ev_reject_reason`;
ALTER TABLE `j1_interview` ADD `hc_alternate_datetime` DATETIME NULL DEFAULT NULL COMMENT 'Alternative date-time on schedule rejected by HC' AFTER `ev_alternate_datetime`;

ALTER TABLE `j1_interview` ADD `hc_id` INT(11) NOT NULL AFTER `portfolio_id`, ADD `pos_id` INT(11) NOT NULL AFTER `hc_id`;
INSERT INTO `email_notification` (`id`, `en_key`, `subject`, `text`, `send_to`, `send_cc`, `status`) VALUES (NULL, 'hc_interview_schedule_to_ev', 'Confirm your interview schedule with [{{hc_name}}]', '<div>\r\n<p>Dear {{ev_name}},</p>\r\n\r\n<p>We are pleased to send you the schedule and details for your interview with <strong>{{hc_name}}</strong>.<br />\r\nInterview Scheduled on: <strong>{{interview_datetime}}</strong><br />\r\nTime Zone: <strong>{{time_zone}}</strong><br />\r\nYou can convert time here: <a href=\"http://www.timezoneconverter.com/cgi-bin/tzc.tzc?style=2\" target=\"_blank\">www.timezoneconverter.com</a></p>\r\n\r\n<p>You will be interviewing with the following host company representative:<br />\r\n- Name: <strong>{{contact_person_name}}</strong>\r\n- Title: <strong>{{contact_title}}</strong></p>\r\n\r\n<p>Your interview will be conduct on zoom meeting, Please make sure you have install zoom software in your computer or zoom app on your mobile.</p>\r\n\r\n<p>Following details you need join zoom meeting.<br />\r\n<strong>Join from PC, Mac, Linux, iOS or Android:</strong><br />\r\n{{join_link}}<br />\r\n<strong>Note:</strong> This link should not be shared with others; it is unique to you.</p>\r\n\r\n<p>Meeting ID: <strong>{{meeting_id}}</strong><br />\r\nPassword: <strong>{{password}}</strong></p>\r\n\r\n<p>Click to add this meeting to your {{calendar_link}} calendar.</p>\r\n\r\n<p>{{confirmation_link}}</p>\r\n&nbsp;\r\n\r\n<p>Thank you,</p>\r\n</div>', '1', 'test_cc_qa@yopmail.com', '');

INSERT INTO `email_notification` (`id`, `en_key`, `subject`, `text`, `send_to`, `send_cc`, `status`) VALUES (NULL, 'hc_interview_schedule_to_hc', 'Confirm your interview schedule with [{{ev_name}}]', '<div>\r\n	<p>Dear {{hc_admin_name}},</p>\r\n	<p>\r\n        We are pleased to send you the schedule and details for your interview with <b>{{ev_name}}</b>.<br/>\r\n        Interview Scheduled on: <b>{{interview_datetime}}</b><br/>\r\n        Time Zone: <b>{{time_zone}}</b><br/> \r\n	</p>\r\n	<p>\r\n        You can convert time here: <a target=\"_blank\" href=\"http://www.timezoneconverter.com/cgi-bin/tzc.tzc?style=2\" >www.timezoneconverter.com</a>\r\n	</p>\r\n	<p>\r\n        Your interview will be conduct on zoom meeting, Please make sure you have install zoom software in your computer or zoom app on your mobile.\r\n	</p>\r\n	<p>Following details you need join zoom meeting.<br/>\r\n<b>Join from PC, Mac, Linux, iOS or Android:</b><br/>\r\n<span style=\"word-break: break-all;color: red;\">{{join_link}}</span><br/>\r\n<b>Note:</b> This link should not be shared with others; it is unique to you.\r\n    </p>\r\n    <p>\r\n    Meeting ID: <b>{{meeting_id}}</b><br/>\r\n    Password: <b>{{password}}</b><br/>\r\n    </p><br/>\r\n    <p>{{claim_host}}</p>\r\n    <p>Click to add this meeting to your {{calendar_link}} calendar.</p>\r\n    <p>{{confirmation_link}}</p><br/>\r\n    <p>Thank you,</p>\r\n</div>', '2', 'test_cc_qa@yopmail.com', '');

INSERT INTO `email_notification` (`id`, `en_key`, `subject`, `text`, `send_to`, `send_cc`, `status`) VALUES (NULL, 'hc_interview_rejected', 'Host Company Interview Rejected {{ev_name}} with {{hc_name}}', '<div>\r\n            <p>Dear admin,</p> \r\n            <p>\r\n            The Host Company interview scheduled for <b>{{ev_name}}</b> with <b>{{hc_name}}</b> has be rejected by <b>{{action_by}}</b>.\r\n            </p>\r\n            <p>\r\n            Following is the details of Host Company interview schedule.<br/>\r\n            Host Company: {{hc_name}}<br/>\r\n            Interviewer of HC: {{contact_person_name}}<br/>\r\n            EV Name: {{ev_name}}\r\n            </p>\r\n            <hr/>\r\n            <p>\r\n            <b>HC Interview Schedule On:</b><br/>\r\n            Date/Time: {{interview_datetime}}<br/>\r\n            Timezone: {{timezone}}\r\n            </p>\r\n            <hr/>\r\n            <p>\r\n            <b>Following is the reason of rejection:</b><br/>\r\n            {{reject_reason}}\r\n            </p>\r\n            {{alternate_datetime}}\r\n            <p>Thank You,</p>\r\n        </div>\r\n        ', '3', 'test_cc_qa@yopmail.com', '');
--------------------------------------------------------------------------------------------------------------------
--Hetal Visaveliya (27 August 2020)

UPDATE `as_steps` SET `j1_status_id` = '3002,3003' WHERE `as_steps`.`id` = 16;
DELETE FROM `as_steps` WHERE `as_steps`.`id` = 15;
 
DELETE FROM `as_steps_by_dos` WHERE `as_steps_by_dos`.`step_id` = 15; 

UPDATE `as_steps` SET `as_title` = 'Start your Program' WHERE `as_steps`.`id` = 22;
--------------------------------------------------------------------------------------------------------------------
--Manisha Odedara (28 August 2020)
INSERT INTO `notification_messages` (`id`, `notification_type_id`, `notification_type_key`, `notification_type_data`, `notification_text`, `notification_message`, `status`, `created_at`, `updated_at`) VALUES (33, '1', 'pending_document', 'pending_document', 'Pending Document', 'Please upload document', '1', '2019-07-22 18:30:00', '2020-01-09 05:47:19');
--------------------------------------------------------------------------------------------------------------------
--Nidhi Lakhani (31 August 2020)
INSERT INTO `permissions` (`id`, `parent_permission_id`, `permission_group_id`, `permission_name`, `display_name`, `description`, `route_name`, `is_menu_item`) VALUES (NULL, NULL, '11', 'zoom.interview.list', 'Manage Zoom Interview', 'Manage Zoom Interview', 'zoom.interview.list', '1');
INSERT INTO `email_notification` (`id`, `en_key`, `subject`, `text`, `send_to`, `send_cc`, `status`) VALUES (NULL, 'pre_screening_interview_canceled', 'Pre Screening Zoom Meeting Cancelled [Admin: {{admin_name}}] and [EV: {{ev_name}}]', '<p>Dear {{name}},</p>\r\n\r\n<p>We are sorry to inform you that your zoom meeting has been cancelled. It was scheduled on:<br />\r\n<strong>Date-time: {{datetime}}</strong><br />\r\n<strong>Time zone: {{timezone}}</strong></p>\r\n\r\n<p><strong>Between:</strong><br />\r\nEV Name: <strong>{{ev_name}}</strong><br />\r\nAdmin Name: <strong>{{admin_name}}</strong></p>\r\n\r\n<p><strong>The meeting was cancelled with the following reason:</strong><br />\r\n{{cancel_reason}}</p>\r\n\r\n<p>Click to remove this meeting from your {{cancel_cal_link}} calendar.</p>\r\n\r\n<p>Thank You,</p>', '3', 'test_cc_qa@yopmail.com', '');
UPDATE `notification_messages` SET `notification_message` = 'Your IMIX pre-screening Interview has been scheduled' WHERE `notification_messages`.`notification_type_key` = '1_itn_interview_msg_1';
ALTER TABLE `notification_log` CHANGE `notified_by_type` `notified_by_type` ENUM('user','admin','auto_admin') CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL COMMENT 'Options: User, Admin, Auto Admin';
ALTER TABLE ``notification_log`` DROP INDEX ``notified_by_type``;
--------------------------------------------------------------------------------------------------------------------
--Nidhi Lakhani (1 September 2020)
ALTER TABLE `users` ADD `new_email` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL AFTER `email_verified`, ADD `new_email_verified` TINYINT(1) NULL COMMENT '0 = not verified, 1 = verified' AFTER `new_email`;
INSERT INTO `email_notification` (`id`, `en_key`, `subject`, `text`, `send_to`, `send_cc`, `status`) VALUES (NULL, 'activate_user_account_with_new_email', 'Please activate your account with new email.', '<p>Dear {{first_name}} {{last_name}},<br />\r\n<br />\r\nYou have changed your email address. Please click on the link below to verify your new email address.<br />\r\n<a href=\"{{url}}\">Click Here</a><br />\r\n<br />\r\nOnce you verify your email address, your further process will be held with your new email address.</p>', 1, 'test_cc_qa@yopmail.com', 0);
CREATE TABLE `imix_email_log` (
  `id` int(11) NOT NULL,
  `en_id` int(5) NOT NULL COMMENT 'Ref key of email_notification >> en_id',
  `send_to_user_id` int(11) NOT NULL,
  `send_to_admin_id` int(11) NOT NULL,
  `send_for` int(11) NOT NULL COMMENT '1 = canidates,2 = admin,3 = both',
  `send_to` varchar(255) CHARACTER SET utf8 NOT NULL,
  `email_subject` varchar(255) CHARACTER SET utf8 NOT NULL COMMENT 'Ref key of email_notification >> subject',
  `email_text` mediumtext NOT NULL COMMENT 'Ref key of email_notification >> text',
  `timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `is_reminder` int(11) NOT NULL DEFAULT '0' COMMENT '0 = "No" and 1 = "Yes"'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `imix_email_log`
  ADD PRIMARY KEY (`id`),
  ADD KEY `send_to_user_id` (`send_to_user_id`),
  ADD KEY `send_to_admin_id` (`send_to_admin_id`);
ALTER TABLE `imix_email_log`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
--------------------------------------------------------------------------------------------------------------------
--Nidhi Lakhani (2 September 2020)
DROP TABLE `imix_email_log`;
--------------------------------------------------------------------------------------------------------------------
--Hetal Visaveliya (8 September 2020)

INSERT INTO `email_notification` (`id`, `en_key`, `subject`, `text`, `send_to`, `send_cc`, `status`) VALUES (NULL, 'registration_fee_payment_success', 'Registration Fee Payment Accepted', 'Dear {{ev_name}},<br />\r\nYour payment of the Registration Fee has been accepted.<br />\r\n<br />\r\n&nbsp;', '1', 'test_cc_qa@yopmail.com', '1');

ALTER TABLE `portfolio` ADD `accounting_status` INT(11) NOT NULL AFTER `user_id`;
 
CREATE TABLE `accounting_invoices` (
  `invoice_id` int(11) NOT NULL,
  `portfolio_id` int(11) NOT NULL,
  `multi_user` int(11) NOT NULL DEFAULT '0',
  `user_id` int(11) NOT NULL,
  `program_id` int(11) NOT NULL,
  `admin_id` int(11) NOT NULL,
  `invoice_type` int(1) NOT NULL COMMENT '(1=Registration) ( 2=Program_One) (3 OR 30=Program_Two(r66/coe))',
  `invoice_number` varchar(255) NOT NULL,
  `invoice_currency` int(11) NOT NULL,
  `invoice_total_amount` decimal(9,2) NOT NULL,
  `payment_type` int(11) NOT NULL COMMENT '1=No Plan 2=Plan',
  `invoice_amount_for` int(11) NOT NULL DEFAULT '0' COMMENT '1 = Full Payment, 2 = Plan Initial Payment, 3 = Plan Instalment Payment, 4 = Other Payment',
  `payment_plan_initial_amount` decimal(9,2) NOT NULL,
  `payment_plan_biweekly_amount` decimal(9,2) NOT NULL,
  `payment_plan_total_payments` int(11) NOT NULL,
  `payment_plan_agreement_collected` int(1) NOT NULL,
  `payment_plan_agreement_date_collected` date NOT NULL,
  `payment_plan_initiated` int(11) NOT NULL,
  `invoice_created_date` date NOT NULL,
  `invoice_due_date` date NOT NULL,
  `comment` varchar(255) NOT NULL,
  `void` int(1) NOT NULL,
  `is_locked` int(11) NOT NULL DEFAULT '0' COMMENT '1=locked,0=Un locked'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

ALTER TABLE `accounting_invoices`
  ADD PRIMARY KEY (`invoice_id`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `invoice_type` (`invoice_type`),
  ADD KEY `user_id_2` (`user_id`),
  ADD KEY `invoice_type_2` (`invoice_type`),
  ADD KEY `invoice_number` (`invoice_number`);

ALTER TABLE `accounting_invoices`
  MODIFY `invoice_id` int(11) NOT NULL AUTO_INCREMENT;

CREATE TABLE `accounting_status` (
  `accounting_status_id` int(11) NOT NULL,
  `partner_type_id` int(11) NOT NULL COMMENT '0= Other, 1 = ITN, 2 = ODC',
  `accounting_status_title` varchar(255) NOT NULL,
  `group_by` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `accounting_status` (`accounting_status_id`, `partner_type_id`, `accounting_status_title`, `group_by`) VALUES
(0, 0, 'Pending Review', 0),
(1, 1, 'Registration Fee <br> Invoiced', 1),
(2, 1, 'Registration Fee <br> Collected Not Verified', 1),
(3, 1, 'Registration Fee <br> Collected Verified', 1),
(52, 0, 'Status unknown', 12),
(61, 1, 'Registration Fee <br> Refunded', 1);

ALTER TABLE `accounting_status`
  ADD PRIMARY KEY (`accounting_status_id`);

ALTER TABLE `accounting_status`
  MODIFY `accounting_status_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=124;

CREATE TABLE `accounting_transactions` (
  `transaction_id` int(11) NOT NULL,
  `invoice_id` int(11) NOT NULL DEFAULT '0',
  `portfolio_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `program_id` int(11) NOT NULL,
  `admin_id` int(11) NOT NULL,
  `invoice_number` int(11) NOT NULL,
  `transaction_type` int(11) NOT NULL COMMENT '1=debit 2=credit',
  `transaction_currency` int(1) NOT NULL COMMENT '1=USD 2=EUR',
  `transaction_payment_type` int(1) NOT NULL COMMENT '1=full 2=initial 3=installment 4=other',
  `transaction_payment_method` int(11) NOT NULL COMMENT '1=bank 2=check 3=online 4=cash',
  `transaction_reference_number` varchar(255) NOT NULL,
  `transaction_amount` decimal(9,2) NOT NULL,
  `transaction_date` date NOT NULL,
  `transaction_verified` int(11) NOT NULL,
  `void` int(1) NOT NULL,
  `comment` varchar(255) NOT NULL,
  `is_locked` int(11) NOT NULL DEFAULT '0' COMMENT '1=locked,0=Un locked'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

ALTER TABLE `accounting_transactions`
  ADD PRIMARY KEY (`transaction_id`),
  ADD KEY `transaction_payment_method` (`transaction_payment_method`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `invoice_number` (`invoice_number`),
  ADD KEY `invoice_id` (`invoice_id`);

ALTER TABLE `accounting_transactions`
  MODIFY `transaction_id` int(11) NOT NULL AUTO_INCREMENT;

CREATE TABLE `user_payment_ipn` (
  `ipn_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL COMMENT 'Ref: user_table >> user_id',
  `portfolio_id` int(11) NOT NULL,
  `invoice_id` int(11) NOT NULL COMMENT 'Ref of accounting_invoices >> invoice_id',
  `transaction_id` int(11) NOT NULL COMMENT 'Ref of accounting_transactions >> transaction_id',
  `payer_id` varchar(50) NOT NULL,
  `payer_status` varchar(255) NOT NULL,
  `payer_email` varchar(255) NOT NULL,
  `receiver_email` varchar(255) NOT NULL,
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  `verify_sign` varchar(255) NOT NULL,
  `txn_id` varchar(50) NOT NULL,
  `receiver_id` varchar(50) NOT NULL,
  `ipn_track_id` varchar(50) NOT NULL,
  `ipn_data` text NOT NULL,
  `payment_date` varchar(50) NOT NULL,
  `payment_status` varchar(50) NOT NULL,
  `payment_type` varchar(50) NOT NULL,
  `payment_fee` float NOT NULL,
  `payment_gross` float NOT NULL,
  `is_paypal_verified` varchar(50) NOT NULL,
  `is_locked` tinyint(4) NOT NULL COMMENT '0 = Unlocked, 1 = Locked',
  `created_at` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `user_payment_ipn`
  ADD PRIMARY KEY (`ipn_id`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `invoice_id` (`invoice_id`),
  ADD KEY `transaction_id` (`transaction_id`);

ALTER TABLE `user_payment_ipn`
  MODIFY `ipn_id` int(11) NOT NULL AUTO_INCREMENT;

--------------------------------------------------------------------------------------------------------------------
--Manisha Odedara (10 September 2020)
INSERT INTO `system_settings` (`id`, `name`, `field`, `value`, `description`) VALUES (NULL, 'Agreement Content', 'agreement_content', '', 'Agreement Content');

TRUNCATE TABLE `notification_messages`;

INSERT INTO `notification_messages` (`id`, `notification_type_id`, `notification_type_key`, `notification_type_data`, `notification_text`, `notification_message`, `status`, `created_at`, `updated_at`) VALUES
(1, 4, '1_resume_upload', '1_resume_upload', 'Application Status: Resume Upload', 'You completed your eligibility test successfully, Now please upload your resume.', 1, '2019-07-22 13:00:00', '2020-09-09 07:22:59'),
(2, 4, '1_resume_approval', '1_resume_approval', 'Application Status: Resume Status', 'Your Resume has been {{document_status}}', 1, '2019-07-22 13:00:00', '2020-09-09 07:23:03'),
(3, 4, '1_skype', '1_skype', 'Application Status: Skype', 'Please enter your skype Id', 1, '2019-07-22 13:00:00', '2019-07-22 13:00:00'),
(4, 4, '1_j1_interview_msg_1', '1_j1_interview', 'Application Status: Pre-Screen Interview', 'Your IMIX pre-screening Interview has been scheduled', 1, '2019-07-22 13:00:00', '2020-09-09 07:23:22'),
(5, 4, '1_j1_interview_msg_2', '1_j1_interview', 'Application Status: Pre-Screen Interview', 'You are not eligible for the J1 program', 1, '2019-07-22 13:00:00', '2020-09-09 07:25:52'),
(6, 4, '1_j1_interview_msg_3', '1_j1_interview', 'Application Status: Pre-Screen Interview', 'Your pre-screening interview has been completed successfully', 1, '2019-07-22 13:00:00', '2020-09-09 07:26:00'),
(7, 4, '1_registration_fee', '1_registration_fee', 'Application Status: Registration Fee', 'Your registration fee has been collected and verified', 1, '2019-07-22 13:00:00', '2019-07-22 13:00:00'),
(8, 4, '1_additional_info', '1_additional_info', 'Application Status: Additional Information', 'Please enter your missing information on your profile', 1, '2019-07-22 13:00:00', '2020-01-08 23:08:48'),
(9, 4, '2_searching_position_msg_1', '2_searching_position', 'Application Status: Searching Position', 'Get ready for interview with Host Company', 1, '2019-07-22 13:00:00', '2020-09-09 07:30:25'),
(10, 4, '2_searching_position_msg_2', '2_searching_position', 'Application Status: Searching Position', 'Not Selected by Host company', 1, '2019-07-22 13:00:00', '2020-09-09 07:30:19'),
(11, 4, '2_searching_position_msg_3', '2_searching_position', 'Application Status: Searching Position', 'Rejected by Host Company', 1, '2019-07-22 13:00:00', '2020-09-09 07:30:11'),
(12, 4, '2_booked_msg_1', '2_booked', 'Application Status: Host Company Reviewing/Interview', 'One host company is reviewing your profile', 1, '2019-07-22 13:00:00', '2020-09-09 07:30:46'),
(13, 4, '2_booked_msg_2', '2_booked', 'Application Status: Host Company Reviewing/Interview', 'Your interview with {{hc_name}} has been scheduled successfully', 1, '2019-07-22 13:00:00', '2020-09-09 07:30:51'),
(14, 4, '2_placed', '2_placed', 'Application Status: Placement', 'Congratulation! {{hc_name}} has confirmed your placement', 1, '2019-07-22 13:00:00', '2020-09-09 07:31:07'),
(15, 4, '3_ds7002_created', '3_ds7002_created', 'Application Status: DS7002 Training Plan', 'Your training placement plan (DS7002) was created for your training at {{hc_name}}', 0, '2019-07-22 13:00:00', '2020-09-09 07:31:26'),
(16, 4, '3_ds7002_signed', '3_ds7002_signed', 'Application Status: DS7002 Training Plan Signed', 'Your training placement plan (DS7002) has been signed successfully', 0, '2019-07-22 13:00:00', '2020-09-09 07:31:38'),
(17, 4, '3_ds2019_sent', '3_ds2019_sent', 'Application Status: DS2019 Sent', 'Your DS2019 form has been sent', 1, '2019-07-22 13:00:00', '2020-09-09 07:31:48'),
(18, 4, '3_predeparture_orientation_msg_1', '3_predeparture_orientation', 'Application Status: Pre-Departure Orientation', 'Pre Departure Orientation Link Sent', 1, '2019-07-22 13:00:00', '2020-09-09 07:32:21'),
(19, 4, '3_predeparture_orientation_msg_2', '3_predeparture_orientation', 'Application Status: Pre-Departure Orientation', 'You have {{result}} your orientation quiz test', 1, '2019-07-22 13:00:00', '2020-09-09 07:32:14'),
(20, 4, '3_us_embassy_interview', '3_us_embassy_interview', 'Application Status: Embassy Interview', 'Please fill up your embassy interview schedule details', 1, '2019-07-22 13:00:00', '2020-09-09 07:32:35'),
(21, 4, '3_us_visa_msg_1', '3_us_visa_outcome', 'Application Status: US Embassy Outcome', 'Please add the outcome of your embassy interview', 1, '2019-07-22 13:00:00', '2020-09-09 07:33:15'),
(22, 4, '3_us_visa_msg_2', '3_us_visa_outcome', 'Application Status: US Embassy Outcome', 'Please quit your program due to consequent visa rejection', 1, '2019-07-22 13:00:00', '2020-09-09 07:33:09'),
(23, 4, '3_flight_info', '3_flight_info', 'Application Status: Flight Information', 'Please update your arrival information', 1, '2019-07-22 13:00:00', '2020-09-09 07:33:43'),
(24, 4, '3_arrival_in_usa', '3_arrival_in_usa', 'Application Status: Arrived', 'Welcome to USA', 0, '2019-07-22 13:00:00', '2020-09-09 07:09:23'),
(25, 4, '4_arrival_check_in', '4_arrival_check_in', 'Application Status: Arrival Check In', 'Please complete your arrival check in', 1, '2019-07-22 13:00:00', '2020-09-09 07:41:46'),
(26, 4, '4_monthly_check_in', '4_monthly_check_in', 'Application Status: Monthly Check In', 'Please complete your monthly check in', 1, '2019-07-22 13:00:00', '2020-09-09 07:41:59'),
(27, 4, '4_mid_super_evaluation', '4_mid_super_evaluation', 'Application Status: Midterm Supervisor Evaluation', 'Please review your midterm supervisor evaluation', 1, '2019-07-22 13:00:00', '2020-09-09 07:42:55'),
(28, 4, '4_final_super_evaluation', '4_final_super_evaluation', 'Application Status: Final Supervisor Evaluation', 'Please review your final term supervisor evaluation', 1, '2019-07-22 13:00:00', '2020-09-09 07:43:17'),
(29, 9, 'other_email_verification', 'other_email_verification', 'Email Verification', 'Your email verified successfully!', 1, '2019-07-22 13:00:00', '2020-01-08 23:11:00'),
(30, 9, 'status_on_hold', 'other_status_update', 'Alert! Status Update', 'Your application is on hold', 0, '2019-07-22 13:00:00', '2020-07-23 12:24:02'),
(31, 9, 'status_pla_cancelled', 'other_status_update', 'Alert! Status Update', 'Sorry! Your Placement was cancelled by Host Company', 1, '2019-07-22 13:00:00', '2020-01-09 00:17:17'),
(32, 9, 'other_document_updates', 'other_document_updates', 'Document Updates', 'Your {{document_name}} document is {{action}}', 1, '2019-07-22 13:00:00', '2020-01-09 00:17:19'),
(33, 4, '2_supporting_documents', '2_supporting_documents', 'Pending Document', 'Please upload document', 1, '2019-07-22 13:00:00', '2020-09-09 07:44:00');
---------------------------------------------------------------------------------------------------------------------
--Hetal Visaveliya (14 September 2020)

CREATE TABLE `field_names` (
  `id` int(11) NOT NULL,
  `interface` int(11) NOT NULL,
  `field_type` int(11) NOT NULL,
  `group_id` int(11) NOT NULL,
  `field_id` int(11) NOT NULL,
  `field_name` varchar(255) NOT NULL,
  `field_order` int(11) NOT NULL DEFAULT '0',
  `is_program_fee` int(11) NOT NULL COMMENT '0 = No and 1 = yes',
  `field_name_alt` varchar(255) NOT NULL,
  `status` int(1) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `field_names` (`id`, `interface`, `field_type`, `group_id`, `field_id`, `field_name`, `field_order`, `is_program_fee`, `field_name_alt`, `status`) VALUES
(1, 1, 1, 1, 1, 'Registration Fee', 1, 0, '', 1);

ALTER TABLE `field_names`
  ADD PRIMARY KEY (`id`),
  ADD KEY `field_type` (`field_type`),
  ADD KEY `field_id` (`field_id`),
  ADD KEY `field_type_2` (`field_type`),
  ADD KEY `field_id_2` (`field_id`);

ALTER TABLE `field_names`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

---------------------------------------------------------------------------------------------------------------------
--Manisha Odedara (14 September 2020)
INSERT INTO `system_settings` (`id`, `name`, `field`, `value`, `description`) VALUES (NULL, 'Paypal Setting', 'paypal_setting', '{\"paypal_mode\":\"1\",\"paypal_live_business_id\":\"manisha@odcitservices.com\",\"paypal_live_url\":\"https:\\/\\/www.paypal.com\\/cgi-bin\\/webscr\",\"paypal_sandbox_business_id\":\"hetal@odcitservices.com\",\"paypal_sandbox_url\":\"https:\\/\\/www.paypal.com\\/cgi-bin\\/webscr\"}', 'Paypal Credentials');
-----------------------------------------------------------------------------------------------------------------------
--Nidhi Lakhani (23 September 2020)
INSERT INTO `permissions` (`id`, `parent_permission_id`, `permission_group_id`, `permission_name`, `display_name`, `description`, `route_name`, `is_menu_item`) VALUES (NULL, NULL, '15', 'accounting.invoice.list', 'All Invoices', 'All Invoices', 'accounting.invoice.list', '1');
INSERT INTO `permissions` (`id`, `parent_permission_id`, `permission_group_id`, `permission_name`, `display_name`, `description`, `route_name`, `is_menu_item`) VALUES (NULL, NULL, '15', 'accounting.invoice.search', 'All Invoices', 'All Invoices', 'accounting.invoice.search', '0');
-----------------------------------------------------------------------------------------------------------------------
--Manisha Odedara (23 September 2020)
ALTER TABLE `agency` ADD `presentation` TEXT NULL DEFAULT NULL AFTER `agency_api_key`, ADD `website` VARCHAR(100) NULL DEFAULT NULL AFTER `presentation`;
DELETE FROM `notification_messages` WHERE `notification_messages`.`notification_type_key` = '2_supporting_documents';
INSERT INTO `notification_messages` (`id`, `notification_type_id`, `notification_type_key`, `notification_type_data`, `notification_text`, `notification_message`, `status`, `created_at`, `updated_at`) VALUES (NULL, '4', '2_supporting_documents', '2_supporting_documents', 'Application Status: Supporting Documents', 'Please upload pending supporting documents', '1', '2019-07-22 13:00:00', '2020-09-16 11:31:15');

INSERT INTO `permissions` (`id`, `parent_permission_id`, `permission_group_id`, `permission_name`, `display_name`, `description`, `route_name`, `is_menu_item`) VALUES (NULL, NULL, '15', 'at.list', 'Accounting Transactions', 'Accounting Transactions', 'at.list', '1');
INSERT INTO `permissions` (`id`, `parent_permission_id`, `permission_group_id`, `permission_name`, `display_name`, `description`, `route_name`, `is_menu_item`) VALUES (NULL, NULL, '15', 'at.search', 'Accounting Transactions Filter', 'Accounting Transactions Filter', 'at.search', '0');
INSERT INTO `permissions` (`id`, `parent_permission_id`, `permission_group_id`, `permission_name`, `display_name`, `description`, `route_name`, `is_menu_item`) VALUES (NULL, NULL, '15', 'at.delete', 'Delete Accounting Transactions', 'Delete Accounting Transactions', 'at.delete', '0');
INSERT INTO `permissions` (`id`, `parent_permission_id`, `permission_group_id`, `permission_name`, `display_name`, `description`, `route_name`, `is_menu_item`) VALUES (NULL, NULL, '15', 'at.edit.form', 'Edit Accounting Transactions Form', 'Edit Accounting Transactions Form', 'at.edit.form', '0');
INSERT INTO `permissions` (`id`, `parent_permission_id`, `permission_group_id`, `permission_name`, `display_name`, `description`, `route_name`, `is_menu_item`) VALUES (NULL, NULL, '15', 'at.edit', 'Edit Accounting Transactions', 'Edit Accounting Transactions', 'at.edit', '0');
INSERT INTO `permissions` (`id`, `parent_permission_id`, `permission_group_id`, `permission_name`, `display_name`, `description`, `route_name`, `is_menu_item`) VALUES (NULL, NULL, '15', 'at.add.form', 'Add Accounting Transactions Form', 'Add Accounting Transactions Form', 'at.add.form', '0');
INSERT INTO `permissions` (`id`, `parent_permission_id`, `permission_group_id`, `permission_name`, `display_name`, `description`, `route_name`, `is_menu_item`) VALUES (NULL, NULL, '15', 'at.add', 'Add Accounting Transactions', 'Add Accounting Transactions', 'at.add', '0');

---------------------------------------------------------------------------------------------------------------------
--Heal Visaveliya (18 September 2020)

INSERT INTO `permissions` (`id`, `parent_permission_id`, `permission_group_id`, `permission_name`, `display_name`, `description`, `route_name`, `is_menu_item`) VALUES (NULL, NULL, '5', 'agency.feestructure.form', 'Agency Fee Structure Form', 'Agency Fee Structure Form', 'agency.feestructure.form', 1);

INSERT INTO `permissions` (`id`, `parent_permission_id`, `permission_group_id`, `permission_name`, `display_name`, `description`, `route_name`, `is_menu_item`) VALUES (NULL, NULL, '5', 'agency.feestructure', 'Store Agency Fee Structure', 'Store Agency Fee Structure', 'agency.feestructure', 0);

CREATE TABLE `agency_fee_structure` (
  `id` int(11) NOT NULL,
  `agency_id` int(11) NOT NULL,
  `fee_year` varchar(255) NOT NULL,
  `stage_2_price` decimal(10,2) DEFAULT '0.00',
  `stage_3_price` decimal(10,2) DEFAULT '0.00',
  `stage_2_3_price` decimal(10,2) DEFAULT '0.00',
  `stage_3_4_price` decimal(10,2) DEFAULT '0.00',
  `stage_2_3_4_price` decimal(10,2) DEFAULT '0.00',
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
ALTER TABLE `agency_fee_structure`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `agency_fee_structure`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
-----------------------------------------------------------------------------------------------------------------------
--Manisha Odedara (25 September 2020)
ALTER TABLE `accounting_transactions` CHANGE `portfolio_id` `portfolio_id` INT(11) NULL DEFAULT NULL, CHANGE `user_id` `user_id` INT(11) NULL DEFAULT NULL, CHANGE `program_id` `program_id` INT(11) NULL DEFAULT NULL, CHANGE `admin_id` `admin_id` INT(11) NULL DEFAULT NULL, CHANGE `transaction_type` `transaction_type` INT(11) NULL DEFAULT NULL COMMENT '1=debit 2=credit', CHANGE `transaction_currency` `transaction_currency` INT(1) NULL DEFAULT NULL COMMENT '1=USD 2=EUR', CHANGE `transaction_payment_type` `transaction_payment_type` INT(1) NULL DEFAULT NULL COMMENT '1=full 2=initial 3=installment 4=other', CHANGE `transaction_payment_method` `transaction_payment_method` INT(11) NULL DEFAULT NULL COMMENT '1=bank 2=check 3=online 4=cash', CHANGE `transaction_reference_number` `transaction_reference_number` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, CHANGE `transaction_amount` `transaction_amount` DECIMAL(9,2) NULL DEFAULT NULL, CHANGE `transaction_date` `transaction_date` DATE NULL DEFAULT NULL, CHANGE `transaction_verified` `transaction_verified` INT(11) NULL DEFAULT NULL, CHANGE `void` `void` INT(1) NULL DEFAULT NULL, CHANGE `comment` `comment` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;
ALTER TABLE `accounting_transactions` CHANGE `invoice_number` `invoice_number` VARCHAR(50) NULL DEFAULT NULL;
----------------------------------------------------------------------------------------------------------------------------------------------------------
--Nidhi Lakhani (28 September 2020)
INSERT INTO `permissions` (`id`, `parent_permission_id`, `permission_group_id`, `permission_name`, `display_name`, `description`, `route_name`, `is_menu_item`) VALUES (NULL, NULL, '15', 'add.invoice.form', 'Add Accounting Invoice Form', 'Add Accounting Invoice Form', 'add.invoice.form', '0');
INSERT INTO `permissions` (`id`, `parent_permission_id`, `permission_group_id`, `permission_name`, `display_name`, `description`, `route_name`, `is_menu_item`) VALUES (NULL, NULL, '15', 'add.invoice', 'Add Accounting Invoice', 'Add Accounting Invoice', 'add.invoice', '0');
INSERT INTO `permissions` (`id`, `parent_permission_id`, `permission_group_id`, `permission_name`, `display_name`, `description`, `route_name`, `is_menu_item`) VALUES (NULL, NULL, '15', 'ai.edit.form', 'Edit Accounting Invoice Form', 'Edit Accounting Invoice Form', 'ai.edit.form', '0');
INSERT INTO `permissions` (`id`, `parent_permission_id`, `permission_group_id`, `permission_name`, `display_name`, `description`, `route_name`, `is_menu_item`) VALUES (NULL, NULL, '15', 'ai.edit', 'Edit Accounting Invoice', 'Edit Accounting Invoice', 'ai.edit', '0');
INSERT INTO `permissions` (`id`, `parent_permission_id`, `permission_group_id`, `permission_name`, `display_name`, `description`, `route_name`, `is_menu_item`) VALUES (NULL, NULL, '15', 'ai.delete', 'Delete Accounting Invoice', 'Delete Accounting Invoice', 'ai.delete', '0');
INSERT INTO `permissions` (`id`, `parent_permission_id`, `permission_group_id`, `permission_name`, `display_name`, `description`, `route_name`, `is_menu_item`) VALUES (NULL, NULL, '15', 'ai.void', 'Void Accounting Invoice', 'Void Accounting Invoice', 'ai.void', '0');
----------------------------------------------------------------------------------------------------------------------------------------------------------
--Manisha Odedara (2 October 2020)
INSERT INTO `j1_statuses` (`id`, `status_key`, `status_name`, `category`) VALUES ('3014', 'predeparture-orientation', 'Predeparture Orientation Quiz', '3');
-------------------------------------------------------------------------------------------------------------------------------------------------------------
--Hetal Visaveliya (05 October 2020)

CREATE TABLE `sponsor` (
  `id` int(11) NOT NULL,
  `sponsor_name` varchar(255) NOT NULL,
  `sponsor_full_name` varchar(255) NOT NULL,
  `sponsor_status` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
INSERT INTO `sponsor` (`id`, `sponsor_name`, `sponsor_full_name`, `sponsor_status`) VALUES
(1, 'ASSE', 'ASSE', 1),
(2, 'CENET', 'CENET', 1),
(3, 'INTRAX', 'INTRAX', 1),
(4, 'INTRAX Co-branded', 'INTRAX Co-branded', 1),
(5, 'AAG', 'AAG', 1),
(6, 'ODYSSEY', 'ODYSSEY International Exchange', 1),
(7, 'ODYSSEY CAMP', 'ODYSSEY CAMP', 1);

ALTER TABLE `sponsor`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `sponsor`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

CREATE TABLE `agency_sponsor` (
  `agency_id` int(11) NOT NULL COMMENT 'admins -> id',
  `sponsor_id` int(11) NOT NULL COMMENT 'sponsor -> id'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `agency_sponsor`
  ADD KEY `agency_id` (`agency_id`),
  ADD KEY `sponsor_id` (`sponsor_id`);

DROP TABLE `agency_fee_structure`;

CREATE TABLE `agency_fee_structure` (
  `id` int(11) NOT NULL,
  `agency_id` int(11) NOT NULL,
  `sponsor_id` int(11) DEFAULT '0',
  `fee_year` varchar(255) NOT NULL,
  `stage_2_price` varchar(255) DEFAULT NULL,
  `stage_3_4_price` varchar(255) DEFAULT NULL,
  `stage_2_3_4_price` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `agency_fee_structure`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `agency_fee_structure`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
-------------------------------------------------------------------------------------------------------------------------------------------------------------
--Manisha odedara(06 October 2020)
INSERT INTO `permissions` (`id`, `parent_permission_id`, `permission_group_id`, `permission_name`, `display_name`, `description`, `route_name`, `is_menu_item`) VALUES (NULL, NULL, '5', 'sponsor.list', 'Sponsor List', 'Sponsor List', 'sponsor.list', '1');
INSERT INTO `permissions` (`id`, `parent_permission_id`, `permission_group_id`, `permission_name`, `display_name`, `description`, `route_name`, `is_menu_item`) VALUES (NULL, NULL, '5', 'sponsor.filter', 'Filter Sponsor', 'Filter Sponsor', 'sponsor.filter', '0');
INSERT INTO `permissions` (`id`, `parent_permission_id`, `permission_group_id`, `permission_name`, `display_name`, `description`, `route_name`, `is_menu_item`) VALUES (NULL, NULL, '5', 'sponsor.add.form', 'Sponsor Add Form', 'Sponsor Add Form', 'sponsor.add.form', '0');
INSERT INTO `permissions` (`id`, `parent_permission_id`, `permission_group_id`, `permission_name`, `display_name`, `description`, `route_name`, `is_menu_item`) VALUES (NULL, NULL, '5', 'sponsor.add', 'Add Sponsor', 'Add Sponsor', 'sponsor.add', '0');
INSERT INTO `permissions` (`id`, `parent_permission_id`, `permission_group_id`, `permission_name`, `display_name`, `description`, `route_name`, `is_menu_item`) VALUES (NULL, NULL, '5', 'sponsor.edit.form', 'Sponsor Edit Form', 'Sponsor Edit Form', 'sponsor.edit.form', '0');
INSERT INTO `permissions` (`id`, `parent_permission_id`, `permission_group_id`, `permission_name`, `display_name`, `description`, `route_name`, `is_menu_item`) VALUES (NULL, NULL, '5', 'sponsor.edit', 'Edit Sponsor', 'Edit Sponsor', 'sponsor.edit', '0');
INSERT INTO `permissions` (`id`, `parent_permission_id`, `permission_group_id`, `permission_name`, `display_name`, `description`, `route_name`, `is_menu_item`) VALUES (NULL, NULL, '5', 'sponsor.delete', 'Delete Sponsor', 'Delete Sponsor', 'sponsor.delete', '0');
INSERT INTO `permissions` (`id`, `parent_permission_id`, `permission_group_id`, `permission_name`, `display_name`, `description`, `route_name`, `is_menu_item`) VALUES (NULL, NULL, '5', 'sponsor.ajax', 'Update Sponsor Status', 'Update Sponsor Status', 'sponsor.ajax', '0');

ALTER TABLE `sponsor` ADD `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `sponsor_status`, ADD `updated_at` TIMESTAMP on update CURRENT_TIMESTAMP NOT NULL AFTER `created_at`;
----------------------------------------------------------------------------------------------------------------------
--Manisha odedara(07 October 2020)

INSERT INTO `permission_group` (`id`, `display_name`, `description`, `is_menu_section`, `menu_description`, `menu_order`, `icon_class`, `created_at`, `updated_at`) VALUES ('16', 'Programs', 'Programs', '1', 'Manage Programs', '1', 'mdi mdi-view-list fa-fw', '2019-12-24 10:33:10', '2020-07-01 05:20:39');
INSERT INTO `permissions` (`id`, `parent_permission_id`, `permission_group_id`, `permission_name`, `display_name`, `description`, `route_name`, `is_menu_item`) VALUES (NULL, NULL, '16', 'pc.list', 'Program category List', 'Program category List', 'pc.list', '1');
INSERT INTO `permissions` (`id`, `parent_permission_id`, `permission_group_id`, `permission_name`, `display_name`, `description`, `route_name`, `is_menu_item`) VALUES (NULL, NULL, '16', 'pc.filter', 'Filter Program category', 'Filter Program category', 'pc.filter', '0');
INSERT INTO `permissions` (`id`, `parent_permission_id`, `permission_group_id`, `permission_name`, `display_name`, `description`, `route_name`, `is_menu_item`) VALUES (NULL, NULL, '16', 'pc.add.form', 'Program category Add Form', 'Program category Add Form', 'pc.add.form', '0');
INSERT INTO `permissions` (`id`, `parent_permission_id`, `permission_group_id`, `permission_name`, `display_name`, `description`, `route_name`, `is_menu_item`) VALUES (NULL, NULL, '16', 'pc.add', 'Add Program category', 'Add Program category', 'pc.add', '0');
INSERT INTO `permissions` (`id`, `parent_permission_id`, `permission_group_id`, `permission_name`, `display_name`, `description`, `route_name`, `is_menu_item`) VALUES (NULL, NULL, '16', 'pc.edit.form', 'Program category Edit Form', 'Program category Edit Form', 'pc.edit.form', '0');
INSERT INTO `permissions` (`id`, `parent_permission_id`, `permission_group_id`, `permission_name`, `display_name`, `description`, `route_name`, `is_menu_item`) VALUES (NULL, NULL, '16', 'pc.edit', 'Edit Program category', 'Edit Program category', 'pc.edit', '0');
INSERT INTO `permissions` (`id`, `parent_permission_id`, `permission_group_id`, `permission_name`, `display_name`, `description`, `route_name`, `is_menu_item`) VALUES (NULL, NULL, '16', 'pc.delete', 'Delete Program category', 'Delete Program category', 'pc.delete', '0');
INSERT INTO `permissions` (`id`, `parent_permission_id`, `permission_group_id`, `permission_name`, `display_name`, `description`, `route_name`, `is_menu_item`) VALUES (NULL, NULL, '16', 'pc.ajax', 'Update Program category Status', 'Update Program category Status', 'pc.ajax', '0');

ALTER TABLE `program_category` ADD `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `category_order`, ADD `updated_at` TIMESTAMP on update CURRENT_TIMESTAMP NOT NULL AFTER `created_at`;
ALTER TABLE `programs` ADD `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `status`, ADD `updated_at` TIMESTAMP on update CURRENT_TIMESTAMP NOT NULL AFTER `created_at`;

INSERT INTO `permissions` (`id`, `parent_permission_id`, `permission_group_id`, `permission_name`, `display_name`, `description`, `route_name`, `is_menu_item`) VALUES (NULL, NULL, '16', 'p.list', 'Program List', 'Program List', 'p.list', '1');
INSERT INTO `permissions` (`id`, `parent_permission_id`, `permission_group_id`, `permission_name`, `display_name`, `description`, `route_name`, `is_menu_item`) VALUES (NULL, NULL, '16', 'p.filter', 'Filter Program', 'Filter Program', 'p.filter', '0');
INSERT INTO `permissions` (`id`, `parent_permission_id`, `permission_group_id`, `permission_name`, `display_name`, `description`, `route_name`, `is_menu_item`) VALUES (NULL, NULL, '16', 'p.add.form', 'Program Add Form', 'Program Add Form', 'p.add.form', '0');
INSERT INTO `permissions` (`id`, `parent_permission_id`, `permission_group_id`, `permission_name`, `display_name`, `description`, `route_name`, `is_menu_item`) VALUES (NULL, NULL, '16', 'p.add', 'Add Program', 'Add Program', 'p.add', '0');
INSERT INTO `permissions` (`id`, `parent_permission_id`, `permission_group_id`, `permission_name`, `display_name`, `description`, `route_name`, `is_menu_item`) VALUES (NULL, NULL, '16', 'p.edit.form', 'Program Edit Form', 'Program Edit Form', 'p.edit.form', '0');
INSERT INTO `permissions` (`id`, `parent_permission_id`, `permission_group_id`, `permission_name`, `display_name`, `description`, `route_name`, `is_menu_item`) VALUES (NULL, NULL, '16', 'p.edit', 'Edit Program', 'Edit Program', 'p.edit', '0');
INSERT INTO `permissions` (`id`, `parent_permission_id`, `permission_group_id`, `permission_name`, `display_name`, `description`, `route_name`, `is_menu_item`) VALUES (NULL, NULL, '16', 'p.delete', 'Delete Program', 'Delete Program', 'p.delete', '0');
INSERT INTO `permissions` (`id`, `parent_permission_id`, `permission_group_id`, `permission_name`, `display_name`, `description`, `route_name`, `is_menu_item`) VALUES (NULL, NULL, '16', 'p.ajax', 'Update Program Status', 'Update Program Status', 'p.ajax', '0');

----------------------------------------------------------------------------------------------------------------------
--Hetal Visaveliya(19 Oct 2020)

DROP TABLE `programs`;

CREATE TABLE `programs` (
  `id` int(11) NOT NULL,
  `program_name` varchar(200) NOT NULL,
  `program_enroll_id` int(11) DEFAULT NULL,
  `dos_program_category_id` int(11) NOT NULL COMMENT 'Ref key of dos_program_category table',
  `status` int(11) NOT NULL,
  `category` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `programs` (`id`, `program_name`, `program_enroll_id`, `dos_program_category_id`, `status`, `category`) VALUES
(1, 'Intern - Current Student', NULL, 1, 1, 1),
(2, 'Intern - Recent Graduate', NULL, 1, 1, 1),
(3, 'Trainee - Recent Graduate', NULL, 2, 1, 1),
(4, 'Trainee - Young Professional', NULL, 2, 1, 1),
(5, 'J1 - Change of Site of Activity', NULL, 0, 1, 1),
(6, 'J1 - Change of Site of Activity Extension', NULL, 0, 1, 1),
(7, 'J2', NULL, 0, 1, 6),
(8, 'Placement Only ', NULL, 0, 1, 4),
(9, 'Placement Only - Extension', NULL, 0, 0, 4),
(10, 'Placement Only - Change of Site of Activity Extension', NULL, 0, 0, 4),
(11, 'Filing Only/Intern', NULL, 1, 1, 5),
(12, 'Filing Only/Trainee', NULL, 2, 1, 5),
(13, 'Route 66 / Int - Rec Grad', 2, 1, 1, 3),
(14, 'Route 66 / Trn - Rec Grad', 3, 2, 1, 3),
(15, 'Route 66 / Int - Current Stud', 1, 1, 1, 3),
(16, 'Route 66 / Trn - Young Pro', 4, 2, 1, 3),
(17, 'Route 66 / Placement Only', 8, 0, 1, 3),
(18, 'Placement Only - W&T', NULL, 0, 1, 4),
(19, 'Extension', NULL, 0, 1, 1),
(20, 'Filing Only/Extension', NULL, 0, 1, 5),
(21, 'Placement Only - Intrax Peru', NULL, 0, 1, 4),
(22, 'Route 66 (2) / Change of Site of Activity', 5, 0, 1, 3),
(23, 'Route 66 (1) / Change of Site of Activity', 5, 0, 1, 3),
(24, 'ODC Filing Only - INT Rec Grad', NULL, 1, 1, 5),
(25, 'ODC Filing Only - INT Current Stud', NULL, 1, 1, 5),
(26, 'ODC Filing Only - TRN Young Pro', NULL, 2, 1, 5),
(27, 'ODC Filing Only - TRN Rec Grad', NULL, 2, 1, 5),
(28, 'ODC Filing Only - Change of Site of Activity', NULL, 0, 1, 5),
(29, 'ODC - DS Reprint', NULL, 0, 1, 6),
(30, 'ODC Filing Only - Change of Site of Activity Extension', NULL, 0, 1, 5),
(31, 'ODC Filing Only - Extension', NULL, 0, 1, 5),
(32, 'ODC &ndash; R66 Filling Only', NULL, 0, 1, 3),
(33, 'Filing Only - Change of Site of Activity Extension', NULL, 0, 1, 5),
(34, 'Route 66 (2) - Extension', 19, 0, 1, 3),
(35, 'Route 66 (2) / Change of Site of Activity - Extension', 6, 0, 1, 3);

ALTER TABLE `programs` ADD PRIMARY KEY (`id`);

ALTER TABLE `programs` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=36;

DROP TABLE `agency_fee_structure`;
CREATE TABLE `agency_fee_structure` (
  `id` int(11) NOT NULL,
  `agency_id` int(11) NOT NULL,
  `sponsor_id` int(11) DEFAULT '0',
  `fee_year` varchar(255) NOT NULL,
  `stage_2_price` varchar(255) DEFAULT NULL,
  `stage_3_4_price` varchar(255) DEFAULT NULL,
  `stage_2_3_4_price` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
ALTER TABLE `agency_fee_structure`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `agency_fee_structure`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

----------------------------------------------------------------------------------------------------------------------
-- Hetal Visaveliya(03 Feb 2021)

INSERT INTO `dos_program_category` (`id`, `program_category_name`, `program_number`, `PositionCode`, `CategoryCode`) VALUES ('3', 'ODC CAMP', '', NULL, NULL), ('4', 'W&T', '', NULL, NULL); 

INSERT INTO `j1_statuses` (`id`, `status_key`, `status_name`, `category`) VALUES ('3015', 'create-job-offer-letter', 'Job Offer Letter Signed', '3');

INSERT INTO `j1_statuses` (`id`, `status_key`, `status_name`, `category`) VALUES ('3016', 'job-offer-letter-signed', 'Job Offer Letter Signed', '3');

INSERT INTO `as_steps` (`id`, `as_order_key`, `as_title`, `as_icon`, `as_desc_before`, `as_desc_current`, `as_desc_after`, `j1_status_id`) VALUES (NULL, '3_create_job_offer_letter', 'Create Job Offer Letter', 'fa-home', 'Create Job Offer Letter', 'Create Job Offer Letter', 'Create Job Offer Letter', '3002');

INSERT INTO `as_steps` (`id`, `as_order_key`, `as_title`, `as_icon`, `as_desc_before`, `as_desc_current`, `as_desc_after`, `j1_status_id`) VALUES (NULL, '3_job_offer_letter_signed', 'Job Offer Letter Signed', 'fa-home', 'Job Offer Letter Signed', 'Job Offer Letter Signed', 'Job Offer Letter Signed', '3015');

UPDATE `as_steps` SET `j1_status_id` = '3005,3016' WHERE `as_steps`.`as_order_key` = '3_ds2019_sent';

INSERT INTO `as_steps_by_dos` (`id`, `dos_program_category_id`, `step_id`, `stage_id`, `step_order`) VALUES 
(NULL, '3', '1', '1', '1'), 
(NULL, '3', '2', '1', '2'),
(NULL, '3', '3', '1', '3'),
(NULL, '3', '4', '1', '4'),
(NULL, '3', '5', '1', '5'),
(NULL, '3', '6', '1', '6'),
(NULL, '3', '30', '1', '7'),
(NULL, '3', '7', '1', '8'),
(NULL, '3', '8', '2', '1'),
(NULL, '3', '9', '2', '2'),
(NULL, '3', '10', '2', '3'),
(NULL, '3', '11', '2', '4'),
(NULL, '3', '12', '2', '5'),
(NULL, '3', '13', '3', '1'),
(NULL, '3', '14', '3', '2'),
(NULL, '3', '34', '3', '3'),
(NULL, '3', '35', '3', '4'),
(NULL, '3', '18', '3', '5'),
(NULL, '3', '32', '3', '6'),
(NULL, '3', '19', '3', '7'),
(NULL, '3', '20', '3', '8'),
(NULL, '3', '21', '3', '9'),
(NULL, '3', '22', '3', '10'),
(NULL, '3', '23', '4', '1'),
(NULL, '3', '24', '4', '2'),
(NULL, '3', '25', '4', '3'), 
(NULL, '3', '28', '4', '4'),
(NULL, '3', '29', '4', '5')

INSERT INTO `program_category` (`id`, `name`, `status`, `category_order`, `created_at`, `updated_at`) VALUES (NULL, 'ODC Camp', '1', '0', current_timestamp(), '0000-00-00 00:00:00.000000');
INSERT INTO `programs` (`id`, `program_name`, `program_enroll_id`, `dos_program_category_id`, `status`, `category`) VALUES 
(NULL, 'ODC Camp - Placement & Filing', NULL, '3', '1', '8'), 
(NULL, 'ODC Camp - All Inclusive', NULL, '3', '1', '8'), 
(NULL, 'ODC Camp - Filing Only', NULL, '3', '1', '8');

-----------------------------------------------------------------------------------------------------------------------
--Ishit Tankaria(1 Feb 2021)

ALTER TABLE `users` CHANGE `status` `status` TINYINT(4) NULL DEFAULT '1' COMMENT '0=De Active,1=Active';

-----------------------------------------------------------------------------------------------------------------------
--Ishit Tankaria(24th march 2021)

INSERT INTO `j1_statuses` (`id`, `status_key`, `status_name`, `category`) VALUES ('3017', 'english-level-interview', 'English Level Interview', '3');

INSERT INTO `as_steps` (`id`, `as_order_key`, `as_title`, `as_icon`, `as_desc_before`, `as_desc_current`, `as_desc_after`, `j1_status_id`) VALUES (NULL, '3_english_level_interview', 'English Level Interview', 'fa-home', 'English Level Interview', 'English Level Interview', 'English Level Interview', '2005');

INSERT INTO `as_steps_by_dos` (`id`, `dos_program_category_id`, `step_id`, `stage_id`, `step_order`) VALUES (NULL, '3', '36', '3', '1');

UPDATE `as_steps_by_dos` SET `dos_program_category_id` = '3' WHERE `as_steps_by_dos`.`id` = 137;

UPDATE `programs` SET `program_name` = 'Full Placement - Intern Current Student' WHERE `programs`.`id` = 1; UPDATE `programs` SET `program_name` = 'Full Placement - Intern Recent Graduate' WHERE `programs`.`id` = 2; UPDATE `programs` SET `program_name` = 'Full Placement - Trainee Recent Graduate' WHERE `programs`.`id` = 3; UPDATE `programs` SET `program_name` = 'Full Placement - Trainee Young Professional' WHERE `programs`.`id` = 4;

-----------------------------------------------------------------------------------------------------------------------
--Ishit Tankaria(24th march 2021)
UPDATE `as_steps_by_dos` SET `step_order` = '2' WHERE `as_steps_by_dos`.`id` = 305;
-----------------------------------------------------------------------------------------------------------------------