-- deal with new content
create or replace function content_insert() returns trigger
  security definer
  language plpgsql
as $$
  begin
    insert into content_mimetype_to_index(id)
    values(new.sha1);
    return new;
  end;
$$;

drop trigger if exists after_content_insert_cascade_mimetype on content;
create trigger after_content_insert_cascade_mimetype after insert on content
  for each row execute procedure content_insert();

-- deal with new content_mimetype
create or replace function content_mimetype_insert() returns trigger
  security definer
  language plpgsql
as $$
  begin
    -- FIXME: Deal with existing data (due to insert on existing data)
    insert into content_mimetype_to_index(id, mimetype)
    values(new.id, new.mimetype);
    return new;
  end;
$$;

drop trigger if exists after_content_mimetype_insert_cascade_mimetype on content_mimetype;
create trigger after_content_mimetype_insert_cascade_mimetype after insert on content_mimetype
  for each row execute procedure content_mimetype_insert();

-- deal with content_mimetype update
create or replace function content_mimetype_update() returns trigger
  security definer
  language plpgsql
as $$
  begin
    update content_mimetype_to_index
      set mimetype=new.mimetype
    where id=new.id;
    return new;
  end;
$$;

drop trigger if exists after_content_mimetype_update_cascade_mimetype on content_mimetype;
create trigger after_content_mimetype_update_cascade_mimetype after insert on content_mimetype
  for each row execute procedure content_mimetype_update();

-- bootstrap values initialization
insert into content_mimetype_to_index(id, mimetype)
select c.sha1 as id, cm.mimetype as mimetype
from content c tablesample system(0.0001) left join content_mimetype cm on c.sha1=cm.id;

-- build index for reading unindexed mimetype
create index content_mimetype_to_index_idx
on content_mimetype_to_index(id)
where mimetype is null;

create unique index content_mimetype_to_index_idx
on content_mimetype_to_index(id, mimetype)