CREATE TABLE action.account_terminology
(
    account_terminology_id int(11) auto_increment PRIMARY KEY,
    account_id int(11) DEFAULT NULL,
    language_code varchar(100) DEFAULT NULL,
    translation_code varchar(245) DEFAULT NULL,
    translation text DEFAULT NULL
)

ALTER TABLE `action`.`account_terminology` 
CHARACTER SET = utf8mb4 ;

ALTER TABLE `action`.`account_terminology` 
CHANGE COLUMN `translation` `translation` TEXT CHARACTER SET 'utf8mb4' NULL DEFAULT NULL ;

ALTER TABLE `action`.`account_terminology` 
ADD UNIQUE INDEX `acc_to_code_lng` (`account_id` ASC, `language_code` ASC, `translation_code` ASC) VISIBLE;

CREATE TABLE action.translations_terminology (
    tx_terminology_id int(11) auto_increment PRIMARY KEY,
    language_code varchar(100) DEFAULT NULL,
    translation_code varchar(245) DEFAULT NULL,
    translation text DEFAULT NULL
)

ALTER TABLE `action`.`translations_terminology` 
CHARACTER SET = utf8mb4 ;

ALTER TABLE `action`.`translations_terminology` 
CHANGE COLUMN `translation` `translation` TEXT CHARACTER SET 'utf8mb4' NULL DEFAULT NULL ;

ALTER TABLE `action`.`translations_terminology` 
ADD UNIQUE INDEX `lng_to_tx_code` (`language_code` ASC, `translation_code` ASC) VISIBLE;

INSERT INTO action.translations_terminology (language_code, translation_code, translation)
VALUES
('en', 'goal', 'goal'),
('en', 'goals', 'goals'),
('en', 'task', 'task'),
('en', 'tasks', 'tasks'),
('en', 'focus_area', 'focus area'),
('en', 'focus_areas', 'focus areas'),
('en', 'team', 'team'),
('en', 'team_member', 'team member'),
('en', 'team_members', 'team members');

ALTER TABLE action.goals ADD COLUMN goal_start_date datetime DEFAULT NULL AFTER goal_description;
ALTER TABLE action.goals ADD COLUMN focus_area int(11) DEFAULT NULL;
ALTER TABLE action.goals ADD COLUMN linked_report varchar(256) DEFAULT NULL;
ALTER TABLE action.goals ADD COLUMN last_modified TIMESTAMP DEFAULT NOW();
ALTER TABLE `action`.`goals` CHANGE COLUMN `last_modified` `last_modified` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP();
UPDATE action.goals SET last_modified = NULL WHERE last_modified IS NOT NULL;


