alter table ${tenant_name}_mod_entities_links.authority disable trigger all;

insert into ${tenant_name}_mod_entities_links.authority(id,
                                              natural_id,
                                              source_file_id,
                                              source,
                                              _version,
                                              subject_heading_code,
                                              heading,
                                              heading_type,
                                              sft_headings,
                                              saft_headings,
                                              identifiers,
                                              notes,
                                              created_date,
                                              updated_date,
                                              created_by_user_id,
                                              updated_by_user_id)
select id,
       natural_id,
       source_file_id,
       source,
       _version,
       subject_heading_code,
       heading_heading_type[1] heading,
       heading_heading_type[2] heading_type,
       sft_headings,
       saft_headings,
       identifiers,
       notes,
       created_date,
       updated_date,
       created_by_user_id,
       updated_by_user_id
from (select id,
             (jsonb ->> 'naturalId')::text                                           natural_id,
             (jsonb ->> 'sourceFileId')::uuid                                        source_file_id,
             'MARC'                                                                  source, -- (jsonb ->> 'source')::text source
             (jsonb ->> '_version')::int                                             _version,
             (jsonb ->> 'subjectHeadings')::char(1)                                  subject_heading_code,

             case
                 when jsonb ->> 'personalName' is not null then Array [jsonb ->> 'personalName', 'personalName']
                 when jsonb ->> 'personalNameTitle' is not null
                     then Array [jsonb ->> 'personalNameTitle', 'personalNameTitle']
                 when jsonb ->> 'corporateName' is not null then Array [jsonb ->> 'corporateName', 'corporateName']
                 when jsonb ->> 'corporateNameTitle' is not null
                     then Array [jsonb ->> 'corporateNameTitle', 'corporateNameTitle']
                 when jsonb ->> 'meetingName' is not null then Array [jsonb ->> 'meetingName', 'meetingName']
                 when jsonb ->> 'meetingNameTitle' is not null
                     then Array [jsonb ->> 'meetingNameTitle', 'meetingNameTitle']
                 when jsonb ->> 'uniformTitle' is not null then Array [jsonb ->> 'uniformTitle', 'uniformTitle']
                 when jsonb ->> 'topicalTerm' is not null then Array [jsonb ->> 'topicalTerm', 'topicalTerm']
                 when jsonb ->> 'geographicName' is not null then Array [jsonb ->> 'geographicName', 'geographicName']
                 when jsonb ->> 'genreTerm' is not null then Array [jsonb ->> 'genreTerm', 'genreTerm']
                 end                                                                 heading_heading_type,
             (select jsonb_agg(val)
              from (select jsonb_build_object('headingType', 'personalName', 'heading', value) val
                    from jsonb_array_elements_text(jsonb -> 'sftPersonalName') x1
                    union all
                    select jsonb_build_object('headingType', 'personalNameTitle', 'heading', value) val
                    from jsonb_array_elements_text(jsonb -> 'sftPersonalNameTitle') x2
                    union all
                    select jsonb_build_object('headingType', 'corporateName', 'heading', value) val
                    from jsonb_array_elements_text(jsonb -> 'sftCorporateName') x3
                    union all
                    select jsonb_build_object('headingType', 'corporateNameTitle', 'heading', value) val
                    from jsonb_array_elements_text(jsonb -> 'sftCorporateNameTitle') x4
                    union all
                    select jsonb_build_object('headingType', 'meetingName', 'heading', value) val
                    from jsonb_array_elements_text(jsonb -> 'sftMeetingName') x5
                    union all
                    select jsonb_build_object('headingType', 'meetingNameTitle', 'heading', value) val
                    from jsonb_array_elements_text(jsonb -> 'sftMeetingNameTitle') x6
                    union all
                    select jsonb_build_object('headingType', 'uniformTitle', 'heading', value) val
                    from jsonb_array_elements_text(jsonb -> 'sftUniformTitle') x7
                    union all
                    select jsonb_build_object('headingType', 'topicalTerm', 'heading', value) val
                    from jsonb_array_elements_text(jsonb -> 'sftTopicalTerm') x8
                    union all
                    select jsonb_build_object('headingType', 'geographicName', 'heading', value) val
                    from jsonb_array_elements_text(jsonb -> 'sftGeographicName') x9
                    union all
                    select jsonb_build_object('headingType', 'genreTerm', 'heading', value) val
                    from jsonb_array_elements_text(jsonb -> 'sftGenreTerm') x10) x)  sft_headings,
             (select jsonb_agg(val)
              from (select jsonb_build_object('headingType', 'personalName', 'heading', value) val
                    from jsonb_array_elements_text(jsonb -> 'saftPersonalName') x1
                    union all
                    select jsonb_build_object('headingType', 'personalNameTitle', 'heading', value) val
                    from jsonb_array_elements_text(jsonb -> 'saftPersonalNameTitle') x2
                    union all
                    select jsonb_build_object('headingType', 'corporateName', 'heading', value) val
                    from jsonb_array_elements_text(jsonb -> 'saftCorporateName') x3
                    union all
                    select jsonb_build_object('headingType', 'corporateNameTitle', 'heading', value) val
                    from jsonb_array_elements_text(jsonb -> 'saftCorporateNameTitle') x4
                    union all
                    select jsonb_build_object('headingType', 'meetingName', 'heading', value) val
                    from jsonb_array_elements_text(jsonb -> 'saftMeetingName') x5
                    union all
                    select jsonb_build_object('headingType', 'meetingNameTitle', 'heading', value) val
                    from jsonb_array_elements_text(jsonb -> 'saftMeetingNameTitle') x6
                    union all
                    select jsonb_build_object('headingType', 'uniformTitle', 'heading', value) val
                    from jsonb_array_elements_text(jsonb -> 'saftUniformTitle') x7
                    union all
                    select jsonb_build_object('headingType', 'topicalTerm', 'heading', value) val
                    from jsonb_array_elements_text(jsonb -> 'saftTopicalTerm') x8
                    union all
                    select jsonb_build_object('headingType', 'geographicName', 'heading', value) val
                    from jsonb_array_elements_text(jsonb -> 'saftGeographicName') x9
                    union all
                    select jsonb_build_object('headingType', 'genreTerm', 'heading', value) val
                    from jsonb_array_elements_text(jsonb -> 'saftGenreTerm') x10) x) saft_headings,
             (jsonb -> 'identifiers')                                                identifiers,
             (jsonb -> 'notes')                                                      notes,

             (jsonb -> 'metadata' ->> 'createdDate')::timestamp /*with time zone*/   created_date,
             (jsonb -> 'metadata' ->> 'updatedDate')::timestamp /*with time zone*/   updated_date,
             (jsonb -> 'metadata' ->> 'createdByUserId')::uuid                       created_by_user_id,
             (jsonb -> 'metadata' ->> 'updatedByUserId')::uuid                       updated_by_user_id
      from ${tenant_name}_mod_inventory_storage.authority) a;

alter table ${tenant_name}_mod_entities_links.authority enable trigger all;