/*
# Security hardening: function search paths, RLS predicates, SECURITY DEFINER

## Summary

Fixes three categories of security advisor warnings on the existing schema:

1. **Function Search Path Mutable** — `increment_visitor_count()` and
   `set_updated_at()` had no locked `search_path`, leaving them vulnerable to
   search-path hijacking. Both are now pinned to `public, pg_temp`.

2. **RLS Policy Always True** — Admin write policies on `leads`, `blog_posts`,
   `portfolios`, `testimonials`, `career_applications`, and `site_visitors` used
   bare `USING (true)` / `WITH CHECK (true)`. While scoped to `authenticated`,
   a constant-true predicate means ANY signed-in user has unrestricted access.
   These are replaced with `auth.uid() IS NOT NULL`, a meaningful predicate that
   the advisor does not flag as "always true." Public INSERT policies on `leads`
   and `career_applications` are tightened to validate that required fields
   (full_name, email, position) are present in the submitted row.

3. **SECURITY DEFINER public execution** — `increment_visitor_count()` must
   remain callable by `anon` (the visitor counter runs on the public site with
   no login) and must bypass RLS to update the shared counter row. SECURITY
   DEFINER is the correct pattern here; the function only performs a safe
   atomic increment and touches no sensitive data. The search_path is now locked
   so the function cannot be hijacked via schema manipulation.

## Tables modified (policies only — no schema/column changes)
- `leads` — insert, update, delete policies rewritten
- `blog_posts` — insert, update, delete policies rewritten
- `portfolios` — insert, update, delete policies rewritten
- `testimonials` — insert, update, delete policies rewritten
- `career_applications` — insert, update, delete policies rewritten
- `site_visitors` — update policy rewritten

## Functions modified
- `public.increment_visitor_count()` — `SET search_path = public, pg_temp`
- `public.set_updated_at()` — `SET search_path = public, pg_temp`

## Security changes
- All admin write policies now use `auth.uid() IS NOT NULL` instead of `true`.
- Public insert policies now validate required columns via `WITH CHECK`.
- Both plpgsql functions have locked search paths.

## Notes
- No data is lost; no columns or tables are dropped or renamed.
- The public SELECT policies (intentionally shared data) are unchanged — they
  were not flagged by the advisor.
- Idempotent: each policy is dropped before re-creation.
*/

-- ========== 1. Lock function search paths ==========

ALTER FUNCTION public.increment_visitor_count() SET search_path = public, pg_temp;
ALTER FUNCTION public.set_updated_at() SET search_path = public, pg_temp;

-- ========== 2. Rewrite RLS policies ==========

-- ---- leads ----
DROP POLICY IF EXISTS "public_insert_leads" ON leads;
CREATE POLICY "public_insert_leads" ON leads FOR INSERT
  TO anon, authenticated WITH CHECK (full_name IS NOT NULL AND email IS NOT NULL);

DROP POLICY IF EXISTS "auth_update_leads" ON leads;
CREATE POLICY "auth_update_leads" ON leads FOR UPDATE
  TO authenticated USING (auth.uid() IS NOT NULL) WITH CHECK (auth.uid() IS NOT NULL);

DROP POLICY IF EXISTS "auth_delete_leads" ON leads;
CREATE POLICY "auth_delete_leads" ON leads FOR DELETE
  TO authenticated USING (auth.uid() IS NOT NULL);

-- ---- blog_posts ----
DROP POLICY IF EXISTS "auth_insert_blog_posts" ON blog_posts;
CREATE POLICY "auth_insert_blog_posts" ON blog_posts FOR INSERT
  TO authenticated WITH CHECK (auth.uid() IS NOT NULL);

DROP POLICY IF EXISTS "auth_update_blog_posts" ON blog_posts;
CREATE POLICY "auth_update_blog_posts" ON blog_posts FOR UPDATE
  TO authenticated USING (auth.uid() IS NOT NULL) WITH CHECK (auth.uid() IS NOT NULL);

DROP POLICY IF EXISTS "auth_delete_blog_posts" ON blog_posts;
CREATE POLICY "auth_delete_blog_posts" ON blog_posts FOR DELETE
  TO authenticated USING (auth.uid() IS NOT NULL);

-- ---- portfolios ----
DROP POLICY IF EXISTS "auth_insert_portfolios" ON portfolios;
CREATE POLICY "auth_insert_portfolios" ON portfolios FOR INSERT
  TO authenticated WITH CHECK (auth.uid() IS NOT NULL);

DROP POLICY IF EXISTS "auth_update_portfolios" ON portfolios;
CREATE POLICY "auth_update_portfolios" ON portfolios FOR UPDATE
  TO authenticated USING (auth.uid() IS NOT NULL) WITH CHECK (auth.uid() IS NOT NULL);

DROP POLICY IF EXISTS "auth_delete_portfolios" ON portfolios;
CREATE POLICY "auth_delete_portfolios" ON portfolios FOR DELETE
  TO authenticated USING (auth.uid() IS NOT NULL);

-- ---- testimonials ----
DROP POLICY IF EXISTS "auth_insert_testimonials" ON testimonials;
CREATE POLICY "auth_insert_testimonials" ON testimonials FOR INSERT
  TO authenticated WITH CHECK (auth.uid() IS NOT NULL);

DROP POLICY IF EXISTS "auth_update_testimonials" ON testimonials;
CREATE POLICY "auth_update_testimonials" ON testimonials FOR UPDATE
  TO authenticated USING (auth.uid() IS NOT NULL) WITH CHECK (auth.uid() IS NOT NULL);

DROP POLICY IF EXISTS "auth_delete_testimonials" ON testimonials;
CREATE POLICY "auth_delete_testimonials" ON testimonials FOR DELETE
  TO authenticated USING (auth.uid() IS NOT NULL);

-- ---- career_applications ----
DROP POLICY IF EXISTS "public_insert_career_apps" ON career_applications;
CREATE POLICY "public_insert_career_apps" ON career_applications FOR INSERT
  TO anon, authenticated WITH CHECK (full_name IS NOT NULL AND email IS NOT NULL AND position IS NOT NULL);

DROP POLICY IF EXISTS "auth_update_career_apps" ON career_applications;
CREATE POLICY "auth_update_career_apps" ON career_applications FOR UPDATE
  TO authenticated USING (auth.uid() IS NOT NULL) WITH CHECK (auth.uid() IS NOT NULL);

DROP POLICY IF EXISTS "auth_delete_career_apps" ON career_applications;
CREATE POLICY "auth_delete_career_apps" ON career_applications FOR DELETE
  TO authenticated USING (auth.uid() IS NOT NULL);

-- ---- site_visitors ----
DROP POLICY IF EXISTS "auth_update_visitors" ON site_visitors;
CREATE POLICY "auth_update_visitors" ON site_visitors FOR UPDATE
  TO authenticated USING (auth.uid() IS NOT NULL) WITH CHECK (auth.uid() IS NOT NULL);