-- Storage policies for content bucket uploads used by:
-- - Admin branding owner avatar/logo/favicon
-- - Member profile avatar

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM pg_policies
    WHERE schemaname = 'storage'
      AND tablename = 'objects'
      AND policyname = 'content_public_read'
  ) THEN
    CREATE POLICY "content_public_read"
      ON storage.objects
      FOR SELECT
      USING (bucket_id = 'content');
  END IF;
END $$;

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM pg_policies
    WHERE schemaname = 'storage'
      AND tablename = 'objects'
      AND policyname = 'content_admin_manage'
  ) THEN
    CREATE POLICY "content_admin_manage"
      ON storage.objects
      FOR ALL
      USING (
        bucket_id = 'content'
        AND EXISTS (
          SELECT 1
          FROM public.user_roles ur
          WHERE ur.user_id = auth.uid()
            AND ur.role = 'admin'
        )
      )
      WITH CHECK (
        bucket_id = 'content'
        AND EXISTS (
          SELECT 1
          FROM public.user_roles ur
          WHERE ur.user_id = auth.uid()
            AND ur.role = 'admin'
        )
      );
  END IF;
END $$;

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM pg_policies
    WHERE schemaname = 'storage'
      AND tablename = 'objects'
      AND policyname = 'content_user_avatar_insert'
  ) THEN
    CREATE POLICY "content_user_avatar_insert"
      ON storage.objects
      FOR INSERT
      TO authenticated
      WITH CHECK (
        bucket_id = 'content'
        AND name LIKE ('users/' || auth.uid()::text || '/%')
      );
  END IF;
END $$;

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM pg_policies
    WHERE schemaname = 'storage'
      AND tablename = 'objects'
      AND policyname = 'content_user_avatar_update'
  ) THEN
    CREATE POLICY "content_user_avatar_update"
      ON storage.objects
      FOR UPDATE
      TO authenticated
      USING (
        bucket_id = 'content'
        AND name LIKE ('users/' || auth.uid()::text || '/%')
      )
      WITH CHECK (
        bucket_id = 'content'
        AND name LIKE ('users/' || auth.uid()::text || '/%')
      );
  END IF;
END $$;

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM pg_policies
    WHERE schemaname = 'storage'
      AND tablename = 'objects'
      AND policyname = 'content_user_avatar_delete'
  ) THEN
    CREATE POLICY "content_user_avatar_delete"
      ON storage.objects
      FOR DELETE
      TO authenticated
      USING (
        bucket_id = 'content'
        AND name LIKE ('users/' || auth.uid()::text || '/%')
      );
  END IF;
END $$;