{"-- Add policies for viewing form submissions
-- 1) Super admin full access (manage if needed, but at least SELECT)
DO $$ BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_policies WHERE schemaname = 'public' AND tablename = 'form_submissions' AND policyname = 'Super admin can manage all form submissions'
  ) THEN
    CREATE POLICY \"Super admin can manage all form submissions\"
    ON public.form_submissions
    FOR ALL
    USING (is_current_user_super_admin())
    WITH CHECK (is_current_user_super_admin());
  END IF;
END $$","-- 2) Branch managers can view submissions from representatives in their branches
CREATE OR REPLACE FUNCTION public.branch_manager_can_view_submission(_submission_id uuid)
RETURNS boolean
LANGUAGE sql
STABLE
SECURITY DEFINER
AS $$
  WITH fa AS (
    SELECT fa.representative_id
    FROM public.form_assignments fa
    JOIN public.form_submissions fs ON fs.form_assignment_id = fa.id
    WHERE fs.id = _submission_id
  ),
  manager_branches AS (
    SELECT br.branch_id
    FROM public.branch_roles br
    WHERE br.user_id = auth.uid() AND br.role = 'branch_manager'
  ),
  rep_branches AS (
    SELECT br.branch_id
    FROM public.user_roles ur
    JOIN public.branch_roles br ON br.user_id = ur.user_id
    WHERE ur.id = (SELECT representative_id FROM fa)
  )
  SELECT EXISTS (
    SELECT 1 FROM manager_branches mb
    JOIN rep_branches rb ON rb.branch_id = mb.branch_id
  );
$$","DO $$ BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_policies WHERE schemaname = 'public' AND tablename = 'form_submissions' AND policyname = 'Branch managers can view branch form submissions'
  ) THEN
    CREATE POLICY \"Branch managers can view branch form submissions\"
    ON public.form_submissions
    FOR SELECT
    USING (public.branch_manager_can_view_submission(id));
  END IF;
END $$"}