UPDATE action.translations_core SET tx_core_translation = '%goals%' WHERE tx_core_loc_key = 'nav_page_goals' AND tx_core_language_code = 'en';
UPDATE action.translations_core SET tx_core_translation = 'There are no available %goals%' WHERE tx_core_loc_key = 'dashboard_no_goals' AND tx_core_language_code = 'en';
UPDATE action.translations_core SET tx_core_translation = '%goal% Status Overview' WHERE tx_core_loc_key = 'dashboard_goal_overview' AND tx_core_language_code = 'en';
UPDATE action.translations_core SET tx_core_translation = '%goal% Details' WHERE tx_core_loc_key = 'dashboard_panel_goal_details' AND tx_core_language_code = 'en';
UPDATE action.translations_core SET tx_core_translation = '%goal% Details' WHERE tx_core_loc_key = 'goals_goal_details' AND tx_core_language_code = 'en';
UPDATE action.translations_core SET tx_core_translation = 'Create new %goal%' WHERE tx_core_loc_key = 'goals_create' AND tx_core_language_code = 'en';
UPDATE action.translations_core SET tx_core_translation = 'View %goal% Progress' WHERE tx_core_loc_key = 'team_view_goal_progress' AND tx_core_language_code = 'en';
UPDATE action.translations_core SET tx_core_translation = '%goal% updated successfully' WHERE tx_core_loc_key = 'goal_updated' AND tx_core_language_code = 'en';
UPDATE action.translations_core SET tx_core_translation = '%goal% created successfully' WHERE tx_core_loc_key = 'goal_created' AND tx_core_language_code = 'en';
UPDATE action.translations_core SET tx_core_translation = 'Overview' WHERE tx_core_loc_key = 'nav_bar_goals' AND tx_core_language_code = 'en';
UPDATE action.translations_core SET tx_core_translation = '%goal% Category Overview' WHERE tx_core_loc_key = 'org_goals_competencies' AND tx_core_language_code = 'en';
UPDATE action.translations_core SET tx_core_translation = 'As you review your report you can click on a competency or individual item to create a %goal% connected to it' WHERE tx_core_loc_key = 'reports_instructions_alert' AND tx_core_language_code = 'en';
UPDATE action.translations_core SET tx_core_translation = '%goal% Category Detail' WHERE tx_core_loc_key = 'org_goals_competencies_detail' AND tx_core_language_code = 'en';
UPDATE action.translations_core SET tx_core_translation = 'No Assignment' WHERE tx_core_loc_key = 'org_goals_unlinked_goals' AND tx_core_language_code = 'en';
UPDATE action.translations_core SET tx_core_translation = '%goal% Deleted Successfully' WHERE tx_core_loc_key = 'goal_deleted' AND tx_core_language_code = 'en';
UPDATE action.translations_core SET tx_core_translation = 'Are you sure you want to delete this %goal%?' WHERE tx_core_loc_key = 'goals_confirm_delete_goal' AND tx_core_language_code = 'en';
UPDATE action.translations_core SET tx_core_translation = '%goal% progress updated: ' WHERE tx_core_loc_key = 'goals_goal_history_progress' AND tx_core_language_code = 'en';
UPDATE action.translations_core SET tx_core_translation = '%goal% created' WHERE tx_core_loc_key = 'goals_goal_history_created' AND tx_core_language_code = 'en';
UPDATE action.translations_core SET tx_core_translation = '%goal% completed' WHERE tx_core_loc_key = 'goals_goal_history_completed' AND tx_core_language_code = 'en';
UPDATE action.translations_core SET tx_core_translation = 'Are you sure you want to delete this %goal%?' WHERE tx_core_loc_key = 'goals_delete_confirm' AND tx_core_language_code = 'en';
UPDATE action.translations_core SET tx_core_translation = 'Delete %goal%' WHERE tx_core_loc_key = 'goals_delete_goal' AND tx_core_language_code = 'en';
UPDATE action.translations_core SET tx_core_translation = '%goal% updated' WHERE tx_core_loc_key = 'goals_goal_history_updated' AND tx_core_language_code = 'en';
UPDATE action.translations_core SET tx_core_translation = '%tasks%' WHERE tx_core_loc_key = 'goals_goal_tasks' AND tx_core_language_code = 'en';
UPDATE action.translations_core SET tx_core_translation = 'Delete %task%?' WHERE tx_core_loc_key = 'goals_goal_delete_task' AND tx_core_language_code = 'en';
UPDATE action.translations_core SET tx_core_translation = 'There are no %tasks%' WHERE tx_core_loc_key = 'goals_goal_no_tasks' AND tx_core_language_code = 'en';
UPDATE action.translations_core SET tx_core_translation = '%task% name' WHERE tx_core_loc_key = 'goals_goal_task_name' AND tx_core_language_code = 'en';
UPDATE action.translations_core SET tx_core_translation = 'Add %task%' WHERE tx_core_loc_key = 'goals_goal_add_task' AND tx_core_language_code = 'en';
UPDATE action.translations_core SET tx_core_translation = '%task% updated successfully' WHERE tx_core_loc_key = 'task_updated' AND tx_core_language_code = 'en';
UPDATE action.translations_core SET tx_core_translation = '%task% successfully created' WHERE tx_core_loc_key = 'task_created' AND tx_core_language_code = 'en';
UPDATE action.translations_core SET tx_core_translation = '%task% successfully deleted' WHERE tx_core_loc_key = 'task_deleted' AND tx_core_language_code = 'en';
UPDATE action.translations_core SET tx_core_translation = '%task% updated: ' WHERE tx_core_loc_key = 'goal_history_task_updated' AND tx_core_language_code = 'en';
UPDATE action.translations_core SET tx_core_translation = '%task% created: ' WHERE tx_core_loc_key = 'goal_history_task_created' AND tx_core_language_code = 'en';
UPDATE action.translations_core SET tx_core_translation = '%task% completed: ' WHERE tx_core_loc_key = 'goal_history_task_completed' AND tx_core_language_code = 'en';
UPDATE action.translations_core SET tx_core_translation = '%task% deleted' WHERE tx_core_loc_key = 'goal_history_task_deleted' AND tx_core_language_code = 'en';
UPDATE action.translations_core SET tx_core_translation = '%team% %goals%' WHERE tx_core_loc_key = 'nav_page_team' AND tx_core_language_code = 'en';
UPDATE action.translations_core SET tx_core_translation = 'Org %goals%' WHERE tx_core_loc_key = 'nav_page_org' AND tx_core_language_code = 'en';
UPDATE action.translations_core SET tx_core_translation = 'There are no %goals%' WHERE tx_core_loc_key = 'goals_board_no_items' AND tx_core_language_code = 'en';
UPDATE action.translations_core SET tx_core_translation = '%goal% status' WHERE tx_core_loc_key = 'goals_assigned_item_status' AND tx_core_language_code = 'en';
UPDATE action.translations_core SET tx_core_translation = '%goal% updated successfully!' WHERE tx_core_loc_key = 'item_updated' AND tx_core_language_code = 'en';


