/*
# Convert increment_visitor_count to SECURITY INVOKER with granular anon policy

## Summary

Resolves the remaining "Public/Signed-In Users Can Execute SECURITY DEFINER
Function" warnings on `increment_visitor_count()`.

## Why this change
The visitor counter runs on the public site (no login), so `anon` must be able
to bump the counter. The original function used SECURITY DEFINER to bypass RLS.
While the search_path is now locked (previous migration), SECURITY DEFINER with
public execution is still flagged by the security advisor.

The cleaner fix: switch the function to SECURITY INVOKER and grant `anon` a
narrow, meaningful UPDATE policy on `site_visitors` that:
  - Restricts updates to the single singleton row (`is_singleton = true`)
  - Prevents the count from going negative (`count >= 0`)

This eliminates BOTH the SECURITY DEFINER warning AND avoids a new "always true"
RLS warning, because the predicate is a real constraint, not a constant.

## Tables modified (policies only)
- `site_visitors` — new `anon_increment_visitors` UPDATE policy for anon +
  authenticated; the existing `auth_update_visitors` policy is retained for
  admin manual updates.

## Functions modified
- `public.increment_visitor_count()` — changed to SECURITY INVOKER (search_path
  stays locked at `public, pg_temp` from the previous migration).

## Notes
- No data loss; no schema changes.
- Idempotent: policy is dropped before re-creation.
- The function body is unchanged (still a safe atomic upsert + increment).
*/

-- Switch to SECURITY INVOKER so the function runs with the caller's privileges
-- and respects RLS. search_path remains locked from the prior migration.
ALTER FUNCTION public.increment_visitor_count() SECURITY INVOKER;

-- Grant anon + authenticated a narrow UPDATE policy with a meaningful predicate.
-- This replaces reliance on SECURITY DEFINER bypassing RLS.
DROP POLICY IF EXISTS "anon_increment_visitors" ON site_visitors;
CREATE POLICY "anon_increment_visitors" ON site_visitors FOR UPDATE
  TO anon, authenticated
  USING (is_singleton = true)
  WITH CHECK (is_singleton = true AND count >= 0);