CREATE SEQUENCE IF NOT EXISTS public.nomination_ref_seq START 1;
ALTER TABLE public.nominations ADD COLUMN reference text NOT NULL UNIQUE DEFAULT ('TU26-' || lpad(nextval('public.nomination_ref_seq')::text, 6, '0'));
CREATE OR REPLACE FUNCTION public.submit_nomination(p jsonb)
RETURNS text LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE r text;
BEGIN
  INSERT INTO public.nominations (nominee_name, age, district, nominee_contact, category, years, description, nominator_name, nominator_phone, nominator_email, lang)
  VALUES (p->>'nominee_name', (p->>'age')::int, p->>'district', p->>'nominee_contact', p->>'category', (p->>'years')::int, p->>'description', p->>'nominator_name', p->>'nominator_phone', p->>'nominator_email', coalesce(p->>'lang','si'))
  RETURNING reference INTO r;
  RETURN r;
END $$;
REVOKE ALL ON FUNCTION public.submit_nomination(jsonb) FROM public;
GRANT EXECUTE ON FUNCTION public.submit_nomination(jsonb) TO anon, authenticated;