INSERT INTO action.translations_core (tx_core_language_code, tx_core_loc_key, tx_core_translation)
VALUES
('en', 'nav_page_my_goals', 'My %goals%'),
('en', 'notifications', 'Notifications'),
('en', 'no_notifications', 'No notifications'),
('en', 'goal_status_on_track', 'On Track'),
('en', 'goal_status_at_risk', 'At Risk'),
('en', 'goal_not_started', '%goal% has not started yet. Progress cannot be updated.'),
('en', 'focus_area_overview', '%focus_area% Overview'),
('en', 'focus_area_stats', '%focus_area% Stats'),
('en', 'org_goal_list_title', 'All %goals%'),
('en', 'org_goal_list_none_found', 'No %goals% found.'),
('en', 'org_goal_list_col_name', 'Name'),
('en', 'org_goal_list_col_goal', '%goal%'),
('en', 'org_goal_list_col_status', 'Status'),
('en', 'org_goal_list_col_perc_complete', 'Percentage Complete'),
('en', 'org_goal_list_col_completed_tasks', 'Completed %tasks%'),
('en', 'start_date', 'Start Date'),
('en', 'filters', 'Filters'),
('en', 'download_toaster', 'You will receive an email with a link to download your file.'),
('en', 'nav_page_team_overview', 'Overview'),
('en', 'team_overview_my_people', 'My %team_members%'),
('en', 'add', 'Add'),
('en', 'focus_area_name', '%focus_area% Name'),
('en', 'goal_count', '%goal% Count'),
('en', 'created_date', 'Created Date'),
('en', 'manage_languages', 'Manage Languages'),
('en', 'archive', 'Archive'),
('en', 'unarchive', 'Unarchive'),
('en', 'focus_area_delete_warning', 'Are you sure you want to delete the %focus_area%? %goals% with this %focus_area% assigned will no longer have a %focus_area%.'),
('en', 'nav_page_settings', 'Settings'),
('en', 'nav_page_org_overview', 'Overview'),
('en', 'nav_page_org_list', '%goal% List'),
('en', 'nav_page_team_list', '%goal% List'),
('en', 'direct_reports', 'Direct Reports'),
('en', 'span_of_control', 'Span of Control'),
('en', 'last_published', 'Last Published'),
('en', 'notification_inactive_member', 'This %team_member% has not made any goal progress in at least %DAYS% days.'),
('en', 'manager', 'Manager'),
('en', 'goal_not_updated', '%goal% not updated. View %goal% details to update the start date.'),
('en', 'invalid_date_range', 'invalid date range'),
('en', 'no_selection', 'No Selection'),
('en', 'goal_progress_not_updated', '%goal% has not started yet. Progress cannot be updated.'),
('en', 'goal_history_focus_area_deleted', '%focus_area% was deleted; %focus_area% unassigned from %goal%'),
('en', 'focus_area_name_in_use', 'Name is already in use. Duplicate names are not allowed.'),
('en', 'goal_list_column_selector_panel', 'Select Columns'),
('en', 'goal_list_filter_selector_panel', 'Select Filters'),
('en', 'filter_label_person_id', 'Name'),
('en', 'filter_label_focus_areas', '%focus_area%'),
('en', 'filter_label_status', 'Status'),
('en', 'goal_list_completed_tasks', '%completed_tasks% of %anticipated_tasks%'),
('en', 'goals_goal_no_history', 'There is no goal history'),
('en', 'add_focus_area', 'Add %focus_area%'),
('en', 'focus_area_added', '%focus_area% added!'),
('en', 'focus_area_deleted', '%focus_area% deleted!'),
('en', 'org_download_email_subject', 'Data Download - Org %goals%'),
('en', 'org_download_failure_email_subject', 'Failure Notification - Org %goals% Data Download'),
('en', 'team_download_email_subject', 'Data Download - %team% %goals%'),
('en', 'team_download_failure_email_subject', 'Failure Notification - %team %goals% Data Download'),
('en', 'org_download_text_1', 'The Org %goals% list can be downloaded via the link below.'),
('en', 'team_download_text_1', 'The %team% %goals% list can be downloaded via the link below.'),
('en', 'download_failure_text_1', 'An error occured while generating the requested file: '),
('en', 'download_button', 'Download File'),
('en', 'language', 'Language'),
('en', 'focus_area_translations_updated', '%count% %focus_area% translations updated.'),
('en', 'focus_area_translations_failed', '%count% %focus_area% translations failed to update.'),
('en', 'no_focus_areas_found', 'No %focus_areas% found.'),
('en', 'no_focus_areas', 'No %focus_areas%');



