set role zope;

-- Update function, this also happens later in a db-mod, but we need it earlier
CREATE or replace FUNCTION sca.fileattr_fill_from_reftable(tablename text)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
declare
  filecat_id bigint;
  appsearch_trigger_was_disabled boolean;
begin

if (tablename != quote_ident(tablename)) then
  raise exception 'Invalid tablename';
end if;

  -- Fill attributes of a category from another table
select filecat.filecat_id
from filecat
where filecat_tablerefname = tablename
into filecat_id;

raise notice '%: filling file attributes from table %', clock_timestamp(), tablename;

execute 'alter  table '||tablename||' disable trigger '||tablename||'_fileattr_upd';
select (tgenabled = 'D')
from pg_trigger
where tgname = tablename || '_appsearch_upd'
into appsearch_trigger_was_disabled;

if (appsearch_trigger_was_disabled is false) then
  execute 'alter  table '||tablename||' disable trigger '||tablename||'_appsearch_upd';
end if;

create temp table _temp_filecat_initfill (
  id bigint primary key,
  shortcut text not null,
  name text not null
);

raise notice '%: fill temp table', clock_timestamp();
execute 'insert into _temp_filecat_initfill (
  id,
  shortcut,
  name
) select
  id,
  shortcut,
  name
from '||tablename||'_fileattrsel';

raise notice '%: create indexes', clock_timestamp();
create unique index _temp_filecat_initfill_id on _temp_filecat_initfill (id);
create unique index _temp_filecat_initfill_shortcut on _temp_filecat_initfill (shortcut);
create unique index _temp_filecat_initfill_name on _temp_filecat_initfill (name);

raise notice '%: deactivate superfluous attributes', clock_timestamp();
update fileattr set
  fileattr_author = 'fileattr_fill_from_reftable deactivate',
  fileattr_modtime = now(),
  fileattr_inactive = true,
  fileattr_name = fileattr_name || ' [id: ' || fileattr_id || ']'
where fileattr_id in (
  select
    fileattr_id
  from fileattr
  left
  join _temp_filecat_initfill
    on shortcut = fileattr_shortcut
  where fileattr_filecat_id = filecat_id
    and not fileattr_inactive
    and id is null
);

raise notice '%: insert missing attributes', clock_timestamp();
insert into fileattr (
  fileattr_author,
  fileattr_name,
  fileattr_shortcut,
  fileattr_filecat_id
) select
  'fileattr_fill_from_reftable insert missing',
  name,
  shortcut,
  filecat_id
from _temp_filecat_initfill
left join fileattr as existing
  on shortcut = existing.fileattr_shortcut
where existing.fileattr_id is null
;

raise notice '%: update foreign key', clock_timestamp();
execute '
update '||tablename||' set
  '||tablename||'_modtime = now(),
  '||tablename||'_author = ''fileattr_fill_from_reftable update'',
  '||tablename||'_fileattr_id = fileattr_id
from fileattr, _temp_filecat_initfill
where fileattr_filecat_id = '||filecat_id||'
  and fileattr_shortcut = shortcut
  and '||tablename||'_id = id
  and (
    '||tablename||'_fileattr_id is null
    or '||tablename||'_fileattr_id != fileattr_id
  )
';

raise notice '%: update name of file attributes', clock_timestamp();
update fileattr set
  fileattr_author = 'fileattr_fill_from_reftable update',
  fileattr_modtime = now(),
  fileattr_name = name
from _temp_filecat_initfill
where fileattr_shortcut = shortcut
  and not fileattr_inactive
  -- no no-op updates
  and fileattr_name != name
;

drop table _temp_filecat_initfill;

execute 'alter  table '||tablename||' enable trigger '||tablename||'_fileattr_upd';
if (appsearch_trigger_was_disabled is false) then
  execute 'alter  table '||tablename||' enable trigger '||tablename||'_appsearch_upd';
end if;

raise notice '%: finished.', clock_timestamp();

end;
$function$;

select filecat_setreference(filecat_id, filecat_tablerefname, filecat_prefix)
from filecat
where filecat_tablerefname is not null;
