create table action.datasource_types
(
    datasource_type_id int         not null,
    datasource_type    varchar(50) not null,
    constraint datasource_types_pk
        primary key (datasource_type_id)
);

INSERT INTO action.datasource_types (datasource_type_id, datasource_type) VALUES (1, 'org');
INSERT INTO action.datasource_types (datasource_type_id, datasource_type) VALUES (2, 'multirater');
INSERT INTO action.datasource_types (datasource_type_id, datasource_type) VALUES (3, 'other');

create table action.uploaded_files
(
    file_id            int auto_increment PRIMARY KEY,
    file_key           varchar(1024) not null,
    file_name          varchar(500)  null,
    account_id         int           null,
    datasource_id      int           null,
    datasource_type_id int           null,
    upload_date        datetime      null,
    file_name          varchar(500)  null,
    is_placeholder     tinyint(1)    0
)
    collate = utf8mb4_unicode_ci;


ALTER TABLE `action`.`uploaded_files` 
ADD UNIQUE INDEX `file_path_name_unique_idx` (`account_id` ASC, `datasource_id` ASC, `datasource_type_id` ASC, `file_key` ASC, `file_name` ASC) VISIBLE;

ALTER TABLE `action`.`uploaded_files` 
ADD INDEX `uploaded_files_datasource_id_datasource_type_id_index` (`datasource_id` ASC, `datasource_type_id` ASC) VISIBLE;


create table action.file_access
(
    file_access_id                int auto_increment,
    file_id                       int                    not null,
    account_id                    int                    null,
    person_id                     int                    null,
    first_viewed                  datetime default NULL  null,
    last_viewed                   datetime default NULL  null,
    times_viewed                  int      default 0     null,
    date_permission_given         datetime default NOW() null,
    permission_given_by           varchar(500)           null,
    constraint file_access_pk
        primary key (file_access_id),
    constraint file_access_accounts_account_id_fk
        foreign key (account_id) references configure.accounts (account_id)
);

alter table action.file_access
    add constraint file_access_uploaded_files_file_id_fk
        foreign key (file_id) references uploaded_files (file_id) ON DELETE CASCADE
  ON UPDATE NO ACTION;


INSERT INTO action.translations_core (tx_core_loc_key, tx_core_language_code, tx_core_translation) VALUES ('file_download_email_subject', 'en', 'File Download');
INSERT INTO action.translations_core (tx_core_loc_key, tx_core_language_code, tx_core_translation) VALUES ('file_download_failure_email_subject', 'en', 'Failure Notification - File download');
INSERT INTO action.translations_core (tx_core_loc_key, tx_core_language_code, tx_core_translation) VALUES ('file_download_text_1', 'en', 'The files you requested can be downloaded via the link below.');


  INSERT INTO action.translations_core (tx_core_loc_key, tx_core_language_code, tx_core_translation)
VALUES ('goals_add_goal', 'en', 'Add Goal');

INSERT INTO action.translations_core (tx_core_loc_key, tx_core_language_code, tx_core_translation) VALUES ('date', 'en', 'Date');
INSERT INTO action.translations_core (tx_core_loc_key, tx_core_language_code, tx_core_translation) VALUES ('datasource', 'en', 'Data Source');
INSERT INTO action.translations_core (tx_core_loc_key, tx_core_language_code, tx_core_translation) VALUES ('type', 'en', 'Type');
INSERT INTO action.translations_core (tx_core_loc_key, tx_core_language_code, tx_core_translation) VALUES ('number_of_goals', 'en', 'Number of %goals%');
INSERT INTO action.translations_core (tx_core_loc_key, tx_core_language_code, tx_core_translation) VALUES ('last_viewed', 'en', 'Last Viewed');
INSERT INTO action.translations_core (tx_core_loc_key, tx_core_language_code, tx_core_translation) VALUES ('datasource_type_org', 'en', 'Org Feedback');
INSERT INTO action.translations_core (tx_core_loc_key, tx_core_language_code, tx_core_translation) VALUES ('datasource_type_multirater', 'en', 'Multi-Rater Feedback');
INSERT INTO action.translations_core (tx_core_loc_key, tx_core_language_code, tx_core_translation) VALUES ('datasource_type_other', 'en', 'Other');
INSERT INTO action.translations_core (tx_core_loc_key, tx_core_language_code, tx_core_translation) VALUES ('new_report', 'en', '*new');
INSERT INTO action.translations_core (tx_core_loc_key, tx_core_language_code, tx_core_translation) VALUES ('no_file_found', 'en', 'No file was found.');

UPDATE action.goals SET linked_report = NULL WHERE linked_report IS NOT NULL;
ALTER TABLE `action`.`goals` 
CHANGE COLUMN `linked_report` `linked_report` INT(11) NULL DEFAULT NULL ;
ALTER TABLE `action`.`goals` 
ADD INDEX `linked_report_to_file_id_idx` (`linked_report` ASC) VISIBLE;

ALTER TABLE `action`.`goals` 
ADD CONSTRAINT `linked_report_to_file_id`
  FOREIGN KEY (`linked_report`)
  REFERENCES `action`.`uploaded_files` (`file_id`)
  ON DELETE SET NULL
  ON UPDATE NO ACTION;