CREATE TABLE action.focus_areas (
	focus_area_id int(11) auto_increment PRIMARY KEY,
    account_id int(11) DEFAULT NULL,
    focus_area_name varchar(50) DEFAULT NULL,
    created_date datetime DEFAULT NOW(),
    archived tinyint(1) DEFAULT 0
);

ALTER TABLE `action`.`goals` 
ADD INDEX `fcs_area_to_goal_idx` (`focus_area` ASC) VISIBLE;

ALTER TABLE `action`.`goals` 
ADD CONSTRAINT `fcs_area_to_goal`
  FOREIGN KEY (`focus_area`)
  REFERENCES `action`.`focus_areas` (`focus_area_id`)
  ON DELETE SET NULL
  ON UPDATE NO ACTION;

CREATE TABLE action.translations_focus_areas (
	translation_focus_area_id int(11) auto_increment PRIMARY KEY,
    focus_area_id int(11) DEFAULT NULL,
    language_code varchar(245) DEFAULT NULL,
    translation varchar(100) DEFAULT NULL
);


ALTER TABLE `action`.`translations_focus_areas` 
CHARACTER SET = utf8mb4 ;

ALTER TABLE `action`.`translations_focus_areas` 
CHANGE COLUMN `translation` `translation` VARCHAR(100) CHARACTER SET 'utf8mb4' NULL DEFAULT NULL ;

ALTER TABLE `action`.`translations_focus_areas` 
ADD UNIQUE INDEX `fa_to_lang` (`focus_area_id` ASC, `language_code` ASC) VISIBLE;
;

ALTER TABLE `action`.`translations_focus_areas` 
ADD CONSTRAINT `fa_to_translation`
  FOREIGN KEY (`focus_area_id`)
  REFERENCES `action`.`focus_areas` (`focus_area_id`)
  ON DELETE CASCADE
  ON UPDATE NO ACTION;

