CREATE TABLE public.contact_submission_events (
  id uuid primary key default gen_random_uuid(),
  outcome text not null,
  reason text,
  email text,
  ip_hash text,
  created_at timestamptz not null default now()
);

GRANT SELECT ON public.contact_submission_events TO authenticated;
GRANT ALL ON public.contact_submission_events TO service_role;

ALTER TABLE public.contact_submission_events ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Admins read submission events" ON public.contact_submission_events
FOR SELECT TO authenticated USING (public.has_role(auth.uid(), 'admin'::app_role));

ALTER TABLE public.contact_submissions ADD COLUMN IF NOT EXISTS ip_hash text;

CREATE OR REPLACE FUNCTION public.log_contact_event(_outcome text, _reason text, _email text, _ip_hash text)
RETURNS void
LANGUAGE sql
SECURITY DEFINER
AS $$
  INSERT INTO public.contact_submission_events (outcome, reason, email, ip_hash)
  VALUES (left(_outcome, 40), left(_reason, 120), left(_email, 255), left(_ip_hash, 80));
$$;

GRANT EXECUTE ON FUNCTION public.log_contact_event(text, text, text, text) TO anon, authenticated, service_role;

CREATE OR REPLACE FUNCTION public.contact_rate_limited(_email text, _ip_hash text)
RETURNS boolean
LANGUAGE sql
STABLE
SECURITY DEFINER
AS $$
  SELECT (
    (SELECT count(*) FROM public.contact_submissions
      WHERE created_at > now() - interval '1 hour'
        AND (lower(email) = lower(_email) OR (_ip_hash IS NOT NULL AND ip_hash = _ip_hash))) >= 3
  ) OR (
    (SELECT count(*) FROM public.contact_submissions
      WHERE created_at > now() - interval '1 minute'
        AND (lower(email) = lower(_email) OR (_ip_hash IS NOT NULL AND ip_hash = _ip_hash))) >= 1
  );
$$;

GRANT EXECUTE ON FUNCTION public.contact_rate_limited(text, text) TO anon, authenticated, service_role;