-- Konfirmimi i të drejtave nuk mund të falsifikohet nga klienti:
-- kur një anëtar i stafit vendos rights_confirmed_at, serveri vendos kohën dhe identitetin e tij (auth.uid()).
-- Gjithashtu vendos published_at herën e parë që titulli publikohet.
create function public.content_stamp() returns trigger
language plpgsql set search_path = public, pg_temp as $$
begin
  if auth.uid() is not null then
    if new.rights_confirmed_at is not null
       and (tg_op = 'INSERT' or new.rights_confirmed_at is distinct from old.rights_confirmed_at
            or new.rights_confirmed_by is distinct from old.rights_confirmed_by) then
      new.rights_confirmed_at := now();
      new.rights_confirmed_by := auth.uid();
    end if;
    if new.rights_confirmed_at is null then
      new.rights_confirmed_by := null;
    end if;
  end if;
  if new.status = 'published' and new.published_at is null then
    new.published_at := now();
  end if;
  new.updated_at := now();
  return new;
end $$;

create trigger content_stamp before insert or update on public.content_items
  for each row execute function public.content_stamp();