ALTER TABLE action.log_action_planning DROP COLUMN assigned_item_id;
DROP TABLE action.assigned_items;
DROP TABLE action.assigned_items_to_people;
DROP TABLE action.translations_assigned_items;
DROP TABLE action.goal_status;
ALTER TABLE action.goals DROP COLUMN goal_status;

DELETE FROM action.translations_core WHERE tx_core_loc_key = 'nav_page_dashboard';
DELETE FROM action.translations_core WHERE tx_core_loc_key = 'nav_page_goals';
DELETE FROM action.translations_core WHERE tx_core_loc_key = 'dashboard_no_goals';
DELETE FROM action.translations_core WHERE tx_core_loc_key = 'dashboard_assigned_items';
DELETE FROM action.translations_core WHERE tx_core_loc_key = 'goals_assigned_item_status';
DELETE FROM action.translations_core WHERE tx_core_loc_key = 'reports_start_date';
DELETE FROM action.translations_core WHERE tx_core_loc_key = 'switcher_switch_to';
DELETE FROM action.translations_core WHERE tx_core_loc_key = 'team_person_name';
DELETE FROM action.translations_core WHERE tx_core_loc_key = 'team_person_email';
DELETE FROM action.translations_core WHERE tx_core_loc_key = 'team_view_goal_progress';
DELETE FROM action.translations_core WHERE tx_core_loc_key = 'no_status';
DELETE FROM action.translations_core WHERE tx_core_loc_key = 'to_do_completed';
DELETE FROM action.translations_core WHERE tx_core_loc_key = 'item_updated';
DELETE FROM action.translations_core WHERE tx_core_loc_key = 'nav_bar_assigned_items';
DELETE FROM action.translations_core WHERE tx_core_loc_key = 'assigned_to';
DELETE FROM action.translations_core WHERE tx_core_loc_key = 'assigned_item_not_found';
DELETE FROM action.translations_core WHERE tx_core_loc_key = 'filter_statistics_label';
DELETE FROM action.translations_core WHERE tx_core_loc_key = 'org_goals_competencies';
DELETE FROM action.translations_core WHERE tx_core_loc_key = 'org_goals_competency';
DELETE FROM action.translations_core WHERE tx_core_loc_key = 'type_multirater';
DELETE FROM action.translations_core WHERE tx_core_loc_key = 'assigned_items_name';
DELETE FROM action.translations_core WHERE tx_core_loc_key = 'assigned_items_description';
DELETE FROM action.translations_core WHERE tx_core_loc_key = 'assigned_items_view_item';
DELETE FROM action.translations_core WHERE tx_core_loc_key = 'assigned_items_view_statistics';
DELETE FROM action.translations_core WHERE tx_core_loc_key = 'assigned_items_view_translations';
DELETE FROM action.translations_core WHERE tx_core_loc_key = 'assigned_items_delete_confirm';
DELETE FROM action.translations_core WHERE tx_core_loc_key = 'assigned_items_edit_item';
DELETE FROM action.translations_core WHERE tx_core_loc_key = 'assigned_items_create_item';
DELETE FROM action.translations_core WHERE tx_core_loc_key = 'assigned_items_item_translations';
DELETE FROM action.translations_core WHERE tx_core_loc_key = 'assigned_items_item_details';
DELETE FROM action.translations_core WHERE tx_core_loc_key = 'competency_banner_report';
DELETE FROM action.translations_core WHERE tx_core_loc_key = 'reports_instructions_alert';
DELETE FROM action.translations_core WHERE tx_core_loc_key = 'org_goals_competencies_detail';
DELETE FROM action.translations_core WHERE tx_core_loc_key = 'type_org';
DELETE FROM action.translations_core WHERE tx_core_loc_key = 'org_goal_list_col_goal';
DELETE FROM action.translations_core WHERE tx_core_loc_key = 'org_goal_list_col_status';
DELETE FROM action.translations_core WHERE tx_core_loc_key = 'filters';


